VBA MacroOptions to Add UDF Description

Introduction - VBA MacroOptions

Use VBA MacroOptions to add a description for your user-defined functions. These descriptions will appear with your UDF in the Excel Function Wizard dialog box.

Example - VBA MacroOptions

Let’s say you’ve added my Linear Interpolation UDF, Linterp, to your Excel spreadsheet, but you can’t remember what it does or what each argument is. You can add a description for the UDF using the Application.MacroOptions function, so the descriptions will show up in the Excel Functions Wizard dialog box.

Here’s a demonstration of how you could register details about the Linterp UDF:

Sub RegisterUDF()
Dim strFunc As String   'name of the function you want to register
Dim strDesc As String   'description of the function itself
Dim strArgs() As String 'description of function arguments

    'Register Linterp linear interpolation function
    ReDim strArgs(1 To 3) 'The upper bound is the number of arguments in your function
    strFunc = "Linterp"
    strDesc = "2D Linear Interpolation function that automatically picks which range " & _
              "to interpolate between based on the closest KnownX value to the NewX " & _
              "value you want to interpolate for."
    strArgs(1) = "1-dimensional range containing your known Y values."
    strArgs(2) = "1-dimensional range containing your known X values."
    strArgs(3) = "The value you want to linearly interpolate on."
    Application.MacroOptions Macro:=strFunc, _
                             Description:=strDesc, _
                             ArgumentDescriptions:=strArgs, _
                             Category:="My Custom Category"
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Tutorial - VBA MacroOptions

What you'll see

Run the RegisterUDF macro and type “=Linterp” into a cell in Excel. Once you do that, click the “fx” button in your formula bar, like this:

The Function Wizard dialog box will appear. This dialog box will show a general description of the function you registered and a description of each of its arguments.

MacroOptions Modifications

You’ll notice I defined 3 variables at the top of the RegisterUDF macro.

1. strFunc - The name of the function you want to register
2. strDesc - A description of what the function does
3. strArgs - An array containing the description of each of the function’s arguments

The first two variables are self-explanatory, but the third variable can trip people up.

If your UDF has 3 arguments, you’ll need to dimensionalize the variable strArgs with 3 elements and add a description for each argument. I do this with the ReDim command, but you can do it during your initial declaration if you want.

Just take a closer look at the example macro if you’re not sure what I mean.

You may be asking why I defined 3 variables but the Application.MacroOptions line has 4 arguments. I did not define a variable for the Category argument of MacroOptions, although I could have.

You have a lot of options with the Category argument. In my example, I created a brand new category called “My Custom Category.” You can see the function in this new category when you hit Shift+F3 from Excel. (Alternately, you can click the “Insert Function” button on the Formulas tab on the top ribbon to bring up the same dialog box).

You don’t have to place your UDFs in new categories. As a matter of fact, you don’t have to include the Category argument at all when calling MacroOptions.

If you omit the Category argument entirely, your custom function will appear in a new category titled “User Defined.”

You can also place your UDFs in existing categories. For example, set the Category argument equal to “Engineering” and it will be added to the list of existing Engineering functions.

In addition, each of these categories is assigned an integer that you can refer to instead of a string, but I’m not going to get into that here. I like referring to the strings so I don’t have to memorize or count!

Bonus Tip

You may be wondering if it’s possible to display a brief description of your UDF as you type its name in a cell, like what shows up when you type an existing worksheet function:

You also may be wondering if you can show a list of the arguments in a tooltip popup, like what happens as you’re typing a native worksheet function:

Well, I hate to be the bearer of bad news, but the answer is no.

But all is not lost! Although not quite as convenient, you can display the names of your arguments by pressing Ctrl-Shift-A after typing the name of your UDF. Take a look:

This key combination displays each of the arguments in your formula bar, which isn’t quite as useful as the tooltip popup, but it’s certainly better than nothing!

Application Ideas

You never have to worry about forgetting the order of your UDF arguments again. Just register your functions when your workbook opens and the descriptions will be there when you need them the most.

I think of the MacroOptions function argument descriptions like comment cards in my VBA procedures. I may never need them, but if I do, I’ll be thankful they’re there!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.