The Range object is a strange bird. There was an interesting discussion on the newsgroups the other day about default properties. What is the default property of the Range object?
Range is an object. Range is also a collection of objects – kind of. Dana DeLouis pointed out this knowledgebase article which says:
In Microsoft Visual Basic for Applications, if an object has a Value property, then this property is the default value for the object. If an object does not have the Value property, then that object does not have a default value.
The Item method is the default value for a collection in the same way that the Value property is the default value for those objects that have the Value property. Note that you can use the Item method with any collection
Well Range is an object with a Value property, so that must be the default. It’s easily proved by going to the Immediate Window and typing
The result is the Value property. But wait, look at the tooltip provided when the Cells property is used
This says “_Default(…” which would seem to indicate that Item is the default property for Cells, not Value. But Cells isn’t an object, it’s just a property that returns a Range object.
In the aforementioned newsgroup thread, Harlan said:
It seems pretty clear to me that either the VBA parser has added logic for handling Range objects or there are separate default properties for scalar and object contexts, in which case .Value is the default property in scalar context and .Cells is the default property in object context, and Cells is a collection object, so its default property is .Item.
That sounds like a pretty good explanation to me. Whoever designed the Range object did one heck of a job, in my opinion. It always works just like I want.
One more quirk about the “collection” we know as Range. With most collection objects, specifying an Item that isn’t in the collection causes an error. Not always with Range however. Take this example
Using the Item property, we just returned B12 which isn’t even in the original Range. It seems that the Item property applies to some phantom collection that includes every cell in the Worksheet. Even stranger, if you try to access a cell beyond row 65,536, you don’t get the typical “subscript out of range” error like with normal collections. Rather, you get “Application defined or object defined error.”
I really enjoy discussing these abstract aspects of Excel. If you get something practical out of this post, you’re a better person than me.