Creating Add-Ins

If you create some custom functions that you use frequently, you may want to store these functions in an add-in file. A primary advantage to this is that you can use the functions in formulas in any workbook without a filename qualifier.

Assume that you have a custom function named ZAPSPACES and that it's stored in Myfuncs.xlsm. To use this function in a formula in a workbook other than Myfuncs.xlsm, you need to enter the following formula:

 =Myfuncs.xlsm!ZAPSPACES(A1:C12) 

If you create an add-in from Myfuncs.xlsm and the add-in is loaded, you can omit the file reference and enter a formula like the following:

 =ZAPSPACES(A1:C12) 

Creating an add-in from a workbook is simple. The following steps describe how to create an add-in from a normal workbook file:

1. Develop your functions, and make sure that they work properly.

2. Activate the VB Editor and select the workbook in the Project window. Choose Tools xxx Properties and click the Protection tab (where xxx corresponds to the name of your project). Select the Lock Project for Viewing checkbox and enter a password (twice). Click OK.

   You need to do this step only if you want to prevent others from viewing or modifying your macros or custom dialog boxes.

3. Reactivate Excel. Choose Office Prepare Properties, and Excel displays its Document Properties panel above the Formula bar. Enter a brief, descriptive title in the Title field and a longer description in the Comments field.

   This step is not required, but it makes the add-in easier to use by displaying descriptive text in the Add-Ins dialog box.

4. Choose Office Save As Other Formats.

5. In the Save As dialog box, select Excel Add-In (*.xlam) from the Save as Type dropdown list (see Figure 23-11).

   
   Figure 23-11: Saving a workbook as an add-in.

   A Few Words about Passwords

   Microsoft has never promoted Excel as a product that creates applications with secure source code. The password feature provided in Excel is sufficient to prevent casual users from accessing parts of your application that you want to keep hidden. However, the truth is that several password-cracking utilities are available. The security features in Excel 2002 and later are much better than those in previous versions, but it's possible that these can also be cracked. If you must absolutely be sure that no one ever sees your code or formulas, Excel is not your best choice as a development platform.

6. If you don't want to store the add-in in the default directory, select a different directory.

7. Click Save. A copy of the workbook is saved (with an .xlam extension), and the original macro-enabled workbook (.xlsm) remains open.

Caution 

When you use functions that are stored in an add-in, Excel creates a link to that add-in file. Therefore, if you distribute your workbook to someone else, they must also have a copy of the linked add-in. Furthermore, the add-in must be stored in the exact same directory because the links are stored with complete path references. As a result, the recipient of your workbook may need to use the Data Connections Edit Links command to change the source of the linked add-in.

After you create your add-in, you can install it by using the standard procedure:

1. Choose Office Excel Options Add-Ins.

2. Select Excel Add-ins from the Manage box.

3. Click Go, to show the Add-Ins dialog box.

4. Click the Browse button in the Add-Ins dialog box.

5. Locate your *.xlam file.