The varType Function in Excel VBA

Here are some custom VBA functions that use the varType function to check the variable type. Very useful when debugging your application to test if your variables are being implemented properly, they can also be used as drop-in procedures to check the expected value of a variable. In keeping with the spirit of modular programming, they are small self-contained procedures that can be copied and pasted elsewhere. All you have to do is pass the variable name to them and they return a string naming the variable type.

They are declared as Private so they cannot be accessed from the GUI, and they can only be called from the same module they are placed in.

I have also provided some examples you can use to test out each function.

Option Explicit

Private Function GetVarType1(vType As Variant) As String

Select Case varType(vType)
    Case 0
        GetVarType1 = "vbEmpty"
    Case 1
        GetVarType1 = "vbNull"
    Case 2
        GetVarType1 = "vbInteger"
    Case 3
        GetVarType1 = "vbLong"
    Case 4
        GetVarType1 = "vbSingle"
    Case 5
        GetVarType1 = "vbDouble"
    Case 6
        GetVarType1 = "vbCurrency"
    Case 7
        GetVarType1 = "vbDate"
    Case 8
        GetVarType1 = "vbString"
    Case 9
        GetVarType1 = "vbObject"
    Case 10
        GetVarType1 = "vbError"
    Case 11
        GetVarType1 = "vbBoolean"
    Case 12
        GetVarType1 = "vbVariant"
    Case 13
        GetVarType1 = "vbDataObject"
    Case 14
        GetVarType1 = "vbDecimal"
    Case 17
        GetVarType1 = "vbByte"
    Case Is >= 8192
        GetVarType1 = "vbArray"
    Case Else
        GetVarType1 = vbNullString
End Select

End Function

Private Function GetVarType2(vType As Variant) As String

Select Case varType(vType)
    Case 0
        GetVarType2 = "Empty"
    Case 1
        GetVarType2 = "Null"
    Case 2
        GetVarType2 = "Integer"
    Case 3
        GetVarType2 = "Long"
    Case 4
        GetVarType2 = "Single"
    Case 5
        GetVarType2 = "Double"
    Case 6
        GetVarType2 = "Currency"
    Case 7
        GetVarType2 = "Date"
    Case 8
        GetVarType2 = "String"
    Case 9
        GetVarType2 = "Object"
    Case 10
        GetVarType2 = "Error"
    Case 11
        GetVarType2 = "Boolean"
    Case 12
        GetVarType2 = "Variant"
    Case 13
        GetVarType2 = "Data Object"
    Case 14
        GetVarType2 = "Decimal"
    Case 17
        GetVarType2 = "Byte"
    Case Is >= 8192
        GetVarType2 = "Array"
    Case Else
        GetVarType2 = vbNullString
End Select

End Function

Sub TestMe()

Dim MyVariable As Boolean
Dim WhatIsIt As String

' test #1 using typename
WhatIsIt = GetVarType1(MyVariable)
MsgBox WhatIsIt

' test #2 using friendly name
WhatIsIt = GetVarType2(MyVariable)
MsgBox WhatIsIt

' test #3 using If-Then
If GetVarType1(MyVariable) = "vbBoolean" Then
    MsgBox "I'm happy!"
End If

' test #4 using Select Case with typename
Select Case GetVarType1(MyVariable)
    Case "vbInteger", "vbLong", "vbSingle", "vbDouble"
        MsgBox "It's a number!"
    Case "vbBoolean"
        MsgBox "It's a True/False value"
End Select

' test #5 using Select Case with friendly name
Select Case GetVarType2(MyVariable)
    Case "Integer", "Long", "Single", "Double"
        MsgBox "It's a number!"
    Case "Boolean"
        MsgBox "It's a True/False value"
End Select

' Select Case using standard varType function
Select Case varType(MyVariable)
    Case 2, 3, 4, 5
        MsgBox "It's a number!"
    Case 11
        MsgBox "It's a True/False value"
    Case Is > 8192
        MsgBox "It's an Array of some type"
    Case Else
        MsgBox "Who cares?"
End Select

End Sub

GetVarType1 returns the name of the constant, where GetVarType2 returns a friendly name; this is what the first two examples show. The third sample is an example of how you can use this function to test a variable. Note that you can declare the variable without actually initializing it, and the function still knows what type of variable it is.

The fourth and fifth samples show how you can use a Select Case statement to test the variable type, while the last one simply uses the varType function. Of course, you could just use the varType function, but the purpose of these custom functions is to convert the "magic number" constants to friendlier names for you. These self-contained procedures can be dragged and dropped into any procedure you choose.

The varType Function in Excel VBA

LAST UPDATED: April 8, 2008