How to use VarType function in VBA?

In VBA (Visual Basic for Applications), the VarType function returns information about the variable type of a given expression. This could be useful when you want to check the data type of a variable at runtime. The function returns an integer that corresponds to a constant representing the variable type.

Basic syntax



Where expression is any valid VBA expression.

The function returns a value from the VariantType enumeration. Some of the VariantType constants include:

  • vbEmpty (0) Empty (uninitialized)
  • vbNull (1) Null (no valid data)
  • vbInteger (2) Integer
  • vbLong (3) Long integer
  • vbSingle (4) Single-precision floating-point number
  • vbDouble (5) Double-precision floating-point number
  • vbCurrency (6) Currency
  • vbDate (7) Date
  • vbString (8) String
  • vbObject (9) Object
  • vbBoolean (11) Boolean
  • vbVariant (12) Variant (used only with arrays of variants)
  • vbByte (17) Byte
  • vbUserDefinedType (36) A user-defined type
  • vbArray (8192) Array



Sub CheckVariableType()
    Dim someValue As Variant

    ' Assign different types of values to someValue and check its VarType
    someValue = Empty
    Debug.Print "VarType(Empty): " & VarType(someValue) ' Returns vbEmpty

    someValue = 10
    Debug.Print "VarType(10): " & VarType(someValue) ' Returns vbInteger

    someValue = 123.456
    Debug.Print "VarType(123.456): " & VarType(someValue) ' Returns vbDouble

    someValue = "Hello"
    Debug.Print "VarType(""Hello""): " & VarType(someValue) ' Returns vbString

    someValue = CBool(1)
    Debug.Print "VarType(True): " & VarType(someValue) ' Returns vbBoolean

    someValue = New Collection
    Debug.Print "VarType(New Collection): " & VarType(someValue) ' Returns vbObject

    ' Checking VarType of an array
    Dim anArray() As Integer
    ReDim anArray(1 To 5)
    Debug.Print "VarType(anArray): " & VarType(anArray) ' Returns vbArray + vbInteger

    ' Close the Immediate window when done observing the results.
End Sub

After running this procedure, check the output in the Immediate Window (Ctrl+G to open it in the VBA editor). You’ll get information about the data type of the someValue variable for each of the assignments. For the array example, the result is a combination of vbArray and the VarType of the element type; you’ll need to use a bitwise AND operation with & to get the VarType of the elements in the array:


VarType(anArray) And vbArray ' This will be 0 for an array
VarType(anArray) And Not vbArray ' This will return the actual VarType of the elements

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *