Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

**DocAElstein** · 11-11-2018, 03:47 PM

Codes to support this
https://www.thespreadsheetguru.com/b...ent-4189507335

....

The main demo code is Sub NamedRangeScopes() , but that Calls the others, so copy them all to the same code module , and then run the main demo code, Sub NamedRangeScopes()

Code:

Sub NamedRangeScopes()
10    Call FukOffNames
20    Call getWbNames
30   Rem 1 Add 3 named ranges, 1(i) '_-in the Workbooks name object collection, and 1(ii) in the first worksheet name object collection and 1(iii) '_-in the second worksheet name object collection
40   '1(i) Add a Workbook names object in the Workbook name object collection of this workbook
50    ThisWorkbook.Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1")   '_-in the Workbooks name object collection
60    'The form above is like   ThisWorkbook.Names.Add Name:="Name1", RefersTo:=Worksheets(Sheet1).Range("A1")
70   '1(ii) Add a name object in the first worksheet's name object collection
80    ThisWorkbook.Worksheets.Item(1).Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1")  '_-in the first worksheet name object collection
90    'The form above is like   Worksheets("Sheet1).Names.Add Name:="Name1"  ,    RefersTo:=Sheet1.Range("A1")
100  '1(iii) Add a name object in the second worksheet's name object collection
110   ThisWorkbook.Worksheets.Item(2).Names.Add Name:="Name2", RefersTo:=ThisWorkbook.Worksheets.Item(2).Range("A1")  '_-in the second worksheet name object collection
120   'The form above is like   Worksheets("Sheet2).Names.Add Name:="Name2"  ,    RefersTo:=Sheet2.Range("A1")
130  Rem 2 Change the string name of a named range
140   Call GetChaNameObjects(140) ' Check out Info for all Name objects
150  '2a) Use Workbook names objects to Change the worksheet names object name that has the same name as the workbook names object name, change it twice, first using the workbook names object collection and then the worksheet names object collection
160   Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1").Name = "Name1_1"
170   '       The form above is like             ThisWorkbook.Names("Sheet1!Name").Name = "Name1_1"
180   Call GetChaNameObjects(180)
190   Let ThisWorkbook.Worksheets.Item(1).Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1_1").Name = "Name1_2"
200   Call GetChaNameObjects(200)
210   Let ThisWorkbook.Worksheets.Item(1).Names("Name1_2").Name = "Name1_3"
220   Call GetChaNameObjects(220)
230  '2b) use a Worksheet's (in this example the second worksheet's) name objects to Change the second worksheet's names object, ( we gave it "Name2", but Excel adds a bit so it looks like  Sheet2!Name2" which you can get from a VBA code line like  ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2"   I do this just in case your second worksheet has a tab name other than  Sheet2
240   Let ThisWorkbook.Worksheets.Item(2).Names("Name2").Name = "Name2_2"
250   ' Note: you could have equally done this:     Let ThisWorkbook.Worksheets.Item(2).Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2").Name = "Name2_2"  , which is like   Let ThisWorkbook.Worksheets.Item(2).Names("Sheet2!Name2").Name = "Name2_2"
260   Call GetChaNameObjects(260)
270  Rem 3 Change the string name of a named range, for example the one in the second worksheet names collection whichg we just renamed to "Name2_2" ,(which Excel holds as like  "Sheet2!Name2_2")
280  '3a) Use Workbook names objects
290   Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("Z123")
300   Call GetChaNameObjects(300)
310  '3b) Use the second worksheets's  names objects
320   Let ThisWorkbook.Worksheets.Item(2).Names("Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("X23")
330   Call GetChaNameObjects(330)
End Sub

Code:

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

Code:

Sub GetChaNameObjects(ByVal CodLn As Long)
Dim Nme As Name, strOut As String
' Name objects belonging in Workbook Names Colection (Workbooks scope)
     For Each Nme In ThisWorkbook.Names
        If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' we will see that a name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
         Let strOut = strOut & "Name object Name is  """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheet scope and" & vbCrLf & "it refers to range  """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
        Else ' we will see that a name for a workbook scope, remains just as we gave it
         Let strOut = strOut & "Name object Name is  """ & Nme.Name & """ (the same as you gave)" & vbCrLf & "It has workbook scope and" & vbCrLf & "it refers to range  """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
        End If
    Next Nme
 MsgBox prompt:="Workbook names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-": Debug.Print "Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-" & vbCr & strOut
' Name objects belonging in Workbooks Names Colection (Worksheets scope)
Dim Ws As Worksheet: Let strOut = ""
    For Each Ws In ThisWorkbook.Worksheets
        For Each Nme In Ws.Names
         Let strOut = strOut & "Name object name is  """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheets scope and" & vbCrLf & "it belongs to the Names collection of worksheet """ & Ws.Name & """" & vbCrLf & "and it refers to range  """ & Nme.RefersTo & """" & vbCrLf & vbCrLf
        Next Nme
    Next Ws
 MsgBox prompt:="Worksheets names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in all the worksheets Names Colections are:-": Debug.Print "Name objects in all the worksheets Names Colections are:-" & strOut
End Sub

Code:

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 '   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:-": Debug.Print strNames
    End If
End Sub