12.14 Error Handling with On Error
Sub SafeCityReport()
Dim ws As Worksheet
Dim city As String
Dim sales As Double, orders As Long
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Orders") ' error 9 if the sheet is missing
city = InputBox("City name:", "City report", "Pune")
If city = "" Then GoTo CleanExit
orders = Application.WorksheetFunction.CountIfs(ws.Range("C:C"), city)
sales = Application.WorksheetFunction.SumIfs(ws.Range("G:G"), ws.Range("C:C"), city)
MsgBox city & ": sales Rs " & Format(sales, "#,##0") & ", AOV Rs " & Format(sales / orders, "0.00")
CleanExit:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "SafeCityReport"
Resume CleanExit
End Sub
If the city has no orders, sales / orders becomes 0 ÷ 0, which raises a run-time error (VBA reports error 6, Overflow; a non-zero number ÷ 0 gives error 11, Division by zero). The handler shows a clear message instead of crashing – though it is even better to check If orders = 0 Then … before dividing. Note that the VBA Editor cannot display the ₹ symbol in string literals, so the code writes "Rs" in messages and uses ChrW(8377) when it needs ₹ (as in 12.12 A).
| Statement | Meaning |
|---|---|
On Error GoTo Label |
Jump to the handler on error |
On Error Resume Next |
Ignore errors (use only for one risky line, then switch off) |
On Error GoTo 0 |
Turn error handling off again |
Err.Number, Err.Description |
Details of the error |
Resume Label |
Continue at a label after handling |
Ravindra Bagale's Tip
Writing On Error Resume Next at the start of a macro and leaving it there is a dangerous habit of many students; all errors get silently hidden and the result is wrong. Use Resume Next only for a single line and immediately write On Error GoTo 0. In every macro, write the cleanup (ScreenUpdating = True) so that it also runs from the error handler.
Ravindra Bagale's Tip – मराठी
On Error Resume Next macro च्या सुरुवातीला लिहून सोडून देणं – ही बऱ्याच students ची धोकादायक सवय आहे; सगळे errors गपचूप लपतात आणि चुकीचा result येतो. Resume Next फक्त एखाद्या line साठी वापरा आणि लगेच On Error GoTo 0 लिहा. सगळ्या macros मध्ये cleanup (ScreenUpdating = True) error handler मधूनही होईल असं लिहा.
Ravindra Bagale's Tip – हिंदी
On Error Resume Next को macro की शुरुआत में लिखकर छोड़ देना – यह बहुत से students की ख़तरनाक आदत है; सारे errors चुपचाप छुप जाते हैं और result गलत आता है. Resume Next सिर्फ़ किसी एक line के लिए इस्तेमाल करो और तुरंत On Error GoTo 0 लिखो. हर macro में cleanup (ScreenUpdating = True) ऐसे लिखो कि वह error handler से भी हो.
Practice task
Add error handling to SplitByCity so that ScreenUpdating and DisplayAlerts are always restored and a friendly message appears if the Orders sheet is missing.