VBA + Excel + Try Catch

Question

In VBA, I'm doing a simple script that records a version of a spreadsheet being used.

Private Sub Workbook_Open()
    version = "1.0"

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "<WEB SERVICE>"

    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send ("version=" + version)
End Sub     

The process works fine, but...

I'm trying to do a try catch so if the web host is offline, instead of showing a run time error I catch it and suppress.

What is the best way to try catch in VBA so there is no error message shown?

Answer 1

This generalizes Trevor's accepted answer for people coming from a search. I also added my own method and ended all error catching asap.
The subs used as examples are for readability only and explicitly meant as placeholders for blackbox, unsafe actions, e.g. for IO or web connection like in the example.

---

Handle error immediately in-place and resume after:

(I think this is the closest to an actual Try-Catch block you can get.)

Private Sub UseTryCatchEquivalent()
    DoSolidStuff

' Try
    On Error Goto Catch
    DoErrorProneStuff1
    DoErrorProneStuff2 ' DOES NOT get executed when 1 errors.
    On Error Goto 0
' Catch
    If False Then
Catch:
        HandleError
'        Exit Sub ' Optionally quit sub here.
    End If
' End Try

    DoSolidStuff
End Sub

---

---

Handle error immediately at bottom and exit sub:

Private Sub HandleErrorAndExit()
    DoSolidStuff

    On Error Goto Catch
    DoErrorProneStuff1
    DoErrorProneStuff2 ' DOES NOT get executed when 1 errors.
    On Error Goto 0

    DoSolidStuff
    Exit Sub

Catch:
    HandleError
End Sub

---

Handle error immediately at bottom and resume at some point:

Private Sub HandleErrorAndResume()
    DoSolidStuff

    On Error Goto Catch
    DoErrorProneStuff1
    DoErrorProneStuff2 ' DOES NOT get executed when 1 errors.
    On Error Goto 0

Continue:
    DoSolidStuff
    Exit Sub

Catch:
    HandleError
    Resume Continue
End Sub

---

Ignore all errors first, just run and handle them later:

Private Sub IgnoreErrorAndHandleLater()
    DoSolidStuff

    ' Everything from here on out will ignore all errors.
    On Error Resume Next
    DoErrorProneStuff1
    DoErrorProneStuff2 ' DOES get executed when 1 errors.
    ' Stop ignoring errors.
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError
        Err.Clear
'        Exit Sub ' Optionally quit sub here.
    End If

    DoSolidStuff
End Sub

If you want to handle them separately:

Private Sub IgnoreErrorAndHandleLater()
    DoSolidStuff

    On Error Resume Next
    DoErrorProneStuff1
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError
        Err.Clear
'        Exit Sub
    End If

    On Error Resume Next
    DoErrorProneStuff2
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError
        Err.Clear
'        Exit Sub
    End If

    DoSolidStuff
End Sub

Answer 2

Something like this:

Try
    ...
Catch (Exception e)
    ...
End Try

Might look like this in VBA:

' The "Try" part
On Error Resume Next
...
On Error GoTo 0
' The "Catch" part
If Err.Number <> 0 Then
...
End If

However, this form may not be following best practices.

Answer 3

Private Sub Workbook_Open()
    on error goto Oops
    version = "1.0"

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "<WEB SERVICE>"

    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send ("version=" + version)
    exit sub
Oops:
    'handle error here
End Sub   

If you wanted to, for example, change the URL because of the error, you can do this

Private Sub Workbook_Open()
    on error goto Oops
    version = "1.0"

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "<WEB SERVICE>"
Send:
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send ("version=" + version)
    exit sub
Oops:
    'handle error here
    URL="new URL"
    resume Send 'risk of endless loop if the new URL is also bad
End Sub   

Also, if your feeling really try/catchy, you can emulate that like this.

Private Sub Workbook_Open()
    version = "1.0"

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "<WEB SERVICE>"
    on error resume next 'be very careful with this, it ignores all errors
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send ("version=" + version)
   if err <> 0 then
      'not 0 means it errored, handle it here
      err.clear 'keep in mind this doesn't reset the error handler, any code after this will still ignore errors
   end if
End Sub  

So extending this to be really hard core...

Private Sub Workbook_Open()
    version = "1.0"
    on error resume next
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    if err <> 0 then
        'unable to create object, give up
        err.clear
        exit sub
    end if
    URL = "<WEB SERVICE>"
    objHTTP.Open "POST", URL, False
    if err <> 0 then
        'unable to open request, give up
        err.clear
        exit sub
    end if
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send ("version=" + version)
   if err <> 0 then
      'unable to send request, give up
      err.clear
      exit sub
   end if
End Sub  

Also worth noting that any errors that happen in an on error goto style will not be handled, so if you did this

private sub MakeError()
   dim iTemp as integer
   on error goto Oops
   iTemp = 5 / 0 'divide by 0 error
   exit sub
Oops:
   itemp = 4 / 0 'unhandled exception, divide by 0 error
end sub

Will cause an unhandled exception, however

private sub MakeError()
   dim iTemp as integer
   on error resume next
   iTemp = 5 / 0 'divide by 0 error
   if err <> 0 then
       err.clear
       iTemp = 4 / 0 'divide by 0 error, but still ignored
       if err <> 0 then
           'another error
       end if
   end if
end sub

Will not cause any exceptions, since VBA ignored them all.