In Excel VBA, declare a dynamic array with empty parentheses, then allocate it with ReDim; for example, use Dim values() As Long followed by ReDim values(1 To 10). Use a fixed-size declaration when the number of elements is known in advance, and use explicit lower and upper bounds when you need predictable indexes.
What array allocation means in VBA
An array stores multiple values of the same declared type under one variable name, with each value accessed by an index. Allocation is the step that gives a dynamic array its dimensions and reserves storage for its elements. Declaring a dynamic array alone does not give it usable indexes: before reading or writing an element, the code must allocate it.
VBA distinguishes between fixed-size arrays and dynamic arrays:
- A fixed-size array has its bounds in the declaration, such as
Dim labels(1 To 8) As String. Its size is set when the procedure or module is initialized. - A dynamic array is declared with empty parentheses, such as
Dim labels() As String, and receives its bounds later withReDim.
Each element in a typed array starts with that type’s default value: for example, numeric values start at zero and strings start as empty strings. A Variant array can hold values of different kinds, but it generally uses more memory than an array of a specific type. Prefer a specific type such as Long, Double, or String when the data has a consistent type.
Allocate a dynamic array with ReDim
Use ReDim when the required number of elements is known only while the procedure runs. An explicit lower bound and upper bound make the number of elements clear: an array from 1 through 10 contains 10 elements. By contrast, an array from 0 through 10 contains 11 elements.
Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The following procedure allocates five elements, assigns values, and prints them in the Immediate window of the VBA editor.
Sub AllocateAndFill()
Dim values() As Long
Dim i As Long
' Allocate five elements with indexes 1 through 5.
ReDim values(1 To 5)
' Fill and display each element.
For i = LBound(values) To UBound(values)
values(i) = i * 10
Debug.Print values(i)
Next i
End Sub
Here LBound returns the lowest valid index and UBound returns the highest. Using those functions in loops is safer than assuming the array starts at zero or one. In the example, the Immediate window displays 10, 20, 30, 40, and 50.
Choose fixed or dynamic sizing
A fixed-size array is convenient when the required size is part of the design and will not change. It avoids a separate allocation statement and can make a small routine easier to read. For example, a procedure that always records the 12 months of a year could declare 12 elements directly.
A dynamic array is usually the better fit when the size comes from a worksheet range, a user selection, or a calculation. It lets code allocate exactly the needed capacity after measuring the input. Common choices include:
- Use a fixed-size array for a stable, known count, such as seven weekday values.
- Use a dynamic array when the number of records varies from one workbook run to another.
- Use a typed array for uniform data, such as
LongIDs orDoublemeasurements. - Use a Variant array when values may have mixed types or when storing a range’s
Valueresult.
Do not choose a very large fixed array simply to avoid resizing unless the memory and bounds are justified. An oversized array makes the intended data range less clear, while repeated resizing can also be inefficient. Select a strategy based on how the count is known and how the data will be used.
Set array bounds and avoid indexing errors
VBA’s default lower bound is usually zero, but a module can use Option Base 1 to change the default for declarations or dimensions that omit a lower bound. The exact lower bound can therefore be easy to overlook when code uses a single number inside the parentheses. For predictable behavior, specify both bounds, as in ReDim items(1 To count).
For an array with a lower bound of 1, the first element is items(1); with a lower bound of 0, it is items(0). An index outside the allocated bounds causes a subscript-out-of-range error. Loop from LBound(items) to UBound(items) when the bounds may vary, and check that a calculated index is within those limits before using it.
Calling LBound or UBound on a dynamic array that has not yet been allocated can itself raise an error. Do not treat a successful declaration as proof that the array has usable bounds. Allocate it first, or track its allocated state in the surrounding code. A Variant containing an array can be checked with IsArray, but that check alone does not establish that a particular dimension has been allocated with usable bounds.
Allocate two-dimensional arrays
Arrays can have more than one dimension. A two-dimensional array is useful for rows and columns, for example when preparing a rectangular block of worksheet data. Supply a lower and upper bound for each dimension in the same ReDim statement.
Sub AllocateGrid()
Dim grid() As Variant
Dim rowIndex As Long
Dim columnIndex As Long
' Allocate three rows and two columns.
ReDim grid(1 To 3, 1 To 2)
' Store a value at every row-column position.
For rowIndex = LBound(grid, 1) To UBound(grid, 1)
For columnIndex = LBound(grid, 2) To UBound(grid, 2)
grid(rowIndex, columnIndex) = rowIndex * 10 + columnIndex
Next columnIndex
Next rowIndex
End Sub
The optional dimension argument to LBound and UBound identifies which dimension to inspect: 1 is the first dimension and 2 is the second. This example creates six values. Keep the meaning and order of dimensions consistent throughout the procedure; swapping row and column indexes can put data in the wrong worksheet positions even when the array bounds themselves are valid.
Resize an array and preserve its contents
Running ReDim on an already allocated dynamic array normally creates the requested dimensions again and discards the existing contents. To retain values while changing the size, use ReDim Preserve. For a one-dimensional array, this is a typical way to expand its upper bound without losing earlier elements.
There is an important limitation: ReDim Preserve can change only the upper bound of the last dimension. It cannot change the lower bound, and for a multidimensional array it cannot resize an earlier dimension while preserving the contents. If a two-dimensional array needs more rows, consider allocating a larger array and copying values, or using a different data structure.
Preserving data can involve copying it into new storage, so repeatedly growing an array by one element may be slow when a procedure processes many records. If the approximate or exact count is available, allocate once to that size. If it is not, grow the capacity in larger steps and keep a separate count of how many positions contain actual data. That approach reduces the number of resize operations, though it requires code to distinguish used elements from spare capacity.
Use arrays with worksheet ranges
VBA arrays are useful for transferring worksheet data in batches. Assigning the Value property of a multi-cell rectangular range to a Variant stores the result as a two-dimensional, one-based array. A single-cell range is different: its value is a scalar, not a two-dimensional array. Code that may receive either one cell or several should account for that distinction rather than assuming every result supports two indexes.
For a rectangular output range with the same number of rows and columns as a two-dimensional array, assigning the array to the range’s Value property writes the values as a block. This is often simpler and faster than writing each cell in a loop. The source and destination dimensions should match; otherwise, the assignment may not produce the intended layout. When working with a one-dimensional array, reshape or copy the values into a two-dimensional structure if a row or column range requires it.
For example, with data in cells A1:C3 on Sheet1, a Variant can receive the range’s values and be processed by row and column. The range must contain multiple cells for the result to be a two-dimensional array. Excel for the web does not run VBA macros; use the desktop Excel application to run VBA. The core array syntax is similar in desktop Excel for Windows and Mac, though available integrations or platform-specific code outside basic arrays can differ.
Common allocation problems and fixes
Array errors often come from a mismatch between the bounds the code allocated and the indexes it later uses. Before changing a routine, identify the declared type, the allocation statement, the lower and upper bounds, and the dimension being indexed. These checks help diagnose the most frequent problems:
- Subscript out of range: A loop or lookup uses an index below the lower bound or above the upper bound. Use the array’s actual bounds and verify calculated indexes.
- Array not allocated: A dynamic array was declared but never sized, or it was cleared with
Erase. Allocate it before reading elements or requesting its bounds. - Values disappear after resizing: A later
ReDimomittedPreserve. Preserve existing contents when appropriate, remembering its dimension restrictions. - Wrong number of elements: A bound was mistaken for a count. An inclusive range from 1 to 10 has 10 elements, while 0 to 10 has 11.
- Range assignment behaves unexpectedly: A single-cell range returns a scalar value, unlike the two-dimensional array returned for a multi-cell range.
Erase also behaves differently depending on the array type. For a dynamic array it releases the allocated array storage; for a fixed-size array it resets the elements to their default values. If a procedure uses Erase, it should not assume the former dynamic bounds are still available afterward. Reallocate the dynamic array before reusing it.
Frequently Asked Questions
Do I need ReDim after declaring an array with empty parentheses?
Yes. A declaration such as Dim data() As Long defines a dynamic array variable, but it has no allocated elements yet. Use ReDim to give it bounds before accessing an element.
What is the difference between Dim and ReDim in VBA?Dim declares a variable and its type; it can also declare a fixed-size array when bounds are included. ReDim sets or resets the bounds of a dynamic array while the code runs.
How can I find the size of an array?
Use LBound and UBound for its bounds. For a one-dimensional array, the element count is the upper bound minus the lower bound plus one. For a multidimensional array, ask for the bounds of each dimension separately.
Can ReDim Preserve resize every dimension?
No. It can preserve existing values while changing only the upper bound of the last dimension. To grow a different dimension while keeping data, allocate another array and copy the values, or redesign the storage approach.
Should I use a Variant array for every worksheet task?
No. A Variant is useful for mixed data and for receiving multi-cell range values, but a typed array is clearer and often more memory-efficient for uniform data. Choose the type that matches the values and the way the procedure handles them.
Related
- Excel: Specify Which Duplicate to Remove?
- Excel Solver With Binary Options: Setup and Examples
- Excel Solver With Integer Values: Setup and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort in Two Columns: Code, Steps, and Key OptionsYes. In Excel VBA, sort the complete data range and pass two keys to its Sort method: the…
- VBA Check If Workbook Exists: Paths, Files, and ErrorsYes. In VBA, use Dir$ with the workbook’s full file path and check whether it returns an empty…
- Excel Macro Message Box: How to Create and Use OneAn Excel macro message box is a pop-up created with VBA’s MsgBox function. In Excel for Microsoft 365…
- Excel VBA Code to Sort Data by One or More Columns in ExcelTo sort data with Excel VBA, set the range to sort, add one or more sort keys, specify…
- Create a UserForm in Excel VBA: A Step-by-Step GuideTo create a UserForm in Excel VBA, open the Visual Basic Editor, choose Insert > UserForm, add controls…
- Excel VBA Find Text in a String: InStr ExplainedIn Excel VBA, use the InStr function to find one piece of text inside another: it returns the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.