Excel VBA UDF Tool-tip/hint box

Question

Hello Everyone,

I am working on creating some UDFs in VBA, but I find it a bit deflating that I cannot create a tooltip as standard excel functions have, demsonstrated in the helow format:

Typed into a cell: =UDF

Excel shows (pops up next to the cell): =UDF[description]

Entered into a cell: =UDF**(** Excel shows (pops up next to the cell): =UDF(argument1,argument2,[argument3],...)

I have come to the conclusion that excel has not bestowed us with this honor yet, as a standard to the excel package- If I am wrong, PLEASE correct me.

Are there any python/add-in/etc. work around for this? The point is, I have quite a few arguments and quite a few UDFs, so it's really easy to get lost in everything (the units as well).

Thanks for reading/answering/sharing-my-pain,

Filip

Answer 1

Lets say you have a function like this:

Function MyFunc(MyValue As Variant, MyRange As Range, MyColumn As Long) ' code goes here ... ... End Function

1. When you type =MyFunc and press Tab, Excel will show =MyFunc(

If you then press Ctrl+Shift+A, Excel will enter the names of the arguments:

2. You can provide a description of the function and its arguments that will be used in the function wizard: Sub RegisterFunction() Application.MacroOptions _ Macro:="MyFunc", _ Description:="Looks up a value in a range", _ Category:="My UDF Category", _ ArgumentDescriptions:=Array( _ "is the value you're looking for", _ "is the lookup range", _ "is the column index of the return value") End Sub

Result when you invoke the function wizard fx:

Answer 2

Re: in addition to the HansV post

You also have to...

save the additional code

close Excel

reopen Excel

before the function wizard form will be displayed.

[Edit]... I just wish there was a way to align the displayed text to the left side.

'---
Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

Answer 3

Hey Hans,

Thanks for responding.

This is the method that I have been using, except, I've been using the immediate window (ctrl + g) and the following code:

Application.MacroOptions macro:="UDF", Description:="stringy", Category:="MyFavUDFs", ArgumentDescriptions:=Array("Argu1string","Argu1sting",...)

And to Nothing Left to Lose's point, in order for the object to update, I've got to save the VBA and Excel file (its on autosave so just the VBA file for me).

Though this an acceptable work-around, it's has no panache- very gray. And I detest the use of ctrl+a or ctrl+shirt+a. Its just another step and more lost time. I suppose the purpose of all of this is to minimize the amount of time spent going anything but filling out the excel sheets and balancing the process equations.

To the above point, the act of pressing ctrl+a, for each and every instance really does pile up.

Answer 4

NLtL,

Thanks for responding!

Try the method that is in my other reply, it should cut down on your non-value added time when creating these UDFs.

Answer 5

You can propose a suggestion to Microsoft using File > Feedback.