Excel VBA Array to Sheet: Write Data with Range.Value

VBA & macros · Updated November 21, 2026

To write a VBA array to an Excel worksheet, assign a two-dimensional array to a range of the same size with the Range.Value property. Resize the destination to match the array’s row and column counts, then make one assignment; this is usually simpler and faster than writing each cell individually.

How an array becomes worksheet data

A VBA array holds a group of values in memory. A worksheet range is a rectangular group of cells. Excel can transfer the values between them in one operation when the array is two-dimensional and the destination range has matching dimensions. The first array dimension represents rows and the second represents columns, so an array indexed as data(row, column) maps naturally onto a worksheet block.

This is useful when a macro imports a file, calculates results, filters records, or reshapes data before displaying it. A common pattern is to read a range into a Variant array, make changes in memory, and write the updated array to another range. It reduces repeated communication between VBA and the worksheet, which can be a major speed improvement for larger datasets.

The assignment writes cell contents, not a new worksheet object. It does not automatically create headers, expand a table, or choose a destination based on the array. Your code needs to select the correct worksheet and starting cell, and ensure the output area is appropriate before writing.

The basic pattern

For a rectangular array, use Resize to create a destination with the array’s row and column counts. UBound returns the highest index in a dimension; with an array whose lower bounds are 1, those upper bounds are also its row and column counts. The following pattern assumes a two-dimensional array named data and a starting cell in A1:

Set destination = ws.Range("A1").Resize(UBound(data, 1), UBound(data, 2))
destination.Value = data

In a complete procedure, declare the worksheet, array, and destination variables. Set the worksheet explicitly rather than relying on whichever sheet happens to be active. The code below creates a small 4-row-by-3-column array and writes it to the Report worksheet beginning in A1.

Complete example: create and write a 2D array

Before running a macro that changes workbook contents, save a separate copy of the workbook. Macros cannot be undone with Ctrl+Z, so a backup is the practical way to restore data if the output is not what you intended. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its code.

Sub WriteArrayToSheet()
    Dim ws As Worksheet
    Dim values(1 To 4, 1 To 3) As Variant
    Dim destination As Range

    ' Fill a 2D array: first index is row, second is column.
    values(1, 1) = "Product"
    values(1, 2) = "Units"
    values(1, 3) = "Price"
    values(2, 1) = "Pencils"
    values(2, 2) = 12
    values(2, 3) = 0.5
    values(3, 1) = "Notebooks"
    values(3, 2) = 4
    values(3, 3) = 3.25
    values(4, 1) = "Folders"
    values(4, 2) = 7
    values(4, 3) = 1.1

    Set ws = ThisWorkbook.Worksheets("Report")
    Set destination = ws.Range("A1").Resize( _
        UBound(values, 1), UBound(values, 2))

    destination.Value = values
End Sub

After the macro runs, A1:C4 contains the headers and three product records. The values are written in row order: values(2, 1) goes to A2, values(2, 2) to B2, and values(2, 3) to C2. The worksheet must contain a sheet named Report; otherwise, the attempt to set ws raises a subscript error. Change the sheet name or create the sheet before running the procedure.

Match the range size to the array

For predictable results, make the target a single rectangular range with exactly as many rows and columns as the array. In the example, the array has four rows and three columns, so the destination is A1:C4. If the target is too small, values cannot all be placed in the intended cells. If it is larger, the extra cells are not a safe place to rely on implicit behavior; explicitly size the range to the data instead.

UBound(data, 1) asks for the upper bound of the first dimension, and UBound(data, 2) asks for the second. The example declares both dimensions beginning at 1, so those bounds equal the number of rows and columns. If an array starts at zero or another lower bound, the count is not just UBound. Calculate each dimension as UBound(array, dimension) minus LBound(array, dimension) plus one. This distinction prevents a range from being undersized when an array’s lower bound is not 1.

An array also needs to be initialized before asking for its bounds. A dynamic array that has not been allocated has no usable dimensions, and calling UBound on it can cause an error. Allocate it with ReDim, or obtain it from a multi-cell range, before calculating the destination size.

Writing arrays read from a range

When a multi-cell range is assigned to a Variant variable, Excel returns its values as a two-dimensional array. This makes copying or processing a block straightforward: assign the source range’s Value or Value2 to a Variant, then assign that array to a destination of equal dimensions. For example, reading A2:C100 produces an array with 99 rows and three columns, which can be written starting elsewhere without looping over individual cells.

There is an important exception: reading a single cell returns a scalar value, not a two-dimensional array. Code that assumes every source is an array can therefore fail when the source range contains only one cell. If the range size can vary, handle the one-cell case separately or normalize the input into a two-dimensional array before using UBound and assigning it.

Value2 is often useful when moving values because it avoids the Currency and Date data subtypes used by Value. It returns the underlying numeric value for dates and currency-formatted cells; number formatting remains a separate worksheet property. Choose Value or Value2 deliberately, especially if VBA code relies on a value being represented as a Date or Currency type.

