VBA VarType Function

The VBA VarType function returns information on the VBA Data Type of a given variable. It returns a number corresponding to one of the values of the VbVarType VBA Enum object.

The Excel VBA VarType function comes useful in a variety of scenarios especially when we are passing Variant objects into a VBA Sub or Function as a parameter.

VarType Function Syntax

The syntax for the VarType function in VBA is:

VarType ( expression )

expression
A VBA variable for which we want to learn the VbVarType.

Example usage

To exemplify the usage of the VBA VarType function let us create a custom VBA Sub called PrintType that will print in the Immediate window the name of the data type we provide:

Sub PrintType(typeCheck As Variant)
    Select Case varType(typeCheck)
        Case VbVarType.vbArray:
            Debug.Print "Array"
        Case VbVarType.vbBoolean:
            Debug.Print "Boolean"
        Case VbVarType.vbByte:
            Debug.Print "Byte"
        Case VbVarType.vbCurrency:
            Debug.Print "Currency"
        Case VbVarType.vbDataObject:
            Debug.Print "Data Object"
        Case VbVarType.vbDate:
            Debug.Print "Date"
        Case VbVarType.vbDecimal:
            Debug.Print "Decimal"
        Case VbVarType.vbDouble:
            Debug.Print "Double"
        Case VbVarType.vbEmpty:
            Debug.Print "Empty"
        Case VbVarType.vbError:
            Debug.Print "Error"
        Case VbVarType.vbInteger:
            Debug.Print "Integer"
        Case VbVarType.vbLong:
            Debug.Print "Long"
        Case VbVarType.vbNull:
            Debug.Print "Null"
        Case VbVarType.vbObject:
            Debug.Print "Object"
        Case VbVarType.vbSingle:
            Debug.Print "Single"
        Case VbVarType.vbString:
            Debug.Print "String"
        Case VbVarType.vbUserDefinedType:
            Debug.Print "UserDefinedType"
        Case VbVarType.vbVariant:
            Debug.Print "Variant"
    End Select
End Sub

I listed above all values within the VBA Enum called VbVarType. Now we can use this function to quickly test the data type of a given variable. See examples below:

PrintType CDate("2019-01-01")
'Result: Date
    
Dim longType As Long
PrintType longType
'Result: Long
   
Dim variantType As Variant
PrintType variantType
'Result: Empty
variantType = "Hello"
PrintType variantType
'Result: String
    
Dim xlApp As Excel.Application
PrintType xlApp
'Result: Object