Dear Friends,
Some time during the VBA programming you may need to perform different action based on “what is the type of the variable” you are dealing with. Or in other words what kind of data is stored in a variable. In VBA, if you have not defined type of a variable while declaring it using “Dim statement”, then whatever value you assign to that variable, data type of the value is inherited by the variable.
How to find the Data Type of a Variable
Get the data type of a Variable in VBA
VarTpe(YourVariable) is a VBA function which
1. Takes your variable name as input parameter
2. Returns the data type of the variable or type of the data stored in this variable.
How to use VarType() VBA function
In the below example, code VBA.VarType(varString) will return 8. This is the return value for String Type variable. Refer the below table for return value for all the data types.
Sub GetTypeOfAllVariables()
Dim varString As String
MsgBox "Data Type of the Variable varString is : " & VBA.VarType(varString)
End Sub
Data Types and their Return Value
| Variable Type | Return Value |
|---|---|
| Variant | 0 |
| Integer | 2 |
| Long | 3 |
| Single | 4 |
| Double | 5 |
| Currency | 6 |
| Date | 7 |
| String | 8 |
| Boolean | 11 |
| Byte | 17 |
As I mentioned above, this function does not return the variable type what is defined but also based on what kind of value it has stored in it. This is quite possible that a variable defined as Variant is holding a NULL value or Nothing or Empty etc. These are special cases where VBA returns different values based on the data which this Variant variable is holding.
Where should I use this?
In VBA, it is possible to define a variable without any type (default type = Variant). This variable will keep changing its type based on the value assigned to it during run time. Then sometime, during programming it will become important to know what kind of data it has got at this moment before I perform any operation on it.
More about VarType Function
As I believe that Microsoft Excel VBA help is really rich. You should always start with the help provided there. It is available offline as well which is the best part of it. Therefore make use of it in learning VBA.
To know more about these return values which I have not specified here you can get them here:
varType – VBA Function
0 Comments