How to distinguish between named ranges defined with same name on 2 different workbooks?

[Kelvin Stott]

Hello, I have 2 different versions of a workbook open, both contain the same named range and scope at workbook level. How do I distinguish between these and refer to the named range in one workbook or another?

Initially I thought I could use something like Workbook1.Range("MyNamedRange") vs Workbook2.Range("MyNamedRange"), but I realize that the range object does not link directly to a workbook object, and I would rather avoid doing this via a worksheet object, given that the ranges are scoped at workbook level.

Meanwhile it is not clear to me which workbook I would be referring to with just Range("MyNamedRange") and no workbook reference. Hmmm...

 

[JoeMo]

You can activate the workbook that contains the named range you wish to refer to before referring to the named range for that workbook.

Sub test()
'assume both workbooks have a range named "dog" scoped at workbook level
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks("Book1"): Set Wb2 = Workbooks("Book2")
Wb2.Activate: Range("dog").Select
Wb1.Activate: Range("dog").Select
End Sub

 

[Kelvin Stott]

You can activate the workbook that contains the named range you wish to refer to before referring to the named range for that workbook.

Sub test()
'assume both workbooks have a range named "dog" scoped at workbook level
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks("Book1"): Set Wb2 = Workbooks("Book2")
Wb2.Activate: Range("dog").Select
Wb1.Activate: Range("dog").Select
End Sub

Thanks, it's a nice idea, but I was also hoping to avoid selecting or activating anything as I need to keep workbook_activate and other events enabled in the background. Plus I want to keep both workbooks in the background, out of sight behind a third (active) workbook which contains the VBA code.

Is there no direct way to distinguish and refer to these two ranges by explicit reference, while keeping the 2 workbooks in the background?

 

[JoeMo]

You can define two ranges, say Nm1 and Nm2, via activation at the outset and then work with those ranges without having to activate/select further.

Sub test()
'assume both workbooks have a range named "dog" scoped at workbook level
Dim Wb1 As Workbook, Wb2 As Workbook, Nm1 As Range, Nm2 As Range
Set Wb1 = Workbooks("Book1"): Set Wb2 = Workbooks("Book2")
Wb2.Activate: Range("dog").Select: Set Nm2 = Selection
Wb1.Activate: Range("dog").Select: Set Nm1 = Selection
Nm2.Value = 2
Nm1.Value = 1
End Sub

 

[Kelvin Stott]

Trouble is I am opening these workbooks in read only mode in the background, within the code itself, and I really want to avoid activating them into the foreground.

Besides I have always read and understood that it is best to avoid activating and selecting objects, where possible, and instead refer to objects by explicit reference, to avoid referring to the wrong object where selection or activation may be slow and/or unreliable (e.g., especially if the object is protected, with selection disabled), or where it may initiate some chain of events. Usually for this reason I try to follow this principle religiously, so am struggling to understand why this can't be done with named ranges at the workbook level...

So basically I still need an explicit object reference, based on the named range as well as a workbook reference, but without activating or selecting anything.

 

[Kelvin Stott]

So I did some more research on my own, and found the required syntax to answer my original question:

Wbk1.Names("MyNamedRange").RefersToRange

This gives a fully qualified explicit reference which includes the named range as well as the appropriate workbook reference, exactly what I needed.

Meanwhile I also found this very useful thread in another forum:

How to efficiently code to access global named ranges in VBA

I am looking for alternative, general coding methods for dealing with global named ranges, in VBA. I'm hoping for answers, here, with some new generalized suggestions and approaches. I suggest...

stackoverflow.com