Refer to Range in Another Workbook

[Merf]

I try to keep my macro code as simple and readable as possible.
I know that I can refer to a named range (with global scope) within ThisWorkbook by simply using the range name in brackets, such as:

Please Login or Register  to view this content.

But how can I most simply refer to a (global) range name in another (open) workbook? The other workbook will be open, and I will have assigned a variable name to it. For example, suppose my macro opens a second file named Budget2018.xlsx, which contains a sheet with the codename MainSheet, and which contains a named range (on MainSheet, with global scope) called TotalIncome (which also does NOT match any named range in ThisWorkbook). Can I do this:

Please Login or Register  to view this content.

or is some other syntax required?

[AlphaFrog]

I think you have to qualify the sheet because its in an external workbook.

Please Login or Register  to view this content.

[Merf]

So if that sheet is already codenamed MainSheet (via the sheet Properties, not just the name displayed on the sheet tab), I could use:

Please Login or Register  to view this content.

correct?

[AlphaFrog]

I don't think code names are allowed to reference sheets in an external workbook. You could use the sheet tab name (as I use in my previous reply) or the sheet index number. Alternatively, set a worksheet variable when you open the BudgetFile.

[Merf]

I avoid using sheet tab names or sheet numbers, since users can change those (and then my macros won't work properly).
I like your idea of using a worksheet variable. Could I do that (using the sheet codename) right after opening the file (when it's still the active workbook):

Please Login or Register  to view this content.

or would I still need to use the tab name or sheet number?

[AlphaFrog]

I avoid using sheet tab names or sheet numbers, since users can change those (and then my macros won't work properly).
I like your idea of using a worksheet variable. Could I do that (using the sheet codename) right after opening the file (when it's still the active workbook):

Please Login or Register  to view this content.

or would I still need to use the tab name or sheet number?

You still couldn't directly use a code name for an external workbook sheet regardless if it's the active workbook.

Here's a kludge around it using the sheet code name.

Please Login or Register  to view this content.

Alternatively, you could loop through all the sheets in BudgetFile and check for a specific value in a cell to determine which one is the MainSheet.

[xlnitwit]

You can use a codename in another workbook directly, but only by setting a reference to that workbook's project.

[AlphaFrog]

You can use a codename in another workbook directly, but only by setting a reference to that workbook's project.

That's what I suggested, but I don't consider that being Directly.

[xlnitwit]

Where did you suggest that? I can't see that anywhere.

[AlphaFrog]

Please Login or Register  to view this content.

[xlnitwit]

Ah, that's not what I mean. I mean set a reference using Tools- References (you have to change the project name from the default VBAProject) and you can then directly refer to the sheet using projectname.codename.