excel - 字典、集合和数组的比较

Question

我正在尝试找出字典与集合和数组相比的相对优势和功能。

我发现了一篇很棒的文章here但找不到一个简单的表格来比较所有不同的功能。

有人知道吗？

Answer 1

请参阅下表，对集合和字典进行有用的比较。

(该表总结了 this page 直至“早期和后期绑定(bind)”部分。仅供引用，该页面还包含有关使用词典的更多详细信息)

总之，通常最好使用字典或数组。

在考虑使用集合时，如果大小不变或很少变化，则使用数组可能更合适。在这种情况下，数组可能比集合更有效，因为数组可以非常有效地一次性填充和检索所有项目(例如，范围到数组和数组返回范围)。

另请注意:

与数组相比，集合在添加和插入项目以及通过其键 Access 和删除项目方面提供了良好的性能。但是，如果通过索引 Access 项目，性能会很差。有关有效执行此操作的信息，请参阅 here其中还讨论了这些列表对象的内部工作原理。

This cpearson page has 有非常有用的代码，用于处理字典、集合和数组(对它们进行排序，并将它们相互转换!)

来自 cpearson 页面的一些文字:

The Collection object and the Dictionary object are very useful forstoring groups of related data. All else being equal, I use aDictionary object rather than a Collection object because you haveaccess (read, write, change) to the Key property associated with anItem in the Dictionary. In a rather poor object design, the Key of anitem in a Collection is write-only. You can assign a Key to an Itemwhen you add the Item to the Collection, but you cannot retrieve theKey associated with an Item nor can you determine (directly) whether akey exists in a Collection. Dictionaries are much friendly and openwith their keys. Dictionaries are also considerably faster thanCollections.

为什么数组是一个糟糕的选择。数组在调整大小和在中间插入项目时要慢得多，因为每个 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!

原图，信息更多，排列更清晰: