Forum Discussion
Excel Undo Function Not Working Properly in Workbook with UDF
I am having a problem with Excel's undo function in a macro-enabled workbook that contains a user-defined function. If I take a bunch of random actions and then click the down-arrow next to the undo icon in the Excel title bar, I see a list of all recent changes. But typing CTRL-z only undoes the most recent change, further undo requests do nothing, and now the undo button and down arrow are greyed out. If I remove the module that contains the UDF, the multi-level undo functionality works as expected. Rebooting Excel and the PC have not changed this behavior. Can anyone shed some light on this behavior? Is there a way for me to retain multi-level undo functionality in a workbook with a UDF?
I am running a fully updated version of Excel 365 64-bit (16.0.13801.21072) on a PC with Windows 10 Pro 21H2 (build 19044.1415, Windows Feature Experience Pack 120.2212.3920.0), a Core i7-6700K CPU and 32GB of RAM.
4 Replies
egurs This is a well documented issue. Google for instance for "undo stack excel cleared after running VBA" and you'll find many resources discussing it, like the one in the link below.
Riny_van_EekelenThanks for the lead. Looks like the issue was the "Application.Volatile True" line in the VBA code of the UDF. Took that out and undo is working again, with no apparent impact on the functionality of the UDF.
- Presumably that means you are OK up to the point at which you force the function to evaluate.
There are techniques like running the code from a validation condition or a hyperlink where the normal restrictions of UDFs a bypassed but that is not my normal territory.
