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
_._________