Null, Empty, etc

What are the differences among Empty, False, Missing, Null, Nothing, vbNullString, zero, and a ZLS?

VBA certainly has a lot of ways to talk about something that's not there!

Empty is both a keyword and a special value that Variants can contain. It indicates an uninitialized Variant.
It is not the same as Null. Empty is rare compared to the other concepts we are discussing here, you probably won't run across it much "in the wild". VBA has the IsEmpty function to tell you if the variable is Empty.

False is a keyword for Boolean values. In a numerical context it is interpreted as the value 0. In a string context, it is interpreted as the string "False".

Now this would be very bad style, but:

vba code:

Public Function FalseTest()
Dim strX As String
Dim intX As Integer

strX = Left$(False, 1)
intX = 100 * False

Debug.Print "strX: " & strX
Debug.Print "intX: " & intX

If (False = 0) Then
Debug.Print "False equals 0"
End If

If (False = "False") Then
Debug.Print "False equals 'False'"
End If
End Function


strX: F
intX: 0
False equals zero
False equals 'False'

You probably shouldn't use False like this. Use it with Boolean values and there's little room for confusion.

It is actually incorrect to talk about Missing, as it is neither a keyword nor value. VBA does have a function, IsMissing that returns True if an optional Variant argument has not been passed to a procedure. IsMissing will not work with simple data types, only with Variants.

Null, like Empty, is both a keyword and a special value Variants can contain. It indicates the variable contains no valid data. This usually causes people problems when reading a field from a table that contains no data. Null is not equal to anything, not even itself! VBA has the IsNull function to tell you if a variable contains Null, and the Nz function to return an expected value (like zero or "") if a variable contains Null. Perhaps the best way to think about Null is as meaning "Unknown".

Nothing is a keyword used to break the reference between an object variable and the actual object. When no variables reference an object, the memory and system resources are released.

Although Access/VBA theoretically handles this "reference-breaking" automatically when a variable goes out of scope, it is considered good practice for the programmer to do this explicitly, through Set objMyObject = Nothing.

vbNullString is a constant that is handled in a special way by VBA. The primary use of vbNullString is to allow you to pass a null pointer to a DLL. C/C++ routines (many DLLs are written in C/C++) often require a null pointer, but VBA does not. vbNullString bridges that gap.

A secondary (and perhaps more common) use is as a synonym for a zero length string. That is, where you would use "" in code, you can use vbNullString instead. Now, at the lower levels, vbNullString is not the same thing as "", but VBA is smart enough to see how you are using it and it handles the interpretation for you. In fact, it handles it so well that it is more efficient to use vbNullString in the place of "".

zero (0)
Zero is 1-1. It's just in here to remind you that none of these things are exactly the same as zero and vice-versa.

ZLS is an abbreviation for zero length string, some people like to call it an empty string. You often see ZLS string in code as "" (although as we talked about above vbNullString is a better choice).

The most common source of errors here is confusing ZLS with Null, usually when reading a field, expecting to get a string, but the field has no data and thus gives Null. This raises the all-to-familiar run-time error "Invalid use of Null".

By default, leaving a field blank in a table stores Null. You can change this behavior by changing the AllowZeroLength property of the field.

And finally, a little sample function. First, try to figure out what the results will be, then run it and see:

Public Function Test(Optional x As Variant)
Dim y As Variant
Dim z As Variant

z = Null

Debug.Print " " & vbTab & "Empty" & vbTab & "Null" & vbTab & "Missing"
Debug.Print "x:" & vbTab & IsEmpty(x) & vbTab & IsNull(x) & vbTab & IsMissing(x)
Debug.Print "y:" & vbTab & IsEmpty(y) & vbTab & IsNull(y) & vbTab & IsMissing(y)
Debug.Print "z:" & vbTab & IsEmpty(z) & vbTab & IsNull(z) & vbTab & IsMissing(z)

'Type mismatch error
'If (x = y) Then
' Debug.Print "x = y"
'End If

'Type mismatch error
'If (x = z) Then
' Debug.Print "x = z"
'End If

If (y = z) Then
Debug.Print "y = z"
End If

'This sometimes catches people
If (z = Null) Then
Debug.Print "z = Null"
End If

If (Null = Null) Then
Debug.Print "Null = Null"
End If

If (Null = vbNullString) Then
Debug.Print "Null = vbNullString"
End If

If (Null = 0) Then
Debug.Print "Null = 0"
End If

'Another tricky one
If Not(Null) Then
Debug.Print "Not Null"
End If
End Function