How can I handle network exceptions in VBA?

Question

    Sub test()

    Dim id As String
    id = "user1234"
    Dim PHARMA As String
    PHARMA = "http://xxxx"
    Dim url As String
    url = PHARMA & id


    Dim IE As MSXML2.XMLHTTP60
    Set IE = New MSXML2.XMLHTTP60

    IE.Open "GET", url, False
    IE.send

    'This part could crash the program crash if the user 
    'or the url is wrong, how can I handle this case with exceptions?  

    While IE.readyState <> 4
        DoEvents
    Wend

    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLBody As MSHTML.HTMLBody

    Set HTMLDoc = New MSHTML.HTMLDocument

    ...

    End Sub

Here the kind of anwser I put a wrong ID or url

When I go in chrome in the network tab :

Cache-Control:no-cache
Content-Length:0
Date:Tue, 25 Oct 2016 15:22:04 GMT
Expires:-1
Pragma:no-cache
Server:Microsoft-IIS/7.0

When I go in the answer tab I have :

the request has no response data available (normal because url or id is wrong)

How can I handle network exceptions in VBA ?

Answer 1

I am assuming you don't need the IE object after the .Send call and work off the HTMLDocument object onward:

Private Function TryHttpGet(ByVal url As String) As MSHTML.HTMLDocument
    On Error GoTo CleanFail
    Dim document As MSHTML.HTMLDocument        

    With New MSXML2.XMLHTTP60
        .Open "GET", url, False
        .Send

        'not needed per comment: https://stackoverflow.com/questions/40244183#comment67753122_40244183
        'While .ReadyState <> 4 'todo: replace magic value with meaningful constant
        '    DoEvents
        'Wend

        'Set document = ...

    End With

CleanExit:
    'cleanup code here?
    Set TryHttpGet = document
    Exit Sub

CleanFail:
    'error-handling code here
    Set document = Nothing
    Resume CleanExit
End Sub

The calling code can then do this:

Set document = TryHttpGet(url)
If document Is Nothing Then
    MsgBox "HTTP request failed for URL: " & url & ".", vbExclamation
    Exit Sub
End If