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.
Here’s the syntax for the `VarType` function:
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
You can use `VarType` in your VBA code like this:
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
