ब्लॉग कोशिश कर रहा है بلاگز کی ک*Trying Blogs

[DocAElstein]

Copy of Reply to this:
https://www.thespreadsheetguru.com/b...ent-4082118270

... to scope a workbook in a named range , best syntax is: ThisWorkbook.Names("YourName").RefersToRange.

Using ThisWorkbook.Range("YourName") will not work!

@ PG CodeRider. Hi!
_1) I’m not familiar with a syntax like ThisWorkbook.Names("YourName").RefersToRange.___ So I am not quite completely sure what you are saying. Can you clarify a bit please, thanks.
_2) & _3) :- These couple of points might be relevant somehow to the issue you are talking about

_Point 2).
This sort of thing, ThisWorkbook.Names("YourName").____ , I would expect would “do something sensible” as it will return a Name object. So then, to that Name object you could , for example , apply the Name property, and so , for example, rename the Named Range object. ( see '2(ii) in my demo code below).
In addition, it seems that the named Range object is one of those objects which if you “use” the object in a code part where a string is expected, rather than an object, then , instead of erroring with type mismatch as you might at first expect, a different phenomena occurs: In such a “usage” it actually “gives you” some string reference. In the case of a named Range, what it “gives you” appears to be the string reference of the range to which the Named Range refers, (see '2(iii)a) in my demo code below). You can use this strange phenomena then to actually change the referred to range , (see '2(iii)b) in my demo code below).
(This phenomena might be something to do with that the .Value property is usually the default property for an object)

_Point 3) I would not expect something like ThisWorkbook.Range("YourName") to ever “work” in most circumstances. The reason for this. I think, is simply that there is no Range property of a workbook.
As far as I know the main range “things” are the Worksheet range property and the Application Range “thing”. ( The latter is often called a property, but I sometimes think of it as a Method.. or “thing” )
In Rem _Point 3) of my demo code below I look at these ways of getting at a named range.
So, for example, something of this form ThisWorkbook.Application.Range(YourName").____ will usually “do something sensible”. For example, '3)(ii)b) and '3)(ii)c) in my demo code below is a version of what I call the “short hand way” of making a workbooks scope Named Range.
( I don’t think I can do anything similar to '2(iii)b) via the range things to actually change the referred to range , as I guess that might cause a chaos …. Maybe…. )
_.________________
If you want to try my demo routine below, Sub ObjRefValueWonkEtc() , make sure you also copy the other two routines, so three in total.

Alan

Sub ObjRefValueWonkEtc() ' https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges#comment-4082118270
Rem 0) Clean the sheet of names
 Call FukOffNames
 Call getWbNames
Rem _Point 2) Name Object Names and Name string range references
'2(i) Add a Named range name object with workbook scope
 ThisWorkbook.Names.Add Name:="YourFirstName", RefersTo:=ActiveSheet.Range("B1")
 Call getWbNames
'2(ii) Use .Name property to change the name
 Let ThisWorkbook.Names("YourFirstName").Name = "YourSecondName" ' Change name using name property of Name object
 Call getWbNames
Dim objYourName As Object: Set objYourName = ThisWorkbook.Names("YourSecondName") ' use an object variable for the Name object
 Call getWbNames
 Let objYourName.Name = "YourThirdName" ' Change name using Name property of Name object again, ( using the object variable this time )
 Call getWbNames
