Text Tools: The Anatomy of a Utility | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

In this section, I describe an Excel utility that I developed (and that is part of my Power Utility Pak add-in). The Text Tools utility enables the user to manipulate text in a selected range of cells . Specifically, this utility enables the user to do the following:

Change the case of the text (uppercase, lowercase, proper case, sentence case, or toggle case).
Add characters to the text (at the beginning, to the end, or at a specific character position).
Remove characters from the text (from the beginning, from the end, or from a specific position within the string).
Remove spaces from the text (either all spaces or excess spaces).
Delete characters from text (non-printing characters, alphabetic characters, non-numeric characters, non-alphabetic characters, or numeric characters).

Figure 16-1 shows the Text Tools Utility dialog box.

Figure 16-1: Use the Text Tools utility to change the case of selected text.

CD-ROM

The Text Tools utility is available on the CD-ROM that accompanies this book. This is a standalone version of the tool that is included with the Power Utility Pak. The file, named text tools.xlam , is a standard Excel add-in. When installed, it adds a new command to the Ribbon: Home Utilities Text Tools. The VBA project is not protected with a password, so you can examine the code to see how it works.

Background for Text Tools

Excel has many worksheet functions that can manipulate text strings in useful ways. For example, you can uppercase the text in a cell (UPPER), add characters to text (CONCATENATE), remove spaces (TRIM), and so on. But to perform any of these operations, you need to write formulas, copy them, convert the formulas to values, and then paste the values over the original text. In other words, Excel doesn't make it particularly easy to modify text. Wouldn't it be nice if Excel had some text manipulation tools that didn't require formulas?

By the way, many good utility ideas come from statements that begin: "Wouldn't it be nice if ?"

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 ten goals:

Its main features will be those listed at the beginning of this section.
It will enable the user to specify that the preceding types of changes work with nontext cells as well as with text cells.
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.
It will be in the form of an add-in and will also be accessible from the Ribbon.
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.
It will remember the last operation used and display those settings the next time the dialog box is invoked.
It will have no effect on cells that contain formulas.
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.
It will enable the user to undo the changes.
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 :

One worksheet: Every workbook must have at least one worksheet. I take advantage of this fact and use this worksheet to handle the undo procedure (see "Implementing Undo," later in this chapter).
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.
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.

Installing an Add-In

To install an add-in, including the text tools.xlam add-in, follow these steps:

Select Office Excel Options.
In the Excel Options dialog box, click the Add-Ins tab.
In the drop-down list labeled Manage, select Excel Add-Ins and then click Go to display the Add-Ins dialog box.
If the add-in that you want to install is listed in the Add-Ins Available list, place a check mark next to the item. If the add-in is not listed, click Browse to locate the XLAM or XLA add-in file.
Click OK, and the add-in will be installed. It will remain installed until you deselect it from the list.

Note	The file also contains some manual modifications that I made in order to get the command to display on the Ribbon. See "Adding the RibbonX code," later in this chapter. Unfortunately, it's not possible to modify Excel's Ribbon using only VBA.

How the Text Tools utility works

The Text Tools add-in contains some RibbonX code that creates a new item in the Ribbon: Home Utilities Text Tools. Selecting this item executes the StartTextTools procedure, which calls the ShowTextToolsDialog procedure.

CROSS-REFERENCE

To find out why this utility requests both the StartTextTools procedure and the ShowTextToolsDialog procedure, see "Adding the RibbonX code," later in this chapter.

The user can specify various text modifications and click the Apply button to perform them. The changes are visible in the worksheet, and the dialog box remains displayed. Each operation can be undone, or the user can perform additional text modifications. Clicking the Help button displays a help window, and clicking the Close button dismisses the dialog box. Note that this is a modeless dialog box. In other words, you can keep working in Excel while the dialog box is displayed. In that sense, it's similar to a toolbar.

The UserForm for the Text Tools utility

When I create a utility, I usually begin by designing the user interface. In this case, it's the dialog box that's displayed to the user. Creating the dialog box forces me to think through the project one more time.

Figure 16-2 shows the UserForm for the Text Tools utility.

Figure 16-2: The UserForm for the Text Tools utility.

