When do you need .Value?

[Fred Smith]

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?

 

[SunTzuComm]

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

 

[Alan Beban]

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

 

[Dick Kusleika]

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

 

[Dana DeLouis]

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>

 

[Tushar Mehta]

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

 

[Alan Beban]

Hi Tushar,

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

Thanks,
Alan Beban

 

[Alan Beban]

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

 

[Dana DeLouis]

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;114857&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)

 

[Tushar Mehta]

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

 

[Chip Pearson]

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

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

 

[Alan Beban]

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]

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

 

[SunTzuComm]

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

 

[Tim Zych]

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

 

[Tushar Mehta]

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

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

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)

 

[Dick Kusleika]

Had a hard time finding this...

Default Property/Methods for Objects in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;114857&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]

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]

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.

 

[Harlan Grove]

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.