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.

If you're in VBA, then you want to use either a Collection or Scripting.Dictionary for something like this. Google them, there's thousands of examples and help sites out there. — RBarryYoung
– RBarryYoung, Commented Aug 30, 2013 at 17:38

Brian B. · Accepted Answer · 2013-08-31 00:33:12Z

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.

chris neilsen · Accepted Answer · 2013-08-30 22:07:06Z

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

Collectives™ on Stack Overflow

Storing arrays within arrays

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related