More Named Range Scope Wonks. Problems when Worksheet Scoped Worksheet is different from Worksheet referred to in RefersTo:= Range Object argument

This is a another partial solution to the This Thread
So
I thought I had enough here to mark the Thread as solved, or at least the Scope side of it. But a Pit fall popped up, so I get that out of the way here for future reference.

I noticed that when a Worksheet Scoped Sheet is different from the Worksheet referred to in the
RefersTo:=
Range Object, then there are problems using the given string, hardcoded, to the Evaluating bit. This forms the basis of the way discussed here to the “trick” to allow Dynamic Coding whilst using the [ ] way of evaluate, so it is very relevant to the solution of this Thread....

So The problem arises in Worksheet Scoped Named Ranges when the Scoped Worksheet is different from the Worksheet to which the
RefersTo:= argument
Range applies.

What then seems to happen the required hardcoded string is different from that given as the
Name:= argument.

It seems quite tricky sometimes not to get all the Range referencing, Scope, Range names and Named Ranges in VBA mixed up. So I have tried too write a fairly compact demo code which when steps through explains the thing I am talking about.

The code in the next Post alsoo summarises again the tricky Name Object , Name Property which makes this Scope stuff realy confusing.

Code Summary.
Sub NameNameScope()

Rem 1) Delete all Named Ranges in Workbook to avoid any confusion when adding and trying to "get at" Named Ranges

Rem 2) Two Worksheet Variables are set which are required to be referenced in the Code ( ws1 is "BracketWonk", ws2 is "Stan" )

Rem 3) An Error Handler is “enabled” ( “plugged in” ), which will be activated ( “switched on” ) at an Error occurring. This then replaces the default error as I am expecting errors especially at the next Line so can handle them better... ( Basically this error handler section explains the Error, clears the error exceptional situation and resumes just after the line which errors. )

Rem 4) Just some background to show we need to create are own Range Name Objects, as the existing ones may be behaving differently , and / or are not fully available to us.
http://www.excelforum.com/excel-prog...ml#post4386105

Rem 5) This repeats again to refresh our memory and for a comparison using Workbooks scope to get a Named range. This was up until now what was mostly done in this Thread
First
180 WB.Names.Add Name:="ws1A1WBScope", RefersTo:=ws1.Cells(1, 1) 'A Workbooks Scope Named Range Name Object is made

In particular the following points, which can really be confusing, are brought out:

_ A Named Range is an Object. It can be got at by
= Range(“ “).Name

_ This Named Range Object has amongst other things the Property of the string name we gave it when we created it in Line 180. ( I used “ws1A1WBScope” here as an arbitrary name )

_ We can further get that string back from the object through that Property, which confusingly also is referred to as Name.
In other words, pseudo code
String name we gave at Range Name Object creation is = Range(“ “).Name.Name

_To confuse us more if_.................., rather than assigning the
Range(“ “).Name to an Object Variable, we instead either
_a) assign it to a String Variable
or
_b) just use Range(“ “).Name anywhere,
_........................then a string with a reference type format is returned, in our case looking Like
"=BracketWonk!$A$1"

If you step through section ‘5b in Debug F8 mode and hover over the variables in the code with the mouse cursor, then you will get the point.

Code ‘5c section is a bit new to this Thread.
It shows that in general we can use either the string reference or the string Name Property in
Range(“ “).
This could be interesting how VBA works. It maybe would recognise the difference of a reference or Name Property by the presence or not of the “!” . (.....maybe it always converts to the reference if needed. Generally you hear that a Range Object is “held” as some sort of an Address.....)
When stepping through this code section it is good to look at the firstcell in ws1 and you see that all code lines are “working”
_...........................

400 Rem 6) ' Worksheets Scope ws1 with Worksheets Scope also from ws1
This just repeats Rem 5) for the simple case of an equivalent Worksheets Scope . There is not too much difference as far as we are concerned.....
( _............The basic difference ( and what Scope is basically about ) , is that after these last two section you can type
= ws1A1WBScope
In any Cell in any Worksheet to get to the first cell in ws1

whereas you can only type the following in any Cell in ws1 to do the same
= ws1A1ws1Scope
_...................This is not too important to our use of the Named ranges)

_One small interesting point is that I had to delete to the Workbook Scoped Named range Object ( Line 490 ) or it threw a Spanner in the works for my code. ? I think apparently this is because the Names Object of a Worksheet Range only holds one Name Property Value, and my code was catching my given Workbooks scoped Named Range Object Name Property at one point when I wanted it to catch my given Worksheet Scope Named Range Name Property . I am not too clear what is going on there or if I can handle that better. ?????????

_Another interesting thing , more relevant to the next section, is that the returned Named Range Object Name Property has a bit added on to it . But somehow I can still use my original given name in
Range(“ “)
And it “works” – maybe VBA has that bit an implicit default.

_ The final conclusion here is that if necessary I could still go ahead with my “trick” to allow Dynamic Coding whilst using the [ ] way of evaluate.....
_.............

620 Rem 7) 'Worksheets Scope ws1 with Worksheets Scope ( from ws2 )
Rem 7 ) is now the crux of this Post:
This follows exactly same lines as Rem 6) but the scope is simply set to the other Worksheet, ws2
(_.....This would have the effect in the worksheet of only allowing
= ws1A1ws2Scope
_...............to get to the first cell in ws1 if you typed it in ws2 – again not too important here_..... )

Now the big problem you will see is that I can no longer use my original given name but must use that with the extra bit that VBA adds. So I could no longer use my “trick” to allow Dynamic Coding whilst using the [ ] way of evaluate. ( This is because the “trick” requires me giving that Name in the Form other codes use it. Unless of course I knew the sheet name. – A possible workaround , but a dynamicy is lost. ( I have to write this Name in the hard code form that it would be used. ( For Rem 5) and Rem 6) case it is any arbitrary Name ) )

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

I did not intend using anything other than Workbooks scope but it was worth mentioning it in passing here. Good to know where a pit Hole is before I fall down in it.

Alan

Codesin next Post:
main Code: Sub NameNameScope()
a required called Public Sub: Public Sub DeleteAllWBNamedRanges()