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

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

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

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

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

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

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.

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.

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.

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)

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)

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