How to put a tooltip on a user-defined function

In Excel 2007, how do I add a description and parameter hints to a user-defined function? When I start typing a function invocation for a built-in function, Excel shows a description and parameter list–a tooltip. I’d like to do the same for the functions I define.

Not just for the formula insert wizard, but in the formula box, so if I key “=myFun(“, at the “(” the tooltip pops up just like it does for “=average(”

There’s no help in VBA Help, none on MSDN and none on any of the Excel and VBA dedicated forums I can find, so this is clearly a long shot.

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)

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.

A lot of dancing around the answer. You can add the UDF context help, but you have to export the Module and edit the contents in a text editor, then re-import it to VBA. Here’s the example from Chip Pearson: Adding Code Attributes

I figured if an inattentive user just typed the function name and closed the parentheses while he thought it over, help would appear and he would be on his way. But dumping a bunch of text into a single cell that I cannot format didn’t seem like a good idea. Instead, When the function is entered in a cell with no arguments i.e.

= interpolateLinear()
or
= interpolateLinear_Help()

a msgBox opens with the help text. A msgBox is limited to ~1000 characters, maybe it’s 1024. But that’s enough (barely 8^/) for my overly tricked out interpolation function. If it’s not, you can always open a user form and go to town.

The first time the message box opened, it looked like success. But there are a couple of problems. First of course, the user has to know to enter the function with no arguments (+1 for the _Help suffix UDF).

The big problem is, the msgBox reopens several times in succession, spontaneously while working in unrelated parts of the workbook. Needless to say, it’s very annoying. Sometimes it goes on until I get a circular reference warning. Go figure. If a UDF could change the cell formula, I would have done that to shut it up.

I don’t know why Excel feels the need recalculate the formula over and over; neither the _Help standalone, nor the full up version (in help mode) has precedents or dependents. There’s not an application.volatile statement anywhere. Of course the function returns a value to the calling cell. Maybe that triggers the recalc? But that’s what UDFs do. I don’t think you can not return a value.

Since you can’t modify a worksheet formula from a UDF, I tried to return a specific string –a value –to the calling cell (the only one you can change the value of from a UDF), figuring I would inspect the cell value using application.caller on the next cycle, spot my string, and know not to re-display the help message. Seemed like a good idea at the time–didn’t work. Maybe I did something stupid in my sleep-deprived state. I still like the idea. I’ll update this when (if) I fix the problem. My quick fix was to add a line on the help box: “Seek help only in an emergency. Delete the offending formula to end the misery.

Leave a Reply

Your email address will not be published. Required fields are marked *