Chapter 14: Developing Excel Utilities with VBA

In This Chapter

• Exploring Excel utilities and utilities in general

• Developing utilities with VBA

• Creating good utilities

• Manipulating text in cells

• Finding additional Excel utilities

About Excel Utilities

A utility, in general, is something that enhances software, adding useful features or making existing features more accessible. A utility isn't an end product, such as a quarterly report. Rather, it's a tool that helps you produce an end product. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities.

Excel is a great product, but many users soon develop a wish list of features that they'd like to see added to the software. For example, users who work with dates may want a pop-up calendar feature to facilitate entering dates into cells. And some users desire an easier way to export a range of data to a separate file or to save a chart as a graphics file. These are all examples of features that aren't currently available in Excel. You can, however, add these features by creating a utility.

Utilities don't need to be complicated. Some of the most useful ones are very simple. For example, have you noticed that Excel 2013 doesn't have a Ribbon command to toggle the page break display in a worksheet? If you don't like to see those dotted lines in your worksheet, a trip to the Excel Options dialog box to turn them off is required. Even worse, you can't add that command to the Ribbon or Quick Access toolbar.

Here's a simple VBA macro that toggles the page break display:

Sub TogglePageBreaks()

  With ActiveSheet

    .DisplayPageBreaks = Not .DisplayPageBreaks

  End With

End Sub

You can store this macro in your Personal Macro Workbook so that it's always available. Or you may prefer to package your favorite utilities in an add-in. For quicker access, you can assign your utility macros to a shortcut key or a right-click shortcut menu or modify your Quick Access toolbar or the Ribbon.

As you'll see, creating utilities for Excel is an excellent way to make a great product even better.

Using VBA to Develop Utilities

Excel 5, released in 1992, was the first version of Excel to include VBA. When I received the beta version of Excel 5, I was impressed by VBA's potential. VBA was light-years ahead of Excel's powerful (but cryptic) XLM macro language, and I decided that I wanted to explore this new language and see its capabilities.

In an effort to learn VBA, I wrote a collection of Excel utilities by using only VBA. I figured that I would learn the language more quickly if I gave myself a tangible goal. The result was a product that I call the Power Utility Pak for Excel, which is available to you at a discounted price as a benefit of buying this book. (Use the coupon in the back of the book to order your copy.)

I learned several things from my initial efforts on this project:

• VBA can be difficult to grasp at first, but it becomes much easier with practice.

• Experimentation is the key to mastering VBA. Every project that I undertake usually involves dozens of small coding experiments that eventually lead to a finished product.

• VBA enables you to extend Excel in a way that is consistent with Excel's look and feel, including custom worksheet functions and dialog boxes. And, if you're willing to step outside VBA, you can write XML code to customize the Ribbon automatically when your application is opened.

• Excel can do almost anything. When you reach a dead end, chances are that another path leads to a solution, especially if you're creative and know where to look for help.

Few other software packages include such an extensive set of tools that enable the end user to extend the software.

What Makes a Good Utility?

An Excel utility, of course, should ultimately make your job easier or more efficient. But if you're developing utilities for other users, what makes an Excel utility valuable? I've put together a list of elements that are common to good utilities:

• It adds something to Excel. This addition could be a new feature, a way to combine existing features, or just a way to make an existing feature easier to use.

• It's general in nature. Ideally, a utility should be useful under a wide variety of conditions. Of course, writing a general-purpose utility is more difficult than it is to write one that works in a highly defined environment.

• It's flexible. The best utilities provide many options to handle various situations.

• It looks, works, and feels like an Excel command. Although adding your own special touch to utilities is tempting, other users will find them easier to use if they look and act like familiar Excel commands and dialog boxes.

• It provides help for the user when needed. In other words, the utility should have documentation that's thorough and accessible.

• It traps errors. An end user should never see a VBA error message. Any error messages that appear should be ones that you write.

• Users can undo its effects. Users who don't like the result caused by your utility should be able to reverse their path.

Text Tools: The Anatomy of a Utility

In this section, I describe an Excel utility that I developed and use frequently. It's also 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, at 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 the text (nonprinting characters, alphabetic characters, non-numeric characters, nonalphabetic characters, or numeric characters).

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

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

The Text Tools utility is available on the book's website. It's a stand-alone version of the tool 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 isn't protected with a password, so you can examine the code to see how it works or make changes to better suit your needs.

Background for Text Tools

Excel has many worksheet functions that can manipulate text strings in useful ways. For example, you can make the text in a cell uppercase (UPPER), add characters to text (CONCATENATE), and remove spaces (TRIM). 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 modifying text particularly easy. 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 a dozen 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 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 will ignore the empty cells in the column.

• It will use a modeless dialog box so that the user can keep the dialog box on-screen and ready to use.

• It will be compact in size so that it doesn't hide too much of the worksheet.

• It will enable the user to undo 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 (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).

• 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 component contains the dialog box. The code that does the text manipulation is stored in the code module for the UserForm.

The file also contains some manual modifications that I made to display the command on the Ribbon. See “Adding the RibbonX code,” later in this chapter. Unfortunately, you can't 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. Choosing this command sequence executes the StartTextTools procedure, which calls the ShowTextToolsDialog procedure.

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, a modeless dialog box is similar to a toolbar.

