Is there a way to make UDF that gives a description like the native Excel functions have when user clicks the Fx button?

If I type =vlookup( (or any other native Excel function) in the formula bar and then click the little Fx button to the left of the formula I get a Function Arguments prompt with all the available arguments. In that prompt, below the functional arguments, is a one or two sentence description of the function and of each argument as you move your cursor from each argument's input box.

When I type in the name of my UDF and click the Fx I get an input box for all of my arguments but that is it. Is there a way I can add those same helpful type of descriptions that native Excel functions have?

Answers


Yes, there is a somewhat hidden way to do that:

After you defined your UDF in VBA, go to the Object Browser in the Visual Basic Editor (F2). Here, in the top drop down, select VBAProject. In the window below, navigate to your UDF and right click it - select Properties:

In the properties, you can provide the description.

If you need further information, e.g. how to add the function to a certain category check out this OzGrid article!


Type =FormulaName( into a cell and then press Ctrl+Shift+A and it will fill in the reference name of the arguments


I suggest further investigating the Application.MacroOptions method. That method allows you to provide not only a description for the function, but also a description for each of the arguments. For example, if you have a function called "SampleFunction" that takes two arguments, you can run the following and it will set you up nicely for using the fx button with your function.:

Private Sub RegisterMyFunction()
    Application.MacroOptions _
        Macro:="SampleFunction", _
        Description:="calculates a result based on provided inputs", _
        Category:="My UDF Category", _
        ArgumentDescriptions:=Array( _
            "is the first argument.  tell the user what it does", _
            "is the second argument.  tell the user what it does")
End Sub

EDIT

I just noted you are creating UDFs in VBA thus my reply may not be applicable to your situation.


Have a look at one of the sample projects in Excel SDK called Generic.

It is a bare minimum skeleton to build upon

Right after the header include statements, you will notice a declaration of a two dimensional array where the rows represent the number of UDFs in your XLL and the columns are used for the description of the particular UDF

The first column is used for the name of UDF followed by the a string that contains the letters that represent the data type of each parameter in your UDF.

These columns may be used to put description text for each of your parameters in UDFs

The number of columns are determined by the UDF that has the largest number of parameters and the UDFs that have fewer parameters use empty strings as values that are beyond the number of parameters in such UDFs

But then these descriptions will be displayed in the dialog box that pops up when your click on Fx icon


Create function like I have created below: Function CompanyNames:

Function CompanyNames(CompanyCode As Integer, Data As Range) As String

CompanyNames = WorksheetFunction.VLookup(CompanyCode, Data, 2, False)


End Function

Run below code once and you get the argument description in Function

Sub DescribeFunction()

Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 2) As String

FuncName = "CompanyNames"
FuncDesc = "Returns the Company Name"

ArgDesc(1) = "Provide the Company Code"
ArgDesc(2) = "Select Range to lookup"


Application.MacroOptions _
  Macro:=FuncName, _
  Description:=FuncDesc, _
  Category:=Category, _
  ArgumentDescriptions:=ArgDesc
End Sub

Need Your Help

Is Loop Hoisting still a valid manual optimization for C code?

c++ c optimization gcc

Using the latest gcc compiler, do I still have to think about these types of manual loop optimizations, or will the compiler take care of them for me well enough?

vb .net, select statement, default

vb.net

In C I can assign default to some existing switch entry: