VBA: IsEmpty, vbEmpty, "Empty" and Empty

Question

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

Cofer

In the docs you mention say isempty only applies to variants. Basic is more than one dialect. So this is basic - all variables have a default value. for strings If a = "", for numbers if a = 0, for bool if a = 0, if a <> 0 (remembering true is not zero), if a = false. For a new variant all the above are true. a = "" = 0 = false. – Clingfish

None of that has anything to do with the question. – Cofer

Answer 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

Fanfaronade

And by the way, I only wrote IsEmpty(var) = True to make explicit that the function yields a Boolean and that the result we are comparing is true. Of course, as you say, just IsEmpty(var) is the way to write it in code. – Cofer

@Cofer Thanks for the explanation. I thought it might be the case but then I figured it wouldn't hurt to draw the attention of the other readers so that they don't use the IsEmpty(var) = True syntax in real code . Glad my answer helped! – Fanfaronade

@Cofer I just put up a +100 bounty that will go to this excellent post =) – Shimmer

@MathieuGuindon - You addressed that comment to me, but I presume you meant it for Christian. – Cofer

@Cofer not at all, just granting that wish about giving this answer 10x the votes ;-) – Shimmer

Oh, cool. Except that the comment with that wish has apparently been deleted. I guess we're not allowed to say thank you around here. – Cofer

@CristianBuse - I wrote to the author of Decision Models to ask if there's a reason he used vbEmpty in his function IsCalced() and IsEmpty() everywhere else on that page (linked in my question). His answer seems to claim that it is not true that IsEmpty() always equals VarType() = vbEmpty: "If var1 is a variant containing an array var1 (defined as varr(1 to 10)) then Vartype(var1) and vartype(varr) both return 8204 (vbarray + vbvariant) not vbempty (which is zero)". So, is this an exception to the equivalence of IsEmpty() and VarType() = vbEmpty? – Cofer

@Cofer Just to be sure I understand correctly, you are saying that we have the following code Dim var1 As Variant then Dim varr(1 To 10) As Variant and then var1 = varr? If your answer is yes, then both VarType(var1) and VarType(varr) return 8204 indeed. Also both IsEmpty(var1) and IsEmpty(varr) return False. So, there is no exception because both VarType(var1) = vbEmpty and VarType(varr) = vbEmpty also return False. There is still equivalence. I see no contradiction but maybe I misunderstood your question. – Fanfaronade

@CristianBuse - I was quoting from the e-mail from the Decision Models author. And it appears that you are right: what he wrote does not contradict the equivalence. I was getting fogged in the logic. In the e-mail, the part I quoted came right after, "When dealing with arrays you really should use vartype." I believe he was saying to use VarType() = vbEmpty instead of IsEmpty(), so I thought he was giving a case where they're not the same. But yeah, they are the same there. I'm writing back to see what he was getting at and will update here when I get a reply. – Cofer

@Cofer Before you get an answer, using IsEmpty(array) or VarType(array) = vbEmpty makes no difference. They will both return False 100%. However, if the array is wrapped in a Variant then all I wrote in my answer above still applies regardless if the Variant points to an array or not. As mentioned, behind the scenes IsEmpty does the exact check VarType() = vbEmpty and so they will always be equivalent. – Fanfaronade

Answer 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.

Cofer

IsEmpty is only useful for testing Variants, all other datatypes including Object are initialised which is what IsEmpty tests for. The GitHub example shows 'Obj As Variant' passed to the function for testing so it seems likely it was declared as a Variant in the calling procedure, and thus in the example IsEmpty works. – Numismatist

@PeterT - I didn't say IsEmpty() doesn't work in the GitHub code. I said the code doesn't seem to work. According to the accepted answer, IsEmpty(Obj) is always equal to (TypeVar(Obj) = vbEmpty). Therefore, in the GitHub code, IsEmpty(Obj) will always be true and the else clause will never be entered. That's what I meant by saying the code doesn't seem to work. – Cofer

Apologies, I didn't read the code in full, only that if Obj is a variant, as implied in the GitHub example IsEmpty(Obj) could = False. As You say, in the select case if VarType(Obj) = vbEmpty is true IsEmpty(Obj) would also always = True. I guess in theory there could be a difference with the default property of an object, but highly unlikely here! – Numismatist

@PeterT - Could you explain that theory? If there's an exception to IsEmpty() = VarType() = vbEmpty, I need to understand that. – Cofer

Almost immediately after posting I wanted to remove that 'in theory' but it was too late, 'edit' wasn't available. Please consider it withdrawn! It occurred to me object and the default property of an object, if there is one, might test differently with with IsEmpty and VarType, but they wouldn't. In Passing I'd normally check an object with 'Is Nothing', though if originally declared as a variant would first need to check its VarType is vbObject. – Numismatist

@PeterT Apologies if I misinterpreted your last comment. Just to clarify: to test for an object in a variant you must always use IsObject because VarType could return the type of the default property of the object. Both IsEmpty and VarType will operate on an object's default property, if there is one, unlike TypeName which ignores the default property. In rest, you use IsEmpty(x)/VarType(x) = vbEmpty to check if a Variant has the first 2 bytes set to vbEmpty but always have to keep in mind the default property nuance. – Fanfaronade

We seem to be apologising to each other:) If (lets name it) vObj is not initialised as an Object testing IsObject will error. OTH, once initialised and later do Set vObj = Nothing, vObj IsObject will work fine. That’s what I meant first need to check VarType(vOb) = vbObject before can test vObj Is Nothing. But you've thrown my spanner back at me! As we’ve both now said, if the default property of the Object is a variant, VarType(vObj) tests its default and could = True even if vObj is a fully functioning object. Fortunately ‘Is Nothing’ tests the object and not its default property. – Numismatist

... ran out of characters - which leaves a potential conundrum! – Numismatist