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

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`. — KL-1, Jan 17 '22 at 18:14

Cristian Buse · Accepted Answer · 2022-01-21T14:32:08.923

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

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. — NewSites, Jan 21 '22 at 16:18
@NewSites 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! — Cristian Buse, Jan 21 '22 at 17:59
@NewSites I just put up a +100 bounty that will go to this excellent post =) — Mathieu Guindon, Jan 21 '22 at 19:22
@MathieuGuindon - You addressed that comment to me, but I presume you meant it for Christian. — NewSites, Jan 22 '22 at 02:11
@NewSites not at all, just granting that wish about giving this answer 10x the votes ;-) — Mathieu Guindon, Jan 22 '22 at 04:07
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. — NewSites, Jan 22 '22 at 10:22
@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`? — NewSites, Jan 22 '22 at 11:06
@NewSites 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. — Cristian Buse, Jan 22 '22 at 13:18
@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. — NewSites, Jan 22 '22 at 16:07
@NewSites 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. — Cristian Buse, Jan 22 '22 at 16:55

NewSites · Answer 2 · 2022-01-21T10:13:00.730

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.

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. — Peter T, Jan 21 '22 at 20:02
@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. — NewSites, Jan 22 '22 at 02:25
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! — Peter T, Jan 22 '22 at 10:01
@PeterT - Could you explain that theory? If there's an exception to `IsEmpty()` = `VarType() = vbEmpty`, I need to understand that. — NewSites, Jan 22 '22 at 10:27
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. — Peter T, Jan 22 '22 at 11:07
@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. — Cristian Buse, Jan 22 '22 at 19:06
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. — Peter T, Jan 22 '22 at 22:31
... ran out of characters - which leaves a potential conundrum! — Peter T, Jan 22 '22 at 22:36

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.

VBA: IsEmpty, vbEmpty, "Empty" and Empty

2 Answers2