Test is cell contents are a URL

[Jim]

Is there a quick and easy way to test if the contents of a cell (it's
text, not a hyperlink object) are a valid URL? I'm wondering about any
functionality and not just looking for the http or some other text to
parse out.

TIA,
Jim

 

[Jake Marx]

Hi Jim,

Is there a quick and easy way to test if the contents of a cell (it's
text, not a hyperlink object) are a valid URL? I'm wondering about
any functionality and not just looking for the http or some other
text to parse out.

You could try something like this, which can be called from VBA or as a
user-defined worksheet function. I haven't tested it much, but it seems to
work OK.

Public Function IsValidURL(rsURL As String) As Variant
Dim x As Object

On Error GoTo ErrHandler

Set x = CreateObject("Microsoft.XMLHTTP")
x.Open "GET", rsURL
x.send
Do Until x.readyState = 4
DoEvents
Loop
IsValidURL = (x.Status <> 404)

ExitRoutine:
Set x = Nothing
Exit Function
ErrHandler:
Select Case Err.Number
Case 429
IsValidURL = "XML lib not installed."
Case Else
IsValidURL = False
End Select
Resume ExitRoutine
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]