Generic Undo routine for VBA

As an excercise to get to grips with class modules I decided to try and write a generic routing one can plug into a project, to create an undo stack of actions performed by one’s macro. Of course I also added the code to do the actual undo.

Try this file and please send me your comments (or write them here)!

If I find this file is getting good enough, I’ll publish an article on my site to explain how things work.

Posted in Uncategorized

8 thoughts on “Generic Undo routine for VBA

  1. This is a very interesting workbook. I definitely want to explore it further. One bug that I noticed: If you press an action twice (for instance, Change Rectangle then Change Rectangle), then Undo All fails to make the change reverse.

  2. I wouldn’t call that a bug, the second press will do the same change, remembering the “settings” after the first change, thus undo will reset it back to the same state as after pressing once.

    There is a bug in there: If you have a cell with a formula and use the coding technique to chage this cell’s Value, an undo will undo the change but replace the formula that was in there with the value of that (previous) formula.

    Also, changing a formula or value on an empty cell fails.

  3. Hi All,

    Just wanted to let you know I updated it a bit to fix a few bugs:

    – When setting a cell’s Value, if the range used to contain a formula it is now properly restored

    – Error checking improved (a bit, still needs full error trapping applied but I’ve been too lazy )

    – Added a demo to change and undo formulas/values in a range of cells.

  4. Hello,
    thank you for your very useful work.
    I found an incorrect behaviour when I click the performed these actions:
    -perform a click on “change formula” button
    -reset manually some values in the modified celle
    -another click in “change formulas”
    I expected that the last click on button replaces my text in cells B17-D20 with formula stored in the sub but it didn’t work; I analyzed (a little) the code and found two little thinkgs to modify:

    1) in module clsExecAndUndo you could replace ResetUndo with this one that doesn’t need lCount

    Public Sub ResetUndo()
    On Error Resume Next
    While mcolUndoObjects.Count > 0
    mcolUndoObjects.Remove (1)
    Set mUndoObject = Nothing
    End Sub

    (ok this one is not so useful, just a little optimization)

    2) in module clsUndoObject you assigned mvNewValue = vValue which is not declared inside the sub (in fact it gives a compilation error) so I think the correct statement is mvNewValue = vnewValue. Full sub:

    Private Sub AddObject(oObj As Object, sProperty As String, vnewValue As Variant)
    Set mUndoObject = oObj
    msProperty = sProperty
    mvNewValue = vnewValue
    mvOldValue = GetOldValue
    End Sub

    This resolves the issue I described above.

    Thank you
    Thomas Imolesi (Italy)

  5. Hello Jan,
    the updated release doesn’t correct the behaviour described, because the ResetUndo sub still doesn’t work as expected.
    If you have time please try for yourself:
    – click “Change Formulas…”
    – manually change B17-D17 cells with some values
    – click again “Change Formulas…”
    you will see that it doesn’t work and it’s not a problem of undoing, the problem is that the action (to be eventually undone in the future) doesn’t execute at all.
    This modified ResetUndo corrects the problem:

    Public Sub ResetUndo()
    While mcolUndoObjects.Count > 0
    mcolUndoObjects.Remove (1)
    Set mUndoObject = Nothing
    End Sub

    Thank you again
    Thomas Imolesi (Italy)

  6. Hi ,
    I have written the following code and want to know whether I can write a macro to undo the code and erase what was copied in sheet 2 and sheet 3

    Please let me know


    Option Explicit

    Sub CommandButton1_Click()
    Dim Last_Row1 As Long, Last_Row2 As Long, last_Row3
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("methode") ' Change the name of your Sheet
    Set ws2 = Worksheets("visuel") ' Change the name of your Sheet
    Set ws3 = Worksheets("saisieSO") ' Change the name of your Sheet
    Last_Row1 = ws1.Range("A" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
    last_Row3 = ws3.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
    ws1.Range("A7:N7" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
    ws1.Range("A7:K7" & Last_Row1).Copy ws3.Range("A" & last_Row3)
    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.