🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Value vs Value2 vs Text in Excel — Which One Reads the Cell Right

|

VBA Value vs Value2 vs Text in Excel — Which One Reads the Cell Right

TL;DR — A cell has three read faces. .Value2 is the raw stored number (a date comes back as its serial 46261, no coercion — fastest). .Value is that number coerced to a VBA type — a Date for a date cell, a Currency for a currency cell (which can round very large numbers). .Text is the displayed string — "$1,235", or "###" if the column is too narrow — and it is read-only. For math, read .Value2. Reading .Text to get a number is the classic silent bug.

' cell A1 holds 1234.5, formatted as currency "$#,##0"
Debug.Print Range("A1").Value2     ' 1234.5      - the raw number
Debug.Print Range("A1").Value      ' 1234.5      - here a Currency, coerced
Debug.Print Range("A1").Text       ' "$1,235"    - the display string (rounded, read-only)

Everyone learns .Value first and assumes it is the value in the cell. It is one of three, and the idea this guide is built on is that a cell has several faces and each answers a different question: .Value2 asks "what number is stored," .Value asks "what number, typed as VBA sees it," and .Text asks "what is painted on screen." Reach for the wrong face and nothing errors — you just quietly get the wrong answer. Hold the three apart and a whole family of "my numbers are off" bugs disappears.

What you'll learn

  • The mental model — three read faces, in order of how much Excel massages the answer
  • The rule that matters most — .Text is a display string, not the value, and is read-only
  • .Value versus .Value2 — where Currency and Date coercion changes the number
  • Why .Value2 is the fast, lossless default for bulk reads
  • Dates: a Date from .Value, a serial Double from .Value2
  • Which face to reach for, every time

The mental model: three faces, from raw to displayed

Order the three by how much Excel processes the stored number before handing it to you:

  • .Value2 — the number exactly as stored. No type conversion at all: a whole number is a Double, text is a String, and a date is its underlying serial number (46261, not a date). It is the closest thing to "what is really in the cell."
  • .Value — the stored number coerced to a matching VBA type. If the cell is formatted as a date, .Value returns a Date; if it is formatted as currency, .Value returns a Currency. The number is the same in most cases, but its VBA type — and sometimes its precision — is not.
  • .Text — the displayed string: the value run through the cell's number format, exactly as it appears on screen. "$1,235", "12.3%", "1-Jan-2026", or "###" if the column is too narrow. Always a String, always read-only.

The three are not interchangeable; they answer three different questions. Everything below is a consequence of which question you actually meant to ask.

The rule that matters most: .Text is the display, not the value

This is the one that produces bugs you cannot reproduce, because they depend on formatting and column width. .Text gives you what the screen shows, run through the cell's number format:

' A1 holds 1234.5, formatted "$#,##0"
Dim n As Double
n = Range("A1").Text        ' n gets "$1,235" coerced -> either a type-mismatch error or 1235

Two things went wrong. The dollar sign and comma make it a string, not a number, so arithmetic breaks or throws. And the format rounded 1234.5 to 1,235, so even if you strip the symbols you have lost the .5. Worse, if the column is too narrow to show the number, .Text returns literally "###" — so the exact same code reads a usable value on your screen and garbage on a user's narrower one. And because .Text is read-only, Range("A1").Text = "5" raises an error — you cannot write through it. The rule is blunt: never read .Text to get a number. Use .Text only when you truly want the formatted string a human sees — for a report label or a log line — and never for computation.

.Value versus .Value2: where coercion changes the number

.Value and .Value2 return the same number for ordinary data. They diverge on two formats: currency and dates.

For a currency-formatted cell, .Value returns the Currency data type, which holds 15 digits with exactly 4 decimal places. That is perfect for money and avoids floating-point wobble — but a number with more than four decimals, or one beyond the Currency range, is rounded or overflows:

' A1 holds 1234567.891234, formatted as currency
Debug.Print Range("A1").Value      ' 1234567.8912  - Currency, tail past 4 dp is gone
Debug.Print Range("A1").Value2     ' 1234567.891234 - full Double, nothing lost

The lesson is not that one is right and one is wrong — it is that they answer different needs. If you are handling money and want exact 4-decimal arithmetic, .Value (Currency) is the safer face. If you are reading a raw measurement or any number where you must not lose precision, .Value2 (Double) is. The silent bug is reaching for .Value on a high-precision number and quietly dropping the tail.

Why .Value2 is the fast, lossless default

Because .Value2 does no coercion, it is both the fastest read and the one that never surprises you with a type. For bulk reads into an array — the performance pattern from VBA Cell Value — .Value2 is the default professionals reach for:

