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
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
All three of these collection types have three attributes in common:
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!
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")
Their contents can be iterated over using the
For Each In construct. (Although for Dictionaries you get the keys rather than the values).
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.
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
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
Items property, you have no guarantee of which order the elements will be provided to you.
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.
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.