Programming Excel with VBA and .NET

Name

Application.MacroOptions([Macro], [Description], [HasMenu], [MenuText], [HasShortcutKey], [ShortcutKey], [Category], [StatusBar], [HelpContextId], [HelpFile])

Synopsis

Sets the description and help files displayed for a macro or user-defined function.

Argument	Setting
Macro	The name of the macro to set.
Description	A description that appears in the Macro or Formula dialog box.
HasMenu	Ignored.
MenuText	Ignored.
HasShortcutKey	True assigns a shortcut key to the macro.
ShortcutKey	The shortcut key to assign.
Category	The name of a category for the user-defined function. Default is User Defined.
StatusBar	Ignored.
HelpContextId	The context ID for the help topic within the compiled help file. Ignored for other help file types.
HelpFile	The name of the help file to display for user-defined functions.

The usable arguments are different for macros (Subs) and user-defined functions (Functions). The Macro dialog box doesn’t use Category, HelpContextId, or HelpFile arguments. The Insert Function dialog box doesn’t use HasShortcutKey or ShortcutKey arguments.

The following code sets the options for the ShowXlOnTop user-defined function:

Sub TestMacroOptions(  )
    Application.MacroOptions "ShowXlOnTop", _
      "Set Excel as the top-most window.", , , , , _
      "Windows", "Excel On Top", , _
      "http:\\excelworkshop.com\Help\ch07.htm"
End Sub

After this code runs, Excel displays the options on the Insert Function dialog as shown in Figure 7-3.

Figure 7-3. How Excel ...