VBA Range.Value: Reading and Writing Excel Cell Data

In Excel VBA, Range.Value reads or writes the value of a cell or range, such as a number, text string, date, error, or formula result. Use Range("A1").Value to get one cell’s value, or assign a value with Range("A1").Value = 42; for a multi-cell range, VBA can return or accept a two-dimensional array.

What Range.Value means in VBA

Range identifies one or more worksheet cells, and Value is a property of that range. A property can be read, assigned, or—in some cases—both. Thus, Range("A1").Value asks Excel for the cell’s value, while an assignment such as Range("A1").Value = "Ready" places text in the cell.

The property returns a VBA Variant. A Variant can hold several kinds of data, including text, numbers, dates, Boolean values, and Excel error values. The returned VBA type depends on the cell’s content, so code that accepts different kinds of worksheet data often stores the result in a Variant rather than assuming it is always a string or number.

Qualify a range with its worksheet when the macro could run while another sheet is active. For example, ThisWorkbook.Worksheets("Data").Range("A1").Value refers to cell A1 on the Data sheet in the workbook containing the macro. An unqualified Range("A1") refers to the active sheet, which can lead to reading or changing the wrong cell.

Reading one cell or a range

When the range is a single cell, its value is a single Variant. Store it in a variable, inspect it, or use it in an expression. This small macro reads a cell and writes the same value to another cell:

Sub CopyOneValue()
    Dim ws As Worksheet
    Dim cellValue As Variant

    ' Use the Data sheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    cellValue = ws.Range("A1").Value
    ws.Range("B1").Value = cellValue
End Sub

For a rectangular range containing several cells, reading .Value produces a two-dimensional Variant array. The first array dimension corresponds to worksheet rows and the second to columns. For a range from A1 through B3, the returned values can be accessed as values(1, 1) through values(3, 2). Assign the result to a Variant variable so it can hold either a single value or an array.

Before running a macro that changes a workbook, save a separate copy: VBA macros cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11 (some keyboards also require Fn). You can also open it from Developer > Visual Basic.

Writing values to cells

Assign a literal, a variable, or the value of another cell to a range. The assignment writes cell contents; it does not copy the source cell’s formatting, comments, or other properties.

Range("A1").Value = 25 writes the number 25.
Range("A2").Value = "Complete" writes text.
Range("A3").Value = Range("B3").Value copies the value from B3 to A3.

You can assign a two-dimensional array to a matching rectangular range in one operation. This avoids a separate VBA-to-worksheet assignment for every cell, which is useful when processing many rows. In the example below, the array dimensions match the destination’s two rows and two columns:

Sub WriteSmallTable()
    Dim ws As Worksheet
    Dim data(1 To 2, 1 To 2) As Variant

    ' Fill the array by row, then by column.
    Set ws = ThisWorkbook.Worksheets("Data")
    data(1, 1) = "Item"
    data(1, 2) = "Quantity"
    data(2, 1) = "Pens"
    data(2, 2) = 12

    ' Write all four values to the matching 2-by-2 range.
    ws.Range("A1:B2").Value = data
End Sub

Match the array’s row and column counts to the destination range. A mismatch can leave cells unchanged or produce unintended results, depending on the assignment and destination. For predictable output, create an array sized for the exact rectangular range you intend to fill.

Using Value with formulas

Reading .Value from a cell that contains a formula returns the formula’s calculated result, not the formula text. For example, if A1 contains =2+3, then Range("A1").Value reads 5 after Excel has calculated the formula. Assigning to .Value replaces the cell’s contents, so assigning 10 to A1 removes its formula and leaves the value 10.

To put a formula in a cell, use the appropriate formula property rather than treating .Value as a formula-writing property. In Microsoft 365 Excel for Windows, .Formula accepts formula text using the traditional Formula property behavior; .Formula2 supports modern dynamic-array formula behavior. For example, a macro can assign "=SUM(B2:B10)" through one of those properties. If the goal is to copy only a formula’s current result, assigning the source cell’s .Value is appropriate.

Value, Value2, Text, and Formula compared

