Excel VBA Variant: What It Is and When to Use It

In Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including numbers, text, dates, errors, arrays, and object references. Use it when the value’s type is unknown or when working with worksheet ranges, but prefer a more specific type when you know what a variable should contain.

What Variant Means in VBA

Variant is a built-in VBA data type designed to accommodate values whose type may change or may not be known in advance. A Variant variable can hold a number at one point and a string later, for example. VBA tracks the kind of value currently stored in the variable, so code can inspect it or work with it accordingly.

You can declare one explicitly with Dim result As Variant. You can also write Dim result without an As clause; in VBA, that variable is a Variant too. The second form can be easy to overlook, so explicitly writing As Variant often makes the code’s intent clearer. If a procedure uses Option Explicit, VBA requires variables to be declared, but it does not require you to choose a specific type: a declared Variant remains valid.

A new Variant starts in the special Empty state. That is different from a Variant containing the number zero, an empty string, or the special value Null. These distinctions matter when checking worksheet data or deciding whether a calculation has enough information to proceed.

What a Variant Can Contain

A Variant can represent common VBA values such as integers, larger numbers, strings, dates, Boolean values, and error values. It can also hold an array or an object reference. For an object, use Set when assigning the reference, just as you would with an object variable declared using a specific class type.

Variants can also represent special states that are useful when dealing with uncertain or imported data:

Empty: The Variant has not been assigned a value. IsEmpty(value) checks for this state.
Null: A value representing the absence of valid data, often encountered in database-related work. IsNull(value) checks for it.
Error: An error value, such as an Excel cell error passed into VBA. Use IsError(value) before treating it as ordinary text or a number.
Zero-length string: Text containing no characters, often written as "". It is not the same as Empty or Null.

These states are not interchangeable. For instance, a worksheet cell that is genuinely blank may be read as Empty, while a formula that returns an empty string may produce a zero-length string. A cell containing #N/A is an error value, not the text “#N/A”. Tests that assume every blank-looking cell has the same underlying value can therefore give misleading results.

Why Excel Uses Variants for Cell Values

Excel cells do not all contain the same kind of data. A column can include names, numbers, dates, blanks, and formulas that return errors. VBA’s Range properties therefore use Variant values to accommodate different cell contents. This is one reason Variants are common at the boundary between a worksheet and a VBA procedure, even when the rest of the program uses more precise types.

Reading a single cell with Range.Value or Range.Value2 gives you a Variant containing that cell’s value. Reading a multi-cell range into one variable gives you a Variant containing a two-dimensional array. For example, assigning Range("A1:C10").Value2 to a Variant creates an array with row and column dimensions. The array elements can still hold different kinds of values.

There is an important exception: a one-cell range returns a scalar value rather than a two-dimensional array. Code that expects to loop over array bounds should account for this difference if the selected range could ever contain just one cell. For a fixed multi-cell range, such as A1:C10, the array form is predictable.

How to Declare and Inspect a Variant

Declare a Variant when flexible storage is useful, then inspect values before performing operations that require a particular kind of data. The TypeName function returns a readable type name, and VarType returns a numeric type code. Checks such as IsError, IsEmpty, and IsNumeric are often more helpful than guessing what a value contains.

For example, before converting user input into a number, check whether it is numeric. That check does not guarantee that every later operation is appropriate: the value may still be outside the range expected by the program, or a conversion may not match the required type. Validation should reflect what the procedure is meant to accept.

When examining a Variant that might contain an error, test it with IsError before trying to print, compare, or calculate with it. Error values do not behave like ordinary strings or numbers, and an operation on one can raise a run-time error. Likewise, use IsNull for Null rather than relying on an ordinary equality comparison.

Reading a Range into a Variant Array

This example reads a fixed three-column range into a Variant array and prints a basic description of each value to the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows. Replace the worksheet name if your workbook uses a different one.

Option Explicit

Sub InspectRangeValues()
    Dim ws As Worksheet
    Dim values As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    values = ws.Range("A1:C10").Value2

    ' Check special values before identifying ordinary values.
    For rowIndex = LBound(values, 1) To UBound(values, 1)
        For columnIndex = LBound(values, 2) To UBound(values, 2)
            If IsError(values(rowIndex, columnIndex)) Then
                Debug.Print rowIndex, columnIndex, "Excel error"
            ElseIf IsEmpty(values(rowIndex, columnIndex)) Then
                Debug.Print rowIndex, columnIndex, "Empty"
            Else
                Debug.Print rowIndex, columnIndex, _
                    TypeName(values(rowIndex, columnIndex))
            End If
        Next columnIndex
    Next rowIndex
