top of page
  • John

A better collection in VBA

In 'vanilla' VBA, there are two types of 'collection' (note the lowercase 'c') – arrays and Collections. Neither IMHO are particularly user-friendly (manually managing the 'bounds' in dynamic arrays?!; hidden keys in Collections?!). Then in the Microsoft Scripting Runtime library there is a Dictionary. Which is great … if you want a Dictionary (i.e. every item in the Dictionary has a key and a value). There is also ArrayList (and friends) in the MSCORLIB library … but only if the device you are running your VBA code on has .NET Framework 3.5 installed … which hasn't been installed by default since Windows 7.

So, all in all, not a great 'collection'.

I also spend quite a bit of my time writing C# code. In C# (and also in VB.NET), there is a 'List' (along with various other collection types). List is great … you can Add items (at the end of the List); you can Insert items (at a specific index of a List); you can AddRange (add multiple items at the end of the List); you can Remove items; you can see if the List Contains items; you can get the Index or LastIndex of an item … and so on.

So I decided to do something to make life a little simpler … and so, below, is VBA's very own List.

To use this in your VBA project, add a Class Module. It must be a Class Module … not a Standard Module. Rename it to 'List' then copy the entire code in the following block and paste it into your new Class Module.

To explain a few things:

  • Index numbers for List are 0-based (the index of the first items is 0, the index of the second item is 1) … so to iterate over the List, you would start at 0 and continue on to .Count – 1 (example below)

  • If you are using List to store primitive types (i.e. Strings, numerics, Booleans, Dates) then all procedures and properties of List will work as-is

  • If you are using List to store Object types (whether your custom Class Objects or built-in Objects e.g. Worksheets, Documents, Slides, MailItems etc) then, with the exception of Join(), the procedures and properties down to, and including, Clear() will work as-is … using the remaining procedures and properties, from Remove() onwards (i.e. those that call the internal FindItem() Function), requires a little code being added to the FindItem() Function … explained in a moment

  • List includes a TYPE_SAFE compiler constant … when this is set to True (which it is in the above code … and normally you would want to leave it being True) then List will enforce run time type safety, in other words, only one type of item can be stored in any one instance of a List (though, in any one VBA project, you can create as many instances of List as you want and each instance can hold a different type of item) … that can be Strings, Longs, Worksheets, Document, custom Class Objects, whatever you want … but it must be one type of item (the first item stored in the List defines the type) … you can change TYPE_SAFE to False and then you can store any type of item (you might do this if you want to store multiple types of numeric values, or perhaps you have multiple types of Class Object that implement the same Interface ...) but you may then get unreliable results (including errors) from the procedures and properties that call the internal FindItem() Function

Here's some example uses. In each cases you need to create your List … I've shown this once here but it is, obviously, required for each List.

This example adds some Longs then uses Join() to create a single String of them and then print them

This example adds some Strings then checks whether certain values are contained in the List, and at what index (remember, indexes are 0-based)

This example adds and inserts multiple Strings then removes some of them, then loops over the list to print each one

So what about storing Objects? As mentioned above, some of the procedures and properties of List require code to be added to the FindItem() Function. Below is an example that works for Excel Worksheets and Ranges and custom Class Object 'Person' which (in this example) include two properties: FirstName and LastName. These are examples and you need to customise FindItem() for the type of Object that you are adding to it. You can add code for as many Object types as you want to FindItem() and then have multiple instances of List in your VBA project storing those types of Object or other primitives as the code will test for the type of item before comparing them.

The code you add to FindItem() needs to test whether properties of vItem equal the (same) properties of mvArray(i) and, if so, set FindItem equal to i and then exit the Function. Which properties you test depends entirely on the nature of the Object. In the first example in FindItem(), below, for a Worksheet, testing the name of the Worksheet and the name of the Workbook that contains it (if you only added Worksheets from one Workbook then you could do away with the test of the Workbook name). Range tests a whole bunch of properties. And Person tests for FirstName and LastName.

So this is the customised FindItem() ... if you want to run the example code, below this, then copy this and replace the existing FindItem() in your List Class Module:

Then this example adds some Worksheets (and so will only work in Excel, in a Workbook with 5 or more Worksheets) then gets the indexes for certain of them

... if you get an error on any of the .Add lines then please check the Workbook has at least 5 Worksheets.

This example adds some Paragraphs (and so will only work in Word, in a Document with 3 or more Paragraphs) then gets the count of items added and an index for one of them

... if you get an error on the .IndexOf line then please check the Document has at least 3 Paragraphs.

Of course, I've only added a limited number of procedures and properties to List. It would be possible to go on and add many more. But I had to stop somewhere. Do you think List would be better with additional procedures and properties? Leave a comment to let me know what …

One additional thing that you can do with a List, with a little effort, is sort it … if it contains Strings or Objects.

If it contains Strings, see my Sorting Strings in VBA post and put a copy of SortStrings into your VBA project (in a Standard, not Class, Module). You'll have to do what is suggested in the final couple of paragraphs of that post … adjust the first parameter of the Sort() method to accept a Variant instead of the array of Strings, then use code like this, which displays the output if used with the relevant prior example

... if you get an error on the .Sort line then please remember to adjust the first parameter of the Sort() method to accept a Variant instead of an array of Strings as per the final couple of paragraphs of the Sorting Strings in VBA post.

If it contains Objects, see my Sorting Objects in VBA post (including adding your custom Compare() Function) and put a copy of SortObjects into your VBA project (in a Standard, not Class, Module), then use code like this

... you could, of course, re-work the SortStrings and SortObjects code and include them within List ... if you have the time!

A final point is to discuss performance. If you examine the code of List, you will see that 'under the hood' it uses an array. It's clear, then, that it must be slower than using an array directly … all the goodness of the lovely procedures and properties added by List takes up time. Here are some stats of the additional time taken for different types of collection compared to using a (dynamic) array. The tests consisted of adding and removing 1000 Strings, repeated 500 times:

List x1.6

Collection x7.1

Dictionary (early bound) x5.1

Dictionary (late bound) x10.3

ArrayList (early bound) x17.3

ArrayList (late bound) x17.4

… from this you can see that List takes 60% longer than using an array directly. Using a Collection was 7 times slower than an array, using a Dictionary (from the Microsoft Scripting Runtime library) was 5 times slower using early binding or 10 times slower when using late binding, and using an ArrayList (from the MSCORLIB library) was 17 times slower whether using early or late binding. Note that I didn't include a static array in the comparison as, obviously, you cannot re-size a static array.

In summary, no surprise, List is slower than using an array. But I'm actually quite pleased with its performance … unless absolute speed is crucial to your needs then using List makes a lot of sense!


Recent Posts

See All


bottom of page