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.
- Redefine the array size.
- 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.