3 dimension array using Excel names

[kossrs]

Hello

I want to use name specification of 3-dim array (sheet, column, raw).

First define var "rw" using Menu->Insert->Name->Insert as
=Sheet1:Sheet3!$B$4:$D$5

Haw can i access any cell in "rw" range using VBA?

Construction -----Range("rw")----- cause the 1004 error - Method
'Range' of object 'Global' failed

Best regards,
Konstantin

 

[Joel]

VBA will not accept a worksheet name that referes to more than one worksheet.
To get multiple sheets you could do something like this

myaddress = Range("rw").Address
For Each sh In ThisWorkbook.Sheets
sh.Range(myaddress) = "123"
Next sh

 

[Bernie Deitrick]

Konstantin,

Well, you have found out that Excel isn't a fully featured 3D spreadsheet. 3D names are only usable
by certain worksheet functions. But, you can get around the limitation by playing with the name's
RefersTo string. See the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim my3DName As String
Dim myR As Range
Dim myNR As String
Dim myShts As String
Dim Sht1 As String
Dim Sht2 As String
Dim iSht1 As Integer
Dim iSht2 As Integer
Dim strAdd As String
Dim lR1 As Long
Dim lR2 As Long
Dim iC1 As Integer
Dim iC2 As Integer

my3DName = "RW"

myNR = Mid(ActiveWorkbook.Names(my3DName).RefersTo, 2)
strAdd = Mid(myNR, InStr(1, myNR, "!") + 1)
myShts = Replace(Left(myNR, InStr(1, myNR, "!") - 1), "'", "")
Sht1 = Left(myShts, InStr(1, myShts, ":") - 1)
iSht1 = Worksheets(Sht1).Index
Sht2 = Replace(myShts, Sht1 & ":", "")
iSht2 = Worksheets(Sht2).Index
lR1 = Range(strAdd).Cells(1).Row
lR2 = Range(strAdd).Cells(Range(strAdd).Cells.Count).Row
iC1 = Range(strAdd).Cells(1).Column
iC2 = Range(strAdd).Cells(Range(strAdd).Cells.Count).Column
MsgBox "The name """ & my3DName & """ refers to" & Chr(10) & _
"Worksheets " & IIf(iSht1 < iSht2, iSht1, iSht2) & _
" to " & IIf(iSht1 < iSht2, iSht2, iSht1) & ", " & Chr(10) & _
"Rows " & lR1 & " to " & lR2 & ", " & Chr(10) & _
"Columns " & iC1 & " to " & iC2 & "."

End Sub

 

[Alan Beban]

Hello

I want to use name specification of 3-dim array (sheet, column, raw).

First define var "rw" using Menu->Insert->Name->Insert as
=Sheet1:Sheet3!$B$4:$D$5

Haw can i access any cell in "rw" range using VBA?

Construction -----Range("rw")----- cause the 1004 error - Method
'Range' of object 'Global' failed

Best regards,
Konstantin

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
want to consider the following:

After running

Sub ab1()
Dim rng1 As range, rng2 As range, rng3 As range
Dim arr, x
Set rng1 = Sheet1.range("b4:d5")
Set rng2 = Sheet2.range("b4:d5")
Set rng3 = Sheet3.range("b4:d5")
arr = Array(rng1, rng2, rng3)
rw = Load3D(1, "XY", arr)

At this point you can access any element in rw by row, column, sheet
with, for example,

elem = rw(2, 2, 1) for 2nd row, 2nd column, 1st sheet

And if before exiting Sub ab1 you execute

Save3DInNames rw, "rw"
End Sub

You can later retrieve the array with

Sub ab2()
Dim rw
rw = Load3DFromNames(1, "rw", [rwPlaneXY0])
. . .

and again access any element by row, column, sheet.

And after running Sub ab1, on any worksheet you can access any element
with, e.g.,

=INDEX(rwPlaneXY0, 1, 2) for row 1, column 2, Sheet1, or
=INDEX(rwPlaneXY2, 2, 3) for row 2, column 3, Sheet3

I.e., the XY indices are 0-based, the Sheet indices 1-based.

Alan Beban

 

[kossrs]

Thank you very much for the answers