Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - VBA MacroOptions

Use VBA MacroOptions to add a description for your user-defined functions. These desciptions 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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free


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:

Excel UDF Insert Functions Button

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.

VBA MacroOptions Function Wizard UDF Description


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).

VBA MacroOptions Function Wizard UDF New Category

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:

Excel Function Tooltip

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:

Excel Function Arguments List

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:

VBA UDF Ctrl-Shift-A Arguments

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!


Comments

I hope you’ll reach out to me via my VBA Consulting page if you’re working on a macro, but you’re too busy to get it done.

Please subscribe to my email list and share this article with your friends on Facebook, Twitter, and Google+.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some powerful automation products. Many of them are free!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.