VBA – Add Items to Array – Dynamic Resize without erasing data

How to add new item to existing array?

In VBA, an array is always an fixed size of data values. If you have to add new item, then size of the field also has to be changed.

Lets see an example: Consider you have an array like the below one:

strArray(0-3) = Array(“1″,”2″,”3″,”4”)

There is no direct method to insert or delete an item in middle of the array. Now, we will add item “5” to the end of this array.

How to Resize the array without loosing existing data?

This can be done by following these steps.

1. Redefine the  array size.
2. Add items to end.

Here is the proper code snippet that initializes the array. Then does all actions mentioned above.

Note: Do not mention number of items in the array in the declaration section.

Sub addItemToArray()
    'Define & inialize Array
    Dim strArray() As String
    
    ReDim Preserve strArray(3)
    strArray(0) = "1"
    strArray(1) = "2"
    strArray(2) = "3"
    strArray(3) = "4"
    
    'Find size of Array
    Debug.Print UBound(strArray)
    
    'Resize Array length
    'ReDim Preserve strArray(5)
    'or
    ReDim Preserve strArray(UBound(strArray) + 1)
    
    'Add new Item to resized Array
    strArray(UBound(strArray)) = "5"
    
    'Find length of Array
    Debug.Print UBound(strArray)
End Sub

The Ubound function will return the index of last element in the array. Do not confuse it with total number of items in the array.

For example: If an array has 5 values (0 to 4) then Ubound will return 4.

How to add multiple items to existing array?

The above code snippet added only one element to existing array. What if You have to add numerous values to the array dynamically during the code run time.

Here is a sample code for that:

Sub addMultipleItemsToArray()
    'Define Array data type & Initialize
    Dim intArray() As Integer
    ReDim intArray(1)
    intArray(0) = 1
    intArray(1) = 2
    
    'Add multiple items to Array
    Debug.Print UBound(intArray)
    Dim i As Integer
    For i = 3 To 100
        ReDim Preserve intArray(UBound(intArray) + 1)
        intArray(UBound(intArray)) = i
    Next i
    
    'Verify Output
    For i = 0 To 99: Debug.Print intArray(i): Next
End Sub

This way you can resize the array at any point of time & add new items to it. But remember that all these items can be only added to the end of array.

External Reference: Here is another reference that explains the same concept with some easy examples – click here to read.