fn_Is_Hyperlink: Detect Hyperlinks in Cells

Overview

Excel VBA UDF

A custom worksheet function that returns TRUE/FALSE indicating whether each cell in a range contains a hyperlink. Useful for auditing workbooks and finding cells with embedded links.

How It Works

Hyperlink_Range: The range of cells to check.
Tests .Hyperlinks.Count > 0 for each cell.
Returns a spilled array matching the shape of the input range.

Usage

=fn_Is_Hyperlink(A1:A100)

Use with SUMPRODUCT to count hyperlinks: =SUMPRODUCT(--fn_Is_Hyperlink(A1:A100))

The VBA Code

Function fn_Is_Hyperlink(Hyperlink_Range As Range) As Variant
    Dim Arr() As Variant
    ReDim Arr(1 To Hyperlink_Range.Rows.Count, 1 To Hyperlink_Range.Columns.Count)
    Dim r As Long, c As Long
    For r = 1 To Hyperlink_Range.Rows.Count
        For c = 1 To Hyperlink_Range.Columns.Count
            Arr(r, c) = (Hyperlink_Range(r, c).Hyperlinks.Count > 0)
        Next c
    Next r
    fn_Is_Hyperlink = Arr
End Function