Dictionary object
2020-07-01 VBA programming 0 687
A dictionary is an object where you can store items of any datatype, and unlike arrays you don't have to set the size of how many items you want to store in the dictionary.
Unlike the collection object, each item in the dictionary object must be associated with a unique key identificator.
More information about array variables.
More information about the Collection object.
The dictionary object:
- Can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays)
- Each item must be associated with a unique key, usually this is a string or integer, can't be an array
- The associated key for each item can be retrieved, e.g. when looping through the dictionary items
- Usually you would only add items of the same datatype to the dictionary
- It is possible to add items of mixed datatypes to the dictionary
- Items can not be added before or after existing items in the dictionary
- Looping through a dictionary is as fast as a collection when using For Each - Next with the key or item
- Looping through a dictionary is a bit slower than a collection when using For Each - Next with the key and getting the item
Below is some code examples on how to create a dictionary object, populate it with items and then retrieving one or more items:
Sub Example_Dictionary() ' updated 2020-06-26 by OPE Dim dict As Object, strKey As String, varKey As Variant, varItem As Variant, i As Long, lngValue As Long, objEmployee As clsEmployee ' dictionary object: ' can store items of any datatype (e.g. string, long, worksheet, object, variant, arrays) ' each item must be associated with a unique key, usually this is a string or integer, can't be an array ' the associated key for each item can be retrieved, e.g. when looping through the dictionary items ' usually you would only add items of the same datatype to the dictionary ' it is possible to add items of mixed datatypes to the dictionary ' items can not be added before or after existing items in the dictionary ' looping through a dictionary is as fast as a collection when using For Each - Next with the key or item ' looping through a dictionary is a bit slower than a collection when using For Each - Next with the key and getting the item 'Set dict = New Dictionary ' create a new dictionary object (using early binding, requires a reference to the Microsoft Scripting Runtime library) Set dict = CreateObject("Scripting.Dictionary") ' create a new dictionary object (using late binding, no extra references are necessary) dict.CompareMode = 1 ' dictionary must be empty, 0=vbBinaryCompare (default), 1=vbTextCompare, 2=vbDatabaseCompare, -1=vbUseCompareOption ' add items to a dictionary For i = 1 To 10 strKey = CStr(i) ' the key for the item to add/edit dict.Add strKey, i * 100 ' add a new item with a key, an error will occur if the item already exists Next i ' add items to a dictionary For i = 1 To 10 strKey = CStr(i) ' the key for the item to add/edit dict(strKey) = i * 100 ' add a new item with a key, if the item already exists it will be updated with the new value Next i ' add items to a dictionary For i = 1 To 10 strKey = CStr(i) ' the key for the item to add/edit If dict.Exists(strKey) Then ' check if item already exist dict(strKey) = dict(strKey) + 1 ' edit the existing item value Else 'dict.Add strKey, 1 ' add the new item and set the initial value, an error will occur if the item already exists dict(strKey) = 1 ' add a new item with a key, if the item already exists it will be updated with the new value End If Next i ' items in a dictionary must be added in the desired order, they can't be added before or after other existing items dict(111) = 111 ' add a new item with a key, if the item already exists it will be updated with the new value dict(222) = 222 ' add a new item with a key, if the item already exists it will be updated with the new value dict("333") = 333 ' add a new item with a key, if the item already exists it will be updated with the new value dict("444") = 444 ' add a new item with a key, if the item already exists it will be updated with the new value ' remove items from the dictionary dict.Remove "9" ' remove item from dictionary using a key identifier (can't be an "index" number) dict.Remove 222 ' remove item from dictionary using a key identifier (can't be an "index" number) dict.Remove "444" ' remove item from dictionary using a key identifier (can't be an "index" number) ' use items in the dictionary Debug.Print "Count of items in the dictionary: " & dict.Count Debug.Print "This is the value of the item for key ""1"": " & dict("1") Debug.Print "This is the value of the item for key ""111"": " & dict(111) Debug.Print "This is the value of the item for key ""333"": " & dict("333") ' update items in the dictionary dict("333") = dict("333") + 1 ' you can change the dictionary items ' if the dictionary item is an object you can change properties of the object (see example below) ' loop through the dictionary Debug.Print "Dictionary: " & dict.Count ' using a For Each - Next loop is the preferred and significantly fastest method for looping through all items in a dictionary if the dictionary has many items ' if you need the item value only: i = 0 For Each varItem In dict.Items ' varItem must be a Variant i = i + 1 Debug.Print i, varItem ' item "index", item value Next varItem ' if you need the key value only: i = 0 For Each varKey In dict.Keys ' varKey must be a Variant i = i + 1 Debug.Print i, varKey ' item "index", key value Next varKey ' if you need both the key and item value: i = 0 For Each varKey In dict.Keys ' varKey must be a Variant i = i + 1 Debug.Print i, varKey, dict(varKey) ' item "index", item key and item value Next varKey ' using a For Next loop can be extremely slow to loop through all items in a dictionary with many items (10000 or more) For i = LBound(dict.Keys) To UBound(dict.Keys) Debug.Print i, dict.Keys()(i), dict.Items()(i) ' item "index", item key and item value Next i ' write the dictionary content to a worksheet ' assumes that the items are normal datatypes, no objects or arrays With Sheet1 On Error Resume Next .Range("A3:B" & .Rows.Count).Clear .Range("A3").Formula = "Keys" .Range("B3").Formula = "Items" .Range("A4").Resize(dict.Count, 1).Formula = Application.Transpose(dict.Keys) ' one-dimensional array, needs to be transposed .Range("B4").Resize(dict.Count, 1).Formula = Application.Transpose(dict.Items) ' one-dimensional array, needs to be transposed With .Range("A3").CurrentRegion .Style = "Borders" .Rows(1).Style = "Tableheader" .EntireColumn.AutoFit End With On Error GoTo 0 End With ' remove all items in a dictionary (usually not necessary) dict.RemoveAll Set dict = Nothing ' or simply delete the dictionary ' or create a new dictionary 'Set dict = New Dictionary ' create a new dictionary object (using early binding, requires a reference to the Microsoft Scripting Runtime library) Set dict = CreateObject("Scripting.Dictionary") ' create a new dictionary object using late binding dict.CompareMode = 1 ' dictionary must be empty, 0=vbBinaryCompare (default), 1=vbTextCompare, 2=vbDatabaseCompare, -1=vbUseCompareOption Set objEmployee = New clsEmployee ' create a new object and add values to it With objEmployee .ID = 1 .Name = "Peder Aas" .Position = "Manager" .Salary = 100000 .StartDate = DateSerial(2000, 1, 1) End With Set dict(objEmployee.ID) = objEmployee ' add a new item or update an existing one, the use of a key is mandatory Set objEmployee = New clsEmployee ' create a new object and add values to it With objEmployee .ID = 2 .Name = "Lars Holm" .Position = "Supervisor" .Salary = 125000 .StartDate = DateSerial(2001, 1, 1) End With Set dict(objEmployee.ID) = objEmployee ' add a new item or update an existing one, the use of a key is mandatory ' loop through the dictionary Debug.Print "Dictionary: " & dict.Count ' looping using the items is just as fast as looping through a collection For Each varItem In dict.Items ' varItem must be a variant varItem.DebugPrint ' assumes that the object has a DebugPrint method With varItem ' update object in dictionary .Salary = .Salary * 1.05 .EndDate = DateSerial(Year(Date), 12, 31) End With Next varItem ' looping using the keys is a little bit slower than looping through a collection if you need to get the corresponding item too For Each varKey In dict.Keys ' varKey must be a variant dict(varKey).DebugPrint ' assumes that the object has a DebugPrint method With dict(varKey) ' update object in dictionary .Salary = .Salary * 1.05 .EndDate = DateSerial(Year(Date), 12, 31) End With Next varKey Debug.Print "After update:" For Each varItem In dict.Items varItem.DebugPrint ' assumes that the object has a DebugPrint method Next varItem ' add some items of another datatype to the dictionary For i = 1 To 3 dict.Add CStr(i), i * 100 Next i ' loop through a dictionary with mixed data types Debug.Print "Dictionary with mixed datatypes: " & dict.Count ' looping using the items is just as fast as looping through a collection i = 0 For Each varItem In dict.Items i = i + 1 If TypeName(varItem) = "clsEmployee" Then varItem.DebugPrint Else Debug.Print "Not an employee: " & i, varItem, "TypeName: " & TypeName(varItem) End If Next varItem ' looping using the keys is a little bit slower than looping through a collection if you need to get the corresponding item too i = 0 For Each varKey In dict.Keys i = i + 1 If TypeName(dict(varKey)) = "clsEmployee" Then dict(varKey).DebugPrint Else Debug.Print "Not an employee: " & i, dict(varKey), "TypeName: " & TypeName(dict(varKey)) End If Next varKey End Sub