Even though you’ve seen Excel’s Formula Intellisense a million times, I bet there’s a few things that you don’t know about it that can make your life even easier.
Let’s first look at an example for one of Excel’s in-built functions. Say we want to use an IFERROR function. As soon as we type the = sign in the toolbar and the opening ‘I’ from IFERROR, Excel provides a helpful list of functions that start with the letter ‘I’, as well as a pop-up tool tip telling us what the currently highlighted function from that list does. That popup tool tip is often in the way, but you can move it somewhere else simply by clicking on it and dragging it to the naughty corner.
To select the IFERROR function from that list, we can either use the arrow keys to navigate down that intellisense list, or we can left-click on the particular function we want:
…or we can continue typing, which allows Excel to gradually narrow down the number of functions until there is only one possible choice…the IFERROR function we’re after:
At this stage, I almost always push Enter in order to get Excel to populate this one remaining function in the intellisense list into the formula bar. And then I almost always swear out loud, because Enter is the incorrect key, and because Excel has never heard of an ife function, it asks me for my name, so that it can pass it on to the ‘re-education’ team at Microsoft:
The correct key we need to push is the TAB key, which tells Excel to fill out the rest of the IFERROR name for us. After it’s done this, Excel then helpfully prompts us for the two arguments of the IFERROR function with another handy pop-up tool-tip:
Now let’s compare that to the options Excel gives us for a UDF. I’ll use my JoinText UDF. Typing =j pops up a very short list indeed: there are no other functions starting with J. But the UDF’s name is all we get. There’s no tooltip remindingus what the JoinText function actually does…
…and after we select it by hitting that TAB key, there’s no tooltip prompting us for the function arguments:
So why don’t UDFs have intellisense? That’s a damn good question, and one that programmers have been asking Microsoft about for years. If you can’t remember the arguments for your UDF, you won’t be getting very far. Unless you happen to know this handy little trick: If you push CTRL + SHIFT + A at this stage, Excel helpfully inserts placeholders for all the argument names for that function into the formula bar:
…which you can then overtype with the actual arguments, now that you’ve been reminded what they are. How cool is that!
This trick also works with Excel’s native functions, too:
But the problem we still have with that UDF is this: Which arguments are optional? In the case of the VLOOKUP above, we can see this by the square brackets around the optional [range_lookup] argument in the tool-tip. (As if that argument is optional in the real world…)
But with our UDF, we don’t get that tool-tip pop-up.
Sure, we could just put the prefix opt in front of the optional arguments in the VBE:
…which would give us this:
…but where’s the fun in that?
It would be cool if we could simply add square brackets to our variable names back in the VBE, so that when we do our Ctrl + Shift + A trick, those arguments look like this:
…but it won’t compile:
So what other interesting characters can we use in VBA variable names? According to this thread, these ones:
Any character in the range 128-255 is allowable. That’s right, you can copy-write and trademark your variable names if you want!
And a couple of them look quite bracketish. Let’s try them:
Yup, that compiles. And how does it look when we Ctrl + Shift + A that sucker?
Hmmm. Works a treat.
Down side? Don’t know. You tell me…
You were right, I didn’t know that.
If we remember to press Tab, then Ctrl-shift-A, and then click the formula icon next to the formula bar we get a whole load of #Name messages in red down the right hand side, because our UDF variable names are not range names.
But if we create the range names, and add some helpful text to those ranges, we get some help text to go along with our helpful variable names.
Alternatively, we could put some typical data in those ranges, so we can remember what sort of data is required, and the dialog box will give us a typical result for the UDF.
A lot of possibilities there.
True, never though of that.
But you’d have to store your Named Ranges in a template I guess. And they’d have to have the same message an any other UDF. Food for thought, though.
Nice approach to use the brackets.
I thought we could use macro options for some missing information:
Haha, I always hit “Enter” instead of “Tab” and it always pisses me off because I have to start all over! Very cool trick with the Ctrl+Shift+A, I had never heard of that gem before!
Ctrl+Shift+A, that’s a very useful trick.
Thanks
Excel 2010 and later have the option to register descriptions of the arguments of a UDF using MacroOptions (like snb uses). Instead of CTRL+SHIFT+A you can use CTRL+A to get the function dialog (or just hit the fx button next to the formula box). It shows the required arguments in bold and the optional ones in err.., well, not bold.
An additional advantage of registering the UDF is that it changes the capitalization of the function in the worksheet formula to how the UDF was declared. So, if in a worksheet formula you would type “=jointext(” etc, it would cange that to “=JoinText(” etc, so you would know immediately if your UDF was recognized. That’s why it is a good habit to declare all your UDFs wit a mixture of capitals and lowercase.
Of course you should not hard-code the arguments for the MacroOptions sub, but put all the required information in a worksheet table, so it can be maintained easily and executed automatically.
Before Excel 2010 you could register the functions using a recipe from Laurent Longre, but:
a. it is tiresome end
b. I often read complaints that it isn’t reliable.
I can assure you that the Excel 2010 onwards method is both reliable and easy.
Let me know if you would like an example.
Niek Otten
I should have added that MacroOptions (Excel 2010 onwards) also gives you the option to add Help text for the UDF. The link to it can be handled in the same worksheet table as the other UDF information.
Hi snb & Niek. I’d missed that enhancement re registering arguments. Serves me right for not upgrading my Excel 2007 Power Programming with VBA when I upgraded from Excel 2007! Thanks for that.
After I’ve done this, here’s what I see in the Insert Function dialog, which is an improvement:
…and here’s an example of the Function Arguments:
…which strangely are all bolded, even though that first one is optional. As opposed to the VLOOKUP one, where the optional argument is not bolded:
To get the description of the optional argument unbolded, you have to leave out the type declaration.
See the difference in a worksheet:
Function test1(a As Integer, Optional b)
test1 = a * b
End Function
Function test2(a As Integer, Optional b As Integer)
test2 = a * b
End Function
Sub RegArg()
Application.MacroOptions macro:=”test1″
Application.MacroOptions macro:=”test2″
End Sub
Ah, awesome. I’m pleased you’re part of my extended hive mind, Niek!
I keep adding:
And when you omit the type declaration, you have to test for missing arguments yourself.
See the difference:
Function test1(a As Integer, Optional b)
test1 = a * b
End Function
Function test2(a As Integer, Optional b As Integer)
test2 = a * b
End Function
Function test3(a As Integer, Optional b As Integer)
If IsMissing(b) Then test3 = 9999 Else test3 = a * b
End Function
Function test4(a As Integer, Optional b)
If IsMissing(b) Then test4 = 9999 Else test4 = a * b
End Function
Sub RegArg()
Application.MacroOptions macro:=”test1″
Application.MacroOptions macro:=”test2″
Application.MacroOptions macro:=”test3″
Application.MacroOptions macro:=”test4″
End Sub
Jeff, your article was a revelation. Ctrl+Shift+A just may change my life.
Also, I am *TOTALLY* going to start using pilcrow’s in my variable names now. Like a BOSS.
Add away, Niek. I guess the downside of declaring as variant is speed. So I’d probably forego having my optional arguments bolded in UDFs, because you never know how many UDFs are going to get stuffed into someone’s spreadsheet. But very good to know.
Patrick: glad to have helped. My forthcoming book is stuffed to the brim with stuff like this. Those funny characters aren’t pilcrows by the way:
Jeff: Yes, I know, the pilcrow is that paragraph-marker thingy. Looking forward to the book!
Niek: Thanks for the reminder on MacroOptions!
I have been sort of aware of the MacroOptions option for some time, but I never actually use it, which is crazy considering the number of UDFs I use.
So thanks snb and Niek for the reminder; I will start doing this as a matter of course today.
Great tip, Jeff, I didn’t know about CTRL+SHIFT+A.
A bonus question for you. In Excel 2010, if I create this little UDF:
And then type in the formua
and press CTRL+SHIFT+A and then simply press ENTER to complete the formula, it returns 0. It doesn’t return #NAME? which means that Excel *knows* what ARG1 is. It’s not stored as a name, so what is ARG1 (other than being an Empty Variant) and where is it kept?
Jeff, right at the end you ask the question: down side? My immediate thought was: how annoying would it be to try and program when all your variable names start with a funky ASCII character? Or will autocomplete work if you start typing the remaining characters in the VBE?
For those who only recently discovered Excel: application.macrooptions was also available in at least Excel 2000. ;)
MacroOptions has existed for a while, but it didn’t become actually useful until Excel 2010.
You’re right Colin, it would be annoying. I just leaned the trick to inserting these with the keyboard: you hold down ALT and then type the character code, preceeded with a zero, and then let go of the alt. It even works in this comment box: Hold down ALT, type 0139, release Alt, and you get this:
‹
Jeff, on my laptop keyboard there’s the additional complication of having to hold down my Fn key to activate the numeric keyboard overlay because 1 to 0 across the top of the keyboard are no good for this purpose :(
So that’s ALT, Fn and type mjlo and release ALT, Fn for me (does anyone else have trouble seeing the Fn numeric keyboard overlays on their laptop? Maybe it’s just me!)
Colin, Isn’t ARG1 just a cell reference? If you select the formula you can press Ctrl+[ to take you to the cell (then press F5 and Enter to return).
Does anybody know how to unassign a UDF after using Application.MacroOptions?
I know that I can move it to another category, but how do I remove it from all categories as well as from “All”?
@Lori – BINGO! You’re a genius!
I use Application.MacroOptions to register my UDFs with the Insert Function and Function Arguments dialogs, but how can I define a popup list of String literal choices similar to Excel’s built-in functions INFO and CELL? When I type =INFO and press Tab, I see a list of literals like
“DIRECTORY” – Current directory or folder
“NUMFILE” – Active worksheets
…
plus a detachable ToolTip for each literal. Does anyone know how to create these using VBA for UDFs?
By the way, how can I add a screenshot (PNG) when I post a comment here?
You can’t add a screen shot. But here’s one
Also, I don’t know of any way to do that for your own functions.
Thanks for the screen shot. After a little more research here:
https://support.office.com/en-us/article/Use-Formula-AutoComplete-6D13DAA5-E003-4431-ABAB-9EDEF51FAE6B
I learned they now call it Formula AutoComplete with Enumerated Constants: “The following functions have arguments with enumerated constants that automatically display in the drop-down list: CELL, FV, HLOOKUP, MATCH, PMT, PV, RANK, SUBTOTAL, and VLOOKUP.” But they forgot INFO.
Does anyone know if there’s a method like MacroOptions to populate this enumeration?