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.
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 |
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.
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.
Items
An item can be of any VBA type.
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 |