Tool-tip for a User defined function

[rkspeaks]

Hi Chandoo and all Ninjas,


Good evening all of you.. meeting you all after a longtime.


Today my questions are :


1. How can I add a Tool-tip for a User Defined Function created by me. Is that possible in VBA for Excel?? If Yes, Just give me some example code in terms of where to place it in a Code window??


2. What is the best place to keep our custom functions(UDFs) in the Project Explorer window so that they will be available for all the workbooks I use?


By the way, I am using Excel 2010.


Regards

RK

 

[SirJB7]

Hi, rkspeaks!

I think that doing that directly is not possible, but you could give a try with Application.MacroOptions starting with the built-in help provided by Excel. I've never done it before by myself but I remember having read that this was the way to achieve your goal.

Regards!

 

[Luke M]

Perhaps this thread will help:

http://stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function

 

[rkspeaks]

Thank you. Luke. I will try that link and come back later, if any issues.

 

[Vegan55]

1. How can I add a Tool-tip for a User Defined Function created by me. Is that possible in VBA for Excel?? If Yes, Just give me some example code in terms of where to place it in a Code window??

I've been searching for the answer to that and everything I have found (including from a Microsoft Expert) is that you can't create an actual tooltip at this time -- though Microsoft is talking about making this available.

However, you've been able to create a module description and function description for a long time and this information is shown in the Insert Function and Function Arguments dialog boxes [fx].

To do this part you get into the VBA editor and press F2 for the Object Browser. Select VBAProject from the first dropdown. Then right-click your class(es), select Properties, and enter & save a description for the class. Repeat that process for each member of your class. These are saved as hidden properties in your module (which you can see if you export the module and use Notepad to view the text file).

Then, at least as of Excel 2010, you can now also create argument descriptions and those will also be shown in the Insert Function and Function Arguments dialog boxes [fx]. The following is an example subroutine you can create in your module to describe each member's arguments.

Sub DescribeArguments()
' One-time execution just to describe arguments for user-defined functions.
    Dim FuncName As String, Category As String, ArgDesc(1 To 4) As String
'
    FuncName = "CountByColor"
    ArgDesc(1) = "The reference cell for selection color."
    ArgDesc(2) = "The range of cells to count or sum."
    ArgDesc(3) = "Boolean 'true' to select by font color, or 'false' to select by cell color (default)."
    ArgDesc(4) = "Boolean 'true' to sum selected cell values, or 'false' to just count the selected cells (default)."
'
    Application.MacroOptions _
        Macro:=FuncName, _
        ArgumentDescriptions:=ArgDesc
'
End Sub

2. What is the best place to keep our custom functions(UDFs) in the Project Explorer window so that they will be available for all the workbooks I use?

Custom functions are only available to the workbook in which they are created. However, you can modify the default Excel template and put your functions in there. Then, each time you create a new workbook the custom functions will be added to that workbook as part of the template used to create the workbook.

 

[Debaser]

though Microsoft is talking about making this available.

Sadly, I don't think that is true.

 

[Vegan55]

Just going by what they said.

 

[Debaser]

I don’t doubt that, I’m just saying I don’t think that is the case now. Although I am certain VBA will be supported for many years to come, Microsoft’s investment in it these days sadly is the bare minimum required to support new Excel functions, not adding value to VBA itself, since their focus is clearly on technology that works on all platforms not just the desktop.

 

[vletm]

Pete Wright
As You remember based Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Start a new post every time you ask a question, even if the theme is similar.