VBA Add New Value to the Array

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.

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.