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.
