Creating and Using Collections in VBA

Similar to arrays, Collections are data structures which can store many values. While each have their own benefits, Collections do not need to be manually resized and are very useful when working with dynamic lists and other data.


This post within the Getting Started with VBA series begins the focus on Other VBA Data Structures.

As the tutorials on one-dimensional arrays and multi-dimensional arrays have provided a basis for storing large amount of data, there is another lesser known data structure that behaves similarly to a one-dimensional array — a Collection.

Collections have two primary uses in VBA. One use–which will be discussed in this post–is storing numeric and string values. The second use–which will be discussed in the Classes in VBA part of this series–can store custom objects.

Tutorial File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Collections vs. Arrays

A Collection is similar to a one-dimensional array — a data structure that can store multiple values. The following table outlines a comparison of difficulty level associated with specific tasks.

Adding ValuesEasy: Add method will automatically add elements to the end of the collection. Parameters within the Add method permit values to be added before or after specific keys.Somewhat Difficult: value must be added when final key (size) is known; must be within array size constraints.
Removing ValuesEasy: Remove method will remove value and re-index the collection; must know key or index value.Difficult: Array key must be assigned an empty value, or manually re-indexed.
ResizingEasy: Will resize automatically when new values are added or removed.Somewhat Difficult: Must use the ReDim or ReDim Preserve statements.
Modifying ValuesDifficult: Must remove and add a new value in the same position.Easy: Assign a new value to the array key.
Printing Individual ValuesEasy: Use Item method and index or key.Easy: Use key.
Printing All ValuesEasy: For Each loop, For loop, or Do loop.Easy: For Each loop, For loop, or Do loop.

Generally speaking, a collection has the upper hand based on ease of use and flexibility. The main drawback is that individual collection elements cannot be modified.

One other drawback of collections relates to efficiency. While a developer has full control over the data type of an array, a collection will always be assigned a variant data type, which uses up more memory than other data types. To their benefit, collections can hold any numeric or string values, even when mixed in the same collection.

Looking beyond these differences, Collections are structured very similarly to, and behave similar to arrays. The terminology is similar, with collections containing keys, indexes, and elements. One thing to keep in mind is that collection elements can be assigned non-numeric (string) keys, and only string keys. a key assigned a number as a string literal will have no impact on that elements order in the collection. See the next section for further explanation.

Collection Methods


As is implied through the method name, the Add method permits a value to be added to the end of a collection. The basic syntax is as follows:

The parenthesis can also be omitted.

Using the Add method will–by default–append the new element to the end (last index) of the collection. This can be overridden, however, using the After or Before parameters.

This will add the value after the 2nd element in the collection. One important thing to remember is that unlike traditional arrays, which start at zero, a collection’s index starts at 1.

This will add the value before the 3rd element in the collection.

Adding Custom Keys

The Collection Add method has a 3rd parameter: Key. This permits a custom keys to be assigned to individual elements in a collection. The syntax is as follows:

Only string values can be assigned as custom key values. Note that in the first example, the integer is surrounded in quotes, making it a string literal.

This is where the nuance between indexes and keys comes to play (see more details in array terminology). When no key is specified as part of a collection’s Add method, elements can only be referred to through their array index. However, if a custom key value is added, elements can be referred to through either their index or custom key.

What’s important to note here is that–under the hood–most collection actions rely on the collections index, not its key. With that being said, you cannot specify element placement in a collection using a custom key. For example, the following two examples will not work:

However, a custom key value will work with the Remove and Item methods. In instances where a custom key exists, either the index or key may be used. Examples will be provided below.


Also implied through its name, the Remove method will remove an element from a collection. The syntax is as follows:

This remove the 2nd element in the collection. A custom key can also be used to remove an element.

Upon removing an element, the index of all subsequent elements in the collection will automatically move up one.


The Item method will call the element value associated with a specific index or key. When using this method, the collection element must be assigned to a Variable, Range, Message Box, or other Object. The behavior of this method is similar to specifying a subscript within an array.

Like the Remove method, the Item method will also accept a custom key string.


The Count method simply returns a count of the total number of elements which currently exist in a collection.

The Count method provides great leverage–over arrays–with For loops and conditionals. Collections can be used when they’re initiated, even if they do not contain any elements. Empty arrays, on the other hand, will return a subscript error if a script tries to use the UBound() function or force it through a For Each loop. With a collection, utilizing the Count method on an empty Collection will return zero, making it very useful for a conditional statement to test if a collection is empty. Just as a side-note (unrelated to Count, but while on the topic), forcing an empty collection through a For Each loop will simply cause the script to skip the loop.

Printing Collections

Printing a collection generally follows the same principles of printing one-dimensional arrays. As mentioned in the previous section, collections provide some added benefits which can help avoid “subscript out of range” errors common with arrays.

Explicit Print

Entire collections can be printed by using the Item method, but you may be better off using a loop unless you only need to extract specific values. Regardless, the following is an example of a collection printout by explicitly calling each item.

Note that the 5th element is called with a custom key. Also note that even though a custom key is assigned to the 6th element, the script still uses the element’s index number to call.

For Loop

A simple For loop can print items within a collection.

Output from a simple Collection.One advantage of a For loop over its For Each counterpart (next section) is that you have control over starting the iteration through the collection at any point, or ending iteration at a specific point. In the example above, the Count method is used to iterate through the entire collection.

Note in the example that the index location of elements “Seven” and “Eight” are overridden, to be before 2, and after 4 respectively. The output is shown to the left. Even though this throws the visual count off, it proves a point about overriding element placement in a collection.

For Each Loop

A For Each loop is the preferred method when simply iterating through an entire Collection.

This example includes an increment counter, assisting the For Each loop with printing on a new row for each iteration.

Other Notes

  • Need to sort a collection? See our post on using bubble sort with collections and other VBA data structures.
  • It is possible to iterate through Collections using Do loops. While the basic behavior would be no different than their For loop counterparts, a Do loop permits the addition of other conditions, and post-tests.

Leave a Reply