CHAPTER

Creating Custom

Excel Add-Ins

903

Part VI: Programming Excel with VBA

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

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

1.Choose File Options.

2.In the Excel Options dialog box, 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.

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.

Tip

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

Caution

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

904

Chapter 45: Creating Custom Excel Add-Ins

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-Ins Included with Excel

The following table lists the add-ins included with Excel 2010. Some add-ins may not have been installed. If you try to use one of these add-ins and it’s not installed, you receive a prompt asking whether you want to install it.

905

Part VI: Programming Excel with VBA

Note

If you open an add-in created in a version prior to Excel 2007, any user interface modifications made by the add-in will not appear as they were intended to appear. Rather, you must access the user interface items (menus and toolbars) by choosing Add-Ins Menu Commands or Add-Ins Custom Toolbars. n

Tip

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

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

906