SOLVED

Add help info to UDF

Copper Contributor

Hi everyone, just a quick question here... It is possible to add help information to a UDF to let the user know how the UDF works and what the possible errors are, and the data is needed for the UDF to function properly?

 

I want Excel to display that help info like any native formula. If possible...

 

Appreciate any help you can give me.

 

 

9 Replies
best response confirmed by hugo francisco jiménez novoa (Copper Contributor)
Solution

@hugo francisco jiménez novoa 

You can use Application.MacroOptions. Here is a very simple example:

Function CelsiusToFahrenheit(t As Double) As Double
 CelsiusToFahrenheit = 1.8 * t + 32
End Function

Sub AddDescriptiom()
    Application.MacroOptions Macro:="CelsiusToFahrenheit", _
       Description:="Convert Celsius to Fahrenheit", _
       ArgumentDescriptions:=Array("Temperature in degrees Celsius")
End Sub

Running the AddDescription macro adds a description of the function as a whole and of its arguments. These will be used if you press fx when creating a formula:

S1473.png

@Hans Vogelaar extraordinary...! I will try it. Thank you so much...

@Hans Vogelaar 

Looks good !

But doesn't work for me  :(

@RobinClay 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

Sorry for the delay ! And thank you for your interest.
I'm afraid this new technology is still byond me ! I can't see how to attach a file, nor how to Paste an image . . .
However . . I copied your UDFs, and the first works fine.
But if I click on the fx, and the window comes up, it bears the line
"Ni help available", and if I click on the bottom left Link "Help on this function", another window pops up with "=the same message.
And Yes, I corrected the spelling ! changed the final "m" to an "n" in your "Sub AddDescriptiom("

@RobinClay 

If you wish, you can send me a Private Message in this forum. You should be able to attach a sample workbook there.

IN-COMING !

@RobinClay 

I have received your PM. This is what I get when I run the AddDescription macro, then click fx in cell B3:

S1645.png

I have attached the workbook.

Thank you.
And it works just fine for me - as does my version - now !

Since I tried it, & sent it to you, I have had to re-installed the entire "365", due to problems with Outlook (still has problems)

Thank you for your help, MUCH appreciated.
1 best response

Accepted Solutions
best response confirmed by hugo francisco jiménez novoa (Copper Contributor)
Solution

@hugo francisco jiménez novoa 

You can use Application.MacroOptions. Here is a very simple example:

Function CelsiusToFahrenheit(t As Double) As Double
 CelsiusToFahrenheit = 1.8 * t + 32
End Function

Sub AddDescriptiom()
    Application.MacroOptions Macro:="CelsiusToFahrenheit", _
       Description:="Convert Celsius to Fahrenheit", _
       ArgumentDescriptions:=Array("Temperature in degrees Celsius")
End Sub

Running the AddDescription macro adds a description of the function as a whole and of its arguments. These will be used if you press fx when creating a formula:

S1473.png

View solution in original post