Storing arrays within arrays

Question

In Excel VBA, is there a way to store an array within another array? For instance, if I created a 1-dimensional array called "World" and 2-dimensional arrays with various names, would it be possible to store each 2D array into each item of "World" (however long the array "World" may be)? If so, how could I do this, and how would I refer to the items in the 2D arrays within "World"?

Should I be looking into using objects and/or classes? How do you do this or do you have a good place to reference me to? I have been looking online for some time and haven't found a solution yet. Any help is greatly appreciated.

Answer 1

You might be able to use a 3d array for this, or whats known as a Jagged Array (or array of arrays)

Sub ThreeDArray()
    Dim World() As String ' or type of your choice

    ReDim World(0 To 4, 1 To 3, 1 To 2)

    World(5, 1, 2) = "a"
    Debug.Print World(5, 1, 2)
End Sub

---

Sub JaggedArray()
    Dim World() As Variant
    Dim MyArray() As String ' or type of your choice
    Dim i As Long, j As Long

    ' If all elements of World are the same size
    ReDim World(0 To 9)
    ReDim MyArray(1 To 2, 1 To 3)
    For i = LBound(World) To UBound(World)
        World(i) = MyArray
    Next

    ' Or, if each element of World is different size
    ReDim World(0 To 9)
    For i = LBound(World) To UBound(World)
        ReDim MyArray(0 To i, 0 To (i + 1) * 2)
        World(i) = MyArray
    Next

    ' to access elements
    World(5)(1, 2) = "a"
    Debug.Print World(5)(1, 2)
End Sub

Answer 2

In my opinion I would use a collection. You can then have collections of collections. Collections are good because you can referece the "Key" and get the corresponding value...

   Public Function MultiDimensionalCollection() as Collection
       Dim tempColl as Collection
       Set MultiDimensionalCollection = new Collection

       For i = 1 to 100
           Set tempColl = New Collection
           tempColl.Add "Value", "Key-" & i 
           tempColl.Add "Value2", "Key2-" & i
           tempColl.Add "Value3", "Key3-" & i
           MultiDimensionalCollection.Add tempColl, "Key-" & i
       Next i

   End Function

You can obviously fill them with anything (objects, range values, etc.). Just make sure you don't duplicate the "KEY" value or you will get an error. Let me know if you want sample code for filling them from ranges or recordsets or whatever you'd like. Thanks, Brian.