Loading 2-D Data Range into 3-D array – Seek VBA Solution

Question

I'd like to read a range of data, say A1:J20, into an array, but I'd like it to be a 3-dimensional array, called 'ArrayData3D'.  I seek a VBA solution with minimized looping, and limited (?) in that the root data cannot be transformed into a Table.

[Anchored to MS Office Pro Plus 2016, VBA v7.1.1116]

For the simpler 2-D case:

   Redim ArrayData(1 to 20, 1 to 10)  as Variant      'A1:J20 = 20 Rows & 10 Columns

            Then load the array via

   ArrayData=Range(Cells(1,1),Cells(20,10))  or  ArrayData = Range ("A1:J20")

But what I'd like to do is use a 3-dimension array, 'ArrayData3D', defined for (#Rows, #Columns, 'Layer'), using 3 layers, where Layer 1 is the root data in A1:J20, and Layers 2 and 3 can be used for other purposes.  Notionally, I'd like Layer 2 to be populated with the Column Header, per the logic :

ArrayData3D(R,C,2) = Cell(R=1,C)

Thus, ArrayData3D(R,C,1) = root values and ArrayData3D(R,C,2) = Column Headers

Since my 'real' dataset is large, I'm trying to avoid the looping

For each cel in Range("A1:J20")

  ArrayData3D(cel.row,cell.column,1) .value = cells(cel.row,cel.column) .value

  ArrayData3D(cel.row,cell.column,2) .value = cells(1,cel.column).value

Next cel

Are there Dim/Redim and direct ArrayData3D= statements to readily load Layers 1 and 2 without the looping ?

Answer 1

I would do it this way.

Andreas.

Sub Test()
Dim Where As Range

Dim ArrayData2D As Variant
Dim ArrayData3D As New Collection

'Refer to some cells
Set Where = Range(Cells(1, 1), Cells(20, 10))

'Get the 1st set of value
ArrayData2D = Where.Value
'Store
ArrayData3D.Add ArrayData2D, "Layer 1"

'Overwrite with something
Where.Value = "x"

'Get the 2nd set of value
ArrayData2D = Where.Value
'Store
ArrayData3D.Add ArrayData2D, "Layer 2"

'Reload the 1st set
ArrayData2D = ArrayData3D.Item("Layer 1")
'Flush into the sheet
Where.Value = ArrayData2D
End Sub

Answer 2

Here is another idea.

Sub Test()
Dim Ws As Worksheet
Dim Where As Range
Dim Array3D
Dim Layer As Long

'Define a range
Set Where = Range("A1:T20")

'Create space for all sheets
ReDim Array3D(1 To Worksheets.Count)

For Each Ws In Worksheets
Layer = Layer + 1
'Read in the data
Array3D(Layer) = Ws.Range(Where.Address).Value
Next

Layer = 2
Dim Row, Col, Value
Row = 3
Col = 7
Value = Array3D(Layer)(Row, Col)
End Sub

Answer 3

Accordingly, I want to find a speedy, non-looping alternative to :

I already shown you an alternative. In VBA there is no way to load data into a 3D array without a loop.

You can use the way I've shown to load the data into a 3D array first, then use a loop to copy the data into your 3D array.

That loop is much faster as your loop, because the bottleneck in your code is the access to the cell.

But why copy the data from one array into another? If you have N 2D-arrays is the same as one 3D array with N layers...

If you think outside the box, you will see that this is the quickest way. 3D arrays are difficult to handle and therefore slower.

Andreas.

Answer 4

@ Andreas, what I desire is the syntax, if it exists, to directly read the 2-D Range("A1:J20") into a 3-D array.  The source data should not be disturbed.

As a slightly different scenario for me ...

Suppose I have data in A1:J20 on each of 4 sheets, Sheet1, Sheet2, Sheet3, and Sheet4.  I wish to capture the data from all 4 sheets in a single 3-D Array, 'Array3D'.

My quest is to find the simplest means to load the data into Array3D, where

   Array3D(r,c,1) is the data from Sheet 1

   Array3D(r,c,2) is the data from Sheet 2

   Array3D(r,c,3) is the data from Sheet 3

   Array3D(r,c,4) is the data from Sheet 4

Accordingly, I want to find a speedy, non-looping alternative to :

For sh = 1 to 4

  [ set DataSheet = Sheet1, 2, 3, or 4 ]

  With DataSheet

    For r=1 to 20

      For c=1 to 10

        Array3D(r,c,sh) = .cells(r,c)

      Next c

    Next r

  End with

Next sh

    -- Jon K