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

VBA Cell Value in Excel — Read and Write .Value, and the Range-to-Array Trick

|

VBA Cell Value in Excel — Read and Write .Value, and the Range-to-Array Trick

TL;DR — Range("A1").Value reads or writes one cell. The thing that matters is what happens with many cells: arr = Range("A1:C1000").Value reads the whole block into a 1-based, 2D Variant array in a single call, and Range("A1:C1000").Value = arr writes it back in one call. Touching cells one at a time — Cells(i, j).Value in a loop — crosses from VBA into Excel on every read, and that boundary crossing, not the arithmetic, is what makes big loops slow.

Dim v As Variant
v = Range("A1").Value              ' read one cell into a variable
Range("B1").Value = v * 1.08       ' write one cell

Dim arr As Variant
arr = Range("A1:A10000").Value     ' read 10,000 cells in ONE call -> 2D array
' ... process arr in memory ...
Range("C1:C10000").Value = arr     ' write them all back in ONE call

Reading and writing values is the first thing anyone does in VBA, and for a single cell it is exactly as simple as it looks. The idea this guide is built on shows up the moment you have more than one cell: .Value on a range is not one value, it is a block — a two-dimensional array — and moving that block in a single call instead of one cell at a time is the difference between a macro that feels instant and one that hangs. Hold that, and both the speed rules and the small type traps fall into place.

What you'll learn

  • The mental model — .Value is a block, and a range gives a 2D Variant array
  • The rule that matters most — read and write the whole block in one round-trip, not cell by cell
  • Why the array is 1-based and 2D, even for a single column
  • The single-cell special case — one cell is a scalar, not an array
  • .Value is the default property — the shortcut, and why not to rely on it
  • Assigning a value replaces whatever was in the cell, formula included

The mental model: .Value is a block

For one cell, .Value is a single value you read or assign:

Dim name As String
name = Range("A1").Value           ' scalar in
Range("A2").Value = "Total"        ' scalar out

For a range of many cells, .Value is the whole grid at once. Read it into a Variant and you get a two-dimensional array; assign a two-dimensional array to it and you set every cell in one move:

Dim block As Variant
block = Range("A1:C100").Value     ' block is now a 100 x 3 Variant array

The important word is once. Each time your code reads or writes .Value, it crosses the boundary between the VBA engine and the Excel application — a relatively expensive hop. One cell is one hop; a whole block read into an array is also one hop. That single fact — a range is a block you can move in a single crossing — is what drives the performance rule below and is the most valuable thing to know about .Value.

The rule that matters most: round-trip the array, don't loop the cells

Here is the same job done two ways. Sum a column, marking up each value by 8% and writing it next to it.

The slow way touches Excel on every iteration:

Dim i As Long
For i = 1 To 10000
    Cells(i, 3).Value = Cells(i, 1).Value * 1.08   ' 2 crossings per row = 20,000 hops
Next i

The fast way crosses twice, total, and does the work in memory:

Dim src As Variant, out() As Variant, i As Long
src = Range("A1:A10000").Value          ' 1 hop in
ReDim out(1 To UBound(src, 1), 1 To 1)
For i = 1 To UBound(src, 1)
    out(i, 1) = src(i, 1) * 1.08        ' pure VBA - no Excel involved
Next i
Range("C1:C10000").Value = out          ' 1 hop out

The arithmetic is identical; the loop is identical in length. The only difference is that the second version reads 10,000 cells in one call and writes 10,000 in one call, while the first makes 20,000 separate trips across the VBA-to-Excel boundary. On real data the array version is routinely tens of times faster — often the difference between "instant" and "watch the hourglass." For an even bigger win, pair this with turning off recalculation and screen updates during the write; see VBA Calculation and VBA ScreenUpdating. The boundary crossing is the cost, and the array round-trip is how you stop paying it per cell.

Why the array is 1-based and 2D

The Variant array you get from a range surprises people twice. First, it is always 1-based, regardless of any Option Base setting — arr(1, 1) is the top-left cell. Second, it is always two-dimensional, even when the range is a single column or a single row:

Dim col As Variant
col = Range("A1:A5").Value          ' a single column
Debug.Print col(3, 1)              ' NOT col(3) - it is (row, column)

A one-column range is a 5-by-1 array, so you index it col(row, 1); a one-row range is 1-by-5, indexed row(1, col). Writing col(3) instead of col(3, 1) raises "Subscript out of range" and is the most common mistake when people first switch to arrays. If you genuinely want a 1-D array from a single column, wrap the read in Application.Transpose — but be aware Transpose has its own limits (it caps around 65,536 elements and coerces types), so for large or mixed data the plain 2D array is safer. See VBA Array for working with the result.

The single-cell special case

