VBA + Excel + Try Catch

Question

In VBA, I make a simple script that writes the version of the spreadsheet 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     

Search for work processes.

I am trying to try to catch, so if the web host is disconnected, instead of showing a runtime error, I will catch and suppress it.

What is the best way to try to catch in VBA so that the error message does not display?

Answer 1

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 want to, for example, change the URL due to an 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 is really trying / catchy, you can emulate it 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, expanding it to be a really solid 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  

, , on error goto, , ,

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

,

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

, VBA .