I have found the following equations do the same thing:

cell.offset(0,2).value = cell.offset(0,4).value * cell.offset(0,6).value
cell.offset(0,2) = cell.offset(0,4) * cell.offset(0,6)

I started out using ".value" in my equations, but have taken most out, with
no apparent ill effects.

Is there any place where ".value" is required? Or can I safely leave it out?

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Each object has a default property, and for cell objects, it's ".Value". I
wouldn't trust myself to remember the default property of every object, but if
you know one for sure, you can safely omit it. In fact, doing so may make the
code more efficient.

Regards,
Wes

I'm not sure what difference it makes, but I think the default property
for a Range Object, including ranges that are cells, is the Item Property.

Alan Beban

SunTzuComm wrote:

Each object has a default property, and for cell objects, it's ".Value". I
wouldn't trust myself to remember the default property of every object, but if
you know one for sure, you can safely omit it. In fact, doing so may make the
code more efficient.

Regards,
Wes

All,

Value is the default property of the Range object. Item is the default
property of the Cells property which returns a Range object. That's why
Range("A1") = Range("a1").Value is True and Cells(1,2) = Cells.Item(1,2) is
True. There is no cell object.

I'm curious why omitting the Value property would be more efficient. I
would think the opposite. If I don't include it, the VBA has to figure out
what type of object I'm using, look up its default property and process from
there. If I don't omit it, there's no lookup. I don't have any real
evidence of this, it's just what I think would happen.

FWIW, I also include every default property. But I'm willing to learn.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



Alan Beban wrote:
I'm not sure what difference it makes, but I think the default property
for a Range Object, including ranges that are cells, is the Item Property.

Alan Beban

SunTzuComm wrote:

Each object has a default property, and for cell objects, it's ".Value".
I wouldn't trust myself to remember the default property of every
object, but if you know one for sure, you can safely omit it. In fact,
doing so may make the code more efficient.

Regards,
Wes

Value is the default property of the Range object.

Just for discussion, here is a copy from vba's help on "Cells Property"

<..copy
Remarks:
Because the Item property is the default property for the Range object, ...
<..end copy

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dick Kusleika" wrote in message
...
All,

Value is the default property of the Range object. Item is the default
property of the Cells property which returns a Range object. That's why
Range("A1") = Range("a1").Value is True and Cells(1,2) = Cells.Item(1,2)
is
True. There is no cell object.

I'm curious why omitting the Value property would be more efficient. I
would think the opposite. If I don't include it, the VBA has to figure
out
what type of object I'm using, look up its default property and process
from
there. If I don't omit it, there's no lookup. I don't have any real
evidence of this, it's just what I think would happen.

FWIW, I also include every default property. But I'm willing to learn.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



Alan Beban wrote:
I'm not sure what difference it makes, but I think the default property
for a Range Object, including ranges that are cells, is the Item
Property.

Alan Beban

SunTzuComm wrote:

Each object has a default property, and for cell objects, it's
".Value".
I wouldn't trust myself to remember the default property of every
object, but if you know one for sure, you can safely omit it. In fact,
doing so may make the code more efficient.

Regards,
Wes

There have been (a few) instances where apparently leaving out the
..Value led to unintended effects. Search google for more.

More importantly, AFAIK, VB.Net does not support a default property.
Why not do it right now so that if/when you port the code, a missing
default property doesn't have to be the cause of problems.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , fredsmith99
@yahoo.com says...
I have found the following equations do the same thing:

cell.offset(0,2).value = cell.offset(0,4).value * cell.offset(0,6).value
cell.offset(0,2) = cell.offset(0,4) * cell.offset(0,6)

I started out using ".value" in my equations, but have taken most out, with
no apparent ill effects.

Is there any place where ".value" is required? Or can I safely leave it out?

Hi Tushar,

Can you give an illustration of what's meant by "VB.Net does not support
a default property"?

Thanks,
Alan Beban

Tushar Mehta wrote:

. . . More importantly, AFAIK, VB.Net does not support a default property.
Why not do it right now so that if/when you port the code, a missing
default property doesn't have to be the cause of problems.

Dick Kusleika wrote:
Value is the default property of the Range object. Item is the default
property of the Cells property which returns a Range object. That's why
Range("A1") = Range("a1").Value is True and Cells(1,2) = Cells.Item(1,2) is
True. There is no cell object. . . .

