VBA If Error Try Again

Question

We have automated our reporting processes using SQL Server, Access and Excel. One of our queries however has difficulties running in the morning. Sometimes it gets a timeout error. When this happens, the whole system breaks down and we have to manually continue the processes.

I was hoping to add a VBA loop to allow the query to try again if it happens to fail.

I want the system to:

Run the query.
If it fails, wait 5 minutes and try again.
If it fails 5x in a row, stop the code.

I have written the following code but I have no way of testing it. I was hoping any of you guys could check it out and comment on wether it should work or not.

    'Counter for the errors
    ErrorCount = 0
    GoTo CheckConnection

CheckConnection:
    If ErrorCount < 6 Then
    'Try to execute the query
        db.Execute sqlq
        'If it fails, ignore the error message and go to BadConnection
        On Error GoTo BadConnection
    Else
    'If the query failed 5x, just give up and show the error message
        db.Execute sqlq
        Resume Next
        End If

BadConnection:
    'Add +1 to the counter
        ErrorCount = ErrorCount + 1
        'Allow the application to wait for 5 minutes
        Application.Wait (Now + TimeValue("0:05:00"))
        'Try the query again
        GoTo CheckConnection

score 4 · Accepted Answer · answered Aug 22 '17 at 09:32

You aren't resuming in the correct spot, it needs to be in the error-handling code:

    'Counter for the errors
    ErrorCount = 0
    GoTo CheckConnection 'This statement is pointless if the label is directly after it

CheckConnection:
    'Try to execute the query

    ' This says to go to BadConnection if an error occurs after it,
    ' not if an error occurred previously
    On Error GoTo BadConnection
    db.Execute sqlq
    ' Start allowing errors to crash Excel again
    On Error GoTo 0
    'Query worked - continue processing
    '....

    '...
    Exit Sub

'Error handling code    
BadConnection:
    ' Start allowing errors to crash Excel again
    On Error GoTo 0
    If ErrorCount = 5 Then
        'If the query failed 5x, just give up and show the error message
        MsgBox "Giving up"
        Exit Sub
    End If
    'Add +1 to the counter
    ErrorCount = ErrorCount + 1
    'Allow the application to wait for 5 minutes
    Application.Wait (Now + TimeValue("0:05:00"))
    'Try the query again by Resuming at CheckConnection
    Resume CheckConnection

Thank you, I tested it with a basic divide by 0 error in Excel and it works like a charm. My VBA is not the strongest so this definitely helped. — TheNiers, Aug 22 '17 at 10:53

Vityata · Answer 2 · 2017-08-22T09:39:11.983

This is something that can work out using Recursion and an Optional parameter:

Option Explicit

Public Sub TestMe(Optional errorCount As Long = 0)

    On Error GoTo TestMe_Error

    'Your code just to test it, make an error
    Debug.Print errorCount / 0

    On Error GoTo 0
    Exit Sub

TestMe_Error:

    Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMe"
    errorCount = errorCount + 1

    Select Case errorCount

        Case 1, 2, 3
            Application.Wait Now + #12:05:00 AM#
            Call TestMe(errorCount)

        Case Else   'The 5th time it exits
            Exit Sub

    End Select

End Sub

Recursion is used, to rerun the code. The times the code is rerun is saved in the parameter errorCount. Thus, the 5. time it exits.

In general, avoid GoTo and use it only for error handling. GOTO still considered harmful?

I wouldn't use recursion here. A simple `wait` and `resume` seem to me simpler. — iDevlop, Aug 22 '17 at 09:42
@PatrickHonorez - with wait & resume the code becomes a bit spaghetti to me. But if it is really a small piece of up to 100 lines anything is ok. — Vityata, Aug 22 '17 at 09:45

VBA If Error Try Again

2 Answers2