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
?Range("A1")
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.
Just palying around with this ‘cus its wried!
?range(“a1?).Item(70000).address
gives app error
?range(“a1:b1?).Item(70000).address
gives $B$35000 ????
and wiredest of all
?range(“a1:c1?).Item(70000).address
$A$23334!!!
it contiues, for example…
?range(“a1:g1?).Item(120000).address
$F$17143
I was expecting something like
(70000-65536 = 4464)
?range(“a1:b1?).Item(70000).address
gives $B$4464 ????
odd!
and wiredest of all
?range(“a1:c1?).Item(70000).address
$A$23334!!!
That’s because Item(2) is B1 and Item(3) is C1 – left to right before top to bottom. 70,000 / 3 = 23333 1/3. Item(69,999) would be C23333, and one more goes to A of the next row.
…. then item 4 will be a2,
when range = “a1? item 2 is a2,
and when range (“a1:b90?) item 3 is a2
so the range effects the rows, limts the number of rows in a “range”, but not the number of colums….? hummm
While some of the behaviour is certainly weird, there are useful aspects.
For example, I often find myself specifying a one-cell Range to indicate the top-left cell of an arbitrarily large output table. If I want to fill the table as I go, I can use
targetRange.Cells(row, column)
, which I find is more intention-revealing than
targetRange.Offset(offsetRows, offsetColumns)
My £0.02p…
The default property of the Range object is actually the hidden “_Default()” property. If you go to the object browser in VBA, right click and choose “Show Hidden Members”, you’ll see it.
The interesting thing with the _Default property is that it takes two optional arguments, RowIndex and ColumnIndex and returns another Range object, using the Item property. However, if you don’t pass in any parameters, it returns the value of the range instead.
Hence the seemingly confusing behaviour.
Now where it really does get confusing is when you consider the Columns(), Cells() and Rows() properties.
Ever wondered why the Count property returns the number of columns of a range which was obtained from the Columns property? After all, in a regular range, Count returns the number of cells.
The answer is that each Range object contains an internal flag which specifies if it’s a cells range, a columns range or a rows range. The behaviour of Count, Item and For-Each are then modified appropriately. Clever, really.
Another interesting point, which follows on from this, is that Cells, Columns and Rows don’t actually take arguments. Instead, they just return fresh Range objects and any arguments are then passed on to the new range’s _Default property.
Hi everyone, the default property of the Range object seems to be its positional reference, not its value property. We seem to confuse a range’s value in VBA with that of a range on a worksheet. The debugger window will only return something for (range).count, not for (range).value, whereas both the range count and the range value make sense in a worksheet.
We tend to think a range is determined by what it contains, rather than what it refers to. As soon as its rows/columns are deleted, we think it’s gone, but its main property which is its reference to a number of cells somewhere in the spreadsheet, is still there. ‘Killing’ a VBA range a million times over doesn’t change that.
Likewise, deleting a row in a spreadsheet does not reduce the total number of cells; no matter how much you cut, your sheet stays ‘spread out’. Long live the spreadsheet.
Thjere’s a more subtle explanation: the default property of a Range object is the Cells collection – but a cell has a default property: the Cell’s value.
What happens when you try to assign an object to a variant, instead of an object variable? The variant takes the object’s default property… And this is transitive: if the default property is an object, the variant takes that object’s default property.