Newton Excel Bach, not (just) an Excel Blog

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:

Click for full-size view

The columns are:

1. The function name
2. The category name
3. A 1 on any continuation lines
4. 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