VBA – Add Items to Array

VBA does not allow you to append items to an array. The standard method to work around this is to re-dimension to array to the required size and then add the required items.

Take the following code as an example:

Dim VbaArrayNames() As String
ReDim Preserve VbaArrayNames(0)
VbaArrayNames(0) = "Peter"
ReDim Preserve VbaArrayNames(1)
VbaArrayNames(1) = "Paul"
ReDim Preserve VbaArrayNames(2)
VbaArrayNames(2) = "Andy"

There are a number of problems with this method:

• Performance – The array is copied into a new array each time it is re-dimensioned.
• Readability – Lots of additional code we can do without.
• Sorting – There is no easy way to sort the data.
• Removal – No Simple way to remove an element from the array.

There is another method which is much easier, faster and has more functionality, and that is to use the ArrayList class from .net framework:

Set DotNetArray = CreateObject("System.Collections.ArrayList")
DotNetArray.Add "Peter"
DotNetArray.Add "Paul"
DotNetArray.Add "Andy"
DotNetArray.Sort
Dim Name As Variant
For Each Name In DotNetArray
    Debug.Print Name
Next

As you can see the array can easily be sorted without having to resort to bubble sort methods and the code is much cleaner.

If you are working with large arrays there will certainly be a performance boost, and even if you aren’t, the code will be much cleaner.

Some of the other useful methods:

Remove an element from the array

DotNetArray.Remove "Andy"

Check if the array contains a particular value using the contains method

This will return true or false depending on the result.

If DotNetArray.contains("Paul") Then
    Debug.Print "Paul is in the array"
Else
    Debug.Print "Paul is not in the array"
End If

Reverse the Array with the reverse method

Just like sorting, reversing the array can be done in an instant:

DotNetArray.Reverse

Adding items to a VBA array is just much easier when using the arraylist class. You can also do exactly the same in VBScript and Powershell, which I have covered in the linked posts.