IN THIS CHAPTER
For developers, one of the most useful features in Excel is the capability to create add-ins. This chapter discusses this concept and provides a practical example of creating an add-in.
Generally speaking, an add-in is something that's added to software to give it additional functionality. Excel includes several add-ins, including the Analysis ToolPak and Solver. Ideally, the new features blend in well with the original interface so that they appear to be part of the program.
Excel's approach to add-ins is quite powerful: any knowledgeable Excel user can create add-ins from workbooks. The type of add-in covered in this chapter is basically a different form of a workbook file. Any Excel workbook can be converted into an add-in, but not every workbook is a good candidate for an add-in.
What distinguishes an add-in form a normal workbook? Add-ins, by default, have an .xlam
extension. In addition, add-ins are always hidden, so you can't display worksheets or chart sheets that are contained in an add-in. But you can access its VBA procedures and display dialog boxes that are contained on UserForms.
The following are some typical uses for Excel add-ins:
As previously noted, Excel ships with several useful add-ins, and you can acquire other add-ins from third-party vendors or online. In addition, Excel includes the tools that enable you to create your own add-ins. I explain this process later in this chapter (see “Creating Add-Ins”).
The best way to work with add-ins is to use the Excel Add-In Manager. To display the Add-In Manager, follow these steps:
The Add-Ins dialog box, shown in Figure 45.1, appears. The dialog box contains all the add-ins that Excel knows about. The add-ins that are checked are open. You can open and close add-ins from this dialog box by selecting or deselecting the check boxes.
The user interface for some add-ins (including those included with Excel) may be integrated into the Ribbon. For example, when you open the Analysis ToolPak add-in, you access these tools by choosing Data Analysis Data Analysis.
Most Excel users have no need to create add-ins. However, if you develop spreadsheets for others — or if you simply want to get the most out of Excel — you may be interested in pursuing this topic further.
Here are some reasons you may want to convert your Excel workbook application to an add-in:
MOVAVG
stored in a workbook named Newfuncs.xlsm
, you have to use syntax such as the following to use this function in a different workbook:
=NEWFUNC.XLSM!MOVAVG(A1:A50)
=MOVAVG(A1:A50)
Save Change In
prompt because changes to add-ins aren't saved unless you specifically do so from the VB Editor window.Technically, you can convert any workbook to an add-in. Not all workbooks benefit from this conversion, though. In fact, workbooks that consist only of worksheets (that is, not macros or custom dialog boxes) become unusable because add-ins are hidden.
Workbooks that benefit from conversion to an add-in are those with macros. For example, you may have a workbook that consists of general-purpose macros and functions. This type of workbook makes an ideal add-in.
The following steps describe how to create an add-in from a workbook:
AddIns
directory. You can override this location and choose any directory you like.After you create the add-in, you need to install it:
This section discusses the steps to create a useful add-in from the change case.xlsm
workbook that I cover in Chapter 41, “Creating UserForms.” This workbook contains a UserForm that displays options that change the text case of selected cells (uppercase, lowercase, or proper case). Figure 45.2 shows the add-in in action.
This workbook contains one worksheet, which is empty. Although the worksheet is not used, it must be present because every workbook must have at least one sheet.
It also contains one VBA module and one UserForm.
The Module1 code module contains one procedure that displays the UserForm. The ShowChangeCaseUserForm
procedure checks the type of selection. If a range is selected, the dialog box in UserForm1 appears. If anything other than a range is selected, a message box is displayed:
Sub ShowChangeCaseUserForm ()
If TypeName(Selection) = "Range" Then
UserForm1.Show
Else
MsgBox "Select some cells."
End If
End Sub
Figure 45.3 shows the UserForm1 form, which has five controls: three OptionButton
controls and two CommandButton
controls. The controls have descriptive names, and the Accelerator
property is set so that the controls display an accelerator key (for keyboard users). The option button with the Upper Case caption has its Value
property set to TRUE
, which makes it the default option.
Before you convert a workbook to an add-in, test it when a different workbook is active to simulate what happens when the workbook is an add-in. Remember that an add-in is never the active workbook, and it never displays any of its worksheets.
To test it, save the XLSM version of the workbook, close it, and then reopen it. With the workbook open, activate a different workbook, select some cells that contain text, and then press Alt+F8 to display the Macros dialog box. Execute the ShowChangeCaseUserForm
macro and try all the options.
Adding descriptive information is recommended but not necessary. Choose File Info and click Show All Properties at the bottom of the right panel (see Figure 45.4). Enter a title for the add-in in the Title field. This text appears in the Add-Ins dialog box. In the Comments field, enter a description. This information appears at the bottom of the Add-Ins dialog box when the add-in is selected.
At this point, the future add-in is missing one key component: a way to execute the macro that displays the UserForm. The easiest solution is to provide a shortcut key that executes the macro. Ctrl+Shift+C is a good key combination. Here's how to do it:
Make sure you save the workbook after making this change.
In some situations (such as a commercial product), you may want to protect your project so that others can't see the source code. To protect the project, follow these steps:
To save the workbook as an add-in, follow these steps:
AddIns
directory, but you can choose a different directory if you like.Now it's time to try the add-in. Make sure the XLSM version of the workbook is not open, and then follow these steps:
change case.xlam
add-in that you just created. The Add-Ins dialog box displays the add-in in its list. Notice that the information you provided in the Properties panel appears here.When the add-in is installed, you can access it by pressing Ctrl+Shift+C. Another option is to add a new item to your Quick Access toolbar or to the Ribbon.