How to distinguish between user created Names and built-in/ system created Names

[BigC]

The Names collection in Excel includes all sorts of names (e.g. built-in/system created names for sheets, autofilter databases, structured tables, etc., as well as all Defined Names created by the user that refer to ranges, constants, formulae, etc..)

The workbook I'm currently playing with contains 412 Names, of which 285 are user created, so presumably the remainder (127) are built-in/system created.

I am trying to find VBA code that will identify whether a selected name is user created or built-in/ system created (either could have a RefersTo range so that property doesn't give it to me ).

I'd be pleased if anyone can provide me with the magic code.

Many thanks in advance

 

[xenou]

I don't know of any name attributes that you could use. 127 "built in names" are a lot. What are these names? The only built in names I see normally are a handful of "Print_Area" names. I would guess you would have to filter them out yourself by making a list of the candidates. If you are the one making the names you can use an identifying feature. For instance, I always start all my names with an underscore (I don't know why I got into that habit).

 

[BigC]

Re: How to distinguish between user created Names and built-in/ system created Names - SOLVED

Problem solved - this old dog was barking up the wrong tree!

The "missing" names were not built-in/system created names, but Defined Names with sheet scope. Unfortunately, clicking Use in Formula / Paste Names / Paste List from the Defined Names tab of the ribbon only includes names with workbook scope rather than ALL defined names (i.e. excludes names with sheet scope). [Now there's a mod. that Microsoft could look at for the next version!??]

I've ended up with the following code which adds a new sheet called “Names” to the Active Workbook and then pastes a list of all the names in that workbook, together with the RefersTo reference, Scope (sheet name or 'Workbook') and whether or not the name is visible.

Sub ListAllDefinedNames()
' Author:   Colin Delane, CA, Perth, Western Australia
' Date:     08 Jan 2014
' Purpose:  This macro adds a new sheet called “Names” the active workbook, and then pastes a list of all the names in that workbook,
'           together with the RefersTo reference, Scope (sheet name or 'Workbook')and whether or not the name is visible.
'           The macro then formats the sheet and applies an Autofilter to the list.
' Source:   Adapted from http://2toria.com/2010/10/22/vba-listing-all-names-and-their-ranges-in-excel/
'----------------------------------------------------------------------------------------------------------------------------------

    ' Dimension Variables:
    Dim nmName As Name
    Dim rng As Range
    Dim intRowCount As Integer
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim strWsName As String
    Dim strWbName As String
    Dim strNmName As String
    Dim strRefersTo As String
    Dim strScope As String

    Set wbk = ActiveWorkbook
    Set wks = ActiveSheet
    
    strWbName = wbk.Name

    ' Add sheet
    Application.Worksheets.Add
    ActiveSheet.Name = "Names"
    
    ' Set up headings
    With Range("A1")
        .Value = "Names in this workbook"
        .Font.Bold = True
        .Font.Underline = True
    End With
    
    Range("A3") = "Name"
    Range("B3") = "Reference"
    Range("C3") = "Scope"
    Range("D3") = "Visible?"
    
    Range("A3:D3").Font.Bold = True
    For Each rng In Range("A3:D3")
        With rng.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With rng.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With rng.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    Next rng
    
    intRowCount = 4 'First blank row
    
    ' Loop through names
    For Each nmName In wbk.Names
                
        ' Capture RefersTo
        strRefersTo = "'" & nmName.RefersTo
        
        ' Capture Name & Scope
        
'        ' Block If #1
        If InStr(1, nmName.Name, "!", vbTextCompare) <> 0 Then    'Name includes a sheet name
            strNmName = Right(nmName.Name, Len(nmName.Name) - InStr(1, nmName.Name, "!"))
            strScope = "'" & Mid(nmName.Name, 1, InStr(1, nmName.Name, "!") - 1)
        Else
            strNmName = nmName.Name
            strScope = "Workbook"
        End If  ' Block If #1
        
        'Populate table
        Range("A" & intRowCount).Value = strNmName
        Range("B" & intRowCount).Value = strRefersTo
        Range("C" & intRowCount).Value = strScope
        Range("D" & intRowCount).Value = nmName.Visible
        
        intRowCount = intRowCount + 1
     
     Next nmName
    
    ' Tidy up
    With Range("A3:D3")
    .EntireColumn.AutoFit
    .AutoFilter
    End With

    Range("B3").EntireColumn.ColumnWidth = 130
    Range("B4").Activate
    ActiveWindow.FreezePanes = True
    
    MsgBox "Done!"
    
End Sub