MacroOptions Method [Excel 2003 VBA Language Reference]

Corresponds to options in the Macro Options dialog box. You can also use this method to display a user defined function (UDF) in a built-in or new category within the Insert Function dialog box.

expression**.MacroOptions(Macro**, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile)

expression Required. An expression that returns an Application object.

Macro   Optional Variant. The macro name or the name of a user defined function (UDF).

Description   Optional Variant. The macro description.

HasMenu   Optional Variant. This argument is ignored.

MenuText   Optional Variant. This argument is ignored.

HasShortcutKey   Optional Variant. True to assign a shortcut key to the macro (ShortcutKey must also be specified). If this argument is False, no shortcut key is assigned to the macro. If the macro already has a shortcut key, setting this argument to False removes the shortcut key. The default value is False.

ShortcutKey   Optional Variant. Required if HasShortcutKey is True; ignored otherwise. The shortcut key.

Category   Optional Variant. An integer that specifies an existing macro function category (Financial, Date & Time, or User Defined, for example). See the remarks section to determine the integers that are mapped to the built-in categories. You can also specify a string for a custom category. If you provide a string it will be treated as the category name that is displayed in the Insert Function dialog box. If the category name has never been used, a new category is defined with that name. If you use a category name that is that same a built-in name, Excel will map the user defined function to that built-in category.

StatusBar   Optional Variant. The status bar text for the macro.

HelpContextId   Optional Variant. An integer that specifies the context ID for the Help topic assigned to the macro.

HelpFile   Optional Variant. The name of the Help file that contains the Help topic defined by HelpContextId.


The following table lists which integers are mapped to the built-in categories that can be used in the Category parameter.

Integer 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 First custom category
16 Second custom category
17 Third custom category
18 Fourth custom category
19 Fifth custom category
20 Sixth custom category
21 Seventh custom category
22 Eighth custom category
23 Ninth custom category
24 Tenth custom category
25 Eleventh custom category
26 Twelfth custom category
27 Thirteenth custom category
28 Fourteenth custom category
29 Fifteenth custom category
30 Sixteenth custom category
31 Seventeenth custom category
32 Eighteenth custom category


This example adds a user-defined macro called "TestMacro" to a custom category named "My Custom Category". After you run this example, you should see "My Custom Category" which contains the "TestMacro" user-defined function in the Or select a category drop-down list in the Insert Function dialog box.

Function TestMacro()
    MsgBox ActiveWorkbook.Name
End Function

Sub AddUDFToCustomCategory()
    Application.MacroOptions Macro:="TestMacro", Category:="My Custom Category"
End Sub

Applies to | Application Object