Looping through a Scripting Dictionary using index item number

Question

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

User · Accepted Answer

According to the documentation of the Item property      Sets or returns an item for a specified key in a Dictionary object    In your case  you don t have an item whose key is 1 so doing   s   d Item i    actually creates a new key   value pair in your dictionary  and the value is empty because you have not used the optional newItem argument   The Dictionary also has the Items method which allows looping over the indices   a   d Items For i   0 To d Count - 1     s   a i  Next i

User · Answer

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

User · Answer

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   amp    -    amp  oDict Items i      Next End Sub

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

Examples related to vba

Examples related to dictionary