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/b...o-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 B2A simple VBA code to get the above cell background colors would look something like:
Row\Col
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
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:
Row\Col
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/b...ent-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 )
WorkbookNamesObjectHierachy.JPG
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……_
Reply With Quote
Bookmarks