Save undo stack during macro run

Question

I am wondering if there is a way to save ability to undo actions after macro has been run. I do not care about results of macro - just need to undo actions that were done by user before macro.

Background: I have a macro on the worksheet_change event that logs who and when made the change on this worksheet. I do not want it to restrict user's ability to undo his/her actions.

Answer 1

There is no easy way to do this, but it's possible. The approach to this is to create three macros, and use some global variables to save state:

1. MyMacro
2. MyStateSavingMacro
3. MyStateRevertingMacro

E.g. My macro changes Cells in Range A1:A10 of the active sheet. So, whenever the code to run my macro is called, it executes

Sub MyMacro()       
    Call MyStateSavingMacro() 
    ' Copies contents and formulae in range A1:A10 to a global data object

    '... Code for MyMacro goes here
    '
    '................

    Call Application.OnUndo("Undo MyMacro", "MyStateRevertingMacro")
    'This puts MyStateRevertingMacro() in the Undo queue
    'So pressing ctrl-Z invokes code in that procedure
End Sub


Sub MyStateSavingMacro()
    ' Code to copy into global data structures anything you might change
End Sub

Sub MyStateRevertingMacro
    ' Code to copy onto the spreadsheet the original state stored in the global variables
End Sub

So there it is. It's not pretty, but can be done. Ref: http://msdn.microsoft.com/en-us/library/office/ff194135%28v=office.15%29.aspx

Edit: To preserve the Undo queue prior to your MyMacro being run, the inelegant solution would be to create a chain of 4-5 MyStateRevertingMacro_1, _2, etc. where you can apply the information from your Worksheet_Change logging system and then chain-up the Application.OnUndo in each of those, so Application.OnUndo for each of those Reverting Macros would refer the previous state reversion code.