Using Sheet Name in combination with a named range [VBA/365/MAC]

[LeighG]

Hi,

Office 365 / Mac OS

I have the following code which works perfectly. However I'd like to combine the Sheet Name (from the current workbook) in combination with the Named Range. I can't seem to get the syntax right.

 Cells(1, 1).Value = ThisWorkbook.Names("Balance").RefersToRange(1, 1)

Thanks!

 

[eduzs]

Try:
(Note that this to work "Balance" name scope should be only worksheet WorksheetName, not the workbook.)
(not tested in MacOS)

Cells(1, 1).Value = Sheets("WorksheetName").Names("Balance").RefersToRange(1, 1)

 

[mikerickson]

What does this mean "combine the Sheet Name (from the current workbook) in combination with the Named Range."

A sheet name is a string, a named range is a Range object. What does it mean to combine them?

 

[LeighG]

Try:
(Note that this to work "Balance" name scope should be only worksheet WorksheetName, not the workbook.)
(not tested in MacOS)

Cells(1, 1).Value = Sheets("WorksheetName").Names("Balance").RefersToRange(1, 1)

That works an absolute treat! Thank you.

Re: The scope, I can't see that you can set that when naming a range in Excel Mac. That was why I specifically wanted to name the sheet

 

[LeighG]

What does this mean "combine the Sheet Name (from the current workbook) in combination with the Named Range."

A sheet name is a string, a named range is a Range object. What does it mean to combine them?

As I wanted to specifically name the sheet the Range was from. @eduzs nailed it.

 

[eduzs]

You're welcome.

 

[LeighG]

That works an absolute treat! Thank you.

Re: The scope, I can't see that you can set that when naming a range in Excel Mac. That was why I specifically wanted to name the sheet

Just for completeness, it seems that in Excel for Mac:
If a named Range exists in multiple sheets in the Workbook then Scope = Worksheet (and your solution works perfectly)
If a named Range only exists once in the Workbook then Scope = Workbook (and adding the sheet name causes an error)

 

[eduzs]

Use the "Name Manager" under "Formulas" tab to see the scope of each existing names.
In Windows Excel (don't know in MacOS version) you can see, edit (change scope, etc.), create named ranges with this menu.
If scope is ThisWorkbook then use ThisWorkbook.Names....
If Worksheet then Sheets("WorksheetName").Names... or by relative position Sheets(SheetNumber).Names...

 

[LeighG]

Use the "Name Manager" under "Formulas" tab to see the scope of each existing names.
In Windows Excel (don't know in MacOS version) you can see, edit (change scope, etc.), create named ranges with this menu.
If scope is ThisWorkbook then use ThisWorkbook.Names....
If Worksheet then Sheets("WorksheetName").Names... or by relative position Sheets(SheetNumber).Names...

Thanks. That's the thing....the scope option doesn't exist in the Mac Name Manager. So I think the 'default' scope is as per my previous post.

 

[mikerickson]

To scope a name to the sheet level on a Mac, one the DefineName dialog enter the name qualified by sheet reference in the Name box

e.g. Sheet2!MyName will create a name MyName that is only valid on Sheet2.