vba - 字典后期绑定中的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 列中的数据用“评论”键保存。

标签： vbaexceldictionary

Answer 1

尝试将row作为对象数组，并在退出循环后将 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