How to use multiple error handlers in VBA

Question

I have tried to use multiple error handling in a VBA code

Sub ErrorTestMultiple()

Test1:
    On Error GoTo ErrHandler1
        y = 6 / 0
        GoTo Test2

ErrHandler1:
        Cells(4, "H") = "Test 1 failed"
        Cells(4, "I") = Err.Description


Test2:

    On Error GoTo ErrHandler2
        y = 6 / 0 ' Process stop here - Why is the 2nd Error handler not working?
        GoTo Test3

ErrHandler2:
        Cells(5, "H") = "Test 2 failed"
        Cells(5, "I") = Err.Description

Test3:

End Sub

I tried adding resume following example in here but it didn't work iter

How can I make it work?

Storax · Accepted Answer · 2020-03-28 11:50:09Z

You need to reset the error handler with On Errro Goto -1 beforehand, i.e.

Sub ErrorTestMultiple()

Test1:
    On Error GoTo ErrHandler1
        y = 6 / 0
        GoTo Test2

ErrHandler1:
        Cells(4, "H") = "Test 1 failed"
        Cells(4, "I") = Err.Description


Test2:

    On Error GoTo -1
    On Error GoTo ErrHandler2

        y = 6 / 0 ' Process stop here - Why is the 2nd Error handler not working?
        GoTo Test3

ErrHandler2:
        Cells(5, "H") = "Test 2 failed"
        Cells(5, "I") = Err.Description

Test3:

End Sub

But this is not good practise. Furhter reading here

PS You could re-structure the above code like that

Sub ErrorTestMultiple()

    Dim y As Double

    On Error GoTo ErrorHandler

    y = 6 / 0
    y = 6 / 0

    Exit Sub

ErrorHandler:

    Dim j As Long
    j = j + 1

    Cells(3 + j, "H") = "Test " & j & " failed"
    Cells(3 + j, "I") = Err.Description

    Resume Next

End Sub

Shivang Gupta · Accepted Answer · 2020-03-28 11:13:12Z

0

Sub ErrorTestMultiple()

Test1:
    On Error GoTo ErrHandler1
        y = 6 / 0
    on error goto 0
        GoTo Test2

ErrHandler1:
        Cells(4, "H") = "Test 1 failed"
        Cells(4, "I") = Err.Description

         Resume next

Test2:

    On Error GoTo ErrHandler2
        y = 6 / 0
    on error goto 0
        GoTo Test3

ErrHandler2:
        Cells(5, "H") = "Test 2 failed"
        Cells(5, "I") = Err.Description

         Resume next
Test3:

End Sub

answered Mar 28, 2020 at 11:13

Shivang Gupta

3474 silver badges11 bronze badges

Comments

BLitE.exe · Accepted Answer · 2020-03-28 11:18:29Z

0

As suggested of Storax.

One Error Handler is applied per Sub Routine. Thus, the Error handling will only apply based on the first instance of the Error call.

So in order to handle multiple Error Calls. To start, try to break your subroutine into smaller routine.

Or hence, the main rule of Error Handling if you know what will happen or the expected behaviour of your program no need to handle it during "Run-time".

A proper code structuring most of the time may do the trick.

answered Mar 28, 2020 at 11:18

BLitE.exe

3211 gold badge2 silver badges20 bronze badges

Comments

freeflow · Accepted Answer · 2020-03-28 15:47:11Z

You can have mutiple error handlers in a procedure. The trick is to avoid the common example of Goto Label. Instead you should use a combination of On Error Resume Next and On Error Goto O to emulate a try catch block.

Sub ErrorTestMultiple()

    ' Test 1
    On Error Resume Next
    Y = 6 / 0
    If Err.Number <> 0 Then
        On Error GoTo 0
        Cells(4, "H") = "Test 1 failed"
        Cells(4, "I") = Err.Description
        Exit Sub

    End If
    On Error GoTo 0
    ' Test 2
    On Error Resume Next
    Y = 6 / 0 
    If Err.Number <> 0 Then
        On Error GoTo 0
        Cells(5, "H") = "Test 2 failed"
        Cells(5, "I") = Err.Description
        Exit Sub
    End If
    On Error GoTo 0

End Sub

Some good reading here on alternative strategies to Goto ErrorHandler

https://rubberduckvba.wordpress.com/2019/05/

Collectives™ on Stack Overflow

How to use multiple error handlers in VBA

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related