VBA 3 Dimensional Array

Question

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.

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

Miscellaneous suggestions. (1) VBA allows you to set the lower bounds of an array. Replace Dim multiSheetArray(2, 4, 4) As Variant by Dim multiSheetArray(1 To 3, 1 To 5, 1 To 5) As Variant and multiArray(S - 1, J - 1, I - 1) can become multiArray(S, J, I) which is much clearer. (2) You declare array as multiSheetArray but access it as multiArray. (3) I agree with GSerg, in Range.Value = MyArray, MyArray cannot be 3D. — Tony Dallimore
– Tony Dallimore, Commented Oct 2, 2016 at 23:03

user6432984 · Accepted Answer · 2016-10-03 01:47:49Z

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:

user6432984 · Accepted Answer · 2016-10-03 01:29:50Z

0

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

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 Loops iterate 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.

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

answered Oct 3, 2016 at 1:29

user6432984

1 Comment

luckyguy73 Over a year ago

in the setup procedure you posted it seems to me the index variable i in worksheets should be a w the way you have your loop setup. For Each c In Worksheets(w).[A1:E5]

Collectives™ on Stack Overflow

VBA 3 Dimensional Array

2 Answers 2

Results:

Comments

Results

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Results:

Comments

Results

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related