Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Undo With Excel VBA > Class Modules (2)
Deze pagina in het Nederlands

Creating An Undo Handler To Undo Changes Done By Excel VBA

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.