Please see the table below for a useful comparison of collections and dictionaries.
(The table summarises this page up to the section on "Early And late binding". FYI the page also has more detailed info about using dictionaries)
In summary it's usually best to use a dictionary or an array.
When considering using collections it may be more appropriate to use an array if the size does not change, or changes only rarely. In this case an array is likely to be more efficient than a collection as Arrays?are very efficient to populate and retrieve all items at once (eg. range to array and array back to range).
Also note:
Compared to Arrays, collections offer good performance for adding and inserting items, and accessing and removing them by their Keys. However, performance is poor if items are to be accessed by index. For information about doing this efficiently see here which also discusses the inner workings of these list objects.
This cpearson page has has very useful code for working with dictionaries, collections and arrays (sorting them, and also converting them to be each other!)
Some text from cpearson's page:
The Collection object and the Dictionary object are very useful for
storing groups of related data. All else being equal, I use a
Dictionary object rather than a Collection object because you have
access (read, write, change) to the Key property associated with an
Item in the Dictionary. In a rather poor object design, the Key of an
item in a Collection is write-only. You can assign a Key to an Item
when you add the Item to the Collection, but you cannot retrieve the
Key associated with an Item nor can you determine (directly) whether a
key exists in a Collection. Dictionaries are much friendly and open
with their keys. Dictionaries are also considerably faster than
Collections.
Why can arrays be a bad choice.
Arrays are much slower at re-sizing and inserting items in the middle as each Redim copies the entire memory block to a larger location, and if Preserve is used, all values copied over as well. This may translate to perceived slowness for every operation - in a potential application)
Collections vs. Dictionaries in VBA
Feature | COLLECTION | DICTIONARY | Remark
------------------------+------------+------------+--------------------------------
Usually faster | | X |
------------------------+------------+------------+--------------------------------
Supported by VB Script | | X | Collections do not exist in VBS.
------------------------+------------+------------+--------------------------------
| | | Dicts: Add ref to Miscrosoft
Native to VBA | X | | Scripting Library. Usage:
| | | Dim MyDict As Scripting.Dictionary
| | | Set MyDict = New Scripting.Dictionary
------------------------+------------+------------+--------------------------------
Can change Keys and | | | Dict properties are writable.
Items | | X | For collections, remove the item
| | | and add a new item.
------------------------+------------+------------+--------------------------------
| | | A collection enumerates its items:
| | | For Each x In MyCollection
| | | Debug.Print x
Enumerated | X | X | Next x
| | | A dict enumerates its keys:
| | | For Each x In MyDictionary
| | | Debug.Print MyDictionary.Item(x)
| | | Next x
------------------------+------------+------------+--------------------------------
| | | A 1-d array of keys
Directly output to | | | and items can be returned by
array | | X | dict methods .Keys and .Items.
| | | (The array is zero-based even
| | | with Option Base 1.)
------------------------+------------+------------+--------------------------------
Retrieve and access | X | X |
items | | |
------------------------+------------+------------+--------------------------------
Add items | X | X |
------------------------+------------+------------+--------------------------------
Implicitly add items | | X | Dicts can implicitly add items
| | | using .Item property.
------------------------+------------+------------+--------------------------------
Remove items | X | X |
------------------------+------------+------------+--------------------------------
Remove all items in | | | With collections, each item must
one step | | X | be removed in turn, or the
| | | collection destroyed and recreated.
------------------------+------------+------------+--------------------------------
Count items | X | X |
------------------------+------------+------------+--------------------------------
Return item using key | X | X |
as lookup value | | |
------------------------+------------+------------+--------------------------------
Return item using | | |
ordinal position | X | (Slow) |
as lookup value | | |
------------------------+------------+------------+--------------------------------
Return ordinal | | |
position using item | X | ?? |
as lookup value | | |
------------------------+------------+------------+--------------------------------
Retrieve and access | | X | Collection keys only used to
keys | | | look up data, not retrievable.
------------------------+------------+------------+--------------------------------
Keys optional | X | | Big + of collections, assuming keys
| | | are not needed. (Access via index.)
------------------------+------------+------------+--------------------------------
Case sensitivity | | X |
optional | | |
------------------------+------------+------------+--------------------------------
| | | Collection keys must be strings.
Keys can be any type | | X | Dict keys can have any type
| | | (except arrays), incl. mixed types.
------------------------+------------+------------+--------------------------------
Keys must be unique | X | X |
------------------------+------------+------------+--------------------------------
| | | * For collections, add code:
| | | Public Function _
| | | Contains(col As Collection, _
Supports .Exists method | Remark* | X | key As Variant) As Boolean
| | | On Error Resume Next
| | | col(key)
| | | Contains = (Err.Number = 0)
------------------------+------------+------------+--------------------------------
Preserve key order when | | X | This is because collection keys
sorting by item value | | | are write-only, not read. Poor design!
The original image, which has more information and is more clearly arranged:
