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?

I posted answer below. But also, as a side note. Your code is either using global variables when it shouldn't. Or you forgot to declare your variables. VBA will allow you to adhoc use variables, but its difficult to maintain if you do that. Add to the beginning of your code, right after version dim URL as string dim objHTTP as object — Trevor
– Trevor, Commented Jun 19, 2017 at 20:40

Trevor · Accepted Answer · 2017-06-19 20:57:00Z

72

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.

edited Jun 19, 2017 at 20:57

answered Jun 19, 2017 at 19:57

Trevor

2,9811 gold badge37 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Trevor Over a year ago

Stretching my memory here but I think that's a no because it doesn't cross the on error command again. So the error handler is effectively disabled. It will throw uncaught again.

A.S.H Over a year ago

You're not completely wrong, but as soon as the Resume statement is invoked in any form, error history is cleared and the process restarts all over :). Now in the new form of the answer, without the resume keyword and the potential infinite loop, that's a +1 (excluding the version with Resume though).

Trevor Over a year ago

I guess we could add on error goto 0 in the error handler. But this is when goto's get hairy and it's one of the reasons we hate them lol.

A.S.H Over a year ago

if you're willing to handle it that way you can possibly track the number of errors with a counter and break the cycle when it reaches some value. It's kinda defining a maximum number of attempts :)

Trevor Over a year ago

Certainly. I was just giving an example of error handling. But I added a comment with a warning about endless loops.

Sevenfold · Accepted Answer · 2018-07-10 22:33:29Z

9

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.

answered Jul 10, 2018 at 22:33

Sevenfold

911 silver badge3 bronze badges

1 Comment

Trevor Over a year ago

Resume next would mean it tries to run all the code in the block before checking for an error. If your careful, this could be ok. But you could only safely have one operation in the block. If for example you read a file, appended, and wrote it back. But the file read errors, you would attempt to append to nothing, and write back with only your appended results, erasing the original. So both file operations need to be in their own block for this method to work. Just something to watch out for.

Rsge · Accepted Answer · 2025-05-20 07:29:12Z

5

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 in VBA (& VB6?). You should never use this kind of hack in a modern programming language.)

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

edited May 20 at 7:29

answered Mar 8, 2023 at 11:44

Rsge

1232 silver badges11 bronze badges

6 Comments

GSerg Over a year ago

I added this method - you really shouldn't have. One of the most confusing and unnecessary pieces of VBA.

Rsge Over a year ago

@GSerg I accept your judgement. I like it because of it's structural closeness to the "real" try-catch in VB.NET: Try-block, directly followed by a catch-block, then continuing, while still working very similarly to a try-catch in VB.NET, with immediate error handling. I find the last one too verbose for my tastes. But you are of course entitled to your opinion, too, so can you tell me what exactly you don't like about it compared to the alternatives and which alternative you think is best, so I may improve in the future?

Trevor Over a year ago

Your first example, If False then, I am not a fan. But the rest is fine. To anyone reading this though, error handlers are for handling exceptions, they are not for flow control. Handle your errors before they become errors. Check a file exists before trying to read from it. My original example was calling black box functions. But in your example, you're calling your own subs. Better for your subs to handle errors and return true/false for the main line to handle flow.

Rsge Over a year ago

Thank you for the explanation. My functions are of course just meant as an example : )

Trevor Over a year ago

if False will error virtually every modern linter for being an unreachable statement in any other langue and is a bad habit to be in for vba, Why do that when a simple exit sub will do? Or optionally check if an error happened instead? Does what you wrote work. Sure. But like I said, I am not a fan of relying on an unreachable code statement just to block off your error handler

|

Collectives™ on Stack Overflow

VBA + Excel + Try Catch

3 Answers 3

5 Comments

1 Comment

Handle error immediately in-place and resume after:

Handle error immediately at bottom and exit sub:

Handle error immediately at bottom and resume at some point:

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

If you want to handle them separately:

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Handle error immediately in-place and resume after:

Handle error immediately at bottom and exit sub:

Handle error immediately at bottom and resume at some point:

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

If you want to handle them separately:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related