12.14 Error Handling with On Error

By Ravindra Bagale Last updated: 25 September 2026

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

On Error Resume Next macro chya suruvatila lihun sodun dene – hi khup students chi dhokadayak savay aahe; sagle errors gapchup lapatat aani chukicha result yeto. Resume Next fakt ekhadya line sathi vapra aani lagech On Error GoTo 0 liha. Sagalya macros madhe cleanup (ScreenUpdating = True) error handler madhun pan hoil ase liha.

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.