varType Function
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, but that means 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.
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
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
Usage:
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 project you choose.
