Dictionary object in Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates dictionary object in Excel VBA (Visual Basic for Applications).

A dictionary class is a data structure that represents a collection of keys and values pair. The key is unique (identical) in a key-value pair and it can have at most one value in the dictionary, but a value can be associated with many different keys.

A dictionary class contains following members -

Add(key, item) - add a new key and item to the dictionary.
CompareMode - set or get the string comparison method.
Count ()- get the number of items in the dictionary. it is read-only.
Exists(Key) -  determine if a given key is in the dictionary.
Item(Key) - get or set the item for a given key.
Items() - get an array containing all items in the dictionary.
Key(Key) - change a key to a different key.
Keys() - get an array containing all keys in the dictionary.
Remove(Key) - remove a given key from the dictionary.
RemoveAll() -  remove all information from the dictionary.

The dictionary is available in the library Microsoft Scripting Runtime. Add a reference to the "Microsoft Scripting Runtime" library as shown below -

In the code window (Press Alt+F11 to switch between windows) -> Select Tools -> Select References -> From the list find "Microsoft Scripting Runtime" -> check it and Press "Ok".

The following example illustrates the dictionary class and the members of dictionary.

'Mandatory to declare variables explicitly
Option Explicit

'declaring global variable
Dim dict As Dictionary

Sub DictionaryExample()
Dim keyArray, itemArray, element

'Initilalize a dictionary object
Set dict = New Dictionary

'dictionary object
With dict
   'set compare mode
   .CompareMode = BinaryCompare
   
   'Other compare modes
   '.CompareMode = DatabaseCompare
   '.CompareMode = TextCompare
   
   'add item using named arguments
   .Add Key:="mike", Item:=22
    'keys are case-sensitive
    'Mike and mike are different
   '.Add Key:="Mike", Item:=22 'this line gives compiler error
   'add item without named arguments
   .Add "joe", 33
            
   'case sensitivity and Exists method
   'does MIKE exist?
   Debug.Print "MIKE exists = " & .Exists("MIKE")
   'change key value
   .Key("mike") = "MIKE"
   'does MIKE exist?
   Debug.Print "MIKE exists = " & .Exists("MIKE")

   'extract keys into variant array
   Debug.Print "Array of Keys"
   keyArray = .Keys
   For Each element In keyArray
      Debug.Print element
   Next

   'extract items into variant array
   Debug.Print "Array of Items"
   itemArray = .Items
   For Each element In itemArray
      Debug.Print element
   Next
    
    'Items in dictionary
    Debug.Print dict.Count & " Items in Dictionary"
   'empty the dictionary
   .RemoveAll
   Debug.Print dict.Count & " Items in Dictionary"

End With
//release the dictionary object
Set dict = Nothing
End Sub


'output:
'MIKE Exists = False
'MIKE Exists = True
'Array of Keys
'MIKE
'joe
'Array of Items
'22
'33
'2 Items in Dictionary
'0 Items in Dictionary

There are three comparison modes available. By default the comparison mode is BinaryCompare. The other two are DatabaseCompare and TextCompare.

To understand the difference between these comparison please follow this link - https://msdn.microsoft.com/en-us/library/8t3khw5f.aspx

Binary comparison is case-sensitive. So 'Mike' and 'mike' are different in the given example. If you try to add the same key twice, you will encounter the following error message.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.