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 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 the same as a built-in name (see list in Remarks section), Microsoft 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. |
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