如何在ExcelVBA中循环创建新字典集合？

欢迎来到避免由于意外/不需要的行为而自动实例化的世界。删除自动实例化并每次设置一个新的引用。

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

见这从美妙的芯片皮尔逊网站：

不要使用自动实例化对象变量

对于对象类型变量，可以在 Dim 语句中包含 New 关键字。这样做会创建所谓的自动实例化变量。同样，虽然这看起来很方便，但应该避免。与某些程序员可能认为的相反，在处理变量声明时不会创建对象。相反，对象是在代码中第一次遇到时创建的。这意味着，首先，您在创建对象时的控制有限。其次，这意味着您无法测试对象是否为 Nothing，这是代码中的常见测试以及常见的测试和诊断技术。如果编译器的输出是 VBA 代码，则处理自动实例化变量的代码如下所示：

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

在这里，简单地测试 FSO for Nothing 会导致创建对象，因此 FSO 永远不会正确测试 Nothing 状态。不要在变量的声明中使用 New，而是使用 Set New 语法：

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

与此：

Dim C As New Class1

这称为自动实例化变量。在代码中首次遇到变量 C 时，会创建一个新实例。通常，出于两个原因，您应该避免自动实例化变量：

首先，它增加了代码的开销，因为每次在代码中遇到变量时都必须测试它是否为 Nothing。

其次，您无法测试自动实例化变量是否为 Nothing，因为在 If Obj Is Nothing Then 语句中使用变量名称的行为将自动创建该变量的实例。

相关兴趣：

1. 声明时未实例化对象的原因是什么？
2. Excel VBA 自动实例化的性能不佳是一个神话吗？

• @ZevSpitz `Dim x As New ...` is special _syntax_ for creating "Auto-instantiated" (ai) variables. Every time an ai variable is referenced (eg, `x.foo()`) VBA will check if that variable x = Nothing, if so it will create an object and assign it to x on the same line before calling `foo`. All object variables (ai and "normal") initialise to `Nothing`, so that means `Set current_row = New Dictionary` call happens implicitly on the line `current_row.Item("first") = ...`. But every subsequentloop iteration the current_row ai variable is not Nothing so it is not re-initalised to a new Dictionary.
• @ZevSpitz Another option while still using the auto instantiation syntax would be to manually set `current_row` variable to Nothing every iteration of the loop. That way when VBA checks `x = Nothing?` before `x.foo`, it would trigger a re-initialisation. Worth noting the Dim statement only runs once, the first time it is hit, not every step of the loop, so it does not reset current_row to Nothing automatically. Interestingly you can never check an ai variable `Is Nothing` manually, since it always auto instantiates just before the check so the check is always false