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:

VBA Variant Intro

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.

VBA Variant Intro - Output
Key Takeaways
  • Variants can hold any data type, making them flexible for handling mixed or unknown data.
  • Data types in Variants can be changed dynamically during runtime.
  • Variants require runtime type checking, potentially affecting performance.
  • Use explicit type conversion functions (e.g., CStr, CInt) when necessary, when using the Variant data type.
  • Consider refactoring specific data types if Variants are used extensively, and code clarity is compromised.

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

Developer - Visual Basic

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

Insert - Module

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

VBA Variant - Step 2

Step 3: Define a variant data type.

VBA Variant - Step 3

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

VBA Variant - Step 4

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

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

VBA Variant - Step 5

Step 6: Print the type of the variable now.

VBA Variant - Step 6


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.

VBA Variant - Step 9
VBA Variant - Step 9 - Output

Now you know how the VBA Variant Data type works.


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.

VBA Variant - Explicit - Var

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

VBA Variant - Explicit - variant data type

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.


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:

VBA Variant - Explicit - Output

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


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.

VBA Variant - Example 1 - Step 1

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

VBA Variant - Example 1 - Step 2

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

VBA Variant - Example 1 - Step 3

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

Example 1 - Step 4

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

VBA Variant - Example 1 - Step 5

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

VBA Variant - Example 1 - Step 6


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.

VBA Variant - Example 1 - Step 7

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.

Example 2 - Step 1

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

Example 2 - Step 2

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.

Example 2 - Step 3

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

Example 2 - Step 4

Step 5: Define a variant variable.

Example 2 - Step 5

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

Example 2 - Step 6

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

Example 2 - Step 7

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

Example 2 - Step 8


Function CombineData(data1 As Variant, data2 As Variant) As Variant
If IsNumeric(data1) And IsNumeric(data2) Then
CombineData = data1 + data2
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.

VBA Variant - Example 2 - Step 9 - number
VBA Variant - Example 2 - Step 9 - string
VBA Variant - Example 2 - Step 9 - data type

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.

VBA Variant Data type - different - Example

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

VBA Variant Data type - different - Step 1

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.

VBA Variant Data type - different - Step 2

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.

VBA Variant Data type - different - Step 3

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

different - Step 4

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.

VBA Variant Data type - different - Step 5

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

VBA Variant Data type - different - Step 6

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.

VBA Variant Data type - different - Step 7

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.

VBA Variant Data type - different - Step 8


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.

VBA Variant Data type - different - Step 9 - numeric
VBA Variant Data type - different - Step 9 - concatenate

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)

1. Can I use VBA Variants in conditional statements?

Yes, you can use VBA Variants in conditional statements. See the example below.

Sub ConditionalStatement()
Dim x As Variant
If Not (VBA.IsNull(x)) Then
Debug.Print “Valid value!”
End If
End Sub

This will show that the Variant datatype isn’t null and print the output in the Immediate tab.

VBA Variant Data type - FAQ 1

2. Can I store numbers in a VBA Variant?

Yes, you can store numbers in a VBA Variant. However, it should be within the limits of the VBA Variant length of any specific data type of the value it is holding.

3. What are the drawbacks of using VBA Variants?

Performance: Variants can be slower than explicitly typed variables due to runtime type checking.
Type Safety: Lacks compile-time type checking, which can lead to runtime errors.
Memory Usage: Consumes more memory due to storing data type information.
Data Type Confusion: Requires careful tracking of current data type.
Initialization: This should always be initialized to avoid unexpected behavior.
Implicit Conversions: Implicit-type conversions can lead to unexpected results.

4. Are there alternatives to using VBA Variants?

Yes, there are many alternatives to using the VBA Variant data type. Some of them are:
• Explicitly declare data types such as String, Integer, Double, etc.
• Arrays
• Collections
• User-Defined-Types (UDT
• Enums

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *