Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
Undoing a VBA procedure
Computer users have become accustomed to being able to undo an operation. You can undo
almost every operation that you perform in Excel. Even better, beginning with Excel 2007,
Microsoft increased the number of undo levels from 16 to 100.
If you program in VBA, you may have wondered whether you can undo the effects of a
procedure. Although the answer is yes, the qualified answer is it’s not always easy .
Making the effects of your VBA procedures undoable isn’t automatic. Your procedure needs to
store the previous state so that it can be restored if the user chooses the Undo command (which
is located in the Quick Access toolbar). How you store the previous state can vary depending on
what the procedure does. You can save the old information in a worksheet or in an array. In
extreme cases, you may need to save an entire worksheet. If your procedure modifies a range,
for example, you need to save only the contents of that range.
Also, keep in mind that executing a VBA Sub procedure wipes out Excel’s undo stack. In other
words, after you run a macro, it’s impossible to undo any previous operations.
The Application object contains an OnUndo method, which lets the programmer specify text
to appear on the Undo drop-down list and a procedure to execute if the user chooses the Undo
command. For example, the following statement causes the Undo drop-down list to display
Undo my cool macro . If the user chooses Undo➜Undo My Cool Macro, the UndoMyMacro
procedure is executed:
Application.OnUndo “Undo my cool macro”, “UndoMyMacro”
The companion CD-ROM contains a simpler example that demonstrates how to enable
the Undo command after a VBA procedure is executed. This example, named simple
undo demo.xlsm , stores the data in an array rather than a worksheet. The array is
made up of a custom data type that includes the value and address of each cell.
Displaying the Help file
I created a simple compiled HTML Help file named texttools.chm for this utility. Clicking the
HelpButton on the UserForm executes this procedure:
Private Sub HelpButton_Click()
Application.Help (ThisWorkbook.Path & “\” & “texttools.chm”, 0)
Figure 16-7 shows one of the Help screens.