Trying to use On Error GoTo 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

Instead of using an error handler just use `On Error Resume Next`. Also I think the issue is you tell it to go to an error handler but then have a colon after it, thus defining the error handler I believe. Also, as an aside, it's best to [avoid using `.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Dec 04 '16 at 16:37
@BruceWayne Unfortunately, i cannot use `on error resume next` since it will blow up the spreadsheets with the subsequent code. It needs to simply go to the next C in the range. I also removed the colons, and its looking to call on a macro not the label. — RRP, Dec 04 '16 at 16:45
Glad to see you got it working. But to stress again, I highly suggest you also work to remove the uses of `.Select` and `.Activate` as that may cause unexpected results later. Also if your code is going to throw multiple errors, try to rewrite it so it throws as few as possible. — BruceWayne, Dec 04 '16 at 17:14
Yeah, i try to code stuff up with no errors. However, VBA errors when two separate instances of excel try to open the same file (only occurs if file is csv extension). But never had any issues with `.Select` or `.Activate`. What is the issue with using those commands? I probably use them in almost all of my projects. — RRP, Dec 04 '16 at 17:26
generally because if you are switching between workbooks or sheets, VBA *could* get confused (if that's a correct term). Instead it's better to qualify ranges and such with the actual workbook. See [this page](http://dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/) for example and Google around for others. Avoiding them will help "tighten" the code up and you'll become a better programmer, IMO, by doing so. Again, you may not get errors, but as your macros grow more robust it can potentially cause issues. — BruceWayne, Dec 04 '16 at 18:35

score 3 · Answer 1 · answered Dec 04 '16 at 16:52

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

answered Dec 04 '16 at 16:52

Docmarti

376
2
6

Thanks! I just solved it this way and was going to come back and answer it. I'll post my code below. – RRP Dec 04 '16 at 17:03
I have the code set to rerun every ten minutes after completion. Do I lose that with the `exit sub` towards the end? – RRP Dec 04 '16 at 17:29
I don't think so. Exit Sub is equivalent to End Sub. – Docmarti Dec 04 '16 at 20:07

score 3 · Answer 2 · answered Dec 04 '16 at 17:27

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

score 0 · Answer 3 · answered Dec 04 '16 at 17:04

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

Trying to use On Error GoTo with Err.Clear

3 Answers3