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.
Jiohn W says
How do I make this work in VBA – I added reference to mscorlib
It complies OK but I always get automation error at the set statement
Terry says
Outstanding. I have spent 10 hours today not understanding this and not finding a solution. You are a legend.
Paulie says
I’m really pleased you liked the solution. It’s much better than the default functionality.
Kashif Abbas says
excellent job
A Romero says
If there a way to use the DotNetArray to populate a VBA combo box? For example:
combobox.ListofStuff = DotNetArray
This works with regular VBA arrays but when I use it with the DotNetArray I get an error.
g says
Hi. Is there any way to insert a new item in any intermediate position without overwriting the existing item ?
Santiago says
Hello G, you can use *.insert pos, value*. Example: list.Insert 4, “Car”)
That would push the value “Car” into the 4th position of the list
Read more in: https://excelmacromastery.com/vba-arraylist/
Heraclio says
Very useful, thank you so much!
Paul Martin says
Very useful, thanks
p says
Hi. Is there any way to insert a new item in any intermediate position without overwriting the existing item in an array?
Santiago says
Not in a standard array. You would have to redimension the array, and shift all the values after the desired position, then adding your value in.
Alternatively, you can use the arrayList object, which do allow to insert values inbetween.
Joaquin says
This is beautiful