VBA - Question on Array

Question

When using Excel VBA to create macros, is it possible to create a single 3-dimensional array instead of two 2-dimensional arrays then copy a selected range.value into the 3-dimensional array by only selecting the 1^st^ array dimension instead of copying a selected range.value into each of the 2 dimensional arrays?

Instead of:

• Dim ShtArray1(3,50) as Variant
• Dim ShtArray2(3,50) as Variant
• ShtArray1 = Range(“a1:c50”).Value
• ShtArray2 = Range(“d1:f50”).Value

Use:

• Dim SheetArray(2,3,50) as Variant
• SheetArray(1) = Range(“a1:c50”).Value
• SheetArray(2) = Range(“d1:f50”).Value

<Mod's note - Changed the title for better clarity >

Answer 1

If you look closely, you will see that ShtArray has been dimensioned as a 2-dimensional array of 1000x20 to contain the worksheet...

     Option  Base 1

     Dim ShtArray(1000,20) as Variant

                          .

                          .

     ShtArray = Range("A1:T1000")

Answer 2

Thank you for your response. That is what I did to move the two worksheets. However, I was looking for a way to move an entire worksheet into the 3-dimensional array with a single statement by only specifying the 1st dimension (worksheet number) similar to how I moved the entire worksheet into the 2-dimensional array with a single statement:

     ShtArray = Range("A1:T1000")      - see above

Is this possible?

Answer 3

I am able to copy an entire worksheet of 20 columns x 1000 rows to a 2-dimensional array with a single statement as shown below...

Option Base 1

Dim ShtArray(1000,20)

          .

          .

          .

ShtArray = Range("A1:T1000")

Is it possible to copy the same worksheet to a 3-dimensional array with a single statement in a similar way where the 1st dimension is the worksheet number and the 2nd and 3rd dimensions are the row and column values as above? I want to be able to copy 2 worksheets to the array without have to copy each worksheet column-by-column and row-by-row...

Option Base 1

Dim ShtArray(2,1000,20)

.

.

.

ShtArray(1,?,?) = Sheets("Sheet1").Range("A1:T1000")

ShtArray(2,?,?) = Sheets("Sheet2").Range("A1:T1000")

I still do NOT understand how to move a 2 2-dimensional arrays to a single 3-dimensional array...          

•