Forum Discussion
WHBurling
Dec 07, 2022Copper Contributor
vba - choosing Workbook object vs Range object in accessing named values
I need help in understanding why one might use a workbook object vs a range object in the accessing of a named value in a workbook
An example of the workbook object approach is:
Dim DataPtr as integer
DataPtr = ThisWorkbook.Names("DoorWidth_Pointer").RefersToRange.Value
My understanding of the above line of code is:
ThisWorkbook is the object from which data or a method is to be found.
Names is an object of ThisWorkbook.
Names has a property that holds a list of sets of data. A set of data in that list
consists of names cells to which each name points.
Names has methods to access the above data.
“DoorWidth_Point” is a text string which will be matched by a method in the Names
object
RefersToRange is the phrase that tells the method of the Names object to go to the
address of the cell or cells for which the text string is associated with
value tells the Names method to extract the value(s) of the cells identified above
To me the value of using the workbook object is that it is ultra clear. One reading the code knows exactly which workbook contains the named variable. It knows the name of the named variable.
It states to go to a specific address and retrieve a value.
an example of the range object approach is:
Dim DataPtr as integer
DataPtr = Range("DoorWidth_Pointer").Value
My understanding of the above is:
- The reader of the code is forced to identify the implied active workbook.
- Implied is that the Range object knows about named ranges for the active workbook.
- It appears that given the text string, the “range” object accesses the active workbook’s object Names and hides the method that extracts the cell address and just outputs the value in the cells
To me the value of using the range object is it’s simplicity. I have little experience, perhaps knowing which is the active workbook is not a big deal. I do not know of another reason for using the Range object vs the Workbook object.
On a separate issue
While I can get the workbook approach to work with a named range, I can not get it to work
With a named cell. I have tried the following without success:
Dim DataPtr as integer
DataPtr = ThisWorkbook.Names("DoorWidth_Pointer").RefersTo.Value
I used RefersTo as it was my understanding that RefersToRange is used with ranges and RefersTo is used wWith single cells. Perhaps I have misunderstood RefersTo usage.
RefersTo returns a string that contains the full address of the named range, for example
"=Sheet1!$A$"
Since ReferstTo returns a string, not a range, you cannot use RefersTo.Value.
You can define names at the workbook level but also at the worksheet level. So the Worksheet object also has a Names collection.
And a defined name does not have to refer to a range - it can also refer to a fixed value, for example 37, or to an expression (formula).