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

Question

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?

on the first page when googling `excel 2010 udf description`: http://www.jkp-ads.com/articles/RegisterUDF00.asp — SeanC, Feb 06 '13 at 16:22

score 36 · Answer 1 · answered Feb 06 '13 at 15:15

36

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

answered Feb 06 '13 at 15:15

JustinJDavies

2,663
4
30
52

Never knew this, very useful in general, +1 – bendataclear Feb 06 '13 at 15:17
1

Me neither! +1. Nice bike. – Doug Glancy Feb 06 '13 at 17:29
1

LIMIT on length of the names of arguments in "SampleFunction" = 254 - NumberOfArguments. E.g., given `Function SampleFunction( Num1, Num2, Num3 )`, `Len("Num1") + Len("Num2") + Len("Num3") + 3` must be <= 254. The length of each argument name can be quite long - at least 88 characters (I have not tested longer argument names). will show the full argument names in the Formula Bar. If you see `(..)` in the Formula Bar, you have exceeded this limit. – Rocky Scott Dec 04 '15 at 13:39

score 35 · Answer 2 · answered Feb 20 '14 at 19:27

35

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

answered Feb 20 '14 at 19:27

Kendall

351
3
2

this is the way to do it – kristen Mar 25 '14 at 01:17
LIMIT on length of the names of arguments in "SampleFunction" = 254 - NumberOfArguments. E.g., given `Function SampleFunction( Num1, Num2, Num3 )`, `Len("Num1") + Len("Num2") + Len("Num3") + 3` must be <= 254. The length of each argument name can be quite long - at least 88 characters (I have not tested longer argument names).The Function Arguments dialog box will expand properly to show the full argument names; and will show the full argument names in the Formula Bar. `..` in the Function Arguments dialog box or `(..)` in the Formula Bar, indicates you have exceeded this limit. – Rocky Scott Dec 04 '15 at 13:35
Note that the ArgumentDescriptions parameter doesn't work for versions prior to 2010. – Alex R. Oct 28 '16 at 21:10

score 29 · Accepted Answer · answered Feb 06 '13 at 15:15

29

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:

enter image description here

In the properties, you can provide the description.

enter image description here

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

answered Feb 06 '13 at 15:15

Peter Albert

16,917
5
64
88

you're genius!) +1 for the trick - for future use) (oh God I'm sooooooo ugly and still don't know soooooooo mush...... doh...) – Peter L. Feb 06 '13 at 15:19
That looks good but for some reason whenever I type something in the description and then click OK it doesn't save what I typed. Immediately after I hit OK I can go back to the properties and there's nothing there again. Any ideas what is causing that? – Dean MacGregor Feb 06 '13 at 15:52
I played around with different add-ins that I had some worked and some didn't work. I toggled public function to just function and that, in some instances. That isn't to say that it matters whether or not its public but something about removing "Public" may have made it work....very strange. – Dean MacGregor Feb 06 '13 at 17:08
4

I think i understood the problem: the UDF description is only loaded once during Excel runtime. This, if you looked at it before in the fx window, Excel will not change it until you restart it! – Peter Albert Feb 06 '13 at 17:52
3

@PeterAlbert - thank you for figuring out that problem... seems I don't have enough patience, and expected the change to occur **NOW** :) – SeanC Feb 06 '13 at 19:24

score 4 · Answer 4 · answered Apr 02 '18 at 12:11

4

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

answered Apr 02 '18 at 12:11

Nitin

41
1

1

While this is a fine answer, it isn't clear what it adds to the 4-year old answer of Kendall which also uses `Application.MacroOptions` – John Coleman Apr 02 '18 at 12:38
1

@John Coleman, It is a complete working example which makes it easier to understand. However, he could have edited Kendall's answer. – Stefan May 19 '22 at 09:56
Great answer, and the easiest one to follow as Stefan states. – rxex Dec 08 '22 at 15:54

score 3 · Answer 5 · answered Feb 06 '13 at 15:00

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

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

5 Answers5

Linked