fn_Does_File_Path_Exist: Check File Paths from Cells

Overview

Excel VBA UDF

A custom worksheet function that checks whether the file path stored in each cell actually exists on disk. Returns a TRUE/FALSE array, perfect for validating lists of file references in project trackers or asset registers.

How It Works

File_Path: A range of cells containing file path strings.
Uses VBA's Dir() function to test each path.
Error handling catches invalid paths (e.g., malformed strings) and returns FALSE.

Usage

=fn_Does_File_Path_Exist(A1:A50)

Returns a spilled column of TRUE/FALSE. Use conditional formatting to highlight broken paths in red.

The VBA Code

Function fn_Does_File_Path_Exist(File_Path As Range) As Variant
    Dim Arr() As Variant
    ReDim Arr(1 To File_Path.Rows.Count, 1 To File_Path.Columns.Count)
    Dim r As Long, c As Long
    For r = 1 To File_Path.Rows.Count
        For c = 1 To File_Path.Columns.Count
            Dim p As String: p = CStr(File_Path(r, c).Value2)
            On Error Resume Next
            Arr(r, c) = (Len(Dir(p, vbNormal)) > 0)
            If Err.Number <> 0 Then Arr(r, c) = False
            On Error GoTo 0
        Next c
    Next r
    fn_Does_File_Path_Exist = Arr
End Function