TypeName vs VarType

Question

I want to check the type of a Variant. It is possible to do it with TypeName and VarType. I guess that using VarType is more efficient, since it doesn't involve String comparison, just a numeric comparison. Any reason for preferring 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 VarType for built-in types covered by the VbVarType enumeration. Use TypeName for other types. I'll explain this recommendation in detail below.

Performance

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

VarType

The big advantage of VarType is that it does not use magic strings: If you misspell vbDouble, you get a compile time error (assuming that you use Option Explicit, which you should). If you misspell "Double()", you code will just silently do the wrong thing.

TypeName

The advantage of TypeName is that it also works for types which are not covered by the VbVarType enumeration:

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

Note that if the variable contains an object with a default property, VarType returns the type of the value contained in the default property instead of vbObject. Here is an example using MS Access VBA's TempVar class:

TempVars("x") = 123

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

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

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

Answer 2

TypeName seems more specific when used with objects (such as Ranges or Sheets).

For example with a LastRow function, which you want to call from other vba functions but also from worksheets cells. When passing a sheet name from another cell it directs it as a Range but according to VarType it is a String (and not an range/object).

Public Function LastRow(ByVal sht As Variant, Optional ByVal colLetter As String = "NoColGiven") As Long
    Dim aSheet As Worksheet
    Select Case TypeName(sht)
        Case "Worksheet" 'VarType 9 (vbObject)
            'call from vba -> LastRow(Sheets(1))
            Set aSheet = sht
        Case "String" 'VarType 8 (vbString)
            'call from vba/ws function -> LastRow("Sheets1")
            Set aSheet = Sheets(sht)
        Case "Range" 'VarType 8 (vbString)
            'call from ws function -> LastRow(A1) where A1 has textvalue "Sheet1"
            Set aSheet = Sheets(sht.Value)
    End Select

    If colLetter = "NoColGiven" Then
        LastRow = aSheet.UsedRange.Rows(aSheet.UsedRange.Rows.Count).row 'last row in whole sheet
    Else
        LastRow = aSheet.Cells(aSheet.Rows.Count, colLetter).End(xlUp).row 'last row in specified column of sheet
    End If
End Function