TypeName vs VarType

Question

I want to check the type Variant. This can be done with TypeNameand VarType. I assume the use is VarTypemore efficient since it does not include string comparison, just a numerical comparison. Any reason for preference TypeName?

Public Sub testType()
    Dim b() As Double
    Dim a As Variant
    a = b

    Debug.Print TypeName(a) = "Double()" 'True
    Debug.Print VarType(a) = vbArray + vbDouble 'True
End Sub

Answer 1

My recommendation

Use VarTypefor built-in types covered by an enumeration VbVarType. Use TypeNamefor other types. I will explain this recommendation in detail below.

Performance

The performance difference is likely to be negligible, especially if you use VBA to write database applications.

Vartype

VarType , : vbDouble, (, Option Explicit, ). "Double()", .

TypeName

TypeName , , VbVarType:

Dim b As New Collection
Dim a As Variant
Set a = b

Debug.Print VarType(a)      ' Prints just the generic vbObject constant
Debug.Print TypeName(a)     ' Prints "Collection"

Gotchas

, , VarType , , vbObject. MS Access VBA TempVar:

TempVars("x") = 123

Dim a As Variant
Set a = TempVars("x")

Debug.Print VarType(a)  ' Prints vbInteger, the type of a.Value current content.
                        ' (Value is TempVar default property)

Debug.Print TypeName(a) ' Prints "TempVar"