Excel VBA Dictionary Datatype – Create & Add Keys, Items

Excel VBA Scripting.Dictionary

“Dictionary” is a datatype similar to an array, but in addition it can hold a group of variable or objects. Another available data type similar to this is “Collection”.

In short it can be called as an Indexed Array.

You have to include a reference to “Microsoft Scripting Runtime” from menu -> tools to use this dictionary object.

How does a Dictionary look like?

A VBA dictionary is arranged as an array of key values along with the item or collection of items.

For Example:

• dictionaryObject -> Key , Item
• “A”, “Apple”
• “B”, “Ball”
• “C”, “Cat”

Now, lets see how to create a new dictionary object & add key+items to it.

How to add Items to Dictionary in VBA?

Here is a code that will add key & items to a dictionary object.

Sub create_dictionary()
    'Define dictionary object
    Dim dictObject As Scripting.Dictionary
    
    'Create Dcitionary object
    Set dictObject = New Scripting.Dictionary
    
    'Add Keys & Items to Dictionary
    dictObject.Add "student1", 100
    dictObject.Add "student2", Array(90, 80)
    
    'Print values in Each Dictionary Key
    Debug.Print dictObject("student1")
    Debug.Print dictObject("student2")(0)
    Debug.Print dictObject("student2")(1)    
End Sub

In the above code sample, ‘student1’, ‘student2’ are the keys for the dictionary object. The values in each key can be accessed using its key value directly.

Each key can have same or different data types in it. The first key “student1” has a integer value whereas the second key “student2” has an array of numbers.

Thus, the dictionary has lot more advanced options that a simple array.

If you would like to read all the keys & items  in a dictionary object using a loop, then refer this article.