The Difference Between Empty, Null and Nothing in VBA

Empty

• Indicates that no beginning value has been assigned to a Variant variable.
• An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context.
• Empty is not the same as Null which indicates that a variable contains no valid data.
• You can use the IsEmpty function to determine whether the variable is initialized.
• Use the VarType Function to determine the subtype of a variable.

Sub Test1()
    Dim varTest As Variant
    
    'returns 0, variable not initialized, indicating variable subtype Empty:
    Debug.Print VarType(varTest)
    
    'returns True, indicating variable subtype Empty:
    Debug.Print IsEmpty(varTest)
    
    'returns False - is an Empty variable, not a Null variable,
    'no beginning value has been assigned to a Variant variable:
    Debug.Print IsNull(varTest)
   
    'Empty indicates a Variant variable for which you do not explicity specify an initial value,
    'which by default gets initialized in VBA to a value that is represented as both a zero and a zero-length string.
    'returns True for all If statements below:
    If varTest = 0 Then
        Debug.Print "True"
    End If
    If varTest = "" Then
        Debug.Print "True"
    End If
    If varTest = vbNullString Then
        Debug.Print "True"
    End If
    If Len(varTest) = 0 Then
        Debug.Print "True"
    End If
   
    'assign Null keyword to set variable to Null
    varTest = Null
    
    'returns 1, Null (no valid data)
    Debug.Print VarType(varTest)
    
    'returns True - is a Null variable, not an Empty variable,
    Debug.Print IsNull(varTest)
End Sub

Null

• A value indicating that a variable contains no valid data.
• Null is the result: if you explicitly assign Null to a variable or perform any operation between expressions that contain Null.

  Sub Test2()
      Dim varTest1 As Variant
      varTest1 = Null
      'returns True - is a Null variable
      Debug.Print IsNull(varTest1)
      
      Dim varTest2 As Variant
      varTest2 = Null + 1
      'returns True - is a Null variable
      Debug.Print IsNull(varTest2)
  End Sub

• The Null keyword is used as a Variant subtype. only a Variant variable can be Null, and variable of any other subtype will give an error.

  Sub Test3()
      'Run-time error 94: Invalid use of Null
      Dim intTest As Integer
      intTest = Null
  End Sub

• Null is not the same as a zero-length string (""), and neither is Null the same as Empty, which indicates that a variable has not yet been initialized.

  Sub Test4()
      Dim varTest As Variant
      
      'assign Null keyword to set variable to Null
      varTest = Null
  
      'No returns for all If statements below:
      If varTest = 0 Then
          Debug.Print "True"
      End If
      If varTest = "" Then
          Debug.Print "True"
      End If
      If varTest = vbNullString Then
          Debug.Print "True"
      End If
      If Len(varTest) = 0 Then
          Debug.Print "True"
      End If
      
      'returns True for If statements below:
      If VarType(varTest) = vbNull Then
          Debug.Print "True"
      End If
  End Sub

• If you try to get the value of a Null variable or an expression that is Null, you will get an error of 'Run-time error 94: Invalid use of Null'. You will need to ensure the variable contains a valid value.

  Sub Test5()
      Dim i As Integer
      Dim varTest As Variant
      varTest = Null
      
      'Run-time error 94: Invalid use of Null
      For i = 1 To varTest
          Debug.Print i
      Next
  End Sub

• You can use the IsNull function to determine whether the expression is Null.

  Sub Test6()
      Dim varTest As Variant
     
      'returns True, indicating variable subtype Empty:
      Debug.Print IsEmpty(varTest)
      
      'returns False - is an Empty variable, not a Null variable,
      'no beginning value has been assigned to a Variant variable:
      Debug.Print IsNull(varTest)
  
      'assign Null keyword to set variable to Null
      varTest = Null
      
      'returns True - is a Null variable, not an Empty variable,
      Debug.Print IsNull(varTest)
  End Sub

Nothing

This is an object reference to an empty object. Setting the object reference to Nothing releases that object. If there are no other references to the object, Visual Basic will destroy the object. Nothing is assigned to an object variable by using the Set statement.

Sub Test7()

    Dim objTest As Object
    
    'returns True, because you have not yet assigned an actual object to the object variable:
    Debug.Print objTest Is Nothing
    
    'assign an actual object (Workbook) to the object variable
    Set objTest = ActiveWorkbook
    
    'returns False, because you have assigned an actual object (Workbook) to the object variable:
    Debug.Print objTest Is Nothing
    
    'disassociate the object variable from an actual object
    Set objTest = Nothing
    
    'returns "Variable not associated with an actual object"
    If objTest Is Nothing Then
        Debug.Print "Variable not associated with an actual object"
    Else
        Debug.Print "Actual object is assigned to an Object variable"
    End If

End Sub

It is advisable to explicity set all object variables to Nothing at the end of your procedure or even earlier while running your code when you finish using them, and this will release memory allocated to these variables.

Sub Test8()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'place your code here.
    
    Set fso = Nothing
End Sub

To check if an object has been assigned or set, use the Is keyword with Nothing.

If object_variable Is Nothing Then
    'place your code here.
End If

If Not object_variable Is Nothing Then
    'place your code here.
End If

For objects, you cannot test if an object_variable is equal to something, and using = instead of Is will give an error.

Sub Test9()
    Dim object_variable As Object
    
    'Compilt error: Invalid use of object
    If object_variable = Nothing Then
        Debug.Print "Variable not associated with an actual object"
    End If
End Sub

vbNullString, zero-length string

vbNullString is a constant, which represents an empty string. It is different from the blank string "", which means nothing string. For many occasions, it is treated as an empty string "", the real purpose of using it is to pass a null parameter to the library function.

A string is composed of two parts: a pointer and a Unicode character array, vbNullString is a string with only the first part and no second part. vbnullstring is a 4-byte null value, which is usually transmitted into the api like this 0&.

zero-length string ("") is also referred to as a null string, and has a length of zero (0). For all practical purposes using vbNullString is equivalent to a zero-length string ("") because VBA interprets both in a similar manner, though both are actually not the same - a 'zero length string' actually means creating a string with no characters, whereas vbNullString is a constant used for a null pointer meaning that no string is created and is also more efficient or faster to execute than zero-length string. You can use "" or vbNullString alternatively in your code and both behave similarly.

Note that there is no Blank keyword in vba, but we can refer to 'blank cells' or "empty cells" in Excel spreadsheet. There are Excel worksheet functions for empty cells:

• The COUNTA function counts the number of cells that are not empty, and also counts or includes a cell with empty text ("") - also referrred to as empty string or zero length string - which is not counted as an empty cell.
• The ISBLANK function returns True for an empty cell, and does not treat a zero-length string ("") as a blank (empty cell) similarly as in COUNTA. Both the worksheet functions of ISBLANK and COUNTA distinguish between an empty cell and a cell containing a zero-length string (ie. "" as formula result).