Microsoft Office Tutorials and References
In Depth Information
Using the Insert Function Dialog Box
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. Type 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.
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. Assigning a function to a
category also causes it to appear in the drop-down controls in the Formulas
Function Library group.
For example, assume that you’ve created a custom function named REMOVESPACES , and you’d
like this function to appear in the Text category (that is, Category 7) in the Insert Function dialog
box. To accomplish this, you need to execute the following VBA statement:
Application.MacroOptions Macro:=”REMOVESPACES”, Category:=7
One way to execute this statement is to use the Immediate window in the VB Editor. If the
Immediate window is not visible, choose View
Immediate Window (or press Ctrl+G). 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.