UDF Descriptions

[adam087]

Hi All

So I just discovered this little snippet at the following web site from John Walkenbach...
Spreadsheet Page Excel Tips

Although many of you may have seen it before, I felt obliged to share because it's previously been a real annoyance for me that I've not been able to provide ToolTip help to users for UDFs, and I wondered if this little extra bit in the 2010 version might help other people.

This will still not provide in-line IntelliSense like information when typing the UDF in to a worksheet, but it's deffo a step forward.

Below is an example of how I implemented it. I ran it once on a workbook before hiding the sub (changing to Private) and then saving as an AddIn. Worked a charm.

Public Sub DescribeFunction_SALULOOKUP()
    Dim FuncName As String
    Dim FuncDesc As String
    Dim Category As String
    Dim ArgDesc(1 To 5) As String
    
    FuncName = "SALULOOKUP"
    FuncDesc = "Merges pairs of names to joint salutations given a common key"
    Category = 7 'Text category
    ArgDesc(1) = "is a unique key upon which to group salutations"
    ArgDesc(2) = "is the Range in which the names are to be found, where sGroupOn will be found in the left-most column"
    ArgDesc(3) = "is the column number in rList which contains the Titles of the individuals"
    ArgDesc(4) = "is the column number in rList which contains the Surnames of the individuals"
    ArgDesc(5) = "is a symbol to use between the names or titles, default is '&'"
    
    Application.MacroOptions _
        Macro:=FuncName, _
        Description:=FuncDesc, _
        Category:=Category, _
        ArgumentDescriptions:=ArgDesc
        
End Sub

Hope that helps someone else out there!
/AJ

 

[ParamRay]

.
.

I've read about the MacroOptions method before and it's definitely useful to know about.


May I also add:

The following table lists which integers are mapped to the built-in categories that can be used in the Category parameter.

1 - Financial
2 - Date & Time
3 - Math & Trig
4 - Statistical
5 - Lookup & Reference
6 - Database
7 - Text
8 - Logical
9 - Information
10 - Commands
11 - Customizing
12 - Macro Control
13 - DDE/External
14 - User Defined
15 - Engineering
16 - Cube
17 - Compatibility


And:

You can also create custom function categories. Instead of using a number for the Category argument for MacroOptions, use a text string.

Application.MacroOptions Macro:="MyMacro", Category:="My Category"