VBA: IsEmpty, vbEmpty, "Empty" and Empty

In VBA, if I understand correctly, emptiness means that a variant has not been initialized, i.e., it is the default value of a variant before an assignment.

There appear to be four ways to test if a variant is empty:

IsEmpty(var) = True
VarType(var) = vbEmpty
TypeName(var) = "Empty"
var = Empty

What I want to know is if those methods are completely equivalent, or if there are subtle (or stark) differences.

It seems that they ought to be equivalent, but I was surprised to find that Microsoft's documentation on IsEmpty, on vbEmpty (1, 2), and on TypeName make no reference to each other, which I thought they would if they are equivalent.

I found two references that seem to imply the first three are the same in VBscript (where everything is a variant): CodeWiki, Herong.

It seems that there are situations that are specific to Excel. It appears that emptiness in Excel also refers to a cell not containing anything, which I suppose is equivalent to the variable representing that cell not being initiated. But the website "Decision Models" says that emptiness also refers to whether a cell value is up to date ("a calculated parameter is Empty if it references uncalculated cells"). But that page says in one place to test for that using vbEmpty and in other places says to use IsEmpty.

I found two StackOverflow questions that discuss the relationship of IsEmpty and Empty (1, 2), but not on the other two methods.

It also seems that there might be subtle differences when applied to arrays.

I found the following code snippet on GitHub, which implies that if VarType(Obj) = vbEmpty, the value of IsEmpty(Obj) may still be either true or false:

Select Case VarType(Obj)
    Case vbNull
        json_toString = "null"
    Case vbEmpty
        'dkottow check if the cell is empty to evtl. convert to null
        If IsEmpty(Obj) Then
            json_toString = "null"
        Else
            json_toString = """"""
        End If

So, pretty confusing.

To summarize, my question is, in VBA, are the following equivalent, or what are the differences in their meanings?

IsEmpty(var) = True
VarType(var) = vbEmpty
TypeName(var) = "Empty"
var = Empty

Solution 1:

All of the below is applicable to VBA regardless of the host application (Excel, Word, AutoCAD etc.) as well as VB6 and prior VB versions. It just happens that Excel works well with Variants but the below hold true regardless.

Variant

Behind the scene a Variant is a structure (tagged union) and can be used to represent any other data type in VB and a couple of special values.

The layout is:

• the first 2 bytes (Integer size) hold the VARTYPE
• bytes 3 to 8 are reserved and mainly not used - Decimal uses them though
• the following bytes can hold a value, a pointer or a flag and the number of bytes used also varies depending on application bitness (for example a pointer is 4 bytes on x32 and 8 bytes on x64)

When running VarType on a Variant the result is those 2 first bytes although they are returned as Long which is 4 bytes but since VBA's memory layout is little-endian then first 2 bytes in a Long perfectly overlap with the 2 bytes in an Integer.

We can use the CopyMemory API to demonstrate the above:

Option Explicit

#If Mac Then
    #If VBA7 Then
        Public Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr
    #Else
        Public Declare Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As Long) As Long
    #End If
#Else 'Windows
    'https://msdn.microsoft.com/en-us/library/mt723419(v=vs.85).aspx
    #If VBA7 Then
        Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    #Else
        Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    #End If
#End If

Sub TestVariantVT()
    Dim v As Variant
    Dim vt As Integer
    
    CopyMemory vt, v, 2
    Debug.Assert vt = VarType(v) 'vbEmpty
    
    v = CInt(2)
    CopyMemory vt, v, 2
    Debug.Assert vt = VarType(v) 'vbInteger
    
    v = CLng(2)
    CopyMemory vt, v, 2
    Debug.Assert vt = VarType(v) 'vbLong
    
    v = CDbl(2)
    CopyMemory vt, v, 2
    Debug.Assert vt = VarType(v) 'vbDouble
End Sub

