Named Ranges and Named Ranges scope. Referencing a named range

**DocAElstein** · 11-18-2018, 04:52 PM

Code:

________________________________________________________________________________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…

_____________________________________WorkbookObject
__________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.

Code:

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
AddressRefersTo.jpg

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/b...ent-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