VBA Data Types in Excel — String, Long, Double, Variant (and the Right Box for Each Value)

TL;DR — A data type is the shape and size of a variable's box. Pick one too small and the value overflows; reach for the lazy all-purpose box (Variant) and you pay in speed and hidden bugs. Two rules cover 90% of real code: use Long for every whole number (Integer overflows at 32,767, and a sheet has 1,048,576 rows), and use Double for math, Currency or rounding for money (Double can't hold 0.1 + 0.2 exactly). Everything else — String, Boolean, Date — you pick to match the value, and you keep Variant for the few cases that genuinely need it.

Dim rowCount As Long      ' whole numbers — ALWAYS Long, never Integer
Dim price    As Double    ' measurements & math
Dim total    As Currency  ' money — exact to 4 decimals
Dim name     As String    ' text
Dim isPaid   As Boolean   ' True / False
Dim dueDate  As Date      ' a date/time

Choosing a type feels like a formality, but it's really a decision about which failure modes you're exposed to. The wrong type doesn't always error — sometimes it just quietly gives the wrong number. This guide is organised around the traps, because that's what actually decides which box to use.

What you'll learn

• Why Integer is a trap and Long should be your default for whole numbers
• What Variant really is, and the narrow cases where it's the right choice
• Why Double corrupts money and what to use instead
• How to read the two type errors: Overflow (6) and Type mismatch (13)
• A one-line rule for picking a type when you're unsure

The mental model: the type is the shape of the box

If a variable is a box, the type is its shape and capacity. Long is a box built for whole numbers up to about two billion. String stretches to fit text. Boolean is a box with room for exactly two states. Double holds fractional numbers but in a binary approximation. Variant is a shape-shifting box that moulds itself to whatever you drop in.

Every type is a trade. A tight, specific type catches mistakes early and runs fast; a loose type (Variant) accepts anything and defers the mistake to later, somewhere harder to find. So "which type?" is the same question as "what am I willing to have go wrong?"

The rule that prevents Overflow: use Long, never Integer

VBA has two whole-number types, and one of them is a relic. Integer is 16-bit: its ceiling is 32,767. Long is 32-bit: it goes past two billion. The moment an Integer is asked to hold 32,768, VBA throws "Overflow" (error 6) and stops.

The classic victim is a row counter:

' FRAGILE — overflows the moment the sheet has > 32,767 rows
Dim r As Integer
For r = 1 To lastRow          ' error 6 at row 32,768
    ' ...
Next r

' CORRECT — Long handles all 1,048,576 rows
Dim r As Long
For r = 1 To lastRow
    ' ...
Next r

A modern worksheet has 1,048,576 rows — thirty-two times past Integer's limit. Any variable that counts rows, holds a .Row number, or accumulates a large sum must be Long. And here's the kicker: on today's hardware there is no speed benefit to Integer — VBA converts it to a 32-bit value internally anyway. So the rule has no downside: use Long for every whole number and forget Integer exists.

The rule about Variant: it's a deliberate choice, never a default

Any variable you declare without a type — Dim x — is a Variant, and so is every undeclared variable. A Variant holds anything: a number now, text later, an array, an object. That sounds convenient, and it's why beginners lean on it. It's also why their bugs hide.

The costs are real. A Variant uses more memory, runs slower, and — most importantly — doesn't catch type mistakes. A number that arrives as text stays text inside a Variant and silently fails a later calculation, where a Long would have errored immediately at the point of the mistake.

But Variant has one genuinely great use, and it's worth knowing because it's fast: reading a whole Range into memory in a single shot.

' The ONE great Variant use: pull a range into a 2-D array in one hit
Dim data As Variant
data = ThisWorkbook.Worksheets("Data").Range("A1:C1000").Value

Dim i As Long
For i = 1 To UBound(data, 1)
    data(i, 2) = data(i, 2) * 1.1     ' work in memory — no per-cell round trips