Dim arr As Variant
arr = Range("A1:Z100000").Value2    ' raw Doubles and Strings - fastest, no Date/Currency coercion

You get plain Doubles and Strings with no hidden Date or Currency typing to reason about, and the read is marginally quicker across large ranges. The trade-off is that dates arrive as serial numbers, so if your data is dates and you want them typed as Date, use .Value. Otherwise, default to .Value2 for reading, and only step up to .Value when you specifically need Date or Currency typing.

Dates: a Date from .Value, a serial from .Value2

Dates are where the .Value / .Value2 split bites hardest:

' A1 holds the date 2026-08-31
Debug.Print Range("A1").Value      ' 2026-08-31   - a real Date value
Debug.Print Range("A1").Value2     ' 46265        - the serial number underneath

If you read a date column with .Value2 and then do "date math" on it, you are really doing arithmetic on serial numbers — which is fine if you know it, and baffling if you do not (+1 is one day, but the value prints as 46266, not a date). If you want VBA to treat the cell as a date, read .Value; if you are round-tripping raw numbers and will format later, .Value2 is cleaner. This is the same underlying Double-with-a-mask idea behind VBA Now, Date & Time and VBA DateAdd.

The honest verdict: one cell, four faces, pick on purpose

A cell is not a single value. You read and write it through four faces, and choosing deliberately is the whole skill:

  • .Formula is the recipe — the live formula, written in the neutral US-English dialect (VBA Formula).
  • .Value is the typed answer — the number coerced to a Date or Currency when the cell is formatted that way; use it when you want that typing, knowing Currency rounds past four decimals.
  • .Value2 is the raw answer — a plain Double or String, dates as serials; the fast, lossless default for reading and for bulk arrays.
  • .Text is what is on screen — a formatted, read-only string; for display only, never for math, and never trustworthy when a column might be too narrow.

Read the wrong one and the bug is silent: .Value quietly rounds money past four decimals, .Text hands you "###" or "$1,235" instead of a number, and .Formula rejects a localized function name. Know which face you meant, and the cell stops lying to you.

How ExcelMaster helps

The value-face bugs are the ones that never throw: money rounded by a Currency coercion, a date read as a serial and mangled by "date math," a .Text read that returned "###" on someone else's narrower screen. They pass every test on your machine and fail quietly on real data.

ExcelMaster reads each cell through the right face. Ask it to "sum the raw amounts" and it reads .Value2 for lossless Doubles; ask for "the invoice totals as money" and it uses .Value for Currency typing; ask for "the label exactly as shown" and it takes .Text — and never uses .Text for arithmetic. It knows dates come back as serials from .Value2 and as Date from .Value, and it picks accordingly. You describe what the number is for; it reads the face that keeps it correct.

Frequently asked questions

What is the difference between Value and Value2 in VBA?

.Value2 returns the raw stored number with no type conversion — a date comes back as its serial Double, a currency cell as a plain Double. .Value returns the same number coerced to a matching VBA type: a Date for a date-formatted cell, a Currency for a currency-formatted cell. They agree for ordinary numbers, but .Value can round a high-precision currency value to four decimals, while .Value2 keeps the full Double.

Should I use Value or Value2 in Excel VBA?

Default to .Value2 for reading: it is the fastest and never surprises you with Date or Currency typing or precision loss, which makes it ideal for bulk reads into an array. Use .Value when you specifically want the cell typed as a Date or want Currency arithmetic for money. Avoid .Text for any value you will compute with — it returns a formatted, read-only string.

Why should I not use .Text to read a cell value?

.Text returns the string displayed on screen, run through the cell's number format — so a currency cell reads as "$1,235" (a string, and rounded), a percentage as "12%", and a too-narrow column literally as "###". That makes math fail or produce wrong results, and the "###" case means the same code works on your screen and breaks on a narrower one. .Text is also read-only, so you cannot assign to it. Read .Value2 or .Value for numbers, and use .Text only for display.

Why does my VBA date come back as a number?

You read it with .Value2, which returns the raw serial Double under the date (for example 46265) rather than a Date. Excel stores dates as serial numbers, and .Value2 gives you that number with no coercion. To get a real Date value, read .Value instead; use .Value2 only when you want the serial number, for example for fast bulk reads you will format later.

Can I set a cell value with .Text in VBA?

No. .Text is read-only — it reports the formatted string shown on screen, and assigning to it, such as Range("A1").Text = "5", raises an error. To write a cell, assign to .Value (or .Value2), and control how it appears with the cell's number format rather than by writing a formatted string.

Tested in

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

Related guides: VBA Cell Value · VBA Formula · VBA Number Format · VBA Now, Date & Time · VBA Format