How to create a collection of new dictionaries in a loop in Excel VBA?

Question

I want to convert a range with two columns into a collection of dictionaries. For example, if the range is

A	B
1	2
3	4
5	6

then I want to have (using JSON notation) the collection [{first:1, second:2}, {first:3, second:4}, {first:5, second:6}].

I tried the following:

Function Make_Collection(r As Range) As Collection
    Dim out_collection As New Collection
    Dim row As Range
    For Each row In r.Rows
        Dim current_row As New Dictionary
        current_row.Item("first") = row.Cells(1, 1).Value2
        current_row.Item("second") = row.Cells(1, 2).Value2
        out_collection.Add current_row
    Next
    Set Make_Collection = out_collection
End Function

but I get [{first:5, second:6}, {first:5, second:6}, {first:5, second:6}], that is three copies of the last row, instead of a dictionary for each row.

How can I fix this?

EDIT

Great answer by @QHarr below. In other words, I thought that my code was equivalent to

Function Make_Collection(r As Range) As Collection
    Dim out_collection As New Collection
    Dim row As Range
    For Each row In r.Rows
        Dim current_row As Dictionary
        current_row = New Dictionary
        current_row.Item("first") = row.Cells(1, 1).Value2
        current_row.Item("second") = row.Cells(1, 2).Value2
        out_collection.Add current_row
    Next
    Set Make_Collection = out_collection
End Function

but it is actually equivalent to

Function Make_Collection(r As Range) As Collection
    Dim out_collection As New Collection
    Dim row As Range
    For Each row In r.Rows
        Dim current_row As Dictionary 'Do nothing on second and later iterations
        If current_row is Nothing Then 'True on first iteration only
            Set current_row = New Dictionary 
        current_row.Item("first") = row.Cells(1, 1).Value2 'Change the object in place on
        current_row.Item("second") = row.Cells(1, 2).Value2 'second and later iterations
        out_collection.Add current_row 'Adds every time a reference to the same object
    Next
    Set Make_Collection = out_collection
End Function

Answer 1

Welcome to the world of avoid auto-instantiation due to unexpected/unwanted behaviour. Remove the auto-instantiation and set a fresh reference each time.

Option Explicit

Public Sub test()
    Dim c As Collection, rng As Range, i As Long

    Set rng = ActiveSheet.Range("A1:B3")
    
    Set c = Make_Collection(rng)
    
    For i = 1 To c.Count
        Debug.Print c.Item(i)("first"), " ", c.Item(i)("second")
    Next

End Sub

Function Make_Collection(r As Range) As Collection
    Dim out_collection As Collection, arr() As Variant
    Dim i As Long, current_row  As Scripting.Dictionary

    Set out_collection = New Collection
    arr = r.Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        Set current_row = New Dictionary
        current_row.Item("first") = arr(i, 1)
        current_row.Item("second") = arr(i, 2)
        out_collection.Add current_row
    Next
    Set Make_Collection = out_collection
End Function

---

See this from the wonderful Chip Pearson site:

Don't Use Auto-Instancing Object Variables

For object type variables, it is possible to include the New keyword in the Dim statement. Doing so create what is called an auto-instancing variable. Again, while this may seem convenient, it should be avoided. Contrary to what some programmers may believe, the object isn't created when the variable declaration is processed. Instead, the object is created when it is first encountered in the code. This means that, first, you have limited control when an object is created. Second, it means that you cannot test whether an object is Nothing, a common test within code and a common testing and diagnostic technique. If the compiler's output were in VBA code, the code to handle auto-instancing variables would look like the following:

Dim FSO As New Scripting.FileSystemObject
'''''''''''
' more code
'''''''''''
If FSO Is Nothing Then ' The compiler does something like this
    Set FSO = New Scripting.FileSystemObject
End If

Here, simply testing FSO for Nothing causes the object to be created and therefore FSO will never test properly for the Nothing state. Instead of using New in the variable's declaration, use the Set New syntax:

Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject

and this:

Dim C As New Class1

This is called an auto-instancing variable. When the variable C is first encountered in code, a new instance is created. In general, you should avoid auto-instancing variables for two reasons:

First, it adds overhead to the code because the variable must be tested for Nothing every time it is encountered in code.

Second, you have no way to test whether a auto-instancing variable is Nothing because the very act of using the variable name in an If Obj Is Nothing Then statement will automatically create an instance of the variable.

---

Related interest:

1. What is the reason for not instantiating an object at the time of declaration?
2. Is the poor performance of Excel VBA auto-instancing a myth?