Workbook Statistics Report Generator in VBA

Overview

Excel VBA Analytics

Understanding the scale and complexity of a workbook is the first step in any model review. This macro generates a comprehensive statistics report showing used ranges, formula density, dynamic array usage, and unique formula counts for every sheet.

The report is saved as a new workbook with a structured table. An optional deep analysis mode traces inter-sheet formula dependencies, building a cross-reference matrix showing how sheets connect to each other.

Report Contents

Sheet Dimensions: Used rows and columns for each worksheet, showing where data actually lives
Constant vs Formula Split: Separate row and column counts for constant cells and formula cells, revealing the calculation density
Dynamic Array Metrics: Counts both parent spill formulas and their child output cells, quantifying dynamic array usage
Unique Formulae: Uses R1C1 notation to count truly distinct formulas, filtering out copies of the same logic
Cross-Sheet Dependencies: Optional deep analysis traces precedent arrows to build a sheet-to-sheet dependency matrix

The VBA Code

Sub Workbook_Stats()
    Dim Wkb As Workbook: Set Wkb = ActiveWorkbook
    Dim New_Wkb As Workbook: Set New_Wkb = Workbooks.Add
    Dim Map_Wks As Worksheet: Set Map_Wks = New_Wkb.ActiveSheet
    Map_Wks.Name = "Model Stats"
    
    ' Header
    Map_Wks.Range("A1").Value = "Stats Report Workbook"
    Map_Wks.Range("A2").Value = "Date: " & Format(Now, "DD/MM/YYYY hh:mm:ss AM/PM")
    Map_Wks.Range("E3").Value = Wkb.Path
    
    ' Column headers
    Map_Wks.Range("A6").Value = "No"
    Map_Wks.Range("B6").Value = "Sheet"
    Map_Wks.Range("C6").Value = "Used Rows"
    Map_Wks.Range("D6").Value = "Used Cols"
    Map_Wks.Range("E6").Value = "Constant Rows"
    Map_Wks.Range("F6").Value = "Constant Cols"
    Map_Wks.Range("G6").Value = "Formula Rows"
    Map_Wks.Range("H6").Value = "Formula Cols"
    Map_Wks.Range("I6").Value = "Dyn Array Child Rows"
    Map_Wks.Range("J6").Value = "Dyn Array Child Cols"
    Map_Wks.Range("K6").Value = "Dyn Array Parent Rows"
    Map_Wks.Range("L6").Value = "Dyn Array Parent Cols"
    Map_Wks.Range("M6").Value = "Unique Formulae"
    
    Dim Idx As Long: Idx = 6
    Dim Wks As Worksheet
    
    For Each Wks In Wkb.Sheets
        Idx = Idx + 1
        Map_Wks.Cells(Idx, 1).Value = Idx - 6
        Map_Wks.Cells(Idx, 2).Value = Wks.Name
        Map_Wks.Cells(Idx, 3).Value = Wks.UsedRange.Rows.Count
        Map_Wks.Cells(Idx, 4).Value = Wks.UsedRange.Columns.Count
        
        ' Count constants, formulas, dynamic arrays
        On Error Resume Next
        Dim Txt_Rng As Range: Set Txt_Rng = Wks.Cells.SpecialCells(xlCellTypeConstants)
        Dim Frm_Rng As Range: Set Frm_Rng = Wks.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        
        ' Write row/col counts for each category
        ' Count unique formulas using R1C1 Dictionary
        Dim Frm_Dic As Object: Set Frm_Dic = CreateObject("Scripting.Dictionary")
        If Not Frm_Rng Is Nothing Then
            Dim Cel As Range
            For Each Cel In Frm_Rng
                If Not Frm_Dic.Exists(Cel.FormulaR1C1) Then Frm_Dic.Add Cel.FormulaR1C1, 1
            Next Cel
        End If
        Map_Wks.Cells(Idx, 13).Value = Frm_Dic.Count
        
        Set Txt_Rng = Nothing: Set Frm_Rng = Nothing
        Frm_Dic.RemoveAll
    Next Wks
    
    Map_Wks.Activate
End Sub

Final Thoughts

This statistics report gives you an instant overview of workbook complexity. The unique formula count is particularly revealing: a sheet with 10,000 formula cells but only 50 unique R1C1 formulas is well-structured, while one with thousands of unique formulas may need attention.