Notice that the controls on this UserForm are laid out differently from how they actually appear to the user. That's because some options use different controls, and the positioning of the controls is handled dynamically in the code. The controls are listed and described next.

The Operation ComboBox: This always appears on the left, and it is used to select the operation to be performed.
Proc1 ComboBox: Most of the text manipulation options use this ComboBox to further specify the operation.
Proc2 ComboBox: Two of the text manipulation options use this ComboBox to specify the operation even further. Specifically, this additional ComboBox is used by Add Text and Remove by Position.
Check box: The Skip Non-Text Cells check box is an option relevant to some of the operations.
Help button: Clicking this CommandButton displays help.
Close button: Clicking this CommandButton unloads the UserForm.
Apply button: Clicking this CommandButton applies the selected text manipulation option.
Progress bar: This consists of a Label control inside a Frame control.
Text box: This text box is used for the Add Text option.

Figure 16-3 shows how the UserForm looks for each of the five operations. Notice that the configuration of the controls varies, depending on which option is selected.

Figure 16-3: The UserForm layout changes for each operation.

Note

You'll notice that this utility violates one of the design rules that I outline earlier in this chapter (see "What Makes a Good Utility?"). Unlike most of Excel's built-in dialog boxes, the Text Tools utility dialog box does not have an OK or a Cancel button, and clicking the Apply button does not dismiss the dialog box. The original version of Text Tools had an OK button and was designed so that clicking OK performed the task and closed the dialog box. User feedback, however, convinced me to change the design. Many people, it turns out, like to perform several different manipulations at one time. Therefore, I changed the utility to accommodate user preferences.

The Module1 VBA module

The Module1 VBA module contains the declarations, a simple procedure that kicks off the utility, and a procedure that handles the undo operation.

DECLARATIONS IN THE MODULE1 VBA MODULE

Following are the declarations at the top of the Module1 module:

 Public Const APPNAME As String = "Text Tools Utility" Public Const PROGRESSTHRESHOLD = 2000 Public UserChoices(1 To 8) As Variant 'stores user's last choices Public UndoRange As Range ' For undoing Public UserSelection As Range 'For undoing

I declare a Public constant containing a string that stores the name of the application. This string is used in the UserForm caption and in various message boxes.

The PROGRESSTHRESHOLD constant specifies the number of cells that will display the progress indicator. When this constant is 2,000 , the progress indicator will be shown only if the utility is working on 2,000 or more cells.

The UserChoices array holds the value of each control. This information is stored in the Windows Registry when the user closes the dialog box and is retrieved when the utility is executed again. This is a convenience feature I added because I found that many users tend to perform the same operation every time they use the utility.

Two other Range object variables are used to store information used for undoing.

THE SHOWTEXTTOOLSDIALOG PROCEDURE IN THE MODULE1 VBA MODULE

The ShowTextToolsDialog procedure follows :

 Sub ShowTextToolsDialog()     Dim InvalidContext As Boolean     If Val(Application.Version) < 12 Then         MsgBox "This utility requires Excel 2007 or later.", vbCritical         Exit Sub     End If     If ActiveSheet Is Nothing Then InvalidContext = True     If TypeName(ActiveSheet) <> "Worksheet" Then InvalidContext = True     If InvalidContext Then         MsgBox "Select some cells in a range.", vbCritical, APPNAME     Else         UserForm1.Show vbModeless     End If End Sub

As you can see, it's rather simple. The procedure starts by checking the version of Excel. If the version is prior to Excel 2007, the user is informed that the utility requires Excel 2007 or later.

Note	It's certainly possible to design this utility so it also works with previous versions. For simplicity, I made this an Excel 2007 “only application.

If the user is running the appropriate version, the ShowTextToolsDialog procedure checks to make sure that a sheet is active, and then it makes sure that the sheet is a worksheet. If either of these is not true, the InvalidContext variable is set to True . The If-Then-Else construct checks this variable and displays either a message (see Figure 16-4) or the UserForm. Notice that the Show method uses the vbModeless argument, which makes it a modeless UserForm (that is, the user can keep working in Excel while it is displayed).

Figure 16-4: This message is displayed if no workbook is active or if the active sheet is not a worksheet.

Notice that the code does not ensure that a range is selected. This additional error handling is included in the code that's executed when the Apply button is clicked.

