VBA Variant

What is Excel VBA Variant Data Type?

In Excel VBA (Visual Basic for Applications), a Variant is a data type that can hold any type of data. It is a versatile data type because it can store numbers, strings, dates, objects, and even arrays. When you declare a variable as a Variant, you don’t have to specify its data type, and you can change the data it holds dynamically during runtime.

Consider the following example:

In this subroutine, we can see that the variable accepts multiple data types. Using a VBA Variant data type, this subroutine accepts two types of variables, String and Date. Both the values are printed in the Immediate tab, as shown below.

How to Declare Variant Data Type?

To learn how to use the VBA Variant Data type, follow the steps below.

Step 1: Open Excel and click “Developer” tab. Then, in the tab, click “Visual Basic.”

In the VBA Editor, click “Insert” and then “Module.” It will open a blank white page where you can start your coding.

Step 2: Name the subroutine to show how variants work.

Step 3: Define a variant data type.

Step 4: Check if it is empty using the VBA IsEmpty function.

Since it is empty, it will print the string in the Immediate tab.

Step 5: Assign an integer value to the Variant variable.

Step 6: Print the type of the variable now.

Code:

Sub VariantExample()
Dim var As Variant
If VBA.IsEmpty(var) Then
Debug.Print “Empty”
End If
var = 200
Debug.Print TypeName(var)
End Sub

Step 9: Click the “run” button which is the Green coloured arrow or press the “F5” button.

Now you know how the VBA Variant Data type works.

Note:

VBA Variant type with numbers requires 16 bytes of memory.

VBA Variant type with characters requires 22 bytes of memory.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VBA Variant Doesn’t Require Explicit Way

Another aspect of the VBA Variant is that it doesn’t require Explicit declaration that it is a variant, unlike other data types.

Simply initializing the variable will convert it into a Variant data type as default.

It is proved by assigning values of multiple data types to the variable and printing them. No error will occur since the variable is defined as a Variant data type by default, and one of the critical characteristics of the Variant data type is that you can dynamically change data types in the subroutine.

Code:

Sub VariantWithoutExplicitDeclaration()
Dim myVar
myVar = 100
Debug.Print myVar
myVar = “Sample String”
Debug.Print myVar
myVar = Now
Debug.Print myVar
End Sub

It will result in the output:

With all of this said, let us go through some interesting examples that use the VBA Variant data type.

Examples

Here, let us view different examples that tackle different practical problems with VBA variants.

Example #1

Let us look at an example where we perform operations such as addition and string concatenation using a Variant variable.

Step 1: Declare a subroutine to show operations using a Variant variable.

Step 2: Give it an integer value and print it.

Step 3: Initialize it again with the VBA Date function, which returns the current date and then prints it.

Step 4: Add the variable now and then print the result.

Step 5: With the same variable, initialize a string value and print it.

Step 6: Now, perform string concatenation and print the result.

Code:

Sub ExampleUsingVariant()
Dim myVar As Variant
myVar = 20
Debug.Print “Value now: ” & myVar
myVar = Date
Debug.Print “Value now: ” & myVar
myVar = myVar + 4
Debug.Print “Value now: ” & myVar
myVar = “Welcome.”
Debug.Print “Value now: ” & myVar
myVar = myVar & ” Join the VBA Tutorial!”
Debug.Print “Value now: ” & myVar
End Sub

Step 7: After running the code, the output is printed in the Immediate tab.

Example 2

With this example, you can use the VBA Variant data type to combine values of different data types without worry of Type Mismatch error. Follow the steps below to see how to do so.

Step 1: Define a function that takes two variant data types as arguments and returns a variant data type as output.

Step 2: In the function, check if both the values are numeric.

If it is true, add both numbers.

Step 3: If that is not the case, use the Else condition to convert both variables into string values and concatenate them.

Step 4: Create a sub-procedure to combine different data types.

Step 5: Define a variant variable.

Step 6: Combine two numbers by calling the function defined in the earlier steps. Then, print it using the Message Box VBA function.

Step 7: Next, combine two string variables and print the result in a Message Box.

Step 8: Call the function again to combine two different data types and print the result.

Code:

Function CombineData(data1 As Variant, data2 As Variant) As Variant
If IsNumeric(data1) And IsNumeric(data2) Then
CombineData = data1 + data2
Else
CombineData = CStr(data1) & ” ” & CStr(data2)
End If
End Function

Sub TestCombineDataFunction()
Dim result As Variant
result = CombineData(5, 3)
MsgBox “Result (numbers): ” & result
result = CombineData(“Hello”, “World”)
MsgBox “Result (strings): ” & result
result = CombineData(“The answer is”, 50)
MsgBox “Result (mixed data types): ” & result
End Sub

Step 9: Run the subroutine to view the results. It will come in multiple message boxes.

Consider you have a dataset of different data types. You want to combine all values and find the total value of different data types, concatenate all the strings, and add all numerical values. The table is shown below.

Step 1: Create a subroutine to combine all the values.

Step 2: Define a Worksheet variable, a range variable, a double variable to hold the sum of all the numeric values, and a string variable to hold all the concatenated strings.

Step 3: Assign the worksheet in which the table is present and assign 0 to the double variable and an empty string to the string variable.

Step 4: Initialize a FOR loop to run through the table.

Here, we count the number of non-empty cells in Column “A” ending with the last non-empty cell using the VBA xlUp function, which simulates the “Up” button on the keyboard.

Step 5: Define a variant variable and assign it the cell values.

Step 6: Check if the cell value is numeric, using the If-Else condition.

Step 7: If it is not numeric, initialize an Else…If condition to check if it is a string. If it is a string, concatenate all the strings with a space in between.

Step 8: After the FOR loop runs through the whole table, print the total value of the numeric values and the concatenated string for the string value using the Message Box function.

Code:

Sub ProcessExcelData()
Dim ws As Worksheet
Dim cell As Range
Dim totalNumeric As Double
Dim concatenatedStrings As String
Set ws = ThisWorkbook.Sheets(“Sheet1”)
totalNumeric = 0
concatenatedStrings = “”
For Each cell In ws.Range(“A1:A” & ws.Cells(Rows.Count, 1).End(xlUp).Row)
Dim cellValue As Variant
cellValue = cell.Value
If IsNumeric(cellValue) Then
totalNumeric = totalNumeric + cellValue
ElseIf VarType(cellValue) = vbString Then
concatenatedStrings = concatenatedStrings & cellValue & ” “
End If
Next cell
MsgBox “Total of numeric values: ” & totalNumeric
MsgBox “Concatenated strings: ” & concatenatedStrings
End Sub

Step 9: Run the subroutine. It will print two message boxes for the numeric and string values, respectively.

Important Things To Note

• Variants can be slower than explicitly typed variables because VBA needs to perform type checking and conversions at runtime.
• Variants do not benefit from compile-time type checking. As a result, you should be extra cautious when working with Variants to avoid runtime errors, especially when performing operations on them.
• Since Variants can hold any data type, you should always be aware of the current data type stored in a Variant variable.
• Variants consume more memory compared to explicitly typed variables because they need to store information about the data type.
• Always initialize Variant variables before using them to ensure that they are not empty. Uninitialized Variant variables can lead to unexpected behavior.
• When working with Variants, it’s a good practice to implement proper error handling in VBA to deal with unexpected data types or errors that might occur during runtime.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Variant, with its syntax and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is VBA Variant?. Here we learn how to declare & use variant data type in VBA, with examples, and points to remember. You can learn more from the following articles –

• VBA Data Type
• VBA FreeFile
• VBA ThisWorkbook