From version 2.10 on, changes that the XL Toolbox makes to your workbooks can be undone. The following outlines some noteworthy characteristics of the way Excel® handles Undo functionality and explains the limitations of the XL Toolbox Undo that follow from them.
Excel® saves your most recent edits in an “Undo stack” so that you can undo them. The capability to perform Undo has been improved from Excel® 2003 to Excel® 2007:
| Excel® 2003 | Excel® 2007 | |
|---|---|---|
| Undo steps (stack size): | 16 | 100 |
| Undo possible after saving a workbook: | No | Yes |
| Undo steps for Addins: | 1 | |
| Addin actions erase Undo stack: | Yes | |
Not only can Excel® 2007 remember many more editing steps than its predecessor; it also remembers those steps when you save a workbook.
Importantly, any changes made by an Addin will always clear the Undo stack. This is not a problem that is specific for the XL Toolbox; try any other addin, e.g. the Analysis Toolpak, and you will see that Excel® “forgets” your previous edits when you use the Addin.
Changes made by addins are not automatically remembered by Excel®; programmers have to go out of their way to implement Undo functionality for their addins. Even when an Undo feature has been implemented for a specific addin, Excel® itself will only remember the most recent Addin action.
The positive exception: Special Paste
When you use any of the Special Paste functions of the XL Toolbox, the Undo stack will not be cleared. Thus, you can repeatedly use those functions without risking to irreversibly lose data, e.g. by accidentally pasting data in the wrong place.
Technically, this is accomplished by remote controlling Excel® during the Special Paste operation: The XL Toolbox actually simulates a keyboard sequence that will activate standard menu commands for pasting.
Technical information about the XL Toolbox Undo function
Programming Undo capability for an Addin is not trivial; Excel® provides almost no framework for this.
The XL Toolbox accomplishes Undo by saving the relevant data in a hidden workbook. Only the data that is immediately necessary for an Undo operation is saved, so that the footprint on the computer’s memory is as small as possible.
The hidden workbook, called “XLTBUNDO”, is saved in the user’s TEMP folder and gets deleted when the application is closed. Occasionally, when Excel® automatically restarts after a crash, the user may see this workbook. It can be dismissed without impairing Undo (the XL Toolbox always makes sure that it has a valid hidden Undo workbook to work with).