VBA Array Add: How to Append Values and Resize an Array

VBA has no built-in Array.Add method. To add values, use a dynamic array and resize it with ReDim Preserve, or use a Collection when you want a container that can grow through its .Add method.

What “add to an array” means in VBA

An array holds a set of values under one variable name, with each value addressed by an index. Unlike a list in some other programming languages, a VBA array does not automatically expand when you assign a value beyond its current bounds. The array’s size and bounds are part of its structure, so adding an element usually means resizing the array and then assigning the new value.

There is no standard VBA syntax such as myArray.Add "North". The exact approach depends on what you need:

Use a dynamic array when you need indexed values, a specific data type, or a final array to pass to another procedure.
Use a Collection when the number of items changes and you want straightforward additions without resizing an array yourself.
Use a capacity-based dynamic array when you will append many values and want to avoid resizing after every addition.

For a small number of additions, ReDim Preserve is usually the clearest answer. For a large or unpredictable number of additions, the choice of data structure can affect performance.

Append a value to a dynamic array

Declare an array with empty parentheses to make it dynamic. You can then use ReDim to give it bounds. When it already contains values, ReDim Preserve changes its size while retaining the existing elements. The following complete macro appends each item from a short list and prints the results in the Immediate window.

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. In the Visual Basic Editor, show the Immediate window with Ctrl+G on Windows; on Mac, the editor’s menu and shortcut can vary by keyboard setup.

Option Explicit

Sub AppendSeveralValues()
    Dim items() As String
    Dim incoming As Variant
    Dim value As Variant
    Dim itemCount As Long
    Dim i As Long

    incoming = Array("North", "South", "West")
    itemCount = 0

    For Each value In incoming
        If itemCount = 0 Then
            ReDim items(0 To 0)
        Else
            ReDim Preserve items(0 To itemCount)
        End If

        items(itemCount) = CStr(value)
        itemCount = itemCount + 1
    Next value

    For i = 0 To itemCount - 1
        Debug.Print items(i)
    Next i
End Sub

The output is North, South, and West, in that order. The first pass creates an array with index 0. Each later pass increases the upper bound by one, keeps earlier values, and stores the next value at the new index. The separate itemCount variable records how many elements are in use; the upper bound itself is not a count when the lower bound is zero.

This example uses explicit zero-based bounds, so its first element is items(0). That avoids ambiguity about Option Base, which can change the default lower bound for some array declarations. Explicit bounds are a useful habit when array indexes matter.

How ReDim Preserve works—and its limits

ReDim allocates or changes the bounds of a dynamic array. Without Preserve, resizing discards the array’s existing contents. With Preserve, VBA copies the existing values into the resized array, which is why the example keeps its earlier entries.

There is an important restriction: with Preserve, VBA can change only the upper bound of the last dimension. For a one-dimensional array, that is the dimension being extended, so appending is simple. For a two-dimensional array, you can preserve values while changing the last dimension’s upper bound, but cannot use ReDim Preserve to add rows by changing the first dimension. If your data needs to grow by rows, consider storing records in a Collection, or redesigning the array so the growing dimension is last.

Resizing on every append is easy to understand, but it can become inefficient. Preserving an array requires VBA to make room for the enlarged array and retain the old values. Repeating that operation for thousands of individual additions can mean repeatedly copying an increasingly large set of values. For a few values, that cost is usually insignificant; for a large workload, use spare capacity.

Use spare capacity for many additions

A common optimization is to distinguish the number of values in use from the amount of memory reserved. Start with room for several entries, and double the capacity only when the array fills. Most additions then write directly into an unused slot, without resizing. The code below uses this approach for strings and trims the array to the number of values actually added at the end.

Option Explicit

Sub AppendWithCapacity()
    Dim items() As String
    Dim incoming As Variant
    Dim value As Variant
    Dim itemCount As Long
    Dim capacity As Long
    Dim i As Long

    incoming = Array("North", "South", "West")

    For Each value In incoming
        If itemCount = capacity Then
            If capacity = 0 Then
                capacity = 16
            Else
                capacity = capacity * 2
            End If

            If itemCount = 0 Then
                ReDim items(0 To capacity - 1)
            Else
                ReDim Preserve items(0 To capacity - 1)
            End If
        End If

        items(itemCount) = CStr(value)
        itemCount = itemCount + 1
    Next value

    If itemCount > 0 Then
        ReDim Preserve items(0 To itemCount - 1)
    End If

    For i = 0 To itemCount - 1
        Debug.Print items(i)
    Next i