These properties answer different questions. Choosing the right one prevents common surprises when dates, currency, formatting, or formulas are involved.

.Value reads or writes a cell’s value. It can represent date-formatted and currency-formatted numbers as VBA Date and Currency subtypes in applicable cases.
.Value2 is similar, but does not use the VBA Date or Currency subtypes. Date-like values are returned as numbers, and currency-like values are returned as numbers. It is often a convenient choice for bulk data processing when that distinction is not needed.
.Text returns the text as displayed in the cell, including effects of number formatting and column width. It is read-only and may return a display such as #### when a column is too narrow, rather than the underlying numeric value.
.Formula reads or writes a formula as formula text. It is not the same as the calculated result returned by .Value.

For example, a cell containing the date serial number 45658 and formatted as a date may be represented by .Value as a date, while .Value2 returns the underlying number. The display depends on the cell’s number format, so use .Text only when the displayed string itself is what the macro needs.

Handling types, blanks, and errors

Worksheet cells can contain values that need careful handling in VBA. A truly empty cell, a formula returning an empty string, and a cell containing a space may look similar but are not equivalent. A formula returning "" still contains a formula; a space is text. If your code needs to distinguish these cases, inspect the value and, when relevant, the cell’s formula or blank status as separate information.

Excel error values such as #N/A can also be returned through a Variant. Before doing arithmetic or converting the value to a string, test for an error with VBA’s IsError function. Similarly, use IsEmpty where you need to detect an Empty Variant, but do not assume it alone identifies every cell that appears blank. A formula returning an empty string is not the same as an empty cell.

When reading a multi-cell range into an array, test and process elements safely if the source may contain errors or mixed data types. A direct conversion such as CLng can fail on text, blanks, or error values. Decide how the macro should treat those cases before converting data.

Common problems and practical fixes

Wrong worksheet: An unqualified Range points to the active sheet. Store a worksheet reference and use ws.Range(...) to make the target explicit.
Formula unexpectedly disappears: Assigning to .Value overwrites the formula. Use .Formula or .Formula2 when you intend to enter a formula.
Unexpected displayed value: .Value gives the underlying value, not necessarily the formatted appearance. Use .Text only if you specifically need what is displayed.
Array indexing error: A multi-cell range yields a two-dimensional array, not a one-dimensional list. Use row and column indices and make the destination dimensions match.
Unexpected results from several areas: A non-contiguous range, such as A1:A3,C1:C3, is a multi-area range. The Value property does not provide a simple combined rectangular array for every such case. Loop through the range’s Areas collection and handle each area separately.
Slow cell-by-cell processing: Repeatedly reading and writing individual cells can be slow for large datasets. Read a contiguous block into an array, process it in VBA, and write the completed array back in a single operation.

If a macro depends on formula results being current, consider Excel’s calculation mode. In manual calculation mode, a formula’s displayed or returned result may not update until calculation occurs. Calculate the relevant worksheet or workbook when appropriate rather than assuming every formula has recalculated automatically.

Frequently Asked Questions

What does Range.Value return?
A single-cell range returns that cell’s value as a Variant. A rectangular multi-cell range returns a two-dimensional Variant array. If the cell contains a formula, the property returns its calculated result rather than the formula text.

What is the difference between Value and Value2 in VBA?
Both access cell values, but Value2 does not use VBA Date and Currency subtypes. It returns date- and currency-formatted numbers as numeric values. Use Value when those VBA subtypes are useful; use Value2 when you want numeric values for processing.

Does Range.Value copy formatting?
No. It transfers cell contents, not formatting. To copy formats as well, use a suitable copy-and-paste method or handle formatting separately.

How do I write a formula with Range.Value?
Use .Formula or, for dynamic-array formula behavior in supported Excel versions, .Formula2. Assigning formula text to .Value is not the clear, reliable way to express the intent and may instead write content as a value.

Can Range.Value return an array?
Yes. Reading a rectangular range with more than one cell returns a two-dimensional Variant array. A single-cell range returns one value rather than an array, so code that handles both cases should account for that difference.

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.