Building undo into an Excel VBA macro

Question

Excel macros don't seem to allow the use of "undo" after running them. Is there any way to bake undo functionality into a VBA macro in Excel?

Answer 1

I always save immediately before running my macros (during testing at least) then, if everything goes pear-shaped, I can just exit without saving and re-open it.

Baking it into the actual macro, you'll have to basically record the old state of everything that changes (cell contents, formulae, formatting and so on) in a list then have an undo macro which plays back that list in reverse order.

For example if your macro changes a cell C22 contents from "3" to "7" and formatting from "general" to "number, 2 decimals), your list would be:

C22 value  3
C22 format general

Playing this back in reverse order (with another macro) would revert the changes.

You could have a whole extra sheet to hold the macro undo information such as:

Step   Cell   Type    Value
----   ----   -----   -------
   1   C22    value         3
       C22    format  general
   2...

It wouldn't integrate very well with the 'real' undo unfortunately, but I don't think there's any way around that.

Answer 2

Excel VBA has the Application.OnUndo function to handle this:

Public Sub DoSomething

    ... do stuff here

    Application.OnUndo "Undo something", "UnDoSomething"
End Sub

Public Sub UnDoSomething

    ... reverse the action here

End Sub

Answer 3

My thought is pretty simple, as the first line in your macro save a copy in a backup directory then close that workbook and reopen the original. If you don't like the results of your macro run, pull up the saved workbook. Keep it simple eh?