Scope of range names

[Otto Moehrbach]

Excel XP & Win Vista
I need some help on something rather basic and I'm embarrassed to ask. When
I MANUALLY assign a range name to a range, what must I do for the scope of
the range name to be the workbook? Same question for the scope to be the
sheet.
When I assign a range name via VBA, same 2 questions. For example:
Rng.Name="Doodle" 'Is the scope workbook or sheet?
Range("A1").Name="Doodle" 'Is the scope workbook or sheet?
With Sheet("TheSheet")
.Range("A1").Name="Doodle"
End With 'Is the scope workbook or sheet?

One more question: What/Where do I look at the range names in a workbook to
determine the scope of a range name? What do I look for? Thanks for your
time and I won't show my face for a while. Otto

 

[NateBuckley]

If I understand your question correctly I think you are talking about global
and local variables.

If you have the code in "ThisWorkBook

Public myRange as Range

Private Sub Workbook_Open()
myRange = Sheets("Sheet1").Range("A1:A5")
myRange.name = "Doodle"
End Sub

Then You create a global variable called myRange which is a Range Object.
The workbook opens and assigns this object (myRange) to the cells Sheet1 a1
to a5. This range is available to other sheets to use and I think also to
modules. So you can display it in a message box by using the line
msgBox( ThisWorkBook.myRange.name).

 

[Otto Moehrbach]

Nate
Thanks for that but I need more. How do I set the scope when I MANUALLY
assign range names? By VBA? Your example sets a global scope via VBA. How
about a local scope? Also, once a range name has been assigned, how can I
tell if it's a global or local scope? Thanks for your time. Otto

 

[Jim Thomlinson]

You are absolutely correct in your assesment of named ranges. There are 2
types that differ by scope. There are locally defined named ranges and
globally defined named ranges. Generally speaking that is not a really well
understood concept. A locally defined named ranges is attached to the
worksheet. It's scope does not extend beyond the worksheet so if you try to
reference it from another work sheet you will get a #name error. The other
type and the one most people think of is the globally defined named range and
it is attached to the workbook. It can be referenced from any sheet in the
workbook.

Since the scope of local named ranges does not extend beyond the sheet you
use the same name on each sheet and end up with multiple instances of the
exact same name. you can also have a local and a global named range with the
same name. It is important to note in this case that the local named range
takes precidence on the sheet where it is declared over the global name.

When we get into VBA things get a bit tricky since sheets hold ranges and
you can not have a range without defining a sheet for it is reside on. To
create a global named range you can use code like this...

ThisWorkbook.Names.Add "GlobalName", Sheet1.Range("A1:A10")
but to refer to it directly from thisworkbook you need to define do so like
this...
MsgBox ThisWorkbook.Names("GlobalName").RefersToRange.Address
You can also reference a global name from the worksheet object like this...
Msgbox Sheets("Sheet1").Range("GlobalName").address

if you do this
Sheets("Sheet1").names.add "LocalName", Sheets("Sheet1").Range("A1:A10")
the you reference it like this
Msgbox Sheets("Sheet1").range("LocalName").address

 

[Otto Moehrbach]

Jim
You are right. This is murky at best. Let me give you some direct
scenarios and, if you can take the time, tell me what scope I have created.
Via VBA:
Range("A1").Name="Doodle"
Sheets("TheSht").Range("A1")="Doodle"

Manually:
Select a range. Click Insert-Name-Define, type Doodle
And
Select a range. In the name box type Doodle.
Thanks for your time. Otto

 

[Peter T]

The two methods of creating a name you cite do exactly the same, ie they
create a Workbook level (aka global) name.

You could also use either of those methods to create a worksheet level name
(aka local) simply by prefixing the name with the sheet-tab name and an
apostrophe. (note with some sheet names you need to embrace the sheet name
with apostrophes - in code always include them to be on the safe side).

When you want to use a local name on its own sheet, just type the name
portion. To refer to a local name that belongs to some other sheet you need
to fully qualify it like sheetname!definedName.

Typically a worksheet (local) name refers to a range on its own sheet, but
it could refer to a range on any sheet, even a range in another wb.

You can have identically named local names on each sheet and a similar
global name. In the scenario of similar local/global names, there's no way
to refer to the global name when on the sheet with its own similarly named
local name.

Almost done...

All names exist in the workbook's Names collection, local names will be
prefixed with sheetname!
Worksheets also have a Names collection for their local names (which as said
co-exist in the workbook's Names collection)

That's just about all there is to it!

Regards,
Peter T

 

[Otto Moehrbach]

Peter
I appreciate the time you gave this. It has helped me. Otto

 

[Peter T]

Serious typo !

You could also use either of those methods to create a worksheet level
name (aka local) simply by prefixing the name with the sheet-tab name and
an apostrophe.

that apostrophe should have been an exclamation mark !

'sheet-name'!defined_name

Regards,
Peter T

 

[Otto Moehrbach]

I forgive you. Otto

Serious typo !


that apostrophe should have been an exclamation mark !

'sheet-name'!defined_name

Regards,
Peter T