Craetion of a Range Name Specific to a single sheet

B

Bob Phillips

When naming the range name, precede it with the sheet name, such as

Sheet1!myName

or

'Master Sheet1!myName

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dave Peterson

Just a typo:

'Master Sheet1!myName
should be:
'Master Sheet1'!myName

(an additional apostrophe)
 
J

Jim May

Thanks Bob (and of course Dave..) -- This helps me to progress
through things - after following your instructions I "look-back" and
see that in creating such a sheet-specific range-name - it once
created is only visible from the "home" sheetname, and not the others.

If In a VBA routine I were to Refer to these rangenames do I need
to do anything SPECIAL to Read them (being Sheet Specific)?

Thanks for your help..
Jim
 
D

Dave Peterson

You can use:

dim myVal1 as variant
dim myVal2 as variant

myval1 = worksheets("sheet1").range("test1").value
myval2 = worksheets("sheet2").range("test1").value

Same range name on different sheets--but fully qualified.
 
J

Jim May

Are you saying that If I were to use:

myval1 = range("test1").value
myval2 = range("test1").value

That there might be a problem with
myval1 and
myval2 ?

Jim
 
D

Dave Peterson

Nope. No problem with the code at all.

But if the code is in a General module and that name is a local name used on the
activesheet, then myVal1 and myVal2 will refer to the same range (Test1) on the
Activesheet.

If you want to retrieve the values from two different ranges on different sheets
that have the same name, then you'll want to qualify each with the correct
worksheet.

Excel is pretty forgiving.

If you have a line like:
msgbox Range("test1").address(external:=true)
And you have a global range named Test1, you may even get the results you
want--depending on where the code is and what sheet is active and if there is a
sheet level name on that activesheet.

But I've found that lots of problems go away by qualifying the range with the
worksheet.

Do some experiments:

Create a test workbook with 3 worksheets.
Put a workbook level name (test1) on sheet1
put a worksheet level name (test1) on sheet2
don't put any names on sheet3.

Put this code behind all 3 worksheets:
Option Explicit
Sub testme()
MsgBox Range("test1").Address(external:=True)
End Sub

Run each procedure.

Put the same code in a general module and activate each sheet and run the code
(3 times total).

You'll see some problems with the code and maybe see some things that you didn't
expect.

And if you delete the global name, you can do a few more experiments to see how
the code reacts.


Jim said:
Are you saying that If I were to use:

myval1 = range("test1").value
myval2 = range("test1").value

That there might be a problem with
myval1 and
myval2 ?

Jim
Click to expand...
 
Top