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

Did you find this helpful?

Please support wellsr.com by purchasing an add-in below. Your support helps me continue to post VBA tutorials like this one.

$15

Mouse to Macro

Record your mouse clicks and cursor movements and convert them directly into VBA macros so you can automate your mouse.

$50

wellsrPRO

Import hundreds of macros from wellsr.com without ever leaving Excel and organize your personal macro library with this best-selling add-in.

$50

CF Shapes

Build stunning dashboards by dynamically controlling your shapes with this add-in that enables conditional formatting for shapes.



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!

Check out more VBA Tutorials

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.

Excel VBA Store