Collections in VBA – Overview

March 27, 2013 in VBA

In VBA programming collections of all types are indispensable. For both the new and experienced VBA programmer there is a lot to know about collection types in VBA, so I’m going to cover them one by one. In this post I’ll provide a quick survey of the available collection types.

Types of Collections in VBA

There are three fundamental collection types in VBA

  • Array
  • Collection
  • Dictionary

Arrays are a language feature and use value-type semantics (they do not use the Set keyword for assignments, for example). Collections and Dictionaries are object types provided by the Visual Basic for Applications library (VBEx.dll) and the Microsoft Scripting Runtime (scrrun.dll), respectively. A reference to the VBA library is always required in VBA projects, so the Collection type is always available. To use a Dictionary you must add a reference to the Microsoft Scripting Runtime, or use late binding and obtain your Dictionary objects with CreateObject.

Common Attributes

All three of these collection types have three attributes in common:

Container

They contain other items. The gives you the ability to refer to a bunch of items at once, and to define some relationships between the items. Obvious, I know, but important!

Associative

They associate keys or indexes with values. The data type of both the keys and the values differ amongst the collection types, but they all use index accessor syntax to get the values from the keys. To obtain an element from any of these collections, you follow the variable name or expression directly with the key or index in parenthesis:

vItem = vArray(0)
vItem = oCollection(1)
vItem = oDictionary("some key")
Enumerable

Their contents can be iterated over using the For Each In construct. (Although for Dictionaries you get the keys rather than the values).

Differing Attributes

In other attributes, they differ:

Value- vs reference-type semantics

In VBA (and unlike most other languages) Arrays are value types. Syntactically this means you assign arrays just like primitive data types, with no Set keyword required. More importantly, this means that whenever you assign an array or pass an array by value to a method (using ByVal), VBA copies the entire array! This can lead to hard-to-find bugs and performance slowdowns if you are not careful. Dictionary and Collection type objects are reference types (Object types in VBA parlance), so the Set keyword is required for assignments, and assignments or passing parameters ByVal simply makes a copy of the reference to the same underlying object.

Data type of keys

Arrays have only one key type: integers. Array keys are usually called indexes. By default array indexes are 0-based, but arrays in VBA can have any lower bound — even a negative one! Collections support two keys for every item — both an integer index and a string key. Collection indexes (the integer keys) are always 1-based, and string keys are optional. Finally, Dictionaries can have keys of any type, including object references, but do not have integer indexes.

Data type of values

Both Collections and Dictionaries can contain values of any type. There is no way to restrict this, although you can build your own wrapper types to do type checking for you. Arrays can be either strongly typed or variant. That is, you can declare an array than can have elements of any type, or an array whose elements are restricted to being a specific type.

Fixed-Length

Collections and Dictionaries are never fixed length, and it is very easy to add and remove elements. Arrays can be defined as fixed length or “dynamic” (meaning the length can change). Changing the length of dynamic arrays requires a little effort using statements like UBound and Redim.

Ordered

Collection and Array types have an intrinsic order. This is important when using a For Each In loop where the order of elements matters. For Arrays the order is defined by the index you must always use to get or set an element. For Collections the order of elements is determined by the order in which they were added using the Add method, and is reflected in the 1-based integer indexes of the Collection. Dictionaries, in contrast, have no intrinsic order. When you obtain the list of keys or values by using a For Each In loop or via the Keys or Items property, you have no guarantee of which order the elements will be provided to you.

Extensibility

There is no way to extend a value type, so when it comes to Arrays you can do exactly what the VBA language provides and no more (thankfully that is quite a lot). But Collections and Dictionaries are object types and you can use them to build your own custom collection types. This is must often done using the Collection object as a base and a nifty little trick for enabling enumeration directly over your own custom collections (to be covered later). Using this, you can create your own object models.

Conclusion

There are three varieties of collection types in VBA: Array, Collection, and Dictionary. They are all containers holding other items which support index accessor syntax and For Each In enumeration. They differ in the data types of the keys or indexes used, in whether they are value- or reference-types, and in the ability to restrict or define the data type, length (count), and order of the elements they contain.