Chapter 21
In This Chapter
Using add-ins: What a concept!
Knowing why you might want to create your own add-ins
Creating custom add-ins
Reviewing an add-in example
One of the slickest features of Excel — at least in my mind — is the capability to create add-ins. In this chapter, I explain why I like this feature and show you how to create add-ins by using only the tools built into Excel.
Glad you asked. An Excel add-in is something you add to enhance Excel’s functionality. Some add-ins provide new worksheet functions you can use in formulas; other add-ins provide new commands or utilities. If the add-in is designed properly, the new features blend in well with the original interface so they appear to be part of the program.
Any knowledgeable user can create add-ins, but VBA programming skills are required. An Excel add-in is basically a different form of an XLSM workbook file. More specifically, an add-in is a normal XLSM workbook with the following differences:
You can convert any workbook file to an add-in, but not all workbooks are good candidates. Because add-ins are always hidden, you can’t display worksheets or chart sheets contained in an add-in. However, you can access an add-in’s VBA Sub and Function procedures and display dialog boxes contained on UserForms.
You might decide to convert your Excel application to an add-in for any of the following reasons:
=NEWFUNC.XLSM!MOVAVG(A1:A50)
But if this function is stored in an add-in file that’s open, you can use much simpler syntax because you don’t need to include the file reference:
=MOVAVG(A1:A50)
You load and unload add-ins by using the Add-Ins dialog box. To display this dialog box, choose File ⇒ Options ⇒ Add-Ins. Then select Excel Add-Ins from the drop-down list at the bottom of this dialog screen and click Go. Or take the fast track and choose Developer ⇒ Add-Ins ⇒ Add-Ins. But the easiest method is to just press Alt+TI (the old Excel 2003 keyboard shortcut).
Any of these methods displays the Add-Ins dialog box, shown in Figure 21-1. The list box contains the names of all add-ins that Excel knows about. In this list, check marks identify any currently open add-ins. You can open and close add-ins from the Add-Ins dialog box by selecting or deselecting the check boxes.
To add a new add-in to the list, click Browse and then locate the XLAM file.
When you open an add-in, you may or may not notice anything different. In many cases, however, the Ribbon changes in some way; Excel displays a new tab or one or more new groups on an existing tab. For example, opening the Analysis ToolPak add-in gives you a new item on the Data tab: Analysis ⇒ Data Analysis. If the add-in contains only custom worksheet functions, the new functions appear in the Insert Function dialog box, and you see no change in Excel's user interface.
Although you can convert any workbook to an add-in, not all workbooks benefit from this conversion. A workbook with no macros makes a useless add-in. In fact, the only types of workbooks that benefit from being converted to an add-in are those with macros. For example, a workbook that consists of general-purpose macros (Sub and Function procedures) makes an ideal add-in.
Creating an add-in isn't difficult, but it does require a bit of extra work. Follow these steps to create an add-in from a normal workbook file:
Develop your application, and make sure that everything works properly.
Don’t forget to include a method for executing the macro or macros. You might want to define a shortcut key or customize the user interface in some way (see Chapter 19). If the add-in consists only of functions, there’s no need to include a method to execute them because they appear in the Insert Function dialog box.
Test the application by executing it when a different workbook is active.
Doing so simulates the application’s behavior when it’s used as an add-in because an add-in is never the active workbook.
Activate the VBE and select the workbook in the Project window; choose Tools ⇒ VBAProject Properties and click the Protection tab; select the Lock Project for Viewing check box and enter a password (twice); then click OK.
This step is necessary only if you want to prevent others from viewing or modifying your macros or UserForms.
Choose File ⇒ Info, and select Show All Properties at the bottom of the right panel.
Excel expands the list of properties displayed.
Enter a brief descriptive title in the Title field and a longer description in the Comments field.
Steps 4 and 5 are not required but make the add-in easier to use, because the descriptions you enter appear in the Add-Ins dialog box when your add-in is selected.
Specify the folder that will store the add-in.
Excel proposes its default add-ins folder (named AddIns), but you can save the file in any folder you like.
A copy of your workbook is converted to an add-in and saved with an XLAM extension. Your original workbook remains open.
In this section, I discuss the basic steps involved in creating a useful add-in. The example is based on the Change Case text conversion utility that I describe in Chapter 16.
The workbook consists of one blank worksheet, a VBA module, and a UserForm. In Chapter 19, I describe how to add code that adds a new item to the Cell shortcut menu.
The original version of the utility includes options for uppercase, lowercase, and proper case. For the add-in version, I add two new options to the UserForm so it has the same options as the built-in tool in Microsoft Word:
Figure 21-2 shows UserForm1. The five OptionButton controls are inside a Frame control. In addition, the UserForm has a Cancel button (named CancelButton) and an OK button (named OKButton).
The code executed when the Cancel button is clicked is very simple. This procedure unloads the UserForm with no action:
Private Sub CancelButton_Click()
Unload UserForm1
End Sub
The code that's executed when the OK button is clicked follows. This code does all the work:
Private Sub OKButton_Click()
Dim TextCells As Range
Dim cell As Range
Dim Text As String
Dim i As Long
' Create an object with just text constants
On Error Resume Next
Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
' Turn off screen updating
Application.ScreenUpdating = False
' Loop through the cells
For Each cell In TextCells
Text = cell.Value
Select Case True
Case OptionLower 'lowercase
cell.Value = LCase(cell.Value)
Case OptionUpper 'UPPERCASE
cell.Value = UCase(cell.Value)
Case OptionProper 'Proper Case
cell.Value = WorksheetFunction.Proper(cell.Value)
Case OptionSentence 'Sentence case
Text = UCase(Left(cell.Value, 1))
Text = Text & LCase(Mid(cell.Value, 2, Len(cell.Value)))
cell.Value = Text
Case OptionToggle 'tOGGLE CASE
For i = 1 To Len(Text)
If Mid(Text, i, 1) Like "[A-Z]" Then
Mid(Text, i, 1) = LCase(Mid(Text, i, 1))
Else
Mid(Text, i, 1) = UCase(Mid(Text, i, 1))
End If
Next i
cell.Value = Text
End Select
Next
' Unload the dialog box
Unload UserForm1
End Sub
In addition to the two new options, this version of the Change Case utility differs from the version in Chapter 16 in two other ways:
Test the add-in before converting this workbook. To simulate what happens when the workbook is an add-in, you should test the workbook when a different workbook is active. Because an add-in is never the active sheet or workbook, testing it when a different workbook is open may help you identify some potential errors.
Open a new workbook. and enter information in some cells.
For testing purposes, enter various types of information, including text, values, and formulas. Or just open an existing workbook and use it for your tests. Remember that any changes to the workbook cannot be undone, so you may want to use a copy.
I recommend entering a description of your add-in, but this isn’t required. Follow these steps to add a description:
Choose File ⇒ Info, and click Show All Properties at the bottom right.
Excel expands the Properties list.
Enter a title for the add-in in the Title field.
This text appears in the list of add-ins in the Add-Ins dialog box. For this example, enter Change Case.
This information appears at the bottom of the Add-Ins dialog box when the add-in is selected. For this example, enter Changes the case of text in selected cells. Access this utility by using the shortcut menu.
Figure 21-3 shows the Properties section with the Title and Comments fields filled out.
If you want to add a password to prevent others from viewing the VBA code, follow these steps:
At this point, you've tested the change case.xlsm file, and it's working correctly. The next step is creating the add-in. Follow these steps:
Activate the change case.xlsm workbook, and choose File ⇒ Save As ⇒ Browse.
Excel displays its Save As dialog box.
A new add-in file (with an .xlam extension) is created, and the original XLSM version remains open.
To avoid confusion, close the XLSM workbook before opening the add-in that you created from that workbook.
Open the add-in by following these steps:
Choose Developer ⇒ Add-Ins ⇒ Add-Ins (or press Alt+TI).
Excel displays the Add-Ins dialog box.
Click OK to close the Browse dialog box.
After you find your new add-in, the Add-Ins dialog box lists the add-in. As shown in Figure 21-4, the Add-Ins dialog box also displays the descriptive information you provided in the Document Properties panel.
Click OK to close the dialog box.
Excel opens the add-in. Now you can use it with all your workbooks. As long as it remains selected in the Add-Ins dialog box, the add-in opens every time you start Excel.
If you’re in a generous mood, you can distribute this add-in to other Excel users simply by giving them a copy of the XLAM file. (They don’t need the XLSM version.) When they open the add-in, the new Change Case command appears on the shortcut menu when they select a range, one or more rows, or one or more columns. If you lock the VBA project with a password, others cannot view your macro code unless they know the password.
An add-in can be edited just like any other workbook. You can edit the XLAM file directly (you do not need to work with the original XLSM version) by following these steps:
Double-click the project’s name in the Project window.
If you protected the code, you are prompted for the password.