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

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.

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.