Introduction | Example | Tutorial | Applications
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,
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's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
Tutorial - VBA MacroOptions
What you'll see
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.
You’ll notice I defined 3 variables at the top of the
- strFunc - The name of the function you want to register
- strDesc - A description of what the function does
- 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!
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!
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.