Search code examples
vbais-empty

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

  • 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