Tip

While I was developing this utility, I assigned a keyboard shortcut (Ctrl+Shift+T) to the ShowTextToolsDialog procedure for testing purposes. That's because I saved the Ribbon modification task for last, and I needed a way to test the utility. After I added the Ribbon button, I removed the keyboard shortcut.

To assign a keyboard shortcut to a macro, press Alt+F8 to display the Macro dialog box. Type ShowTextToolsDialog in the Macro Name box and then click Options. Use the Macro Options dialog box to assign the shortcut key combination.

THE UNDOTEXTTOOLS PROCEDURE IN THE MODULE1 VBA MODULE

The UndoTextTools procedure is executed when the user clicks the Undo button (or presses Ctrl+Z). This technique is explained later in this chapter (see "Implementing Undo").

The UserForm1 code module

All the real work is done by VBA code contained in the code module for UserForm1 . Here, I briefly describe each of the procedures in this module. The code is too lengthy to list here, but you can view it by opening the text tools.xlam file on the companion CD-ROM.

THE USERFORM_INITIALIZE PROCEDURE IN THE USERFORM1 CODE MODULE

This procedure is executed before the UserForm is displayed. It sizes the UserForm and retrieves (from the Windows Registry) the previously selected values for the controls. It also adds the list items to the ComboBox (named ComboBoxOperation ) that determines which operation will be performed. These items are:

Change case
Add text
Remove by position
Remove spaces
Delete characters

THE COMBOBOXOPERATION_CHANGE PROCEDURE IN THE USERFORM1 CODE MODULE

This procedure is executed whenever the user selects an item in the ComboBoxOperation . It does the work of displaying or hiding the other controls. For example, if the user selects the Change Case option, the code unhides the second ComboBox control (named ComboProc1 ) and fills it with the following choices:

UPPER CASE
lower case
Proper Case
Sentence case
tOGGLE cASE

THE APPLYBUTTON_CLICK PROCEDURE IN THE USERFORM1 CODE MODULE

This procedure is executed when the Apply button is clicked. It does some error checking to ensure that a range is selected and then calls the CreateWorkRange function to make sure empty cells are not included in the cells to be processed . See the upcoming section, "Making the Text Tools utility efficient."

The ApplyButton_Click procedure also calls the SaveForUndo procedure, which saves the current data in case the user needs to undo the operation. See "Implementing Undo," later in this chapter.

The procedure then uses a Select Case construct to call the appropriate procedure to perform the operation. It calls one of the following Sub procedures:

ChangeCase
AddText
RemoveText
RemoveSpaces
RemoveCharacters

Some of these procedures make calls to function procedures. For example, the ChangeCase procedure might call the ToggleCase or SentenceCase procedures.

THE CLOSEBUTTON_CLICK PROCEDURE IN THE USERFORM1 CODE MODULE

This procedure is executed when the Close button is clicked. It saves the current control settings to the Windows Registry and then unloads the UserForm.

THE HELPBUTTON_CLICK PROCEDURE IN THE USERFORM1 CODE MODULE

This procedure is executed when the Help button is clicked. It simply displays the Help file (which is a standard compiled HTML help file).

Making the Text Tools utility efficient

The procedures in the Text Tools utility work by looping through a range of cells. It makes no sense to loop through cells that will not be changed - for example, empty cells and cells that contain a formula.

The ApplyButton_Click procedure calls a Function procedure named CreateWorkRange . This function creates and returns a Range object that consists of all non-empty and nonformula cells in the user's selected range. For example, assume that column A contains text in the range A1:A12. If the user selects the entire column, the CreateWorkRange function would convert that complete column range into a subset that consists of only the non-empty cells (that is, the range A:A would be converted to A1:A12). This makes the code much more efficient because empty cells and formulas need not be included in the loop.

The CreateWorkRange function accepts two arguments:

