Making Your Custom Functions Available Anywhere

To use a custom function, the workbook containing the module in which you created the function must be open. If that workbook is not open, you get a #NAME? error when you try to use the function. Even if the workbook is open, if you use the function in a different workbook, you must precede the function name with the name of the workbook in which the function resides. For example, if you create a function called DISCOUNT in a workbook called Personal. xlsb and you call that function from another workbook, you must type =personal. xlsb!discount(), not simply =discount().

You can save yourself some keystrokes (and possible typing errors) by selecting your custom functions from the Insert Function dialog box. Your custom functions appear in the User Defined category:

An easier way to make your custom functions available at all times is to store them in a separate workbook and then save that workbook as an add-in. You can then make the add-in available whenever you run Excel. Here's how to do this:

1. After you have created the functions you need, click the Microsoft Office Button, and click Save As.

2. In the Save As dialog box, open the Save As Type drop-down list, and select Excel Add-In. Save the workbook under a recognizable name-such as MyFunctions-in the AddIns folder. (The Save As dialog box will propose that folder, so all you need to do is accept the default location.)

3. After you have saved the workbook, click the Microsoft Office Button, and click Excel Options.

4. In the Excel Options dialog box, click the Add-Ins category.

5. In the Manage drop-down list, select Excel Add-Ins. Then click the Go button.

6. In the Add-Ins dialog box, select the check box beside the name you used to save your workbook, as shown on the next page.

After you follow these steps, your custom functions will be available each time you run Excel. If you want to add to your function library, press Alt+F11 to return to the Visual Basic Editor. As Figure 27-5 shows, in the Visual Basic Editor Project Explorer under a

Figure 27-5: If you save your custom functions as an add-in, the code for those functions is available in a module in the Visual Basic Editor, and you can add more functions as the need arises.

VBAProject heading, you will see a module named after your add-in file. (Your add-in will have the extension.xlam.) Double-clicking that module in the Project Explorer causes the Visual Basic Editor to display your function code. To add a new function, position your insertion point after the End Function statement that terminates the last function in the Code window, and begin typing. You can create as many functions as you need in this manner, and they will always be available in the User Defined category in the Insert Function dialog box.