Microsoft Office Tutorials and References
In Depth Information
Creating Add-Ins for Functions
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 file-
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:
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:
A few words about passwords
Microsoft has never promoted Excel as a product that creates applications with secure source code. The pass-
word 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 more recent versions of Excel are much better than those in previous versions, but it's possible that
these can also be cracked. If you must be absolutely sure that no one ever sees your code or formulas, Excel is
not your best choice as a development platform.
Creating an add-in from a workbook is simple. The following steps describe how to create an add-in from a nor-
mal 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 check box and enter a password (twice). Click OK.
You need to do this last step only if you want to prevent others from viewing or modifying your macros or
custom dialog boxes.
3. Reactivate Excel. Choose File ⇒ Info ⇒ Properties ⇒ Show Document Panel, and Excel displays its Document
Properties panel above the Formula bar. Enter a brief, descriptive title in the Title field and a longer descrip-
tion in the Comments field.
This last 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 File ⇒ Save As.
5. In the Save As dialog box, select Excel Add-In (*.xlam) from the Save as Type drop-down list.
6. If you don't want to store the add-in in the default directory, select a different directory.
7. Click Save.