VBA Error Handling Not working

Question

I am writing a vba program to refresh power queries for a number of reports based on a user saying which reports to refresh via a form with checkboxes.

This part of the code refreshes the power query and I want it to capture the error if the refresh isn't successful. It will update a control table "Not Updated" if it wasn't updated and then I want it to resume next.

For some reason the "On Error Goto Error" isn't triggering the error line. It is still throwing the error and stopping the code run.

Any help would be greatly appreciated!

For Each cell In 
wsConfig.ListObjects("tblReportstoRun").ListColumns(2).DataBodyRange
    If cell.Value = True Then
        cell.Offset(, 1).Value = Now()
        cell.Offset(, 2).Value = frmSetting.tbStartDate
        cell.Offset(, 3).Value = frmSetting.tbEnddate

        strCurrWS = cell.Offset(0, -1)
        ThisWorkbook.Sheets(strCurrWS).Activate
        Application.StatusBar = "Updating tab " & strCurrWS

            For Each qt In ThisWorkbook.Sheets(strCurrWS).QueryTables
                qt.Refresh BackgroundQuery:=False
            Next qt

            For Each lo In ThisWorkbook.Sheets(strCurrWS).ListObjects
                On Error GoTo Error
                lo.QueryTable.Refresh BackgroundQuery:=False
            Next lo
    Else
 Error:
    cell.Offset(, 4).Value = "Not Updated"
    If InStr(Err.Description, "Permission Error") Then
    cell.Offset(, 6).Value = "Permission Error. Check Credentials"
    Err.Clear
    End If


    End If
Next cell

Set qt = Nothing
Set wks = Nothing

Put the Error: at the end of the sub and make sure to use Exit Sub just before that statement. You shouldn't put it in an IFTHEN statement — Maldred
– Maldred, Commented Dec 7, 2017 at 18:17
Also, the Error variable is used already, I'd suggest renaming it to something like Errhandle — Maldred
– Maldred, Commented Dec 7, 2017 at 18:19

Sobigen · Accepted Answer · 2017-12-07 18:27:27Z

2

I think you want to avoid having the error handling as part of the normal flow. I'm not sure of the logic you want but if you want to resume back in the For Each lo... loop replaced Resume Top with Resume Next

Sub a()

    For Each cell In wsConfig.ListObjects("tblReportstoRun").ListColumns(2).DataBodyRange
Top:

        If cell.Value = True Then
            cell.Offset(, 1).Value = Now()
            cell.Offset(, 2).Value = frmSetting.tbStartDate
            cell.Offset(, 3).Value = frmSetting.tbEnddate

            strCurrWS = cell.Offset(0, -1)
            ThisWorkbook.Sheets(strCurrWS).Activate
            Application.StatusBar = "Updating tab " & strCurrWS

                For Each qt In ThisWorkbook.Sheets(strCurrWS).QueryTables
                    qt.Refresh BackgroundQuery:=False
                Next qt

                For Each lo In ThisWorkbook.Sheets(strCurrWS).ListObjects
                    On Error GoTo ErrorCatch
                    lo.QueryTable.Refresh BackgroundQuery:=False
                Next lo
        Else
            cell.Offset(, 4).Value = "Not Updated"
        End If
    Next cell

    Set qt = Nothing
    Set wks = Nothing
    Exit Sub
ErrorCatch:
    cell.Offset(, 4).Value = "Not Updated"
    If InStr(Err.Description, "Permission Error") Then
        cell.Offset(, 6).Value = "Permission Error. Check Credentials"
    End If
    Resume Top
End Sub

answered Dec 7, 2017 at 18:27

Sobigen

2,17916 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maldred Over a year ago

Could you not use Resume Next after the error handle to return to the rest of the code?

Sobigen Over a year ago

@Maldred Yes, I put a note at the top for that. I wasn't sure exactly what logic SanomaJean wanted. If you wanted to resume at the For Each lo ... loop Resume Next would be the way to go.

Maldred · Accepted Answer · 2017-12-07 18:27:38Z

1

You should have your Errorhandler as the end of your code, and the handler should never be named "Error"

Try...

For Each cell In 
wsConfig.ListObjects("tblReportstoRun").ListColumns(2).DataBodyRange
    If cell.Value = True Then
        cell.Offset(, 1).Value = Now()
        cell.Offset(, 2).Value = frmSetting.tbStartDate
        cell.Offset(, 3).Value = frmSetting.tbEnddate

        strCurrWS = cell.Offset(0, -1)
        ThisWorkbook.Sheets(strCurrWS).Activate
        Application.StatusBar = "Updating tab " & strCurrWS

            For Each qt In ThisWorkbook.Sheets(strCurrWS).QueryTables
                qt.Refresh BackgroundQuery:=False
            Next qt

            For Each lo In ThisWorkbook.Sheets(strCurrWS).ListObjects
                On Error GoTo Errorhandle
                lo.QueryTable.Refresh BackgroundQuery:=False
            Next lo
Next cell

Set qt = Nothing
Set wks = Nothing

Exit Sub

Errorhandle:
    cell.Offset(, 4).Value = "Not Updated"
    If InStr(Err.Description, "Permission Error") Then
        cell.Offset(, 6).Value = "Permission Error. Check Credentials"
        Err.Clear
    End If

edited Dec 7, 2017 at 18:27

answered Dec 7, 2017 at 18:24

Maldred

1,1144 gold badges12 silver badges35 bronze badges

5 Comments

SanomaJean Over a year ago

Question on this, if there is no error does this code not get run through still at the end of the code?

Maldred Over a year ago

@SanomaJean My appologies! I missed the addition of Exit Sub, please refer to edit

SanomaJean Over a year ago

Ohh just saw your comment on the other post ! Thank you that worked!

Maldred Over a year ago

@SanomaJean Glad I could help! :) If this answered your question please mark as answered

Maldred Over a year ago

@SanomaJean Sobigen, proved a valid point. If you want to continue your code after the error I suggest using his method; as mine will simple quit out once it receives the error.

Collectives™ on Stack Overflow

VBA Error Handling Not working

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related