Working with Empty in VBA
A deep dive into the Empty keyword in VBA: why it exists, how to check for it, when it makes sense to check for it, and--most importantly--how NOT to check for it.
Empty is one of many ways to express the concept of nothingness in VBA. For the others, check out An Article About Nothing.
Empty
From the VBE glossary entry for 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.
IsEmpty()
The proper way to check whether a variable is nothing in VBA is via the IsEmpty() function.
Returns a Boolean value indicating whether a variable has been initialized.
You should never use the equal sign with the Empty keyword to check if a variable or value is Empty (more on that below).
Sub TestEmpty(MyVar As Variant)
'GOOD:
Debug.Print "Is MyVar Empty? "; IsEmpty(MyVar)
'BAD:
Debug.Print "Is MyVar Empty? "; MyVar = Empty
End SubPractical Usage: Guard Clauses
I rarely use the IsEmpty() function to test the contents of a local Variant variable.
Generally speaking, I try to ensure that a local Variant variable gets initialized before I try to interact with it, regardless of which code path gets followed. However, if I have a routine has a required Variant parameter, it might be wise to check to see whether the calling code passed me an Empty value, like so:
Sub MyRoutine(MyParam As Variant)
If IsEmpty(MyParam) Then Throw "MyParam cannot be empty"
End SubFor more information about Guard Clauses and Throwing Errors in VBA, see the related articles:
Mike Wolfe
Mike Wolfe
Never Use = Empty to Check for Empty
Ben Clothier posted a great, succinct explanation in the comments section of An Article About Nothing:
IMO, I think Empty is the worst variant because if one doesn't understand how Variant and implicit conversion work, the following expression can appear downright bizarre:
?Empty = 0 True ?Empty = vbNullString True ?Empty = #12:00 AM# True ?Empty = vbNullChar False ?Empty = Null Null ?IsNull(Empty) False ?IsMissing(Empty) False ?IsEmpty(Empty) TrueAll of those make logical sense though not necessarily intuitive and requires thinking about because VBA's implicit conversion of variants is really what distorts the evaluation.
Let's make sense of each of Ben's examples.
?Empty = 0 Returns True
From the VBE glossary entry for Empty:
An Empty variable is represented as 0 in a numeric context...
An explicit conversion of the value Empty to Integer produces a value of zero:
?CInt(Empty)
0 Thus, if we use the result of the explicit conversion, the result of the comparison makes perfect sense:
?CInt(Empty) = 0
TrueHowever, VBA does not require explicit conversion among data types.
If you directly compare two different data types in VBA, the language will attempt an implicit conversion of one of the values to a matching data type before performing the comparison. When we compare the value Empty to 0, VBA first performs an implicit conversion of Empty to 0.
In the end, VBA is reducing the expression ?Empty = 0 to ?0 = 0 via implicit conversion.
?Empty = vbNullString Returns True
From the VBE glossary entry for Empty:
An Empty variable is represented as ... a zero-length string ("") in a string context.
The more interesting portion of this expression is not Empty; it is vbNullString. That's because vbNullString is not the same as "". In fact, vbNullString does not exist as a string in memory at all. Rather, it is a constant that represents a "null string pointer." It has a memory address of 0:
?StrPtr(vbNullString)
0Let's turn to the VBA Language Specification for some clarification. From section 6.1.2.2, Constants Module:
vbNullString: An implementation-defined String value representing a null string pointer
I could not find any concrete documentation on how the Office VBA implementation represents a null string pointer, but from some cursory testing it appears that it treats it as a zero-length string for comparison purposes (see the expression ?vbNullString = "" in the screenshot below):
For practical purposes, then, we can assume VBA is reducing the expression as follows:
?Empty = vbNullString
?Empty = ""
?"" = ""?Empty = #12:00 AM# Returns True
From the VBE glossary entry for Empty:
An Empty variable is represented as 0 in a numeric context...
Dates are considered numeric for the purpose of Empty comparisons. The Date literal #12:00 AM# represents a numeric date value of 0 (zer0), which is why the ?Empty = #12:00 AM# expression returns True.
?Empty = vbNullChar Returns False
The vbNullChar constant represents a single Null character (i.e., ASCII code 0).
The easiest way to understand why this particular comparison returns False is to realize that Empty is represented by a zero-length string while vbNullChar is a string with a length of 1:
?Empty = Null Returns Null
Comparing any value to Null with the equals sign always returns Null.
This behavior is documented in section 5.6.9.5 of the VBA Language Specification, "Relational Operators." The equals sign is tokenized as the "equality-operator-expression."
As per the specification, "[t]he effective value type is determined as follows, based on the value types of the operands:"
?IsNull(Empty) Returns False
The IsNull() function is only used to check whether the passed value is Null or non-Null. The IsNull() function tells us nothing about whether the passed value is Empty or not Empty, as this sample code shows:
For more information about Null values, check out this related article, Working with Null in Microsoft Access:
Mike Wolfe
?IsMissing(Empty) Returns False
The IsMissing() function is only used to check whether an optional variant parameter has been provided by the calling code. See my article on The Missing Keyword in VBA for details:
Mike Wolfe
?IsEmpty(Empty) Returns True
As our alternative examples above prove, using the IsEmpty() function is the only proper way to check if a value contains Empty or not.
UPDATE [2023-09-06]: Added introductory paragraph with a link to An Article About Nothing.
