Using the Insert Function Dialog Box

Excel's Insert Function dialog box is a handy tool that enables you to choose a particular worksheet function from a list of available functions. The Insert Function dialog box also displays a list of your custom worksheet functions and prompts you for the function's arguments.

Note 

Custom Function procedures defined with the Private keyword don't appear in the Insert Function dialog box. This option is useful if you create functions that are intended to be used by other VBA procedures rather than in a formula.

By default, custom functions are listed under the User Defined category, but you can have them appear under a different category. You also can add some text that describes the function.

Adding a Function Description

When you select one of Excel's built-in functions in the Insert Function dialog box, a brief description of the function appears (see Figure 23-3). You may want to provide such a description for the custom functions that you create.

Figure 23-3: Excel's Insert Function dialog box displays a brief description of the selected function.

Note 

If you don't provide a description for your custom function, the Insert Function dialog box displays the following text: No help available.

The following steps describe how to provide a description for a custom function:

1. Create your function in the VB Editor.

2. Activate Excel and choose Developer Code Macros (or press Alt+F8). The Macro dialog box lists available Sub procedures but not functions.

3. Type the name of your function in the Macro Name box. Make sure that you spell it correctly.

4. Click the Options button to display the Macro Options dialog box. If the Options button is not enabled, you probably spelled the function's name incorrectly.

5. Enter the function description in the Description box (see Figure 23-4). The Shortcut key field is irrelevant for functions.

   
   Figure 23-4: Provide a function description in the Macro Options dialog box.

6. Click OK and then click Cancel.

Note 

When you use the Insert Function dialog box to enter a function, the Function Arguments dialog displays after you click OK. For built-in functions, the Function Arguments dialog displays a description for each of the function's arguments. Unfortunately, providing argument descriptions for your custom functions is not possible.

Specifying a Function Category

Oddly, Excel does not provide a direct way to assign a custom function to a particular function category. If you want your custom function to appear in a function category other than User Defined, you need to execute some VBA code in order to do so.

For example, assume that you've created a custom function named COMMISSION, and you'd like this function to appear in the Financial category (that is, Category 1) in the Insert Function dialog box. To accomplish this, you need to execute the following VBA statement:

 Application.MacroOptions Macro:="COMMISSION", Category:=1 

One way to execute this statement is to use the Immediate window in the VB Editor. Figure 23-5 shows an example. Just type the statement and press Enter. Then save the workbook, and the category assignment is also stored in the workbook. Therefore, this statement needs to be executed only one time. In other words, it is not necessary to assign the function to a new category every time the workbook is opened.

Figure 23-5: Executing a VBA statement that assigns a function to a particular function category.

Alternatively, you can create a Sub procedure and then execute the procedure.

 Sub AssignToFunctionCategory()     Application.MacroOptions Macro:="COMMISSION", Category:=1 End Sub 

After you've executed the procedure, you can delete it.

You will, of course, substitute the actual name of your function, and you can specify a different function category. The AssignToFunctionCategory procedure can contain any number of statements-one for each of your functions.

Table 23-1 lists the function category numbers that you can use. Notice that a few of these categories (10–13) normally don't display in the Insert Function dialog box. If you assign your function to one of these categories, the category then appears.

Table 23-1: FUNCTION CATEGORIES
Open table as spreadsheet

Category Number	Category Name
0	All (no specific category)
1	Financial
2	Date & Time
3	Math & Trig
4	Statistical
5	Lookup & Reference
6	Database
7	Text
8	Logical
9	Information
10	Commands
11	Customizing
12	Macro Control
13	DDE/External
14	User Defined
15	Engineering