Excel VBA - Website Status Checker

[jfoBoston]

I'm trying to setup an workbook that will check the current status of a website (URL RETURN CODE 200) using a Public Function. While I found an example of one that seems to work like a charm, I'm not sure how it is being triggered and how to "refresh" it.

Public Function CheckURL(url As String)
    Dim request As Object
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    On Error GoTo haveError
    With request
        .Open "HEAD", url
        .Send
        CheckURL = .Status
        End With
        Exit Function
haveError:
    CheckURL = Err.Description
End Function

Does this automatically check so often or does it simply trigger on open/data refresh?

Thanks in advance for any insight.

 

[Legacy 456155]

Are you calling the function from a cell?
Are you desiring to call your function at regular intervals? Or in response to some event?

This tweak will give you a bit better performance and automatically bind to the most recent version. Note Static and dropping the version.

Public Function CheckURL(url As String)
    Static request As Object
    If request Is Nothing Then Set request = CreateObject("WinHttp.WinHttpRequest")
    On Error GoTo haveError
    With request
        .Open "HEAD", url
        .Send
        CheckURL = .Status
        End With
        Exit Function
haveError:
    CheckURL = Err.Description
End Function

You might gain a negligible performance improvement if you early bind as well by setting a reference to Microsoft WINHTTP Service, Version x.x and changing a couple lines of code.

    Static request As Object
    If request Is Nothing Then Set request = CreateObject("WinHttp.WinHttpRequest")

to

    Static request As WinHttp.WinHttpRequest
    If request Is Nothing Then Set request = New WinHttp.WinHttpRequest

I would not bother with the optimizations unless you will be calling your function often.