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?
feanor_zaAsked:
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.

Cory VandenbergSenior 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

Your issues matter to us.

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

Start your 7-day free trial
Patrick MatthewsCommented:
WC,

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

Regards,

Patrick
0
Cory VandenbergSenior 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

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

patrickabCommented:
My question is simple. Whilst the compiler doesn't object to that approach, how do you access an element of the 'third' array?
0
Cory VandenbergSenior 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

Open in new window

0
feanor_zaAuthor 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)
 
ReadReturns = final
 
End Function

Open in new window

0
Cory VandenbergSenior Risk ManagerCommented:
Nicely done.  That wrapper is definitely a perfect way to handle it.
0
Cory VandenbergSenior 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
feanor_zaAuthor 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

Open in new window

0
patrickabCommented:
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

Open in new window

0
Cory VandenbergSenior Risk ManagerCommented:
Gotcha,

Thanks for clarifying.

Cheers,

WC
0
Cory VandenbergSenior 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.