Variant Data Type in VBA Excel

Previously. we learned about various numerical and nonnumerical data types in the VBA programming language. Today, we would learn about another particular data type which is known as a variant. Variants are easy to use and provide additional functionality to our code.

So let us start learning about variants in the VBA Programming language.

What are Variants in VBA?

A Variant is a special type of variable having any data type including Integers, strings, decimals, or even objects. When we do not specify the data type of a variable then it becomes a variant and its data type would be automatically decided on the basis of the value assigned to the variant.

Generally, we avoid using variants in VBA code because they require extra main memory.

You can download the practice workbook to follow along this article.

Declaring a Variant in VBA

There are two ways to declare a variant type variable in VBA Programming language, Explicit Declaration, and Implicit Declaration.

The syntax for Explicit Declaration of Variant

In this method, we explicitly tell the data type of variable to be a variant at the time of its declaration.

Dim <variant_name> As Variant

For example, we have these examples of explicit variant declarations.

Dim var1 As Variant
Dim var2 As Variant

The syntax for Implicit Declaration of a Variant

We can implicitly declare a variant if we do not specify the data type of a variable when declaring it, it is automatically declared as a variant. We remove the As <Data_Type> part of the normal variable declaration then it becomes an implicit variant declaration.

Dim <Variant_Name>

For instance, we are having examples of some valid implicit variant declarations in VBA.

Sub Implicit_Declaration()
Dim Name
Dim Roll
Dim group
Dim percentage

Name="Chetna Dua"          'name variant becomes text string
Roll=45                    'roll becomes an integer 
group="BCA Final year"     'group is assigned to a text string
percentage=45.53           'data type of percentage changes to double

Debug.Print Name,Roll,group,percentage

End Sub

Example to Use Variant in VBA

Within the program, if we reassign the value then the variant can change its data type based on the type of the newly assigned value.

For instance, look at this code.

Sub example()
Dim var                            'no data type specified by the user
var = "ExcelUnlocked"              'string literal assigned 
Debug.Print var;
var = 56                           'integral literal assigned
Debug.Print var
End Sub

• We have declared a variable named var using the Dim keyword but we didn’t specify its data type.
• We assigned the string literal “ExcelUnlocked” to var. Now var has a String data type.
• When we reassign the var with a string literal 56, var changes its data type to Integer and stores 56 in it.

When we run the above Sub Procedure, we get the value of the variable on the Immediate Window in VBE Interface.

Press Ctrl and G keys together to open the Immediate Window in Visual Basic Editor. This window prints the messages with the help of Debug.Print method

Module Level Variant

The Variant declared in the above examples are local to the Sub Procedure in which they are declared. We cannot access the value of the variant out of the Sub Procedure block in which we declare it.

Module Level variant can be accessed or updated in any sub-procedure of the module. To declare a variant at a module level, use the declaration statement out of all the Sub Procedures at the top of the VBA Module code.

Global Level Variant

A Global level variant can be accessed or updated from all the Sub Procedures of all the modules of our VBA Project. We use the Public keyword to declare a Globar level variant at the top of the VBA Module code.

Public <Variant_name> As Variant

This brings us to an end.

Thank you for reading.