To append a value to a one-dimensional Excel VBA array, increase its upper bound with ReDim Preserve, then assign the new value at the next index. For example, use ReDim Preserve values(0 To itemCount) before writing to values(itemCount); if the array is not allocated yet, use ReDim without Preserve.
What “append” means in VBA
VBA arrays have a defined set of index positions. Unlike a worksheet column, an ordinary array does not automatically grow when you assign a value beyond its current bounds. Appending therefore means keeping track of the next available index, enlarging the array when necessary, and placing the new item there.
A dynamic array is declared with empty parentheses, such as Dim values() As Variant. It is not allocated until a ReDim statement gives it bounds. A fixed-size declaration, such as Dim values(0 To 9) As Long, already has ten positions and cannot be resized with ReDim.
Use an explicit lower bound, such as zero or one, and track the item count separately. The count is not the same as the array’s upper bound: a zero-based array containing four items has an upper bound of 3, while its count is 4. Keeping that distinction clear prevents off-by-one errors.
A simple one-dimensional append example
This macro reads nonblank, non-error values from A2:A5 on Sheet1, appends them to a dynamic array, and prints the results in the VBA Immediate window. It assumes the worksheet exists and that the cells contain ordinary values; blank cells and cells containing errors are skipped.
Option Explicit
Sub AppendCellsToArray()
' Save a copy of the workbook first; macros cannot be undone with Ctrl+Z.
Dim source As Range
Dim cell As Range
Dim values() As Variant
Dim itemCount As Long
Dim i As Long
Set source = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5")
For Each cell In source.Cells
If Not IsError(cell.Value2) Then
If Len(CStr(cell.Value2)) > 0 Then
If itemCount = 0 Then
ReDim values(0 To 0)
Else
ReDim Preserve values(0 To itemCount)
End If
values(itemCount) = cell.Value2
itemCount = itemCount + 1
End If
End If
Next cell
For i = 0 To itemCount - 1
Debug.Print values(i)
Next i
End Sub
The first accepted value creates an array with index 0. On each later value, ReDim Preserve extends the upper bound by one and keeps the values already stored. The assignment uses the old item count as the new index, and then the count is incremented. If every source cell is blank or an error, the array remains unallocated and the output loop has no iterations.
To use the macro, change the sheet name and range to match the workbook. In the Visual Basic Editor, the Immediate window can be shown with Ctrl+G on Windows. Before running any macro, save a separate copy of the workbook: VBA changes cannot be reversed with Excel’s Ctrl+Z undo command.
Bounds, empty arrays, and data types
VBA cannot safely call UBound on a dynamic array that has never been allocated; it raises an error. That is why the example uses itemCount to identify its first item instead of checking the array’s upper bound. Another approach is to allocate the array at the outset and treat a separate count of zero as “no items yet.” Either way, establish a consistent convention.
For an array declared with Dim values() As Variant, a Variant array can hold values of different types, including text, numbers, dates, and other Variants. A typed array such as Dim values() As Long uses less flexible values and is preferable when every appended item must be a Long. The array’s element type does not change the resizing rule, but the value assigned must be compatible with that type.
Use LBound(values) and UBound(values) when code may receive arrays with different bounds. Do not assume that all arrays start at zero: VBA supports other lower bounds, and a module’s Option Base setting can affect bounds when they are not stated explicitly. Writing bounds directly in ReDim, as in ReDim values(0 To 0), makes the intended indexing clear.
Why repeated ReDim Preserve can be slow
Appending one item at a time with ReDim Preserve is easy to understand, but it can become inefficient for a large array. Each resize may require VBA to allocate a larger block and copy the existing contents. If a loop appends thousands of items, repeatedly copying the items already collected can take much longer than adding them to a structure that grows in larger steps.
One improvement is to reserve spare capacity, such as 16 slots, and double that capacity only when it fills. The array then grows much less often. Keep a separate item count because some allocated positions may be unused. This example shows the pattern; it skips blanks and errors like the earlier macro.
Option Explicit
Sub AppendWithCapacity()
' Save a copy of the workbook first; macros cannot be undone with Ctrl+Z.
Dim source As Range
Dim cell As Range
Dim values() As Variant
Dim itemCount As Long
Dim capacity As Long
Dim i As Long
Set source = ThisWorkbook.Worksheets("Sheet1").Range("A2:A1000")
capacity = 16
ReDim values(0 To capacity - 1)
For Each cell In source.Cells
If Not IsError(cell.Value2) Then
If Len(CStr(cell.Value2)) > 0 Then
If itemCount = capacity Then
capacity = capacity * 2
ReDim Preserve values(0 To capacity - 1)
End If
values(itemCount) = cell.Value2
itemCount = itemCount + 1
End If
End If
Next cell
If itemCount > 0 Then
ReDim Preserve values(0 To itemCount - 1)
For i = 0 To itemCount - 1
Debug.Print values(i)
Next i
End If
End Sub
Doubling is a common growth strategy because it reduces how often the array is resized. Trimming at the end is optional; it reduces unused capacity if later code needs the array to have exactly the number of collected items. If there are no items, the code deliberately avoids trimming to bounds that would be invalid.
Appending rows to a two-dimensional array
A common misconception is that ReDim Preserve can grow any dimension of an array. In VBA, it can preserve existing contents while changing only the upper bound of the last dimension. For a two-dimensional array declared as data(row, column), the last dimension is the column dimension. You cannot use ReDim Preserve to add rows by changing the first dimension.
For a table-shaped array, practical choices include storing rows as columns so the dimension being extended is last, or allocating a new two-dimensional array and copying the old values into it with nested loops before adding the new row. The copy approach is straightforward but repeats more work as the table grows. If many rows are being collected, it is often better to estimate the final size or use another data structure and convert to a two-dimensional array once.
Excel’s Range.Value2 has a related wrinkle: reading a multi-cell range returns a two-dimensional Variant array, generally with bounds starting at 1, while reading a single cell returns a scalar Variant rather than a one-cell array. Code that appends range data should account for this difference instead of assuming every range read has the same array shape.
Choosing an array, Collection, or worksheet range
An array is a good fit when you need fast indexed access, know the approximate size, or will process a batch of values in memory. If the number of items is small, the simple resize-and-append pattern is usually clear and adequate. For large batches, reserve capacity, count the source items first and allocate once, or build the result in a worksheet range in one operation.
A VBA Collection is often more convenient when the number of items is unknown and you want to add items without managing array bounds. It supports Add and retrieval by position, but it is not interchangeable with an array: code expecting a typed or two-dimensional array may still need a conversion step. A dictionary is more suitable when items need to be looked up by key, rather than merely appended in order.
For worksheet data, consider whether an in-memory array is needed at all. If the final destination is a column, writing a correctly sized two-dimensional Variant array to a range in one assignment can be much faster than writing each cell separately. The tradeoff is that range reads and writes need careful attention to dimensions, blank values, and the single-cell scalar case.
Common append errors and how to avoid them
- Subscript out of range: The code may be writing to an index beyond the current upper bound. Resize before assigning, and check that the index follows the lower-bound convention.
- Subscript out of range from UBound: The dynamic array may not have been allocated. Track initialization or item count before calling
UBound. - Existing values disappear: A plain
ReDimdiscards the existing contents. UseReDim Preservewhen extending a one-dimensional array and keeping its values. - Preserve fails for a multidimensional array: The requested change may be to a dimension other than the last one. Rebuild and copy the array, change the storage layout, or choose another structure.
- Unexpected items are skipped or included: Decide explicitly how to treat empty cells, formulas returning an empty string, numeric zero, and error values. For example, the sample skips a formula returning an empty string but keeps zero.
When an array comes from another procedure, check its expected element type, lower bound, and dimensions before appending. A helper procedure can centralize the growth logic, but it should also receive or maintain the current item count; an array’s allocated capacity alone does not say how many positions contain meaningful data.
Frequently Asked Questions
Can I append to a VBA array without ReDim Preserve?
Not if the array needs to grow while retaining its existing contents. You can avoid repeated resizing by allocating enough space in advance, collecting items in a Collection, or growing the array in capacity-sized steps.
How do I append one array to another?
Loop through the source array’s valid indexes and append each element to the destination using the same count-and-resize approach. Confirm that both arrays have compatible element types and account for their actual lower and upper bounds.
Can ReDim Preserve add a row to a two-dimensional array?
No. VBA preserves existing contents only when it changes the upper bound of the last dimension. To add rows to an array indexed as row then column, use a new array and copy the contents, or choose a different storage layout.
Why does UBound fail on my empty array?
A dynamic array that has not been allocated has no bounds for UBound to return. Track whether it has been initialized or maintain an item count, and call UBound only after allocation.
Is a Collection better than an array for appending?
It can be, when the item count is unknown and convenient incremental additions matter more than array-specific operations. Arrays are often preferable for typed, indexed, or bulk worksheet processing. Choose based on how the collected values will be used afterward.
Related
- Excel Split Cell at a Specific Character?
- Excel: Specify Which Duplicate to Remove?
- Excel Solver With Binary Options: Setup and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA to Protect and Unprotect Sheet: Full CodeThe fastest way to protect or unprotect a sheet in Excel VBA is Worksheets("Sheet1").Protect and Worksheets("Sheet1").Unprotect. For a…
- Excel VBA Calendar Date Picker: Options and SetupExcel for Microsoft 365 on Windows does not include a universal VBA calendar date-picker control that you can…
- VBA MsgBox Text Formatting: Lines, Buttons, and LimitsVBA’s MsgBox can display plain text, line breaks, tabs, a title, and built-in buttons and icons, but it…
- Excel VBA: Copy a Formula Down to the Last Used RowTo copy a formula in Excel VBA down to the last row of data, find the last used…
- VBA Scripting.Dictionary: How It Works and When to Use ItA VBA Scripting.Dictionary is an object that stores pairs of unique keys and associated values, letting a macro…
- Excel VBA: How to Check Whether Cell Colors Match ExactlyYes. In Excel VBA, compare the cells’ Interior.Color values; they are numeric color values, so an equality test…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.