Using Add-ins to Store Custom Functions

You may prefer to store frequently used custom functions in an add-in file. A primary advantage is that you can use those functions in any workbook.

In addition, you can use the functions in formulas 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 such as the following:

=ZapSpaces(A1:C12)

I discuss add-ins in Chapter 21.

A potential problem with using add-ins to store custom functions is that your workbook is dependent on the add-in file. If you need to share your workbook with a colleague, you also need to share a copy of the add-in that contains the functions.