The VARTYPE holds the data type but can also have the VT_BYREF flag set which means that the Variant is passed by reference (as an argument) to the current method which helps VB know what memory needs to be deallocated and which does not, when the method exits scope. VarType does not return the VT_BYREF flag but this is outside of the question scope. There is also a separate VT_ARRAY flag (as in vbArray) which can be used in combination with other flags to describe the contents of an array e.g. an array of integers will have the vbArray (VT_ARRAY) and the vbInteger (VT_I2) flags set (as in vbArray + vbInteger).

Unrelated to the question but related with the above, the VT_BYREF flag can be used to manipulate memory as seen in my VBA-MemoryTools repository.

IsEmpty

This is quite easy to understand once you've read the above. The IsEmpty function simply checks if the VARTYPE (first 2 bytes) of the Variant is vbEmpty (which is 0).

So yes, the 2 conditions VarType(var) = vbEmpty and IsEmpty(var) = True are always equivalent.

I need to draw attention that most people don't use the IsEmpty(var) = True syntax because IsEmpty already returns a boolean. I, at least will never write something like If IsEmpty(var) = True Then but instead will always write If IsEmpty(var) Then. The latter is cleaner.

VarType

A few notes. You may be wondering what happens when we pass a non-Variant to the VarType function. Well, the VarName argument is of type Variant and so if you pass a non-Variant it actually gets wrapped in a Variant. Inspecting the VBE7.dll reveals this: VbVarType _stdcall VarType([in] VARIANT* VarName);

Note the remark on the link above:

If an object is passed and has a default property, VarType(object) returns the type of the object's default property.

This means that to check for objects you need to use IsObject which checks if the VARTYPE bytes are set to vbObject. In this particular case (objects) the two VarType(var) = vbObject and IsObject(var) are not always equivalent.

However, the above remark does not influence the equivalence of VarType(var) = vbEmpty and IsEmpty(var) because the latter will also check an object's default member.

Empty

In VB*, Empty is just a keyword but is the equivalent of a Variant with the first 2 bytes set to vbEmpty. It's there for convenience in the same way Null is (Variant with first 2 bytes set to vbNull).

Hence, comparing a Variant with Empty is like comparing 2 Variants. When comparing 2 Variants, there are some special rules that apply. Stated here:

If expression1 and expression2 are both Variant expressions, their underlying type determines how they are compared. The following table shows how the expressions are compared or the result from the comparison, depending on the underlying type of the Variant.

If	Then
Both Variant expressions are numeric	Perform a numeric comparison.
Both Variant expressions are strings	Perform a string comparison.
One Variant expression is numeric and the other is a string	The numeric expression is less than the string expression.
One Variant expression is Empty and the other is numeric	Perform a numeric comparison, using 0 as the Empty expression.
One Variant expression is Empty and the other is a string	Perform a string comparison, using a zero-length string ("") as the Empty expression.
Both Variant expressions are Empty	The expressions are equal.

So, var = Empty is NOT the equivalent of VarType(var) = vbEmpty/IsEmpty(var). Quick example: if var is an empty string ("") or a null string (vbNullString) then var = Empty returns True while VarType(var) = vbEmpty and IsEmpty(var) both return False.

TypeName

TypeName is quite different as it returns a String.

It is quite useful when used with objects. For example if var is a Collection then VarType(var) returns vbObject while TypeName(var) returns Collection. So, TypeName offers some more information. Same with arrays: TypeName(Array()) returns Variant() but depending on the array type it can return Integer() or Double() and so on.

That's why you are seeing Range when your parameter is an Excel.Range wrapped in a Variant. The actual VARTYPE is vbObject but TypeName goes a step further and checks the type of the object.

I think in your Excel example you are actually interested in the Range.Value property. If var is a Range then TypeName(var.Value) = "Empty" is just by coincidence equivalent with IsEmpty(var.Value) but only because the .Value property never returns an object but if it did then they would not be equivalent anymore. However, TypeName(var) will never be equivalent with IsEmpty(var) if var is an object.

