Named Ranges and Named Ranges scope. Referencing a named range

[DocAElstein]

Creating named ranges and referencing named ranges

The discussions from the last post came to the conclusions that creating a named range will involve making/Adding a named range object to either the worksheets name objects collection or the workbook names objects collection. A code line like this was proposed:

NamesObjectToBe.AddedTo _ stringNameYouWant:=”___” , RangeItRefersTo:= ____


_...Well , bugger me sideways with a barge pole, we find that the actual syntax is just like that! But before we give the exact code lines lets refresh our memories about what all that is about in terms of practical considerations like…. what actually it means in the final actual real ( virtual ) Excel world is …_

Place where Excel will reference when given _ stringName _ , _ RangethatName RefersTo
____Place to Add a Name Object ____ stringName __ , __ RangethatName RefersTo


WorkSheets Scope: The information about the named range can be “got at” through referencing the Work sheet Name object that it “belongs to” , or the Workbook Names Object . We can define which of the Work sheet Name object it should “belong to”. We call this “scoping to a worksheet” and say that the name object is then “scoped to” the chosen Work sheet
!!!Note: The worksheet does not have to be that in which the named range is. In fact , it can even be a worksheet in another workbook.. Note further: The workbook must be open to do things like Creating , Adding , Scoping, but the named range can sometimes be used to access values from a closed workbook containing the named range object

Workbooks Scope The information about the named range can only be “got at” through referencing a Workbook Names Object. We call this “scoping to a workbook” and say that the name object is then “scoped to” the Workbook.
Note: The “scoped” workbook does not have to be that in which the named range is. It can be another workbook.. Note further: The workbooks must be open to do things like Creating , Adding , Scoping, but the named range can sometimes be used to access values from a closed workbook containing the named range object

https://www.thespreadsheetguru.com/b...ent-4189507335

Creating named ranges
So a typical pair of code lines, for example to give a name to the first cell in the second worksheet of the workbook in which you run the code, could be like…_

Dim wBuk As Workbook: Set wBuk = ThisWorkbook
'1_1) Add a named range object to this Workbook's Names collection object Workbooks Scope
__wBuk.Names.Add _ Name:="NameWkBkScoped", RefersTo:=wBuk.Worksheets.Item(2).Range("A1")

'1_2) Add a named range object to a worksheet's Names collection object WorkSheets Scope
wBuk.Worksheets.Item(1).Names.Add Name:="NameWkShtScoped", RefersTo:=wBuk.Worksheets.Item(2).Range("A1")

_.. and here in a in a demo code:
(Note:
_ The code starts by clearing any named ranges, so before you run the code, be sure there are no named ranges existing which you want . It is probably best to start with a new workbook
_ In this and further demo codes , two other sub routines can be Called, ( Sub FukOffNames() and Sub getWbNames. ) These codes can be found at these places:
http://www.excelfox.com/forum/showth...0802#post10802
https://pastebin.com/QEarx5Fc



The above code will give you a message something like:
1 "NameWkBkScoped" refers to the range ref "=Sheet2!$A$1" and can be referenced from any sheet in the Workbook "YourWorkbook.xlsm" ( Workbook Scope )

2 "Sheet2!NameWkShtScoped" refers to the range ref "=Sheet2!$A$1" and can be referenced only from worksheet with tab Name "Sheet2" ( Worksheet Scope ). ( That worksheet is in the workbook "YourWorkbook.xlsm" )


A couple of points to note are:
_ Excel has added the extra Sheet2! to the name of the worksheets scope named range. You cannot give a name with a “!” in it, so I expect this is Excel’s way of being able to tell if the name is worksheet scoped, and to which worksheet it is scoped.
_ The called code which produces that message, Sub getWbNames , I wrote shortly after I first learned about named ranges. I have written this sort of thing regarding the Name added to the worksheets name collection… can be referenced only from worksheet with tab Name "Sheet2" ( Worksheet Scope …. I mostly disagree with that now.

Referencing created/Added named ranges, Implicit defaults.
___ Implicit defaults: Excel and Excel VBA has a habit of trying to add things if you miss them out. Often it guesses correct, so you maybe never notice this, and are not aware that you missed something out. ( Most people have a bad surprise later when something unexpectedly does not work because Excel guessed wrong).
You need to be careful, when trying to get at a Named range, that you refer to it correctly, or else Excel may guess wrong as to where it “is” or rather where it “belongs” and to where it finds “internally” the information it wants . Unfortunately, it is difficult to be sure in some cases exactly what the full correct reference syntax is. This is because Excel has the annoying habit, of changing, in different situations, what you actually see of a reference string which you give it: Sometimes Excel will show just as much as it needs in order to guess correctly the full reference that it uses in some hidden place “internally”.

To be 100% sure of always getting at your named range FROM ANY WORKSHEET, NO MATTER WHAT THE SCOPE IS, then you can use a reference of the following form, which , as far as I know , will usually work to get at a named range, “MyNamedRange” in the workbook “MyWorkbook1.xls” ( Note that this formula reference will also work in a different workbook, and further, it will work whether or not the workbook “MyWorkbook1.xls” is open or closed )
“='C:\Desktop\[MyWorkbook1.xls]Sheet1'!MyNamedRange”
Pseudo like
_ “ = ' FullPath \ [ Workbook ] Worksheet ' ! Cell “
So that is what you could type in any cell to be sure of always getting at the named range, “MyNamedrange”
I believe that to be the full reference required for the Worksheet scoped named range, and you must include the correct worksheet name to which the named range belongs.
I am not quite sure what the exact form is that Excel holds internally for the case of a Workbook scoped named range, but you can give it that reference with any worksheet in the workbook and Excel will use that or possibly use a modified version of that.

So it appears to me that scope in the case of a named range is more to do with both where the range information “belongs”, that is to say where the information to tell excel where to go is, and also what defaults Excel guesses based on how much info you give it. In other words, scope seems to just define what Excel adds on when you only type “MyNamedrange”

Myth: “…scope means what worksheets the name can be accessed from…”
What the last discussions all boils down to for the most common use of typing in a reference in a worksheet in an open workbook is the follows.
For a worksheet scoped named range remember to include the worksheet in the formula, like =”Sheet1!MyNamedRange” . ( If you are in Sheet1, then you can leave that Sheet reference out if you like, as Excel guesses that you want the Sheet in which you are in, Sheet1 in this case.)
For a workbook scoped named range you can include any existing worksheet or leave out completely the worksheet reference and simply do like =“MyNamedRange”
It is this last phenomena which leads to the typical phrase …”.. scope means what worksheets the name CAN BE ACCESSED FROM……”
What scope as it is usually discussed really seems to be about, or rather what the average user as scope experiences, is what default full path reference to a range, that Excel decides to use, if you only give it the string name of the named range
A partial possible explanation for how implicit defaults and why the idea of … the Named range added to the worksheets name collection ( Worksheets scope) can be referenced only from that worksheet … came about could be: If I am “in” the “thing that has” , or I am “where” the Named range Object is, then I may actually reference the Named object with a simple “call” like = “MyNamedRange” rather than be relying on a reference path to “take me there..” . Along a similar train of thought: Applying the .Value property to many objects, as is the case of a named range, returns a string reference. In the case of a named range it appears to be the same as the what is returned by the RefersTo property. Further to that, many objects , such as the named range object, have the .Value property as the default. So using such an object , where a string text might be expected will often not error , but rather use .Value property by default, to return, in this case, the required reference.

_.______________________-
In the next post is some further codes, Workbooks and discussions to help get more familiar with the information explained so far.