VBA Collections
Remarks
A Collection is a container object that is included in the VBA runtime. No additional references are required in order to use it. A Collection can be used to store items of any data type and allows retrieval by either the ordinal index of the item or by using an optional unique key.
Feature Comparison with Arrays and Dictionaries
| Collection | Array | Dictionary | |
|---|---|---|---|
| Can be resized | Yes | Sometimes1 | Yes |
| Items are ordered | Yes | Yes | Yes2 |
| Items are strongly typed | No | Yes | No |
| Items can be retrieved by ordinal | Yes | Yes | No |
| New items can be inserted at ordinal | Yes | No | No |
| How to determine if an item exists | Iterate all items | Iterate all items | Iterate all items |
| Items can be retrieved by key | Yes | No | Yes |
| Keys are case-sensitive | No | N/A | Optional3 |
| How to determine if a key exists | Error handler | N/A | .Exists function |
| Remove all items | Iterate and .Remove | Erase, ReDim | .RemoveAll function |
1 Only dynamic arrays can be resized, and only the last dimension of multi-dimensional arrays.
2 The underlying .Keys and .Items are ordered.
3 Determined by the .CompareMode property.
