feanor_za
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
My question is simple. Whilst the compiler doesn't object to that approach, how do you access an element of the 'third' array?
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
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
ASKER
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
Nicely done. That wrapper is definitely a perfect way to handle it.
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.
ASKER
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.
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
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
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
Gotcha,
Thanks for clarifying.
Cheers,
WC
Thanks for clarifying.
Cheers,
WC
patrick,
Both methods worked for me. I like yours better though, as it is more efficient.
WC
Both methods worked for me. I like yours better though, as it is more efficient.
WC
Brilliant idea. I am curious now to see if it works :)
Regards,
Patrick