How to put a tooltip in a user-defined function

Question

In Excel 2007, how to add description and parameter hints for a user-defined function? When I start to enter a function call for a built-in function, Excel displays a description and a list of parameters - a tooltip. I would like to do the same for the functions that I define.

Not only for the paste formula wizard, but also in the formula field, so if I "=myFun(" , in "(" tooltip appears just like for "=average("

There is no help in VBA help, there is not a single one on MSDN and none of the highlighted Excel and VBA forums that I can find, so this is definitely a long way to go.

Answer 1

Excel Professional Development Stephen Bullen describes how to register UDF, which allows the description to appear in the Arguments Dialog function:

 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 display the Function Arguments dialog, enter a name for the function and press Ctrl A. Alternatively, click the "fx" symbol in the formula bar:

Answer 2

Not a hint solution, but an adequate workaround:

Start by typing UDF =MyUDF( , then press CTRL + Shift + A and your function parameters will be displayed. As long as these parameters have meaningful names, you at least have a viable prompt

For example, this:

=MyUDF( + CTRL + Shift + A

Inclusion in this:

=MyUDF(sPath, sFileName)

Answer 3

I just create a "help" version of the function. It is displayed on the right under the function in autocomplete - the user can select it in the next 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 [e-mail] for more information." END FUNCTION 

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

Answer 4

I know that you have accepted the answer for this, but now there is a solution that allows you to get the intellisense style completion window, as for other excel functions, either through an Excel-DNA add-on or by registering an intellisense server inside your own add. See here .

Now I prefer the C # way to do this - it’s much easier, as in Excel-DNA, any class that implements IExcelAddin is picked up by the addin framework and has AutoOpen() and AutoClose() when you open / close the add-on. You just need to:

 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 ExcelDNA annotations for 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 ExcelDNA annotations, the intellisense server will display the names and descriptions of the arguments.

There are examples of using it only with VBA, but I don’t get into my VBA too much, so I don’t use these parts.

Answer 5

Unfortunately, there is no way to add tooltips for UDF arguments.
To extend Remou's answer, you can find a more complete, but more complex approach to the descriptions for the Function Wizard in
http://www.jkp-ads.com/Articles/RegisterUDF00.asp

Answer 6

A lot of dancing around the answer. You can add contextual help to UDF, but you must export the Module and edit the contents in a text editor, and then re-import it into VBA. Here is an example from Chip Pearson: adding code attributes

Answer 7

You can also use this macro to assign descriptions to arguments and UDF:

 Private Sub RegisterMyFunction() Application.MacroOptions _ Macro:="SampleFunction", _ '' Your UDF name Description:="calculates a result based on provided inputs", _ Category:="My UDF Category", _ '' Or use numbers, a list in the link below ArgumentDescriptions:=Array( _ '' One by each argument "is the first argument. tell the user what it does", _ "is the second argument. tell the user what it does") End Sub 

Kendall Credits and the original post here . For UDF Category

Answer 8

The @will method is the best. Just add a few lines about the details so people don’t use ExcelDNA earlier than me.

Download Excel-DNA IntelliSense from https://github.com/Excel-DNA/IntelliSense/releases

There are two versions, one for 64, check the version of Excel. For my case, I use 64 versions.

Open Excel / Developer / Add-Ins / Browse and select ExcelDna.IntelliSense64.xll.

Insert a new sheet, change the name to "IntelliSense", add a description of the function, https://github.com/Excel-DNA/IntelliSense/wiki/Getting-Started

Then enjoy! :)

Answer 9

I tried the @ScottK approach, first as a side feature of my functional UDF, then as a standalone version of the HELP suffix when I ran into a problem (see below). Looking back, the latter approach is better in any case - more obvious to the user, attentive enough to see the tooltip, and it does not clutter up the functional code.

I realized that if an inattentive user just typed the name of the function and closed the parentheses when he thought about it, help will appear and he will be on the way. But dumping a bunch of text into one cell, which I cannot format, did not seem like a good idea. Instead, when a function is entered into a cell with no arguments ie

  = interpolateLinear() or = interpolateLinear_Help() 

msgBox opens with help text. MsgBox is limited to ~ 1000 characters, maybe 1024. But that's enough (barely 8 ^ /) for my overly deceived interpolation function. If this is not the case, you can always open the user form and go to the city.

When you first open the message box, it looked like a success. But there are a couple of problems. First, of course, the user must know to enter a function without arguments (+1 for the _Help UDF suffix).

The big problem is that msgBox reopens several times in a row, spontaneously, working in unrelated parts of the book. Needless to say, this is very annoying. Sometimes this continues until I get a circular warning. Go figure. If UDF can change the cell formula, I would do this to close it.

I do not know why Excel considers the need to recalculate the formula again and again; neither the standalone nor the full version (in help mode) have precedents or dependents. There is no application. Of course, the function returns the value to the calling cell. Maybe this causes a repeat? But what UDF do. I do not think you cannot return the value.

Since you cannot change the worksheet formula from UDF, I tried to return a specific row - value - to the calling cell (the only one you can change the value from UDF), indicating that I will check the cell value with application.caller in the next loop , point my line and do not recount the helper message. It seemed like a good idea at the time - it didn't work. Perhaps I did something stupid in my sleep deprived state. I still like the idea. I will update this when (if) I fix the problem. My quick solution was to add a line to the help field: “Seek help only in an emergency. Remove the out-of-order formula to end poverty.

In the meantime, I tried Application.MacroOptions approach. Pretty easy, and he looks professional. Just one problem to develop. I will post a separate answer to this approach later.