Creating a Name Report with VBA Code
If you use lots of named cells and ranges in your workbook, you may be surprised to discover that Excel provides no way to list the details for each name. Useful information appears in the Name Manager dialog box, but there is no way to display this information in a manner that can be printed.
The VBA code offered here generates a useful report that describes the names defined in any workbook.
Example Name Report
Here, you see an example of a Name Report.
The report, which is created on a new worksheet, includes the following information for each name:
Name: The name.
RefersTo: The definition for the name. Usually, this will be a cell or range, but a name can also define formula.
Cells: The number of cells contained in the named range. For named formulas, this field displays #N/A.
Scope: The scope of the name — either Workbook, or the name of the specific worksheet on which the name is valid.
Hidden: True if the name is hidden. Hidden names are created by some add-ins (such as Solver), and do not appear in the Name Manager dialog box.
Error: True if the name contains an erroneous reference.
Link: A hyperlink that, when clicked, activates the named range. Only names that refer to cells or ranges include a link.
Comment: The comment for the name, if any.
The VBA code
To use this code, press Alt+F11 to activate Visual Basic Editor. Then choose Insert → Module to insert a new VBA module. Copy the code and paste it into the new module.
Sub GenerateNameReport() ‘ Generates a report for all names in the workbook ‘ (Does not include Table names) Dim n As Name Dim Row As Long Dim CellCount As Variant ‘ Exit if no names If ActiveWorkbook.Names.Count = 0 Then MsgBox “The active workbook has no defined names.” Exit Sub End If ‘ Exit if workbook is protected If ActiveWorkbook.ProtectStructure Then MsgBox “A new sheet cannot be added because the workbook is protected.” Exit Sub End If ‘ Insert a new sheet for the report ActiveWorkbook.Worksheets.Add ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count) ActiveWindow.DisplayGridlines = False ‘ Add first line of title Range(“A1:H1”).Merge With Range(“A1”) .Value = “Name Report for: “ & ActiveWorkbook.Name .Font.Size = 14 .Font.Bold = True .HorizontalAlignment = xlCenter End With ‘ Add second line of title Range(“A2:H2”).Merge With Range(“A2”) .Value = “Generated “ & Now .HorizontalAlignment = xlCenter End With ‘ Add the headers Range(“A4:H4”) = Array(“Name”, “RefersTo”, “Cells”, _ “Scope”, “Hidden”, “Error”, “Link”, “Comment”) ‘ Loop through the names Row = 4 On Error Resume Next For Each n In ActiveWorkbook.Names Row = Row + 1 ‘Column A: Name If n.Name Like “*!*” Then Cells(Row, 1) = Split(n.Name, “!”)(1) ‘ Remove sheet name Else Cells(Row, 1) = n.Name End If ‘Column B: RefersTo Cells(Row, 2) = “‘“ & n.RefersTo ‘Column C: Number of cells CellCount = CVErr(xlErrNA) ‘ Return value for named formula CellCount = n.RefersToRange.CountLarge Cells(Row, 3) = CellCount ‘ Column D: Scope If n.Name Like “*!*” Then Cells(Row, 4) = Split(n.Name, “!”)(0) ‘ extract sheet name Cells(Row, 4) = Replace(Cells(Row, 4), “‘“, ““) ‘remove apostrophes Else Cells(Row, 4) = “Workbook” End If ‘Column E: Hidden status Cells(Row, 5) = Not n.Visible ‘Column F: Erroroneous name Cells(Row, 6) = n.RefersTo Like “*[#]REF!*” ‘Column G: Hyperlink If Not Application.IsNA(Cells(Row, 3)) Then ActiveSheet.Hyperlinks.Add _ Anchor:=Cells(Row, 7), _ Address:=““, _ SubAddress:=n.Name, _ TextToDisplay:=n.Name End If ‘Column H: Comment Cells(Row, 8) = n.Comment Next n ‘ Convert it to a table ActiveSheet.ListObjects.Add _ SourceType:=xlSrcRange, _ Source:=Range(“A4”).CurrentRegion ‘ Adjust the column widths Columns(“A:H”).EntireColumn.AutoFit End Sub
Generating a Report
Execute the GenerateNameReport procedure, and the report is generated on a new worksheet in the active workbook. The code doesn’t have to be in the workbook that contains the names for the report.
If you find this code useful, you might want to store it in your Personal Macro Workbook, or create an add-in.