End Sub

The two indexes represent the array’s row and column positions, not necessarily the worksheet’s row and column numbers. The code checks for an Excel error before calling TypeName, and checks for Empty separately. A formula returning an empty string will not meet the Empty test; it is a string value instead. This sample only reads cells and writes descriptions to the Immediate window; it does not change the worksheet.

Before running any macro on important work, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when a macro changes cells in a way that looks like an ordinary edit.

Variant Compared with Specific Data Types

Specific types make a variable’s purpose and limits clearer. For example, Long is commonly used for whole-number counters and worksheet row numbers, while String expresses that a value is text. Types such as Double are suitable for many decimal calculations. Choosing the right type can make errors easier to identify and reduce unnecessary flexibility.

A Variant is not automatically wrong or unsafe, and it is not a substitute for deciding what the data means. The useful question is whether the value genuinely needs to be flexible. A worksheet range with mixed content is a natural Variant use. A loop counter that will always be an integer is usually clearer as a Long.

Choose a specific type when the permitted kind of value is known and stable.
Choose Variant when data may legitimately have different types, or when an Excel or VBA interface returns a Variant.
Validate a Variant before conversion or calculation if it comes from a user, a file, or a mixed-content range.
Consider converting values at a clear boundary so the rest of the procedure can use predictable types.

Variants can require more storage than a narrowly typed variable, and flexible type handling can add overhead. The practical impact depends on the code and workload. In many ordinary macros, clarity and correctness are more important than trying to optimize a small number of Variant assignments. For large data-processing tasks, avoid unnecessary cell-by-cell interaction and measure performance before changing types solely for speed.

Value, Value2, and Date or Currency Data

Excel’s Range.Value and Range.Value2 both return values through Variants, but they do not treat every value identically. The key distinction is that Value can return VBA Date or Currency subtypes for relevant cell values, while Value2 does not use those two subtypes. Date-like or currency-like values read with Value2 are represented as numbers instead.

Excel stores dates as serial numbers, with the displayed date determined by the cell’s number format. If you read such a cell using Value2, the numeric serial is what VBA receives; formatting is not a separate date value in the array. Choose the property with your intended handling in mind, especially when transferring values between Excel and another system. Do not assume that a number’s appearance in the worksheet tells you its VBA subtype.

Common Variant Problems and Misconceptions

One frequent source of confusion is assuming that Dim amount means an untyped variable. In VBA, it means a Variant. Another is assuming that Option Explicit forces every variable to have a specific type. It catches undeclared names, but it does not change the rules for a variable declared without an As clause.

Another common mistake is treating a blank cell, a formula result of "", and a cell error as equivalent. They need different checks. Use IsEmpty for an unassigned or empty Variant, compare a string value with a zero-length string when that is the intended test, and use IsError for error values. If the data comes from a range, test representative cells so the code accounts for formulas as well as manually entered values.

Finally, a Variant does not make every conversion safe. An assignment or calculation can still fail when the stored value is incompatible with the operation. A Variant can also conceal inconsistent input for longer than a specifically typed variable would. Treat flexible storage as a way to handle varied data, not as a replacement for validation or error handling.

Frequently Asked Questions

What does Variant mean in Excel VBA?
A Variant is a VBA data type that can hold different kinds of values, including text, numbers, errors, arrays, and object references. VBA also uses it for values returned by many Excel range operations.

Is a Variant the default data type in VBA?
Yes. If you declare a variable with Dim name and omit the As clause, VBA makes it a Variant. Writing Dim name As Variant declares the same data type explicitly.

Should I use Variant or Long in VBA?
Use Long for values that are intended to be whole-number counters or indexes. Use Variant when the value may contain different kinds of data or when an Excel interface returns a Variant that you need to inspect.

How do I check whether a Variant is empty or an error?
Use IsEmpty(value) to test for Empty and IsError(value) to test for an error value. Use IsNull(value) for Null; these checks detect different states.

Does Range.Value return a Variant array?
A multi-cell range assigned to one variable returns a Variant containing a two-dimensional array. A one-cell range returns a scalar Variant, so code that loops through array bounds must account for a possible single-cell range.

Is Value2 better than Value?
Neither is universally better. Value2 avoids returning VBA Date and Currency subtypes, while Value can return those subtypes for applicable cells. Choose according to how your code should handle dates and currency-like values.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.