Rng : A Range object that represents the range selected by the user.
TextOnly : A Boolean value. If True , the function returns only text cells. Otherwise, it returns all non-empty cells.

 Private Function CreateWorkRange(Rng, TextOnly) '   Creates and returns a Range object     Set CreateWorkRange = Nothing '   Single cell, has a formula     If Rng.Count = 1 And Rng.HasFormula Then         Set CreateWorkRange = Nothing         Exit Function     End If '   Single cell, or single merged cell     If Rng.Count = 1 Or Rng.MergeCells = True Then         If TextOnly Then             If Not IsNumeric(Rng(1).Value) Then                 Set CreateWorkRange = Rng                 Exit Function             Else                 Set CreateWorkRange = Nothing                 Exit Function             End If         Else             If Not IsEmpty(Rng(1)) Then                 Set CreateWorkRange = Rng             Exit Function         End If      End If End If On Error Resume Next Set Rng = Intersect(Rng, Rng.Parent.UsedRange) If TextOnly = True Then     Set CreateWorkRange = Rng.SpecialCells(xlConstants, xlTextValues)     If Err <> 0 Then         Set CreateWorkRange = Nothing         On Error GoTo 0         Exit Function     End If Else     Set CreateWorkRange = Rng.SpecialCells _       (xlConstants, xlTextValues + xlNumbers)     If Err <> 0 Then         Set CreateWorkRange = Nothing         On Error GoTo 0         Exit Function     End If End If End Function

Note

The CreateWorkRange function makes heavy use of the SpecialCells property. To learn more about the SpecialCells property, try recording a macro while making various selections in Excel's Go To Special dialog box. You can display this dialog box by pressing F5 and then clicking the Special button in the Go To dialog box.

It's important to understand how the Go To Special dialog box works. Normally, it operates on the current range selection. For example, if an entire column is selected, the result is a subset of that column. But if a single cell is selected, it operates on the entire worksheet. Because of this, the CreateWorkRange function checks the number of cells in the range passed to it.

Saving the Text Tools utility settings

The Text Tools utility has a very useful feature: It remembers the last settings that you used. This is handy because many people tend to use the same option each time they invoke it.

The most recently used settings are stored in the Windows Registry. When the user clicks the Close button, the code uses VBA's SaveSetting function to save the value of each control. When the Text Tools utility is started, it uses the GetSetting function to retrieve those values and set the controls accordingly .

In the Windows Registry, the settings are stored at the following location:

 HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ Text Tools Utility\Settings

Figure 16-5 shows these settings in the Windows Registry Editor program ( regedit.exe ).

Figure 16-5: Use the Windows Registry Editor program to view the settings stored in the Registry.

If you examine the code for the Text Tools utility, you'll find that I used an eight-element array (named UserChoices ) to store the settings. I could have used separate variables for each setting, but using an array made the coding a bit easier.

The following VBA reads the settings from the Registry and stores them in the UserChoices array:

 '  Get previous settings    UserChoices(1) = GetSetting(APPNAME, "Settings", "OperationIndex", 0)    UserChoices(2) = GetSetting(APPNAME, "Settings", "ChangeCaseIndex", 0)    UserChoices(3) = GetSetting(APPNAME, "Settings", "TextToAdd", "")    UserChoices(4) = GetSetting(APPNAME, "Settings", "AddTextIndex", 0)    UserChoices(5) = GetSetting(APPNAME, "Settings", "CharsToRemoveIndex", 0)    UserChoices(6) = GetSetting(APPNAME, "Settings", "RemovePositionIndex", 0)    UserChoices(7) = GetSetting(APPNAME, "Settings", "RemoveSpacesIndex", 0)    UserChoices(8) = GetSetting(APPNAME, "Settings", "RemoveCharactersIndex", 0)    cbSkipNonText.Value = GetSetting(APPNAME, "cbSkipNonText", 0)

The code that follows is executed when the dialog box is closed. These statements retrieve the values from the UserChoices array and write them to the Registry.

 '  Store settings    SaveSetting APPNAME, "Settings", "OperationIndex", UserChoices(1)    SaveSetting APPNAME, "Settings", "ChangeCaseIndex", UserChoices(2)    SaveSetting APPNAME, "Settings", "TextToAdd", UserChoices(3)    SaveSetting APPNAME, "Settings", "AddTextIndex", UserChoices(4)    SaveSetting APPNAME, "Settings", "CharsToRemoveIndex", UserChoices(5)    SaveSetting APPNAME, "Settings", "RemovePositionIndex", UserChoices(6)    SaveSetting APPNAME, "Settings", "RemoveSpacesIndex", UserChoices(7)    SaveSetting APPNAME, "Settings", "RemoveCharactersIndex", UserChoices(8)    SaveSetting APPNAME, "Settings", "cbSkipNonText", cbSkipNonText.Value * -1

