Undoing A VBA Subroutine
Computer users are accustomed to the ability to "undo" an operation. Almost every operation you perform in Excel can be undone. If you program in VBA, you may have wondered if it's possible to undo the effects of a subroutine. The answer is yes. The qualified answer is it's not always easy.
Making the effects of your subroutines undoable isn't automatic. Your subroutine will need to store the previous state so it can be restored if the user choose the Edit Undo command. How you do this will vary, depending on what the subroutine does. In extreme cases, you might need to save an entire worksheet. If your subroutine modifies a range, for example, you need only save the contents of that range.
The code below demonstrates how to enable the Edit Undo command after a subroutine is executed. The subroutine itself is very simple: it simply inserts a 0 into every cell in the current range selection. The bulk of the code is used to save the contents of the current selection.
Trying it out
To try out this example code:
- Copy the code to an empty VBA module.
- Enter some data into a worksheet range.
- Select the range and execute the ZeroRange subroutine. The cells will be replaced with zeros.
- Select the Edit Undo command. The original contents of the selection will be restored.
How it works
The OldSelection array stores the cell address and the cell contents (using a custom data type). Notice that this array is declared as a Public variable so it's available to all subroutines. The last statement in the ZeroRange subroutine specifies the text to display in the Undo menu, and the subroutine to call if this command is selected. The UndoZero routine loops through the OldSelection array and restores the values to their appropriate cells. Notice that I also store the workbook and worksheet -- which ensures that the correct cells will be restored even if the user switches out of the original worksheet.
The Undo example
'Custom data type for undoing Type SaveRange Val As Variant Addr As String End Type ' Stores info about current selection Public OldWorkbook As Workbook Public OldSheet As Worksheet Public OldSelection() As SaveRange Sub ZeroRange() ' Inserts zero into all selected cells ' Abort if a range isn't selected If TypeName(Selection) <> "Range" Then Exit Sub ' The next block of statements ' Save the current values for undoing ReDim OldSelection(Selection.Count) Set OldWorkbook = ActiveWorkbook Set OldSheet = ActiveSheet i = 0 For Each cell In Selection i = i + 1 OldSelection(i).Addr = cell.Address OldSelection(i).Val = cell.Formula Next cell ' Insert 0 into current selection Application.ScreenUpdating = False Selection.Value = 0 ' Specify the Undo Sub Application.OnUndo "Undo the ZeroRange macro", "UndoZero" End Sub Sub UndoZero() ' Undoes the effect of the ZeroRange sub ' Tell user if a problem occurs On Error GoTo Problem Application.ScreenUpdating = False ' Make sure the correct workbook and sheet are active OldWorkbook.Activate OldSheet.Activate ' Restore the saved information For i = 1 To UBound(OldSelection) Range(OldSelection(i).Addr).Formula = OldSelection(i).Val Next i Exit Sub ' Error handler Problem: MsgBox "Can't undo" End Sub
Other examples of Undo
If you've purchased the source code to Power Utility Pak, you can examine these utilities for other, more complex, examples of using undo.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks