Tachytelic.net

VBA – Add Items to Array

by 6 Comments

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
Image showing how to add items to a VBA array.
It’s easy to add or remove items and sorting is a one liner.

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.

Reader Interactions

Comments

  1. 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

  2. Terry says

    Outstanding. I have spent 10 hours today not understanding this and not finding a solution. You are a legend.

  5. 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.

  6. g says

    Hi. Is there any way to insert a new item in any intermediate position without overwriting the existing item ?

Leave a Reply