Scanning Workbooks for Errors with VBA

Overview

Excel VBA Error Handling

In large workbooks, error cells can hide deep within sheets and go unnoticed until they cascade into wrong results. This VBA macro scans every worksheet in the active workbook and produces a clickable error report with hyperlinks that jump directly to each problem cell.

The scanner detects all Excel error types including #REF!, #VALUE!, #N/A, #DIV/0!, #NAME?, and #NULL!. It also catches errors inside dynamic array spill ranges that standard SpecialCells might miss.

How It Works

Formula Text Scan: Searches formula strings for #REF! references that remain embedded in formulas even when the cell itself may not show an error
SpecialCells Detection: Uses xlCellTypeFormulas with error flag (16) to find all cells currently evaluating to an error value
Dynamic Array Coverage: Checks spilling formulas and their child cells for errors that may appear only in the spill range
Hyperlinked Report: Creates a new worksheet with Sheet Name, Cell Address, clickable Hyperlink, and Error Type columns for quick navigation

The VBA Code

Public Sub List_Hash_Errors()
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dim Cel As Range, Rng As Range
    Dim Idx_Lst As Long: Idx_Lst = 1
    Dim Wks As Worksheet
    
    ' Create output sheet
    Dim New_Wks As Worksheet: Set New_Wks = Worksheets.Add
    
    For Each Wks In ActiveWorkbook.Worksheets
        If Wks.Name = New_Wks.Name Then GoTo Skip
        
        ' Check for #REF! in formula text
        Dim Err_Ref_Rng As Range: Set Err_Ref_Rng = Nothing
        On Error Resume Next
        For Each Cel In Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
            If InStr(Cel.Formula, "#REF!") > 0 Then
                If Err_Ref_Rng Is Nothing Then
                    Set Err_Ref_Rng = Cel
                Else
                    Set Err_Ref_Rng = Union(Err_Ref_Rng, Cel)
                End If
            End If
        Next Cel
        On Error GoTo 0
        
        ' Log errors with hyperlinks
        If Not Err_Ref_Rng Is Nothing Then
            For Each Rng In Err_Ref_Rng.Areas
                Idx_Lst = Idx_Lst + 1
                New_Wks.Cells(Idx_Lst, 1).Value = Wks.Name
                New_Wks.Cells(Idx_Lst, 2).Value = Rng.Address(False, False)
                New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Lst, 3), "", _
                    "'" & Wks.Name & "'!" & Rng.Address(False, False)
                New_Wks.Cells(Idx_Lst, 4).Value = "#REF!"
            Next Rng
        End If
        
        ' Check for all other error types
        Dim Err_Rng As Range: Set Err_Rng = Nothing
        On Error Resume Next
        Set Err_Rng = Wks.Cells.SpecialCells(xlCellTypeFormulas, 16)
        On Error GoTo 0
        
        If Not Err_Rng Is Nothing Then
            For Each Rng In Err_Rng.Areas
                Idx_Lst = Idx_Lst + 1
                New_Wks.Cells(Idx_Lst, 1).Value = Wks.Name
                New_Wks.Cells(Idx_Lst, 2).Value = Rng.Address(False, False)
                New_Wks.Hyperlinks.Add New_Wks.Cells(Idx_Lst, 3), "", _
                    "'" & Wks.Name & "'!" & Rng.Address(False, False)
            Next Rng
        End If
Skip:
    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 = "Error Type"
    New_Wks.Range("A:D").EntireColumn.AutoFit
End Sub

Final Thoughts

This error scanner is one of the first tools to run when inheriting or reviewing a workbook. The hyperlinked output makes it easy to jump straight to problem areas and fix them systematically rather than hunting through sheets manually.