Chapter 45. Creating Custom Excel Add-Ins

IN THIS CHAPTER

Understanding add-ins

Converting a workbook to an add-in

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.

45.1. What Is 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 because any knowledgeable Excel user can create add-ins from workbooks. An Excel add-in 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:

• Store one or more custom worksheet functions. When the add-in is loaded, you can use the functions like any built-in worksheet function.

• Store Excel utilities. VBA is ideal for creating general-purpose utilities that extend the power of Excel. The Power Utility Pak that I created is an example.

• 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. A user can use the macros, but they can't view or change them unless the user knows 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 (see the sidebar "Add-Ins Included with Excel"), and you can acquire other add-ins from third-party vendors or the Internet. In addition, Excel includes the tools that enable you to create your own add-ins. I explain this process later in the chapter (see "Creating Add-Ins").

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

Excel displays its Add-Ins dialog box, shown in Figure 45.1. The list 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.

Pressing Alt+TI is a much faster way to display the Add-Ins dialog box.

You can also open most add-in files by choosing File Open. After an add-in is opened, however, you can't choose File Close to close it. The only way to remove the add-in is to exit and restart Excel or to write a macro to close the add-in. Therefore, you're usually better off opening the add-ins by using the Add-Ins dialog box.

Figure 45.1. The Add-Ins dialog box.

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.

Add-In Name	What It Does	Where to Find It
Analysis ToolPak	Statistical and engineering tools. See Chapter 38.	Choose Data Analysis Data Analysis.
Analysis ToolPak — VBA	VBA functions for the Analysis ToolPak.	The functions in this add-in are used by VBA programmers, and they are not accessible from Excel.
Euro Currency Tools	Tools for converting and formatting the euro currency.	Choose Formulas Solutions Euro Conversion And Formulas Solutions Euro Formatting.
Solver Add-In	A tool that helps you to use a variety of numeric methods for equation solving and optimization.	Choose Data Analysis Solver.

You can also download additional Excel add-ins from http://office.microsoft.com.

45.3. Why 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:

• Avoid confusion. If an end user loads your application as an add-in, the file isn't visible in the Excel window — and, therefore, is less likely to confuse novice users or get in the way. Unlike a hidden workbook, an add-in can't be unhidden.

• 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 have to use a syntax such as the following to use this function in a different workbook:

  =NEWFUNC.XLSM!MOVAVG(A1:A50)

  
  

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

  =MOVAVG(A1:A50)

  
  

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

• Permit better loading control. You can automatically open add-ins when Excel starts, regardless of the directory in which they're stored.

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

45.4. 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, 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.

These steps describe how to create an add-in from a workbook:

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

45.5. An Add-In Example

This section discusses the steps to create a useful add-in from the change case.xlsm workbook I cover in Chapter 41. 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.

Figure 45.2. This dialog box enables the user to change the case of text in the selected cells.

45.5.1. Setting up the workbook

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.

Choose Insert Module in the VB Editor to insert a VBA module (named Module1). Choose Insert UserForm to insert a UserForm (named UserForm1).

45.5.2. Procedures in Module1

The two macros that follow are contained in the Module1 code module. The ShowUserForm 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.

The ChangeCaseOfText procedure is a special callback procedure, with one argument, that is executed from a control on the Ribbon. See "Creating the user interface for your add-in macro," later in this chapter. This procedure simply executes the ShowUserForm procedure.

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

Sub ChangeCaseOfText(ByVal control As IRibbonControl)
    Call ShowUserForm
End Sub

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

Figure 45.3. The custom dialog box.

The UserForm1 object contains the event-handler procedures for the two CommandButton objects that are on the form. The following procedure is executed when the OK button is clicked. This procedure does all the work:

Private Sub OKButton_Click()
    CaseChangerDialog.Hide
    Application.ScreenUpdating = False

'   Upper case
    If OptionUpper Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbUpperCase)
        End If
        Next cell
    End If
'   Lower case
    If OptionLower Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbLowerCase)
        End If
        Next cell
    End If
'   Proper case
    If OptionProper Then
        For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = StrConv(cell.Value, vbProperCase)
        End If
        Next cell
    End If
    Unload UserForm1
End Sub

The following procedure is executed if the user clicks the Cancel button:

Private Sub CancelButton_Click()
    Unload UserForm1
End Sub

45.5.4. Testing the workbook

Before you convert this 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 workbook, close it, and then reopen it. With the workbook open, then activate a different workbook, select some cells that contain text, and then press Alt+F8 to display the Macros dialog box. Execute the ShowUserForm macro and try all the options.

45.5.5. Adding descriptive information

Adding descriptive information is recommended but not necessary. Choose File Info Properties Show Document Panel to display the Properties panel below the Ribbon. 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.

Figure 45.4. Use the Document Properties panel to enter descriptive information about your add-in.

45.5.6. Protecting the project

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:

Figure 45.5. The Project Properties dialog box.

45.5.7. Creating the add-in

To save the workbook as an add-in, switch to the Excel window and activate your workbook. Then choose File Save As. Select Microsoft Excel Add-In (*.xlam) from the Save as Type drop-down list. Enter a name for the add-in file and then click OK. By default, Excel saves the add-in in your AddIns directory, but you can choose a different directory if you like.

45.5.8. Creating the user interface for your add-in macro

At this point, the add-in is created, but it's missing one key component: a way to execute the macro that displays the UserForm. Here are three ways to do this:

• Customize your Quick Access toolbar (or the Ribbon) to include a new command. See Chapter 23 for details.

• Create an old-style (pre–Excel 2007) CommandButton. See the sidebar, "Modify the User Interface the Old Way."

• Modify the add-in file so that it adds a new command to the Ribbon when the add-in is opened. I discuss this topic briefly in Chapter 23, but complete details are beyond the scope of this book.

Private Sub Workbook_Open()
    Set NewMenuItem = Application.CommandBars("Worksheet Menu Bar") _
        .Controls("Tools").Controls.Add
    With NewMenuItem
        .Caption = "Change Case of Text..."
        .BeginGroup = True
        .OnAction = "ChangeCaseofText"
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Tools"). _
        Controls("Change Case of Text...").Delete
End Sub

45.5.9. Installing the add-in

Now it's time to try the add-in. Choose File Excel Options Add-Ins. Select Excel Add-ins from the Manage drop-down list, and click Go.

Excel displays its Add-Ins dialog box. Click the Browse button and locate the change case.xlam add-in that you just created. After you do so, the Add-Ins dialog box displays the add-in in its list. Notice that the information that you provided in the Properties panel appears here. Click OK to close the dialog box and open the add-in.