Ravindra BagaleCourses & study guides मराठी Track your progress

12. Macros and VBA

12.14 Error Handling with On Error

By Last updated: 

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

About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Aamir Khan) are made up for this lesson on error handling with on error.

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.

Worked example — error handling with on error

Why

Priya Deshmukh in Pune and Carlos Rivera abroad both need a clear, repeatable way to use error handling with on error on a Maple Bakery Co sheet without breaking totals.

What

Error Handling With On Error is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for error handling with on error.
  2. Enter a small made-up value for Priya Deshmukh using € where money appears.
  3. Add a second value for Carlos Rivera using ₹ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for Maple Bakery Co's sample row.
Meaning: Once error handling with on error is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of error handling with on error on a blank sheet for NovaCraft Tools.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Aarti Kulkarni and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for James O'Neill.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply error handling with on error to a 5-row made-up NovaCraft Tools table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with error handling with on error.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as NovaCraftTo_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
  2. Late orders (Easy): How many rows have Status "Late"?
  3. Total amount (Easy): Add up the Amount column.
  4. Worst delay (Easy): What is the largest number in the Days late column?
  5. Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
  6. Customer for an order (Medium): Find the Customer for Order ID "DL-704".
  7. Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
  8. Big orders (Medium): How many rows have an Amount of 2000 or more?
  9. Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
  10. Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
  11. Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
  12. Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Error Handling With On Error?
Card 1: What is Error Handling With On Error? · Download PNG
Revision card 2: Tiny example — Error Handling With On Error
Card 2: Tiny example — Error Handling With On Error · Download PNG
Revision card 3: Checklist — Error Handling With On Error
Card 3: Checklist — Error Handling With On Error · Download PNG