Excel Macro Arrays: How VBA Arrays Work

VBA & macros · Updated November 11, 2026

An Excel macro array is a VBA variable that holds multiple values, so a macro can work with a collection of data in memory instead of reading or writing one worksheet cell at a time. In Excel for Microsoft 365 on Windows, a common pattern is to load a range into a Variant array, process its rows in VBA, and write the results back to the sheet in one operation.

What “Excel macro array” means

The phrase usually refers to an array in an Excel macro written with Visual Basic for Applications (VBA). An array is a variable with multiple numbered positions, called elements. For example, a one-dimensional array could hold five department names, while a two-dimensional array could represent rows and columns of a worksheet table.

Arrays are not a separate Excel worksheet feature, and they are not the same thing as an array formula. A VBA array exists while the macro runs; it is stored in the macro’s working memory. By contrast, a worksheet formula such as =FILTER(A2:B20,B2:B20>0) calculates in cells and may spill results into adjacent cells. The two ideas can be used in the same workbook, but they are managed differently.

Arrays are useful when a macro needs to inspect, transform, sort, or calculate across many values. They can also reduce the repeated communication between VBA and the worksheet, which is often a major source of delay in macros that handle large ranges.

Why macros use arrays

A macro can read a cell, change it, and move to the next cell. That approach is easy to understand, but it can be slow when repeated thousands of times. Instead, VBA can copy a rectangular range into an array, make changes to the in-memory values, and place the finished array back into a matching range.

For example, consider 20,000 rows with a product name in column A and a quantity in column B. A macro could retrieve A2:B20001 once, inspect the values in memory, calculate a result for every row, then write a results array to column C in one assignment. This can be substantially faster than accessing the worksheet separately for each row.

  • Speed: Fewer worksheet read and write operations can improve performance on large datasets.
  • Control: A macro can apply several transformations before writing anything back to the workbook.
  • Convenience: A two-dimensional array follows the rows-and-columns shape of a table.
  • Safer staging: The macro can calculate results in memory before replacing or adding worksheet values.

Arrays do not automatically make every macro faster or simpler. For a few cells, direct references may be clearer. The best choice depends on the amount of data and the work being done.

How a range becomes an array

When VBA assigns the value of a multi-cell range to a Variant variable, Excel returns a two-dimensional array. Its first dimension represents the rows and its second dimension represents the columns. For instance, if a macro reads A2:B4, the data is addressed as row 1, column 1 through row 3, column 2 inside the array. Those array indexes are relative to the selected range: the first array row corresponds to worksheet row 2, not worksheet row 1.

A Variant is commonly used for this operation because the range may contain different kinds of values, such as text, numbers, dates, and blanks. In this context, the Variant variable holds the array returned by Excel. VBA can inspect an array’s limits with LBound and UBound, which return its lowest and highest index in a specified dimension.

A one-cell range is an important exception: its .Value is a single value rather than a two-dimensional array. A macro that may receive either one cell or several should account for that case rather than assume every range produces an array. Similarly, a range containing an Excel error value can place an error value in the array; code that performs arithmetic should handle that possibility.

Practical example: calculate with a range array

This macro reads item names and quantities from A2:B4 on a sheet named Sheet1, doubles each quantity, and writes the calculated values to column C. It assumes the quantity cells contain numbers and that the worksheet has those exact columns and rows. The output is a three-row, one-column array, assigned to C2:C4 in one operation.

Option Explicit

Sub DoubleQuantities()
    Dim ws As Worksheet
    Dim sourceData As Variant
    Dim outputData() As Variant
    Dim i As Long

    ' Read item names and quantities from the worksheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    sourceData = ws.Range("A2:B4").Value

    ' Prepare one output value for each input row.
    ReDim outputData(1 To UBound(sourceData, 1), 1 To 1)

    ' The second array column contains the quantities.
    For i = LBound(sourceData, 1) To UBound(sourceData, 1)
        outputData(i, 1) = sourceData(i, 2) * 2
    Next i

    ' Write all calculated values to C2:C4 at once.
    ws.Range("C2").Resize(UBound(outputData, 1), 1).Value = outputData
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so the usual Excel undo history will not reverse the values this example writes. To try the code, open the VBA editor with Alt+F11 on Windows, insert a standard module, and paste the code. You can run a macro from the editor or use Alt+F8 in Excel to open the Macro dialog. On a Mac, Option+F11 and Option+F8 are common equivalents; some keyboards require holding Fn as well.

For this example, if B2:B4 contains 4, 7, and 10, the macro writes 8, 14, and 20 to C2:C4. The source range is deliberately small to make the indexing visible. For a changing data size, determine the last populated row and build the source and destination ranges from that row, while also handling the case where there are no data rows.

