VBA Variant

Variant Data Type in VBA is a universal data type that can hold any data type, 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 depends on what kind of data we need to assign to the declared variables.

For example, look at the below code.

In the above code, we declare the variable as "IntegerNumber." We assigned the data type as "Integer."

Before assigning the data type to the variable, we should be aware of the limitations of the variable. For example, since we have assigned the data type Integer, my variable can hold the numbers from -32768 to 32767.

Anything more than the data type limit will cause an error. So, if we want to store more than 32767 values, we need to assign the different data types 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 on a Variant data type.

We can declare the variant data type as the usual data type, but while assigning the data type, we need to use the word "Variant."

Code:

Sub Variant_Example1()

    Dim MyNumber As Variant

End Sub

It makes the variable work any data now. We can assign any numbers, strings, dates, and many other things.

Below is the demonstration of the same.

Code:

 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

We have assigned a date, number, and string to the above variable. So, the Variant data type allows us not to worry about what kind of data we will store or assign.

As soon as we declare a variable as a "Variant." While coding, we don't have to worry about our data type somewhere in the middle of the project. It makes the variable work flexibly to our needs. We can probably carry out our operations in the project with a single variable.

The general procedure to declare a VBA variable is first to name it and then assign the data type. Below is an example of the same.

It is the explicit way of declaring the variable. However, when we declare the Variant data type, we do not need to declare them explicitly. Rather, we can name the variable and leave out the data type part.

Code:

Sub Variant_Example1()

    Dim MyNumber

End Sub

In the above code, we have named the variable "MyNumber," but after naming it, we have not assigned any data type.

I have left out As part because the moment we ignore the data type assignment part, the variable becomes variant.

Even though the "Variant" data type is flexible with the data, we will store it, but this is not the popular data type. Looks strange but is TRUE. People avoid using this data type unless there is any specific reason to use it. Below are some of the reasons for avoiding the use of variants.

• It ignores all the data mismatch errors.
• The variant data type limits us from accessing the IntelliSense list.
• VBA always guesses the best possible data type and assigns it accordingly.
• In the case of the Integer data type limit, the variant data type does not notify us when it crosses 32767 limits.

This article is a guide to VBA Variant. Here, we discuss how to declare and use the Variant data type, which is universal and holds any data type in VBA except fixed-length string data. You can learn more about VBA from the following articles: -

• VBA DoEvents
• Refresh Pivot Table using VBA Code
• Class Modules in VBA
• VBA Outlook