请参阅下表，了解集合和词典的有用比较。
（该表总结了这一页 http://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html直到“早期和后期绑定”部分。仅供参考，该页面还有有关使用词典的更多详细信息）
总之，通常最好使用字典或数组。
在考虑使用集合时，如果大小不变或很少变化，则使用数组可能更合适。在这种情况下，数组可能比集合更有效，因为数组可以非常有效地一次性填充和检索所有项目（例如，范围到数组和数组返回范围）。
另请注意：
与数组相比，集合在添加和插入项目以及通过其键访问和删除项目方面提供了良好的性能。但是，如果通过索引访问项目，性能会很差。有关有效执行此操作的信息，请参阅here http://www.vbaccelerator.com/home/VB/Code/Techniques/A_Fast_Index-Based_Object_Collection/article.asp其中还讨论了这些列表对象的内部工作原理。
此 cpearson 页面 http://www.cpearson.com/excel/CollectionsAndDictionaries.htmhas 有非常有用的代码，用于处理字典、集合和数组（对它们进行排序，并将它们相互转换！）
cpearson 页面上的一些文字：
Collection 对象和 Dictionary 对象对于
存储相关数据组。在其他条件相同的情况下，我使用
Dictionary 对象而不是 Collection 对象，因为你有
访问（读、写、更改）与某个关联的 Key 属性
词典中的项目。在相当糟糕的对象设计中，
集合中的项目是只写的。您可以为项目分配一个键
当您将项目添加到集合中，但无法检索该项目时
与项目关联的键也不能（直接）确定是否
key 存在于集合中。词典非常友好和开放
用他们的钥匙。字典也比
收藏。
为什么数组是一个糟糕的选择。
数组在调整大小和在中间插入项目时要慢得多，因为每个 Redim 都会将整个内存块复制到更大的位置，并且如果使用 Preserve，所有值也会被复制。这可能会转化为每个操作的感知缓慢 - 在潜在的应用程序中）
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!
原图，信息更多，排列更清晰：
