Building Undo Into an Excel VBA Macro

Question

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

score 23 · Accepted Answer · answered Dec 04 '08 at 00:39

23

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

answered Dec 04 '08 at 00:39

e.James

116,942
41
177
214

6

Although this will help to undo the actions of the first sub, unfortunately you still lose the "history" of undo actions that may have been available before running the sub. If anyone knows how to solve that issue it would be a real boon. – Excel Developers Feb 12 '14 at 13:23
@ExcelDevelopers you mean like [here](http://www.jkp-ads.com/Articles/UndoWithVBA00.asp) – Reafidy Oct 28 '17 at 05:54
@Reafidy, not really as that solution only preserves undo history for changes made by VBA and not changes made by the user. – Gregor y Jun 26 '18 at 18:23
Changes made by the user can be undone using ctrl-z. – Reafidy Jul 01 '18 at 05:05
1

@Reafidy: not after the VBA code has made its own changes to the workbook state. – pstraton Apr 27 '21 at 22:19

score 7 · Answer 2 · answered Dec 23 '08 at 18:01

7

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?

answered Dec 23 '08 at 18:01

CABecker

161
1
10

Or save before run and if you are not happy close the file without saving (prereq autosave is deactivated.) – Thierry Dalon Aug 04 '23 at 14:42

paxdiablo · Answer 3 · 2008-12-04T00:32:47.210

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.

Building Undo Into an Excel VBA Macro

3 Answers3

Linked

Related