Making Sense of Cells Intellisense – Part 1

A friend of mine asked me a good question today:

Why is it that when I type Cells. into the VBA IDE I get Intellisense whereas when I type something like Cells(1). or Cells(1,1). I don’t?

Being a strong VBA programmer he wants to use Intellisense whenever possible whilst writing code and is frustrated by the lack of it in the latter examples.

I remember delving into the Range and Cells properties a few years ago with another Microsoft Excel MVP, Mike Rosenblum, and, given that these properties are bread and butter for any Excel programmer, they have a few hidden surprises which very few people know about. In short, a lot of work has been done under the hood to make these properties extremely flexible.

There are a few Excel classes which have Range and Cells properties but, for the purposes of this discussion, it makes no difference which one we use so we’ll choose the Worksheet class. First of all, let’s pick an example which gives us Intellisense:

When you type Sheet1.Cells. into Excel’s VBA IDE you will get Intellisense (provided you have Tools > Options > Auto List Members set to True and there are no compile errors in your code) which is great because you don’t have to remember or guess what you should type in next. It’s a very valuable tool. So why do we get Intellisense in this case? Well, we can confirm in the Object Browser that the Cells property returns a Range object:

It returns an Excel.Range object and the Excel Object library is referenced by the project, so Intellisense can give us the members of the Excel.Range class at design time. Okay, so far so good.

 Now let’s look at another example which doesn’t give Intellisense:

Argh, no Intellisense! But why?!

Well, the final call we make before the last full-stop isn’t actually the Cells property. We can see in the previous screenshot of the object browser that the Cells property doesn’t accept any arguments, yet in our (fully legitimate) code we are passing in two 1s.

Cells doesn’t accept any arguments so when we use this syntax in VBA, since Cells returns a Range object, we are actually calling the default member of the Range class. Due to misinformation in the VBA help files, a lot of Excel VBA users think that the default members of the Range class are the Value and Item properties; however (perhaps with  rare exceptions) a class can only ever have one default member so this cannot be correct.

When you type Sheet1.Cells( into the VBA IDE, provided that you have Tools > Options > Auto Data Tips set to True, you will see this data tip:

The data tip suggests that we are using a mysterious property called _Default which has two optional parameters called RowIndex and ColumnIndex. With hidden members shown, the VBA IDE object browser confirms that the _Default property is the default member of the Range Class:

For anyone who’s so inclined, the OleView.exe OLE/COM object browser shows the following definition for Range._Default:

[id(00000000), propget, helpcontext(0x00010000)]
VARIANT _Default(
            [in, optional] VARIANT RowIndex,
            [in, optional] VARIANT ColumnIndex);

[id(00000000), propput, helpcontext(0x00010000)]
void _Default(
            [in, optional] VARIANT RowIndex,
            [in, optional] VARIANT ColumnIndex,
            [in] VARIANT rhs);

The underscore prefix in the _Default property name is a standard convention to indicate that the property is hidden. It also means that if you were ever to actually type this property into the VBA IDE, you would have to surround it with [ ].

 I’ll dive into the logic of Range._Default, its signature and its design in a later blog post so, for now, the most important things to note are that the _Default property returns a Variant type – not a Range object type – and that when you type Sheet1.Cells(1,1) into the VBA IDE it is a short-hand way of writing Sheet1.Cells.[_Default](1,1).

We now have an explanation as to why we don’t get Intellisense when using Sheet1.Cells(1,1). The _Default property returns a Variant: we won’t know until runtime what object or literal it returns so we can’t be given an auto list at design time.

I hope that makes sense?