VBA Excel词典内部词典后期绑定

Question

以下代码在调试语句上失败

Sub Tets()
Dim cl_data As Object
Set cl_data = CreateObject("Scripting.Dictionary")
Dim row As Object

Dim irow As Long
For irow = 11 To 12
    Set row = CreateObject("Scripting.Dictionary")
    With row
        row.Add "YN", Cells(irow, 2).Value
        row.Add "Comment", Cells(irow, 3).Value
    End With
    cl_data.Add Cells(irow, 1).Value, row
Next irow
Debug.Print cl_data(CStr(Cells(irow, 1)))("YN")
End Sub

​

​

我试着保存A、B和C列中的数据。“外部字典应该以A列的值作为键，内部是另一个字典，其中B列的数据以"YN”键保存，c列的数据由键“注释”保存。

Answer 1

这里的问题是在你的循环之后。

For irow = 11 To 12
     '…
Next irow

Debug.Print irow返回13。这不在字典中，因为您只读取行11和12。

Answer 2

尝试将行作为对象数组，并在退出循环后将irow重置为边界内的某个内容。

Sub Tets()

    Dim irow As Long, cl_data As Object, row(11 To 12) As Object

    Set cl_data = CreateObject("Scripting.Dictionary")

    For irow = 11 To 12
        Set row(irow) = CreateObject("Scripting.Dictionary")
        With row(irow)
            .Add "YN", Cells(irow, 2).Value
            .Add "Comment", Cells(irow, 3).Value
        End With
        cl_data.Add Key:=Cells(irow, 1).Value, Item:=row(irow)
    Next irow

    irow = 11
    Debug.Print cl_data(CStr(Cells(irow, 1)))("YN")
    irow = 12
    Debug.Print cl_data(CStr(Cells(irow, 1)))("YN")

End Sub