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

? Range("A1:B1,A4:B4").Item(3).Address
A2

Unless you count a Range as a collection of Areas and not cells...

Tim

"Alan Beban" <unavailable wrote in message
...
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

For the sake of argument -

Cells IS documented as a Range object.

I don't equate the Range object with a collection at all. The range's 'Item'
property is effectively an offset from the first cell in the range
(constrained within the number of columns in the first area), eg

Range("A2,D3:E4").Name = "myRange"

For Each cel In Range("myRange")
i = i + 1
If i = 3 Then
MsgBox cel.Address ' E3
End If
Next

MsgBox Range("myRange").Item(3).Address ' A4

MsgBox Range("myRange").Item(0).Address ' A1
' Item - 0, in a collection !

Regards,
Peter T



"Alan Beban" <unavailable wrote in message
...
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

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
...
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

Hi Chip,

In the Object Browser, how does one turn on "Show Hidden Members"? (xl2002)

Thanks,
Alan

Chip Pearson wrote:
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.

Alan,
Right-click in the browser window, it is on the popup menu.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Alan Beban" <unavailable wrote in message
Hi Chip,
In the Object Browser, how does one turn on "Show Hidden Members"? (xl2002)
Thanks,
Alan

Thanks Jim and David

Jim Cone wrote:

Alan,
Right-click in the browser window, it is on the popup menu.

The easiest way is to rightclick on one of the Object browsers windows and
select it from the popup menu.

Alan Beban wrote:

Hi Chip,

In the Object Browser, how does one turn on "Show Hidden Members"? (xl2002)

Thanks,
Alan

Chip Pearson wrote:
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.



--

Dave Peterson

"Alan Beban" <unavailable wrote...
....
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?

When you use[*].Cells (with the period), it's a property of some object of
type Range. If you use it without a period, it's still a property of some
object not necessarily of type Range. Either way, the property has type
Range. Either way the property itself is type Range. The Cells property,
when used, returns objects, but it's not a different class. You have to
distinguish between class, object you can instantiate just by declaring them
via Dim, and derived objects that can only be instantiated by running code.

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?

From a pure OO programming point of view, collections aren't always the same
class type as the things they contain. Ignoring generic container classes
(which can contain themselves, just to make things fun), the Sheets
container class contains objects of class type Sheet, but it's not class
type Sheet itself. However, Range objects contain other Range objects. In
that sense, it may be useful to think of the Range class as PARTITIONABLE
rather than as a collection. OTOH, collections themselves can only add to or
remove from themselves or return their members (Item properties), but the
collection itself can't do anything its members can do.

This is purely terminological semantics.