Excel VBA Array Add: Append Values with ReDim Preserve

VBA & macros · Updated November 21, 2026

To add an item to a VBA array, resize a dynamic array with ReDim Preserve, then assign the new value to its new index. VBA arrays do not have an Add method; if you need frequent additions, a Collection may be simpler.

Why VBA arrays do not have an Add method

An array is a block of indexed values with bounds, such as indexes 0 through 2 or 1 through 3. A fixed-size array cannot grow after it is declared. A dynamic array can be resized, but VBA requires you to manage that resizing explicitly. This differs from a Collection, which provides an Add method and expands as you insert items.

For a dynamic array, the usual append sequence is: find the current upper bound, increase it by one while preserving the existing values, and write the new value at that index. This is appropriate when the array is small or changes only occasionally. If you are collecting many values one at a time, repeatedly resizing can be slow because VBA copies the existing array contents on each resize.

Append an item with ReDim Preserve

This example uses a zero-based dynamic string array. It contains two names, expands the array by one slot, and puts a third name at the new upper bound.

Option Explicit

Private Sub AppendText(ByRef values() As String, ByVal newValue As String)
    Dim newIndex As Long

    ' The caller must pass an already initialized array.
    newIndex = UBound(values) + 1
    ReDim Preserve values(LBound(values) To newIndex)
    values(newIndex) = newValue
End Sub

Sub DemoAppendText()
    Dim names() As String

    ReDim names(0 To 1)
    names(0) = "Mia"
    names(1) = "Noah"

    AppendText names, "Ava"
    Debug.Print Join(names, ", ")
End Sub

After the call, the array contains Mia, Noah, and Ava; the Debug window prints Mia, Noah, Ava. The helper keeps the existing lower bound by using LBound(values), so it also works with an initialized array whose lower bound is 1. It assumes a valid, nonempty array, however: calling UBound on an uninitialized dynamic array causes an error.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken change may require restoring the saved copy. The sample only prints the values, but this is a sensible habit for any macro that edits workbook data.

Initialize the array before appending

A dynamic array is declared with empty parentheses, as in Dim values() As String, but that declaration does not give it usable bounds. You must use ReDim before accessing an element or using LBound and UBound. The sample initializes two slots with ReDim values(0 To 1).

If your program can start with no values, decide on an explicit empty-state strategy rather than calling the append helper on an uninitialized array. For example, you can keep a separate item count and allocate the first slot when that count is zero, or use a Collection while gathering unknown quantities. VBA does not provide a universally safe, convenient zero-length array convention for this pattern, so code should not assume that an uninitialized dynamic array has a valid upper bound.

Bounds matter. ReDim values(0 To 1) creates indexes 0 and 1, while ReDim values(1 To 2) creates indexes 1 and 2. Explicit bounds make code easier to follow and are unaffected by the module-level Option Base setting. Use LBound and UBound rather than hard-coding a bound when the array might use either convention.

Append repeatedly and consider performance

The helper can be called once for each new item, but each call to ReDim Preserve may copy all values already stored. Adding 10,000 items one at a time can therefore involve far more copying than adding them in one resize. For a short list, the straightforward approach is usually easiest to maintain. For a large or unknown number of items, consider one of these approaches:

  • Preallocate: If you know the maximum or expected number of items, size the array once and track how many positions are in use. Resize only if the capacity is exceeded.
  • Collect, then convert: Add values to a Collection, then copy them into an array once you know the final count.
  • Read worksheet data in a batch: When processing a range, read the range values into a Variant array, work with that array, and write results back in a block. This usually avoids cell-by-cell worksheet access as well as repeated resizing.

Preallocation requires care: unused slots are not actual items. Keep a separate count and process only indexes that contain meaningful values. Do not use UBound as the item count if the array has spare capacity.

When a Collection or Dictionary is a better fit

Use a Collection when the main requirement is to add items dynamically and keep them in insertion order. Collections are one-based and support Add, Count, and indexed retrieval. This short example creates one and adds two strings:

Sub DemoCollection()
    Dim names As Collection

    Set names = New Collection
    names.Add "Mia"
    names.Add "Noah"

    Debug.Print names.Count
    Debug.Print names(1)
End Sub

The count printed is 2, and names(1) returns Mia. A Collection is not an array: code expecting an array, array bounds, or array functions may need a conversion step. A Scripting.Dictionary is another option when values should be looked up by unique keys. It is useful for keyed data, but it is not simply an array with an Add method; its keys, duplicate-key behavior, and iteration are different. Choose based on how the data will be accessed, not just how it is added.

Adding to a multidimensional array

There is an important limitation when resizing a multidimensional VBA array: with ReDim Preserve, you can change only the last dimension. For example, an array declared as values(1 To 5, 1 To 3) can be expanded from three to four columns while preserving data. It cannot be expanded from five to six rows with ReDim Preserve, because the first dimension is not the last one.

If your data is arranged as rows and you need to append rows, possible alternatives include storing records in a one-dimensional array of custom types, accumulating rows in a Collection, or allocating a larger two-dimensional array and copying existing values into it. If you choose the larger-array approach, copy every required element and then replace the old array; unlike ReDim Preserve, a new allocation does not automatically keep the prior contents. For worksheet tables, it may be simpler to append to the worksheet range and read the full range again when needed.

Common errors and misconceptions

  • “Subscript out of range”: The index is outside the current bounds, or the array has not been initialized. Check the bounds with LBound and UBound before indexing.
  • Values disappear after resizing: Omitting Preserve discards the existing contents when the array is redimensioned. Include it when you intend to retain values.
  • Trying array.Add: A VBA array has no Add method. Use ReDim Preserve for a dynamic array or use a Collection.
  • Trying to resize the wrong dimension: With ReDim Preserve, only the last dimension can change. Redesign the data layout or copy into a newly allocated array if you must grow another dimension.
  • Assuming all arrays start at zero: The lower bound can vary. Check LBound, especially when code receives an array from another procedure or uses a different declaration style.

Also distinguish a VBA array from a worksheet range. Adding an element to an in-memory array does not insert a cell or extend an Excel table. To change worksheet data, use worksheet or table operations separately; then update or reload the array if your code depends on those changes.

Running VBA in Microsoft 365

For Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert or select a standard module, and place the procedure there. On Mac, use Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard and function-key settings. Excel for the web cannot create, edit, or run VBA macros, so use the desktop Excel app for this code.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). Only enable macros in files you trust, since VBA can make changes beyond the visible worksheet. If the code changes data, test it on a copy first; saving as .xlsx does not preserve VBA projects.

Frequently Asked Questions

Can I use Add with a VBA array?
No. Arrays do not have an Add method. Use ReDim Preserve to extend a dynamic array, or use a Collection if its Add method better suits the task.

How do I add an item to an empty dynamic array?
Initialize it before relying on LBound or UBound; those functions fail for an uninitialized dynamic array. Track whether the array has been initialized, or start with a Collection if the number of items may be zero or unknown.

Does ReDim Preserve keep the existing values?
Yes, for the array contents that fit within the retained bounds. The word Preserve is essential when resizing an array and keeping its existing values. It does not allow you to change any dimension except the last one in a multidimensional array.

Is a Collection faster than appending with ReDim Preserve?
For repeated additions, a Collection avoids explicitly resizing and copying the array on every addition, and it is often more convenient. The best choice depends on the amount of data and what you need to do afterward; if you ultimately need an array, conversion has a cost too.

Can I append an entire row to a two-dimensional array?
Not with ReDim Preserve if rows are the first dimension, because only the last dimension can be resized while preserving data. Use a different data structure or allocate a larger array and copy the existing values.

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.