Nothing is one of many ways to express the concept of nothingness in VBA. For the others, check out An Article About Nothing.
The Nothing keyword is used to disassociate an object variable from an actual object. -Microsoft Learn
Nothing is the starting value of an object variable before it has been initialized.
It is also a value we can assign to an object variable to clear its reference to the associated object. Once all references to an object have been cleared, any memory that had been reserved for the object may be reclaimed. This concept is known as reference counting.
Reference counting is how memory is managed in COM and, by extension, VBA:
Nothing is Only for Objects
You can only assign
Nothing to object variables. You cannot assign it to value-type variables, such as
Testing for Nothing
To test if a variable is
Nothing we use the
Dim MyObject As Object Debug.Print MyObject Is Nothing
Note that you cannot use the equal sign to check for
Debug.Print MyObject = Nothing ' <-- Compile error: Invalid use of object
A Warning About As New
It makes no sense to check whether an object variable is
Nothing if it is declared
As New because the check will always return
True. That's because the act of checking if the object exists is enough to make VBA create a new instance of the object.
Dim MyCollection As New Collection Debug.Print MyCollection Is Nothing '<-- Will always return False Set MyCollection = Nothing Debug.Print MyCollection Is Nothing '<-- Yep, still False
Assigning Nothing (Explicitly)
To set an object variable to
Nothing, use the
Dim MyCollection As Collection Set MyCollection = New Collection Debug.Print MyCollection Is Nothing '<-- Returns False Set MyCollection = Nothing Debug.Print MyCollection Is Nothing '<-- Now it returns True
Assigning Nothing (Implicitly)
While many VB6 and VBA programmers insist on explicitly setting every object variable to Nothing, you generally don't have to. With a few exceptions–circular references and poorly implemented components to name two–object variables will "self-destruct" as soon as they go out of scope.
Sub DemoSelfDestruct() Dim MyCollection As Collection Set MyCollection = New Collection MyCollection.Add "Foo" Debug.Print MyCollection.Count 'Set MyCollection = Nothing '<-- This line is not needed ' (but it doesn't really hurt, either) End Sub
With New to Obviate
One way to strictly and cleanly control the lifetime of an object is to create it and destroy it implicitly within a
With ... End With block.
Sub DemoWithObjectLifetime() With New Collection .Add "Foo" Debug.Print .Count End With ' POOF! the collection object is gone forever End Sub
With this technique, the object variable is instantiated automatically at the beginning of the
With block and released from memory when exiting the
Cover image created with Microsoft Designer
UPDATE [2023-09-06]: Added introductory paragraph with a link to An Article About Nothing.