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?

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , , , . Bookmark the permalink.

5 Responses to Making Sense of Cells Intellisense – Part 1

  1. Pingback: Making Sense Of Cells Intellisense – Part 2 | RAD Excel

  2. Chris Spicer says:

    Hi Colin, this is a great article. It’s nice to see someone cover these always used but often overlooked methods. I’ve put together a summary of how to create your own default methods at http://www.technicana.com/index.php?option=com_content&view=article&id=228&catid=36

    Regards Chris

    Like

  3. aardvark1234 says:

    I’ve noticed that ActiveSheet. also doesn’t bring up intellisense. Does that mean that ActiveSheet is also a Variant? Or (more likely) because ActiveSheet could be a worksheet or a chart sheet or a macro sheet?

    Like

    • Colin Legg says:

      Hi,

      That’s a great observation: yes, ActiveSheet also does not bring up intellisense. If you search for it in the object browser you’ll find that ActiveSheet is a property of the Application, Window and Workbook classes, as well as being a member of Globals. The object browser also tells us that, in all cases, it returns a generic Object type (which could hold a reference to an instance of any class) so we don’t get intellisense.

      So why was it designed like that? Well, it’s exactly as you supposed. In the Excel object model there isn’t a “Sheet” class and the Sheets object collection can hold different types such as charts, dialogsheets and worksheets. It’s only dynamically at runtime that we’ll know which of these the ActiveSheet is.

      Like

  4. Guzman says:

    Beautifully explained article!
    Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s