Adding Function Categories and Descriptions

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

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)
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
End With
End Sub

To run the macro automatically add:

Private Sub Workbook_Open()
End Sub

under “ThisWorkbook”

Example code has been added to the String Functions worksheet here:

This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

3 Responses to Adding Function Categories and Descriptions

  1. 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.


  2. dougaj4 says:

    Jan – Thanks for the comment. I guess this is just one more reason to switch to 2007 😉


  3. One of few if you ask me 🙂


Comments are closed.