Microsoft Office Tutorials and References
In Depth Information
9.5.4 Freeing an Object Variable: the Nothing Keyword
9.5.4 Freeing an Object Variable: the Nothing Keyword
To free an object variable so that it no longer points to anything, we use the Nothing keyword, as
in:
Set rng2 = Nothing
It is good programming practice to free object variables when they are no longer needed, since this
can save resources. An object variable is also set to Nothing automatically when its lifetime
expires.
Note that once an object no longer has any references to it, the object will automatically be
destroyed by VBA, thus freeing up its resources (memory). However, all references to the object
must be freed before the object is destroyed. This is another reason not to point more than one
object variable at the same object if possible.
9.5.5 The Is Operator
To compare the values of two ordinary variables, Var1 and Var2 , we would just write:
If Var1 = Var2 Then . . .
However, the syntax for comparing two object variables to see if they refer to the same object is
special (as is the syntax for setting the value of an object variable—using the Set statement). It is
done using the Is operator:
If rng Is rng2 then . . .
Similarly, to test whether or not an object variable has been set to Nothing , we write:
If rng Is Nothing Then . . .
Be advised that there is a problem with the Is operator in the current version of VBA. This
problem exists in the version of VBA used by Office 97 and Office 2000. (Microsoft has
acknowledged the problem.) For example, the code:
Dim Wks As Worksheet
Dim Wks2 As Worksheet
Set Wks = ActiveSheet
Set Wks2 = ActiveSheet
MsgBox Wks Is Wks2
will correctly display the value True . However, the analogous code:
Dim rng As Range
Dim rng2 As Range
Set rng = ActiveSheet.Rows(1)
Set rng2 = ActiveSheet.Rows(1)
MsgBox rng Is rng2
incorrectly displays the value False . If we change the penultimate line to:
Search JabSto ::




Custom Search