Making Sense Of Cells Intellisense – Part 2

I’m going to follow on from my last post by demonstrating a VBA IDE Intellisense bug when using the Cells property. In my earlier post I used the Worksheet.Cells property but here I’m going to use Range.Cells to avoid Out of Memory runtime errors.

Typing in Range("A1:A2").Cells()(). into the VBA IDE gives us an auto list of the Excel.Range class:

It shouldn’t. VBA’s loose syntax means that Range("A1:A2").Cells() with no arguments passed in is the equivalent of Range("A1:A2").Cells. We established in the last post that the _Default property is the default member of the Range class and therefore, since Range.Cells returns a Range object, the confusing- looking Range("A1:A2").Cells()() is the equivalent of Range("A1:A2").Cells.[_Default](). We can confirm this with a little test:

Sub Test()
    Debug.Print TypeName(Range("A1:A2").Cells) 'Range
    Debug.Print TypeName(Range("A1:A2").Cells()) 'Range
    Debug.Print TypeName(Range("A1:A2").Cells.[_Default]) 'Variant()
    Debug.Print TypeName(Range("A1:A2").Cells().[_Default]) 'Variant()
    Debug.Print TypeName(Range("A1:A2").Cells()()) 'Variant()
     'and just for completeness...
    Debug.Print TypeName(Range("A1:A2").Cells.[_Default]()) 'Variant()
    Debug.Print TypeName(Range("A1:A2").Cells().[_Default]()) 'Variant()
End Sub

So, Intellisense tells us that it is perfectly legitimate to write something like this:

But if we complete the line to make it into a Let call and try to run it, we get an Object Required runtime error because, as previously noted, Intellisense is wrong: Cells()() returns a Variant or a Variant array, not a Range object.

This is a extremely unusual situation and I recommend that you always (well, excluding fringe cases such as the one above) take advantage of and trust Intellisense when you are writing code.

About Colin Legg

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

1 Response to Making Sense Of Cells Intellisense – Part 2

  1. Pingback: A Guided Tour Through The VBA IDE’s Options | RAD Excel

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s