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?
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
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