User-Defined Function Argument Descriptions In Excel 2010
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.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks