An Excel VBA array is a variable that holds multiple values under one name, with each value accessed by an index. Declare a fixed array with a statement such as Dim scores(1 To 5) As Long; use a dynamic array, declared as Dim scores() As Long, when its size will be set or changed while the macro runs.
What an array means in VBA
Ordinary VBA variables usually hold one value at a time. An array groups related values of the same declared type, or of mixed types if it is a Variant array, so code can process a collection with a loop. Instead of creating separate variables for five scores, for example, a macro can store them in one array and refer to each value by its position.
An array is not the same thing as an Excel worksheet range, even though the two work well together. A range is a set of cells in a workbook; an array is data held in memory while VBA runs. A macro can read a range into an array, work with the values, and write results back to cells. That approach can be much faster than repeatedly reading and writing individual cells in a large loop.
Array indexes are called subscripts. An array declared with bounds from 1 to 5 has five elements, addressed as scores(1), scores(2), and so on. The bounds are part of the array’s definition; they do not necessarily match worksheet row numbers or column numbers.
Declaring a fixed-size array
A fixed-size array has bounds specified in its declaration. For example, this procedure stores five whole-number values and displays the third value:
Option Explicit
Sub FixedArrayExample()
Dim scores(1 To 5) As Long
Dim i As Long
' Fill each element with a multiple of 10.
For i = LBound(scores) To UBound(scores)
scores(i) = i * 10
Next i
MsgBox scores(3)
End Sub
The message box displays 30 because the third element is assigned the value 3 × 10. Long is a common type for whole numbers in VBA. Other useful element types include String for text, Double for decimal numbers, Date for dates and times, and Variant when elements may need to hold different kinds of values.
If you declare Dim scores(5) As Long without writing a lower bound, the usual default lower bound is zero: the indexes are 0 through 5, making six elements. A module-level Option Base 1 changes the default for declarations without explicit lower bounds in that module. To avoid ambiguity, especially in shared code, specify both bounds explicitly when the intended range matters.
Dynamic arrays and ReDim
A dynamic array leaves the bounds out of its initial declaration. VBA allocates its storage later, when the procedure knows how many elements are needed. The following example starts with three elements and expands the array to five:
Option Explicit
Sub DynamicArrayExample()
Dim items() As String
Dim i As Long
' Allocate three elements, indexed from 1 to 3.
ReDim items(1 To 3)
items(1) = "Red"
items(2) = "Green"
items(3) = "Blue"
' Preserve existing values while increasing the upper bound.
ReDim Preserve items(1 To 5)
items(4) = "Gold"
items(5) = "Silver"
For i = LBound(items) To UBound(items)
Debug.Print items(i)
Next i
End Sub
ReDim allocates or reallocates a dynamic array. Without Preserve, resizing discards the values already stored in the array. With Preserve, VBA keeps the existing elements, but there is an important limit: for a multidimensional array, only the upper bound of the last dimension can be changed while preserving the data. A dynamic array can also be released with Erase; for a dynamic array, that deallocates its storage.
Use a fixed-size array when the required number of elements is known and will not change. Use a dynamic array when the number of records comes from a range, a file, or some other value determined at run time. Dynamic does not mean that VBA automatically grows the array: your code must use ReDim to allocate or resize it.
Finding array bounds and looping safely
LBound returns an array’s lower bound, and UBound returns its upper bound. These functions let a loop work with the actual indexes rather than assuming that an array begins at zero or one. This matters when arrays come from another procedure, use a different base, or have been resized.
For a one-dimensional array, use LBound(arrayName) and UBound(arrayName). For a multidimensional array, include the dimension number: LBound(data, 1) checks the first dimension and UBound(data, 2) checks the second. Asking for a dimension that does not exist causes an error, so use the correct dimension count.
There is no requirement that an index represent a row number. If an array’s lower bound is 1, its first element is at index 1 regardless of whether the related worksheet data begins in row 2, row 10, or another location. Keep that distinction in mind when translating between array positions and worksheet cells.
Multidimensional arrays
A multidimensional array uses more than one index to represent a table-like structure. This declaration creates a two-dimensional array with three rows and four columns:
Dim grid(1 To 3, 1 To 4) As Double
The first index identifies a position in the first dimension, and the second identifies a position in the second. For instance, grid(2, 3) refers to the element at position 2 in the first dimension and position 3 in the second. In Excel work, two-dimensional arrays are especially useful because a rectangular cell range can be read and written as a block.
When code loops through a two-dimensional array, it is often clearest to use a nested loop: the outer loop visits one dimension and the inner loop visits the other. Check both dimensions with LBound and UBound, rather than assuming a particular size. That makes the code easier to adapt if the source range changes.
Using arrays with worksheet ranges
For a range containing multiple cells, assigning its Value property to a Variant variable creates a two-dimensional Variant array. Its first dimension corresponds to worksheet rows and its second to columns; the indexes for this range-based array are one-based. With data in A2:C6 on Sheet1, the first array element corresponds to A2, and data(1, 3) corresponds to C2.
Option Explicit
Sub ReadRangeIntoArray()
Dim data As Variant
Dim i As Long
' Read the five-row, three-column block into memory.
data = ThisWorkbook.Worksheets("Sheet1").Range("A2:C6").Value
' Print the first and third columns for each row.
For i = LBound(data, 1) To UBound(data, 1)
Debug.Print data(i, 1), data(i, 3)
Next i
End Sub
This example reads the range once, then works with the stored values. It prints values from columns A and C to the Immediate window in the VBA editor; it does not change the worksheet. To write a same-sized two-dimensional array back to a matching rectangular range, assign the array to that range’s Value property. The dimensions must fit the destination range.
A common edge case is a range containing only one cell. In that case, reading its Value returns a single value, not a two-dimensional array. Code that expects two indexes can therefore fail if the selected range sometimes shrinks to one cell. Handle the single-cell case separately, or ensure that the range always contains multiple cells.
Choosing an array type and avoiding common errors
Choose the element type according to the data and the operations the macro performs. A typed array, such as Long() or String(), makes the intended contents clear and can prevent unsuitable values from being assigned. A Variant array is convenient for mixed data and for receiving a multi-cell range, but each element can hold different kinds of values. In workbook automation, a Variant variable is often the practical choice for a range read, while a typed array may suit a well-defined calculation.
- Subscript out of range: the code refers to an index outside the array’s bounds. Check the declared bounds or use
LBoundandUBoundin the loop. - Array not dimensioned: a dynamic array was used before storage was allocated with
ReDim, or it was erased. Allocate it before accessing an element. - Values disappeared after resizing:
ReDimwithoutPreservediscards existing values. UseReDim Preservewhen its restrictions fit the task. - Type mismatch: a value cannot be stored in the declared element type, or code treats a single-cell range result as an array.
- Wrong dimension used: a two-dimensional array needs two indexes for an element. Use the right dimension number when checking its bounds.
Arrays and Excel’s dynamic-array worksheet formulas are separate features. A VBA array is data used by a macro; worksheet formulas such as FILTER or UNIQUE spill results into cells and do not require a VBA declaration. If the task is simply to calculate or display a list with a worksheet formula, a macro may not be necessary.
Running and saving VBA code safely
VBA runs in the desktop Excel application, not Excel for the web. On Windows, open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 is commonly used; depending on keyboard settings, you may also need to hold Fn. You can place a procedure in a standard module in the editor and run it from Excel’s macro controls or from the editor.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain its macros. A standard .xlsx workbook does not store VBA project code. Macro security settings may also prevent code from running until the workbook or its macros are permitted under your organization’s security rules; do not enable macros in an untrusted file.
Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a macro that writes values to cells can overwrite existing content. A copy gives you a way to restore the original if the code produces an unexpected result.
Frequently Asked Questions
What is the simplest way to define an array in Excel VBA?
For five whole numbers, write Dim values(1 To 5) As Long. This declares a fixed array with indexes 1 through 5. Use explicit bounds to make the intended indexes clear.
Does a VBA array start at 0 or 1?
It depends on how its bounds are declared and, for declarations without an explicit lower bound, on the module’s Option Base setting. An array declared as Dim values(1 To 5) As Long always uses indexes 1 through 5. Check with LBound and UBound when the bounds are not obvious.
How do I resize an array in VBA?
Declare a dynamic array, such as Dim values() As Long, then allocate it with ReDim values(1 To 10). Use ReDim Preserve to retain existing values when resizing, bearing in mind that for a multidimensional array only the last dimension’s upper bound can be changed this way.
How do I put an Excel range into an array?
Assign a multi-cell range’s Value to a Variant variable, for example data = Worksheets("Sheet1").Range("A2:C6").Value. The result is a two-dimensional array with row and column indexes. A one-cell range returns a single value instead.
Can I use VBA arrays in Excel for the web?
You can open a macro-enabled workbook there, but Excel for the web cannot run, create, or edit VBA macros. Use desktop Excel for Windows or Mac to work with VBA code.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Pivot Table Formatting: A Practical GuideTo format an existing Excel PivotTable with VBA, set its TableStyle2 property for an overall design, then set…
- How to Assign a Macro to a Toggle Button in Excel 365In Excel for Microsoft 365 on Windows, an ActiveX Toggle Button runs VBA from its Click event: insert…
- VBA PivotTable Range: Source, Output, and ExamplesIn VBA, a PivotTable’s source range is the worksheet data used to build or refresh it, while its…
- Excel VBA Format Worksheet: Examples and Key OptionsTo format a worksheet with Excel VBA, reference the worksheet and set properties such as Font, Interior, NumberFormat,…
- Excel VBA Function Return Value: Syntax and ExamplesAn Excel VBA function returns a value by assigning that value to the function’s own name before the…
- Excel VBA: Loop Through Files in a Folder with DirIn Excel VBA, use the Dir function to find files that match a folder path and filename pattern,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.