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?
Comparison of Dictionary, Collections and Arrays
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:
Great research! I have a few more related links here, maybe it will help –
Backwash
thanks for the feedback and reading it. I found the links you provided useful and have incorporated some of them and some of your text) into my answer. –
Amphisbaena
@Paul your comment elsewhere covers a lot of what I wrote, but what I like about the info in a table, is that it gives info at a glance and really allows you to more easily choose whether to use a collection or not. –
Amphisbaena
My favorite feature of Dicts is that I can output the values or keys as an array. If this table were not an image, I would have added that feature, but alas... –
Gagliano
@Dick I've added this for you. Is there anything else you want added or any mistakes you notice? (I've restructured the table) PS. I tried to reformat my OneNote table to insert it as a table into the answer and couldn't work out how it could be done! (Any ideas) –
Amphisbaena
You may be missing that using Dictionary requires setting a reference while Collection is native to Access. –
Tacmahack
@Gustav. Thanks. I've added it and reworded the above text to point people towards this. –
Amphisbaena
That's why I like your research - short, visual summary, and easy to compare! I always forget about the advantage that @Dick mentioned - it's can be very effective; I'll have to review it in more details see if I can remember anything else - it's a great reference to have –
Backwash
Thanks @Paul. there is so much to remember, I rely on creating crib sheets like this in Onenote (often with screen grabs using snagit). I'm glad I shared it now. I'm generally a bit frustrated that a lot of documentation and help is not more consice and comparative. –
Amphisbaena
It's covering quite a few aspects! I'm not sure about this but for dictionaries it may not be possible to retrieve the the item using an index (for collections it is), but dictionaries have the array feature, so an indirect index retrieval might be acceptable. You should probably make this more dynamic (easier to update, somehow) instead of a picture - I want to keep it as a quick reference (good thing you shared it!) –
Backwash
As I see it the two significant advantages of collections are: 1. You can access and item using it's index. 2: You need not populate the key. So they're good for lots of scenarios with objects where you just want to keep a collection of object references and loop through them... Am I right? –
Amphisbaena
You're right - some of the obvious ones, but this is why it's good to have this kind of list: to compare advantages for scenarios that are not that common. I'll add an answer with some more basic details and differences –
Backwash
Let us continue this discussion in chat. –
Amphisbaena
Thanks for this useful summary. I've attempted to edit and replace the screenshot with a table. –
Perfidy
One more difference from docs.microsoft "if you're creating a custom object model, you can use a Collection object to store a reference to a custom collection, but you can't use a Dictionary object to do this." –
Perfidy
I'm not entirely sure how to interpret the statement quoted in my previous comment, but since I learned the technique of representing a collection with a custom class from this great book (p. 677) by @DickKusleika, I thought maybe you could shed some additional light on this? –
Perfidy
First point isn't correct in case of adding, deleting or searching..Dictionary perform way more worst than collection. If you want to see the comparison then please see this >> facebook.com/… –
Diencephalon
Very useful comparison @HarveyFrench. I'd add near the top Win/Mac support. Since a reference is required for Dictionary it's not available on macOS out of the box but there is a GitHub Dictionary class which is a drop in replacement: github.com/VBA-tools/VBA-Dictionary –
Denys
Option Explicit
Sub CollectionsVSdictionaries() ' Requires ref to "Microsoft Scripting Runtime" Library
Dim c As Collection ' TypeName 1-based indexed
Dim d As Dictionary ' 0-based arrays
Set c = New Collection ' or: "Dim c As New Collection"
Set d = New Dictionary ' or: "Dim d As New Dictionary"
c.Add Key:="A", Item:="AA": c.Add Key:="B", Item:="BB": c.Add Key:="C", Item:="CC"
d.Add Key:="A", Item:="AA": d.Add Key:="B", Item:="BB": d.Add Key:="C", Item:="CC"
Debug.Print TypeName(c) ' -> "Collection"
Debug.Print TypeName(d) ' -> "Dictionary"
Debug.Print c(3) ' -> "CC"
Debug.Print c("C") ' -> "CC"
'Debug.Print c("CC") ' --- Invalid ---
Debug.Print d("C") ' -> "CC"
Debug.Print d("CC") ' Adds Key:="CC", Item:=""
Debug.Print d.Items(2) ' -> "CC"
Debug.Print d.Keys(2) ' -> "C"
Debug.Print d.Keys()(0) ' -> "A" - Not well known ***************************
Debug.Print d.Items()(0) ' -> "AA" - Not well known ***************************
'Collection methods:
' .Add ' c.Add Item, [Key], [Before], [After] (Key is optional)
' .Count
' .Item(Index) ' Default property; "c.Item(Index)" same as "c(Index)"
' .Remove(Index)
'Dictionary methods:
' .Add ' d.Add Key, Item (Key is required, and must be unique)
' .CompareMode ' 1. BinaryCompare - case-sensitive ("A" < "a")
' .CompareMode ' 2. DatabaseCompare - MS Access only
' .CompareMode ' 3. TextCompare - case-insensitive ("A" = "a")
' .Count
' .Exists(Key) ' Boolean **********************************************
' .Item(Key)
' .Items ' Returns full array: .Items(0)(0)
' .Key(Key)
' .Keys ' Returns full array: .Keys(0)(0)
' .Remove(Key)
' .RemoveAll ' ******************************************************
End Sub
Thanks Paul - thats a useful resource. –
Amphisbaena
With regards to the performance of collections versus dictionaries, I am finding that writing to dictionaries performs similarly to writing to collections, reading from a dictionary takes about twice as long as reading from a collection. Creating a dictionary in the first place is way slower than creating a collection.
These are results I got for doing 100,000 iterations of reading from, writing to, and creating dictionaries/collections:
Creating Multiple Dictionaries: 731ms
Writing To Dictionary: 494ms
Reading From Dictionary: 65ms
Creating Multiple Collections: 29ms
Writing To Collection: 459ms
Reading From Collection: 26ms
Note adding a reference to the Microsoft Scripting Runtine improves the speed of creating multiple dictionaries (to 495ms here).
This is the code I used for testing this:
Option Explicit
Private p_lngTestCount As Long
Sub SetUp()
p_lngTestCount = 100000
End Sub
Sub TestAll()
CreatingMultipleDictionaries
WritingToDictionary
ReadingFromDictionary
CreatingMultipleCollections
WritingToCollection
ReadingFromCollection
End Sub
Sub CreatingMultipleDictionaries()
Const sSOURCE As String = "CreatingMultipleDictionaries"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim dcTest As Dictionary
SetUp
Dim dblTimeElapsed As Double
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
'Set dcTest = CreateObject("Scripting.Dictionary")
Set dcTest = New Dictionary
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Sub CreatingMultipleCollections()
Const sSOURCE As String = "CreatingMultipleCollections"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim colTest As Collection
SetUp
Dim dblTimeElapsed As Double
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
Set colTest = New Collection
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Sub WritingToDictionary()
Const sSOURCE As String = "WritingToDictionary"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim dcTest
SetUp
Set dcTest = CreateObject("Scripting.Dictionary")
'Set dcTest = New Dictionary
Dim dblTimeElapsed As Double
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
' Performance about the same for both ways:
dcTest.Item(CStr(i)) = "test"
'dcTest.Add CStr(i), "test"
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Sub WritingToCollection()
Const sSOURCE As String = "WritingToCollection"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim colTest As Collection
SetUp
Dim dblTimeElapsed As Double
Set colTest = New Collection
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
colTest.Add "test", CStr(i)
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Sub ReadingFromDictionary()
Const sSOURCE As String = "ReadingFromDictionary"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim dcTest
SetUp
Set dcTest = CreateObject("Scripting.Dictionary")
'Set dcTest = New Dictionary
dcTest.Add "key", "test"
Dim stTest As String
Dim dblTimeElapsed As Double
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
stTest = dcTest.Item("key")
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Sub ReadingFromCollection()
Const sSOURCE As String = "ReadingFromCollection"
Dim oPerfMon As CDevPerformanceMonitor
Set oPerfMon = New CDevPerformanceMonitor
Dim i As Long
Dim colTest As Collection
SetUp
Dim stTest As String
Dim dblTimeElapsed As Double
Set colTest = New Collection
colTest.Add "test", "key"
oPerfMon.StartCounter
For i = 0 To p_lngTestCount
stTest = colTest.Item("key")
Next i
dblTimeElapsed = oPerfMon.TimeElapsed
Debug.Print sSOURCE & ": " & p_lngTestCount & " iterations. " & vbCrLf & _
"Time elapsed: " & Round(dblTimeElapsed, 0) & "ms" & vbCrLf
End Sub
Performance monitor class (CDevPerformanceMonitor):
Option Explicit
' Performance monitoring used in logging
' See: https://mcmap.net/q/203692/-how-do-you-test-running-time-of-vba-code
Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type
#If VBA7 Then
Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
#Else
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
#End If
#If VBA7 Then
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
#Else
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
#End If
Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double
Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#
Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
Low = LI.lowpart
If Low < 0 Then
Low = Low + TWO_32
End If
LI2Double = LI.highpart * TWO_32 + Low
End Function
Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
QueryPerformanceFrequency PerfFrequency
m_crFrequency = LI2Double(PerfFrequency)
End Sub
Public Sub StartCounter()
QueryPerformanceCounter m_CounterStart
End Sub
Public Function PerformanceCount() As Double
Dim liPerformanceCount As LARGE_INTEGER
QueryPerformanceCounter liPerformanceCount
PerformanceCount = LI2Double(liPerformanceCount)
End Function
Public Function MicroTime() As Double
MicroTime = Me.PerformanceCount * 1000000# / m_crFrequency
End Function
Public Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
QueryPerformanceCounter m_CounterEnd
crStart = LI2Double(m_CounterStart)
crStop = LI2Double(m_CounterEnd)
TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property
Option Explicit
Sub UpdateSummary()
Dim varData
Dim objDicCountry As Object
Dim objDicCity As Object
Dim objDicData As Object
Dim lngR As Long
Dim varResult
Dim lngC As Long
Dim strKey As String
Dim varUnique
varData = Sheet12.Range("A1").CurrentRegion
Set objDicCity = CreateObject("Scripting.Dictionary")
Set objDicCountry = CreateObject("Scripting.Dictionary")
Set objDicData = CreateObject("Scripting.Dictionary")
For lngR = LBound(varData) + 1 To UBound(varData)
strKey = varData(lngR, 1) '--Country
objDicCountry.Item(strKey) = ""
strKey = varData(lngR, 2) '--City
objDicCity.Item(strKey) = ""
strKey = varData(lngR, 1) & "|" & varData(lngR, 2) '--Country and City
objDicData.Item(strKey) = objDicData.Item(strKey) + varData(lngR, 3)
Next lngR
ReDim varResult(1 To objDicCountry.Count + 1, 1 To objDicCity.Count + 1)
varUnique = objDicCountry.keys '--get Unique Country
For lngR = LBound(varUnique) To UBound(varUnique)
varResult(lngR + 2, 1) = varUnique(lngR)
Next lngR
varUnique = objDicCity.keys '--get Unique City
For lngC = LBound(varUnique) To UBound(varUnique)
varResult(1, lngC + 2) = varUnique(lngC)
Next lngC
For lngR = LBound(varResult) + 1 To UBound(varResult)
For lngC = LBound(varResult) + 1 To UBound(varResult, 2)
strKey = varResult(lngR, 1) & "|" & varResult(1, lngC) '--Country & "|" & City
varResult(lngR, lngC) = objDicData.Item(strKey)
Next lngC
Next lngR
Sheet12.Range("F6").Resize(UBound(varResult), UBound(varResult, 2)).Value = varResult
MsgBox "Done", vbInformation
End Sub
Thanks for wanting to contribute to Stack Overflow. The OP was asking for a simple table that compares the various features. Posting an answer with a bunch of code without an explanation on how it relates to the question isn't very helpful. –
Limit
© 2022 - 2024 — McMap. All rights reserved.