Not at all clear on use of variables and/or object variables
Bob;
When trying out your example:
MsgBox myRange.Value
MsgBox myRange.Font.Name
MsgBox myRange.Interior.Colorindex
In the immediate window - I had to use an index numbers like:
MsgBox myRange(1).Value
MsgBox myRange(2).Font.Name
MsgBox myRange(3).Interior.Colorindex
as the above covers multiple elements (there's probably a better explanation
but I can't provide it...
Thanks again,
Jim
"Bob Phillips" wrote:
Specifically on the range object.
If you want your variable to refer to the range object itself, you have to
declare your variable as type Range or type Object (you will get away with
type Variant as well), and when loading the variable, you use Set
Set myRange = Range("A1:A4")
This means that myRange is pointing to that Range, and can be interrogated
for any of the properties of the range, such as its value, its font, its
fille colour and so on, and can also change many of thos properties. So you
can so all of these
MsgBox myRange.Value
MsgBox myRange.Font.Name
MsgBox myRange.Interior.Colorindex
and
myRange.Value = "abc"
myRange.Font.Bold = True
and so on.
Because myRange is an object variable, it points to the real object, and
gives you access to all properties and methods of that object.
If you use a variable and don't use set, then in effect you are picking up
the objects default property, in the case of Range, this is the value.
So
myVal = Range("A1")
is the same as
myVal = Range("A1").Value
Unlike the object variable, all you can do with myVal is use it to get the
Range Value, or set the range value to that variable. So in addition to the
previous line, you can also do
myVal = "some value"
Range("A1").Value = myVal
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"JMay" wrote in message
...
Exactly -- Spot-on recrit !!
"recrit" wrote:
On Jul 4, 10:19 am, JMay-Rke
wrote:
I jumped on the Excel (VBA) bandwagon some years ago. I have
dozens of books and reference them constantly. In no book or
in my visiting any newsgroup have I been able to clearly make
the distinction between when to engage a regular variable versus
an object variable - particularly as regards the Range object.
Can someone make an attempt to CONTRAST these two elements?
do you mean when to use either of the following?
dim x
x = Range("A1")
|OR|
dim rng as Range
set rng = Range("A1")
|