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.

7 Comments

  1. David Wasserman says:

    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 says:

    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. ross says:

    I havn’t looked yet, but i wonder what the performance over head will be like.

    Very intresting JKP.

  4. jkpieterse says:

    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.

  5. 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)

  6. jkpieterse says:

    Thanks Thomas, well spotted!

  7. 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)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: