SET OBJECT (Excel 2000)

[HUHandEH]

Hi all,

I need an understand setting the range object variable. Is it necessary to set every Range variable?
I need to get this straight in my head

Thanks
Darryl.

[Rudi]

You need to set a reference to any object you declare. Each Object needs a set statement! You can use the set statement to change the reference as many times within the sub procedure as you like, as long as the reference is of the same object type!
EG:
Dim myR as Range
Set myR = Range("A1")
Do code with rande A1
Set myR = Range("A3:B10")
Do code on this range
Set myR = Nothing
End sub

[HansV]

A Range is an object that has properties and methods. A variable that represents an object must always be assigned a value by using Set. Before you do so, an object variable is just an empty object that doesn't contain any data.

If you look up Excel Objects in the VBA help, you will see a chart of the Excel object model; since it is very large some parts are presented on separate pages. The screenshot below is part of the object model for Excel worksheets. Yellow boxes are collections, blue boxes are objects.

[HUHandEH]

Thank you

[LegareColeman]

You only need to set the object variables that you use. Until you set an object variable is set, it is empty (Nothing). If you attempt to use that object variable in any way that uses the object, then an error will result. If you DIM an object variable, but never reference it in a way that requires it to have a reference set, then that is not a problem. You can also use object variables that have not been set in ways that do not cause an error. For example:

<pre>DIM oRng As Range
If oRng Is Nothing Then
</pre>


The second line uses oRng which has not been set, and this does not cause an error. However, the following would cause an error:

<pre>DIM oRng As Range
oRng.Value = oRng.Value + 1
</pre>