• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

VBA – Add Items to Array

September 4, 2019 by Paulie 8 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.

Filed Under: How To Tagged With: VBA

Reader Interactions

Comments

  1. Jiohn W says

    March 26, 2021 at 6:07 pm

    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

    April 1, 2021 at 8:34 pm

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

  3. Paulie says

    April 1, 2021 at 8:35 pm

    I’m really pleased you liked the solution. It’s much better than the default functionality.

  4. Kashif Abbas says

    April 2, 2021 at 7:23 pm

    excellent job

  5. A Romero says

    May 25, 2021 at 9:33 pm

    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

    June 3, 2021 at 1:39 pm

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

  7. Santiago says

    July 11, 2021 at 7:48 pm

    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/

  8. Heraclio says

    January 18, 2022 at 8:24 am

    Very useful, thank you so much!

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 259 other subscribers.

Go to mobile version