VBA Dictionary Look up
Looking up items
Dictionaries have been designed to allow for easy (and fast) lookups of items. Lookups are based the keys of the key/item pairs.
1 2 3 4 | Dim D As New Dictionary D.Add "Key", "Item" MsgBox D.Item("Key") |
If the items are of the type dictionary, lookups can be nested. This feature is used a lot in the context of JSON deserialization.
1 2 | MsgBox D.Item("Country").Item("Adress").Item("Street") |
When the keys are of the type string, keep in mind the compare mode.
1 2 3 | MsgBox D.Item("Key") MsgBox D.Item("KEY") 'might give a different result |
Test for existence of a key
It is good practice to check if the key exists, before you look up the item.
1 2 3 4 5 6 | If (D.Exists("Key")) Then MsgBox D.Item("Key") Else MsgBox "Cannot find ""Key"" in dictionary." End If |
If you try to get an item of a key that does not exist, you will not error. The item will be empty.
1 2 3 4 | D.Add "Key1", "Item1" MsgBox D.Item("Key1") 'shows Item1 MsgBox D.Item("Key2") 'shows empty string! |
It is possible to test if the item is empty by using the function IsEmpty.
1 2 3 4 | If (IsEmpty(D.Item("Key2"))) Then MsgBox "Item is empty" 'shows Item is empty End If |
In the context of JSON text deserialization, safe coding comes at the cost of elegance. Safe coding turns the elegant
1 2 | D.Item("A").Item("B").Item("C") |
into the not so-elegant but safer
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | If (D.Exists("A")) Then If (D.Item("A").Exists("B")) Then If (D.Item("A").Item("B").Exists("C")) Then MsgBox D.Item("A").Item("B").Item("C") Else 'handle error End If Else 'handle error End If Else 'handle error End If |
It can be a pain to come up with clear error messages for every case.
The type of the item
An item can be of any VBA type. When assigning the item to a VBA variable: the types must match. If not, you will get a run time error.
1 2 3 4 5 | Dim S As String S = D.Item("Key") Dim I As Integer I = D.Item("Key") 'this will generate an error |
Sometimes, you might not know the VBA type of the item upfront. In such a case, the VBA function VarType can be used to check the VBA type of the item.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | Dim S As String S = "abc" Dim Dt As Date Dt = DateSerial(2016, 6, 19) Dim B As Boolean B = False Dim Bt As Byte Bt = 12 Dim I As Integer I = 1234 Dim L As Long L = 12345678 Dim Sgl As Single Sgl = 1.23456 Dim Dbl As Double Dbl = 1.2345678 Dim Curr As Currency Curr = 1.234 Dim V As Variant Dim O As Object Set O = CreateObject("Scripting.Dictionary") MsgBox VarType(S) 'shows 8 MsgBox VarType(Dt) 'shows 7 MsgBox VarType(B) 'shows 11 MsgBox VarType(I) 'shows 2 MsgBox VarType(L) 'shows 3 MsgBox VarType(Sgl) 'shows 4 MsgBox VarType(Dbl) 'shows 5 MsgBox VarType(Curr) 'shows 6 MsgBox VarType(V) 'shows 0 MsgBox VarType(O) 'shows 9 |
If the item is an object, the function VarType can tell you, but, it cannot tell you which kind of object. For this, you can use the VBA function TypeName or the operator TypeOf...Is.
1 2 3 | MsgBox TypeName(O) 'shows Dictionary If TypeOf O Is Dictionary Then MsgBox ("Dictionary") 'shows Dictionary |
If the item is of the type dictionary, the following code works.
1 2 3 4 5 6 7 8 9 10 11 | Dim D3 As New Dictionary D3.Add "C", "Item" Dim D2 As New Dictionary D2.Add "B", D3 Dim D1 As New Dictionary D1.Add "A", D2 MsgBox D1.Item("A").Item("B").Item("C") 'shows Item |
In the context of JSON deserialization, you will probably use code like this to extract information from the dictionary. As all keys are strings, you just need to know the sequence of keys that leads to the item you need. In a dictionary that is the result of the deserialization of a JSON text, all items can be looked up in this way. If the dictionary is the result of a JSON deserialization, only the following types are possible for an item.
Looping
It is possible to loop through a dictionary. You can loop through the keys and through the items. Take care to manage the possible different VBA types of the keys and items.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Dim D As New Dictionary D.Add "Key", "Item" D.Add -1, 66.66 D.Add Now(), False Dim Key As Variant For Each Key In D.Keys MsgBox Key & " : " & D.Item(Key) Next Key Dim Item As Variant For Each Item In D.Items MsgBox Item Next Item |
Looping is useful when you are copying a dictionary.
It is possible to output all keys or all items to an array of the type variant. The array is one dimensional and zero based (always, even if you use the option Base 1).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Dim D1 As New Dictionary D1.Add "A1", "Item1" Dim D As New Dictionary D.Add "Key", "Item" D.Add -1, 66.66 D.Add Now(), False D.Add "D1", D1 Dim V1 As Variant V1 = D.Keys() Dim V2 As Variant V2 = D.Items() |
Because of this feature, there is an alternative way to loop through a dictionary.
1 2 3 4 5 6 | Dim Index As Long Index = 0 For Index = 0 To D.Count - 1 MsgBox D.Keys(Index) & " : " & D.Items(Index) Next Index |