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 with the workbook 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 named DISCOUNT in a workbook named 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 possibly some 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:
After you create the functions you need, click File, Save As.
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.)
After you save the workbook, click File, Options.
In the Excel Options dialog box, click the Add-Ins category.
In the Manage drop-down list, select Excel Add-Ins. Then click the Go button.
In the Add-Ins dialog box, select the check box beside the name you used to save your workbook.
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 28-5 shows, in the Visual Basic Editor Project Explorer under a 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.