excelvbamultidimensional-array

VBA pasting 3 dimensional array into sheet


I have a 3 dimensional array (5 x 5 x 3) and I need to post (5 x 5 x 1) to Sheet1, (5 x 5 x 2) to Sheet2, (5 x 5 x 3) to Sheet3. Because I am building this 3 dimensional array inside 3 nested for loops, I cannot use a for loop to access the (5 x 5) part of the loop. Is there any identifier that tells excel to index all elements of an array, such as in MatLab by using (1:end, 1:end, 1)? Basic code is below:

Sub practice_2()

Dim arr(1 To 5, 1 To 5, 1 To 3)
Dim a As Integer
Dim x As Integer
Dim y As Integer

    For a = 1 To 3
        For x = 1 To 5
            For y = 1 To 5
                arr(x, y, a) = x * y
            Next
        Next

        Sheets(a).Select
        'Following line is where I want to access the (5 x 5 x 1) array
        Range(Cells(1, 1), Cells(5, 5)) = arr
    Next
End Sub 

Solution

  • There's not much you can do directly with a 3-D array in Excel. However, VBA Variants are pretty flexible. You could get what you want by using a 1-D array that contains 2-D arrays instead of a 3-D array:

    Dim arr(1 To 3)
    
    Dim a As Integer
    Dim x As Integer
    Dim y As Integer
    
    For a = 1 To 3
        ReDim inner(1 To 5, 1 To 5)
    
        'don't worry...makes a copy
        arr(a) = inner
    
        For x = 1 To 5
            For y = 1 To 5
                arr(a)(x, y) = a * x * y
            Next
        Next
    
        Sheets(a).Select
    
        Range(Cells(1, 1), Cells(5, 5)) = arr(a)
    Next
    

    (In answer to your specific question about array syntax, the answer is "no".)