[vba] Looping through a Scripting.Dictionary using index/item number

Similar to this issue, when using a Scripting.Dictionary object in VBA, the outcome of the code below is unexpected.

Option Explicit

Sub test()

    Dim d As Variant
    Dim i As Integer
    Dim s As String
    Set d = CreateObject("Scripting.Dictionary")

    d.Add "a", "a"
    Debug.Print d.Count ' Prints '1' as expected

    For i = 1 To d.Count
        s = d.Item(i)
        Debug.Print s ' Prints ' ' (null) instead of 'a'
    Next i

    Debug.Print d.Count ' Prints '2' instead of '1'

End Sub

Using a zero-based index, the same outcome is achieved:

For i = 0 To d.Count - 1
    s = d.Item(i)
    Debug.Print s
Next i

Watching the object, I can actually see that it has two items, the key for the newly added is 1, as added from i. If I increase this loop to a higher number, then the number of items in the dictionary is increased, once for each loop.

I have tested this in Office/VBA 2003, 2010, and 2013. All exhibit the same behavior, and I expect other versions (2007) will as well.

I can work around this with other looping methods, but this caught me off guard when I was trying to store objects and was getting an object expected error on the s = d.Item(i) line.

For the record, I know that I can do things like this:

For Each v In d.Keys
    Set o = d.item(v)
Next v

But I'm more curious about why I can't seem to iterate through the items by number.

Using d.Keys()(i) method is a very bad idea, because on each call it will re-create a new array (you will have significant speed reduction).

Here is an analogue of Scripting.Dictionary called "Hash Table" class from @TheTrick, that support such enumerator: http://www.cyberforum.ru/blogs/354370/blog2905.html

Dim oDict As clsTrickHashTable

Sub aaa()
    Set oDict = New clsTrickHashTable

    oDict.Add "a", "aaa"
    oDict.Add "b", "bbb"

    For i = 0 To oDict.Count - 1
        Debug.Print oDict.Keys(i) & " - " & oDict.Items(i)
    Next
End Sub

Adding to assylias's answer - assylias shows us D.ITEMS is a method that returns an array. Knowing that, we don't need the variant array a(i) [See caveat below]. We just need to use the proper array syntax.

For i = 0 To d.Count - 1
    s = d.Items()(i)
    Debug.Print s
Next i()

KEYS works the same way

For i = 0 To d.Count - 1
    Debug.Print d.Keys()(i), d.Items()(i)
Next i

This syntax is also useful for the SPLIT function which may help make this clearer. SPLIT also returns an array with lower bounds at 0. Thus, the following prints "C".

Debug.Print Split("A,B,C,D", ",")(2)

SPLIT is a function. Its parameters are in the first set of parentheses. Methods and Functions always use the first set of parentheses for parameters, even if no parameters are needed. In the example SPLIT returns the array {"A","B","C","D"}. Since it returns an array we can use a second set of parentheses to identify an element within the returned array just as we would any array.

Caveat: This shorter syntax may not be as efficient as using the variant array a() when iterating through the entire dictionary since the shorter syntax invokes the dictionary's Items method with each iteration. The shorter syntax is best for plucking a single item by number from a dictionary.