Excel VBA "UNDO" command

[reneuend]

I've read that the "UNDO' command is disabled in Excel VBA and have tested it myself, but what is actually going on behind the scenes that causes the "UNDO" not to work?

Thanks!

 

[Ruddles]

I'm not sure what you mean. If you create a procedure which just does Application.Undo, then type something into the worksheet and run the procedure, it Undoes.

Or at least it does here.

Do you mean that Undo doesn't work after running a VBA procedure?

 

[reneuend]

Thanks for the reply.

In short, "yes". After VBA code has been executed, I need to have the ability to "undo" it manually.

I have several subroutines that work together after the user clicks on a "button" to execute the code. I was asked if there was a way to "undo" after the code has been executed.

It sounds like at least there is a way to "undo" at a routine level? What is the scope of the "undo" if I run a series of commands and subroutines?

 

[Ruddles]

I believe Application.Undo only undoes your last interaction with the worksheet and not anything VBA does.

Once you've run any VBA the ability to undo is lost. If you need to have it enabled then... I suppose your VBA would have to save the workbook somehow and you'd have to write an 'undo macro' which would roll back to the saved workbook.

 

[reneuend]

Thanks Ruddles. I suspected that was the case.

What I really would like to know is "why" the UNDO function doesn't work with Excel VBA? Why was it designed this way?

I read that the "UNDO stack" is cleared when VBA is executed, but this doesn't explain why changes to Excel can't be saved during VBA operations so it can then be restored after the VBA is done running.

I haven't been able to find any explanation as to why the UNDO doesn't work after VBA has been used. It's just kind of a mystery to me.

 

[Ruddles]

Yes, I sort of see what you mean...

... but imagine a situation where you type 100 in cell A1 and 999 in cell A2. Then you run a VBA routine which does a whole load of things, one of which is to change cell A1 to 101.

Then you click Undo once.

In an ideal world, what would you want Excel to do?

 

[xenou]

... but imagine a situation where you type 100 in cell A1 and 999 in cell A2. Then you run a VBA routine which does a whole load of things, one of which is to change cell A1 to 101.

I was thinking along the same lines ... a hypothetical vba "undo" would have changes in excel worksheets to keep track of, but also (perhaps) changes in the VBA environment as well ("state", such as the values of variables). It could be rather messy as you get into more powerful and complicated vba-enabled applications. And I think apropos of Ruddles' comment - you might not want your users hitting "undo" to change the value of one cell when the process will also "undo" an unknown number of other things as well (which the user may not even be aware has happened). It's been a few years since I've thought about this problem - I think as your programming skills increase you just become more adept at controlling program flow and you run into fewer and fewer cases where a macro needs to be "undone".

Though some googling suggests Word does keep track of its Undo stack even after a vba routine runs, unlike Excel.

It's not a bad idea to save the file before major changes are run via code (if the process has a risk of going astray or changes needing to be discarded - you can close and re-open the file). Also a message box can give the user a last chance to say yes or no ("are you sure..."). My own habits are well established when I'm not sure what will happen - hit save, then hit run macro.

 

[reneuend]

All points well-written. I automate Excel Models that engineers use for testing all types of theoritical conditions. They run their calculations over and over. I was requested to see if there was a way to "undo" a calculation. My answer was that I could save the state before the calculation and then allow the engineer to restore after the operation if they wanted too. But, then I was asked if Excel "disabled" (their word) the undo feature in all cased involving VBA. My short answer to them was "yes". The only way I know of is for the programmer to save the state before running the calculation.

I also read that MS Word has the UNDO function in it's VBA, but Excel is much more complicated than Word, just as you stated Xenou. I also read that MS was possibly looking at ways to allow UNDO in Excel VBA, including a patent submitted back in 2004.

I appreciate everyone's time for answering.