Excel VBA Variant Data Type

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 VBAData Type Assignment In VBAData type is the core character of any variable, it represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.read more 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Variant (wallstreetmojo.com)

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

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.

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.

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.

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.

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 [Data Type Name] 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.

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 DoEventsRefresh Pivot Table using VBA CodeClass Modules in VBAVBA Outlook