Alan,
I would beg to differ on both counts.
> The first is "There is no Cell (or Cells) object in Excel."
There is no object named Cells. There is a property named Cells whose type
is Range. Since the output of Cells is always a Range, it will behave as
Range, but that doesn't mean it is indeed a Range range or any other sort of
object. To make the analogy in VBA,
' in Class1
Public Property Get TheProperty() As Class2
Set TheProperty= New Class2
End Property
This doesn't make "TheProperty" an object. It is still just a property but
will have all the properties and methods of Class2, so it has the look and
feel of Class2, but it can't be considered an object.
> Can anyone envision circumstances in which one could go wrong by
> disbelieving this "truism?
I would suspect that the definition of "object" would include the ability to
create a variable of that type. You can't declare a variable as type Cell or
Cells.
Dim R As Range ' good
Dim C As Cells ' no good
> With this definition, Range("MyRange") is clearly a collection object, as
> tested by Range("MyRange").Item(3).Value, which returns the value of the
> third element of the range.
Actually, it only does with contiguous ranges. But what about,
?Range("A1,B10,C20").Item(3).Address
This refers to A3 not C20, and that item is clearly not in the "collection"
Range itself.
By any standard definition of a real Collection object, the elements can be
accessed by a positive integer as an index into the Collection. As shown
above, you can access via Item objects that are not in the collection
itself. Moreover, no real collection I know of accepts negative values as
the index. But the Item property of Range quite happily accepts negative
numbers:
?Range("D4").Item(-1,-1).Address
No other "collection" supports this sort of negative indexing.
Finally, when you use syntax like
Range("C3")(1,2)
you are not going through the "Item" property, you are going through the
"_Default" property, which is marked as the default property of a Range
object. Turn on "Show Hidden Members" in the Object Browser and you'll see
_Default marked as the default property.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Alan Beban" <unavailable> wrote in message
news:(E-Mail Removed)...
> It's a nice sunny day and I'm sitting here with no place to go for the
> next half hour, so I thought I'd try to engender some controversy over 2
> xl "truisms".
>
> The first is "There is no Cell (or Cells) object in Excel." I quite
> understand that it is not documented as such by Microsoft, but my
> conclusion is that if it looks like an object, waddles like an object and
> quacks like an object, well . . . . The functionality of Excel seems to
> treat Cells in every way (except the documentation) as an object with the
> same properties and methods as any range. Can anyone envision
> circumstances in which one could go wrong by disbelieving this "truism?
>
> The second is "Ranges are not collections". In considering this I spent
> some time trying to find a concise definition of a collection. I have
> found a number of discussions and several descriptions, but no concise
> definition by which one could functionally test the statement. I propose:
> Collections are container objects whose members are a group of like
> objects that are accessible by means of the collection's Item Method.
> With this definition, Range("MyRange") is clearly a collection object, as
> tested by Range("MyRange").Item(3).Value, which returns the value of the
> third element of the range. Can anyone envision circumstances in which
> one could go wrong relying on the above definition? What is a concise
> alternative?
>
> Have a nice day,
> Alan Beban
Similar Threads
