User-Defined Function Argument Descriptions In Excel 2010

Category: VBA Functions | [Item URL]

One of the new features in Excel 2010 is the ability to provide argument descriptions for user-defined functions. These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box.

Here's a simple (but very useful) user-defined function:

Function EXTRACTELEMENT(Txt, n, Separator) As String
     EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1)
End Function

Here's a VBA macro that provides a description for the EXTRACTELEMENT function, assigns it to a function category, and provides a description for each of its three arguments:

Sub DescribeFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 3) As String

   FuncName = "EXTRACTELEMENT"
   FuncDesc = "Returns the nth element of a string that uses a separator character"
   Category = 7 'Text category
   ArgDesc(1) = "String that contains the elements"
   ArgDesc(2) = "Element number to return"
   ArgDesc(3) = "Single-character element separator"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

You need to run this macro only one time. After doing so, the descriptive information is stored in the workbook (or add-in) that defines the function.

Here's how the function appears in the Function Arguments dialog box:

What about compatibility with earlier versions?

If the file is opened in Excel 2007, the argument descriptions are not displayed. If you save the workbook as an XLS file, the Compatibility Checker kicks in and tells you that the function descriptions will be removed.