Application.MacroOptions method (Excel)
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.
Syntax
expression.MacroOptions (Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile, ArgumentDescriptions)
expression A variable that represents an Application object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| 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 is 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 the same as a built-in name (see list in Remarks section), Excel maps 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. |
| ArgumentDescriptions | Optional | Array | A one-dimensional array that contains the descriptions for the arguments to a UDF that are displayed in the Function Arguments dialog box. |
Remarks
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 |
Example
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
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.