Dana DeLouis has already pointed out in this thread documentation that
the Item Property is the default for the Rrange Object. That Dick's
illustrations above do not provide a definitive test (I'm not sure what
does), consider that Range("A1")(1) = Range("A1").Item(1) also returns True.

Alan Beban

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic
http://support.microsoft.com/default...57&Product=xlw

To add to Tushar's warning, one needs to be aware of the potential problems
with the value property when referring to dates and currency. Value2 may be
more appropriate.

I noticed the following bug is still present in Excel 2003.
If one has a vba code similar to the following
v = Range("A1").Value
and you do an "Add Watch" to the statement "Range("A1")"

then the "Value" property is still missing from this list. Using "Add
Watch" is a nice way to determine what properties are available for an
object. So, it's a little troublesome to see "Value" missing.

(looks like "Address" is still missing also, and perhaps a few others)
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dana DeLouis" wrote in message
...
Value is the default property of the Range object.

Just for discussion, here is a copy from vba's help on "Cells Property"

<..copy
Remarks:
Because the Item property is the default property for the Range object,
....
<..end copy

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dick Kusleika" wrote in message
...
All,

Value is the default property of the Range object. Item is the default
property of the Cells property which returns a Range object. That's why
Range("A1") = Range("a1").Value is True and Cells(1,2) = Cells.Item(1,2)
is
True. There is no cell object.

I'm curious why omitting the Value property would be more efficient. I
would think the opposite. If I don't include it, the VBA has to figure
out
what type of object I'm using, look up its default property and process
from
there. If I don't omit it, there's no lookup. I don't have any real
evidence of this, it's just what I think would happen.

FWIW, I also include every default property. But I'm willing to learn.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



Alan Beban wrote:
I'm not sure what difference it makes, but I think the default
property
for a Range Object, including ranges that are cells, is the Item
Property.

Alan Beban

SunTzuComm wrote:

Each object has a default property, and for cell objects, it's
".Value".
I wouldn't trust myself to remember the default property of every
object, but if you know one for sure, you can safely omit it. In
fact,
doing so may make the code more efficient.

Regards,
Wes

Just that, Alan. The software will not make an inference about what
property it should refer to. So,

Dim x as Integer
x=Cells(1,1)

will not be interpreted as
x=Cells(1,1).Value

but an attempt to assign an object, i.e., Cells(1,1), to an integer
variable (x).

After all, .Net doesn't require a Set to assign an object to a
variable. So, if it supported a default property concept, the
following would be ambiguous:

Dim x as Variant
x=Cells(1,1)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi Tushar,

Can you give an illustration of what's meant by "VB.Net does not support
a default property"?

Thanks,
Alan Beban

Tushar Mehta wrote:

. . . More importantly, AFAIK, VB.Net does not support a default property.
Why not do it right now so that if/when you port the code, a missing
default property doesn't have to be the cause of problems.

Dim x as Variant

Just to nit pick, NET doesn't support Variants, either.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tushar Mehta" wrote in
message news:MPG.1b102c4883d528ef9897bb@news-server...
Just that, Alan. The software will not make an inference about
what
property it should refer to. So,

Dim x as Integer
x=Cells(1,1)

will not be interpreted as
x=Cells(1,1).Value

but an attempt to assign an object, i.e., Cells(1,1), to an
integer
variable (x).

After all, .Net doesn't require a Set to assign an object to a
variable. So, if it supported a default property concept, the
following would be ambiguous:

Dim x as Variant
x=Cells(1,1)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,

says...
Hi Tushar,

Can you give an illustration of what's meant by "VB.Net does
not support
a default property"?

Thanks,
Alan Beban

Tushar Mehta wrote:

. . . More importantly, AFAIK, VB.Net does not support a
default property.
Why not do it right now so that if/when you port the code,
a missing
default property doesn't have to be the cause of problems.

The reason I asked is that the On-line help attributes the availability
of the syntax

