Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

You might prefer to store frequently used custom functions in an add-in file. A primary advantage of doing this is that you can use the functions in any workbook.

In addition, the functions can be used 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)

CROSS-REFERENCE

I discuss add-ins in Chapter 21.

Caution

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.

Категории