Haphazardly Posted Excel Information and Other Stuff

Formula Arguments and Intellisense

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?

25 thoughts on “Formula Arguments and Intellisense”

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.

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.

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.

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:

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?

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 :(