CODEWITHSUNDEEP
excelvbaerror-handling
Michael
Michael

Reputation: 33

VBA error handler doesn't detect error within error handler

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!

Upvotes: 0

Views: 97

Answers (2)

z32a7ul
z32a7ul

Reputation: 3777

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).

Upvotes: 1

Michael
Michael

Reputation: 33

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!

Upvotes: 0

Related Questions