Support Called routines for Thread:
http://www.excelfox.com/forum/showth...-a-named-range


Code:
Sub FukYaWkBkNames(ByVal WnkBuk As Workbook)
Dim Nme As Name
    For Each Nme In WnkBuk.Names
     Nme.Delete
    Next Nme
End Sub
Sub GeTchaNms(ByVal CodLn As Long, ByVal WnkBuk As Workbook) ' To get info aboout all Name objects in a Workbook,m WnkBuk
Dim Cnt As Long, Nme As Name, strOut As String
' Name objects in Workbook Names Colection object (Workbooks scope and Worksheets scope)
     For Each Nme In WnkBuk.Names '   For convenience it goes through the Workbook named  objects collection object  for a workbook, as this has "its own" named range objects, that is to say the Workbooks scoped named range objects, and also the  named range objects for all the worksheets. So I do not need to go through the named range objects collection object of every worksheet in that workbook separately for every worksheet.
      Let Cnt = Cnt + 1 ' A simple count number of each workbooks collection names objects in order it finds in looping them
      ' We look now for a "!" in the string name, ...  Excel adds a bit onto the name we give to a name Added to a Worksheet’s named objects collection ( Scoped to a Worksheet’s named objects collection = worksheet “scoping”     We scoped to the Names object of a particular Worksheet = We Added the named range Name object to the names objects collection object of that particular Worksheet( and also indirectly  the names objects collection object of the workbook in which that worksheet is) = We scoped that named range to that Workbook = That named range has Workbook Scope  ). That added bit is something like “Sheet1!” . In other words, if you had given Name:=”MyName” in a code line for a worksheets scope Named range object Addition, like, …_   Worksheets("Sheet2").Names.Add Name:="FoodHeader", RefersTo:=____    _.. Then excel seems to hold and use a name like “Sheet2!FoodHeader"
        If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' A name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
         Let strOut = strOut & Cnt & "  Name object Name is  """ & Nme.Name & """" & vbCrLf & "(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 & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """  without any preceding info about" & vbCrLf & "where that named range is," & vbCrLf & "then you must  be in spreadsheet with tab name  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & Nme.Parent.Name & "'" & "!" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """"
            If Nme.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Name Then Let strOut = strOut & vbCrLf & "Note: The refered to range is in worksheet  """ & Application.Range(Nme.RefersTo).Parent.Name & """"
            If Nme.Parent.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Parent.Name Then Let strOut = strOut & vbCrLf & "Note also: The refered to range is in File  """ & Application.Range(Nme.RefersTo).Parent.Parent.Name & """"
        Else ' Assume we have a workbook scoped name... we will see that a name for a workbook scope, remains just as we gave it
         Let strOut = strOut & Cnt & "  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 & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Nme.Name & """" & vbCrLf & "with no preceding info " & vbCrLf & "about where that named range is," & vbCrLf & "then you must be in any spreadsheet in workbook  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & WnkBuk.Name & "'" & "!" & Nme.Name & """" & vbCrLf & "or alternatively use a similar string like this with any of the worksheets in it:" & vbCrLf & """" & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & WnkBuk.Worksheets.Item(1).Name & "'" & "!" & Nme.Name & """"
            If WnkBuk.Name <> Nme.Parent.Name Then Let strOut = strOut & vbCrLf & "Note the refered to range is in" & vbCrLf & """" & Application.Range(Nme.RefersTo).Parent.Parent.Name & """  worksheets  """ & Application.Range(Nme.RefersTo).Parent.Name & """  !!"
        End If
     Let strOut = strOut & vbCrLf & vbCrLf & vbCrLf ' To clearly seperate each name object
    Next Nme
    If strOut = "" Then
     MsgBox prompt:="The workbooks names object collection object is empty," & vbCrLf & "and so there are no named range objects in" & vbCrLf & "workbook   """ & WnkBuk.Name & """", Title:="At " & CodLn & " , for File  """ & WnkBuk.Name & """": Debug.Print "'_= ========" & vbCrLf & "You have no named range Name objects in workbook " & WnkBuk.Name & vbCrLf & vbCrLf
    Else
     MsgBox prompt:=strOut, Title:="At " & CodLn & " , """ & WnkBuk.Name & """ Names Collection has:-": Debug.Print "'_= ========" & vbCrLf & "You have " & Cnt & " named range Name objects in workbook " & WnkBuk.Name & vbCrLf & strOut
    End If
End Sub