Excel 2010 Bible

Chapter 45: Creating Custom Excel Add-Ins

907

Creating Add-Ins

Technically, you can convert any workbook to an add-in. Not all workbooks benefit from this con-

version, 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 work-

book makes an ideal add-in.

These 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 ➪
   Properties ➪ Show Document Panel. When the Properties panel above your worksheet
   appears, enter a brief descriptive title in the Title field, and then enter a longer descrip-
   tion in the Comments field. Although this step isn’t required, it makes installing and
   identifying the add-in easier. To close the Document Properties panel, click its close
   button (X).


3. (Optional) Lock the VBA project. This step protects the VBA code and UserForms from
   being viewed. You do this in the VB Editor; choose Tools ➪ projectname Properties
   (where projectname 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 in your AddIns directory. You can override this location and choose any directory
   you like.

Note
After you save the workbook as an add-in, the original (non–add-in) workbook remains active. You should
close this file to avoid having two macros with the same name. n

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 to display
   the Add-Ins dialog box.


3. In the Add-Ins dialog box, click the Browse button to locate the XLAM file that you
   created, which installs the add-in. The Add-Ins dialog box uses the descriptive title that
   you provided in the Properties panel.