VBA 3 维数组

VBA 3 Dimensional Array

提问by codingYogi

I have to use nested For/Next loops to populate a multi-dimensional array that holds the data from ranges A1:E5 on the previous 3 sheets. I then want to display the array on the current sheet. This is for class and the book really doesn't cover using 3 dimensional arrays much.

我必须使用嵌套的 For/Next 循环来填充一个多维数组，该数组保存前 3 个工作表上 A1:E5 范围内的数据。然后我想在当前工作表上显示数组。这是课堂用的，这本书实际上并没有涉及太多使用 3 维数组的内容。

Private Sub multiWorksheetArray_Click()

Dim multiSheetArray(2, 4, 4) As Variant 'array with 3 sheets, 5 columns/rows
Dim I As Integer 'counters
Dim J As Integer
Dim S As Integer


For S = 1 To 3
    For I = 1 To 5
        For J = 1 To 5
           'populate the array
            multiArray(S - 1, J - 1, I - 1) = ActiveWorkbook.Worksheets("Sheet" + S).Cells(J, I).Value
        Next J
    Next I
Next S

Range("A15:O19").Value = multiArray

End Sub

采纳答案by codingYogi

Setup:I set the value of each cell in Range("A1:E5") on the first 3 sheets to it's external address:

设置：我将前 3 张纸上 Range("A1:E5") 中每个单元格的值设置为它的外部地址：

---

---

Sub Setup()
    Dim w As Long
    Dim c As Range

    For w = 1 To 3
        For Each c In Worksheets(i).[A1:E5]
            c = c.Address(External:=True)
        Next
    Next
End Sub

Test:Iterate over each element in the 3D Array and push it's and push the element's value into a range that is 3 cells wide. Because For Each Loopsiterate over its elements column by column and range's fill values row by row extending past the range's boundary, the values will natural fill in.

测试：迭代 3D 数组中的每个元素，将其推入一个 3 个单元格宽的范围内。因为For Each Loops逐列迭代其元素，并且范围的填充值逐行扩展到范围的边界之外，所以这些值将自然填充。

---

---

Sub Test3DArray()
    Dim w As Long, x As Long, y As Long, z As Long
    Dim c As Range
    Dim v As Variant
    Dim multiSheetArray(2, 4, 4) As Variant    'array with 3 sheets, 5 columns/rows

    For w = 1 To 3
        With Worksheets(w)
            For x = 1 To 5
                For y = 1 To 5
                    multiSheetArray(w - 1, x - 1, y - 1) = .Cells(x, y)
                Next
            Next
        End With
    Next

    For Each v In multiSheetArray
        With Worksheets(4)
            z = z + 1
            .Range("A1:C1").Cells(z) = v
        End With
    Next
End Sub

Results

结果

回答by codingYogi

The practical way storing the data is to have a 1D array filled with 2D arrays. This will allow you to use standard techniques to work with thee data.

存储数据的实用方法是用二维数组填充一维数组。这将允许您使用标准技术来处理数据。

Sub Test3DArray()
    Dim w As Long, x As Long, y As Long, z As Long
    Dim c As Range
    Dim v(4, 4) As Variant
    Dim multiSheetArray(2) As Variant    'array with 3 sheets, 5 columns/rows

    multiSheetArray(0) = v
    multiSheetArray(1) = v

    For w = 1 To 2
        With Worksheets(w)
            For x = 1 To 5
                For y = 1 To 5
                    multiSheetArray(w - 1)(x - 1, y - 1) = .Cells(x, y)
                Next
            Next
        End With
    Next

    multiSheetArray(2) = Worksheets(3).Range("A1:E5").Value

    With Worksheets(4)
        .Range("A1:E5").Value = multiSheetArray(0)

        .Range("A1:E5").Offset(6).Value = multiSheetArray(1)

        .Range("A1:E5").Offset(12).Value = multiSheetArray(2)
    End With

End Sub

Results:

结果：