CODEWITHSUNDEEP
vba
Selrac
Selrac

Reputation: 2293

How to use multiple error handlers in VBA

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?

Upvotes: 1

Views: 1782

Answers (4)

freeflow
freeflow

Reputation: 4355

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/

Upvotes: 0

Storax
Storax

Reputation: 12167

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

Upvotes: 2

BLitE.exe
BLitE.exe

Reputation: 321

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.

Upvotes: 0

Shivang Gupta
Shivang Gupta

Reputation: 347

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

Upvotes: 0

Related Questions