How can I use If Error GoTo "specific line" multiple times in the same Excel VBA sub?

Question

I have tried using If Error GoTo "blank" multiple times, so that it skips the next section of code if a sheet won't activate. It will work the first time, then the second time it won't skip past the error, just presents it as if there was no If Error command at all. The code looks like this:

On Error GoTo errorHandler1
Windows(MyFile).Activate
Sheets(secondDetailTab).Activate
'bunch of code
errorHandler1:

'bunch of code

On Error GoTo errorHandler2
Windows(MyFile).Activate
Sheets(secondDetailTab).Activate
'bunch of code
errorHandler2:

'bunch of code

If Sheets(secondDetailTab).activate has an error the first time, it will skip to errorHandler1 like it's supposed to, but if there's an error the second time then it just presents the error normally and ignores the On Error GoTo errorHandler2 command. Any suggestions would be greatly appreciated!

Answer 1

You could do it indeed in the way as you want to do it although this is not best practise to do it like that. You need to reset the error with On Error Goto -1 before you "activate" another error handler.

    On Error GoTo errorHandler1
    Windows(myFile).Activate
    Sheets(secondDetailTab).Activate
    'bunch of code
errorHandler1:

    'bunch of code
    
    On Error GoTo -1
    On Error GoTo errorHandler2
    Windows(myFile).Activate
    Sheets(secondDetailTab).Activate
    'bunch of code
errorHandler2:

    'bunch of code

Here you find a pretty good tutorial on error handling. There is also an explanation for On Error Goto -1

Answer 2

In the Remarks section of the On Error MS doc

If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error.

My suggestion is that instead of stacking error handlers, you test and avoid errors in the main code. If Sheets(secondDetailTab).Activate is predictably causing errors then first test if the sheet exists and is valid. I wrote a short function that does that:

Function SheetExists(ByRef ExcelBook As Workbook, ByVal SheetName As String) As Boolean
    Dim ws As Object
    For Each ws In ExcelBook.Sheets
        If ws.Name = SheetName Then Exit For
    Next ws
    SheetExists = Not ws Is Nothing
    'True = it Exists, False = Doesn't Exist
End Function

Sub Example()
    Const sName As String = "Sheet4"
    Dim B As Boolean
    B = SheetExists(ThisWorkbook, sName)
    MsgBox B
End Sub

You could implement this into your code by changing Sheets(secondDetailTab).Activate into If SheetExists(WB, secondDetailTab) Then Sheets(secondDetailTab).Activate

Answer 3

Error handling in VBA is primitive compared to other languages. It is much better to check for error conditions preemptively than to catch errors after they occur. Check to see if files exist before you try to open them, don't assume the function you called succeeded--check the return value. If you practice these steps consistently, you almost never need to handle errors.

When you do need to handle errors, you can be very specific about the scope of the error trap and which errors you're handling. Here is a pattern I have found myself using a lot. You wrap a single line in the error handler, then check the error code. For example, I prefer Toddleson's code above, which checks to see if a worksheet exists by enumerating the existing worksheets, but this an alternative implementation which illustrates the use of tightly focused error handling.

‘ Get worksheet by name. If the worksheet does not exist, create a new worksheet.
Function GetWorksheet(ByVal name As String) As Worksheet

    Dim ws As Worksheet
    
    ' Turn on error handling.
    On Error GoTo Error_NoSuchSheet

    Set ws = Worksheets(name)

    ' Turn off error handling.
    On Error GoTo 0
    
    ' Create new worksheet.
    If ws Is Nothing Then
        Set ws = Worksheets.Add
        ws.name = name
    End If

    Set GetWorksheet = ws
    
    Exit Function
    
Error_NoSuchSheet:
    If Err.Description = "Subscript out of range" Then
        ' Resume execution on the line following the one that threw the error.
        Resume Next
    Else
        ' Invoke the default VBA error handler.
        Err.Raise Err.Number
    End If
    

End Function

Note that only a specific error on one specific line is handled. An error on any other line will receive the default error handling (VBA pops up a dialog box). The same thing if any other error occurs on that one line. This pattern provides a much more finely granulated control of error conditions. This is not necessarily the best way to handle an error in every situation and it is far from the only way, but I think it has significant advantages over the pattern you described.

Of course any error handling, or no error handling at all, is significantly better than this.

Function IgnoreAllErors()

    ' Ignore all errors.
    On Error Resume Next

    ' Your code here
    
End Function

Please don't do this.