Working With The Excel Object Model

Exir a

You can also use the Is operator with the Nothing keyword to ensure that an object variable points to a valid object. To do this, you compare the value of the object variable to the Nothing keyword with an If Then statement, as shown in this example. If the If Then statement returns a value of True, the object variable does not contain a reference to a valid variable.


If objvar Is Nothing Then

MsgBox ("Variable does not point to a valid object")

You use the Nothing keyword to free an object variable. By doing so, you free up the memory required to store the object pointer in the object variable. When an object has no variable references pointing to it, VBA destroys it. Therefore, if you have multiple object variables pointing to the object, you need to set each one of them to Nothing, as shown in this example:


Set objvar = Nothing

L0 Type Result = ObjVar1 Is ObjVar2, replacing ObjVarl and ObjVar2 with the object variables.

Q Type MsgBox (Result), replacing Result with the variable in step 4.

□ Switch to Excel and run the macro.

-■ The message box displays a value of True if the objects point to the same location.

End If


0 0

Post a comment