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