Not at all clear on use of variables and/or object variables

[JMay-Rke]

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?

 

[recrit]

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

 

[JMay]

Exactly -- Spot-on recrit !!

do you mean when to use either of the following?

dim x
x = Range("A1")

|OR|

dim rng as Range
set rng = Range("A1")

 

[recrit]

Exactly -- Spot-on recrit !!

for this decision, I would consider the following:

(1) Data type, search Excel Visual Basic Help for data type summary.
Using "set range" you create an object reference which only uses 4
bytes of memory. The size of the variable using a non-reference type
depends on what you are reading in from the cell.... worst case is
that its not consistent so you have to use variant type all the time
which uses 16 bytes or more depending if its a number or string.
However if you know you read in a boolean it only uses 2bytes and then
would be more memory efficient. Range object become versatile when
you are grinding data where the type is not all the same, so loop
through rows and doing something with value will be more efficient.

(2) if you are reading in a range of cells, Range("A1:A5")... you
still have the same choice. When pulled to a variable you end up with
an variant array which can amplify the memory issue. With the range
variable you get a referenced object again that you can still iterate
over, either for...each || for r=1 to rng.Rows.Count etc

(3) Range object allows access to all the Range properties, so it can
be useful for interacting with the sheet. ie changing colors, formats,
retrieving sheet object, dependents, etc

(4) If you are going to perform a destructive operation on the range
that you are reading in, then the reference variable will change
according... this can cause some errors and confusion. For instance
you are reading in Range("A1:A5") but then you delete rows 2 & 3 ...
your range variable will not have the values for rows 2 & 3 any
more.... in this case if you wanted those values you would have to
start with pulling the values into an array.

As you can see, it really depends on your application. My preference
is to use the range object if possible since its the most versatile
and memory efficient.

Hope this helps.

 

[Jim Thomlinson]

A varaible and an object are both things that you store in memory in order to
reference them. That is really where the similarity ends.

Lets start with a variable. A variable is one of your fundamental data
types. It is an integer, long, double, string, boolean, array or... They are
a variables of a specific memory size. You store values in the varaibles and
youi can read the values back. You can write to a varaible with an equal sign.

Objects are much more complex. Without getting too far into object oriented
programming objects are tangable things like workbooks, worksheets or ranges.
Each different object has it's own properties and methods. Properties
describe the object such as the cells value, colour, border, ... Methods are
things that the object can do such as sort, copy, calculate, ... When you
want to create or modify an object you need to use the set key word. Objects
use up a fair bit more memory than a standard variable but then again they
can do a lot more stuff. While they do take up a lot more memory they are
still very efficient to pass around as you are really only passing a pointer
to the object and not the entire object itself.

 

[Bob Phillips]

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]

Thank you recrit!!
Thank you Bob!!
Thank you Jim!!

All for such lengthy explanations. I'm gonna spend some time on each
of your explanations. At first glance they all appear **informative**,
and just what i wanted/needed.

I'll get back with possible questions.

Jim

 

[JMay]

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]

You are right Jim, bad example.

But what you are doing when you used it is just using extra properties of
the Range object, in this case Cells

MsgBox myRange.Cells(1,1).Value 'A1 in this example

or

MsgBox myRange.Cells(3,1).Value 'A3 in this example

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMay]

excellant.
Thanks jim

A varaible and an object are both things that you store in memory in order to
reference them. That is really where the similarity ends.

Lets start with a variable. A variable is one of your fundamental data
types. It is an integer, long, double, string, boolean, array or... They are
a variables of a specific memory size. You store values in the varaibles and
youi can read the values back. You can write to a varaible with an equal sign.

Objects are much more complex. Without getting too far into object oriented
programming objects are tangable things like workbooks, worksheets or ranges.
Each different object has it's own properties and methods. Properties
describe the object such as the cells value, colour, border, ... Methods are
things that the object can do such as sort, copy, calculate, ... When you
want to create or modify an object you need to use the set key word. Objects
use up a fair bit more memory than a standard variable but then again they
can do a lot more stuff. While they do take up a lot more memory they are
still very efficient to pass around as you are really only passing a pointer
to the object and not the entire object itself.

 

[JMay]

excellant.
Thanks recrit

for this decision, I would consider the following:

(1) Data type, search Excel Visual Basic Help for data type summary.
Using "set range" you create an object reference which only uses 4
bytes of memory. The size of the variable using a non-reference type
depends on what you are reading in from the cell.... worst case is
that its not consistent so you have to use variant type all the time
which uses 16 bytes or more depending if its a number or string.
However if you know you read in a boolean it only uses 2bytes and then
would be more memory efficient. Range object become versatile when
you are grinding data where the type is not all the same, so loop
through rows and doing something with value will be more efficient.

(2) if you are reading in a range of cells, Range("A1:A5")... you
still have the same choice. When pulled to a variable you end up with
an variant array which can amplify the memory issue. With the range
variable you get a referenced object again that you can still iterate
over, either for...each || for r=1 to rng.Rows.Count etc

(3) Range object allows access to all the Range properties, so it can
be useful for interacting with the sheet. ie changing colors, formats,
retrieving sheet object, dependents, etc

(4) If you are going to perform a destructive operation on the range
that you are reading in, then the reference variable will change
according... this can cause some errors and confusion. For instance
you are reading in Range("A1:A5") but then you delete rows 2 & 3 ...
your range variable will not have the values for rows 2 & 3 any
more.... in this case if you wanted those values you would have to
start with pulling the values into an array.

As you can see, it really depends on your application. My preference
is to use the range object if possible since its the most versatile
and memory efficient.

Hope this helps.

 

[JMay]

excellant.
Thanks,

for this decision, I would consider the following:

(1) Data type, search Excel Visual Basic Help for data type summary.
Using "set range" you create an object reference which only uses 4
bytes of memory. The size of the variable using a non-reference type
depends on what you are reading in from the cell.... worst case is
that its not consistent so you have to use variant type all the time
which uses 16 bytes or more depending if its a number or string.
However if you know you read in a boolean it only uses 2bytes and then
would be more memory efficient. Range object become versatile when
you are grinding data where the type is not all the same, so loop
through rows and doing something with value will be more efficient.

(2) if you are reading in a range of cells, Range("A1:A5")... you
still have the same choice. When pulled to a variable you end up with
an variant array which can amplify the memory issue. With the range
variable you get a referenced object again that you can still iterate
over, either for...each || for r=1 to rng.Rows.Count etc

(3) Range object allows access to all the Range properties, so it can
be useful for interacting with the sheet. ie changing colors, formats,
retrieving sheet object, dependents, etc

(4) If you are going to perform a destructive operation on the range
that you are reading in, then the reference variable will change
according... this can cause some errors and confusion. For instance
you are reading in Range("A1:A5") but then you delete rows 2 & 3 ...
your range variable will not have the values for rows 2 & 3 any
more.... in this case if you wanted those values you would have to
start with pulling the values into an array.

As you can see, it really depends on your application. My preference
is to use the range object if possible since its the most versatile
and memory efficient.

Hope this helps.