Implementing Undo

Unfortunately, Excel does not 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 handling undo:

 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 ThisWorkbook.Sheets(1).UsedRange.Clear

The UserSelection object variable saves the user's current selection so it can be re-selected after the undo operation. WorkRange is a Range object that's returned by the CreateWorkRange 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        ThisWorkbook.Sheets(1).Range _          (RngArea.Address).Formula = RngArea.Formula    Next RngArea

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.)

After the specified operation is performed, the code then uses the OnUndo method to specify the procedure to execute if the user chooses Undo. For example, after performing a case change operation, this statement is executed:

 Application.OnUndo "Undo Change Case", "UndoTextTools"

Excel's Undo drop-down will then contain a menu item: Undo Change Case (see Figure 16-6). If the user selects the command, the UndoTextTools procedure, shown next, will be executed.

Figure 16-6: The Text Tools utility includes a single level of undo.

 Private Sub UndoTextTools() '   Undoes the last operation     Dim a As Range     On Error GoTo ErrHandler     Application.ScreenUpdating = False     With UserSelection         .Parent.Parent.Activate         .Parent.Activate         .Select     End With     For Each a In UndoRange.Areas         a.Formula = ThisWorkbook.Sheets(1).Range(a.Address).Formula     Next a     Application.ScreenUpdating = True     On Error GoTo 0     Exit Sub ErrHandler:     Application.ScreenUpdating = True     MsgBox "Can't undo", vbInformation, APPNAME     On Error GoTo 0 End Sub

Undoing a VBA Procedure

Computer users have become accustomed to being able to undo an operation. Almost every operation that you perform in Excel can be undone. Even better, Excel 2007 increased the number of undo levels from 16 to 100.

If you program in VBA, you may have wondered whether it's possible to 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 do this 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 might 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 previous operations.

The Application object contains an OnUndo method, which lets the programmer specify text to appear on the Undo drop-down and a procedure to execute if the user chooses the Undo command. For example, the following statement causes the Undo drop-down 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 UndoTextTools procedure first ensures that the correct workbook and worksheet are activated and then selects the original range selected by the user. Then it loops through each area of the stored data (which is available because of the UndoRange public variable) and puts the data back to its original location (overwriting the changes, of course).

CD-ROM

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") End Sub

Figure 16-7 shows one of the help screens.

Figure 16-7: A help screen for the Text Tools utility.

CD-ROM

The companion CD-ROM includes all of the source files that were used to create the Help file. These files are in a directory named \helpsource . If you're not familiar with HTML Help files, refer to Chapter 24 for additional information.

Adding the RibbonX code

The final task in creating this utility is to provide a way to execute it. Before Excel 2007, it was relatively easy to insert a new menu command or toolbar button. But, with the new Ribbon user interface, this once-simple job is significantly more challenging.

To add a custom Ribbon interface to an Excel file (which is actually a package of files), you must modify the file in two ways:

Create a new folder within the package and add a new part - an XML file that describes the Ribbon modification.
Define a relationship to the new part.

CROSS-REFERENCE

Chapter 22 contains additional information about working with the Ribbon.

Figure 16-8 shows the Ribbon with a new group (called Utilities) added to the end of the Home tab. This group contains a single control that, when clicked, executes this procedure:

Figure 16-8: The Ribbon contains a new group in the Home tab.

 Sub StartTextTools(control As IRibbonControl)     Call ShowTextToolsDialog End Sub

Following is a step-by-step list of instructions that will add that single control to the Ribbon. These instructions assume that the workbook is an XLAM add-in file.

Close the text tools.xlam add-in file.
Make a copy of the add-in file and name it copy of text tools.xlam.zip .

Excel 2007 files are actually ZIP compressed files, but without the .zip extension. If you add a .zip extension, you can work with the individual files within the ZIP file.
Use a zip utility to extract the files into a new directory named copy of text tools .

You can use the zip tools built into Windows. Right-click the ZIP file and choose Extract All to launch the Windows Extraction Wizard. Figure 16-9 shows the files in the directory.

