I used to believe that every Excel macro would clear the undo stack. But, as you will see today, sometimes a macro can run and the Undo stack remains in place.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1904.
Every Macro Does Not Clear UnDo Stack!
This is kind of a follow up to yesterday's podcast, episode 1903, where I showed that we have this event handler macro, that, when we would change something from No to Yes in the Paid column, that record was automatically being written to the data column.
And I was going to record an addendum to that podcast yesterday, warning that the problem with these event handler macros, let me show you the event handler macro, is that, you know, traditionally the belief is that every time we run a macro, we clear the undo stack, all right.
So that means, that every time that something changes on this worksheet, this macro runs and then therefore we lose undo.
And so to demonstrate that, I was going to come here and delete some stuff and then come here and overwrite some stuff.
And then show you that when I press Ctrl+Z, it would not be able to undo.
But here, watch, I'm going to Ctrl+Z and undoes, and Ctrl+Z and undoes again, what the heck is up with this?
Were those two changes I made not enough to cause the event handler to fire?
So we'll come over here and let's just add a message box: Msgbox “Yep, I am running!”, all right.
And then we'll come back here, just choose some records, press Delete and sure enough, it is running the macro.
But Ctrl+Z, we still have “undo”.
What's up with that?
So, as I dug in and did some more research, it turns out that the rule is: the undo stack is cleared when your macro writes something to the worksheet.
If the macro does some analysis and doesn't write to the worksheet, then the undo stack is not cleared.
So in this particular macro, it is only doing something, if the change happened in column 7.
And if the change happened to be Yes, so if we had changed any other column, other than column G, and if we wrote anything other than a lowercase or capital Y, this macro isn't going to make any changes and therefore the undo stack stays intact.
Now, I had been under the wrong impression for a long time, that any macro would clear the undo stack.
But it turns out, if you have a macro that just does some analysis and does not change the worksheet, the undo stack remains intact.
Alright, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1904.
Every Macro Does Not Clear UnDo Stack!
This is kind of a follow up to yesterday's podcast, episode 1903, where I showed that we have this event handler macro, that, when we would change something from No to Yes in the Paid column, that record was automatically being written to the data column.
And I was going to record an addendum to that podcast yesterday, warning that the problem with these event handler macros, let me show you the event handler macro, is that, you know, traditionally the belief is that every time we run a macro, we clear the undo stack, all right.
So that means, that every time that something changes on this worksheet, this macro runs and then therefore we lose undo.
And so to demonstrate that, I was going to come here and delete some stuff and then come here and overwrite some stuff.
And then show you that when I press Ctrl+Z, it would not be able to undo.
But here, watch, I'm going to Ctrl+Z and undoes, and Ctrl+Z and undoes again, what the heck is up with this?
Were those two changes I made not enough to cause the event handler to fire?
So we'll come over here and let's just add a message box: Msgbox “Yep, I am running!”, all right.
And then we'll come back here, just choose some records, press Delete and sure enough, it is running the macro.
But Ctrl+Z, we still have “undo”.
What's up with that?
So, as I dug in and did some more research, it turns out that the rule is: the undo stack is cleared when your macro writes something to the worksheet.
If the macro does some analysis and doesn't write to the worksheet, then the undo stack is not cleared.
So in this particular macro, it is only doing something, if the change happened in column 7.
And if the change happened to be Yes, so if we had changed any other column, other than column G, and if we wrote anything other than a lowercase or capital Y, this macro isn't going to make any changes and therefore the undo stack stays intact.
Now, I had been under the wrong impression for a long time, that any macro would clear the undo stack.
But it turns out, if you have a macro that just does some analysis and does not change the worksheet, the undo stack remains intact.
Alright, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
