VBA Collections


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

Can be resizedYesSometimes1Yes
Items are orderedYesYesYes2
Items are strongly typedNoYesNo
Items can be retrieved by ordinalYesYesNo
New items can be inserted at ordinalYesNoNo
How to determine if an item existsIterate all itemsIterate all itemsIterate all items
Items can be retrieved by keyYesNoYes
Keys are case-sensitiveNoN/AOptional3
How to determine if a key existsError handlerN/A.Exists function
Remove all itemsIterate and .RemoveErase, 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.