Named Ranges and Named Ranges scope. Referencing a named range

[DocAElstein]

Named Ranges and Named Ranges scope. Referencing a named range

The reason for this Thread is to share my own notes which I made as I recently revised the subject of named ranges.
I got a bit mixed up with the referenced range for a named range and the named range scope issue, so I am taking a slightly different approach to Named Ranges than many Blogs.
I am only briefly describing what a named range is.
I also start early with discussing VBA considerations related to named ranges, as I find it easier to understand, in particular the “scoping” issue, when looking at named ranges in VBA. A key to getting a good understanding is the following: To make a named range you basically create or Add a Named range object. Both a workbook and a worksheet have a named ranges objects “collection” object. You can Add a new named range Name object to either. The difference in how Excel then, by default, accesses information from a simple use of a named range using its simple string name , like “=MyName” , is what leads to the most confusion when learning about named ranges###

If you are not familiar already with the basics of named ranges and the basics of VBA and the basics of range referencing, then this Thread that I am writing here now may not be so useful for you yet.
( The first part here is a good introductory to named ranges: https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges
Here info on ways to reference cells: https://tinyurl.com/y9pn4nep
http://excelmatters.com/referring-to-ranges-in-vba/ )

Brief Introduction to what a named range is
To a first approximation, the typical column letter and row number coordinates, ( sometimes referred to as an address) could be considered as like a named range. So the first cell in a worksheet could be considered as having the string name of “A1” . Here is a colored sketch representing a worksheet to demo where the worksheet cells are using the cell co ordinates in the classic column letter and row number convention:
_____ A1 B1
_____ A2 B2Row\Col
A
B

1

2
A simple VBA code to get the above cell background colors would look something like:
Range("A1").Interior.Color = 0
Range("B1").Interior.Color = 12835293
Range("A2").Interior.Color = 15849925
Range("B2").Interior.Color = 14474738

Excel allows us to customise this basic idea, so that we could give one or more cells a name of our choice, and that is basically what named ranges are about. Here a similar sketch showing a possible alternative way to indicate the cells after we had created/Added a couple of named ranges
_____Name1
____ A2_ Name3
Row\Col
A
B

1

2
In this last example, after creating/Adding the named range of “Name1”, this could be used in some situations in both Excel and Excel VBA in place of “A1:B1” . Similarly, after creating/Adding the named range of “Name3” we could use either Name3 or B2 in many situations interchangeably. ( Assume we have made no changes relating to the cell “A2” ) So we could now in some situations do code things like this to get the background colors in that last screenshot:
Range("A1:B1").Interior.Color = 0
Range("Name1").Interior.Color = 0
Range("A2").Interior.Color = 15849925
Range("B2").Interior.Color = 14474738
Range("Name3").Interior.Color = 14474738
Similarly, ### in some situations in a spreadsheet, after creating the named range, you can use something like A1:B1 and Name1 interchangeably.
We will investigate the basic idea of a simple formula, as example writing in a cell, say B5, a formula like
“=B2”
In our named range example this would be like
“=Name3”
We will look at doing this with VBA, like, simplified
Range(“=B5”).Value = ”=Name3”
and extend this to full use of named ranges , like
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.
###I say in some situations , because: …..
If you are familiar with slightly more advanced range referencing, then you will know that often the simple address is not enough to make sure you are always in all situations referencing the correct cell. You might need to include extra bits. So you could have something like these examples to makes sure you get at the wanted worksheet range :
= “ MySheet ! A1:B1 ”
= “ [ MyWorkbook ] MySheet ! A1:B1 ”

For a named range the situation is very similar like these:
= “ MySheet ! Name1 ”
= “ [ MyWorkbook ] MySheet ! Name1 ”

In fact, you can even take that one stage further sometimes and get at values in a closed workbook in some situations with a full string reference of like
= “ ' MyWorkbookPath \ [ MyWorkbook ] MySheet ' ! A1 ”

You will probably know that Excel and Excel VBA often guesses and/ or adds defaults when you do not give it all the information that it needs. Range referencing is no exception to this, and in fact, I think any simple address or range name that you give will, in most situation, be “internally” converted to a full reference similar to that last shown before it is used

We will learn in this Thread that the so called “scope” in named ranges has the effect of us being able to define to a certain extent the default that is used when you give a simple range name. This is where my discussion of named ranges differs from most , and further, I suggest that the common definition of scope in named ranges as something like ….“… scope means what worksheets the name CAN BE ACCESSED FROM……”….. is not correct https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges#comment-4189507335
I would say, that Scope needs some understanding about Excel VBA, and that scope means where the information about a named range is kept. That we will see can be in two different places. As a by product, probably by accident, we can determine to a certain extent what the default is that will be added to a given simple string range name.

Excel VBA and named ranges. Name Objects in a Worksheet, Name Objects in a Workbook
For my understanding of named ranges, in particular the scoping issue, I would suggest that scoping should be considered to mean “..to What something BELONGS… “ .. This belongs concept is only really understandable if you have some basic VBA knowledge. It is more a case of who/what “owns” it, or rather where Excel actually goes internally to find out information about the named range. If you like, “who/what it belongs to” is “who/what owns the rights to use it” and “who/what holds all the information about it”
VBA
VBA talks all about Objects. These Objects have things in them. Often they are organised in a hierarchical structure. For example, A Workbook Object has a few Worksheet Objects “in it”. ( The whole concept is a bit abstract, and the things “in it” are often, in the real inner workings of a computer ,just information, typically references , about where and what the things are or what they should do). Often as you “go down” the hierarchical structure you eventually end up with smaller things. So towards the bottom of the hierarchical structure the things are “smaller” and are often referred to as properties or methods.
Sometimes we talk about “applying the properties to return something”, like applying the .Value property to a cell range object, say “A1” : If the cell “A1” has “Hello” in it, then .Value applied to the range object for “A1” ( in code the range object is given by Range(“A1”) ) will return the string value “Hello”. We do something like this:
Dim Var As String ' declare the variable , var, as something to hold a text string
_ Let Var = Range(“A1”).Value ' “Return” the Value from the range object and “put it in” the variable, var

Here a typical (very**) simplified structure for a Workbook with two worksheets ( **Not all of the very many objects and properties are shown) :
( I suggest you have a wide window or low zoom on your browser to see this: WorkbookNamesObjectHierachy.JPG: https://imgur.com/44omYFi )
2111


WkbookObject
_____________Wksht1Object ________________________ Wksht2Object ________ WkbookNamesObject
Wksht1RangeObjects _ Wksht1NamesObject ___ Wksht2RangeObjects _ Wksht2NamesObject _ WkbkNameObjectsProperties
Wksht1RangeObjsProperties _ Wksht1NameObjsProperties ___ Wksht2RangeObjProperties _ Wksht2NameObjsProperties

Typical Range object properties are its .Address and its .Values ( If you have a named range object for that range object then it would also have a .Name property to “return” that Name object , even though that Name object may “belong” in either the Workbook Name object or one of the Worksheets Name objects. Note a quirk here: The name object that it “belongs” to, if it is a worksheets Name object, does not have to be the worksheet that the range object is in!!! )

Typical Name object properties are its (string) .Name and .RefersedTo range .

For a range having a Name object the .Address and the RefersedTo range are similar looking text strings.

( A typically confusing thing here is that you can have a code line to get at the string Name from a Name object like this:
Range(“ “).Name.Name
That follows the hierarchical model logically, but it is an example of an unfortunate choice of words where Objects and Properties thereof have unfortunately been given the same name )

Important to note from the hierarchy sketch above is that both Worksheet objects can have a names collection object, and also the Workbook object can also have Names collections object. They are very similar objects in terms of what information they have in them, but they are separate objects.
( If you were to “look in” the Name object collection in a Workbook , it will also include the named range Objects in all that workbook’s Worksheets “collections” of named range objects. I suppose this makes some sense as the Worksheet objects are in the Workbook “collection” of it’s Worksheets. In other words, the Workbook collection of named ranges has all named range objects in it: both “its own” and those in all it’s worksheet’s collection of named range objects. A worksheet collection of named range objects just has it’s ”own” named range objects in it. That does make some sense, based on the hierarchical sketch, I suppose…..

_.___

VBA Parent property and named ranges
A useful side note here is to mention the VBA .Parent property.
Applying the Parent property to a Name object something like this .._
Name.Parent
_... is one way to determine whether the named range object belongs to a Workbook’s collection of named range objects object or to a particular worksheet’s collection of named range objects object. By virtue of the Hierarchical structure, that code line will return a Workbook or a Worksheet.

_.____



Initial conclusions to this first post in next post……_

[DocAElstein]

_..... Initial conclusions from first post





Initial Conclusions: What is named ranges and named ranges scope
An important final point here to avoid confusion later: Scope in named ranges is not talking about where the actual range that it Refers To is. The named ranges scope concept, as it is usually explained, seems to come out as a by product of what Excel adds by default
From our VBA considerations, we can see a clear definition of what is known as the two "scopes" when talking about named ranges

Worksheets Scope: The information about the named range is held, ( or probably more correct technically a reference to where to find all that is ) in a particular worksheet's collection object of Name objects. Somehow, probably by virtue of the hierarchical structure, this info is duplicated in the corresponding workbook's collection of named objects object. But it is a good guess that Excel probably "goes" by default to find information it needs "at the worksheet", in situations when you don't specify otherwise.
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 "scoped" worksheet does not have to be that in which the named range is. In fact , it can even be a worksheet in another workbook..

Workbooks Scope The information about the named range is held, ( or probably more correct technically a reference to where to find all that is ) in a particular workbook's collection object of Name objects. It is a good guess that Excel probably "goes" by default to find information it needs "at that workbook", in situations when you don't specify otherwise. ( Possibly, by virtue of the hierarchical structure, if you specify to "look" in a worksheet , ( belonging to that workbook) , by something like arange reference rather than a specific reference to a worksheet's collection of named ranges, then that may get you to the information at the workbook's collection of named objects object)
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

[DocAElstein]

__________________________________________________ ______________________________WkbookObject

___________Wksht1ClassObject ________________________ _____WkshtCVlassObject
______________Wksht1Object ________________________ __________Wksht2Object ___________________________ WkbookNamesObject

______Wksht1RangeObjects _ Wksht1NamesObject _______ ____ Wksht2RangeObjects _ Wksht2NamesObject ________ WkbkNameObjectsProperties___

Wksht1RangeObjsProperties _ Wksht1NameObjsProperties Wksht2RangeObjProperties _ Wksht2NameObjsProperties











Summary and initial conclusions

VBA and named ranges is like…

_____________________________________WorkbookObjec t
__________Wksht1ClassObject ___________________ Wksht2ClassObject
_____________Wksht1Object ________________________ Wksht2Object ________ WkbookNamesObject
Wksht1RangeObjects _ Wksht1NamesObject ___ Wksht2RangeObjects _ Wksht2NamesObject _ WkbkNameObjectsProperties
Wksht1RangeObjsProperties _ Wksht1NameObjsProperties ___ Wksht2RangeObjProperties _ Wksht2NameObjsProperties

Typical Range object properties are its .Address and its .Values ( If you have a named range object for that range object then it would also have a .Name property to “return” that Name object , even though that Name object may “belong” in either the Workbook Name object or one of the Worksheets Name objects. Note a quirk here: The name object that it “belongs” to, if it is a worksheets Name object, does not have to be the worksheet that the range object is in!!! )

Typical Name object properties are its (string) .Name and .RefersedTo range .

For a range having a Name object ,
Range(“ “).Address
and
Names(“ “).RefersedTo range
return similar looking text strings.

Sub SimilarProperties()
ActiveWorkbook.Names.Add Name:="MyName", RefersTo:=ActiveWorkbook.ActiveSheet.Range("A1")
MsgBox prompt:=Names("MyName").RefersTo & vbCrLf & Range("A1").Address & vbCrLf & Range("A1").Address(External:=True)
Debug.Print Names("MyName").RefersTo & vbCrLf & Range("A1").Address & vbCrLf & Range("A1").Address(External:=True) ' Hit Ctrl+g from the VB Editor to reveal Immediate Window for printed display, ( Hit Alt+F11 to get VB Editor from Excel Spreadsheet view )
End Sub




'=Sheet1!$A$1 ‘Names("MyName").RefersTo
'$A$1 ‘Range("A1").Address
'[MyWorkbook.xlsm]Sheet1!$A$1 ‘ Range("A1").Address(External:=True)





AddressRefersTo.JPG : https://imgur.com/uIJCtd1
2112






Conclusions: What is named ranges and named ranges scope
An important final point here to avoid confusion later: Scope in named ranges is not talking about where the actual range that it Refers To is. The named ranges scope concept, as it is usually explained, seems to come out as a by product of the next post.
From our VBA considerations, we can see a clear definition of what is known as the two “scopes” when talking about named ranges

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 “scoped” worksheet does not have to be that in which the named range is. In fact , it can even be a worksheet in another workbook..

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/blog/the-vba-guide-to-named-ranges#comment-4189507335



Referencing the range of a named range is similar to referencing any range. The difference lies to some extent in the default full reference that Excel will use to “find” the range if you just give the string name , like “=MyName”

Creating named ranges and referencing and referencing named ranges
So the next post discuses range referencing and referencing named ranges and creating named ranges. Finally we will see how the classic definition of what scope in named ranges is comes about. I would say that it is a false definition.

The key to some understanding is probably that a reasonable assumption would be that a default full path for a workbook scoped name will likely “go” somehow to the workbook name object to get the information it needs. The default full path for a worksheets scoped name will likely “go” somehow to the worksheet name object to which it “belongs”, that is to say the worksheet to which we “scoped it”.

Possibly it follows that the creation of a named range will involve some process which allows determination of this scope. Looking at it from the VBA point of view, a good guess would be that it will involve Adding the named range somehow to a named range object collections object of a workbook or a worksheet.

As I actually know the answer, ( and like we did it in the routine above) I will give the pseudo simplified form :) :-) :-
' Code line to create a Named range is like:
NamesObjectToBe.AddedTo _ stringNameYouWant:=”___” , RangeItRefersTo:= ____


A good Blog which explains the VBA adding of a named range in more detail is this: https://powerspreadsheets.com/vba-create-named-range/








Other Refs used:
http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells
https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges#comment-4189507335
http://excelmatters.com/referring-to-ranges-in-vba/
http://www.excelfox.com/forum/showthread.php/268-Delete-Name-Ranges-by-Scope-VBA

[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/blog/the-vba-guide-to-named-ranges#comment-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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10802#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.

[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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10811#post10811
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10812#post10812
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10813#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


5FoodKcal


6Orange
50


7Apfel
60


8
Worksheet: Tabelle1

“Data2.xlsx”
Row\Col
A
B
C
D

8


9


10SupplimentKcal


11BCAA
398


12EAA
400


13
Worksheet: Tabelle1

The main File looks like this initially:
MasturFile.xlsm
Row\Col
A
B
C
D

3


4NutritionEnergy


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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10815#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.

[DocAElstein]

Main codes
- Initially, in the first demo code, single cells will be named and the named ranges then used. The use of the named range is fairly straight forward for this. When using a named range for a multiple call range, there can be some difficulties due to some restrictions caused by being forced into referencing the entire range. The use of Array formula entry , ( the , ( "the CSE stuff" ) will sometimes need to be used, so as an aside this will be revised before going on to multi cell named ranges in the second main code




Main Code, Sub FoxySingleCellNamedRanges()
Part 1 (Rem 1)
The code deals principally with named ranges referring to single cells.

100 _ '-2b) This section is not specifically to do with named ranges, but concerns my personal preference to use a full reference. As mentioned in the introduction first post, this is generally a good practice to make sure the correct cell is referenced at any time or code part. In addition, as we will also be considering named ranges in closed workbooks, it is useful to have the full reference stored in a string variable.
We find that VBA generally will accept that variable containing the complete reference in most situations where it only needs part of it, so it rarely does any harm to "give too much" in any cell reference.
It is my belief that VBA itself converts all cell references internally to the full form before it uses them. So if we always give the full reference we want, then that avoids annoying problems that often catch you out unawares, for example, when Excel guesses wrong the full path that it then uses: If you give the full path, then Excel takes that , and makes no attempt to replace any parts: it makes no attempt to guess anything if you give the full path.
So code section '-2b) just gives us some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference, say B5:
B5
Sheet7!B5
[myWorkbook.xlsm]Sheet4!B5
'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5
The last one is the full form I prefer, and we hold that form in the variables. As noted, Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand, you may get unexpected problems if you used a shorter version , and Excel then guesses wrongly for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.


Rem _1 Data1 Food header, ( value "Food" from first data file screenshot ) , as a named range
So here we looked at the right hand side of the basic code line of, simplified,
Range("B5").Value = " = myNamedRange "
This is very similar to investigating manual uses of named ranges, that it to say, typing things like …_
__ " = myNamedRange "
____ _... in a spreadsheet cell: That simple code line basically writes the string in the cell , as a person would do manually.

The practical example considered here is to write a simple formula in the main workbook, that will bring in the "Food" heading name cell, B5, from the data file, "data1.xls" into B5 in the main workbook.

The simplified form of this would be , without named ranges
Range("B5").Value = " = [data1.xls]Tabelle1!B5 "
Using a named range for the range in the data1 workbook, say "Dta1Foodheader1" , then this could be like
Range("B5").Value = " = [data1.xls]Tabelle4!Dta1Foodheader "
I say like because of two things
_ With any range referencing we need to be careful that the actual range Excel "goes" to is where we want. That goes for the range referred to in both side of the equation
_ and then we have the scope issue for a named range … The "scope" issue confuses very easily: The right hand side of the last formula can actually be written differently when using named ranges depending on where the name "Dta1Foodheader" is "scoped".
I continually attempt to explain this all clearly, but you must bear in mind that it takes some very careful thought in order not to get confused. I have seen many experienced professional totally mixed up with scoping issues, and this may be part of the reason for the, incorrect in my opinion, statement that one often hears like "….…scope means what worksheets the name can be accessed from… "
A near statement that is true would be .…scope means what worksheets the name can be accessed from if you only give the string name and no other information about where to find the name object to which that string name belongs…

Once again to attempt to add clarity…
Consider initially just that right hand side above
" = [data1.xls]Tabelle4!Dta1Foodheader "
This "takes us" to worksheet "Tabelle4" in file "data1.xls"
Once there, the named object with string name "Dta1Foodheader" is attempted to be accessed. It does not follow directly that the range Refered To is the B5 we wanted. That information about what range is Refered To is kept in the name object with the string name "Dta1Foodheader"
Going back to that code line, right hand side…
There are two possibilities based on that code part ( assuming it "works". In other words for that part not to error we must have one of these:
_Possibility 1: At worksheet "Tabelle4" in workbook "data1.xls" we have a worksheets scoped named range object with the string name "Dta1Foodheader1". That named object "belongs" to the named objects collection object of worksheet "Tabelle1", so we must include the part "Tabelle4!" so that we get to that worksheet
_Possibility 2: The workbook "data1.xls" has a workbook scoped named range object with the string name "Dta1Foodheader1". That named object "belongs" to the named objects collection object of workbook "data1.xls" . For this possibility, we only need to use in the spreadsheet , "=Dta1Foodheader" in any worksheet. This means we can "go" to any worksheet, so for example if we have the worksheets "Tabelle2" , "Sheet3" , "MySheet" in the workbook, "data1.xls" , then all these are valid also
" = [data1.xls]Tabelle2!Dta1Foodheader "
" = [data1.xls]Sheet3!Dta1Foodheader "
" = [data1.xls]MySheet!Dta1Foodheader "
They will all give us the same result.
In addition we have an extra valid formula: This arises because we can "go" to the Workbook. The syntax to do this would, we find, is:
" = data1.xls!Dta1Foodheader "
( I have no idea why the syntax is not " = [data1.xls]!Dta1Foodheader ". Probably the Microsoft programmers were equally confused with what named ranges were about )


'1a) - '1b)
'1a) Code lines 330 - 400 scope to the workbook names object
and then
'1b)code lines 410 - 480 scope one of the worksheet's names object of the Data1 file, workbook "data1.xls" . For the scoping the data 1 File had to be open, but it was closed before the named range object was referenced in the lines 390, 400 or 480 . We see that we can reference the named ranges in the closed workbook. Note here , that the Referes To range is in the same workbook as the named ranges. ( A personal preference of mine is , once again, to use a full reference, also in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference )
I finally end up with a string in cell B5 in the main workbook for like
'1a) _ "='C:\Folder\Data1.xls'!Dta1Foodheader "
'1b) _ "='C:\Folder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
Code section '1b)(ii) Is similar to '1b) , except that the data 1 workbook is open and I reference the named object with just the required reference of [Data1.xls]Tabelle2!Ws2Dta1Foodheader
As expected I then end up with in cell B5 in the main workbook
__ =[Data1.xls]Tabelle2!Ws2Dta1Foodheader
But I note, that after the line which closes the data 1 workbook, code line , that formula does not error but changes to the closed workbook reference like we had before, like
__"='C:\Folder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
This behaviour is typically observed when a range is referenced in a workbook which is then closed. It would appear that a reference to named object behaves in a similar way.

In '1c) the scope is to the main workbook. The name object used is therefore in a different workbook to that where the referred to range is, but the reference to this named range works as before. Note the main workbook is open

'1d) This is an attempt to get at the named range object in a roundabout sort of a way. Here the data 1 cell s scoped to the second data file, "Data2.xlsx" ( Workbooks scoped to workbook "Data2.xlsx" )
Both files must be open , for the scoping code line: data 1 file must be open as usual as it is needed in the range assignment argument Refers To:= _ ; The data 2 file must be opened as that has its names object referenced to , ( dataWb2xlsx.Names.Add ____ ) . With both files open we see in the formula bar the expected string reference to the named range:
=Data2.xlsx!Dta2Dta1Foodheader
If we close data 1 file, then that string does not change, and the link still works , ( we have the word "Food" in the cell B5 in the main file.)
If we close the data 2 file also, then initially the string reference in the formula changes to a closed reference like
='C:\Folder\Data2.xlsx'!Dta2Dta1Foodheader
Also initially the value "Food" still appears in the cell B5 in the main file. But if you re enter that formula, then it errors. I am not quite sure why…
If data 2 file is re opened and the formula is re entered, then all is well
If now data 2 file is closed, and data 1 file is opened and the value of "Food" changed, then the value "Food" still remains in cell B5 in the main file.
So I am not quite sure what is going on there…

Maybe I will come back to this post one day and comment further on this.




Further with this first demo code in the next post
_._________

[DocAElstein]

Rem 2 Second part of first main code



Rem 2
So far we have looked at the right hand side of the basic code line of, simplified,
Range("B5").Value = " = myNamedRange "
This is very similar to investigating manual uses of named ranges, that it to say, typing things like …_
__ " = myNamedRange "
____ _... in a spreadsheet cell: That simple code line basically writes the string in the cell , as a person would do manually.

We take the experimenting a little further now , so as to include a named range in the left hand side, like
Range("RangeName").Value = " = myNamedRange "

Range ( " _ " ) What it is
As far as I know, this Range(" ") thing, ( that confusingly pops up all over the place as an object or property, ( an occasionally in some senior professionals opinion as a method) ), is not really supposed to be used with nothing before it, but usually it is, and usually Excel guess correct what to put in front of it, so it usually works as expected.
The Range(" ") thing is usually used in two main situations, in a worksheets range property
or
" Application Range " .
The worksheets range property would have a syntax like, for example, to reference the second cell in Sheet1
Sheet1.Range("B1")
Application Range is something similar, and is what in most situations is the default that Excel uses when you just write like:…_
Range(" ")
_.... _ In many situations, Excel will take that as:
Application.Range(" ")

There is a bit more to it than all that, and that can easier be explained by looking at what that Range(" ") thing seems to do..

( One thing to note here is that the official documentation is often wrong or at least questionable. It seems that nobody really understands what goes on in the internal "wiring" anymore. Often what it seems to have been done is to interpret what happens, and then Methods, Properties are given to explain what seems to happen. I have often had heated discussions with professionals that disagree with my interpretations. But sometimes literature from Microsoft has been changed to reflect my interpretations, whilst the same professionally , sometimes Microsoft MVP's (Most Valuable Professional's) at the time, haven't been able to get any response from Microsoft about anything… )

Range ( " _ " ) What it does
This usually returns a range object. It takes in the (" ") a string reference to that wanted range. That reference is similar to those discussed already. ( Once again, I believe that Excel will add to what you give, so as to give a full reference , should you only give part of it).
If you give a full reference to say, a range in Sheet2 using Sheet1 range property, like …_
Sheet1.Range("='C:\MyFolder\[MyFile.xls]Sheet2'!G5")
_... then that will error as it will not find G5 from Sheet2 in Sheet1
As far as I know, Application.Range(" ") will take any valid range reference and return the range object of that range.

Range(" ") will accept a full range reference ( which is a reference to a closed workbook ) , and it will return the range object wanted, but only if that workbook is open. I expect it is designed that way as Excel will not let you make a range object of a range in a closed workbook.
_.___________


On now with the code part Rem 2 description.
Initially I will make a named range for the range B5 which we were referencing so far like:
__ Application.Range("='C:\Folder\[MasturFile.xlsm]Tabelle1'!B5").Value = ____
'2a) I scope to one of the data files, data 2 file, "Data2.xlsx". Then the code line4 above is used inn this form.
(For the right hand side of the equation( which is required to get the value from data 1 file, B5 , we use a reference containing one of the existing named ranges Added/created in Rem 1)
Application.Range("='C:\Folder[Data2.xlsx]Tabelle1'!MainFoodheader").Value = ____
Just to refresh our memories of what we are doing with that last line in the left hand side: We have in Range(" ") a reference to a named range object in data 2 file. That in turn has the info we want of the range Referred To by that name which is B5 in the main file. This will result in range(" ") returning us the range object of that cell. Then assigning a .Value to that range object will result in that .Value appearing in the cell in the spreadsheet. That .Value is a full reference to the ( closed) data file, ( ='C:\Folder\Data1.xls'!Dta1Foodheader ) , which brings the text "Food" into the cell B5 in the main workbook.
With the data 2 file open, the code line ( ) works . What is perhaps slightly surprising is that with the data 2 file closed, the code line ( ) errors as it can't define the range. ( 1004 The Range method for the _Application object failed ) . Possibly the "wiring" of Range(" ") is set to error if any workbook referenced is closed. That is required for the more usual range reference in the (" "), and possibly such a usage as I am doing here was simply not envisaged at the time….

Rem 3
As a quick reminder to simple referencing of ranges , this simply brings in the Header "Suppliment" from data 2 workbook directly without named ranges. The code line shows similar strings on both sides

The code line is this sort of form:
Range("=" & "'" & WbMain.Path & "" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
The actual string references are like:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B10 ").Value = " ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10"

_.___

For comparison, some corresponding code lines for bringing in the Header "Food" from data 1 workbook , using some of our created named ranges are:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\Data1.xls'!Dta1Foodheader"
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\MasturFile.xlsm'!MainDta1Foodheader"
Range("='C:\MyFolder\MasturFile.xlsm'!MainFoodheader ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
The same basic code line without using named ranges would be
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle1'!B5""
Remember the difference in what "goes on" with and without the named ranges is: Without the named ranges we are referencing the referred to range directly. With the named ranges, we reference somehow to the relevant named range Name object ( via its string Name) . That Name object contains, and somehow "gives out" to Excel, the referred to range: We give that Refered To range, along with the string Name when we create/Add that named range Name object to either a workbook's named objects collection or a worksheets named objects collection.
_._____________________________




In the next post we consider how to bring in the data to the master workbook from the two data workbooks.

[DocAElstein]

So far we have got this far, that is to say we all the headers, that originally in the main book,
Nutrition _ | _ Energy
along with now the sub headers also
Food


Suppliment



Using Excel 2007 32 bit
Row\Col
A
B
C
D

1


2


3


4NutritionEnergy


5Food


6


7


8


9


10Suppliment


11


12


13


14


15


16


17


18


19


20


21


22
Worksheet: Tabelle1


The words “Food” and “Suppliment” are seen in the spreadsheet cells, but in the formula bar we see for
B5 _ - _ ='C:\MyFolder\Data1.xls'!Dta1Foodheader
MainFoodHeader.JPG : https://imgur.com/uJCkJwb
and for
B10 _ - _ ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
MainSupplimentHeader.JPG : https://imgur.com/wQD5FPB

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.eileenslounge.com/viewtopic.php?p=311844#p311844 (https://www.eileenslounge.com/viewtopic.php?p=311844#p311844)
https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe (https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe)
https://www.eileenslounge.com/viewtopic.php?p=311826#p311826 (https://www.eileenslounge.com/viewtopic.php?p=311826#p311826)
https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783 (https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783)
https://www.eileenslounge.com/viewtopic.php?p=310916#p310916 (https://www.eileenslounge.com/viewtopic.php?p=310916#p310916)
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720 (https://www.eileenslounge.com/viewtopic.php?p=310720#p310720)
https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171 (https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171)
https://www.eileenslounge.com/viewtopic.php?p=310110#p310110 (https://www.eileenslounge.com/viewtopic.php?p=310110#p310110)
https://www.eileenslounge.com/viewtopic.php?p=310024#p310024 (https://www.eileenslounge.com/viewtopic.php?p=310024#p310024)
https://www.eileenslounge.com/viewtopic.php?p=309121#p309121 (https://www.eileenslounge.com/viewtopic.php?p=309121#p309121)
https://www.eileenslounge.com/viewtopic.php?p=309101#p309101 (https://www.eileenslounge.com/viewtopic.php?p=309101#p309101)
https://www.eileenslounge.com/viewtopic.php?p=308945#p308945 (https://www.eileenslounge.com/viewtopic.php?p=308945#p308945)
https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880 (https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Second Main Code. Sub FoxyMultiCellNamedRanges()
Range referencing In Excel and VBA

Code section up to and including Rem 3 take us as far as the last code

Before going onto the rest of the code, as an aside, a review of some basic techniques for bringing a range of data values into a main workbook, such as our "MasturFile.xlsm" , from a closed data workbook, " Data1.xls"
This post is not really to do with named ranges, but we must understand some advanced techniques relating to applying the ideas discussed so far to the multicell ranges, before we can do similar data retrieval from multicell data ranges.

"Conventional formulas and range references"
Excel has two basic ways to Hold a single cell reference. Either
it holds it as a fixed co ordinate,
or
it holds it as a fixed vector. The vector corresponds, ( by default settings, if you don't specify otherwise ), as a fixed angle and direction from the cell to the worksheet origin.
One way in which Excel can be told which system to use by including a $ sign if we want to use the co ordinate system. If no $ is included then Excel holds the fixed vector.
The fixed vector will mean that if a cell reference in a particular cell , such as =B6 , is copied to the next cell to the right, the fixed vector is copied and shifted one place to the right. The fixed vector is responsible for bringing the value from B6 into the original cell. That same vector if placed in the next cell to the right will bring in the value of B7. Excel will then display correspondingly the appropriate reference. This would be either =B7 or $B$7 , but Excel conventionally keeps the convention given to it, so it will display =B7
In VBA things work similarly. If I fill in a single cell with the reference =B6 using the , ( simplified) code line of .._
__ Range("B2").Value = "=B6"
____ _.. then the vector is placed in cell B2, so I get

C6.jpg : https://imgur.com/RrR2zrA
Row\Col
A
B
C
D

1


2
=C6


3


4


Paste a Single cell reference( in fixed vector format) across a multicell range
If I put the same reference, =C6 , across a range, whether manually by
copy / paste, or draging
or
by VBA thus: .. _
__ __ Range("B2:C3").Value = "=B6"
____ _.. then the same fixed vector is copied thus:
C6inB2toC3.JPG : https://imgur.com/BrMGrqn

The convention remains to stay in the fixed vector notation, so the reference put in the cells is: Row\Col
A
B
C
D
E

1


2=C6=D6


3=C7=D7


4


5


6ab


7cd


8

In the spreadsheet we will see:
Row\Col
A
B
C
D
E

1


2ab


3cd


4


5


6ab


7cd


8


Coming back to our last formula in the last post, we had this:
='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
That brought in our "Suppliment" heading from here …_
Row\Col
A
B
C
D

9


10SupplimentKcal


11BCAA
398


12EAA
400


13
Worksheet: Tabelle1
_..... into out main file , using a code line of this form, (simplified)

See that full code line in the last code, Sub FoxySingleCellNamedRanges() , code line 880 , was of this form, simplified, .._
Range("B10").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10"
____ _..which gave the formula of this form in B10 of the main File: ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10


In the second code , Sub FoxySingleCellNamedRanges() we use the similar code line ( at '3b) "Fixed vector" B11 into main workbook at B11 ) like
Range("B11").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11"
____ _..which gives the formula of this form in B11 of the main File: ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11
This results in this in the main file, that is to say, we bring in one of the data values, "BCAA", from data file "Data2.xlsx"
MasturFile.xlsm
Row\Col
A
B
C
D

1


2


3


4NutritionEnergy


5Food


6


7


8


9


10Suppliment


11BCAA


12


13


14


Following the arguments of this post so far, we see that we must only change the left hand side of that formula, as in code line '3c) to this form : .._
Range("B11[size=3]:C12").Value = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11"
____ _.. and then we will have formulas of this form in the master file,
Row\Col
A
B
C
D

11='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11
='C:\MyFolder \[Data2.xlsx]Tabelle1'!C11


12='C:\MyFolder \[Data2.xlsx]Tabelle1'!B12
='C:\MyFolder \[Data2.xlsx]Tabelle1'!C12


13


Correspondingly, we see in the master file all the data from Data 2 file:
Row\Col
A
B
C
D

11BCAA
398


12EAA
400


13


_._______________

Paste a Mullticell reference
The basic idea discussed so far is OK, but there is a subtle problem for a multicell range paste.
This would be the case, if for example our named range was referring to the data 2 range of B11:C12. If we wish to use a named range reference , we cannot use the single fixed vector reference. We are dealing with multicells.
We need to consider some advanced theory or how Excel actually works, the end result of which is an understanding of the so called CSE stuff..
We will consider this in the next post.

_.____




Refs for this post
http://www.eileenslounge.com/viewtopic.php?f=30&t=31150#p241197
https://teylyn.com/2017/03/21/dollarsigns/#comment-191

[DocAElstein]

Controlling Shifted references before Entering.

Briefly, how Excel works:
Excel updates a worksheet cell for cell , and Interception theory ( http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp ) explains how a controlled synchronisation allows any single reference to intercept with the help matrix of effectively a full spreadsheet full with the value at the referenced cell. In the first instance this prevents the pasting of a multi cell range into a worksheet. The technique discussed in the last post overcomes the problem in that the pasting of a fixed vector effectively keeps the synch , or effectively shifts the reference exactly in step with the cell by cell up date. Similarly, a manual or code copy and paste of a range will work as the copy will be in synch with the paste. The problem comes if we try to directly write in a multicell reference, such as = B11:C12, either manually or by a code such as
Range("A1:B2").Value = "=B11:C12"
If we are forced into this simultaneous pasting of a range we can use a technique whereby, before the next update each reference in the multicell range to be pasted is effectively shifted by its offset / vector. This is a sort of Controlled Shifting before the Enter. Excel allows us to call in this process if we use the keys Ctrl+Shift+Enter in place of the Enter. The effect of this process is to have an array of values to update in the same time, or rather in the same synch period ( delayed / increased in time by N times, where N is the number of elements in the range ) due to the shifting , that a single cell would have been updated. We may define an area of the worksheet to accommodate this returned array of values.

Manually the complete process involves the following
_ The area for the returned array will be taken as the current selection, so your wanted area for display should be selected.
_ Hit F2 or click in the Formula Bar
_ Type in the formula for the range reference of the range you want to use ( like =B11:C12 )
_ Hit Ctrl+Shift+Enter
The above is sometimes referred to as Type 2 CSE.

In Excel, the indication that a spreadsheet area is being used in this way can be seen by the presence of curly brackets: In the formula bar, one can see if a range has a formula entered by the CSE method as it has curly brackets on it : _ {=B11:C12}
http://www.excelfox.com/forum/showthread.php/2141-How-to-populate-all-the-rows-in-one-column-based-on-one-cell-value#post10038


In Excel VBA, A range object that is used to hold an array will have the indication of that by virtue of it having a .FormulaArray Property.
As we saw in previous posts, we can make reference via a range object to its .Value property and therefore assign it, as we did thus:
Range(" ").Value = "=B6"
That is equivalent to writing =B6 in a cell.

By doing something similar with the .FormulaArray property we effectively do the same as in the complete manual process for CSE as described above,
A code line such as .._
__ Range("myRange").FormulaArray = "=B11:C12"
____ _.. will effectively define the myRange area in the spreadsheet as that area for the returned array of values from the range B11:C12
In other words, if, as example, I want the 4 values of the range B11:C12 at the top left of a worksheet, then this code line will do that for me:
Range("A1:B2").FormulaArray = "=B11:C12"
( The manual equivalent of doing that is
_ Select the range to be used for the retuned array of values ( A1:B2 )
_ Hit F2 or click in the Formula Bar
_ Type in _ =B11:C12
_ Hit Ctrl+Shift+Enter )

We can now see how to bring an array of data values from our data worksheets into the master file with code lines of this form
Range("B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"
Application.Range("B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"
Application.Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"

In those code lines we can replace the range references in those formulas, with named ranges in exactly the same way that we have done previously once we have created the named ranges to refer to those two ranges
The final code lines will be very similar to the previous, differing mainly in the use of .FormulaArray instead of .Value
( Note: In the code lines previously for single cell ranges, the .Value could also have been replace with .FormulaArray : We would effectively have been pasting in an array of one element, which is perfectly valid. )

_.____

From Rem 4 in the second demo code, ( Sub FoxyMultiCellNamedRanges() ) we begin to incorporate named ranges into code lined to bring in the data from the two data files into the main file……

_....................


Ref CSE
https://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/comment-page-1/#comment-737
http://www.excelfox.com/forum/showthread.php/2141-How-to-populate-all-the-rows-in-one-column-based-on-one-cell-value#post10038

[DocAElstein]

_.........................

From Rem 4 in the second demo code, ( Sub FoxyMultiCellNamedRanges() ) we begin to incorporate named ranges into the to bring in the data from the two data files into the main file……


Rem 4 using named ranges for data in data ranges from data workbooks

We will create/Add 4 named ranges in total, one for each data range in the two data workbooks, and a name referring to the ranges in the main file where we want to bring in those values

To help emphasise that named ranges are actually objects which can be “in” any workbook or workbook worksheet, and which can then be referenced from other workbooks in order to get information about to which range they refer, we will introduce another workbook, “StoredNamedRangeNameObjects.xls”. This file will only contain Name objects. We will use this to store the 4 required Named range Name objects. This is quite a crazy idea.
( I can’t imagine why anyone would want to do this. I just thought, well, someone ought to do it , :-) …..Or maybe a security possibility?? – Without the file and the named range object’s information, it could be difficult to use other files requiring this information …..Or if you use worksheets scope, then in the worked example you could devote a worksheet for different data ranges – This would have the named range object or objects , and possibly other information about that data in the spreadsheet. The only thing missing would be the data itself, which might want to be kept elsewhere for, for example, security reasons.. )
Here is the File, “StoredNamedRangeNameObjects.xls” https://app.box.com/s/0dhyk41yumx857539wu0hji5fsekeg2w

Code sections '4b) and '4c) use the worksheet with Name “DataFileNameObjects” for the name objects referring to the two data workbooks ( "Data1.xls" and "Data2.xlsx" ) data ranges.
Code Section '4d) uses worksheet “MainFileNameObjects” for the name objects referring to the two Main workbook ( "MasterFile.xlsm" ) data ranges.

Rem 5 Using the Added data named ranges to bring in data from the data files into the main workbook.
The basic code line for the Import, as described in the last post are adapted to use the Added data named ranges in this sort of way:

Range(“=’C:\MyFolder\[MasturFile.xlsm]Tabelle1'B6:C7”).FormulaArray = “='C:\MyFolder\[Data2.xlsx]Tabelle1'!B6:C7”
becomes:
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import”).FormulaArr ay = “='C:\MyFolder\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data”

Range(“=’C:\MyFolder\[MasturFile.xlsm]Tabelle1'NmsObjDta2Import”).FormulaArray = “='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12”
becomes
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import”).FormulaArr ay = “='C:\MyFolder\[ StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data”

As we are using a different workbook, ( “StoredNamedRangeNameObjects.xls “ ) for the names , both sides of the code lines must have the complete path reference.
( As the workbook “StoredNamedRangeNameObjects.xls” is open at the time of using those formulas, we could use
Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArray = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
and
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import”).FormulaArr ay = “='C:\MyFolder\[ StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data” )

_._____

At the end of the second code we have the final range info in the main workbook:

_____ Workbook: MasturFile.xlsm ( Using Excel 2007 32 bit )
Row\Col
B
C

4NutritionEnergy


5Food


6Orange
50


7Apfel
60


8


9


10Suppliment


11BCAA
398


12EAA
400
Worksheet: Tabelle1

The current information in the immediate Window ( that produced by code line 1080 ) will be something like this:



'_= ========
You have 1 named range Name objects in workbook MasturFile.xlsm
1 Name object Name is "MainFoodheader" (the same as you gave)
It has workbook scope and
it refers to range "=Tabelle1!$B$5"
and if in a spreadsheet formula you only want to use
"MainFoodheader"
with no preceding info
about where that named range is,
then you must be in any spreadsheet in workbook "MasturFile.xlsm"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\MasturFile .xlsm'!MainFoodheader"
or alternatively use a similar string like this with any of the worksheets in it:
"='G:\Excel0202015Jan2016\ExcelFox\Blogs\[MasturFile.xlsm]Tabelle1'!MainFoodheader"



'_= ========
You have 1 named range Name objects in workbook Data1.xls
1 Name object Name is "Dta1Foodheader" (the same as you gave)
It has workbook scope and
it refers to range "=Tabelle1!$B$5"
and if in a spreadsheet formula you only want to use
"Dta1Foodheader"
with no preceding info
about where that named range is,
then you must be in any spreadsheet in workbook "Data1.xls"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\Data1.xls' !Dta1Foodheader"
or alternatively use a similar string like this with any of the worksheets in it:
"='G:\Excel0202015Jan2016\ExcelFox\Blogs\[Data1.xls]Tabelle1'!Dta1Foodheader"



'_= ========
You have no named range Name objects in workbook Data2.xlsx


'_= ========
You have 4 named range Name objects in workbook StoredNamedRangeNameObjects.xls
1 Name object Name is "DataFileNameObjects!NmsObjDta1Data"
(you gave "NmsObjDta1Data")
It has worksheet scope and
it refers to range "=[Data1.xls]Tabelle1!$B$6:$C$7"
and if in a spreadsheet formula you only want to use
"NmsObjDta1Data" without any preceding info about
where that named range is,
then you must be in spreadsheet with tab name "DataFileNameObjects"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
Note: The refered to range is in worksheet "Tabelle1"
Note also: The refered to range is in File "Data1.xls"


2 Name object Name is "MainFileNameObjects!NmsObjDta1Import"
(you gave "NmsObjDta1Import")
It has worksheet scope and
it refers to range "=[MasturFile.xlsm]Tabelle1!$B$6:$C$7"
and if in a spreadsheet formula you only want to use
"NmsObjDta1Import" without any preceding info about
where that named range is,
then you must be in spreadsheet with tab name "MainFileNameObjects"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import"
Note: The refered to range is in worksheet "Tabelle1"
Note also: The refered to range is in File "MasturFile.xlsm"


3 Name object Name is "DataFileNameObjects!NmsObjDta2Data"
(you gave "NmsObjDta2Data")
It has worksheet scope and
it refers to range "=[Data2.xlsx]Tabelle1!$B$11:$C$12"
and if in a spreadsheet formula you only want to use
"NmsObjDta2Data" without any preceding info about
where that named range is,
then you must be in spreadsheet with tab name "DataFileNameObjects"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data"
Note: The refered to range is in worksheet "Tabelle1"
Note also: The refered to range is in File "Data2.xlsx"


4 Name object Name is "MainFileNameObjects!NmsObjDta2Import"
(you gave "NmsObjDta2Import")
It has worksheet scope and
it refers to range "=[MasturFile.xlsm]Tabelle1!$B$11:$C$12"
and if in a spreadsheet formula you only want to use
"NmsObjDta2Import" without any preceding info about
where that named range is,
then you must be in spreadsheet with tab name "MainFileNameObjects"
If you want to be sure to access this named range from anywhere,
you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import"
Note: The refered to range is in worksheet "Tabelle1"
Note also: The refered to range is in File "MasturFile.xlsm"