VBA Collection

VBA Collection Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 3.50 out of 5)
Loading...

The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. VBA Collections are more flexible than VBA Arrays as they are not limited in their size at any point in time and don’t require manual re-sizing. Collections are also useful when you don’t want to leverage there more complex (but quite similar) Data Structures like the VBA ArrayList or even a VBA Dictionary.

Adding items to a VBA Collection

Let’s start by creating a New VBA Collection and adding some items to it.

Notice that the VBA Collection is not typed in any way as we didn’t have to declare what types of objects it stores. This is because a VBA Collection stores object of type Variant.

By default the Add procedure will push items to the end of the VBA Collection. However, you can also choose to insert items before or after any index in the Collection like so:
Before:

After:

If you want to be able to reference a particular item in your VBA Collection by a certain string/name you can also define a key for object added to your VBA Collection like so:

Removing items from a VBA Collection

Removing items from a VBA Collection is equally easy. However, items are removed by specifying their index.

Be careful when removing items from a VBA Collection – items start indexing at 1 (not 0 as in most common programming languages.

When removing items in a loop do remember that the index of the remaining items in the VBA Collection will decrease by 1.

Clearing a VBA Collection

To Clear all items (remove them) from a VBA Collection use the Clear function.

Clearing all items from a Collection is similar to recreating a VBA Collection:

Counting items in a VBA Collection

Similarly as with other VBA Data Structures the VBA Collection facilitates the Count function which returns the amount of items currently stored.

Getting items in a VBA Collection

To get a specific item in your VBA Collection you need to either specify it’s index or optionally it’s key (if it was defined).

Traversing a VBA Collection

As with VBA Arrays you can similarly traverse a VBA Collection using loops such as For, While etc. by indexing it’s items, or by traversing its items using the For Each loop (preferred).

Check if VBA Collection contains item

Unfortunately, the VBA Collection object does not facilitate a Contains function. We can however easily write a simple Function that will extend this functionality. Feel free to use the Function below:

Usage example:

Convert VBA Collection to VBA Array

In some case you may want to convert your VBA Collection to a VBA Array e.g. if you would want to paste items from your Collection to a VBA Range. The Code below does that exactly that:

Below a test of how it works:

Summary

The VBA Collection is an extremely useful data type in VBA. It is more versatile than the VBA Array allowing you to add and remove items more freely. The Key-Value store works also similarly as for the VBA Dictionary making it a useful alternative.

As an exercise – next time you will consider using a VBA Array consider replacing it with the Collection object. Hopefully it will guest in your VBA Code more often.

Related Posts

3 thoughts on “VBA Collection Tutorial”

  1. I maybe wrong but I have noticed some irregularities (obviously, a typo) in what is written/coded and then shown as a result. For example, it says that items in the collection start indexing at 1; yet, when adding an item after “0”, it is placed in the middle, as if indexing actually starts at 0, rather than 1. Then, when removing items, the code says remove item index number 2 (which should be 20, if starting from 1); yet, 30 is removed – again, creating an impression, that indexing actually starts from 0.
    I would really appreciate if you could clarify this. Thank you very much for your hardwork in maintaining this website and your time!

    1. I have tested my finding. It really is a typo. When removing items, it should say “10,30” rather than “10, 20”.
      In case of the use “after” expression, “after:=0” code does not seem to work. Given the result of “10,30,20”, the code should be “After: =1”.

Leave a Reply