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 ?

You don't get *exceptions* in VBA. You get *runtime errors*. [Error Handling](http://stackoverflow.com/documentation/vba/3211/error-handling#t=201610251637166952314) on Docs.SO would be a good start, more specifically the [On Error statement](http://stackoverflow.com/documentation/vba/3211/error-handling/11021/on-error-statement#t=20161025163747113082) topic. — Mathieu Guindon, Oct 25 '16 at 16:37
Also these lines are unnecessary for sending synced GET/POST requests in VBA: `While IE.readyState <> 4` `DoEvents` `Wend` — jamheadart, Oct 25 '16 at 16:46

score 0 · Accepted Answer · edited Sep 21 '17 at 21:49

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

Hi, Almost work but it goes each time inside "CleanFail" even if the link is good , any way to display the errors ? — Simon, Oct 25 '16 at 21:56

How can I handle network exceptions in VBA?

1 Answers1