Generic Undo routine for VBA

By in Uncategorized on .

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.

8 thoughts on “Generic Undo routine for VBA

  1. David Wasserman

    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. jkpieterse Post author

    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. jkpieterse Post author

    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. Thomas Imolesi

    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)
    Wend
    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. Thomas Imolesi

    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)
    Wend
    Set mUndoObject = Nothing
    End Sub

    Thank you again
    Thomas Imolesi (Italy)

  6. Bruno

    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

    Thanks

    Bruno
    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

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax