VBA Dictionary Fill
A dictionary must first be filled with key/item pairs. When the object reference is assigned, it contains zero key/item pairs.
1 2 3 4 5 | D.Add "Key1", "Item1" D.Add 2, 3.141592 D.Add 9.99, False |
You can add as many key/item pairs as you like. The keys must be unique. The keys and items can be of any VBA type.
Or equivalently.
1 2 3 4 5 | D.Add Key:="Key1", Item:="Item1" D.Add Key:=2, Item:=3.141592 D.Add Key:=9.99, Item:=False |
Key
A key can be a variable of any VBA type, even object or variant.
Although keys can be of the type object or variant, we have never come across a situation where this seemed to be the best design choice. If you use objects or variants be aware that the variable is not the object itself.
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 | 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 D.Add S, "Item" 'String D.Add Dt, "Item" 'Date D.Add B, "Item" 'Boolean D.Add Bt, "Item" 'Byte D.Add I, "Item" 'Integer D.Add L, "Item" 'Long D.Add Sgl, "Item" 'Single D.Add Dbl, "Item" 'Double D.Add Curr, "Item" 'Currency |
When deserializing a JSON text, the keys will always be of the type string.
Uniqueness
If the key is not unique, VBA will generate a run time error.
1 2 3 4 | D.Add "Key", "Item1" D.Add "Key", "Item2" 'this will generate a VBA error |
This seems straightforward, but it can be subtle.
1 2 3 4 5 6 7 8 9 10 11 12 | Dim S As String S = "1" Dim I As Integer I = 1 Dim Dbl As Double Dbl = 1# D.Add S, "Item" D.Add I, "Item" 'this works D.Add Dbl, "Item" 'this will generate a VBA error |
If the key is a string, there are two options to compare: case sensitive ("key" <> "KEY", BinaryCompare, 0) and case insensitive ("key" = "KEY", TextCompare, 1). The default is case sensitive.
1 2 3 4 5 | D.CompareMode = BinaryCompare 'case sensitive D.Add "Key", "Item" D.Add "KEY", "Item" 'this works |
Or, equivalently.
1 2 3 4 5 | D.CompareMode = 0 'case sensitive D.Add "Key", "Item" D.Add "KEY", "Item" 'this works |
You can change to case insensitive comparison.
1 2 3 4 5 | D.CompareMode = TextCompare 'case insensitive D.Add "Key", "Item" D.Add "KEY", "Item" 'this will generate a VBA error |
Or, equivalently.
1 2 3 4 5 | D.CompareMode = 1 'case sensitive D.Add "Key", "Item" D.Add "KEY", "Item" 'this will generate a VBA error |
You need to make this choice before you start filling the dictionary. If the method Count returns > 0, the compare mode cannot be changed anymore (unless you first call the method RemoveAll).
Changing the compare mode affects the behavior of other methods.
When deserializing a JSON text into a dictionary, use the default.
Adding key/item pairs by using the method Add, is called explicit adding. There is also implicit adding. The following line of code illustrates implicit adding.
1 2 3 | D("Key") = "Item" |
If the key does not yet exist, then the key/item pair will be added. If it the key already exists, this statement will overwrite the item attached to the key, without warning. If you use this statement, be aware of the compare mode.
1 2 3 4 5 6 7 8 9 | D.CompareMode = BinaryCompare D("Key") = "Item1" D("KEY") = "Item2" 'this will add D.CompareMode = TextCompare D("Key") = "Item1" D("KEY") = "Item2" 'this will overwrite |
This example shows it is risky to change the CompareMode of a dictonary in an existing piece of VBA code (without looking at the other lines in which the dictionary is used). We try to avoid adding key/value pairs implicitely.
When deserializing a JSON text into a dictionary, use the default.
Items
An item can be of any VBA type.
As it can be an object, this means an item can be another dictionary. It is this feature that makes dictionaries the obvious output choice for JSON deserialization.
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 34 35 | 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") D.Add "Key1", S 'String D.Add "Key2", Dt 'Date D.Add "Key3", B 'Boolean D.Add "Key4", Bt 'Byte D.Add "Key5", I 'Integer D.Add "Key6", L 'Long D.Add "Key7", Sgl 'Single D.Add "Key8", Dbl 'Double D.Add "Key9", Curr 'Currency D.Add "Key10", V 'Variant D.Add "Key11", O 'Object |
