how do I refer to a multi-sheet named range

[dlh]

In VBA, how can I reference a named range that is pre-defined with cells
in various sheets of a workbook?

As far as I know, the following only works for name ranges that are
restricted to a single sheet:

Set MyRange = ThisWorkbook.Sheet(iSheet).Range("myNamedRange")

But myNamedRange is already defined as something like:

Sheet1!$A$1, Sheet2!$B$4, etc.

Thanks for any advice.
-dlh

 

[Dave Peterson]

I don't think that name refers to a range. A range has a parent that holds that
range--your name doesn't have a parent.

But you could parse the string that the name refers to and pick out the cells
that way:

Option Explicit
Sub testme01()

Dim myStr As String
Dim mySplit As Variant
Dim iCtr As Long
Dim myCell As Range

myStr = ThisWorkbook.Names("test1").RefersTo
mySplit = Split(myStr, ",")

For iCtr = LBound(mySplit) To UBound(mySplit)
For Each myCell In Application.Range(mySplit(iCtr)).Cells
MsgBox myCell.Address(external:=True)
Next myCell
Next iCtr

End Sub

split was added in xl2k. If you're using xl97, this won't work--but there are
other ways to split the string.

 

[dlh]

To make sure I'm understanding you correctly (please bear with me as my
understanding grows):

You're saying there is no way of defining a single range that contains,
for example, one cell on each page of a workbook. Instead, I have to
parse the Name string and process the various cells individually.

True?

-dlh

 

[JE McGimpsey]

To make sure I'm understanding you correctly (please bear with me as my
understanding grows):

You're saying there is no way of defining a single range that contains,
for example, one cell on each page of a workbook.

True. Ranges are child objects of worksheets. A range object only has
one parent sheet.

Instead, I have to parse the Name string and process the various
cells individually.

Possibly. If your string can be evaluated by *some* worksheet functions,
you could use the Evaluate() method instead.

Say the value of your name, say "myname" is "=Sheet1:Sheet4!$A$1"

You could then use

Debug.Print Evaluate("=SUM(myname)")

But you'd need to be very careful. For instance if "myname"s value is
instead

=Sheet1!$A$1,Sheet2!$A$1

then

Debug.Print Evaluate("=SUM(myname)")

will return an error, and

Debug.Print Evaluate("=COUNT(myname)"), Evaluate("=COUNTA(myname)")

will return the bogus values

0 1

even if both cells contain numeric values.