Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

excel - Comparison of Dictionary, Collections and Arrays

I am trying to work out the relative benefits and features of dictionaries compared with collections and arrays.

I found an excellent article here but can't find a simple table that compares all the various features.

Does anyone know of one?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:

Comparison table image


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...