Trying to use On Error GoTo <label> with Err.Clear

Question

I am using a loop on several instances of excel. I am using several PCs and instances to plow through my data, so each instance will grab the next available file. VBA will get an error if more than one instance is opening the same file (csv format). I want the error handling label to simply go to the next file in the loop. However, i can only get this error handling to work once. The second time around it doesn't handle the error. I left the entire code intact below in case another part of the code is causing the error handling to fail.

Sub RunRoutine()



CloseOtherWorkbook
Application.StatusBar = False
manualcalc
Calculate
ListAllFile
Calculate
Sheets("RUN").Select
Set wBRun = ActiveWorkbook
Workbooks.Open Filename:=Range("FO_CalcName_Range").Value, ReadOnly:=True
Set wBCalc = ActiveWorkbook
wBRun.Activate

For Each C In ActiveSheet.Range("FILE_RANGE_RUN").Cells
    Err.Clear
    On Error GoTo Error_handler:
    wBRun.Activate
    Sheets("RUN").Select
    C.Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate

    If ActiveCell.Value = False Then
        Application.ScreenUpdating = True
        Application.StatusBar = False
        Application.StatusBar = "Run Routine" & " - " & C
        Application.ScreenUpdating = False
        Range("Date_Range").Value = C
        ActiveSheet.Calculate
        FO_RawName = Range("FO_RawName_Range").Value
        Workbooks.Open FO_RawName, ReadOnly:=True 'this is where the code fails
        Set wBRaw = ActiveWorkbook
        wBRaw.Activate
        Columns("A:dn").Select
        Selection.Copy

        wBCalc.Activate
        Sheets("CALC").Select
        Columns("A:dn").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ResizeRows
        wBRaw.Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Close False
        wBRun.Activate
        RunallSheets
    Else
        'do nothing
    End If
Error_handler:

Next
Application.ScreenUpdating = True
wBCalc.Activate
ActiveWorkbook.Close False
Application.StatusBar = False
Application.ScreenUpdating = True
wBRun.Activate
manualcalc
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:10:00"), "RunRoutine"

End Sub

Answer 1

GOTOs are possibly to be avoided, even with errors handling too

best practice is handle error consciously, i.e. catch them when you expect them and treat appropriately

this means you have to leave your code open to any error while debugging it

for instance to catch possibile workbook opening exceptions, you may want to:

• have a specific Function to open a workbook and return :

  • True if successful, along with an object reference of opened workbook

  • False if not

  like, for instance

  Function OpenWorkbook(wbName As String, wb As Workbook) As Boolean
      On Error Resume Next
      Set wb = Workbooks.Open(wbName, ReadOnly:=True)
      OpenWorkbook = Not wb Is Nothing
  End Function
  

• use it like follows

      ... your code
      ActiveSheet.Calculate
  
      If OpenWorkbook(Range("FO_RawName_Range").Value, wBRaw) Then
          Columns("A:dn").Select '<--| this will select columns "A:DN" in wBRaw active sheet
          Selection.Copy
  
          wBCalc.Activate
          Sheets("CALC").Select
          Columns("A:dn").Select
          ActiveSheet.Paste
          Application.CutCopyMode = False
          ResizeRows
          wBRaw.Activate
          Application.CutCopyMode = False
          ActiveWorkbook.Close False
          wBRun.Activate
          RunallSheets
      End If
  

Finally you may also want to avoid Activate/Active.../Select/Selectionand use fully qualified range references to both improve code performance (switching between workbooks/worksheets is time consuming) and not to loose control of your ranges

Answer 2

Final code that handles multiple errors:

Sub RunRoutine()



CloseOtherWorkbook
Application.StatusBar = False
manualcalc
Calculate
ListAllFile
Calculate
Sheets("RUN").Select
Set wBRun = ActiveWorkbook
Workbooks.Open Filename:=Range("FO_CalcName_Range").Value, ReadOnly:=True
Set wBCalc = ActiveWorkbook
wBRun.Activate

For Each C In ActiveSheet.Range("FILE_RANGE_RUN").Cells
    On Error GoTo Error_handler:
    wBRun.Activate
    Sheets("RUN").Select
    C.Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate

    If ActiveCell.Value = False Then
        Application.ScreenUpdating = True
        Application.StatusBar = False
        Application.StatusBar = "Run Routine" & " - " & C
        Application.ScreenUpdating = False
        Range("Date_Range").Value = C
        ActiveSheet.Calculate
        FO_RawName = Range("FO_RawName_Range").Value
        Workbooks.Open FO_RawName, ReadOnly:=True
        Set wBRaw = ActiveWorkbook
        wBRaw.Activate
        Columns("A:dn").Select
        Selection.Copy

        wBCalc.Activate
        Sheets("CALC").Select
        Columns("A:dn").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ResizeRows
        wBRaw.Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Close False
        wBRun.Activate
        RunallSheets
    Else
        'do nothing
    End If
LabelA:
Next
Application.ScreenUpdating = True
wBCalc.Activate
ActiveWorkbook.Close False
Application.StatusBar = False
Application.ScreenUpdating = True
wBRun.Activate
manualcalc
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:10:00"), "RunRoutine"
Exit Sub

Error_handler:
Resume LabelA:
End Sub

Answer 3

That is not the right way to use On Error Goto.

You must use it like this:

Sub test()
 On Error GoTo Error_handler
'your code

NextItem:
Next

Application.ScreenUpdating = True
wBCalc.Activate
ActiveWorkbook.Close False
Application.StatusBar = False
Application.ScreenUpdating = True
wBRun.Activate
manualcalc
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:10:00"), "RunRoutine"
Exit Sub

Error_handler:
Resume NextItem
End Sub