User defined functions (UDF’s) are by default added to the “User Defined” category in the function list, and when a UDF is selected the words “no help available” appear, rather than a description of the function and it’s parameters. A rather complex way of getting the full functionality of the built-in function descriptions is given at: Registering a User Defined Function with Excel. In this post I will describe a much simpler method to achieve the same end, but with the restriction that the total number of characters in the description must be less than 256.
The simple method uses a four column table in the workbook containing the functions as shown below:
The columns are:
- The function name
- The category name
- A 1 on any continuation lines
- The function description, with descriptions of each parameter on a separate line
Assign the range name “functionlist” to the list, excluding the column headings.
The macro listed below will create the category names and add the function descriptions to the function list:
Sub FuncDescriptions()
Dim FunctionA As Variant, NumRows As Long, NL As Long
Dim FuncName As String
Dim Descript As String
Dim Cat As Variant
Dim i As Long, j As Long
FunctionA = Range("functionlist").Value
NumRows = UBound(FunctionA)
On Error Resume Next
With Application
i = 1
Do While i <= NumRows
If NL = 0 Then
FuncName = FunctionA(i, 1)
Cat = FunctionA(i, 2)
Descript = FunctionA(i, 4)
Else
Descript = Descript & vbCrLf & FunctionA(i, 4)
End If
If i < NumRows Then NL = FunctionA(i + 1, 3)
If NL = 0 Or i = NumRows Then
.MacroOptions Macro:=FuncName, Description:=Descript, Category:=Cat
End If
i = i + 1
Loop
End With
End Sub
To run the macro automatically add:
Private Sub Workbook_Open()
FuncDescriptions
End Sub
under “ThisWorkbook”
Example code has been added to the String Functions worksheet here: StringFunctions.zip
Nice post, thanks for referring to my article.
One tiny problem. In Excel 2003 the function wizard shows only 3 lines of text, so in your examples only the descriptions of the first two arguments are shown.
LikeLike
Jan – Thanks for the comment. I guess this is just one more reason to switch to 2007 😉
LikeLike
One of few if you ask me 🙂
LikeLike