Figure 16-9: The files in text tools.xlam.
Add a new folder named customUI to the copy of text tools directory.

In the customUI folder, create a text file named customUI.xml with the following contents (the spacing isn't important). This is the XML part.

 <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">      <ribbon>        <tabs>          <tab idMso="TabHome">            <group id="Utilities1" label="Utilities">              <button id="TextToolsButton" label="Text Tools"    onAction="StartTextTools" imageMso="ControlsGallery" size="large"    supertip="Displays a modeless dialog box that contains tools for    working with text in cells. Add-in distributed with 'Excel 2007 Power    Programming With VBA,' by John Walkenbach."/>            </group>          </tab>        </tabs>      </ribbon>    </customUI>

Use a text editor (such as Windows Notepad) to edit the .rels file in the _rels directory. This step defines the relationship of the XML part that was added. Insert the following line into the .rels file and save the file:
```
 <Relationship Id="12345"    Type="http://schemas.microsoft.com/office/2006/relationships/ui/    extensibility" Target="customUI/customUI.xml"/> 
```
Note
The value for Relationship Id can be any value or string that's not already in use.
Zip all of the files in the copy of text tools directory and remove the .zip extension. The ZIP file should be named copy of text tools.xlam .
Open the copy of text tools.xlam file with Excel. If all went well, the Home tab of Excel's Ribbon will display a new group.

Add this new procedure to Module1 of copy of text tools.xlam .

 Sub StartTextTools(control As IRibbonControl)     Call ShowTextToolsDialog End Sub

When you're satisfied that all is working, close the file and rename it to text tools.xlam .

Note

When a workbook has a customized Ribbon, the Ribbon customizations appear only when that workbook is active. But, fortunately, there is an exception to this rule. When the Ribbon customization is contained in an XLAM add-in file (as in this example), the Ribbon modifications appear as long as the add-in file is opened, regardless of which workbook is active.

The preceding steps describe the manual method for adding a customUI.xml folder and modifying the _rels\.rel file. I will confess that I didn't actually go through these absurdly tedious steps. Rather, I used a utility called Office 2007 Custom UI Editor, written by Doug Mahugh (see Figure 16-10). This utility still requires that you write the XML code manually, but it eliminates the need for all of the zipping and unzipping. Refer to Chapter 22 for more information.

Figure 16-10: An easier way to modify the Ribbon.

Post-mortem of the project

The previous sections describe each component of the Text Tools utility. At this point, it's useful to revisit the original project goals to see whether they were met. The original goals, along with my comments, are as follows:

Its main features will be those listed at the beginning of this section. Accomplished.
It will enable the user to request the preceding types of changes on nontext cells as well as text cells. Accomplished.
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. As I noted earlier, the Text Tools utility deviates from Excel's normal look and feel by using an Apply button rather than an OK button. And, unlike most of Excel's dialog boxes, Text Tools uses a modeless, stay-on-top dialog box. In light of the enhanced usability, I think these deviations are quite reasonable.
It will be in the form of an add-in and will be accessible from the Ribbon. Accomplished.
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. Accomplished. And because the dialog box need not be dismissed, it didn't require the use of a RefEdit control.
It will remember the last operation used and display those settings the next time the dialog box is invoked. Accomplished (thanks to the Windows Registry).
It will have no effect on cells that contain formulas. Accomplished.
It will be fast and efficient. For example, if the user selects an entire range, the utility should ignore empty cells. Accomplished.
It will enable the user to undo the changes. Accomplished.
Comprehensive help will be available. Accomplished.

Understand the Text Tools utility

If you don't fully understand how this utility works, I urge you to load the add-in and use the Debugger to step through the code. Try it out with different types of selections, including an entire worksheet. You will see that regardless of the size of the original selection, only the appropriate cells are processed, and empty cells are completely ignored. If a worksheet has only one cell with text in it, the utility operates just as quickly whether you select that cell or the entire worksheet.

If you convert the add-in to a standard workbook, you'll be able to see how the original data is stored in the worksheet for undo. To convert the add-in to a workbook, double-click the ThisWorkbook code module. Press F4 to display the Properties box and then change the IsAddin property to False .