Part VI: Automating Excel
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:
- Develop your application, and make sure that everything works properly.
- (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 identi-
fying the add-in easier.
- (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
➪ Properties (where corresponds to your VB proj-
ect 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.
- 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 save the workbook as an add-in, the original (non-add-in) workbook remains active. If you’re going to install
the add-in and test it, you should close this file to avoid having two macros with the same name.
After you create the add-in, you need to install it:
