Understanding Excel VBA Error Handling

Question

I got code below. I am trying to learn about error handling in excel VBA.

Sub LoopErrorHandling()
    Dim ws As Worksheet
    Dim c  As Range

    On Error GoTo LoopErrorHandling_Err

    Set ws = ThisWorkbook.Worksheets(1)

    ws.Range("C1:C5").ClearContents
    For Each c In ws.Range("A1:A5").Cells
        c.Offset(0, 2).Value = c.Value / c.Offset(0, 1).Value
    Next c

LoopErrorHandling_Exit:
    On Error Resume Next
    Set ws = Nothing
    On Error GoTo 0
    Exit Sub

LoopErrorHandling_Err:
    MsgBox Err.Description
    Resume Next
    Resume LoopErrorHandling_Exit

End Sub

I want to understant the following in the above code.

Should line Set ws = Nothing be coming after or before the line LoopErrorHandling_Exit:.
Shouldn't line LoopErrorHandling_Err: be enough, is LoopErrorHandling_Exit: necessary.
What is the work of line LoopErrorHandling_Exit: in above code and does it triggers only if Error occurs.
Does above code covers everything what error handling needs in excel vba or is there stuff missing.

`Set ws = Nothing` shouldn't really be there at all..`LoopErrorHandling_Exit` is neccessary in this snippet (*because there is no `Exit Sub` anywhere before the labels*). `LoopErrorHandling_Exit:` is the `label` to which code will jump when the `LoopErrorHandling_Exit` is triggered or when normal code execution reaches it which is after the for each loop because there is not `Exit Sub` — , Oct 20 '14 at 09:41
you should definitely see [THIS ARTICLE](http://www.cpearson.com/excel/errorhandling.htm) — , Oct 20 '14 at 09:45
The `Resume Next` statement would make the `Resume LoopErrorHandling_Exit` line pointless as it will never execute. — Rory, Oct 20 '14 at 10:15

score 6 · Answer 1 · edited May 23 '17 at 10:29

Should line Set ws = Nothing be coming after or before the line LoopErrorHandling_Exit:

Since you are working from within Excel, that line is not necessary as Excel will clean up objects. However it is a good practice to clean up objects. I call it flushing the toilet after use :P This way when you work with other applications from Excel, you will by default remember doing it :)

BTW, it should come after LoopErrorHandling_Exit: so that when the code meets an error, the LoopErrorHandling_Exit: will take care of it. Forget about Set ws = Nothing, you can reset other events in that section. I have included a link in the later part of the post which demonstrates that.

Shouldn't line LoopErrorHandling_Err: be enough, is LoopErrorHandling_Exit: necessary. What is the work of line LoopErrorHandling_Exit: in above code and does it triggers only if Error occurs.

Yes that is required. You don't want the MsgBox Err.Description running under normal code execution. The Resume statement takes care of that and it resumes execution at the relevant point in the code. It also helps you reset any specific events. For example, you may want to see THIS LINK

enter image description here

Does above code covers everything what error handling needs in excel vba or is there stuff missing.

I usually add ERL to error handling so that I can know which line is giving the error. For example

Sub Sample()
10  On Error GoTo Whoa

Dim i As Long

20  i = "Sid"

LetsContinue:
30  Exit Sub
Whoa:
40  MsgBox Err.Description & " on line " & Erl
50  Resume LetsContinue
End Sub

Thanks a lot Siddarth for explanation. Fantastic diagram. – kami Oct 20 '14 at 14:07 — kami, Oct 20 '14 at 14:07

Understanding Excel VBA Error Handling

1 Answers1