VBA has a dictionary structure. Dictionary is an object, and it can be referenced either with early binding or with a late binding, referring to Microsoft Scripting Runtime:
What is a dictionary? This is the Microsoft definition of the VBA dictionary:
A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.
With other words, we have some kind of object, that maps values to keys. What does this mean? Let’s imagine we have a dictionary object, to which we want to save some information. E.g., information about two other people and salary. Like this:
|
1 2 3 4 5 6 7 8 9 10 |
Sub MyDictionary() Dim myDict As New Scripting.Dictionary myDict.Add "Peter", "Peter is a friend." myDict.Add "George", "George is a guy I know." myDict.Add "Salary", 1000 Debug.Print myDict("Peter") End Sub |
There “Peter” is a key and “Peter is a friend” is a value. At the end of the program, we request to see the value of the key “Peter”, which is printed in the immediate window. The dictionary object has methods and properties. This is a short presentation of them.
Methods:
| Add | Adds a new key/item pair to a Dictionary object. |
| Exists | Returns a Boolean value that indicates whether a specified key exists in the Dictionary object. |
| Items | Returns an array of all the items in a Dictionary object. |
| Keys | Returns an array of all the keys in a Dictionary object. |
| Remove | Removes one specified key/item pair from the Dictionary object. |
| RemoveAll | Removes all the key/item pairs in the Dictionary object. |
Properties:
| CompareMode | Sets or returns the comparison mode for comparing keys in a Dictionary object. |
| Count | Returns the number of key/item pairs in a Dictionary object. |
| Item | Sets or returns the value of an item in a Dictionary object. |
| Key | Sets a new key value for an existing key value in a Dictionary object. |
What can we do with them? This is an example with all the methods and properties:
|
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 36 37 38 39 40 41 42 43 |
Sub MyDictionary() 'Add Dim myDict As New Scripting.Dictionary myDict.Add "Peter", "Peter is a friend." myDict.Add "George", "George is a guy I know." myDict.Add "Salary", 1000 'Exists If myDict.Exists("Salary") Then Debug.Print myDict("Salary") myDict("Salary") = myDict("Salary") * 2 Debug.Print myDict("Salary") End If 'Remove If myDict.Exists("George") Then myDict.Remove ("George") End If 'Items Dim item As Variant For Each item In myDict.Items Debug.Print item Next item 'Keys Dim key As Variant For Each key In myDict.Keys Debug.Print key Next key 'Remove All myDict.RemoveAll 'Compare Mode myDict.CompareMode = BinaryCompare myDict.Add "PeTeR", "Peter written as PeTeR" myDict.Add "PETeR", "Peter written as PETeR" PrintDictionary myDict End Sub |
The trick with .CompareMode is that by default its value is set to vbTextCompare, thus “PeTeR” and “PETeR” would not be allowed as different keys. However, once the value is reset to “BinaryCompare”, this is allowed.
True story…