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
Now this would be very bad style, but:
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"
If (False = "False") Then
Debug.Print "False equals 'False'"
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 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
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 &
Debug.Print "y:" & vbTab & IsEmpty(y) & vbTab & IsNull(y) & vbTab &
Debug.Print "z:" & vbTab & IsEmpty(z) & vbTab & IsNull(z) & vbTab &
'Type mismatch error
'If (x = y) Then
' Debug.Print "x = y"
'Type mismatch error
'If (x = z) Then
' Debug.Print "x = z"
If (y = z) Then
Debug.Print "y = z"
'This sometimes catches people
If (z = Null) Then
Debug.Print "z = Null"
If (Null = Null) Then
Debug.Print "Null = Null"
If (Null = vbNullString) Then
Debug.Print "Null = vbNullString"
If (Null = 0) Then
Debug.Print "Null = 0"
'Another tricky one
If Not(Null) Then
Debug.Print "Not Null"