Excel VBA Array Example: Create, Fill, and Write Data

VBA & macros · Updated November 21, 2026

A simple Excel VBA array example is to store values in a two-dimensional array, then write the array to a worksheet range in one operation. The macro below fills a 4-by-3 array and places its contents in cells A1:C4; this is useful for transferring groups of values without writing to each cell separately.

What an array does in VBA

An array is a variable that holds multiple values under one name. Instead of declaring separate variables for a product name, quantity, and price on every row, you can store those values in an array and refer to each item by its position, called an index. Arrays are useful when VBA needs to collect, calculate, sort, or transfer a set of related values.

A one-dimensional array is like a list. A two-dimensional array is like a table, with a row index and a column index. For worksheet data, a two-dimensional array usually maps most naturally to a rectangular range: the first array dimension represents rows, and the second represents columns. VBA arrays can have more dimensions, but two is the common choice for tabular worksheet data.

Arrays are held in memory while the macro runs. That makes it possible to work with many values before sending the result to the sheet. The array itself does not create a worksheet, add a formula, or automatically update when cells change; the macro controls when values are read and written.

A complete example: build and write a 2D array

This macro fills a fixed-size, two-dimensional Variant array with a header and three records. It then writes the entire array to a matching worksheet range.

Sub WriteArrayExample()
    Dim data(1 To 4, 1 To 3) As Variant
    Dim ws As Worksheet

    ' Use the worksheet in the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Fill the header row.
    data(1, 1) = "Product"
    data(1, 2) = "Qty"
    data(1, 3) = "Price"

    ' Fill three rows of sample data.
    data(2, 1) = "Notebook"
    data(2, 2) = 4
    data(2, 3) = 2.5

    data(3, 1) = "Pen"
    data(3, 2) = 10
    data(3, 3) = 0.8

    data(4, 1) = "Folder"
    data(4, 2) = 3
    data(4, 3) = 1.25

    ' Match the range dimensions to the array dimensions.
    ws.Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data
End Sub

The declaration reserves four rows and three columns. For example, data(3, 2) means the value in the third array row and second array column, which is 10. The worksheet range starts at A1 and expands to four rows by three columns, so the output is A1:C4. Row 1 contains Product, Qty, and Price; the remaining rows contain the sample records.

The sample uses numeric values for quantities and prices, not text that merely looks numeric. VBA writes values rather than number formats, so Excel may display 2.5 as 2.5 or 2.50 depending on the cell’s existing format. Apply a suitable worksheet number format separately if fixed decimal places or currency display are required.

Run the macro and check the result

In desktop Excel for Microsoft 365 on Windows, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) before keeping VBA code in it. Open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel and use Alt+F8 to select and run the macro.

On Mac, VBA is available in desktop Excel, though keyboard shortcuts can depend on the keyboard and macOS settings. Option+F11 commonly opens the Visual Basic Editor, and Option+F8 commonly opens the macro list; use the Excel menu or ribbon if your function keys are configured differently. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. The Developer tab may need to be enabled in desktop Excel before some macro controls are visible.

Before running a macro that changes a workbook, save a copy. VBA changes cannot be undone with Ctrl+Z, and running this example again overwrites A1:C4 on the named sheet. The procedure expects a worksheet named Sheet1 in the same workbook that contains the macro. If that sheet is missing or renamed, change the worksheet name in the code or the procedure will stop with a run-time error.

Fixed, dynamic, and Variant arrays

The example uses a fixed-size array: its row and column bounds are set in the declaration and stay the same while the procedure runs. Fixed-size arrays are straightforward when the amount of data is known in advance. Here, the bounds run from 1 to 4 for rows and 1 to 3 for columns. Using explicit bounds makes the intended layout easy to read and avoids relying on VBA’s default lower bound.

A dynamic array is useful when the number of records is not known until the macro runs. Declare it with empty parentheses, then allocate its size with ReDim. For example, Dim items() As Variant declares a dynamic array, and ReDim items(1 To recordCount, 1 To 3) gives it a row for each record and three columns. The variable recordCount must already contain a valid positive count. ReDim normally discards values already stored in the array; ReDim Preserve can keep existing values when resizing the last dimension of a multidimensional array, but it cannot preserve values while changing the earlier dimension.

Variant is a flexible data type that can hold text, numbers, dates, or other values, which is convenient for mixed worksheet tables. You can also declare typed arrays such as Dim quantities() As Long when all the values are whole numbers. Typed arrays can make the intended data type clearer, but they are less convenient for mixed columns. Choose a type that matches the data and account for possible values that do not fit it.

Read worksheet values into an array

Arrays are useful in both directions. Assigning a multi-cell range’s Value property to a Variant variable loads the range into a two-dimensional array. This macro reads the quantity column from the sample output and totals it in memory:

Sub SumQuantitiesFromRange()
    Dim ws As Worksheet
    Dim values As Variant
    Dim r As Long
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Read three worksheet rows and two columns into a 2D Variant array.
    values = ws.Range("A2:B4").Value

    ' The quantity is in the second column of the selected range.
    For r = LBound(values, 1) To UBound(values, 1)
        total = total + values(r, 2)
    Next r

    Debug.Print total
