Broken Hyperlink Detector: Find Every Dead Link Before It Reaches the Client

TL;DR: Before you strip hyperlinks from a workpaper (or worse, before you send it to a client with broken links that reveal your firm’s folder structure), you need to know what’s there. This macro catalogs every hyperlink in the workbook, checks file-path links against the filesystem, and produces a one-page report showing exactly which links are live and which are dead.

The Problem

You’re about to send the Henderson workpapers to the client. You know there are hyperlinks scattered across the file — there always are. But which ones? The TB sheet has a link to a prior-year file on the network share. The Fixed Assets sheet links to three supporting PDFs on the prior preparer’s desktop. The Notes sheet has the prior preparer’s email address as a clickable link.

You hover over cells, right-click → Edit Hyperlink, squint at the target path, and try to guess whether \\FILESERVER\TaxDept\Engagements\2024\Old_TB.xlsx still exists. You spend 15 minutes manually auditing 40 links across 12 sheets, and you’re still not sure you caught them all. Then you run the hyperlink stripper, delete everything, and hope you didn’t accidentally keep a broken link or delete a live reference you needed.

This macro eliminates the guessing. It catalogs every hyperlink — file paths, web links, email addresses — and tests every file-path link against the actual filesystem using Dir(). The output is a one-page report with columns for Sheet, Cell Address (hyperlinked back to the source), Display Text, Target, and Status. File links that point to actual files get “OK — File found.” Dead links get “BROKEN — File not found” in bold red. Web and email links get tagged for manual review.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop, Windows)
A workbook with hyperlinks — any workpaper you’re auditing before sending or archiving
Output is a new “Hyperlink-Report” sheet, created at the end of the workbook — your original data is never touched

What the macro does:

Catalogs every hyperlink on every sheet (file paths, web URLs, and email links)
Tests every file-path link against the actual filesystem using Dir()
Reports web and email links as “Not tested” (the macro doesn’t reach out to the internet)
Creates a formatted, filterable report sheet with hyperlinks back to every source cell
Reports a summary in the MsgBox: total links, broken file links, web links, email links

What the macro does NOT do:

It does not test web links. Dir() only works on the local filesystem and network shares. Web links (http:// / https://) are flagged as “Not tested” so you can review them manually
It does not test email links. mailto: links are flagged as “Not tested”
It does not delete or modify any hyperlinks. This is a read-only audit tool. Use Hyperlink Stripper to remove links after review
It does not follow UNC network paths that are unreachable. If your machine can’t access \\OLDSERVER\Share\, Dir() returns empty and the link is flagged as broken — even if the file technically exists on a server you’re not connected to

Limitations:

Dir() only checks the exact path stored in the hyperlink. If the path uses mapped drive letters (P:\...) and those drives aren’t mapped on your machine, the link will appear broken
The macro cannot distinguish between “file doesn’t exist” and “you don’t have permission to access this folder.” Both return an empty string from Dir()
Web links are never tested automatically. If you need live URL validation, add a WinHTTP request (see Adapt It section)
Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook (PERSONAL.XLSB) and change ThisWorkbook to ActiveWorkbook to run on any open file

#The Macro

Option Explicit

Sub BrokenHyperlinkDetector()
    ' ── Broken Hyperlink Detector ───────────────────────
    ' Catalogs every hyperlink on every sheet. Tests
    ' file-path links against the filesystem with Dir().
    ' Produces a one-page report showing which links
    ' are live and which are broken.
    '
    ' Read-only. Does not modify any hyperlinks.
    ' ────────────────────────────────────────────────────

    Const OUT_SHEET As String = "Hyperlink-Report"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim hl As Hyperlink
    Dim outRow As Long
    Dim totalLinks As Long
    Dim fileLinks As Long, brokenLinks As Long
    Dim webLinks As Long, emailLinks As Long
    Dim otherLinks As Long
    Dim sheetCount As Long
    Dim target As String, status As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Count before building output ───────────
    totalLinks = 0
    sheetCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Hyperlinks.Count > 0 Then
            sheetCount = sheetCount + 1
            totalLinks = totalLinks + ws.Hyperlinks.Count
        End If
    Next ws

    If totalLinks = 0 Then
        MsgBox "No hyperlinks found in this workbook.", _
               vbInformation, "Hyperlink Audit"
        GoTo CleanUp
    End If

    ' ── Step 2: Remove old output sheet ────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Step 3: Create output sheet ────────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Step 4: Write headers ──────────────────────────
    wsOut.Range("A1:E1").Value = Array( _
        "Sheet", "Cell", "Display Text", "Target", "Status")
    With wsOut.Range("A1:E1")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    ' ── Step 5: Catalog and test every hyperlink ───────
    outRow = 2
    fileLinks = 0: brokenLinks = 0
    webLinks = 0: emailLinks = 0: otherLinks = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Hyperlinks.Count = 0 Then GoTo NextSheet

        For Each hl In ws.Hyperlinks
            ' Sheet name
            wsOut.Cells(outRow, 1).Value = ws.Name

            ' Hyperlinked cell address (clickable back to source)
            wsOut.Hyperlinks.Add _
                Anchor:=wsOut.Cells(outRow, 2), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & _
                    hl.Range.Address(False, False), _
                TextToDisplay:=hl.Range.Address(False, False)

            ' Display text
            On Error Resume Next
            wsOut.Cells(outRow, 3).Value = hl.TextToDisplay
            On Error GoTo CleanUp

            ' Target path/URL
            target = hl.Address
            wsOut.Cells(outRow, 4).Value = target

            ' Determine status by link type
            If Left(target, 7) = "mailto:" Then
                status = "Email — Not tested"
                emailLinks = emailLinks + 1
            ElseIf Left(target, 4) = "http" Then
                status = "Web — Not tested"
                webLinks = webLinks + 1
            ElseIf InStr(target, ":") > 0 Or _
                   InStr(target, "\\") > 0 Then
                ' File-path link — test existence
                fileLinks = fileLinks + 1
                If Dir(target) <> "" Then
                    status = "OK — File found"
                Else
                    status = "BROKEN — File not found"
                    brokenLinks = brokenLinks + 1
                End If
            Else
                status = "Unknown — Review manually"
                otherLinks = otherLinks + 1
            End If

            wsOut.Cells(outRow, 5).Value = status

            ' Highlight broken links in red
            If Left(status, 6) = "BROKEN" Then
                wsOut.Range("A" & outRow & ":E" & outRow). _
                    Font.Color = RGB(180, 30, 30)
                wsOut.Cells(outRow, 5).Font.Bold = True
            End If

            outRow = outRow + 1
        Next hl

NextSheet:
    Next ws

    ' ── Step 6: Format output ──────────────────────────
    With wsOut
        .Columns("A:E").AutoFit
        .Columns("D").ColumnWidth = 55
        .Columns("E").ColumnWidth = 26
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
        .AutoFilterMode = False
        .Range("A1:E1").AutoFilter
    End With

    ' ── Step 7: Build summary message ──────────────────
    Dim msg As String
    msg = "Found " & totalLinks & " hyperlink(s) across " & _
          sheetCount & " sheet(s)." & vbCrLf & vbCrLf

    If fileLinks > 0 Then
        msg = msg & "File links: " & fileLinks & _
              " (" & brokenLinks & " broken)" & vbCrLf
    End If
    If webLinks > 0 Then
        msg = msg & "Web links: " & webLinks & _
              " — not tested" & vbCrLf
    End If
    If emailLinks > 0 Then
        msg = msg & "Email links: " & emailLinks & _
              " — not tested" & vbCrLf
    End If
    If otherLinks > 0 Then
        msg = msg & "Other links: " & otherLinks & _
              " — review manually" & vbCrLf
    End If

    msg = msg & vbCrLf & "Report created on '" & _
          OUT_SHEET & "' sheet." & vbCrLf & vbCrLf & _
          "Broken links are highlighted in red."

    If brokenLinks > 0 Then
        MsgBox msg, vbExclamation, "Hyperlink Audit"
    Else
        MsgBox msg, vbInformation, "Hyperlink Audit"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#The three categories of hyperlinks

Excel stores all hyperlinks in the same Hyperlinks collection, regardless of what they point to. The macro classifies each one by inspecting the Address property:

If Left(target, 7) = "mailto:" Then
    status = "Email — Not tested"
ElseIf Left(target, 4) = "http" Then
    status = "Web — Not tested"
ElseIf InStr(target, ":") > 0 Or _
       InStr(target, "\\") > 0 Then
    ' File-path link — test existence
    fileLinks = fileLinks + 1
    If Dir(target) <> "" Then
        status = "OK — File found"
    Else
        status = "BROKEN — File not found"
    End If
End If

Email links always start with mailto:. Web links start with http (which covers both http:// and https://). Everything else with a colon (drive letter) or UNC path prefix (\\) is treated as a file link and tested with Dir().

Links that don’t match any pattern — extremely rare, but possible with custom protocols or malformed addresses — get tagged “Unknown — Review manually.”

#Why `Dir()` instead of `FileSystemObject`

Dir() is the simplest way to check if a file exists in VBA. It’s built into the language, requires no references, and works on local drives and accessible network shares:

If Dir(target) <> "" Then
    status = "OK — File found"
Else
    status = "BROKEN — File not found"
End If

Dir() returns an empty string if the file doesn’t exist, the path is invalid, or you lack permissions. It does NOT distinguish between these cases — all three return the same result. This is acceptable for a triage tool: if Dir() can’t see the file, you can’t follow the link either, so “broken” is the practical truth.

The trade-off: Dir() does not test web links. You’d need WinHttp.WinHttpRequest to ping a URL, which requires internet access, adds latency (a slow server can hang the macro for 30 seconds), and often fails on corporate networks with proxies. The macro skips web link testing and flags them for manual review instead.

#Hyperlinked cell addresses in the report

Every row in the report has a clickable cell address that jumps back to the source hyperlink:

wsOut.Hyperlinks.Add _
    Anchor:=wsOut.Cells(outRow, 2), _
    Address:="", _
    SubAddress:="'" & ws.Name & "'!" & _
        hl.Range.Address(False, False), _
    TextToDisplay:=hl.Range.Address(False, False)

Address:="" means “no external target.” SubAddress points within the current workbook to the exact cell. The single quotes around ws.Name handle sheet names with spaces or special characters — 'Fixed Assets'!B3 is valid, Fixed Assets!B3 is not.

This is the same pattern used in the Comment Collector and Formula Reporter macros. It turns the report from a static list into a navigation tool: click any cell address in column B, and Excel jumps directly to the hyperlink in the source sheet.

#Broken links get red, not just a text label

A status of “OK — File found” and “BROKEN — File not found” look identical in a sea of 50 rows. The macro makes broken links visually jump out:

If Left(status, 6) = "BROKEN" Then
    wsOut.Range("A" & outRow & ":E" & outRow). _
        Font.Color = RGB(180, 30, 30)
    wsOut.Cells(outRow, 5).Font.Bold = True
End If

The entire row turns dark red and the Status column goes bold. At a glance, you can count the red rows and know exactly how many links are dead — no manual scanning required.

#AutoFilter on the report headers

After writing all the data, the macro adds an AutoFilter to the header row:

.Range("A1:E1").AutoFilter

This lets you filter the report in-place: show only broken links, only links from a specific sheet, only web links. Combined with the hyperlinked cell addresses, you can filter to “BROKEN” status and click through to every dead link in 30 seconds.

#The MsgBox changes icons based on findings

If any broken links are found, the MsgBox uses vbExclamation (yellow warning icon) to signal that something needs attention. If all file links are live (but web/email links exist), it uses vbInformation (blue info icon):

If brokenLinks > 0 Then
    MsgBox msg, vbExclamation, "Hyperlink Audit"
Else
    MsgBox msg, vbInformation, "Hyperlink Audit"
End If

This is a small UX detail, but it trains the user: yellow icon = go review the report, there are problems. Blue icon = all clear on file links, web/email links still need manual review.

#The counting pass separates from the reporting pass

Like the Comment Collector and Hyperlink Stripper, the macro first counts everything, then builds the report. If the count is zero, it exits with a clean message — no empty report sheet to delete later. This also means the MsgBox summary is accurate before you even scroll through the report.

#Adapt It

Run on the active workbook instead of the macro workbook. Store this macro in your Personal Macro Workbook (PERSONAL.XLSB) and replace ThisWorkbook with ActiveWorkbook:

For Each ws In ActiveWorkbook.Worksheets

Make sure to replace ALL four occurrences — the counting loop, the deletion of the old output sheet, the output sheet creation, and the main collection loop.

Test web links too. Add a WinHTTP request to check if URLs respond. This adds a reference to “Microsoft WinHTTP Services” and ~15 lines of code. Replace the web link status assignment with this:

Dim http As Object, testURL As String
testURL = target
On Error Resume Next
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.Open "HEAD", testURL, False
http.SetTimeouts 5000, 5000, 5000, 5000
http.Send
If Err.Number = 0 And http.Status = 200 Then
    status = "OK — Responded (200)"
Else
    status = "BROKEN — No response"
End If
On Error GoTo CleanUp
Set http = Nothing

Be aware: this will slow down the macro significantly if your workbook has many web links. Each request adds 1–5 seconds. Also add http.WaitForResponse for async-capable environments.

Change the output sheet name. Replace the constant at the top:

Const OUT_SHEET As String = "Link-Audit"

Skip specific sheets. Add a filter before the collection loop to exclude tabs you don’t want audited:

If ws.Name Like "Cover*" Or ws.Name = "TOC" Or _
   ws.Name = "Instructions" Then GoTo NextSheet

Place this immediately after If ws.Hyperlinks.Count = 0 Then GoTo NextSheet.

Add a “Last Modified” column for file links. For file links that are OK, you can append the file’s last-modified date using FileDateTime():

If Dir(target) <> "" Then
    status = "OK — File found"
    On Error Resume Next
    wsOut.Cells(outRow, 6).Value = _
        FileDateTime(target)
    On Error GoTo CleanUp
Else
    status = "BROKEN — File not found"
End If

Add a sixth column header “Last Modified” and change the header array from Array("Sheet", "Cell", "Display Text", "Target", "Status") to Array("Sheet", "Cell", "Display Text", "Target", "Status", "Last Modified"). Adjust the broken-link red-highlight range from "A" & outRow & ":E" & outRow to "A" & outRow & ":F" & outRow.

Add a keyboard shortcut. Assign Ctrl+Shift+L to the macro via Macro Options (Alt+F8 → Options). With the report sheet and hyperlink navigation, two keystrokes gives you a complete hyperlink audit.

See also: Hyperlink Stripper — run this audit first, then strip all links with one click. External Link Reporter — the formula-link counterpart that catalogs external workbook references. Comment Collector — same report-producing pattern applied to cell comments.

Download the sample workbook (8 sheets — 5 with hyperlinks including live file paths, broken file paths, web links, and email links, 2 clean sheets, and an Instructions tab)

xlsx 22 KB