Refactor named ranges to work with Sheet/Workbook scopes

[sdementen]

This may be related to issue #156 but not sure.

In Excel, a named range can be defined either at workbook scope (per default when typing a name for a range) or at worksheet scope (when creating the name through the Name Manager GUI and choosing a sheet as scope OR when typing a name for a range that already exists at workbook scope).
The range referred by a named range (irrespective of the scope : workbook or sheet) can any range in any sheet of the workbook.

It could be useful to have access to the named range at sheet scope through the notation:

sh.names

as we have currently for named range at workbook scope with

wb.names

[fzumstein]

Umm so: As described in #156 and also here, xlwings currently doesn't handle global named ranges, but only those tied to sheets.
However, sheet-bound named ranges are still contained in the Workbook names collection - this follows the logic of VBA. I don't think VBA offers access to named ranges through Sheet objects.
So your statement that wb.names contains only the named range at workbook scope is wrong and hence your sh.names would be part of in wb.names...

[sdementen]

you are right, the workbook.Names collection has them all.
but the worksheet object has also a Names collection with only the named
range in its scope.
If we have the following names

The sheet scope names can be accessed either as
wb.Names("Sheet1!foo")
or as
Sheet1.Names("foo")
The Workbook scope names are not easily accessed if there are sheet scope
named range with the same names.
wb.Names("baz") is OK but wb.Names("foo") gives the Sheet1 scope level.

When using Range(name_of_named_ranged), it apparently depends on the active
sheet. If the name exists at the scope of the active sheet, it is this
named range that is selected, otherwise it it the workbook one that is
selected. If both a sheet and workbook name is defined, the sheet has
preference.

On Sat, Oct 17, 2015 at 12:49 AM, Felix Zumstein notifications@github.com
wrote:

Umm so: As described in #156
https://github.com/ZoomerAnalytics/xlwings/issues/156 and also here
https://zoomeranalytics.uservoice.com/forums/269851-xlwings/suggestions/10096362-globalized-named-ranges-across-sheets,
xlwings currently doesn't handle global named ranges, but only those tied
to sheets.
However, sheet-bound named ranges are still contained in the Workbook
names collection - this follows the logic of VBA. I don't think VBA offers
access to named ranges through Sheet objects.
So your statement that wb.names contains only the named range at workbook
scope is wrong and hence your sh.names would be part of in wb.names...

—
Reply to this email directly or view it on GitHub
https://github.com/ZoomerAnalytics/xlwings/issues/256#issuecomment-148858172
.

[sdementen]

After some additional investigation in the mechanism of the named ranges, with the following setup

I have the following results when calling with sheet1 active

with sheet2 active

with sheet3 active

(empty cells means the scenario triggers an error in VBA)

To get the workbook scope range, you need to have as active sheet a sheet that does not have the name defined also at the sheet level or you need to iterate explicitly in the workbook.Names collection looking for a named range with Name = "name_of_the_named_range". Just calling workbook.Names("foo") returns in fact the sheet1 scoped named (which looks like a weirdness in Excel or a bug)