Storing Stuff in VBA Lists

You no doubt recall when snb wrote about Scripting.Dictionaires. Well, there’s more.

I use Collection objects in my custom class modules almost exclusively. It’s the only object, that I know of, that I can enumerate using For Each.

Outside of custom class modules, I use Dictionary objects. I used to avoid them because they weren’t built in to the language. I was always afraid of some dependency problem. But I’ve never seen one in all my years, so I’m over that now. The advantage of the Exists property and the ability to produce an array of keys or items is awesome. it’s probably more awesome than For Each, but I just haven’t made that leap yet.

And I never use ArrayLists because I never remember them. That’s not totally true. When I’m writing a procedure with a Dictionary and I need to sort, I kick myself for not using an ArrayList.

Here’s some features of Collections, Dictionaries, and ArrayLists.

Feature Collection Dictionary ArrayList
New Enum in class Yes No No
Exists No .Exists .Contains
Key Value paradigm Yes Yes No
Unique keys Yes Yes NA
Key data types String Any NA
Get keys No Yes NA
Auto create items No Yes No
Insert anywhere .Add(,,before,after) No .Insert
Output to array No .Keys or .Items .ToArray

There are other differences. Those are just the ones that are important to me. If there’s a difference that’s important to you, leave a comment. You can read everything you ever wanted to know about these objects at one of the pages below:


17 thoughts on “Storing Stuff in VBA Lists

  1. Dick,

    Did you consider:

  2. You mean for the For Each comment? I’m talking specifically about using NewEnum in a custom class module.

    With this, I can use

    But as far as I know, I can’t use

    with anything but a Collection.

  3. Interesting…I haven’t come across ListArrays before.

    Case Sensitivity is important to me sometimes. Which means I use Dictionaries over Collections.

    I know we’re talking about VBA arrays here, but sorting in the sheet itself is really, really fast. So if your data is there to start with, sort it there, then pull it in. In fact it still seems faster to sort in the sheet even in the event that your data is in VBA to start with. i.e. dump it to a temp sheet, sort it, then pull it back to VBA. Granted this relies on you either not hitting the application.transpose limit, or alternately your unsorted data being in an appropriately dimensioned 2D array that will let you dump it to the sheet without transposing.

    By way of comparison, I punched up the below code which compares how long it takes to sort both numbers and text that’s in a VBA array of dimensions1 to 1, 1 to 1000000):

    The result?

    Sheet – Numerical: 2.54122
    ListArray – Numerical: 6.95288
    Sheet – Text: 7.07139
    ListArray – Text: 9.52241

  4. Thanks for the link, Dan. I take it you’re the author of this great article? If so, I’ve got a question on it. The article says:

    Notice, how you can actually use the .Item property to assign a value to a Key, instead of using the .Add method described earlier. Using the .Item property to create a Key, instead of using the .Add method, described earlier, is called ‘Silent Add’, and in fact, this way of creating Keys is significantly faster than using the .Add method, thus you should avoid using the .Add method at all, and instead stick to using the .Item property – even when creating Keys for the first time.

    So a silent add is something like this:
    oDic.Item(vData(i, 1)) = vData(i, 2)

    …as opposed to the hard add:
    oDic.Add vData(i, 1), vData(i, 2)

    But I’m finding that the silent add is only about one second faster when adding one million randomly sorted keys and associated items. Will flick you my test workbook.

  5. You can only compare methods after loading the mscor library into memory:

    It gives me this result

    Sheet – numerical: 3,41666

    Sheet – string: 21,84833

    Arraylist numeric: 10,45621

    Arraylist string: 14,35369

  6. snb: you need to turn those RAND formulas to values to perform a fair test, otherwise the “Sheet – string” time includes the overhead of recalculating 2 million RAND formulas in the original sheet when the sort is performed in the temp sheet.

    If I amend your code like so:

    …then I get these results:
    Sheet – numerical: 2.64272
    Sheet – string: 7.54244
    Arraylist numeric: 6.97590
    Arraylist string: 9.31773

  7. I hadn’t heard of arraylists either, but after some brief experimentation I have two questions:

    You can add an array to an arraylist with a single statement, but the array is then a single item. Is there any way to sort that array?

    If you add a 2D array to an arraylist is it possible to sort the array on a specified column?

  8. Hi Dick – you can roll your own “exists” for a collection. This is one I found along the way;

    …best, Michael

  9. Sorting an array in a Arraylist:
    1. read the array
    2. put the values of the sorting ‘column’ in an arraylist (in the example ‘column’ 3)
    3. sort those values
    4. add the ‘rows’ of the array into the arraylist in the resulting sorting order

  10. Thanks Dan, snb and James.

    I also have my own VBA sort routine, base on a comb sort (see
    and some VBA links to Python sorting routines (see

    When I have time I’ll compare them with the other suggestions (including Jeff’s on-sheet sort), and also update the Python to use xlwings/ExcelPython.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.