VBA · Variables
Variant
A catch-all type that holds anything, including arrays and ranges of values; slower and easier to misuse.
When to use it
A Variant holds any type, including whole ranges of values as a 2D array. It is the right type for reading a range into memory and the wrong type for everything else.
The code
- Code
Dim v As Variant v = Range("A1:C10").Value
Worked examples
Read a range
Dim v As Variant v = Range("A1:C10").Value → A 10 by 3 array of the cell values
The fast way to read a block.
Silent conversion
Dim x As Variant x = "5" x = x + 1 → x becomes 6, a number
Variants convert as they go, which is convenient and dangerous.
Empty test
If IsEmpty(v) Then ... → Tests whether a Variant has been assigned
Empty is different from 0 or "".
Worth knowing
- Use Variant for range arrays and for function arguments that may be a range or a value.
- Use typed variables everywhere else.
- VarType and TypeName tell you what a Variant currently holds.
Where it goes wrong
- Slower than typed variables in tight loops.
- "5" + 1 is 6 but "5" & 1 is "51"; the operator decides.
Related
Learn the moves here — or let Wauvel run them on your numbers.
$99/mo, everything included. Free for 14 days, no card.
One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.