Excel VBA – Loop thru all Keys & Items in Dictionary

Dictionary Object – Accessing all Keys & Items

In this article, You can get code to access a Dictionary object key & item in a loop.

There are 5 different methods to do this.

1. Loop thru each Key by
   • Directly accessing each key
   • Using Index number
2. Read each Item by
   • Directly accessing each item
   • Using each Key value
   • Using Index number

1.1 Loop thru Keys in Dictionary

The keys in dictionary acts as an index for the objects stored in a dictionary.

Additional Note: Key strings are case sensitive. While accessing a key value “key1” if we use “kEy1” then we will get a mismatch.

But, now we are not going to access key directly. We will construct a for loop that will read each key in the order it is stored.

Sub dictionary()
    'Define Data Type:
    'To use dictionary object,
    'Add refenrece to "Microsfot Scripting Runtime" from Toole->Reference
    Dim iDict As Scripting.dictionary
    Set iDict = New Scripting.dictionary
    
    'Create a Dictioniary Object
    iDict.Add "apple", 1
    iDict.Add "Orange", 222
    iDict.Add "Grape", 5
    iDict.Add "LEMON", 123
    
    'Loop Thru each key
    For Each ikey In iDict.Keys
        Debug.Print ikey
    Next ikey
    
End Sub

1.2 Access Dictionary Keys using Index Number

Replace the for each loop in the above code with this code.

This will look for each key using the index number.

Dictionaryobject.Count will give the number of elements in a dictionary. And the index starts from 0 (zero).

    'Access each key using index number
    Dim i As Integer
    Debug.Print vbCrLf & "----------------------"
    For i = 1 To iDict.Count
        Debug.Print "Key(" & (i) & "): " & iDict.Keys(i - 1)
    Next i

The out from both the loops are the same. Both will display all the key elements in the dictionary in the immediate window.

2. Loop Thru Each Item in Dictionary Directly

We are done with Keys. Now, lets access each item.

Sub dictionary_items()
    'Define Data Type:
    'To use dictionary object,
    'Add refenrece to "Microsfot Scripting Runtime" from Toole->Reference
    Dim iDict As Scripting.dictionary
    Set iDict = New Scripting.dictionary
    
    'Create a Dictioniary Object
    iDict.Add "apple", 1
    iDict.Add "Orange", 222
    iDict.Add "Grape", 5
    iDict.Add "LEMON", 123
        
    'Loop Thru each Items directly
    Debug.Print vbCrLf & "----------------------"
    For Each itm In iDict.Items
        Debug.Print itm
    Next itm
    
End Sub

2.2 Access dictionary Item using each of its Key

The previous loop accessed each item directly. Lets see how to read each of the items using its corresponding key value.

    'Loop Thru Items using Keys
    For Each ikey In iDict.Keys
        Debug.Print iDict(ikey) 'Or
        Debug.Print iDict.Item(ikey)
    Next ikey    
End Sub

2.3 Read Dictionary Items using Index Number

There is no direct method to access a dictionary item using index. So, we are going to use the indirect method.

You can get each key using index number & with each key we can read each item.

The below loop using this logic to fetch items using index.

    'Access each Items using index number
    Dim i As Integer
    Debug.Print vbCrLf & "----------------------"
    For i = 1 To iDict.Count
        Debug.Print "Item(" & (i) & "): " & iDict.Item(iDict.Keys(i - 1))
    Next i
End Sub