End Sub

For these three input values, the macro reserves 16 slots, stores three strings, then trims the array to indexes 0 through 2. Doubling is a practical strategy, not a special VBA requirement; another initial size or growth factor may suit a particular workload. The key is that capacity can exceed the number of values currently used.

When a Collection is a better fit

A VBA Collection is a built-in container that can grow without manually changing array bounds. Its .Add method is often the simplest answer when the question is literally how to add items in VBA:

Option Explicit

Sub AddToCollection()
    Dim colors As Collection

    Set colors = New Collection
    colors.Add "Blue"
    colors.Add "Green"
    colors.Add "Gold"

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

This prints a count of 3 and then Blue. Collection positions are one-based, unlike the zero-based array in the earlier examples. A Collection can also accept an optional key when adding an item, which can be useful for looking up a value by a unique text key. Keys must not conflict with existing keys in the same Collection.

Collections are convenient for adding and removing items, but they are not arrays. If another procedure requires an array, you may need to copy the Collection’s items into one. A Collection also does not give you the same compile-time element type as an array declared As String; its items are held as Variants. Choose based on how the data will be used, rather than assuming one structure is universally better.

Arrays, Collections, and other choices

For occasional additions and a final indexed result, a dynamic array with ReDim Preserve is direct and keeps the data in an array. For frequent additions or removals, a Collection avoids manual resizing and has a readable .Add operation. If you need key-based access or dictionary-style lookup, a Scripting.Dictionary may fit better, but it is a different structure and its availability or reference setup should be considered when sharing a workbook across computers.

VBA also has an Array function, but it creates an array from the values supplied to it; it does not provide an append method for an existing array. For example, Array("A", "B") creates a Variant array containing two values. It is useful for initializing a short list, as in the examples, but repeatedly rebuilding a larger array with that function is not a general replacement for a dynamic array or Collection.

Excel worksheet ranges are another option when the values are fundamentally tabular and need to be used by formulas or displayed to users. A range is not a VBA array, though. You can read a range into a Variant array, process values in memory, and write results back to a range; the right choice depends on whether the data belongs in the worksheet or only needs to exist temporarily in code.

Common errors and practical checks

“Subscript out of range”: The requested index is outside the current bounds. Check the lower and upper bounds with LBound and UBound before reading or writing, especially if an array might contain no elements.
Old values disappear: A resize used ReDim instead of ReDim Preserve. Use Preserve when keeping existing values is required.
Type mismatch: A value cannot be converted to the array’s declared type. For a String array, convert suitable values with CStr or validate them before assignment. Conversion does not make every possible value appropriate.
Unexpected indexes: Code assumes indexing starts at zero when the array starts at one, or vice versa. Use explicit bounds such as 0 To n, and do not confuse an element count with an upper bound.
Slow loops: The code resizes with Preserve for every value in a large input. Track capacity and expand in batches, or use a Collection while gathering values.

For an array that may be unallocated, do not assume that UBound can always be called safely; it raises an error when no valid bounds exist. Keep an explicit count or an initialization flag if an empty state is possible. This also helps avoid trying to trim a zero-item array to bounds such as 0 through -1.

Frequently Asked Questions

Can I use Array.Add in VBA?
No. A VBA array has no built-in .Add method. Resize a dynamic array with ReDim Preserve, or use Collection.Add when you want an addable container.

How do I add one item to an existing array?
For a one-dimensional dynamic array, increase its upper bound with ReDim Preserve, then assign the value at the new index. Track the current element count so you know which index is next.

Does ReDim Preserve keep the old values?
Yes. It retains existing values while resizing a dynamic array, provided the requested bounds are valid. In a multidimensional array, Preserve allows changing only the upper bound of the last dimension.

Should I use a Collection or an array?
Use a Collection when items need to be added or removed easily and array-specific behavior is not required. Use an array when you need indexed, typed storage or a procedure expects an array. For repeated high-volume appends, a capacity-managed dynamic array can reduce resizing work.

How do I add a row to a two-dimensional VBA array?
ReDim Preserve cannot enlarge the first dimension while retaining data. Consider a Collection of row-level items, store the growing dimension last if that fits the design, or allocate and copy into a larger array when rows need to be added.

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.