Excel VBA: How to create Data Types in a 3-d array

by FancyDolphin   Last Updated November 09, 2018 01:26 AM

Im wondering how to create data types for my 3-d array. I really only need it on the x,y variable in the initialization route. I'm at a loss how to do this. Below is how I'm creating the array, and I can use it but I'd prefer to have more control. To give an example: It would be gret to something like dim miultisheetArray(11 as Type1, 7 as Type2, 3 as Type3) as Variant. So that I could do a call like multisheetArray.Type1Var.Type2Var.Type3Var. Like a 1-d array but for 3-d. Thanks.

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(11, 7, 3) As Variant    'array with 3 sheets, 5 columns/rows
With Worksheets("Sheet1")
'start at Cells(10,4)
Dim startingRow, startingColumn, staggeredYears As Integer
startingRow = 10
startingColumn = 4
staggeredYears = 3
For w = 0 To 10

    For x = 0 To 6
        For y = 0 To 2
            multiSheetArray(w, x, y) = .Cells(x + startingRow, y + startingColumn + (w * staggeredYears))
        Next
    Next
Next
End With

End Sub


Related Questions


Median/average does not return the right values

Updated April 20, 2015 01:11 AM

Function caller code VBA

Updated September 05, 2017 05:26 AM