When do you need .Value?

[Alan Beban]

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

 

[Dana DeLouis]

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]

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>

 

[Tushar Mehta]

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

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.

 

[Harlan Grove]

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("A13")(7)
Range("A13")(2, 3)
Range("A13").Cells(7)
Range("A13").Cells(2, 3)
Range("A13").Cells.Item(7)
Range("A13").Cells.Item(2, 3)

all produce references to cell C2, but

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

all assign the value of cell C2 to x.

 

[Tushar Mehta]

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

 

[Harlan Grove]

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

 

[xtb]

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