How to display a 3D array initialized in Excel VBA

Question

I'm learning Excel VBA Programming for Dummies, 3rd Edition. There's an example which uses nested For-Next loops to initialize a three-dimensional array with the value of 100.

I want to see this sub procedure's result.

Sub NestedLoops()
    Dim MyArray(10, 10, 10)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    For i = 1 To 10
        For j = 1 To 10
            For k = 1 To 10
                MyArray(i, j, k) = 100
            Next k
        Next j
    Next i
End Sub

I tried using a MsgBox to display MyArray, but it says Type mismatch. Is there a way to show the array?

Peter Mortensen · Accepted Answer · 2022-05-31 20:05:09Z

3

Ok, a couple of things.

Excel usually shows 2D data (row & col are the same as height and width or X and Y on a graph). You have 3D data.
If you insist on forcing Excel to show the data, then you would need to use worksheets as the 3rd dimension. So you would create a sheet for each I and on that sheet, you would fill in each cell with j and k (where j was a row and k was a column). If you do this right, you should end up with 10 sheets each with 100 in every cell from a1 to j10.

As it appears you are taking a class, I will leave it to you to figure out how to write the data out to the sheets.

edited May 31, 2022 at 20:05

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jul 30, 2019 at 20:30

Fletcher Johnson

581 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to display a 3D array initialized in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related