How to put a tooltip on a user-defined function

Solution 1:

Not a tooltip solution but an adequate workaround:

Start typing the UDF =MyUDF( then press CTRL + Shift + A and your function parameters will be displayed. So long as those parameters have meaningful names you at-least have a viable prompt

For example, this:

=MyUDF( + CTRL + Shift + A

Turns into this:

=MyUDF(sPath, sFileName)

Solution 2:

Professional Excel Development by Stephen Bullen describes how to register UDFs, which allows a description to appear in the Function Arguments dialog:

Function IFERROR(ByRef ToEvaluate As Variant, ByRef Default As Variant) As Variant
    If IsError(ToEvaluate) Then
        IFERROR = Default
    Else
        IFERROR = ToEvaluate
    End If
End Function

Sub RegisterUDF()
    Dim s As String
    s = "Provides a shortcut replacement for the common worksheet construct" & vbLf _
    & "IF(ISERROR(<expression>), <default>, <expression>)"

    Application.MacroOptions macro:="IFERROR", Description:=s, Category:=9
End Sub

Sub UnregisterUDF()
    Application.MacroOptions Macro:="IFERROR", Description:=Empty, Category:=Empty
End Sub

From: http://www.ozgrid.com/forum/showthread.php?t=78123&page=1

To show the Function Arguments dialog, type the function name and press CtrlA. Alternatively, click the "fx" symbol in the formula bar:

Solution 3:

I know you've accepted an answer for this, but there's now a solution that lets you get an intellisense style completion box pop up like for the other excel functions, via an Excel-DNA add in, or by registering an intellisense server inside your own add in. See here.

Now, i prefer the C# way of doing it - it's much simpler, as inside Excel-DNA, any class that implements IExcelAddin is picked up by the addin framework and has AutoOpen() and AutoClose() run when you open/close the add in. So you just need this:

namespace MyNameSpace {
    public class Intellisense : IExcelAddIn {
        public void AutoClose() {
        }
        public void AutoOpen() {
            IntelliSenseServer.Register();
        }
    }
}

and then (and this is just taken from the github page), you just need to use the ExcelDNA annotations on your functions:

[ExcelFunction(Description = "A useful test function that adds two numbers, and returns the sum.")]
public static double AddThem(
    [ExcelArgument(Name = "Augend", Description = "is the first number, to which will be added")] 
    double v1,
    [ExcelArgument(Name = "Addend", Description = "is the second number that will be added")]     
    double v2)
{
    return v1 + v2;
}

which are annotated using the ExcelDNA annotations, the intellisense server will pick up the argument names and descriptions.

There are examples for using it with just VBA too, but i'm not too into my VBA, so i don't use those parts.

Solution 4:

I just create a "help" version of the function. Shows up right below the function in autocomplete - the user can select it instead in an adjacent cell for instructions.

Public Function Foo(param1 as range, param2 as string) As String

    Foo = "Hello world"

End Function

Public Function Foo_Help() as String

Foo_Help = "The Foo function was designed to return the Foo value for a specified range a cells given a specified constant." & CHR(10) & "Parameters:" & CHR(10)
& "  param1 as Range   :   Specifies the range of cells the Foo function should operate on." & CHR(10)
&"  param2 as String  :   Specifies the constant the function should use to calculate Foo"
&" contact the Foo master at [email protected] for more information."

END FUNCTION

The carriage returns improve readability with wordwrap on. 2 birds with one stone, now the function has some documentation.