Range("a1:d4").Cells(1,2) [rather than Range("a1:d4").Cells.Item(1,2)],
thus by implication Range("a1:d4")(1,2) [rather than
Range("a1:d4.Item(1,2)],

to the fact that the Item Property is the default for the Range Object,
to wit:

"Because the Item property is the default property for the Range object,
you can specify the row and column index immediately after the Cells
keyword." [from the online help for the Cells Property]

Is the syntax Range("a1:d4")(1,2) not available in VB.Net to refer to
Cell B1?

Alan Beban

Tushar Mehta wrote:

Just that, Alan. The software will not make an inference about what
property it should refer to. So,

Dim x as Integer
x=Cells(1,1)

will not be interpreted as
x=Cells(1,1).Value

but an attempt to assign an object, i.e., Cells(1,1), to an integer
variable (x).

After all, .Net doesn't require a Set to assign an object to a
variable. So, if it supported a default property concept, the
following would be ambiguous:

Dim x as Variant
x=Cells(1,1)

True. VB.Net doesn't support Variant as a datatype. Should have been
Object.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Dim x as Variant

Just to nit pick, NET doesn't support Variants, either.

Dick,

I didn't expect to trigger a discussion of semantics. I was thinking of OOP in
general: Anything that has a property is an object, even if that thing is
itself a property of another object. If the Cells property returns a Range
object whose default property is Value, then Cells is as good as an object
whose default property is also Value. If that's not the terminology Excel's
object model uses, I apologize profusely.

Omitting the default property MAY be more efficient because parsing is
processing intensive, and a look-up is, well, just a look-up. I have no hard
evidence for this, and I'm not going to spend time testing the theory, but it
seems that whenever one can omit source code from an Excel macro, one MAY be
reducing run time. I, too, always include each default property, if only for
the sake of documentation.

Regards,
Wes

I ran a quick test and found that omitting the .Value ran *slightly* faster.
Kind of surprising. I expected the .Value version to be faster.

This took approximately .75 seconds on average to run.
Sub TestNoValue()
Dim i As Integer
Dim cell As Range
For i = 1 To 10000
Set cell = Cells(i, 1)
cell = i
cell(, 2) = cell
Next i
End Sub

while this took approximately .781 seconds on average to run.
Sub TestValue()
Dim i As Integer
Dim cell As Range
For i = 1 To 10000
Set cell = Cells(i, 1)
cell.Value = i
cell(, 2).Value = cell.Value
Next i
End Sub


"SunTzuComm" wrote in message
...
Dick,

I didn't expect to trigger a discussion of semantics. I was thinking of
OOP in
general: Anything that has a property is an object, even if that thing is
itself a property of another object. If the Cells property returns a
Range
object whose default property is Value, then Cells is as good as an object
whose default property is also Value. If that's not the terminology
Excel's
object model uses, I apologize profusely.

Omitting the default property MAY be more efficient because parsing is
processing intensive, and a look-up is, well, just a look-up. I have no
hard
evidence for this, and I'm not going to spend time testing the theory, but
it
seems that whenever one can omit source code from an Excel macro, one MAY
be
reducing run time. I, too, always include each default property, if only
for
the sake of documentation.

Regards,
Wes

Thanks for pointing that out, Alan. Apparently, .Net does support some
default properties ;-)

It would appear that at least when dealing with collections, 'Item'
indeed works as the default property. So, when one uses a range as a
collection, .Item is not needed.

But, Value definitely is not the default property for the range object.

Dim x as object
x=cells(1,1) will create an object reference to the cell, whereas
x=cells(1,1).value will create a simple data type (of the appropriate
numeric/string type) that contains a value.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
The reason I asked is that the On-line help attributes the availability
of the syntax

Range("a1:d4").Cells(1,2) [rather than Range("a1:d4").Cells.Item(1,2)],
thus by implication Range("a1:d4")(1,2) [rather than
Range("a1:d4.Item(1,2)],

to the fact that the Item Property is the default for the Range Object,
to wit:

"Because the Item property is the default property for the Range object,
you can specify the row and column index immediately after the Cells
keyword." [from the online help for the Cells Property]

Is the syntax Range("a1:d4")(1,2) not available in VB.Net to refer to
Cell B1?

Alan Beban

Tushar Mehta wrote:

Just that, Alan. The software will not make an inference about what
property it should refer to. So,

Dim x as Integer
x=Cells(1,1)

will not be interpreted as
x=Cells(1,1).Value

but an attempt to assign an object, i.e., Cells(1,1), to an integer
variable (x).

After all, .Net doesn't require a Set to assign an object to a
variable. So, if it supported a default property concept, the
following would be ambiguous:

Dim x as Variant
x=Cells(1,1)

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic
http://support.microsoft.com/default...57&Product=xlw


Hmmm. Now I'm confused. Maybe since Range is an atypical object (both an
object and a collection) the Value and Item are both default properties (the
help you quoted notwithstanding). And the existence of arguments determines
which one is used.

But then the Item property for Range doesn't work like other Item
properties. You can't get a subscript error with Range.Item like most
(all?) other collections. It's probably just incorrect of me to think of
Range as a collection.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

That Dick's
illustrations above do not provide a definitive test (I'm not sure what
does), consider that Range("A1")(1) = Range("A1").Item(1) also returns
True.

Okay, that blows a big hole in my theory. Since I never use that syntax, my
brain must have gotten into a rut which excluded Item as a default property
(of course I use that syntax with Cells, just not Range). Clearly Item is
the default property. But I can't get past the fact that Value is too.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

I didn't expect to trigger a discussion of semantics. I was thinking of
OOP in
general: Anything that has a property is an object, even if that thing is
itself a property of another object. If the Cells property returns a
Range
object whose default property is Value, then Cells is as good as an object
whose default property is also Value. If that's not the terminology
Excel's
object model uses, I apologize profusely.

No need to apologize. It's my terminology that's wrong. I was doubting
myself even writing that, but seemed to make sense at the time. I'm sure
the original poster doesn't care about this semantical stuff, but I like it.
I'm already changing my thinking because of this thread. I just don't know
to whay yet.


Omitting the default property MAY be more efficient because parsing is
processing intensive, and a look-up is, well, just a look-up. I have no
hard
evidence for this, and I'm not going to spend time testing the theory, but
it
seems that whenever one can omit source code from an Excel macro, one MAY
be
reducing run time. I, too, always include each default property, if only
for
the sake of documentation.

It appears you're right, based on Tim's post. That' really surprising to
me.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Tushar Mehta" wrote...
....
But, Value definitely is not the default property for the range
object.

Dim x as object
x=cells(1,1) will create an object reference to the cell, whereas

No it won't. As written, when run it throws a Run-time error '91': Object
variable or With block variable not set. You need 'Set' at the beginning of
this statement.

x=cells(1,1).value will create a simple data type (of the appropriate
numeric/string type) that contains a value.
....

Not with x defined as an object it won't. Enter 1 in cell A1 and 2 in cell
A2, then modify your sample code slightly to be

Dim x As Object
Set x = Cells(1, 1)
x = Cells(2, 1).Value

The Set statement makes x an object reference to cell A1. The second
statement doesn't change x itself, rather it's interpretted as

x.Value = Cells(2, 1).Value

which you can test for yourself, since given the initial setup already
described, this statement sets the value of cell A1 to the value of cell A2,
namely, 2.

On the other hand, in the code

Dim x As Variant
x = Cells(1, 1)
x = Cells(2, 1).Value

the first statement sets x equal to the value of cell A1, and the second
call sets it to the value of cell A2. Modify it yet again to

Dim x As Variant
Set x = Cells(1, 1)
x = Cells(2, 1).Value

and the first statement sets x to an object reference to cell A1, but the
second statement sets x to the value in cell A2 rather than manipulating the
value in cell A1.

Moral: VERY dangerous (bordering on reckless) ever to try to use default
properties in left hand side terms of assignment statements. Default
properties are a laziness feature that leads to more time finding and fixing
the problems its use causes than time saved not having to type the default
property in the statement. This is especially the case in VB[A] when using
variant type variables.

The way I rationalize it is to think of Range Objects as Collection
Objects, but to recognize that a Range Object is a collection of more
items than just those specified in the explicitly stated range. I.e.,
Range("D20:H30") [or any other explicit range] is a collection of all
cells on the worksheet, not just the 55 cells explicitly stated; and to
recognize that the indexing starts with the upper left cell of the
explicitly stated range as Item(1,1). And Range("D20:H30").Rows acts as
a collection of the rows D:H on the worksheet, with
Range("D20:H30").Rows.Item(1) being the first row of the explicitly
stated range, i.e., D20:H20; and similarly for a range collection of
columns.

This mental construct breaks down for single indexing of ranges that are
collections of cells. In this case the ranges act like collections of
cells in an idiosyncratic range that I'll be happy to describe if
anybody's interested.

Alan Beban

Dick Kusleika wrote:

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic
http://support.microsoft.com/default...57&Product=xlw



Hmmm. Now I'm confused. Maybe since Range is an atypical object (both an
object and a collection) the Value and Item are both default properties (the
help you quoted notwithstanding). And the existence of arguments determines
which one is used.

But then the Item property for Range doesn't work like other Item
properties. You can't get a subscript error with Range.Item like most
(all?) other collections. It's probably just incorrect of me to think of
Range as a collection.

Maybe since Range is an atypical object (both an
object and a collection)

Just for fun and to add to the confusion.
You have a Workbook, and a Workbooks Collection, a Sheet, and a Sheets
collection. There is also a Row, and a Rows collection. There are many
more like Chart, and the Charts collection...etc. A collection is anything
with an 's at the end. (vbg).
You have Range, but no Ranges collection, so it fails the 's test. For
this, one needs "Areas" Now, that has an 's at the end, so Areas is a
collection. It's so confusing. :)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dick Kusleika" wrote in message
...

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic

http://support.microsoft.com/default...57&Product=xlw


Hmmm. Now I'm confused. Maybe since Range is an atypical object (both an
object and a collection) the Value and Item are both default properties
(the
help you quoted notwithstanding). And the existence of arguments
determines
which one is used.

