Tracking/Documenting Defined Names [Excel VBA]

If you've ever developed large models in workbooks, there's a high probability you've ran into a scenario where you're working with a fairly large number of defined names.  Tracking these defined names, where they link to, and what data they represent can be cumbersome at best.  Especially if you're tracking down bugs or errors.  If you're working with hidden defined names the debugging process can become a nightmare.

To document all of the defined names (hidden or visible) in a workbook I use the procedure below.

The code creates a new Worksheet in the Active Workbook labeled "Defined_Names".  Looping through each name in the workbook, the code creates columns for the following information:

1. Name: The name of the defined Range/cell;
2. Visible: Is the name visible to the user through the Name Manager (ALT-I, N, D or Formulas>Name Manager)
3. RefersTo: The address of the linked range or cell.  Sometimes this can also be a value that's been set to a defined name.
4. Value: The value of the range the defined name references.

Option Explicit
'Code created by Business Bear for yunolikeexcel.com
Sub ListNames()
    Dim x As Long
    Dim i As Long
    Dim wb As Workbook
    Dim Name As Name
    Const WSName = "Defined_Names"

    'Turn off alerts; prevents user from having to select yes if sheet
    'to delete exists
    Application.DisplayAlerts = False
    Set wb = ActiveWorkbook
    'Dirty way to check and delete if sheet exists
    On Error Resume Next
    wb.Worksheets(WSName).Delete
    On Error GoTo 0
    wb.Worksheets.Add
    ActiveSheet.Name = WSName

    'Set columns to read as text only; 
    'prevents links to ranges and arrays displaying an error
    Range(Columns(1), Columns(4)).EntireColumn.NumberFormat = "@"
    Cells(1, 1) = "Name"
    Cells(1, 2) = "Visible"
    Cells(1, 3) = "RefersTo"
    Cells(1, 4) = "Value"

    i = 0
    'Loop through each name in workbook
    For Each Name In wb.Names
        i = i + 1
        Cells(i + 1, 1) = Names(i).NameLocal
        Cells(i + 1, 2) = Names(i).Visible
        Cells(i + 1, 3) = Names(i).RefersTo
        'Prevents error if name references value only
        On Error Resume Next
        Cells(i + 1, 4) = Names(i).RefersToRange.Value
        On Error GoTo 0
    Next Name
    Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    'Turn on alerts again for user
    Application.DisplayAlerts = True
End Sub