There is one edge that breaks array code: if the range is a single cell, .Value returns a plain scalar, not a 1-by-1 array.

Dim v As Variant
v = Range("A1").Value              ' A1 alone -> a scalar, not v(1, 1)
' v(1, 1) here raises an error

So code that reads rng.Value into an array and then indexes arr(1, 1) works for a multi-cell rng but blows up when rng happens to be one cell. If a range size can vary down to one cell, either force a minimum size, or check rng.Cells.Count > 1 before treating the result as an array. It is a small inconsistency in Excel's object model, but it causes real "works on the big sheet, fails on the small one" bugs.

.Value is the default property — the shortcut and its risk

.Value is the default property of a Range, which means Excel lets you leave it off:

x = Range("A1")                    ' works - implicitly .Value
Range("A1") = 42                   ' works - implicitly .Value

It reads cleanly, and plenty of code does it. The risk is that the shortcut hides which operation you mean. Set rng2 = Range("A1") assigns the range object; x = Range("A1") assigns its value — the presence or absence of Set silently changes the meaning. And If Range("A1") = Range("B1") compares values, which may or may not be what a reader expects when the code says nothing about values. The habit worth keeping: write .Value explicitly when you mean the value. It costs six characters and removes a whole class of "did they mean the cell or its contents" ambiguity — especially around Set, covered in VBA Range.

Assigning a value replaces the formula

One last thing that trips people who mix values and formulas: writing .Value to a cell overwrites whatever was there, including a formula.

Range("D2").Formula = "=B2*C2"     ' D2 is now a live formula
Range("D2").Value = 100            ' D2 is now the static number 100 - the formula is gone

This is usually what you want — it is exactly how you "convert a formula to its result" in a single cell (assign the value back over itself). But it means you cannot nudge a value without destroying a formula that produced it. When you need a live formula in the cell, assign .Formula, not .Value — the two are different faces of the same cell, covered in VBA Formula. And when you care about the precise number you read back — money, dates, long decimals — the choice between .Value and .Value2 starts to matter, which is the subject of VBA Value vs Value2 vs Text.

How ExcelMaster helps

The .Value mistakes that cost real time are not typos — they are the slow cell-by-cell loop on a big sheet, the arr(3) that should have been arr(3, 1), the array code that breaks on a one-cell range, and the implicit .Value that quietly compared the wrong thing. Each one runs; it just runs wrong or slow.

ExcelMaster writes the fast, correct version by default. Ask it to "mark up column A by 8% into column C," and it reads the block into a Variant array, processes it in memory, and writes it back in one assignment — with recalculation and screen updates handled around the write. It indexes the 2D array correctly, guards the single-cell case, and writes .Value explicitly so the code says what it means. You describe the transformation; it writes the round-trip that runs in a blink instead of a loop that crawls.

Frequently asked questions

How do I get a cell value in Excel VBA?

Read the Value property of a Range: x = Range("A1").Value or x = Cells(1, 1).Value. For many cells at once, read the whole range into a Variant — arr = Range("A1:C100").Value — which gives a 1-based, two-dimensional array (arr(row, column)). Reading a block into an array is far faster than reading each cell in a loop, because each individual .Value access crosses from VBA into Excel.

How do I write a value to a cell in VBA?

Assign to Value: Range("A1").Value = 42 or Cells(1, 1).Value = "Total". To write many cells at once, build a 2D Variant array and assign it to a range of the same shape: Range("A1:A100").Value = arr. This writes all 100 cells in a single operation instead of looping, which is dramatically faster on large ranges.

Why is my VBA range value a 2D array?

Because a multi-cell range is a grid, so its .Value is a two-dimensional array indexed as arr(row, column) and always 1-based — even a single column is an N-by-1 array, so you index it arr(3, 1), not arr(3). Indexing with one subscript raises "Subscript out of range." A single cell is the exception: Range("A1").Value returns a plain scalar, not a 1-by-1 array.

Why is reading cells in a VBA loop so slow?

Every Cells(i, j).Value access crosses the boundary between the VBA engine and the Excel application, and that hop is the expensive part — not the arithmetic. A loop over 10,000 rows that reads and writes each cell makes 20,000 crossings. Reading the range into an array, processing it in memory, and writing the array back makes just two crossings, which is why it is often tens of times faster.

What is the difference between .Value and just Range("A1") in VBA?

.Value is the default property of a Range, so x = Range("A1") is treated as x = Range("A1").Value. They read the same for values, but leaving .Value off hides your intent: Set rng = Range("A1") assigns the range object, while x = Range("A1") assigns its value, and the difference is only the Set keyword. Writing .Value explicitly makes the code unambiguous.

Tested in

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

Related guides: VBA Range · VBA Cells · VBA Formula · VBA Value vs Value2 vs Text · VBA Array