If you use this utility in Excel 2013, the Text Tools dialog box will not be available if you switch to a different workbook window. To use the utility in a different workbook, you must close the Text Tools dialog box, activate the other window, and then issue the command to display the dialog box again.

The UserForm for the Text Tools utility

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

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

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

Note that the controls on this UserForm are laid out differently from how they 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 control always appears on the left, and you use it 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 control consists of a Label control inside a Frame control.

• Text box: This text box is used for the Add Text option.

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

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

The Module1 VBA module

The Module1 VBA module contains the declarations, a simple procedure that starts 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. I added this convenience feature 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

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.

For simplicity, I made this utility an application for Excel 2007 or later. However, you can design this utility so that it also works with previous versions of Excel.

If the user is running the appropriate version of Excel, 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 one isn't true, the InvalidContext variable is set to True. The If-Then-Else construct checks this variable and displays either a message (see Figure 14-4) or the UserForm. The Show method uses the vbModeless argument, which makes it a modeless UserForm (that is, the user can keep working in Excel while it's displayed).

Figure 14-4: This message is displayed if no workbook is active or if the active sheet isn't a worksheet.

Note that the code doesn't ensure that a range is selected. This additional error handling is included in the code that's executed when the Apply button is clicked.

While I was developing this utility, I assigned a keyboard shortcut (Ctrl+Shift+T) to the ShowTextToolsDialog procedure 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 (or unassign) 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 in the code module for UserForm1. Here, I briefly describe each procedure in this module. The code is too lengthy to list here, but you can view it by opening the text tools.xlam file available on the book's website.

The UserForm_Initialize procedure in the UserForm1 code module

The UserForm_Initialize 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

The ComboBoxOperation_Change 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

The ApplyButton_Click 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 that empty cells aren't 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 procedure.

The CloseButton_Click procedure in the UserForm1 code module

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

The HelpButton_Click procedure in the UserForm1 code module

The HelpButton_Click procedure is executed when the Help button is clicked. This procedure 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. Therefore, I added code to improve the efficiency of the cell processing.

The ApplyButton_Click procedure calls a Function procedure named CreateWorkRange. This function creates and returns a Range object that consists of all nonempty 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 nonempty cells (that is, the range A:A would be converted to A1:A12). This conversion 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 nonempty 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

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 useful feature: It remembers the last settings that you used. This feature 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_USERSoftwareVB and VBA Program Settings

Text Tools UtilitySettings

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

Figure 14-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 code 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 doesn't provide a direct way to undo an operation performed using VBA. Undoing a VBA macro is often possible, but it usually 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 that you can reselect it after the undo operation. WorkRange is a Range object that's returned by the CreateWorkRange function. The range consists of the nonempty 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 WorkRange and stores the data in the worksheet. (If 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 list will then contain a menu item: Undo Change Case (see Figure 14-6). If the user selects the command, the UndoTextTools procedure, shown next, will be executed.

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

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

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

The book's website 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)

End Sub

Figure 14-7 shows one of the Help screens.

Figure 14-7: A Help screen for the Text Tools utility.

The book's website includes all 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 22 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, inserting a new menu command or toolbar button was relatively easy. But, with the new Ribbon user interface, this once-simple job is significantly more challenging.

I used the Custom UI Editor for Microsoft Office to add the RibbonX code that generates a new Ribbon group and command. The Custom UI Editor isn't included with Microsoft Office, but you can locate and download the program on the Internet.

Chapter 20 contains additional information about working with the Ribbon and the Custom UI Editor.

Figure 14-8 shows a portion of 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:

Sub StartTextTools(control As IRibbonControl)

    Call ShowTextToolsDialog

End Sub

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

Figure 14-9 shows the RibbonX code in the Custom UI Editor.

Figure 14-9: Using the Custom UI Editor to provide a way to execute the utility from the Ribbon.

When a workbook has a customized Ribbon, the Ribbon customizations appear only when that workbook is active. But, fortunately, this rule has an exception. 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.

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 specify that the preceding types of changes work with nontext cells as well as with 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. 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 also be accessible from the Ribbon. Accomplished.

• It will operate with the current selection of cells (including multiple selections) and 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 column, the utility will ignore empty cells in the column. Accomplished.

• It will use a modeless dialog box so that the user can keep the dialog box on-screen and ready to use. Accomplished.

• It will be compact in size so that it doesn't hide too much of the worksheet. Accomplished.

• It will enable the user to undo changes. Accomplished.

• Comprehensive help will be available. Accomplished.

Understand the Text Tools utility

If you don't fully understand how the Text Tools utility works, I urge you to load the add-in and use the Debugger to step through the code. Try out the utility with different types of selections, including an entire worksheet. You'll see that regardless of the size of the original selection, only the appropriate cells are processed and empty cells are 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 in the Properties window. Press F4 to display the Properties box and then change the IsAddin property to False.

More about Excel Utilities

If you are interested in creating Excel utilities, I urge you to download a trial copy of Power Utility Pak. This product includes about 60 useful utilities (plus many custom worksheet functions). If you find it helpful, you can use the coupon in the back of this book to order a copy at a discounted price. The complete VBA source code also is available for a small fee.

In addition to the Power Utility Pak, many other utilities are available, and you can download most of them from the Internet.