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

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" wrote:

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

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
"NateBuckley" wrote in message
...
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" wrote:

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

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.Add ress
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
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

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
"NateBuckley" wrote in message
...
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" wrote:

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

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
"Jim Thomlinson" wrote in message
...
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.Add ress
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
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

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
"NateBuckley" wrote in message
...
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" wrote:

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

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" wrote in message
...
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



"Jim Thomlinson" wrote in
message ...
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.Add ress
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
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

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
"NateBuckley" wrote in message
...
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" wrote:

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

Peter
I appreciate the time you gave this. It has helped me. Otto
"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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



"Jim Thomlinson" wrote in
message ...
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.Add ress
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
--
HTH...

Jim Thomlinson


"Otto Moehrbach" wrote:

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
"NateBuckley" wrote in message
...
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" wrote:

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

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

I forgive you. Otto
"Peter T" <peter_t@discussions wrote in message
...
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