Dictionary in VBA Excel | debugvba.com

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 –

1. Add
2. Exists
3. Items
4. Keys
5. Remove
6. 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 –

1. CompareMode
2. Count
3. Item
4. 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 –

1. vbBinaryCompare
2. vbTextCompare
3. vbDatabaseCompare
4. 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 –

 

1. Option Compare Binary – In this string are case sensitive, “NYC” and “Nyc” are treated differently
2. Option Compare Text – In this string are not case sensitive, “NYC” and “Nyc” are treated same
3. 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

Offset Property in VBA Excel