What If a Variable Doesn’t Refer to Anything?

How can you tell if an object variable doesn’t refer to anything? When working with dynamic data structures, you’ll find it useful to be able to discern whether a reference has yet been instantiated. Pascal uses Nil, C uses Null, and VBA uses Nothing to represent the condition in which an object variable doesn’t currently refer to a real object.

If you have an object variable and you’ve not yet assigned it to point to an object, its value is Nothing. You can test for this state using code like this:

If objItem Is Nothing Then
    ' You know that objItem isn’t currently referring to anything
End If 

If you want to release the memory used by an object in memory, you must sever all connections to that object. As long as some variable refers to an object, VBA won’t be able to release the memory used by that object. (Think of it as a hot-air balloon, tied down with a number of ropes; until someone releases the last rope, that balloon isn’t going anywhere.) To release the connection, set the object variable to Nothing:

Set objItem = Nothing

Once you’ve released all references to an object, VBA can dispose of the object and free up the memory it was using.

© 1997 by SYBEX Inc. All rights reserved.