An Undo Stack in VBA

I’m working on an Excel add-in which will allow a user to perform operations on cells. I’d also like to allow the user to ‘undo’ these operations. Excel itself, of course, does have an Undo button (shortcut Ctrl + Z), but this works only to undo changes made in the user interface, and is not going to help for changes made by VBA. This means the add-in will need to remember each operation performed and have them ready to supply for un-doing should the user want to do so.

Many people have created their own general solutions for undoing changes made in VBA – for instance, here’s one from Jan Karel Pieterse (who also created the hugely useful Name Manager add-in): Undo With Excel VBA. However I decided not to use a solution like this – it’s quite a lot of code to add to a project, and it is very general.

In this case I’m happy to write a more specific solution, which copes with undoing a specific action, by simply delegating the work of undoing the action to the object which did the action in the first place. Here’s an example:

So what about storing up a list of operations for undoing? This is ideally suited to a stack. Stacks are neat data structures – they don’t do much, but they do it well and they’re really easy to code. Here’s one I picked up from the VBA Developer’s Handbook and tweaked a little to suit. There are two classes, StackItem and Stack. StackItem simply stores a value and a reference to another StackItem object – the next item on the stack.

' StackItem class holds a reference to the object it refers to and
' a reference to the next item in the stack
Public Value As Variant
Public NextItem As StackItem

Stack stores a reference to the item on the top of the stack, and exposes the functions Push (to push a new item on to the top) and Pop (to remove the top item and return a reference to it). Properties IsEmpty and Peek tell you if there are any items in the stack, and what the value of the top item is (without removing it), respectively. There’s a very nice visual explanation of how this looks here.

' Stack holds a reference to the top item in the stack data structure
' and manages the adding and removing of stack items
Private siTop As StackItem
'*****************************************************************
' Public Properties
'*****************************************************************
Public Property Get IsEmpty() As Boolean
IsEmpty = siTop Is Nothing
End Property
Public Property Get Peek() As Variant
If IsObject(siTop.Value) Then
Set Peek = siTop.Value
Else
Peek = siTop.Value
End If
End Property
'*****************************************************************
' Public Methods
'*****************************************************************
Public Function Push(ByRef varIn As Variant) As Boolean
Dim siNew As StackItem
On Error GoTo PushError
Set siNew = New StackItem
If IsObject(varIn) Then
Set siNew.Value = varIn
Else
siNew.Value = varIn
End If
Set siNew.NextItem = siTop
Set siTop = siNew
Push = True
Exit Function
PushError:
Push = False
End Function
Public Function Pop() As Variant
If IsObject(siTop.Value) Then
Set Pop = siTop.Value
Else
Pop = siTop.Value
End If
Set siTop = siTop.NextItem
End Function

So now to store an actions for undoing, I just need to push the object which performed it onto the undo stack. Every time I wish to actually undo an action, I just pop the object reference off, and instruct it to undo.