How to put a tooltip on a user-defined function

Question

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

User · Answer

Unfortunately there is no way to add Tooltips for UDF Arguments.
To extend Remou's reply you can find a fuller but more complex approach to descriptions for the Function Wizard at
http://www.jkp-ads.com/Articles/RegisterUDF00.asp

User · Answer

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

User · Answer

will s method is the best  Just add few lines about the details for the people didn t use ExcelDNA before like me   Download Excel-DNA IntelliSense from https   github com Excel-DNA IntelliSense releases  There are two version  one is for 64  check your Excel version  For my case  I m using 64 version   Open Excel Developer Add-Ins Browse and select ExcelDna IntelliSense64 xll   Insert a new sheet  change name to  IntelliSense   add function description  as https   github com Excel-DNA IntelliSense wiki Getting-Started  Then enjoy

User · Answer

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   amp  vbLf        amp   IF ISERROR  lt expression gt     lt default gt    lt expression gt         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 amp page 1  To show the Function Arguments dialog  type the function name and press CtrlA  Alternatively  click the  fx  symbol in the formula bar

User · Answer

I tried  ScottK s approach  first as a side feature of my functional UDF  then as a standalone  Help suffix version when I ran into trouble  see below   In hindsight  the latter approach is better anyway--more obvious to a user attentive enough to see a tool tip  and it doesn t clutter up the functional code   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   In the meantime  I tried the Application MacroOptions approach  Pretty easy  and it looks professional  Just one problem to work out  I ll post a separate answer on that approach later

User · Answer

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

User · Answer

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

User · Answer

Also you can use  this Macro to assign Descriptions to arguments and the 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   Credits to Kendall and the original post here  For the UDF Categories

User · Answer

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    amp  CHR 10   amp   Parameters    amp  CHR 10   amp     param1 as Range       Specifies the range of cells the Foo function should operate on    amp  CHR 10   amp    param2 as String      Specifies the constant the function should use to calculate Foo   amp   contact the Foo master at master foo com for more information    END FUNCTION   The carriage returns improve readability with wordwrap on  2 birds with one stone  now the function has some documentation

[excel] How to put a tooltip on a user-defined function

Examples related to excel

Examples related to vba