Error handling in VBA - on error resume next

Question

I have the following code:

ErrNr = 0
For Rw = StRw To LsRw 'ToDo speed up with fromrow torow
    If Len(ThisWorkbook.Sheets(TsSh).Cells(Rw, TsCl)) = 0 Then
        ThisWorkbook.Sheets(TsSh).Cells(Rw, TsCl).Interior.ColorIndex = 46
        ErrNr = ErrNr + 1
    End If
Next

My problem is if there is an error on the page, my code is not running after that. I think the solution should be with:

On Error Resume Next
N = 1 / 0    ' cause an error
If Err.Number <> 0 Then 
    N = 1
End If

But I don't know how to use this code.

Solution 1

It depends on what you want to do.

• On Error Resume Next will ignore the fact that the error occurred. This is a great way to get your code to execute to completion, but will just about guarantee that it won't do what you want.
• On Error Goto 0 is the default response. It will pop up the error message that VBA is generating
• On Error Goto <label> will cause your code to jump to a specified label when an error occurs and allows you to take an appropriate action based on the error code.

The last option, On Error Goto <label> is usually the most useful, and you'll want to do some digging on how to best use it for your application.

This site is where I got the details above from, and is usually the first results that comes from Googling for "excel vba on error". I've used that reference myself a number of times.

Solution 2

I generally try to avoid On Error Resume Next as it will try to continue no matter what the error (there are some cases where it's useful though).

The code below passes all errors out of the procedure where they can be handled on a case by case basis.

Sub test1()

    Dim n As Double

    On Error GoTo ERROR_HANDLER

        n = 1 / 0  ' cause an error

    On Error GoTo 0
    Exit Sub

ERROR_HANDLER:
    Select Case Err.Number
        Case 11 'Division by zero
            n = 1
            Err.Clear
            Resume Next
        Case 13 'Type mismatch

        Case Else
            'Unhandled errors.
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure test1."
            Err.Clear

    End Select

End Sub