Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
Project goals for Text Tools
The first step in designing a utility is to envision exactly how you want the utility to work. Here’s
my original plan, stated in the form of a dozen goals:
h Its main features will be those listed at the beginning of this section.
h It will enable the user to specify that the preceding types of changes work with nontext
cells as well as with text cells.
h It will have the same look and feel of other Excel commands. In other words, it will have a
dialog box that looks like Excel’s dialog boxes.
h It will be in the form of an add-in and will also be accessible from the Ribbon.
h It will operate with the current selection of cells (including multiple selections), and it will
enable the user to modify the range selection while the dialog box is displayed.
h It will remember the last operation used and display those settings the next time the
dialog box is invoked.
h It will have no effect on cells that contain formulas.
h It will be fast and efficient. For example, if the user selects an entire column, the utility
should ignore the empty cells in the column.
h It will use a nonmodal dialog box, so the user can keep the dialog box on-screen and
ready to use.
h It will be compact in size so that it doesn’t hide too much of the worksheet.
h It will enable the user to undo the changes.
h Comprehensive help will be available.
The Text Tools workbook
The Text Tools utility is an XLAM add-in file. During development, I worked with the file as a
macro-enabled XLSM workbook. When I was satisfied that all was working properly, I saved the
workbook as an add-in.
The Text Tools workbook consists of the following components:
h One worksheet: Every workbook (including add-ins) must have at least one worksheet. I
take advantage of this fact and use this worksheet to store information used in the Undo
procedure (see “Implementing Undo,” later in this chapter).
h One VBA module: This module contains public variable and constant declarations, the
code to display the UserForm, and the code to handle the undo procedure.
h One UserForm: This contains the dialog box. The code that does the actual text
manipulation work is stored in the code module for the UserForm.