VBA error handler doesn't detect error within error handler

Question

I've been using this ultra-helpful forum for awhile, always found answers to my questions. You are the best!

But this time I can't seem to find a solution to what should be a simple case. Tried several suggestions but doesn't seem to work...

So, I'd like to download two reports from another software using the GUI. But sometimes, Report1 or/and Report2 doesn't exist.

Sub Report_download()
    On Error Goto RP1_err
        'GUI codes to download Report(1)

    On Error Goto RP2_err
        'GUI codes to download Report(2)

    MsgBox "Both Reports downloaded."

    Exit Sub

RP1_err:
        If MsgBox("Report(1) not found. Proceed to Report(2) download?", 
vbYesNo) = vbNo Then Exit Sub
        On Error Resume Next
            'GUI codes to download Report(2)
        If Err.Number > 0 Then
        MsgBox "Neither Report(1) nor Report(2) Found"
        End If
    Exit Sub


RP2_err:
    MsgBox "Report(1) downloaded, Report(2) not found. Review manually."

    Exit Sub

End Sub

When I run this for a case where neither Report(1) nor Report(2) exist, an error occurs in the "GUI codes to download Report(2)" within RP1_err error handler (as it should), after I press "yes." However, what follows is, instead of showing the message "Neither Report(1) nor Report(2) found," a debugging dialogue box appears. What am I doing wrong?

Appreciate your help!

You need to clear the active exception (error state) with a Resume statement (On Error Resume Next doesn't count). (Technically you can use On Error Goto -1 to clear it, but that's generally a sign of bad design in my experience. — Rory
– Rory, Commented Feb 26, 2019 at 9:31
This is one of those case where "such" error handling should be avoided. Use Boolean variables. Much easier to handle — Siddharth Rout
– Siddharth Rout, Commented Feb 26, 2019 at 9:32
If you share exact codes for GUI codes to download Report(1) and GUI codes to download Report(2) then perhaps I can demonstrate on the use of boolean variables — Siddharth Rout
– Siddharth Rout, Commented Feb 26, 2019 at 9:33
The best would be to reorganize your code and use two Functions (called by a main function) to download each report. They could focus on handling one kind of error and return an error code or a Boolean if the download was successful, while the main function could evaluate what to answer to the user. — z32a7ul
– z32a7ul, Commented Feb 26, 2019 at 9:38
@SiddharthRout There are too many lines to check to censor data so I can't but thank you nonetheless! — Michael
– Michael, Commented Feb 26, 2019 at 12:28

z32a7ul · Accepted Answer · 2019-02-26 13:36:59Z

I would refactor your code something like this:

Option Explicit

Sub Report_download()
    Dim blnSuccess1 As Boolean: blnSuccess1 = DownloadReport1
    Dim blnSuccess2 As Boolean: blnSuccess2 = DownloadReport2

    If blnSuccess1 = False And blnSuccess2 = False Then
        Debug.Print "Both reports failed to download"
    ElseIf blnSuccess1 = False And blnSuccess2 = True Then
        Debug.Print "Report 1 failed to download"
    ElseIf blnSuccess1 = True And blnSuccess2 = False Then
        Debug.Print "Report 2 failed to download"
    Else
        Debug.Print "Both reports successfully downloaded"
        ' Process the results
    End If
End Sub

Function DownloadReport1() As Boolean
    On Error GoTo ErrorHandler
    ' Your code to do the actual download 1, which may cause error
    On Error GoTo 0
    DownloadReport1 = True
    Exit Function
ErrorHandler:
    DownloadReport1 = False
End Function

Function DownloadReport2() As Boolean
    On Error GoTo ErrorHandler
    ' Your code to do the actual download 2, which may cause error
    On Error GoTo 0
    DownloadReport2 = True
    Exit Function
ErrorHandler:
    DownloadReport2 = False
End Function

This way, it's easier to understand the error handling because you have to focus on one problem only in each function.

Moreover, debugging is easier because you may step over DownloadReport1 or DownloadReport2.

Further, it's more flexible: you can remove report 1 and 2 or add report 3 much easier.

And later, you may have functions for report 1, 2 and 3. And sub to download report 1 and 2, and another to download 1 and 3. In this case you can avoid redundancy (coding twice to download report 1).

Thank you! I haven't tried this method, but it got me to explore into scalability in error handling! :)

Michael · Accepted Answer · 2019-02-26 12:24:55Z

Thank you for all the comments! Based on your suggestions, I came up with a quick fix using

on Error goto -1

which worked fine for my case. Then I went to study how Boolean works and came up with a solution. (although, I believe my codes to be rather disgusting...)

Quick fix:

Sub Report_download()
    On Error Goto RP1_err
        'GUI codes to download Report(1)

    On Error Goto RP2_err
        'GUI codes to download Report(2)

        MsgBox "FVD reports downloaded."

        Exit Sub

RP1_err:
    AppActivate Application.Caption
    DoEvents
    If MsgBox("RP1 not found. Proceed to RP2?", vbYesNo) = vbNo Then Exit Sub
        On Error GoTo -1
        On Error GoTo VDC_err
        'GUI codes to download Report(2)

    Exit Sub

Both_err:
    AppActivate Application.Caption
    DoEvents
    MsgBox "No VDC report saved."

    Exit Sub

RP2_err:
    AppActivate Application.Caption
    DoEvents
    MsgBox "RP1 saved. RP2 not saved."

    Exit Sub

End Sub

Boolean

Sub Test_RP_DL_Boolean()
Dim RP1_state As Boolean, RP2_state As Boolean

On Error Resume Next
       'codes to download Report(1)
        If Err.Number > 0 Then
        RP1_state = False
        Else
        RP1_state = True
        End If
        On Error GoTo 0

On Error Resume Next
        'codes to download Report(2)
        If Err.Number > 0 Then
        RP2_state = False
        Else
        RP2_state = True
        End If
        On Error GoTo 0

If ((RP1_state = True) And (RP2_state = False)) Then
MsgBox "RP1 saved. RP2 not saved"
End If

If ((RP1_state = False) And (RP2_state = True)) Then
MsgBox "RP1 not saved. RP2 saved."
End If

If ((RP1_state = False) And (RP2_state = False)) Then
MsgBox "No report found"
End If

If ((RP1_state = True) And (RP2_state = True)) Then
MsgBox "RP1 and RP2 saved."
End If


End Sub

Not sure if this is what you meant by "use Boolean," regardless, it was a learning experience. Thank you!

Also, I couldn't share my exact codes for the GUI, since they are very long and have some sensitive data, I would need to go through each line to censor them out. My apologies!

Collectives™ on Stack Overflow

VBA error handler doesn't detect error within error handler

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related