Range object evaluates to Range.value, or Range object

[paul.domaskis]

In most equations, when a Range object shows up in an expression on
the right hand side of the assignment operator "=", the range object's
value is used. I'm interested in assigning a range object to a
variable that has been dim'd as type Range.

Where can one find the rules that govern what objects evaluate to when
they show up in expressions?

Also, in many cases, things like Range are used in the documentation
to describe a Range object as well as a collection of range objects.
Where in the documentation does it describe how the word Range is
interpretted in (say) a Dim statement? What about rules governing
whether a collection is returned in evaluating an expression, or
whether a default object within the collection is returned?

Thanks. (I'm using Office 2003)

 

[Bernie Deitrick]

Paul,

You need to use Set:

Dim objRange As Range
Set objRange = Range("A1")
Msgbox objRange.Address & " is " & objRange.Value

HTH,
Bernie
MS Excel MVP

 

[Patrick Molloy]

objects are assigned using the SET command.

for the range object, the value property is the default

so
dim x as string
x = range("A1")
is the same as
x = range("A1").Value
which is the "correct" coding - no ambiguity/guesses, it reads more clearly,
and with .NET, mandatory

but now
dim x as range
SET x = Range("A1")
creates an object referencing the sheet range. what you do to that, you see
on the sheet

with x
..Value = 12
..Interior.ColorIndex = 4
end with

read help on the excel object model will cover a good deal of this

 

[paul.domaskis]

Patrick,

Thanks for that. I found that the object model leads me to navigate
the class definitions, but haven't found my way to description of the
semantics of that aspect of the language (though I'm sure it's there).

 

[paul.domaskis]

Thanks for that, Bernie.

Paul,

You need to use Set:

Dim objRange As Range
Set objRange = Range("A1")
Msgbox objRange.Address & " is " & objRange.Value

HTH,
Bernie
MS Excel MVP

 

[r]

Sub test()
Dim rng As Excel.Range
Debug.Print TypeName(rng) 'Nothing
Debug.Print VarType(rng) '9 -> vbObject

Set rng = [a1]

Debug.Print TypeName(rng) 'Range
Debug.Print TypeName(rng.Value) 'Empty
Debug.Print VarType(rng) '0 -> vbEmpty
Debug.Print VarType(rng.Value) '0 -> vbEmpty

rng.Value = 1

Debug.Print TypeName(rng) 'Range
Debug.Print TypeName(rng.Value) 'Double
Debug.Print VarType(rng) '5 -> vbDouble
Debug.Print VarType(rng.Value) '5 -> vbDouble

End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[paul.domaskis]

Thanks, r.

That is an extremely illuminating and simple example that illustrates
all the subleties that could leave a newbie scratching one's head.

Sub test()
Dim rng As Excel.Range
Debug.Print TypeName(rng) 'Nothing
Debug.Print VarType(rng) '9 -> vbObject

Set rng = [a1]

Debug.Print TypeName(rng) 'Range
Debug.Print TypeName(rng.Value) 'Empty
Debug.Print VarType(rng) '0 -> vbEmpty
Debug.Print VarType(rng.Value) '0 -> vbEmpty

rng.Value = 1

Debug.Print TypeName(rng) 'Range
Debug.Print TypeName(rng.Value) 'Double
Debug.Print VarType(rng) '5 -> vbDouble
Debug.Print VarType(rng.Value) '5 -> vbDouble

End Sub

regards
r

Il mio ultimo lavoro ...http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-ecc...

In most equations, when a Range object shows up in an expression on
the right hand side of the assignment operator "=", the range object's
value is used.  I'm interested in assigning a range object to a
variable that has been dim'd as type Range.

Where can one find the rules that govern what objects evaluate to when
they show up in expressions?

Also, in many cases, things like Range are used in the documentation
to describe a Range object as well as a collection of range objects.
Where in the documentation does it describe how the word Range is
interpretted in (say) a Dim statement?  What about rules governing
whether a collection is returned in evaluating an expression, or
whether a default object within the collection is returned?

Thanks.  (I'm using Office 2003)- Hide quoted text -

- Show quoted text -