But then the Item property for Range doesn't work like other Item
properties. You can't get a subscript error with Range.Item like most
(all?) other collections. It's probably just incorrect of me to think of
Range as a collection.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

I seem to be having problems with OE lately. The message got sent before I
was finished.

Anyway, a point to make is that Range by itself uses the first Area of it's
Areas collection.
Here's a demo. This has two cells in the Range, ... A1 & D1. One would
think that item(2) should return the number 4. However, it returns 2
because it only looked in Areas(1) and extended its search down.

Sub Demo()
[A1] = 1
[A2] = 2

[D1] = 4

Debug.Print Range("A1,D1").Item(2) ' Returns 2
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip

When I want to point out to a regular poster, especially one who is
more often right than wrong, that their post demonstrates reckless
laziness, I go over their post with a fine-tooth comb; then repeat with
an even finer-tooth comb. Chances are the exercise will save me some
serious embarrassment -- like missing a first paragraph that read, in
part, "...Apparently, .Net does support some default properties..."

Notice the .Net part!

This sub-discussion, starting with Alan's query (Can you give an
illustration of what's meant by "VB.Net does not support a default
property") to my first response to the OP (...More importantly, AFAIK,
VB.Net does not support a default property...) morphed into one about
VB.Net's support of default properties.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
"Tushar Mehta" wrote...
...
But, Value definitely is not the default property for the range
object.

