An Excel VBA array is a variable that holds multiple related values, such as a list of names or a block of worksheet cells. In VBA, declare an array, fill or read its elements, and use loops or array operations to process them; for example, assigning a multi-cell range’s Value2 property to a Variant variable loads the range into a two-dimensional array.
What an array means in Excel VBA
A normal VBA variable holds one value at a time. An array groups values under one variable name and lets your code refer to each value by an index. Instead of creating separate variables such as name1, name2, and name3, you can store the names in names(1), names(2), and names(3). This is useful when the number of items is large or when a loop needs to process every item in the same way.
Arrays are held in memory while the macro runs. They can contain values read from a worksheet, values created by the code, or both. An array is not itself a worksheet range: changing an array does not change cells until the code writes the array back to a range.
Array types, dimensions, and indexes
A one-dimensional array is like a list. A two-dimensional array is like a table with rows and columns. VBA can also work with arrays that have more dimensions, although tables and lists are the common cases in Excel macros.
Arrays can be fixed-size or dynamic. A fixed-size array has its bounds declared in its declaration. A dynamic array is declared without bounds and sized later with ReDim. For example, Dim scores(1 To 5) As Double declares five numeric elements, while Dim scores() As Double declares a dynamic array that must be sized before use.
VBA arrays can start at zero or one, depending on how they are declared and the module’s Option Base setting. Explicit bounds make code easier to understand. Use LBound(arrayName, dimension) and UBound(arrayName, dimension) to find the first and last indexes rather than assuming them. For a two-dimensional array, the dimension argument distinguishes row bounds from column bounds.
- One-dimensional: use one index, such as
items(i). - Two-dimensional: use a row and column index, such as
data(r, c). - Dynamic: use
ReDimto set its size after declaring it.
Reading and writing worksheet ranges
For many spreadsheet tasks, the most useful array technique is moving a block of cells between a worksheet and memory. With a multi-cell range, assigning Range.Value or Range.Value2 to a Variant variable returns a two-dimensional array. Its first dimension corresponds to worksheet rows and its second to columns. The array’s bounds reflect the range’s positions within the returned data, not the worksheet’s row and column numbers.
For example, if the code reads A2:C4, the array contains three rows and three columns, accessed as data(1, 1) through data(3, 3). The first value is from A2, and the value in the third row and second column is from B4. A common surprise is that reading a single cell returns a scalar value rather than a two-dimensional array. Code that might read either one cell or several should account for that difference.
To write an array to the sheet, assign it to a destination range with matching dimensions. A two-dimensional array with three rows and one column should be assigned to a three-row, one-column range. If the destination is too small or its dimensions do not match, the assignment can fail. When generating output, it is often clearest to build a two-dimensional array explicitly and use Resize to size the destination range.
Example: process a range in an array
This procedure reads three columns from rows 2 through 4 on a worksheet named Sheet1. It combines the value in column A with the value in column C, then writes the three results to column E. The example assumes that the worksheet exists and that the source range contains the intended records.
Sub MakeLabelsInMemory()
Dim ws As Worksheet
Dim sourceData As Variant
Dim outputData() As Variant
Dim r As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
sourceData = ws.Range("A2:C4").Value2
ReDim outputData(1 To UBound(sourceData, 1), 1 To 1)
For r = 1 To UBound(sourceData, 1)
outputData(r, 1) = sourceData(r, 1) & ": " & sourceData(r, 3)
Next r
ws.Range("E2").Resize(UBound(outputData, 1), 1).Value = outputData
End Sub
Here, the first dimension of sourceData is the row number within the loaded block; the second is its column number. The loop uses UBound(sourceData, 1) so it follows the number of rows in the array rather than relying on a hard-coded loop limit. The output is also a two-dimensional array, even though it has only one column, which makes it suitable for writing to a vertical range.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. On Mac, the editor shortcut can be Option+F11 or Fn+Option+F11 depending on the keyboard; Excel for the web does not run VBA macros.
Resizing dynamic arrays
Use ReDim when a dynamic array needs a specific size. If the number of items is not known until the macro runs, calculate the size first or use a collection while gathering the items, then transfer them into an array when appropriate. Repeatedly resizing an array inside a long loop can be inefficient.
ReDim Preserve keeps existing values while changing an array’s bounds, but it has an important limitation: for a multidimensional array, VBA can resize only the last dimension while preserving values. For example, code can preserve a two-dimensional array while changing its column bound, but cannot use ReDim Preserve to change its row bound. If the required resizing pattern does not fit that limitation, store values in another structure first or create a new array and copy the required elements.
Do not use ReDim casually on an array whose existing contents matter. A plain ReDim initializes the resized array again and discards its prior contents; use Preserve when keeping values is required and the resize is allowed.
When arrays are a good choice
Arrays are especially useful when a macro needs to read many cells, perform repeated calculations, and write results back in a block. A typical pattern is to read a range once, loop over the in-memory data, and write the result once. This avoids making a separate worksheet request for every cell and is generally more efficient for larger datasets than repeatedly reading and writing individual cells.
Arrays also help separate the data-processing logic from the worksheet layout. A procedure can calculate results in memory and then place them in a destination range. For small tasks involving only a few cells, direct cell references may be simpler and easier to maintain. Choose the approach that makes the code clearest, while avoiding unnecessary cell-by-cell operations in a large loop.
Use an explicit data type, such as Long for whole-number indexes or Double for decimal values, when the array’s contents have a known type. A Variant array is convenient for data read from a range because worksheet cells may contain different kinds of values, including text, numbers, dates, and empty cells. That flexibility also means code should account for unexpected or blank cell contents before applying operations that require a particular type.
Common errors and how to avoid them
- Subscript out of range: the code requested an index outside the array’s bounds. Check the actual bounds with
LBoundandUBound, and remember that the first item is not always index zero. - Wrong dimension: a range loaded from multiple cells is normally a two-dimensional array. Use both row and column indexes, and specify the correct dimension when calling
UBound. - Single-cell input: a one-cell range returns a scalar, not the same array shape as a multi-cell range. Add a branch or normalize the input if the range size can vary down to one cell.
- Output size mismatch: make the destination range the same number of rows and columns as the array.
Resizecan help size the destination from the array bounds. - Blank or error values: worksheet data may contain empty cells or Excel error values. Check values before treating them as text or numbers, especially if the macro performs arithmetic or concatenation.
For array loops, a numeric For loop gives direct access to the index and is useful when the code needs row and column positions. A For Each loop can iterate through array elements too, but its control variable should be a Variant for a general array. It is less convenient when the code needs to know each element’s index.
Arrays compared with ranges and other structures
A Range object represents cells in a worksheet; an array represents values in memory. Use a range when the task concerns worksheet formatting, formulas, or cell addresses. Use an array when the main task is processing values. The two work together: read a range into an array, process it, then write results back to a range.
A Collection is useful when code needs a resizable group of items that can be added or removed one at a time. A Dictionary is useful for key-value lookup, such as finding a product’s price by product code; the Dictionary object is provided by the Microsoft Scripting Runtime or can be created with late binding. Arrays are often simpler when the data is an ordered block and the number of elements is known or can be sized in advance. These structures solve related problems, but they are not interchangeable in every situation.
A VBA array is also different from a dynamic array formula such as a formula that spills multiple results into worksheet cells. VBA arrays exist in macro code and require a macro to write values to the sheet. Dynamic array formulas are worksheet formulas and recalculate under Excel’s calculation system. Excel for the web supports many worksheet features but does not execute VBA.
Frequently Asked Questions
How do I declare an array in Excel VBA?
For a fixed array, declare its bounds and type, such as Dim values(1 To 10) As Double. For a dynamic array, use empty parentheses, such as Dim values() As Double, and set its bounds later with ReDim.
How do I put an Excel range into a VBA array?
Assign a multi-cell range’s Value2 property to a Variant variable. For example, a range covering several rows and columns becomes a two-dimensional array. Handle a one-cell range separately because it returns a single value.
Are VBA arrays zero-based or one-based?
They can be either. The lower bound depends on the declaration and, where bounds are omitted, the module’s Option Base setting. Use explicit bounds or check them with LBound to avoid assumptions.
Can VBA write an array to worksheet cells?
Yes. Assign the array to a range with matching dimensions. A two-dimensional array is the usual shape for a rectangular block of cells, including a single-column output array.
Can Excel for the web run VBA arrays or macros?
No. Excel for the web does not run VBA macros. Use the desktop version of Excel to execute VBA; workbook data and formulas can still be accessed in the web app, but VBA code will not run there.
Related
- How to Run Scenario Manager in Excel
- How to Run Goal Seek in Excel to Find a Target Value
- How to Run Goal Seek Automatically in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macro Converter: What It Does and LimitsA Google Sheets macro converter can help translate some Excel VBA macros into Google Apps Script, but it…
- Excel VBA Array Example: Create, Fill, and Write DataA simple Excel VBA array example is to store values in a two-dimensional array, then write the array…
- Excel VBA Function Not Showing Up? Fixes That WorkIf an Excel VBA function is not showing up, first check that it is a public function in…
- Excel Macro to Convert Text to Columns: VBA Guide and TipsYes. In Excel for Microsoft 365 on Windows, a VBA macro can convert text in a selected column…
- Excel UserForm Grid Size: Spacing, Settings, and LayoutIn Excel VBA, a UserForm’s design grid is adjustable in the Visual Basic Editor; it does not have…
- VBA Turn Off AutoFilter in Excel Without Losing DataTo turn off a worksheet AutoFilter in VBA, first show all rows if a filter is active, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.