Answers for "excel vba collection vs dictionary"

excel vba collection vs dictionary

Feature                 | COLLECTION | DICTIONARY | Remark
------------------------+------------+------------+--------------------------------
Usually faster          |            |     X      | 
------------------------+------------+------------+--------------------------------
                        |            |            | 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.)
------------------------+------------+------------+--------------------------------
Implicitly add items    |            |     X      | Dicts can implicitly add items 
                        |            |            | using .Item property.
------------------------+------------+------------+--------------------------------
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       |            |            |
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.
------------------------+------------+------------+--------------------------------
                        |            |            | * 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)