Dim x as object
x=cells(1,1) will create an object reference to the cell, whereas

No it won't. As written, when run it throws a Run-time error '91': Object
variable or With block variable not set. You need 'Set' at the beginning of
this statement.

x=cells(1,1).value will create a simple data type (of the appropriate
numeric/string type) that contains a value.
...

Not with x defined as an object it won't. Enter 1 in cell A1 and 2 in cell
A2, then modify your sample code slightly to be

Dim x As Object
Set x = Cells(1, 1)
x = Cells(2, 1).Value

The Set statement makes x an object reference to cell A1. The second
statement doesn't change x itself, rather it's interpretted as

x.Value = Cells(2, 1).Value

which you can test for yourself, since given the initial setup already
described, this statement sets the value of cell A1 to the value of cell A2,
namely, 2.

On the other hand, in the code

Dim x As Variant
x = Cells(1, 1)
x = Cells(2, 1).Value

the first statement sets x equal to the value of cell A1, and the second
call sets it to the value of cell A2. Modify it yet again to

Dim x As Variant
Set x = Cells(1, 1)
x = Cells(2, 1).Value

and the first statement sets x to an object reference to cell A1, but the
second statement sets x to the value in cell A2 rather than manipulating the
value in cell A1.

Moral: VERY dangerous (bordering on reckless) ever to try to use default
properties in left hand side terms of assignment statements. Default
properties are a laziness feature that leads to more time finding and fixing
the problems its use causes than time saved not having to type the default
property in the statement. This is especially the case in VB[A] when using
variant type variables.

I should have read the preceding branch of this thread first. It looks like
the code I commented on was supposed to be VB.NET rather than VBA. That
makes a difference.

Variants and explicit object variables holding object references have
different syntactic characteristics. An explicit object variable with no
property qualifier on either side of a [Let] assignment statement will cause
VBA to use the default property. This only works on the right hand side with
variants holding object references. This is syntactic, governed by how VBA
parses variants vs how it parses objects. A LHS variant can receive any
value VBA can generate on the RHS. A LHS object can't receive anything
itself in a Let assignment, so there's an implicit parsing step that uses
the object's default property.

On to .Value vs .Item. In XL2K, the following runs just fine.

Msgbox Range("A1:A2").Address

but oddly the following throws a runtime error

Msgbox Range.Item("A1:A2").Address

