# How do I Combine several 2D arrays into a 3D Array in VBA

I have several (7) large datasets spread across workbooks.  Each workbook contains a single datasets of size 100x361.

I have managed to transfer these datasets each to their own 2D array.

I would like to combine these 7 2D arrays into a single 3D array.

Is there a way I can do so without having to step through all of the elements in each array?

Secondly, if the datasets become larger (say 7x2001x361) is using arrays for the data still feasible or would another method be quicker?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior Risk ManagerCommented:
Have you tried the following:

Dim combined(1 to 7) as Variant

combined(1) = <your first 2D array>
...
combined(7) = <your last 2D array>

Thus, an array of 2D arrays, giving a 3D array, no?

WC
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
WC,

Brilliant idea.  I am curious now to see if it works :)

Regards,

Patrick
0
Senior Risk ManagerCommented:
It worked for a small example I did.  I just watched in the locals window of the VBE.  I used to do C++ back in college, and I know that's how we always tackled this issue, so I figured what the heck.

WC
``````Sub arrays()
Dim first(1 To 10, 1 To 10) As Variant
Dim second(1 To 10, 1 To 10) As Variant
Dim third(1 To 2) As Variant

For i = 1 To 10
For j = 1 To 10
first(i, j) = i * j
second(i, j) = i + j
Next j
Next i

third(1) = first()
third(2) = second()

End Sub
``````
0
Commented:
My question is simple. Whilst the compiler doesn't object to that approach, how do you access an element of the 'third' array?
0
Senior Risk ManagerCommented:
Patrick,

Now that was a fantastic question.  It took me a few minutes to figure out.  You will have to use two steps to access an element.

For example, to grab element (1,1,1)
i = third(1)
i = i(1,1)

Here is the full code, where I never technically Dim i, so I believe it is variant.

WC
``````Sub arrays()
Dim first(1 To 10, 1 To 10) As Variant
Dim second(1 To 10, 1 To 10) As Variant
Dim third(1 To 2) As Variant

For i = 1 To 10
For j = 1 To 10
first(i, j) = i * j
second(i, j) = i + j
Next j
Next i

third(1) = first()
third(2) = second()

i = third(1)
i = i(1,1)
End Sub
``````
0
Author Commented:
The easiest is just to define a function as variant and use it like you would any other array.

``````Function ReadReturns(i, j, k As Integer) As Variant

Dim AnArray(), final As Variant

ReDim AnArray(Scenarios, MaxMonths)
AnArray = Returns(i)

final = AnArray(j + 1, k + 1)

End Function
``````
0
Senior Risk ManagerCommented:
Nicely done.  That wrapper is definitely a perfect way to handle it.
0
Senior Risk ManagerCommented:
Oh, I just noticed you are adding 1 to your j and k.  Not sure you need that unless it's specific to your project.
0
Author Commented:
It is specific to my project.

I draw data from a closed worbook by using the function at the bottom.  The only problem in that case is that the array starts from 1 to .... rather than from 0 to ........

I prefer being consistent and starting from 0.
``````For i = 0 To 6

Set xlWB = xlApp.Workbooks.Open(FileLoc(i - 1))
AnArray = xlWB.ActiveSheet.Range(ArRange).Value
Returns(i) = AnArray
xlWB.Close

Next i
``````
0
Commented:
WarCrimes,
That didn't work for me but the code below does. It appears it can be done directly - the syntax looks odd but it works!
Patrick

``````Option Explicit
Option Base 1

Sub arrays()
Dim first(1 To 10, 1 To 10) As Variant
Dim second(1 To 10, 1 To 10) As Variant
Dim third(1 To 2) As Variant
Dim k()
Dim j As Long
Dim i As Long

For i = 1 To 10
For j = 1 To 10
first(i, j) = i * j
second(i, j) = i + j
Next j
Next i

third(1) = first()
third(2) = second()
MsgBox third(2)(1, 10)

End Sub
``````
0
Senior Risk ManagerCommented:
Gotcha,

Thanks for clarifying.

Cheers,

WC
0
Senior Risk ManagerCommented:
patrick,

Both methods worked for me.  I like yours better though, as it is more efficient.

WC
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.