named range scope...

[Mark Kubicki]

i get confused...

a named range can have:
a global level (to the entire workbook),
or
a worksheet level (to the active worksheet),
or
a ...

also, i've really looked, but can't find good information on how to define 1
level name vs. the other... (any direction on this ?)

 

[Bob Phillips]

Mark,

Just the two.

A workbook name is created by just inserting a name in the Names box, such
as myRange.

A worksheet name is created by adding the sheet name to the name in the
Names box, such as Sheet1!myRange. You can then also create Sheet2!myRange.
You can only add a worksheet name if that sheet is active, else you get an
error.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Kubicki]

thanks...

and could you tell how to do the same in VBA?

 

[Bob Phillips]

Pleasure.

With ActiveWorkbook.Names
.Add Name:="myRange", RefersTo:="=Sheet1!$A$1"
.Add Name:="Sheet1!myRange2", RefersTo:="=Sheet1!$A$1"
.Add Name:="Sheet2!myRange2", RefersTo:="=Sheet2!$A$1"
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Kubicki]

did you mean that:
.Add Name:="myRange", RefersTo:="=$A$1" (on the active sheet)?
whereas
the other 2 exmpl refer to cell $A$1 on Sheet1 or Sheet 2 respectively,
despite which sheet is active in the workbook?

 

[Bob Phillips]

Mark,

The first creates a workbook name, that is workbook global, the second
creates worksheet local name. It does not matter which sheet is active as I
use the sheet name qualifier in the name as well as in the range.

What I previously said (You can only add a worksheet name if that sheet is
active, else you get an error.) wasn't absolutely correct, as it can be done
if you include the correct refersto sheet as well as the name sheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Just another way to add a worksheet level name:

Option Explicit
Sub testme()
With ActiveSheet
.Range("a1").Name = "'" & .Name & "'!HiThere"
End With
End Sub

And if you're going to work with names, do yourself a big favor and get a copy
of this:

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) utility
"Name Manager.xla" from http://www.bmsltd.co.uk/mvp/