Excel VBA module to test if hyperlink string refers to valid file

Question

I am trying to figure out a way to test if a hyperlink string in excel points to a valid file on our network. But my limited VBA knowledge seems to be getting the better of me.

Example: The hypothetical function "HyperTest" returns TRUE or FALSE depending on Hyperlink validity. Cell A1 Contains "c:\123.txt" Cell B1 Contains "=IF(HyperTest(A1),"Yay, the file is real!","Looks like somebody deleted your file.")

I assume the module code should resemble something like:

Public Function HyperTest(hyperpath As String) As Boolean
If [insert test here] Then
   return TRUE

Else
   return FALSE

End If

End Function

Answer 1

You can use the Dir() function. If the path doesn't exist, the function doesn't return anything. And since the default value of a boolean is False, you don't need an Else statement.

Public Function HyperTest(hyperpath As String) As Boolean

    If Dir(hyperpath) <> vbNullString Then HyperTest = True

End Function

Answer 2

you can use this small UDF to get your OP

Function HyperTest(c As Range)
    If Dir(c) <> "" Then
        HyperTest = "File exists."
    Else
        HyperTest = "File doesn't exist."
    End If
End Function

and call this function in any cell