In Excel VBA, you can read a block of worksheet cells into a Variant array with one assignment, process the values in memory, then write the array back to a same-sized range. This is usually much faster than reading and writing each cell individually, especially for large tables.
What “VBA array cells” means
A worksheet range and a VBA array are different things. A range refers to cells in a workbook; an array is a set of values held in memory while the macro runs. Moving data between them lets VBA work on many cells without repeatedly communicating with the worksheet.
For a rectangular range with more than one cell, assigning its Value2 property to a Variant creates a two-dimensional array. The first dimension represents worksheet rows and the second represents columns. If the range is A2:C4, for example, the array has three rows and three columns: the value from A2 is at data(1, 1), B2 at data(1, 2), and C4 at data(3, 3).
This does not create a live link. After the values are copied into the array, editing an array element does not change the worksheet until the array is assigned back to a range.
Read a range of cells into an array
Use a Variant variable to receive the values. The following example reads a contiguous, multi-cell range from a worksheet named Sheet1. Before running any macro that changes workbook data, save a separate copy of the workbook: VBA changes cannot be undone with Ctrl+Z.
Sub ReadCellsIntoArray()
Dim ws As Worksheet
Dim data As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")
data = ws.Range("A2:C100").Value2
' The values are now available in memory.
Debug.Print data(1, 1)
End Sub
The example assumes A2:C100 contains at least two cells. A multi-cell range returns a two-dimensional array, including when the range is only one row high or one column wide. Use LBound and UBound to inspect its dimensions rather than assuming how many rows or columns it contains.
Value2 returns the cells’ underlying values without converting dates and currency values to VBA Date and Currency subtypes. Excel dates are stored as serial numbers, so a date read with Value2 is represented numerically. The cell’s displayed date format is not included in the array.
Process array values in memory
Loop through the array’s row and column dimensions to examine or change individual elements. This example doubles numeric values in the range and writes the results back. It is intended for a range of numeric data: dates and numeric identifiers are also numeric in Excel, so do not use this transformation on columns where doubling those values would be inappropriate.
Sub DoubleNumericCells()
Dim ws As Worksheet
Dim data As Variant
Dim r As Long
Dim c As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
data = ws.Range("A2:C100").Value2
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Not IsError(data(r, c)) Then
If IsNumeric(data(r, c)) Then
data(r, c) = CDbl(data(r, c)) * 2
End If
End If
Next c
Next r
ws.Range("A2:C100").Value2 = data
End Sub
The first argument to LBound or UBound identifies the dimension: 1 means rows, and 2 means columns. Using these bounds makes a loop more adaptable if the range size changes. The IsError check prevents an Excel error value such as #N/A from being passed into the numeric test and calculation. Blank cells are not treated as numeric values by this example.
Write an array back to worksheet cells
Assign the completed array to a range through Value2 or Value. The destination should be one contiguous rectangular range with the same number of rows and columns as the array. In the example above, the array came from A2:C100, so that same range is a matching destination.
Writing an array in one assignment replaces the destination cells’ contents with the array values. It does not copy the source cells’ number formats, fonts, borders, or column widths. Existing formatting in the destination usually remains, but formulas in those cells are replaced by the written results. Choose the output range carefully if the original formulas or values must be preserved.
For a separate output area, use a destination of matching dimensions. For instance, data read from A2:C100 can be written to E2:G100. A mismatched range size can lead to unwanted results or errors; do not rely on Excel to resize a destination to fit the array.
Handle one cell and changing range sizes
A common source of a “Subscript out of range” error is assuming that every range produces a two-dimensional array. When the source is exactly one cell, Range.Value2 returns a single value, not a 2D array. Code such as data(1, 1) therefore fails if the selected range has only one cell.
If a procedure must support both one cell and many cells, test the range size and handle the one-cell case separately, or normalize the input into an array before using two-dimensional indexing. A simple alternative is to make the input range a known multi-cell rectangle, as in the examples here. Also remember that a one-row range with several cells is still represented as a two-dimensional array; it has one row, not a one-dimensional array.
When a range’s size varies, determine its last row or column first and build a range that excludes headers or unused cells. Be cautious with CurrentRegion: it identifies a connected block around a cell, but a blank row or column can split a table and change the returned range. For reliable automation, use a defined table or a clear data-boundary rule rather than assuming that every used cell belongs to the dataset.
Values, formulas, and other cell details
Value2 reads calculated results, not the formula text. If a cell contains =A1+B1, the array normally receives the value currently displayed by the calculation, rather than the characters of the formula. Assigning that array back as shown writes values and does not restore the original formulas.
If your task is to copy or edit formulas, use the appropriate formula property instead of treating the values array as a formula array. Excel for Microsoft 365 on Windows supports Formula2 for formulas that use modern dynamic-array behavior; Formula remains relevant for traditional formula handling. Formula behavior can depend on the type of formulas and the Excel version, so preserve a workbook copy and verify the result when modifying formula ranges.
An array of values also does not carry formatting, comments, hyperlinks, validation rules, or row heights. Those are separate worksheet properties. If the goal is to duplicate a fully formatted range rather than process its data, a range-to-range copy may be more suitable than a values array.
Why arrays can make VBA faster
Every direct reference such as Cells(r, c).Value requires VBA to access the Excel object model. Repeating that operation for thousands of cells adds overhead. Reading a block once, looping over values in memory, and writing once reduces the number of worksheet interactions to two, which can make bulk processing substantially faster.
Arrays are especially useful for tasks such as checking records, transforming numeric columns, filtering rows into a new result, or preparing data for export. They are less useful for a tiny range or for work that depends on cell formatting and worksheet features. Large arrays also consume memory, so processing an enormous sheet may be better done in manageable blocks.
Common problems and practical checks
- Wrong array bounds: Use
LBoundandUBoundfor both dimensions instead of assuming the last index. - One-cell input: A single cell returns a scalar value, so it cannot be indexed as
data(1, 1)without special handling. - Wrong output size: Make the destination range match the array’s rows and columns.
- Unexpected formulas disappearing: Assigning values back replaces formulas in the destination cells.
- Error values: Check for errors before performing numeric operations or string conversions.
- Dates or identifiers changing: Excel dates are numeric serials, and identifiers may look numeric. Apply transformations only to the intended fields.
- Noncontiguous selections: A multi-area range is not a simple rectangular block. Read and process each area separately or use a contiguous range.
For debugging, inspect the source range address, array bounds, and a few representative elements before writing anything back. If the array is large, test the procedure on a small copied sample first. That helps distinguish an indexing problem from a data-type or range-sizing problem.
Frequently Asked Questions
How do I put Excel cells into a VBA array?
Declare a Variant and assign a multi-cell range’s Value2 property to it, for example: data = Worksheets("Sheet1").Range("A2:C10").Value2. The result is a two-dimensional array indexed by row and column.
How do I write an array to cells in Excel?
Assign the array to a same-sized contiguous range, such as Worksheets("Sheet1").Range("E2:G10").Value2 = data, provided data contains three columns and nine rows. This writes values, not cell formatting or formulas.
Why does a one-cell range cause a VBA array error?
A one-cell range returns a single value rather than a two-dimensional array. Code that indexes it with two subscripts will fail. Handle a single cell as a scalar or ensure the input range contains multiple cells.
Does assigning a range to an array keep the formulas?
Not when you use Value or Value2; those properties return calculated values. To work with formula text, use a formula property such as Formula or, for modern dynamic-array formulas in supported Excel versions, Formula2.
Are VBA arrays faster than looping through cells?
For large blocks, usually yes: one range read and one range write avoid repeated worksheet access for every cell. For a handful of cells, the difference may not matter, and tasks involving formatting may be better handled directly with range methods.
Related
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Right in Excel VBA: Syntax, Examples, and TipsIn Excel VBA, Right returns a specified number of characters from the end of a string. For example,…
- Excel VBA: Open a New Workbook with Workbooks.AddTo open a new workbook in Excel VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates and…
- VBA Delete Row Based on Cell Value in ExcelTo delete Excel rows when a cell contains a chosen value, loop through the worksheet from the bottom…
- VBA MsgBox Function: Syntax, Buttons, and ExamplesIn VBA, MsgBox displays a dialog box with a message and optional buttons, title, and icon. It also…
- VBA MsgBox Context: What It Means and How to Use ItIn VBA, the context argument in MsgBox is a numeric help-topic identifier: it tells the associated help system…
- Excel VBA Substring: Extract, Find, and Replace TextExcel VBA has no built-in function named Substring, but you can work with part of a string using…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.