Contents
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
VarType(expression)
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
Example
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
Excel
Access
Power BI
