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:

' CellTest Class - allows calling code to change the value of a
' cell and then change it back

'*****************************************************************
' Private Variables
'*****************************************************************
Private mCell As Range
Private vOrigFormula As Variant

'*****************************************************************
' Public Properties
'*****************************************************************
Public Property Get Address() As String
  Address = mCell.Address
End Property

Public Property Set Cell(ByRef rCell As Range)
  Set mCell = rCell
  vOrigFormula = rCell.Formula
End Property

'*****************************************************************
' Public Methods
'*****************************************************************
Public Sub Change(ByVal sText As String)
  mCell.Value = sText
End Sub

Public Sub ChangeBack()
  mCell.Formula = vOrigFormula
End Sub

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.

Next time, I’ll demonstrate how this looks in practice.

Version issues that plague me

I should begin by apologising once again for leaving the blog alone for a long time – work’s been crazy and it’s been tough finding the time to write anything. My main preoccupation at work recently has been building a data entry application over a fairly tight timeframe, and the process has thrown into stark contrast a lesson in Excel development I’ve had to learn the hard way.

The vast majority of the users in our business use Excel 97, while I use Excel 2007. There’s a lot of differences between the versions, both in terms of the Excel object model and VBA, so what this means for me is that if I build something for external use, it should be frequently and thoroughly tested in 97 throughout the process. I’ve had to have this lesson pounded into me several times by the cruel, hard fist of experience – there’s nothing like the sinking feeling you get when hours of work in a later version basically have to be tossed and an alternative approach found when you discover that your code won’t compile in 97. Or that a function you’re relying on to work a certain way just didn’t do that in 97.

There are some differences which are easy to work around. For example, if you use functions like Split, Join or Replace (which are not available in VBA in Excel 97), Daily Dose of Excel has published a set of VBA replacements which will run in 97: Split97, Join97 and Replace97. I’ve learnt to be aware of these sorts of issues. I’ve also learnt to avoid declaring variables as VBA enumeration members (such as vbMsgBoxResult or vbDayOfWeek) or defining custom enumerations. Handy as these may be, they will cause compile errors in 97. I usually declare these variables as Long, or substitute declaring a bunch of constants for a custom enumeration.

However, there’s always another surprise waiting to be discovered. In my current project I manage the user interface through a userform, and included in the project a user interface support class. This was a technique I learnt about in Professional Excel Development, where the authors suggest this as a way to separate the user interface layer from the business logic layer. Basically, the class acts as a layer of abstraction sitting between the two – it knows about how business logic is implemented, which means that the form doesn’t need to. The form lets the class object know when it needs new data, and the class object in turn calls the function from the business logic layer required to get the data, and passes it back to the form by way of a public event which the form is listening for. In my case I had a combobox cboJudicialOfficers which needs to be repopulated whenever a new judicial officer type is selected elsewhere in the form.

Here’s the code that initially went in the CUserInterface class:

Public Event GetJudicialOfficers(ByRef vJOList As Variant)

Public Sub JOTypeSelected(ByVal sType As String)
' Pick up change in Judicial Officer type from form
Dim vOfficers As Variant

' Get the list of JOs from the return
vOfficers = vUpdateJOList(sType)

    ' Pass the list back to the form
RaiseEvent GetJudicialOfficers(vOfficers)

End Sub

In the form, I created an instance of the class and an event handler to retrieve the data required:

Dim WithEvents mclsUIS as CUserInterface

Private Sub mclsUIS_GetJudicialOfficers(ByRef vJOList As Variant)
cboJudicialOfficers.List = vJOList
End Sub

When the new type is selected in the form, the class procedure JOTypeSelected is called, and the event handler listens for the list coming back in from the CUserInterface instance. I quite like the neat separation this affords the application – the form never has to query the business logic layer directly, so its own implementation is not at all tied to the implementation of the business logic. Of course, the only problem with this approach is that Excel 97 won’t have a bar of it. It’s perfectly ok to create event handlers for built-in objects like worksheets, pivot tables or the application itself, but VBA in 97 will not understand when a user-defined object starts using terms like Public Event and RaiseEvent. This left me with the option of getting the form to call the required functions directly, which I don’t like but at least it will compile (and run).

Type or Class?

I’ve been kept quite busy recently building an application for work. It’s a fairly standard task – take a number of regularly (monthly) populated workbooks from offices around the country and consolidate the data in them at a regional and national level. Aside from a little fluffing around with validation at the beginning and end of the process, the flow of the consolidation procedure runs like this:

Locate workbooks to be processed, add them to a collection
For each workbook in the collection:
	Open it
	Read the data in the workbook into memory
	Close the workbook

Open consolidation workbook
For each block of data collected:
	write into the appropriate place in the workbook

Save and close

Now that’s all fairly straightforward. The issue I found myself faced with a couple of days ago relates to keeping the data from each of the workbooks in memory. There’s a fair variety of data in them, so I elected to use a user-defined type (UDT) to hold it:

Public Type uCheckSheetData
    sLocation As String
    dtMonth As Date
    vErrors As Variant
    lNumCases As Long
    sComment As String
End Type

I then formed a collection which would hold the UDTs – or so I thought. For those of you who’ve tried this before, you’ve probably also seen the rather obscure error message:

Compile error:

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

After a fair amount of head-scratching and wtf?ing I finally decided that Google is my friend and found a solution: UDTs can’t be added to collections – use a class. Classes in VBA may not be all that they are in other languages, but they do represent a more object-oriented approach, and can accomplish all and more that a UDT can. They just represent a bit more work to code. For example, here’s how I replaced the UDT uCheckSheetData with the class CCheckSheet:

Option Explicit
' Replaces public type as class can be added to 
' collections/dictionaries

Private sLocation As String
Private dtMonth As Date
Private vErrors As Variant
Private lNumCases As Long
Private sComment As String

Public Property Get Location() As String
    Location = sLocation
End Property

Public Property Let Location(ByVal strIn As String)
    sLocation = strIn
End Property

Public Property Get Month() As Date
    Month = dtMonth
End Property

Public Property Let Month(ByVal dtIn As Date)
    dtMonth = dtIn
End Property

Public Property Get Errors() As Variant
    Errors = vErrors
End Property

Public Property Let Errors(ByVal vArr As Variant)
    vErrors = vArr
End Property

Public Property Get NumCases() As Long
    NumCases = lNumCases
End Property

Public Property Let NumCases(ByVal lIn As Long)
    lNumCases = lIn
End Property

Public Property Get Comment() As String
    Comment = sComment
End Property

Public Property Let Comment(ByVal strIn As String)
    sComment = strIn
End Property

Now it might be argued that a lot of that code is not really necessary. I could have defined a set of public variables in the class and just exposed them that way, rather than exposing public properties with Get and Let. However, one benefit of writing these procedures is that a lot of other code can be included in them, for instance to validate the data passed in to the class members – something that has to be done outside of a UDT. There’s also the possibility to include class methods and trap events and a whole lot of other stuff, which in this case is not really required. Chip Pearson has an excellent introductory discussion of classes in VBA at Classes in VBA. Which, by the way, includes a prominent warning not to make classes auto-instancing (using Dim as New syntax when declaring a class variable, otherwise known as a really bad idea). There’s also a great chapter in the book Professional Excel Development covering classes, which also warns against auto-instancing (did I mention that was a really bad idea?).

Finally, there’s a rather interesting discussion here about why VB won’t let you add a UDT to a collection.