Part VI: Programming Excel with VBA
904
The following are some typical uses for Excel add-ins:l Store one or more custom worksheet functions. When the add-in is loaded, you can
use the functions like any built-in worksheet function.
l 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.
l 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 addi-
tion, 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- Choose File ➪ Options.
- In the Excel Options dialog box, select the Add-Ins category.
- 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