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

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")

Answer 2

JT,

Re:  "If I can incorporate the 2 2-dimensional arrays into a single 3-dimensional array"

You can change the size of the last dimension (only) of an array.  Perhaps that is what you are referring to...

'Setup array size...

   Dim arr() as Double

   ReDim arr(1 to 100, 1 to 3)

'Resize/change array by adding 2 columns...

   ReDim Preserve arr(1 to 100, 1 to 5)

'The above requires a 'dynamic' array - one created using the ReDim statement.

'An array established using a fixed size - "Dim arr(1 to 100, 1 to 3)" - cannot be changed.

'--- 

'There are other limitations involved, check the Excel help file under "Declaring Arrays" and "Using Arrays".

'Obviously, one can just create a large array, to start with, that is big enough to contain all of the data.

'---

'FWIW, an individual row or column of an array can be returned using the Excel Worksheet Function "Index".

   Dim arr1 as Variant

   arr1 = Application.Index(Arr, 0, 1) 'returns 1st column of arr.

'---

Posting your code and before/after examples will be necessary if you want me to stop guessing.

'---

"XL Professional" - commercial excel add-in (xl97 thru xl2010)

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 3

I do use Option Base 1 for the variant arrays. I want to use a single loop to populate the 2 2-dimensional arrays. Currently I have to use an 'If' statement to identify which of the 2-dimensial arrays to populate. If I can incorporate the 2 2-dimensional arrays into a single 3-dimensional array, the code can become faster and more compact...

Answer 4

Some array concepts...

ShtArray1(3, 50) is an array with 4 rows and 51 columns.

Arrays are zero based unless you specify otherwise. 

So you are actually sizing the arrary as ShtArray1( 0 to 3, 0 to 50)

Also,

Range ("A1:C50) has 50 rows and 3 columns. and will not fit in your array.

Using a Variant variable to contain a set of range values is usually a better option...

  Dim arrSht as Variant

  arrSht = Range ("A1:C50).Value

 'the above creates a 2 dimensional array (1 to 50, 1 to 3) containing the range values.

  So arrSht(3, 2) contains the value in cell B3

In addition,

A 3 (or more) dimensional array (2, 3, 50) is a difficult concept for most (it is not whatever you think it is), and is usually not needed.

'---

What are you trying to do?

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)