Note that TypeName does not look at the default member of an object.

Conclusion

• VarType(var) = vbEmpty is always the equivalent of IsEmpty(var).
• var = Empty follows the rules of comparing two variants and so is not equivalent with the 2 above.
• TypeName(var) = "Empty" is only equivalent with VarType(var) = vbEmpty/IsEmpty(var) if var is NOT an object.

IsMissing

Just to clarify, because you've shown it in your own answer, if a variant has the vbError type (first 2 bytes VT_ERROR) and the SCODE member (bytes 9 to 12) set to DISP_E_PARAMNOTFOUND (0x80020004) then VB* sees it as the special Missing value.

The following code returns the special Missing value:

Public Function Missing() As Variant
    Missing = &H80020004 'Sets bytes 9 to 12
    CopyMemory Missing, vbError, 2 'Sets first 2 bytes
End Function

Solution 2:

Okay, I've done some testing in Excel. I don't intend to accept this answer because I don't think it's a definitive answer to my question because:

• It's specific to Excel, so I don't know how these results will carry over to Access and other Office programs.
• It's just a test of a variety of cases. A definitive answer would be based on knowledge of the algorithms used to calculate IsEmpty(), VarType, and TypeName(), and to assign Empty.

With that disclaimer, here is the VBA function used for the test:

Function vTestEmptiness(sCellOrVar As String, sTest As String, Optional vCell As Variant) As Variant

Dim vVar As Variant

Select Case sCellOrVar
    Case "Cell":
        Select Case sTest
            Case "IsEmpty":   vTestEmptiness = IsEmpty(vCell)
            Case "VarType":   vTestEmptiness = Choose(VarType(vCell) + 1, _
                                                        "vbEmpty", "", "", "", "", "vbDouble", _
                                                        "", "", "vbString", "", "vbError")
            Case "TypeName":  vTestEmptiness = TypeName(vCell)
            Case "Empty":     vTestEmptiness = (vCell = Empty)
            Case "IsNull":    vTestEmptiness = IsNull(vCell)
            Case "IsMissing": vTestEmptiness = IsMissing(vCell)
          End Select
    Case "Var":
        Select Case sTest
            Case "IsEmpty":   vTestEmptiness = IsEmpty(vVar)
            Case "VarType":   vTestEmptiness = Choose(VarType(vVar) + 1, _
                                                        "vbEmpty", "", "", "", "", "vbDouble", _
                                                        "", "", "vbString", "", "vbError")
            Case "TypeName":  vTestEmptiness = TypeName(vVar)
            Case "Empty":     vTestEmptiness = (vVar = Empty)
            Case "IsNull":    vTestEmptiness = IsNull(vVar)
            Case "IsMissing": vTestEmptiness = IsMissing(vVar)
          End Select
  End Select

End Function         ' vTestEmptiness()

Here are the formulas that make up the test using that function:

And here are the results:

From those results, I conclude the following:

• IsEmpty() and VarType() = vbEmpty appear to be equivalent in Excel in the sense that whenever IsEmpty() is true or false, VarType() is, respectively, vbEmpty or not vbEmpty.
• IsEmpty() and TypeName() = "Empty" are definitely not completely equivalent in Excel because when IsEmpty() is true, TypeName() may or may not be "Empty".
• IsEmpty() and Empty are definitely not completely equivalent in Excel because in the four cases tested that did not result in an error, IsEmpty() was true or false, but the tested variable was always equal to Empty.

And so, it seems that in Excel, one can use IsEmpty() and VarType() = vbEmpty interchangeably, but one has to be careful about differences between those and TypeName() = "Empty" and Empty.

From these results, I don't see how the code from GitHub cited in my question works. It seems that in that code, IsEmpty(Obj) would never be false.

I hope someone who knows what's going on under the hood in VBA will speak up about what's really going on here.