Creating An Undo Handler To Undo Changes Done By Excel VBA

Pages in this article

  1. Class Modules (1)
  2. Class Modules (2)
  3. Implementation
  4. Conclusion

Class modules (2)


OK, another big piece of code (explanation below the code)...

' Module    : clsExecAndUndo
' Company   : JKP Application Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 31-8-2005
' Purpose   : Class module, stores the objects processed and
'             handles the exection of the commands
' Copyright : This code is free for you to use for applications
'             for personal use.
'             It is not allowed to use this for a commercial program,
'             unless you have my consent.
'             If you want to include this code in freeware, make sure you add :

' This code originates from    : Jan Karel Pieterse
' Company                      : JKP Application Development Services (c) 2005
Option Explicit

Private mcolUndoObjects As Collection
Private mUndoObject As clsUndoObject

Public Function AddAndProcessObject(oObj As Object, sProperty As String, vValue As Variant) As Boolean
    Set mUndoObject = New clsUndoObject
    With mUndoObject
        Set .ObjectToChange = oObj
        .NewValue = vValue
        .PropertyToChange = sProperty
        mcolUndoObjects.Add mUndoObject
        If .ExecuteCommand = True Then
            AddAndProcessObject = True
            AddAndProcessObject = False
        End If
    End With
End Function

Private Sub Class_Initialize()
    Set mcolUndoObjects = New Collection
End Sub

Private Sub Class_Terminate()
End Sub

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

Public Sub UndoAll()
    Dim lCount As Long
    '    On Error Resume Next
    For lCount = mcolUndoObjects.Count To 1 Step -1
        Set mUndoObject = mcolUndoObjects(lCount)
        Set mUndoObject = Nothing
End Sub

Public Sub UndoLast()
    Dim lCount As Long
    '    On Error Resume Next
    If mcolUndoObjects.Count >= 1 Then
        Set mUndoObject = mcolUndoObjects(mcolUndoObjects.Count)
        mcolUndoObjects.Remove mcolUndoObjects.Count
        Set mUndoObject = Nothing
    End If
End Sub

Public Function UndoCount() As Long
    UndoCount = mcolUndoObjects.Count
End Function

Short explanation of the subs and functions shown above

This is the entry routine of this class. It receives the object and its properties to be modified and adds a member to the collection of changed objects (members of clsUndoObject), so changes can be undone later on. It calls the routines needed to store previous settings and execute the change.
Clears the undo stack of this utility (run when instances of the class are destroyed)
Method to call to undo all operations stored in the collection of changed objects. This procedure is called when control-z is pressed or Edit, undo is selected.
Only undoes the last change. This needs to be tied to a button or a shortcut-key programmatically in your application.
Returns the number of objects stored in the collection

That sums up the two central (class-) modules of this example. The next page will describe implementation of the technique.