VBA Variant

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

arrow

Excel VBA Variant Data Type

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.

Table of contents

For example, look at the below code.

Integer Type

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.

vba-variant

How to Declare 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
vba variant declaration

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
vba variant example

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.

VBA Variant Doesn’t Require Explicit Way

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.

Explicitly Declaring Variable

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.

Things to Remember

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.