VBA Collections

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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

CollectionArrayDictionary
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.



Got any VBA Question?