Excel VBA VarType Function
VBA VARTYPE means “Variable Type”. This function helps us to identify the data type assigned to the specific variable or in simple word we can say it finds what kind of value is stored or assigned to the variable.
VarName: We just need to supply the variable name to find the data stored in the supplied variable name.
So, it takes the variable name as the syntax or argument and in the output, it returns the data type assigned to the variable or the kind of data stored in the variable.
In VBA while writing the code we usually declare variable and assign a data type to them. For an example look at the below VBA code.
Sub VarType_Example() Dim MyVar As String MyVar = "Hello" End Sub
In the above example, we have declared the variable as “String” and for this string, we have assigned the value as “Hello”.
This is a straight forward case but it is also possible to declare the variables without assigning the variables to them, so in such cases, VarType function helps us.
Sub VarType_Example() Dim MyVar MyVar = "Hello" End Sub
In the above code we have not assigned any data type but straight away assigned the value as “Hello”, so by using the VarType function we can find the data type of the variable.
Open MSGBOX in VBA coding in the above code.
Then open the VarType function.
Now enter the variable name as the argument of the VARTYPE function.
Sub VarType_Example() Dim MyVar MyVar = "Hello" MsgBox VarType(MyVar) End Sub
Now run the code and see what we get in the message box.
We got the result as 8 because VBA has certain codes for each kind of variable data type, so below is the detailed list for you.
|0||vbEmpty||Variable is not yet initialized|
|1||vbNull||Not valid data is assigned|
|2||vbInteger||The variable value is “Integer” data type|
|3||vbLong||The variable value is “Long” data type|
|4||vbSingle||The variable value is “Single” data type|
|5||vbDouble||The variable value is “Double” data type|
|6||vbCurrency||The variable value is “Currency” data type|
|7||vbDate||The variable value is “Date” data type|
|8||vbString||The variable value is “String” data type|
|9||vbObject||The variable value is “Object” data type|
|10||vbError||The variable value is Error Value|
|11||vbBoolean||Variable value is “Boolean” data type|
|12||vbVariant||The variable value is “Variant” data type (used only with arrays of variants)|
|13||vbDataObject||The variable value is the Data Access Object|
|14||vbDecimal||Variable value is “Decimal” data type|
|17||vbByte||The variable value is “Byte” data type|
|20||vbLongLong||The variable value is “LONG LONG” data type (valid on 64-bit platforms only)|
|36||vbUserDefinedType||Variable value is “User Defined” data type|
|8192||vbArray||The variable value is Array|
Ok, now our code has retuned the variable data type as 8 i.e. variable name “MyVar” contains “String” data type.
Now, look at the below code.
Sub VarType_Example2() Dim MyVar Set MyVar = ThisWorkbook MsgBox VarType(MyVar) End Sub
Let’s run this code and see what the result is.
The result is 9 i.e. variable contained the “Object” data type. Yes, this is correct because for the variable “MyVar” we have set the workbook reference of “This Workbook”.
Now, look at the below code.
Sub VarType_Example3() Dim MyVar MyVar = 32500 MsgBox VarType(MyVar) End Sub
This will return the result as 2.
Because the number 32500 which is assigned to the variable is an “Integer” value.
Now I will change the value to 40000 and see the result.
Sub VarType_Example4() Dim MyVar MyVar = 40000 MsgBox VarType(MyVar) End Sub
This will give the result as 3.
Because Integer value is ending at 32767, so anything above that will be treated as a LONG data type.
Now I will enclose the number in double-quotes.
Sub VarType_Example5() Dim MyVar MyVar = "40000" MsgBox VarType(MyVar) End Sub
Run the code and see the result.
We got the result as 8 i.e. String data type.
This is because anything supplied within the parenthesis will be treated as a String variable.
Things to Remember
- VARTYPE stands for “Variable Type”.
- Data Type is represented by unique numbers so refer the table to understand which number represents which variable data type.
This has been a guide to VBA VarType. Here we learn how to find the variable data type or the kind of data assigned to the variable using excel vba VarType function with examples. You can learn more from the following VBA articles –
- VBA CreateObject Examples
- Excel VBA IsDate
- VBA Replace String Function
- VBA CLng Function
- Variable Declaration in VBA
- Public Variables in VBA
- Global Variables in VBA
- Return Statement in VBA