Microsoft Office Tutorials and References
In Depth Information
Dealing with the Insert Function Dialog Box
Following is an example of a procedure that uses the MacroOptions method to provide
information about a function.
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = “DrawOne”
FuncDesc = “Displays the contents of a random cell from a range”
FuncCat = 5 ‘Lookup & Reference
Arg1Desc = “The range that contains the values”
Arg2Desc = “(Optional) If False or missing, a new cell is not “
Arg2Desc = Arg2Desc & “selected when recalculated. If True, a “
Arg2Desc = Arg2Desc & “new cell is selected when recalculated.”
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc) End Sub
This procedure uses variables to store the various information, and the variables are used as
arguments for the MacroOptions method. The function is assigned to function category 5
(Lookup & Reference). Notice that descriptions for the two arguments are indicated by using an
array as the last argument for the MacroOptions method.
The ability to provide argument descriptions is new to Excel 2010. If the workbook
is opened in an early version of Excel, however, the arguments won’t display the
Figure 10-11 shows the Insert Function and Function Arguments dialog boxes after executing this
You need to execute the DescribeFunction procedure only one time. After doing so, the
information assigned to the function is stored in the workbook. You can also omit arguments. For
example, if you don’t need the arguments to have descriptions, just omit the
ArgumentDescriptions argument.
For information on creating a custom help topic accessible from the Insert Function
dialog box, refer to Chapter 24.
Search JabSto ::

Custom Search