Iterate over keys in VBA and print them in cells

Question

I would like to iterate through a dictionary's keys and put them in cells.

I tried that:

Dim dic1 As Object
Set dic1 = CreateObject("Scripting.Dictionary")
dic1.Add "name1", "value1"
dic1.Add "name2", "value2"
For i = 0 To Len(dic1)
    Cells(1, dic1()(i)) = dic1()(i).Key
Next

I added the parentheses to avoid an error but I still get Wrong number of arguments or invalid property assignment.

To answer a comment, I would like to avoid For Each.

Edit: following this link, I tried:

Set cell1 As String
cell1 = dic1.Items(1)

It didn't work. I got Property let procedure not defined and property get procedure did not return an object. Does it mean that it's not a dictionary?

Answer 1

Looping Over Elements of a Dictionary (Early vs Late Binding)

• If you want to loop over the indexes of a dictionary, you will want to use early binding i.e. create a reference to the Microsoft Scripting Runtime library (TestEarly).
• Doing it without the reference is inefficient (TestLate).

Option Explicit


Sub TestEarly()
    
    Const FIRST_ROW As Long = 2
    
    ' Early Binding. Needs a reference to the library:
    ' VBE->Tools->References->Microsoft Scripting Runtime
    Dim dict As Scripting.Dictionary: Set dict = New Scripting.Dictionary
    
    dict.Add "name1", "value1"
    dict.Add "name2", "value2"
    
    ' Start writing at 'A2' (keys) and 'B2' (items).
    
    Dim i As Long
    
    For i = 0 To dict.Count - 1
        Cells(i + FIRST_ROW, "A") = dict.Keys(i)
        Cells(i + FIRST_ROW, "B") = dict.Items(i)
    Next i

    MsgBox "Found " & i & " item(s).", vbInformation

End Sub


Sub TestLate()

    Const FIRST_ROW As Long = 2

    ' Late Binding. No library reference needed
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "name1", "value1"
    dict.Add "name2", "value2"
    
    ' Start writing at 'A2' (keys) and 'B2' (items).
    
    Dim i As Long
    
    For i = 0 To dict.Count - 1
        Cells(i + FIRST_ROW, "A") = dict.Keys()(i)
        Cells(i + FIRST_ROW, "B") = dict.Items()(i)
    Next i

    MsgBox "Found " & i & " item(s).", vbInformation

End Sub


Sub TestLateForEach()

    Const FIRST_ROW As Long = 2

    ' Late Binding. No library reference needed
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "name1", "value1"
    dict.Add "name2", "value2"
    
    ' Start writing at 'A2' (keys) and 'B2' (items).
    
    Dim i As Long: i = FIRST_ROW
    
    Dim Key
    
    For Each Key In dict.Keys
        Cells(i, "A") = Key
        Cells(i, "B") = dict(Key)
        i = i + 1
    Next Key

    MsgBox "Found " & i - FIRST_ROW & " item(s).", vbInformation

End Sub

Answer 2

try this

Dim dic1 As Object
Set dic1 = CreateObject("Scripting.Dictionary")
dic1.Add "name1", "value1"
dic1.Add "name2", "value2"

Dim i As Long
Dim keys As Variant
keys = dic1.Keys

For i = LBound(keys) To UBound(keys)
    Cells(1, i + 1) = keys(i)
Next i

Answer 3

If your dictionary has less than 65,536 elements, there is no need to iterate:

Option Explicit
Sub foo()

Dim dic1 As Object
Set dic1 = CreateObject("Scripting.Dictionary")
dic1.Add "name1", "value1"
dic1.Add "name2", "value2"

Range(Cells(1, 1), Cells(dic1.Count, 1)) = WorksheetFunction.Transpose(dic1.Keys)

End Sub

The limit is due to the misbehaviour of WorksheetFunction.Transpose when there are more than that number of elements. If you exceed that limit, merely create a 2D variant array by looping.

If you want the output in a single row, omit the WorksheetFunction.Transpose (but you will be limited to the number of columns in Excel.