One-dimensional arrays need a shape decision

A one-dimensional array has one index, but a worksheet range has rows and columns. Excel cannot infer whether a list should fill down a column or across a row in every situation. For robust output, build a two-dimensional array with either one column or one row, then write it to a correspondingly shaped range.

Application.Transpose can convert a one-dimensional array for certain assignments, but it is not a universal substitute for choosing the shape yourself. Its result can be awkward when array sizes or bounds vary, and its behavior should be checked for the specific data being handled. A clearly constructed two-dimensional array makes the intended layout explicit and works well with the same Resize-and-assign pattern used for tables.

For instance, if a list of names should occupy A1:A20, create an array with 20 rows and one column. If the same names should go across the first row, create one row and 20 columns. This also makes later code easier to maintain because the array’s dimensions communicate how the data is meant to appear.

Values, formulas, and cell formatting

Use Value or Value2 when the array contains results or ordinary data. If the output is intended to be worksheet formulas, use the Range.Formula property and supply formula strings in an array of the same shape. Keeping the property choice explicit helps distinguish calculated expressions from values that have already been calculated. Test formula output on a small range before applying it to a large block.

Writing values does not copy the source cells’ number formats, fonts, borders, or fills. For example, a date serial number may be written correctly but display as a number if the destination is formatted as General. Apply a suitable format to the destination separately when presentation matters, or copy formats using an appropriate range operation. Formatting and data transfer are separate tasks.

Also consider what is already in the destination. The assignment replaces the contents of the cells it covers, so existing values or formulas in that block can be overwritten. It generally does not clear unrelated cells beyond the destination, and it does not automatically remove old output below a shorter new result. If output size changes from run to run, decide whether to clear the previous output area and do so only where that is safe.

Why bulk assignment is often faster

A loop that writes one cell at a time repeatedly crosses between VBA and Excel’s worksheet object model. A single assignment transfers a block in one call, so bulk assignment is usually preferable for large tables. You can still use loops to calculate or edit array elements in memory; the speed benefit comes from avoiding a separate worksheet write for every element.

For larger macros, consider reading the source data into an array once, performing the processing in VBA, and writing the final result once. Avoid selecting or activating cells just to move data: direct references such as ThisWorkbook.Worksheets(“Report”).Range(“A1”) are less dependent on the user’s current selection. If a macro also changes calculation or screen updating settings for performance, restore the prior settings even if an error occurs.

Common errors and practical checks

  • Wrong number of dimensions: A two-dimensional range needs a rectangular, two-dimensional array. Confirm that the array is not merely a one-dimensional list.
  • Wrong dimension order: The first index is the row and the second is the column. Reversing them can transpose the intended layout or lead to a size mismatch.
  • Unallocated array: Allocate a dynamic array before using UBound, or check that a range read actually returned an array rather than a single value.
  • Wrong worksheet reference: Check the workbook and sheet names. ThisWorkbook means the workbook containing the VBA code, which may differ from ActiveWorkbook.
  • Output overwrites needed data: Verify the start cell and output dimensions, particularly when the macro runs repeatedly or produces a variable number of rows.
  • Unexpected display: The underlying values may be correct even if dates, decimals, or large numbers look wrong. Check the destination’s number format separately.

When diagnosing a problem, inspect the array’s lower and upper bounds and the destination’s row and column counts before making the assignment. A small test range and a few known values can reveal whether the issue is the array’s shape, the selected sheet, or cell formatting.

Running VBA in Excel and alternatives

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings, so the ribbon command is a dependable route. VBA macros do not run in Excel for the web; open the workbook in the desktop app to run them.

For a one-time transfer that needs no transformation, copying and pasting a worksheet range may be simpler than writing a macro. For repeatable import and cleanup workflows, Power Query is often a useful alternative because it can load and transform data through its interface. Formulas can also generate results directly on a sheet. VBA arrays make most sense when a macro needs custom processing, automation, or a fast write of values already assembled in memory.

Frequently Asked Questions

Can I write a VBA array directly to a range?
Yes. Assign a two-dimensional array to a single-area range of matching dimensions using Range.Value or Range.Value2. The first array dimension maps to worksheet rows and the second to columns.

Why does assigning my array to a range give a type mismatch?
Common causes include assigning a one-dimensional array to a two-dimensional range, using an unallocated array, or giving the target a different shape. Check the array bounds and make sure the target matches its row and column counts.

How do I write an array vertically?
Use a two-dimensional array with one column and as many rows as there are items, then assign it to a one-column destination. This states the intended orientation directly and avoids ambiguity about a one-dimensional array.

Does writing an array also copy formatting?
No. A Value or Value2 assignment transfers contents, not number formats, colors, borders, or other formatting. Format the destination separately if the output needs a particular appearance.

Can I use this method in Excel for the web?
Excel for the web does not run VBA macros. Use the desktop version of Excel to run the procedure; for browser-based automation, consider an available alternative such as Office Scripts, which uses a different programming model.

Related

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.