Named Ranges and Named Ranges scope. Referencing a named range

[DocAElstein]

Further Practice with using named ranges

.

Codes accompanying the notes in this post can be found here:
https://pastebin.com/sgPyKaRS
http://www.excelfox.com/forum/showth...0811#post10811
http://www.excelfox.com/forum/showth...0812#post10812
http://www.excelfox.com/forum/showth...0813#post10813


There two main routines , and a few Called routines
There are three files.
The main file, “MasturFile.xlsm” , has all the codes in it. You need that file open when running the codes.
The other files, “Data1.xls” and “Data2.xlsx” are intended to be data files which normally would be closed in normal use as a data file, for example when looking for or retrieving data. ( They will be opened temporarily by the main code when needed for named range work associated with the demos )

The data files look like this, a header and some data

“Data1.xls”

Row\Col	A	B	C	D
4
5		Food	Kcal
6		Orange	50
7		Apfel	60
8

Worksheet: Tabelle1

“Data2.xlsx”

Row\Col	A	B	C	D
8
9
10		Suppliment	Kcal
11		BCAA	398
12		EAA	400
13

Worksheet: Tabelle1

The main File looks like this initially:
MasturFile.xlsm

Row\Col	A	B	C	D
3
4		Nutrition	Energy
5
6
7
8
9
10
11
12
13
14

Worksheet: Tabelle1

Here are the files at a file share site:
“MasturFile.xlsm” : https://app.box.com/s/4pjc8rryozeeveieyk0qvbocqil9bk0b
“Data1.xls” : https://app.box.com/s/r1r3js6cmaubzus6kp1ij1mtlbmdr9px
“Data2.xlsx” : https://app.box.com/s/16eh0ba1292k8ki4272hzykf8gpen017
You should download them all into the same folder. To run the demo codes , you only need to open “MasturFile.xlsm” and run the main routines, Sub FoxySingleCellNamedRanges() and Sub FoxyMultiCellNamedRanges()

The demo will involve making some named ranges, and incorporating them into code lines to bring in the data from data files ( with them closed )
( Finally we want to get something like this : http://www.excelfox.com/forum/showth...0815#post10815

The simplified form of what we will be considering is, as example, considering our brief introduction sketches from the first post, we would start with putting some string formula into a cell, which , without named ranges would be like writing in cell B5
“=B2”
Using named ranges this would look something like
“=Name3”

We will do this writing in of the formula in VBA, with a code line like, simplified,
Range(“=C5”) = ”=Name3”

We will extend this VBA approach to investigate using named ranges in the complete coding, like, simplified
Range(“=myNameforB5”).Value = ”=Name3”

Considerations of variations of the right hand side of that formula are similar to those for writing a formula manually in a cell.



General notes to code:
Called routine Sub GeTchaNms(ByVal CodLn As Long, ByVal WnkBuk As Workbook)
This routine is used at various points in the main code to check the current situation regarding named range objects. For convenience it goes through the Workbook named objects collection object for a workbook, as this has “its own” named range objects, that is to say the Workbooks scoped named range objects, and also the named range objects for all the worksheets. So I do not need to go through the named range objects collection object of every worksheet in that workbook separately for every worksheet.
To determine if a name is workbook scoped or worksheet scoped…
We remember that Excel adds a bit onto the name we give to a name Added to a Worksheet’s named objects collection ( Add name object to a Worksheet’s named objects collection = worksheet “scoping” ). That added bit is something like “Sheet1!” . In other words, if you had given Name:=”MyName” in a code line for a worksheets scope Named range object Addition, like, …_
Worksheets("Sheet2").Names.Add Name:="FoodHeader", RefersTo:=____
_.. then after we do that, Excel seems to hold and use a name like “Sheet2!FoodHeader"
So, for example , in the Adding code line above you use , _ Worksheets("Sheet2").Names.Add Name:="FoodHeader" , RefersTo:=___ _ , but we find that if we then use the Name property to return that string name like : …_
= Worksheets(“Sheet2”).Names(“FoodHeader").Name
-.. then we will be returned a string like
“Sheet2!FoodHeader"
The routine uses a check for that “!” in the returned .Name string in order to determine If the name object is worksheet scoped, Else the name object is assumed to be Workbooks scoped named object

The routine then builds up a string with text information about that named range. That string is then given in a message box. ( Additionally, the information is printed to the Immediate window. If you are in the VB Editor and Hit Ctrl + g , then you will see this window. You may be able to drag that window to a convenient place where you can enlarge it. You can then copy all or some of this information. This is useful, for example, to get the correct reference path syntaxes. Note also, you are less limited for space in the immediate window, compared to the message box window )



Using a named range
A major part of my discussion in this Thread has discussed the scoping issue, which in simple terms, we have found determines where you can “get away with” using a simple =MyRangedname or MyRangedName in a spreadsheet cell or in a code part such as Range(“___”) , like Range(“MyRangedName”) or Range(“=MyRangedName”) . By “Get away with” we mean that Excel will guess correct what it adds to make a full path reference string to find the information it needs about that named range, such as where the range is that it Refers To. What clearly Excel seems to do is to go to the Names Object collection where we Added the Name object. This is what the phrase “scoping to” means in the case of named ranges:

Worksheet Scope:
We scoped to the Names object of a particular Worksheet = We Added the named range Name object to the names objects collection object of that particular Worksheet = We scoped that named range to that Worksheet = That named range has Worksheet Scope
So in a practical Example, let me say I want to scope to Sheet1 ( Sheet1 is , say , in a file , “MyWorkbook.xls” )
To Scope MyWshtScp to Sheet1 is done like this in code:
__ Sheet1.Names.Add _ Name:=”MyWshtScp” ,_ Refers To:=some range somewhere
After doing this creating/Adding of the named range, If I then use MyWshtScp anywhere in Excel, then Excel will not use that but will increase the string reference that it uses so as to get to the appropriate worksheet, after which the use of the MyWshtScp will be recognised as a name object “held” there in the Names objects collection object of Sheet1. So Excel will actually use something like this
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]Sheet1'!MyWShtScp"
As that is effectively the so called “Implicit default” , then I am free to use either that or just MyWShtScp interchangeably

Workbook Scope:
We scoped to the Names object of a particular Workbook = We Added the named range Name object to the names objects collection object of that particular Workbook = We scoped that named range to that Workbook = That named range has Workbook Scope
So in a practical Example, let me say I want to workbook scope to a file , “MyWorkbook.xls”
To Scope MyWkBookScp to MyWorkbook.xls is done like this in code:
__ Workbooks(“MyWorkbook.xls”).Names.Add _ Name:=”MyWkBookScp” ,_ Refers To:=some range somewhere
After doing this creating/Adding of the named range, If I then use MyWKBookScp anywhere in Excel, then Excel will not use that but will increase the string reference that it uses so as to get to the appropriate workbook ( MyWorkbook.xls ) , after which the use of the MyWkBookScp will be recognised as a name object “held” there in the Names objects collection object of MyWorkbook.xls. So Excel will actually use something like this
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]'!MyWkBookScp"
As that is effectively the so called “Implicit default” , then I am free to use either that or just MyWShtScp interchangeably
(Note that Excel seems to accept also for a workbook scoped named range an alternative full string reference to any of the worksheets in that workbook, So for example, if I my second worksheet had the name Sheet2 , then this would also be accepted:
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]Sheet2'!MyWkBookScp" )

The reason I have just explained that last bit about the “implied” default full references, is that I personally prefer not to rely on them, and usually use the full references.


The next posts describe the main demo codes in detail.