Seems awful odd for a default property not to work when used explicitly.
Gosh, do you suppose this could mean there are *ERRORS* in VBA online help?!
As if this wouldn't be the only one!!

There's also the syntactic problem that Item as default property is
unsatisfactory. Given

Dim x As Variant
x = Range("A1:A3").Item(1)

the Item property returns an *OBJECT* reference, to cell A1 in this case. In
the Let assignment statement, there's no way to assign this object to x. But
VBA is somehow able to assign something to x, and that something isn't an
object reference. What is it? The .Value property! If it weren't, then the
assignment statement above would lead to an infinite loop. The default
property of an object of type 'A' can't be another object of type 'A'.

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's the only way I
can see how

Range("A1:D3")(7)
Range("A1:D3")(2, 3)
Range("A1:D3").Cells(7)
Range("A1:D3").Cells(2, 3)
Range("A1:D3").Cells.Item(7)
Range("A1:D3").Cells.Item(2, 3)

all produce references to cell C2, but

Dim x As Variant
x = Range("A1:D3")(7)
x = Range("A1:D3")(2, 3)
x = Range("A1:D3").Cells(7)
x = Range("A1:D3").Cells(2, 3)
x = Range("A1:D3").Cells.Item(7)
x = Range("A1:D3").Cells.Item(2, 3)

all assign the value of cell C2 to x.

That is why whenever I expect having to deal with a range with more
than 1 area land up writing a function

Sub testIt2()
[A1] = 1
[A2] = 2
[D1] = 4
With Range("A1,D1")
MsgBox .Cells.Count & ", " & .Cells(2) & "," & CellInRng(.Cells, 2)
End With
End Sub
Function CellInRng(aRng As Range, ByVal Idx As Long)
Dim anArea As Range, aCell As Range
For Each anArea In aRng.Areas
If Idx <= anArea.Cells.Count Then
Set CellInRng = anArea.Cells(Idx)
Exit Function
Else
Idx = Idx - anArea.Cells.Count
End If
Next anArea
End Function

I benefited tremendously from a study of the exposition on the Range
object/collection in the XL97 developer edition printed manual. It's a
shame that manuals for subsequent versions have become more and more
like marketing material pushing whatever it is that MS wants to push in
a given version. Though, the introduction to COM add-ins in 2000
(2002?) was useful.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Sub Demo()
[A1] = 1
[A2] = 2

[D1] = 4

Debug.Print Range("A1,D1").Item(2) ' Returns 2
End Sub

"Tushar Mehta" wrote...
When I want to point out to a regular poster, especially one who is
more often right than wrong, . . .

I could be really snotty and ask who that might be, but I admit I screwed up
by not reading the preceding branch.

I'd bet default properties were only available on the right hand side of
assignments in VB.NET. In VBA, if the code were

Dim x As Object
Set x = Range("A1")
x = Range("A2")

the second [Let] assignment statement is equivalent to

x.Value = Range("A2").Value

In VB.NET, the closest code would be

Dim x As Object
x = Range("A1")
x = Range("A2")

which would be the same as the VBA code

Dim x As Object
Set x = Range("A1")
Set x = Range("A2")

To do the same thing as the first VBA code above, the tersest VB.NET code
would have to be

Dim x As Object
x = Range("A1")
x.Value = Range("A2")

Right hand side default properties are up to the object itself to provide.
Left hand side default properties require assistance from the parser.
Without the Set keyword, there's no way to do it without imposing a level of
type awareness that's never been part of any Microsoft BASIC dialect. In C++
and Java, there can be class member functions that return different things
depending on the type of the variable to which the object variable is
assigned. VBA doesn't support this, but it'd be nice if VB.NET did (and if
VB.NET doesn't provide the Variant type, there's no reason it couldn't).

VB.Net does support default properties.

However, it does *not * support -parameterless- default properties:

This means that you can still access collection types with the arra
syntax

*MyCollection(6)* would be the same as *MyCollecion.Item(6)*, assumin
that 'Item' was the default property.

However, Range("A1") would return the range object and not it's value
as stated earlier in the thread. This actually allows you to kee
default properties and also lose the *set* keyword.

Nice.

As an aside, it's now much easier to create default properties - yo
just specifiy it in the decaration:

Public *Default* Property Item(Index as Long)
Get
Item = MyBase.Item(Index)
End Get

Set
MyBase.Item(Index) = Value
End Set
End Property

Much nicer than having that wierd thing in the Procedure Propertie
dialogue box in VB

--
Message posted from http://www.ExcelForum.com