How do I create and use 3D ranges in Excel-VBA

It is easy to create and use 2D range in excel. The following code will do

Option Explicit

Sub Demo3DRange()
Dim sh As Worksheet, r As Range, vData
Dim Row As Integer, COl As Integer
Set sh = ThisWorkbook.Sheets("Sheet1")
Set r = sh.Range(sh.Cells(1, 1), sh.Cells(100, 2))
vData = r.Cells.Value
For Row = 1 To 100
For COl = 1 To 2
Debug.Print vData(Row, COl)
Next COl
Next Row
End Sub

When we execute the above code, the variant vData will become a 2D array of 100*2.

Now my question is following:

A)How do I create a 3D range in VBA code
B)Can I assign that 3D range to a variant, and get a 3D array, just as I get 2D array in case of 2D range

Thanks

-Rahul Mehta

Answer this question

How do I create and use 3D ranges in Excel-VBA

Behrooz PB
Hi ChasAA,

Yes. in 3D Range the data is across the sheet
eg say you have 5 sheets in workbook named as sheet1 ... sheet6

In sheet1's A1 cell, you can type a formula as

=sum(Sheet2:Sheet6!A1:B100)

And this will add 5*100*2 = 1000 i.e. 200 cells on each of the 5 sheets

Creating and using 3D range on formuale is cool and easy.

But I cant figure out how it can be defined and accessed in VBA

-Rahul Mehta
CarlosHouse
Rahul,

Have a look at:

http://www.csc.liv.ac.uk/~frans/COMP101/AdditionalStuff/multiDarrays.html

Probably explains it better than I did.

ChasAA
geliser131
You would dimension the array as:

DIM MultiArray(5,100,2)

1 to 5 would be the pointer to the sheet number

1-100 would be the pointer to the row number

1-2 would be the pointer to the column number

I am loosely using the term "pointer" not to be confused with "pointers" in C language.

suppose you have 5 grids 100 squares down by 2 squares across

to read what you have in the square of grid 3, 25 rows 2 and 2nd columns across

You would want to read element MultiArray(3,25,2) of the array.

I have stayed away from the statement "Cells) as this is a type of two dimensional array in itself.

Also, have to assume that Option Base 1 is set when using arrays

Hope this helps

ChasAA.

(Best way to picture it would be to have 5 pages of square gridded paper put together (like we used to have in Maths at school) .
Predator14567
Hello Rahul,

Surely if you want to reference a cell in the third dimension, it will have to be on another sheet

so if I had an array 3Darray(3,100,2)

element 1,1,1 would be on sheet1 row1 and col1

element 2,3,2 would be on sheet2 row3 and col2

or am I missing the point

ChasAA
How do I create and use 3D ranges in Excel-VBA

Answer this question