HTML Code Test --post8798

[DocAElstein]

Further Practice with using named ranges

.

Codes accompanying the notes in this post can be found here:


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” :
“Data1.xls” :
“Data2.xlsx” :
You should download them all into the same folder. To run the demo code , you only need to open “MasturFile.xlsm” and run the main routine, Sub FoxyNamedRanges()

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 )

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 to use them, and so have done so throughout the coding


The next post describes the main demo codes in detail.

[DocAElstein]

Main codes
- Initially a single cell will be named. 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()
The code deals principally with named ranges referring to single cells.

'0b) 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 '-1b) 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 form we hold 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 would be like
Range(“B5”).Value = “ = [data1.xls]Tabelle1!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]Tabelle1!Dta1Foodheader “
This “takes us” to worksheet “Tabelle1” 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 “Tabelle1” 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 “Tabelle1!” 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]Tabelle1!Dta1Foodheader “
“ = [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)
Code lines - scope to the workbook names object and then code lines - 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 or . 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.


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
4		Nutrition	Energy
5		Food
6
7
8
9
10		Suppliment
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 _ - _
B10 _ - _
MainFoodHeader.JPG : https://imgur.com/uJCkJwb



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

B10 _ - _ ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
MainSupplimentHeader.JPG : https://imgur.com/wQD5FPB