Much Ado About Nothing

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

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 String, Long, Date, Currency, etc.

Testing for Nothing

To test if a variable is Nothing we use the Is statement:

Dim MyObject As Object
Debug.Print MyObject Is Nothing

Note that you cannot use the equal sign to check for Nothing:

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 Set keyword:

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

Using With New to Obviate Nothing

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 With block.

Cover image created with Microsoft Designer

UPDATE [2023-09-06]: Added introductory paragraph with a link to An Article About Nothing.