Microsoft Office Tutorials and References
In Depth Information
Describing Function Arguments
Describing Function Arguments
When you use Excel’s Insert Function dialog box to insert a built-in worksheet function, clicking
OK leads to the Function Arguments dialog box, which assists you with entering the arguments
for the function.
You can use the Insert Function dialog box to insert a custom VBA function in a formula, but the
Function Arguments dialog box doesn’t display descriptions for the arguments. However, a new
feature in Excel 2010 lets you create argument descriptions for your custom VBA functions.
To demonstrate how this works, I’ll use the EXTRACTELEMENT function, listed in Tip 207:
Function EXTRACTELEMENT(Txt, n, Separator) As String
‘ Returns the nth element of a text string, where the
‘ elements are separated by a specified separator character
Dim AllElements As Variant
AllElements = Split(Txt, Separator)
EXTRACTELEMENT = AllElements(n - 1)
End Function
To provide function descriptions for this function, you must create and execute a macro. The
following macro, when executed, provides a description for the function, plus a description for each
of the three arguments:
Sub SpecifyDescriptions()
Dim D0 As String, D1 As String
Dim D2 As String, D3 As String
D0 = “Returns a specified element from a string that uses a separator
D1 = “The text string from which you’re extracting”
D2 = “An integer that represents the element to extract”
D3 = “A single character used as the separator”
Application.MacroOptions _
Description:=D0, _
ArgumentDescriptions:=Array(D1, D2, D3)
End Sub
Figure 208-1 shows how the function appears in the Function Arguments dialog box. Note that
you need to run this macro only one time. The descriptions are stored in the workbook that
contains the function definition.
Search JabSto ::

Custom Search