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.
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.