End Sub

For the example data, the array contains the values from A2:B4, and the second selected column contains 4, 10, and 3. The result printed in the Visual Basic Editor’s Immediate window is 17. The loop uses LBound and UBound to find the first and last row indexes rather than assuming a particular range size. For a multi-cell range, Excel returns a two-dimensional array even when the range has only one row or one column. A single-cell range is an exception: its Value is a scalar, not a two-dimensional array.

Choose between an array and direct range operations

Use an array when a macro must process a substantial block of data, perform several calculations or checks, and then write results back. Reading a range into memory, working on the array, and writing a result range in a single assignment can avoid repeated interaction with worksheet cells. The benefit is most noticeable for many rows or repeated operations.

For a small, simple task, direct range code may be easier to understand. Setting one cell, applying a format, or using a worksheet formula does not automatically call for an array. Arrays also require attention to dimensions and indexes, so they can add unnecessary complexity to a short macro. A practical approach is to use arrays for bulk data handling and worksheet ranges for tasks that are more naturally expressed as cell formatting, formulas, or a few isolated edits.

One common misconception is that an array is the same thing as a worksheet range. It is not: a range is an Excel object on a sheet, while an array is data held in VBA memory. Assigning an array to a range copies values into cells; it does not create a permanent link between the array and those cells. If the worksheet changes later, the in-memory array does not change unless the macro reads the range again.

Common array problems and fixes

  • Array and range dimensions do not match. When writing a two-dimensional array, make the destination range the same number of rows and columns. In the example, UBound(data, 1) gives the last row index and UBound(data, 2) gives the last column index because both dimensions start at 1.
  • Using the wrong dimension in a loop. LBound(array, 1) and UBound(array, 1) refer to rows in a row-column layout; dimension 2 refers to columns. Check which index you are looping over before reading or writing an element.
  • Assuming every range read returns an array. A single-cell range returns one value. If code must handle both one cell and multiple cells, branch on the range size or ensure the source range has more than one cell.
  • Reading text or blanks as numbers. A worksheet range can contain empty cells, text, error values, or numbers stored as text. Validate the data before arithmetic, and consider VBA’s IsNumeric function when the input is not guaranteed to be numeric. A Variant array can hold mixed values, but that does not make every operation valid for every item.
  • Using a sheet name that does not exist. The example’s worksheet reference must match a tab in the workbook containing the code. For a macro stored in a different workbook, ThisWorkbook still means the workbook containing the code; ActiveWorkbook refers to the workbook currently active in Excel, which may not be the intended target.

Also avoid assuming that indexes always start at zero. VBA’s default lower bound is usually zero unless a different base is specified, while arrays returned from worksheet ranges are typically indexed from 1. Explicit bounds, as used in the writing example, and LBound/UBound checks make array code more reliable.

Performance, compatibility, and safety

For a large block, one range-to-array read and one array-to-range write are generally preferable to reading or writing every cell individually in a loop. The worksheet still has to display and store the results, but reducing repeated cell access can make bulk VBA routines more efficient. For especially large jobs, additional choices—such as calculation mode or screen updating—may matter, but they should be changed carefully and restored to their previous settings when the macro finishes.

VBA arrays work in desktop Excel for Microsoft 365 on Windows and in desktop Excel for Mac, though some user-interface details and keyboard shortcuts differ. They are not a way to run VBA in Excel for the web. If a workbook must be used in a browser without desktop Excel, consider whether worksheet formulas, Power Query, or Office Scripts meet the need; these are separate tools with different capabilities and are not VBA arrays.

Macros can change or overwrite workbook contents, and Ctrl+Z cannot reverse those changes. Keep a backup before running code, confirm the target workbook and sheet, and only enable macros from sources you trust. Do not lower macro security globally just to run an unfamiliar file. Saving as .xlsm preserves VBA code; saving as a macro-free .xlsx file removes the VBA project.

Frequently Asked Questions

How do I write an array to Excel cells? Create a two-dimensional array whose dimensions match the output table, then assign it to a range of the same size. A pattern such as ws.Range("A1").Resize(rowCount, columnCount).Value = data writes the values in one assignment when data is a matching two-dimensional array.

How do I find the size of a VBA array? Use UBound(array, dimension) for the upper index and LBound(array, dimension) for the lower index. Subtract the lower bound from the upper bound and add one to get the number of elements in that dimension.

Can a VBA array contain text and numbers? Yes. A Variant array can contain different value types, which is why it is commonly used for mixed worksheet tables. An array declared as a specific type, such as Long, is intended for values of that type and is not suitable for arbitrary text in the same elements.

Why does assigning a one-dimensional array to a range fail? A multi-cell range assignment normally expects a two-dimensional array arranged as rows and columns. A one-dimensional list does not have that same shape. For worksheet tables, build a two-dimensional array; for a single row or column, make sure the array shape and destination range are compatible.

Can I run this VBA array example in Excel for the web? No. Excel for the web can store and open macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel for Windows or Mac to edit and run the code.

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.