Workbook Documentation Suite in VBA

Overview

Excel VBA Documentation

Understanding a workbook's structure is crucial when inheriting files or conducting reviews. This suite of four macros generates comprehensive documentation: named ranges, embedded objects, unique formulas in R1C1 notation, and chart series details.

Each tool creates a new worksheet (or workbook) with a structured report and hyperlinks for quick navigation back to the source cells.

The Four Tools

List Names Range: Documents every visible named range in the workbook with its scope (worksheet vs workbook level), the name, and the RefersTo formula
List Objects: Catalogs all shapes, images, charts, and other embedded objects across every sheet with their positions
List Unique Formulas R1C1: Groups identical formulas by their R1C1 representation, revealing the true number of distinct calculations in your workbook
Chart Identifier: Extracts the SERIES formula for every chart series in the workbook, making it easy to see what data each chart is referencing

The VBA Code

Public Sub List_Names_Range()
    Dim Nam_Rng As Variant
    Dim Idx_Spn As Long: Idx_Spn = 1
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    New_Wks.Cells.ClearContents
    For Each Nam_Rng In ActiveWorkbook.Names
        If Nam_Rng.Visible Then
            Idx_Spn = Idx_Spn + 1
            New_Wks.Cells(Idx_Spn, 1).Value = IIf(TypeName(Nam_Rng.Parent) = "Worksheet", _
                Nam_Rng.Parent.Name, "[Workbook]")
            New_Wks.Cells(Idx_Spn, 2).Value = Nam_Rng.Name
            New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Spn, 3), "", Nam_Rng.RefersTo
            New_Wks.Cells(Idx_Spn, 4).Value = "'" & Nam_Rng.RefersTo
        End If
    Next Nam_Rng
    
    New_Wks.Range("A1").Value = "Scope"
    New_Wks.Range("B1").Value = "Name"
    New_Wks.Range("C1").Value = "Hyperlink"
    New_Wks.Range("D1").Value = "Refers To"
    New_Wks.Range("A:D").EntireColumn.AutoFit
End Sub

Public Sub List_Objects()
    Dim Obj As Shape, Wks As Worksheet
    Dim Idx_Lst As Long: Idx_Lst = 1
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    On Error Resume Next
    For Each Wks In ActiveWorkbook.Sheets
        For Each Obj In Wks.Shapes
            Idx_Lst = Idx_Lst + 1
            New_Wks.Cells(Idx_Lst, 1).Value = Obj.Parent.Name
            New_Wks.Cells(Idx_Lst, 2).Value = Obj.TopLeftCell.Address
            New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Lst, 3), "", _
                "'" & Wks.Name & "'!" & Obj.TopLeftCell.Address
        Next Obj
    Next Wks
    On Error GoTo 0
    
    New_Wks.Range("A1").Value = "Worksheet"
    New_Wks.Range("B1").Value = "Cell Address"
    New_Wks.Range("C1").Value = "Hyperlinks"
    New_Wks.Range("A:C").EntireColumn.AutoFit
End Sub

Public Sub List_Unique_Formulas_R1C1()
    Dim Frm_Dic As Object: Set Frm_Dic = CreateObject("Scripting.Dictionary")
    Dim Wks As Worksheet, Cel As Range
    Dim Idx_Lst As Long: Idx_Lst = 1
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    On Error Resume Next
    For Each Wks In ActiveWorkbook.Sheets
        For Each Cel In Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
            If Not Frm_Dic.Exists(Cel.FormulaR1C1) Then
                Frm_Dic.Add Cel.FormulaR1C1, Cel
            Else
                Set Frm_Dic(Cel.FormulaR1C1) = Union(Frm_Dic(Cel.FormulaR1C1), Cel)
            End If
        Next Cel
        
        Dim Frm As Variant
        For Each Frm In Frm_Dic.Keys
            Dim Rng_Are As Range
            For Each Rng_Are In Frm_Dic(Frm).Areas
                Idx_Lst = Idx_Lst + 1
                New_Wks.Cells(Idx_Lst, 1).Value = Wks.Name
                New_Wks.Cells(Idx_Lst, 2).Value = Rng_Are.Address
                New_Wks.Cells(Idx_Lst, 4).Value = "'" & Frm
                New_Wks.Cells(Idx_Lst, 5).Value = "'" & Rng_Are.Cells(1, 1).Formula
            Next Rng_Are
        Next Frm
        Frm_Dic.RemoveAll
    Next Wks
    
    New_Wks.Range("A1").Value = "Worksheet"
    New_Wks.Range("B1").Value = "Cell Address"
    New_Wks.Range("C1").Value = "Hyperlinks"
    New_Wks.Range("D1").Value = "Formula R1C1"
    New_Wks.Range("E1").Value = "Formula A1"
    New_Wks.Range("A:C").EntireColumn.AutoFit
    New_Wks.Range("D:E").ColumnWidth = 60
End Sub

Public Sub Chart_Identifier()
    Dim Wks As Worksheet, Cht As Object, Srs As Series
    Dim Cur_Wkb As Workbook: Set Cur_Wkb = ActiveWorkbook
    Dim New_Wkb As Workbook: Set New_Wkb = Workbooks.Add
    
    New_Wkb.Range("A1").Value = "File:"
    New_Wkb.Range("B1").Value = Cur_Wkb.Name
    New_Wkb.Range("A6:I6").Value = Array("Sheet", "Chart", "Series", _
        "Formula", "Name", "Y-axis", "X-axis", "Bubble Size", "Label Range")
    
    Dim i As Long: i = 0
    For Each Wks In Cur_Wkb.Sheets
        For Each Cht In Wks.ChartObjects
            Cht.Activate
            For Each Srs In ActiveChart.SeriesCollection
                i = i + 1
                New_Wkb.Range("A6").Offset(i, 0).Value = Wks.Name
                New_Wkb.Range("A6").Offset(i, 1).Value = Cht.Name
                New_Wkb.Range("A6").Offset(i, 2).Value = Srs.Name
                On Error Resume Next
                New_Wkb.Range("A6").Offset(i, 3).Value = "'" & Srs.Formula
                On Error GoTo 0
            Next Srs
        Next Cht
    Next Wks
End Sub

Final Thoughts

Together, these four tools give you a complete picture of what is inside a workbook. They are especially valuable during model reviews, handovers, and compliance audits where understanding every component matters.