Referencing named ranges with dual scope on same sheet

Question

Here's the situation:

First Named Range:
Name = "DualRange"
Worksheet = "Sheet1"
Scope = Workbook
Value = "global"

Second Named Range:
Name = "DualRange"
Worksheet = "Sheet1"
Scope = Sheet1
Value = "local"

Now

Worksheets("Sheet1").Range("DualRange").Value

will always resolve to "local"

But

Range("DualRange").Value

resolves to "local" if Sheet1 is active and "global" if sheet1 is not active.

Can someone please tell me how to reference the workbook scope range when sheet1 is active?

Answer 1

Using Application.Range("DualRange") will always resolve to Global (even when Sheet1 is active or when the VBA code being executed resides in Sheet1 Module).

Answer 2

You use: ThisWorkbook.Names(Index).RefersToRange

Since the names are the same, you'll have to find the correct one by index and use that index number to refer to that range.

You can do this by looping through the collection of names and finding the one that does not have an "!" in its name or add a comment to them and then use the loop to find the comment.

Answer 3

How about using a dictionary at the start of your code to access all global variables:

Set d = New Scripting.Dictionary

For Each n In ActiveWorkbook.Names

        If TypeOf n.Parent Is Workbook Then 'check to verify name is global
            d.Add n.Name, n                 'add name to dictionary
        End If

Next n

Then:

d("DualRange").RefersToRange.Value

Resolves to "global" as required, regardless of which sheet is active.

Answer 4

Thank you for your responses. I should have clarified that I will know the scope of the range. I am looking for a reasonably elegant solution to accessing the "global" range in the above situation when Sheet1 is active. The method I am currently using is :

disable screen updates
get name of current active sheet
activate a sheet other than Sheet1
get value of Range("DualRange").Value
restore previously active sheet
enable screen updates

Would be very interested if anyone can suggest a better solution. Thanks