ExcelVBA字典的输出

'练习字典的输出看代码吧
Sub 字典输出()

    Dim dic As Object, arr()

    Set dic = CreateObject("scripting.dictionary")

    ReDim arr(10)

    'key是一个数字，item是一个一维数组，

    For M = 1 To 10

        For i = 0 To UBound(arr)

            arr(i) = "k" & M & "--" & i

        Next i

        dic("key" & M) = arr

    Next M

    With Sheets("字典输出")

        .range("B2").Resize(100, 50).ClearContents

        .range("A12:A30").ClearContents

        '第1种输出：表中已有key,先读取单元格的数据，再匹配字典，输出item

        For i = 2 To 11 '.range("A2").End(xlDown).Row

            '        .Cells(Rows.count, 1).End(xlUp).Row

            s = .Cells(i, 1).Value

            If dic.exists(s) Then

                s_arr = dic(s)

                .Cells(i, 2).Resize(1, UBound(s_arr) + 1).NumberFormatLocal = "@"

                .Cells(i, 2).Resize(1, UBound(s_arr) + 1) = dic(s)

            End If

        Next i

        '第2种输出，指定P2单元格输出keys,指定Q2,输出items数组，

        'keys,一次Transpose再输出,items用Application.Rept(数组，1)也可以用两次Transpose再输出

        ks = dic.keys()

        Tems = dic.items()

        c = UBound(Application.Transpose(Tems), 2)

        MsgBox "准备输出，共几行：" & c

        .range("P2").Resize(dic.Count, 1) = Application.Transpose(ks)

        '        .range("Q2").Resize(dic.count, c + 1) = Application.Transpose(Application.Transpose(Tems))

        .range("Q2").Resize(dic.Count, c + 1) = Application.Rept(Tems, 1)

        MsgBox "准备第3种输出"

        '第3种输出，循环dic.count-1,读出dic.keys()(k)，还有dic.items()(k)

        For k = 0 To dic.Count - 1

            .Cells(12 + k, 1) = dic.keys()(k)

            .Cells(12 + k, 2).Resize(1, 11) = dic.items()(k)

        Next k

    End With

End Sub