To use a custom function, the workbook containing the module in which you create 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.xls, and you call that function from another workbook, you must write =personal.xls!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 (an XLA file) in your XLStart folder. (The XLStart folder is a subfolder of the folder containing your Excel files. When you start Excel, the program opens any documents it finds in XLStart.) To save a workbook as an add-in, choose File, Save As (or File, Save). Then choose Microsoft Excel Add-in from the Files Of Type list.

If your user-defined functions are stored in an XLA file that is present in memory, you don’t have to specify the name of that file when you call a function. If the XLA file is saved in your XLStart folder, it will be present in memory whenever you run Excel.