Excel "this" keyword / current cell

[JohnLBevan]

Hi Guys,

please could you help me? I've written a VBA function, ValidURL, which takes a string (url) as an argument, performs an HTTP GET operation on it, and returns true if the URL is valid, or false otherwise.

I'm now trying to setup data validation such that a column can only contain valid URLs. To do this, I've selected the column, chosen custom validation, and enterred the formula ValidURL(this). However, I'm not sure if there's a keyword which performs the "this" or self-reference function - ie. gives me some way to reference the cell to which the validation is being applied.

Thanks in advance for any advise.

Kind regards,

JB

Public Function ValidURL(ByRef url As String) As Boolean
    Dim valid As Boolean
    Dim request As New WinHttpRequest 'click tools, references, "Microsoft WinHTTP Services" to make this class available
    valid = False
    On Error Resume Next
    request.Open "GET", url
    request.Send
    valid = (request.Status = 200)
    If Err.Number <> 0 Then valid = False
    Set request = Nothing
    On Error GoTo 0
    ValidURL = valid
End Function

 

[Andrew Poulsom]

Welcome to the Board.

Refer to the cell that's active when you choose Data Validation. If the active cell is A1:

=ValidURL(A1)