30 Advanced Excel Tips for 2026

Excel Champs / VBA / Arrays / VBA Add New Value to the Array
By Puneet Gogia (Microsoft MVP)

VBA Add New Value to the Array

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Resize the array one element larger with ReDim Preserve, then put the new value in the last position:

ReDim Preserve myArray(LBound(myArray) To UBound(myArray) + 1)
myArray(UBound(myArray)) = newValue

The array must be dynamic, declared with empty brackets, as in Dim myArray() As Variant. A fixed array such as Dim myArray(1 To 3) can’t be resized and gives “Array already dimensioned”. Preserve keeps the existing values; without it, every element is cleared.

For the very first value, UBound fails with error 9 because the array has no elements yet. Size it directly the first time with ReDim myArray(1 To 1), or keep a count of the items you’ve added and use that instead of UBound.

When you don’t know how many items you’ll add, a Collection is simpler: Dim items As New Collection, then items.Add newValue, with no resizing at all. Each ReDim Preserve copies the whole array, so adding one item at a time gets slow in long loops.

To add a new value to an existing array you need to have a dynamic array to redefine the elements of it, and when you do this, you need to preserve the values for the old elements. That helps you to only add the value to the new element you have defined and gives the rest of the part intact.

Below you have an array where you have two elements defined. As it’s a dynamic array you have a “ReDim” statement to define two elements and then add values to those elements.

dynamic-array-to-redefine-the-elements

Ahead we will add a third element to this array.

Add a New Value to an Array in VBA

  1. First, you need to use the “ReDim” statement with the “Preserve” keyword to preserve the two elements including the new element for which you want to add the value.
  2. Next, you need to define the elements that you want to have in the array. Here you need to have three elements, so we are using 1 to 3 for that.
  3. After that, you need to add value to the third element which is the new element you have defined.
  4. In the end, use the debug.print to get all the elements along with the new elements into the immediate window.
Option Explicit
Option Base 1
Sub vba_array_add_value()

    Dim myArray() As Variant
    ReDim myArray(2)
    myArray(1) = 5
    myArray(2) = 10
   
    ReDim Preserve myArray(1 To 3)
    myArray(3) = 15
   
    Debug.Print myArray(1)
    Debug.Print myArray(2)
    Debug.Print myArray(3)
   
End Sub

Related – Option Explicit in VBA

Let me share a few more words with you.

  • You need to declare an array as a dynamic array at the starting if you know you need to add more value to the array in the code further.
  • By using this method, you can only add a new element to the end of the array.
Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel