Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
SaveSetting APPNAME, “Settings”, “RemoveSpacesIndex”, UserChoices(7)
SaveSetting APPNAME, “Settings”, “RemoveCharactersIndex”, UserChoices(8)
SaveSetting APPNAME, “Settings”, “cbSkipNonText”, cbSkipNonText.Value * -1
Unfortunately, Excel doesn’t provide a direct way to undo an operation performed using VBA.
Undoing a VBA macro is possible, but it takes quite a bit of work. And, unlike Excel’s Undo
feature, the undo technique used in the Text Tools utility is a single level. In other words, the user
can undo only the most recent operation. Refer to the sidebar, “Undoing a VBA procedure,” for
additional information about using Undo with your applications.
The Text Tools utility implements Undo by saving the original data in a worksheet. If the user
undoes the operation, that data is then copied back to the user’s workbook.
In the Text Tools utility, recall that the Module1 VBA module declared two public variables for
Public UndoRange As Range
Public UserSelection As Range
Before modifying any data, the ApplyButton_Click procedure calls the SaveForUndo
procedure. The procedure starts with three statements:
Set UserSelection = Selection
Set UndoRange = WorkRange
The UserSelection object variable saves the user’s current selection so that you can reselect
it after the undo operation. WorkRange is a Range object that’s returned by the CreateWork
Range function. The range consists of the non-empty and nonformula cells in the user’s
selection. The preceding third statement erases any existing saved data from the worksheet.
Next, the following loop is executed:
For Each RngArea In WorkRange.Areas
(RngArea.Address).Formula = RngArea.Formula
This code loops through each area of the WorkRange and stores the data in the worksheet. (If
the WorkRange consists of a contiguous range of cells, it will contain only one area.)