' Check info
'2(iii)a)
 MsgBox prompt:="You last name for the cell B1 was  """ & objYourName.Name & """" & vbCrLf & "and if you ""use"" that object where a string is expected like here in this MsgBox, then you get a reference looking like this:  " & """" & objYourName & """"
'2(iii)b) use the string ""Value" property" to change the refered to range reference
 Let objYourName.Value = "=" & ActiveSheet.Name & "!$B$2" ' give the typical full range reference for a cell
 Call getWbNames
Dim strRef As String: Let strRef = objYourName ' this defaults probably to the next line
 Let strRef = objYourName.Value
Rem _Point 3) Use range to access a Named Range
'3)(i) use Worksheets range property
 Let ActiveSheet.Range(strRef).Value = "Allo4"
' Let Worksheets.Item(1).Range(strRef).Value = "Allo5" ' This will only work if the Named Range is in, that is to say, refers to, the first worksheet
' Let Worksheets.Item("NamedRanges").Range(strRef).Value = "Allo6" ' This will only work if the Named Range is in, that is to say, refers to, the worksheet with tab Name "NamedRanges"
'3)(ii)a) Use Application.Range "thing" to get at the named range
 Let ThisWorkbook.Application.Range("YourThirdName").Value = "Allo1"
 Let ThisWorkbook.Application.Range(strRef).Value = "Allo2"
 Let strRef = Replace(strRef, "$B$2", "YourThirdName") ' replace the cell address with the name we last gave that cell
 Let ThisWorkbook.Application.Range(strRef).Value = "Allo3"
 Let Application.Range("=" & "'" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & ActiveSheet.Name & "'" & "!" & "YourThirdName").Value = "Allo7" ' Just for fun to demo that Application.Range(" ") syntax will accept a full reference
'3)(ii)b) Give another names to an existing Named Range using "short hand way"
 Let ThisWorkbook.Application.Range(strRef).Name = "YourforthName" ' Note this adds another name, it does not replace the existing one
 Let ThisWorkbook.Application.Range(ActiveSheet.Name & "!" & "YourThirdName").Name = "YourfifthName"  ' Note this adds another name, it does not replace the existing one
'3)(ii)c) Add a new named range using "short hand way"
 Let ThisWorkbook.Worksheets.Item(2).Range("G4").Name = "WorkbookScopeNamedRangeInCellG4InSecondWorksheetOfThisWorkbook"
 Call getWbNames
Rem 4 Just for passing fun .. strange phenomena  .. the next line won't work initially _....
 Call FukOffNames: Call getWbNames
'4a) .. strange phenomena  .. the next line won't work initially _....
 'Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbook"
 ' _.... But if I do this next line first, then it will work
 Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1"
 Call getWbNames
 Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName2"
 Call getWbNames
'4b) .. what is going on  ... attempt to explain  ,  ThisWorkbook.Worksheets.Item(2).Range("G5").Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1"  somehow made a named range object, and gave it the name  "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1"
Dim objNameG5 As Name: Set objNameG5 = ThisWorkbook.Worksheets.Item(2).Range("G5").Name  ' .. as I have a name object, then I can assign an object variable to it.
 Call getWbNames
 Let objNameG5.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName3" ' this has now given a new name to the name Object,  objNameG5
 Call getWbNames
 Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName4" ' This does the same as objNameG5.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName4"
 Call getWbNames
End Sub





Sub getWbNames()
Dim Nme As Name, Cnt As Long
    For Each Nme In ThisWorkbook.Names
     Let Cnt = Cnt + 1
    Dim strNames As String: Let strNames = strNames & Cnt & "   "
        If TypeOf Nme.Parent Is Worksheet Then ' Answer 2 - https://stackoverflow.com/questions/8656793/progammatically-determine-if-a-named-range-is-scoped-to-a-workbook
         Let strNames = strNames & """" & Nme.Name & """  refers to the range ref  """ & Nme & """  and and can be referenced only from worksheet with tab Name  """ & Nme.Parent.Name & """ ( Worksheet Scope ). ( That worksheet is in the workbook  """ & Nme.Parent.Parent.Name & """  )" & vbCrLf & vbCrLf
        Else
         Let strNames = strNames & """" & Nme.Name & """  refers to the range ref  """ & Nme & """  and can be referenced from any sheet in the Workbook  """ & Nme.Parent.Name & """  ( Workbook Scope )" & vbCrLf & vbCrLf
        End If
    Next Nme
    If strNames = "" Then
     MsgBox prompt:="I don't think you have any Names at the moment luvy"
    Else
     MsgBox prompt:=strNames, Title:="Spreadsheet Named range objects in " & ThisWorkbook.Name & " are:-"
    End If
End Sub

Sub FukOffNames()
Dim Nme As Name
     For Each Nme In ThisWorkbook.Names
      Nme.Delete
     Next Nme
End Sub

Edit: I added Rem 4 just to the post here at excelfox for fun as I have more space, and the code 'comments go to the right , so the code looks less cluttered
_.___________________
Edit 2 ...… a few hours later… after answering a few comments at the blog ( https://www.thespreadsheetguru.com/b...o-named-ranges ) I understand now what you PG CodeRider means by “….to scope a workbook in a named range , best syntax is: ThisWorkbook.Names("YourName").RefersToRange….”
I see now that if I have an existing Named range with the name , "YourName" , then something like this will change the range it refers to
ThisWorkbook.Names("YourName").RefersToRange = "=Sheet1!$A$1"
So that answers my first question, 1)
_.________
Something like this will also work
Range("=Sheet1!$A$1").Name = "YourName"
The first time you use that, a Name object with the string name "YourName" is made if it does not already exist. ( It has workbooks scope and refers to the range $A$1 in Sheet1). Once it exists, then code lines like this will work to change the range it refers to, or its string Name

Application.Range("=Sheet1!$A$1").Name.RefersTo = "=Sheet1!$X$700"
Application.Range("YourName").Name.RefersTo = "=Sheet2!$A$100"
Application.Range("=Sheet2!$A$100").Name.RefersTo = "=Sheet3!$A$100"
Application.Range("YourName").Name.Name = "YourNewName"

I refer to a code line like Range("=Sheet1!$A$1").Name = "YourName" as a shorthand way to make a workbook scoped Named range. It is quite curious how/ why it works. I am not sure that I understand exactly how/ why it works. … Initially this, Range("=Sheet1!$A$1").Name , is returning a name object, not a name string. But a named range seems to be one of those objects which returns a reference string if you use the object variable in a place where a string is given or expected. Also I note that the default property of a lot of objects is .Value. I find that if I apply the .Value property to a Name object, then I get the same as if I apply the . RefersTo property. Somehow the result of all this is that a code line like Range("=Sheet1!$A$1").Name = "YourName" seems to make a named range object and give it the string name of "YourName”