Using Function Procedures on Worksheets

Function procedures you can use to replace long worksheet formulas in a worksheet cell — just as you use built-in worksheet functions — are called user-defined worksheet functions.

For example, suppose that the workbook named "MyBook" contains the function ConeSurface, which was defined in "Returning a Value from a Function" earlier in this chapter. If you want a cell on a worksheet in MyBook — a cell in any workbook that has a reference to MyBook — to contain the surface area of a cone with a radius of 5 and a height of 10, you should place the formula =ConeSurface(5,10) in the cell. If you want to use the ConeSurface function in a worksheet cell in a workbook that doesn't contain a reference to MyBook, you should precede the function name with the name of the workbook and an exclamation mark — for example, =MYBOOK.XLS!ConeSurface(5,10) — and MyBook must be open.

Note

You can call functions in loaded add-ins without opening the add-in workbook and without specifying the workbook name. For information about creating add-ins, see Chapter 12, "Creating Add-Ins."

A user-defined worksheet function must not change any data in a workbook or change the Microsoft Excel environment in ways such as the following:

• Inserting, deleting, or formatting cells
• Changing cell values
• Moving, renaming, deleting, or adding sheets
• Changing the calculation mode or screen view

In addition, names of procedures you intend to use as user-defined functions in Microsoft Excel cannot have names that resemble A1-style or R1C1-style cell addresses.

If you cannot remember the exact name of a user-defined function or the name or number of its arguments, you can use the Function Wizard to examine the function and its arguments.

To insert a user-defined function using the Function Wizard

1. On the Insert menu, click Function.

2. In the Function Category box, click User Defined.

3. In the Function Name box, click the name of the function you want to insert.

4. Follow the rest of the Wizard steps. Where appropriate, the Wizard will give you a chance to fill in arguments.

If you want a user-defined function to appear in a category other than User Defined, assign it to a different function category.

To specify a user-defined function's category

1. With a module active, click Object Browser on the View menu.

2. In the Methods/Properties box, click the function you want to specify a category for.

3. Click Options to display the Macro Options dialog box.

4. In the Function Category box, click the category where you want the function to appear.