Fixed, dynamic, and multidimensional arrays

VBA offers more than one way to declare an array. A fixed-size array has its capacity stated in its declaration, for example Dim scores(1 To 5) As Double. Its size is known when the procedure is written. Fixed arrays suit a small, predictable set of values, but they are less convenient when the amount of worksheet data changes.

A dynamic array is declared without its final size, then allocated with ReDim. The example uses ReDim outputData(...) after it has read the source range, so the output can match the number of rows. If a dynamic array needs to grow while preserving existing values, VBA supports ReDim Preserve with a limitation: it can change only the last dimension of a multidimensional array. This matters when designing arrays that grow in both directions.

A one-dimensional array uses one index, such as names(1). A two-dimensional array uses two, such as sourceData(2, 1). A range read from Excel naturally produces a two-dimensional array even if it has only one column or one row, provided it contains more than one cell. VBA’s Option Base can affect the default lower bound for some declared arrays, but it does not change the bounds of an array returned from a worksheet range. Using LBound and UBound avoids assumptions about limits.

VBA arrays versus worksheet array formulas

These features solve related but different problems. A VBA array is suited to a programmed task: a macro can read a table, apply a procedure, and write values or results. A worksheet array formula is a formula entered in a cell. In current Microsoft 365 Excel, dynamic-array functions such as FILTER, SORT, and UNIQUE can return multiple results that spill into neighboring cells when there is room.

Choose a worksheet formula when the result should update as source cells change and the calculation can be expressed with formulas. Choose VBA when the task involves a sequence of actions, custom logic, or changes that should be made by an explicitly run macro. A macro’s written values do not automatically recalculate just because its original input changes; the macro must be run again unless the workbook is designed to run it in response to an event.

Excel for the web can display and edit many workbooks containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for those tasks. Excel for Mac supports VBA, although some Windows-specific components and APIs are not available, so code that depends on them may need adjustment.

Common array problems and how to avoid them

Many array errors come from mismatched dimensions or mistaken assumptions about the data. If the source has 100 rows but the destination array has 99, assigning the result may fail or produce an incomplete outcome. Build output dimensions from the source array and check that the destination range has the same number of rows and columns.

  • Subscript out of range: The code tried to use an index outside an array’s bounds. Check the limits with LBound and UBound, and remember that an array index is not necessarily the worksheet row number.
  • One cell instead of an array: A one-cell range returns a scalar value. Add logic for one-cell input if the selected range can shrink to that size.
  • Type mismatch or arithmetic error: A supposed number may be blank, text, or an Excel error. Validate the value before doing arithmetic; a Variant can contain different data types.
  • Unexpected overwrite: Writing an array to a worksheet range replaces the target cells’ values. Confirm the destination and save a backup before running a macro.
  • Unexpected lower bound: Do not assume every array starts at zero or one. Use the actual bounds, particularly when working with arrays created in different ways.

When debugging, set a breakpoint in the VBA editor and inspect the array in the Locals window or with the Immediate window. Testing on a short sample range first makes it easier to verify row and column indexes before processing a full dataset.

When an array is the right tool

Use a range array when a macro processes a meaningful number of cells and can do most of its work without continually interacting with the worksheet. It is especially helpful for repetitive calculations, cleaning imported data, checking rows against rules, or preparing output tables. Keep the process understandable: name variables according to their purpose, use Option Explicit so VBA requires variable declarations, and separate reading, processing, and writing where that improves clarity.

For a small one-off change, a direct range assignment may be simpler. For a calculation that should remain live and visible to workbook users, a formula may be better. For automation that must run in a browser, VBA is not suitable in Excel for the web; an Office Script may be an alternative, depending on the task and the user’s Microsoft 365 setup. The key distinction is whether the job is a stored calculation, a desktop macro procedure, or browser-based automation.

Frequently Asked Questions

What is an array in an Excel macro?
An array is a VBA variable that stores multiple values under indexed positions. A macro can process those values in memory and, commonly, transfer them to or from a worksheet range.

How do I put worksheet data into a VBA array?
Assign a multi-cell range’s .Value to a Variant variable, such as data = Worksheets("Sheet1").Range("A2:B10").Value. The result is a two-dimensional array corresponding to the range’s rows and columns.

Are Excel macro arrays the same as array formulas?
No. A VBA array is held and used by macro code. An array formula is a worksheet formula; in Microsoft 365, some functions return results that spill into neighboring cells.

Why is my VBA array subscript out of range?
The code likely refers to an element outside the array’s valid bounds, or confuses a worksheet row number with an array index. Check each dimension with LBound and UBound.

Can Excel for the web run VBA array macros?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run VBA; the Mac desktop app supports VBA but may not support Windows-only dependencies.

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.