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.

Understanding Add-Ins

An add-in provides software with 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 from 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. However, 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:

• Store one or more custom worksheet functions  When the add-in is loaded, you can use the functions as you would any built-in worksheet function.
• Store Excel utilities  VBA is ideal for creating general-purpose utilities that extend the power of Excel.
• Store proprietary macros  If you don't want end users to see (or modify) your macros, store the macros in an add-in and protect the VBA project with a password. Users can use the macros, but they can't view or change them unless they know the password. An additional benefit is that the add-in doesn't display a workbook window, which can be distracting.

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. We explain this process later in this chapter (see “Creating Add-Ins”).

Working with 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:

1. Choose File ➪ Options. The Excel Options dialog box appears.
2. Select the Add-Ins category.
3. At the bottom of the dialog box, select Excel Add-Ins from the Manage list and then click Go.

The Add-Ins dialog box appears, as shown in Figure 45.1. The dialog box contains all the add-ins that Excel knows about, which varies from computer to computer. 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.

Understanding When to Create Add-Ins

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 why you may want to convert your Excel workbook application to an add-in:

• To avoid confusion  If an end user loads your application as an add-in, the file isn't visible in a window and, therefore, is less likely to confuse novice users or get in the way. Unlike a hidden workbook window, an add-in can't be unhidden.
• To simplify access to worksheet functions  Custom worksheet functions stored in an add-in don't require the workbook name qualifier. For example, if you have a custom function named MOVAVG stored in a workbook named Newfuncs.xlsm, you must use syntax such as the following to use this function in a different workbook:

  =NEWFUNCS.XLSM!MOVAVG(A1:A50)

  If this function is stored in an add-in file that's open, however, the syntax is much simpler because you don't need to include the file reference:

  =MOVAVG(A1:A50)
• To provide easier access  After you identify the location of your add-in, it appears in the Add-Ins dialog box and can display a friendly name and a description of what it does.
• To permit better loading control  You can automatically open add-ins when Excel starts, regardless of the directory in which they're stored.
• To omit prompts when unloading  When an add-in is closed, the user never sees the Save Change In prompt because changes to add-ins aren't saved unless you specifically do so from the VB Editor window.

Creating Add-Ins

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, no 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:

1. Develop your application, and make sure that everything works properly.
2. (Optional) Add a title and description for your add-in. Choose File ➪ Info and click Show All Properties at the bottom of the right panel. Enter a brief descriptive title in the Title field and then enter a longer description in the Comments field. Although this step isn't required, it makes installing and identifying the add-in easier.
3. (Optional) Lock the VBA project. This step protects the VBA code and UserForms from being viewed. You do this in the Visual Basic Editor (VBE) by choosing Tools ➪ <Project Name> Properties (where <Project Name> corresponds to your VB project name). In the dialog box, click the Protection tab and select Lock Project for Viewing. If you like, you can specify a password to prevent others from viewing your code.
4. Save the workbook as an add-in file by choosing File ➪ Save As and selecting Excel Add-In (*.xlam) from the Save As Type drop-down list. By default, Excel saves your add-in to your AddIns directory. You can override this location and choose any directory that you like.

After you create the add-in, you need to install it:

1. Choose File ➪ Options ➪ Add-Ins.
2. Select Excel Add-Ins from the Manage drop-down list and then click Go. The Add-Ins dialog box appears.
3. Click the Browse button to locate the XLAM file that you created, and click OK, which installs the add-in. The Add-Ins dialog box uses the descriptive title that you provided for the Title field in the Show All Properties panel.

Looking at an Add-In Example

The following sections discuss the steps required to create a useful add-in from the change case.xlsm workbook that we covered 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.

Sub ShowChangeCaseUserForm ()
  If TypeName(Selection) = "Range" Then
    UserForm1.Show
  Else
    MsgBox "Select some cells."
  End If
End Sub

Learning about the UserForm

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.

Adding descriptive information

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.