VBA Variant Data Type
Variant Data Type in VBA is a universal data type which can hold any kind of data type in VBA, but while assigning the data type we need to use the word “Variant”.
We all know how important variables are in VBA projects. Once the variable is declared we need to assign a data type to the declared variables. Data type assignment in VBA is dependent on what kind of data we need to assign to the declared variables.
For an example look at the below code.
In the above code, I have declared the variable as “IntegerNumber” and I have assigned the data type as “Integer”.
Before assigning the data type to the variable I should be aware of the limitations of the variable. Since I have assigned the data type as Integer, my variable can hold the numbers from -32768 to 32767.
Anything more than the limit of the data type limit it will cause an error. So if we want to store more than 32767 value we need to assign the different data type which can hold more than 32767.
To overcome this limitation we have a universal data type “Variant”. This article will show you the complete guide of a Variant data type.
4.6 (247 ratings)
How to Declare Variant Data Type?
As I told at the beginning of the article every data type can hold only the respective data to it. Anything other than its own data will cause an error. So, we have a universal data type “Variant” which can hold any kind of data type in VBA. We can store all most all the data to it. One data type which cannot hold is fixed-length string data.
We can declare the variant data type like as usual data type, but while assigning the data type we need to use the word “Variant”.
Sub Variant_Example1() Dim MyNumber As Variant End Sub
This makes the variable to work any kind of data now. We can assign any numbers, strings, dates, and many other things.
Below is the demonstration of the same.
Sub Variant_Example1() Dim MonthName As Variant Dim MyDate As Variant Dim MyNumber As Variant Dim MyName As Variant MonthName = "January" MyDate = "24-04-2019" MyNumber = 4563 MyName = "My Name is Excel VBA" End Sub
In the above I have assigned a date to the variable, number to the variable, a string to the variable. So VBA Variant data type allows us to not to worry about what kind of data we are going to store or assign to it.
As soon as we declare a variable as Variant we don’t have to worry about our data type somewhere in the middle of the project while coding. This makes the variable to work flexibly to our needs. Probably with a single variable we can carry out our operations in the entire project.
VBA Variant Doesn’t Require Explicit Way
The general procedure to declare a VBA variable is to first name the variable and then assign the data type to it. Below is an example of the same.
This is the explicit way of declaring the variable. However, when we declare the Variant data type we need to declare them explicitly rather we can just name the variable and leave out the data type part.
Sub Variant_Example1() Dim MyNumber End Sub
In the above code, I have names the variable as “MyNumber” but after naming the variable I have not assigned any kind of data type to it.
I have left out As [Data Type Name] part because the moment we ignore the data type assignment part invariably variable becomes Variant.
Things to Remember
Even though “Variant” data type is flexible with data we are going to store this is not the popular data type. Looks strange but absolutely TRUE. Unless there is any specific reason to use Variant data type people avoid using this data type. Below are some of the reason for avoiding using a Variant data type.
- It ignores all the data mismatch errors.
- Variant data type limits us from accessing to IntelliSense list.
- VBA always guesses the best possible data type and assign accordingly.
- In case of Integer data type limit Variant data type doesn’t notify us the moment it crosses 32767 limits.
This has been a guide to VBA Variant. Here we discuss how to declare and use the Variant data type which is universal and hold any kind of data type in VBA except fixed-length string data. You can learn more about VBA from the following articles –