Next i

Range("A1:C1000").Value = data        ' write it back in one hit

That pattern is dramatically faster than touching cells one at a time, and it requires a Variant because Excel hands you a Variant array. Use Variant here, and for values that are genuinely mixed. Everywhere else, declare the real type.

The rule about money: Double lies in the last cent

Double is binary floating point, so it can't represent every decimal exactly — the famous result is that 0.1 + 0.2 is not exactly 0.3. For a chart or a physics calculation that's invisible. For money, those tiny errors accumulate and your totals drift by a cent, which is exactly the kind of thing an auditor notices.

Dim a As Double
a = 0.1 + 0.2
Debug.Print (a = 0.3)         ' -> False   (a is 0.30000000000000004)

Dim m As Currency
m = 0.1 + 0.2
Debug.Print (m = 0.3)         ' -> True    (Currency is exact to 4 decimals)

Use Currency for money — it's a fixed-point type, exact to four decimal places, and built for exactly this. If you must use Double (say you need more than four decimals), round deliberately with Round() or Format() before you compare or store. Never test two Double values for exact equality; compare within a tolerance instead.

A quick tour of the everyday types

• String — text. Variable length by default; you rarely need fixed-length.
• Boolean — True / False. Perfect for flags (isPaid, found).
• Date — a date and/or time. Under the hood it's a number (the same serial number Excel uses), which is why you can do date arithmetic.
• Long — your default whole number, as above.
• Double — your default fractional number for math and measurements.
• Currency — money.
• Variant — deliberate use only.

One more trap that belongs here: Dim i, j As Long makes only j a Long — i is a Variant. Each variable needs its own As Type. That declaration mechanics is covered in full in VBA Dim.

How ExcelMaster helps

Type errors — Overflow on a big sheet, a Double that drifts a cent, a Variant masking a text-versus-number bug — are the slow-burn kind: the macro runs, and the wrong answer shows up downstream. Getting types right is real engineering discipline, and it's discipline you have to repeat on every macro.

ExcelMaster takes the task in plain English — "total column D as currency, count the rows, flag anything over budget" — and handles the type choices for you: Long for the counts, exact arithmetic for the money, no silent overflow on a million-row sheet. You keep writing typed macros where you need fine control; for the everyday job, you describe the result and skip the class of bugs that types are there to prevent.

Frequently asked questions

What's the difference between Integer and Long in VBA?

Integer is a 16-bit whole number capped at 32,767; Long is 32-bit and goes beyond two billion. Because a worksheet has 1,048,576 rows and Integer offers no speed advantage on modern Excel, you should use Long for every whole number and avoid Integer entirely. Exceeding Integer's limit raises "Overflow" (error 6).

Should I use Variant in VBA?

Only deliberately. Variant holds any value, but it uses more memory, runs slower, and hides type bugs (a number stored as text won't error until later). Its one excellent use is reading a whole Range into a 2-D array (data = Range("A1:C1000").Value), which is very fast. Everywhere else, declare the specific type.

What data type should I use for money in VBA?

Use Currency. It's a fixed-point type exact to four decimal places, so it avoids the rounding drift of Double (which can't store 0.1 + 0.2 exactly). If you use Double for money, round explicitly with Round() before comparing or storing, and never test two Double values for exact equality.

What is the default data type in VBA?

Variant. Any variable declared without As Type — or used without being declared at all — is a Variant. That's convenient but bug-prone, which is why Option Explicit plus explicit types is the recommended discipline.

Why do I get "Type mismatch" (error 13)?

You assigned a value that doesn't fit the variable's type — for example putting the text "abc" into a Long, or a cell containing an error value into a Double. Check what's actually in the cell or variable (text that looks numeric is a common culprit) and convert it explicitly with CLng, CDbl, or CStr before assigning.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-07-31.

Related guides: VBA Dim · VBA Const · VBA CStr · VBA Array · VBA For Loop