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.
Set ws = Nothing
be coming after or before the line
LoopErrorHandling_Exit:
.LoopErrorHandling_Err:
be enough, is
LoopErrorHandling_Exit:
necessary.LoopErrorHandling_Exit:
in above code and
does it triggers only if Error occurs.On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.
The Visual Basic error handling model allows programmers to perform special actions when an error occurs, such as jumping to a particular line of code. When an exception occurs in the Active Expert, the standard Visual Basic error handling works as expected.
VBA enables an error-handling routine and can also be used to disable an error-handling routine. Without an On Error statement, any run-time error that occurs is fatal: an error message is displayed, and the execution stops abruptly.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With