Dictionary is an object that can store the data in Key and item format. It is part of a library called “Microsoft Scripting Runtime”. So, to use dictionary object we need to add this library as shown in the image below.
In the below shown code, we have created a dictionary called “SampleDictionary”, then we added 3 items in the dictionary and then we print each of the item in the dictionary using a for loop.
Sub DictionaryCreate_1() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") 'Add items in dictionary SampleDictionary.Add Key:=1, Item:="NYC" SampleDictionary.Add Key:=2, Item:="LA" SampleDictionary.Add Key:=3, Item:="SF" 'Print each of dictionary items For Each DictItem In SampleDictionary Debug.Print SampleDictionary(DictItem) Next DictItem End Sub
In a dictionary we add a key and its corresponding item. A key in a dictionary will always be unique.
Dictionary Methods
Dictionary is associated with 6 methods –
- Add
- Exists
- Items
- Keys
- Remove
- RemoveAll
(1) Add Method
This method is used to add a key and its corresponding item.
SampleDictionary.Add Key:=1, Item:="NYC"
(2) Exists Method
Exists method will return true if the key exists. In the below shown code we can see that if the key is found in the dictionary then it will display the message confirming that key is present in the dictionary.
Sub ExistsMethod() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="LA" 'If dictionary exists If SampleDictionary.exists(10001) = True Then MsgBox "Dictionary key exists" Else MsgBox "Dictionary key does not exist" End If End Sub
(3) Items Method
Items method will return an array of items present in a dictionary. Then we can access those items using array subscript as shown in the code below.
Sub ItemMethod() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") Dim Myarray 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="LA" 'Store the dictionary items in a array Myarray = SampleDictionary.Items 'Print the array items Debug.Print Myarray(0) Debug.Print Myarray(1) End Sub
(4) Keys Method
Keys method will return an array of keys present in a dictionary. Then we can access those keys using array subscript as shown in the code below.
Sub KeysMethod_4() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") Dim Myarray As Object 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="LA" 'Store the dictionary items in a array MyKeys = SampleDictionary.keys 'Print the array items Debug.Print MyKeys(0) Debug.Print MyKeys(1) End Sub
(5) Remove Method
With the help of this method, we can remove a key or an item from the dictionary. In the below shown code we have removed the key “10001” from the dictionary. In the immediate window, we can see that the result. Key 10001 and its corresponding Item “NYC” has been deleted.
Sub RemoveMethod() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") Dim Myarray As Object 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="LA" Debug.Print "Value before removing key is " & SampleDictionary.Item(10001) SampleDictionary.Remove (10001) Debug.Print "Value after removing key is " & SampleDictionary.Item(10001) End Sub
(6) RemoveAll Method
We can remove all the items from the dictionary using this method. In the below code we had 2 items in the dictionary called SampleDictionary, after using the RemoveAll method all the keys and items have been deleted from the dictionary. We can see the same in the image below.
Sub RemoveAllMethod() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") Dim Myarray As Object 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="LA" Debug.Print "Count of items in dictionary before removing " & SampleDictionary.Count 'Remove all items from dictionary SampleDictionary.RemoveAll Debug.Print "Count of items in dictionary after removing " & SampleDictionary.Count End Sub
Properties of dictionary object
There are 4 properties of dictionary object –
- CompareMode
- Count
- Item
- Key
(1) CompareMode Property
This property is used to set how we compare string keys. Basically, it determines how strings should be compared, for example “vba”,” Vba” , “VBA” can be considered same or each of them can be considered as different string. So, by setting this property we can customize string comparison.There are 4 compare modes in Dictionary –
- vbBinaryCompare
- vbTextCompare
- vbDatabaseCompare
- vbUseCompareOption
(a) vbBinaryCompareMode
This compare argument checks for case in the strings, which are compared.Here “NYC” and “nYC” are treated as different strings.
Sub BinaryCompareMode() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="nYC" 'Upper and Lower case are considered different SampleDictionary.CompareMode = vbBinaryCompare 'If statement shows that both the strings are not same If SampleDictionary.Item(10001) <> SampleDictionary.Item(90001) Then MsgBox "Both the items are different" End If End Sub
(b) vbTextCompareMode
This compare argument does not check for case in the strings, which are compared.Here “NYC” and “nYC” are treated as same strings.
Sub TextCompareMode() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") SampleDictionary.CompareMode = vbTextCompare 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="nYC" 'Upper and Lower case are considered same 'If statement shows that both the strings are considered same If SampleDictionary.Item(10001) <> SampleDictionary.Item(90001) Then MsgBox "Both the items are same" End If End Sub
(c) vbUseCompareOption
In vbUseCompareOption option we shall compare two strings according to values set using Option Compare statement. We declare Option Compare statement at the top of module.
Option Compare statement can be used in 3 ways as shown below –
- Option Compare Binary – In this string are case sensitive, “NYC” and “Nyc” are treated differently
- Option Compare Text – In this string are not case sensitive, “NYC” and “Nyc” are treated same
- Option Compare Database- This statement can only be used in MS access database only. String comparison is dependent on locale ID (local language setting of user)
(d) vbDatabaseCompare
It works only for ms access, comparison is made based on language settings in database.
So, item a,b,c,d mentioned above describe the CompareMode property of Dictionary object.
(2) Count Property
With this property we can find the number of items present in the dictionary. In the code shown below we have added 2 items and their corresponding keys in dictionary. Now after executing VBA code, we can see in the immediate window, count is shown as 2 as there are only two items exist.
ItemCount = SampleDictionary.count
Sub CountProperty() 'Create a dictionary using late binding Dim SampleDictionary As Object Set SampleDictionary = CreateObject("Scripting.Dictionary") SampleDictionary.CompareMode = vbTextCompare 'Add items in dictionary SampleDictionary.Add Key:=10001, Item:="NYC" SampleDictionary.Add Key:=90001, Item:="nYC" Debug.Print "Number of keys in dictionary - " & SampleDictionary.Count End Sub
(3) Item Property
It will return an item for a key passed as argument. In the code shown below first we have added a key (i.e. KeyOne) and its corresponding item (i.e. “NYC”). Then in the next line we have printed that item using the key (i.e. KeyOne).With the help of Item property we can access the value of an individual item.Difference between Item property and Items Method is the Item property returns a single item, while Items Method returns an array of items.
Sub ItemProperty() Dim SampleDictionary As Scripting.Dictionary Set SampleDictionary = New Scripting.Dictionary 'Add items in dictionary SampleDictionary.Add Key:="KeyOne", Item:="NYC" 'We are accessing the item using its key Debug.Print SampleDictionary.Item("KeyOne") Set SampleDictionary = Nothing End Sub
(4) Key Property
Key property is used to replace an already existing key. For example, here we have added a key (i.e. KeyOne) and its corresponding item (i.e. “NYC”). Then we are replacing the first key (“KeyOne”) with another key (i.e. KeyThree). Now, we can access the item “NYC” using Key (KeyThree).
Sub KeyProperty() Dim SampleDictionary As Scripting.Dictionary Set SampleDictionary = New Scripting.Dictionary 'Add items in dictionary SampleDictionary.Add Key:="KeyOne", Item:="NYC" Debug.Print SampleDictionary.Item("KeyOne") 'Here we are replacing or changing KeyOne with KeyThree SampleDictionary.Key("KeyOne") = "KeyThree" 'Checking the output with the newly replaced key Debug.Print SampleDictionary.Item("KeyThree") Debug.Print SampleDictionary.Item("KeyOne") Set SampleDictionary = Nothing End Sub
Reference
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-compare-statement
Post you may like