Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Error Handling in VBA

Learn how to identify runtime errors, handle unexpected situations, use On Error statements, and create reliable Excel automation macros.


1. Introduction to Error Handling

An error occurs when VBA cannot execute an operation as expected. For example, a macro may try to access a worksheet that does not exist, divide a number by zero, or convert invalid text into a number.

Error handling allows a program to respond to certain errors, display useful messages, clean up resources, and exit gracefully instead of stopping unexpectedly.

Without Error Handling

An unhandled runtime error interrupts execution and may leave the user unsure what went wrong.

With Error Handling

The macro can report the problem, restore settings, and stop or continue according to the chosen strategy.

Learning objective: Understand VBA error-handling statements and apply them to practical Excel automation tasks.

2. Types of Errors in VBA

Error Type Description Example
Syntax Error Code violates VBA syntax rules. Missing End If.
Compile Error VBA cannot compile the procedure. Calling an undeclared procedure incorrectly.
Runtime Error An error occurs while the code is executing. Accessing an invalid object or worksheet.
Logical Error The code executes but produces an incorrect result. Calculating a discount using the wrong percentage.
Remember: On Error handles runtime errors. It does not fix syntax, compilation, or logical errors automatically.

3. How Error Handling Works

A procedure executes its normal instructions until an error occurs. If an appropriate handler is active, VBA transfers control to that handler.

Start Procedure Execute VBA Instructions Error occurs? Yes Error Handler Report / Recover No Continue Normal Execution Cleanup / End

Conceptual flow: the exact execution path depends on the active handler and the code's recovery logic.

4. Understanding On Error Statements

VBA provides three important forms of error handling.

A. On Error GoTo Label

Transfers control to a named error-handling section when a runtime error occurs.

Sub GoToErrorExample()

    On Error GoTo ErrorHandler

    Dim result As Double
    result = 10 / 0

    MsgBox result
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation

End Sub

Explanation: The error occurs during division. VBA transfers control to ErrorHandler. The Err object provides information about the error.

The example intentionally triggers a runtime error. Use it to understand the handler, not as a pattern for normal arithmetic. Validate a divisor before dividing in real applications.

B. On Error Resume Next

Tells VBA to continue with the next statement after a runtime error. It is useful only when the failure is expected and the result is checked immediately.

Sub ResumeNextExample()

    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Sales")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The Sales worksheet does not exist.", vbExclamation
    Else
        MsgBox "The Sales worksheet was found.", vbInformation
    End If

End Sub

Explanation: If the worksheet cannot be found, the assignment fails and ws remains Nothing. The code then checks the object reference.

Avoid leaving On Error Resume Next active across large sections of code. It can hide errors that should be fixed.

C. On Error GoTo 0

Disables the current procedure's enabled error handler and restores normal runtime error behavior. It is commonly used after a small, intentional Resume Next section.

Sub DisableErrorHandlerExample()

    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Report")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Report sheet not found."
        Exit Sub
    End If

    ws.Range("A1").Value = "Ready"

End Sub

5. Understanding the Err Object

The built-in Err object contains information about the most recent runtime error.

Property / Method Purpose
Err.Number Returns the error number.
Err.Description Returns the error description.
Err.Source Identifies the source of the error when available.
Err.Clear Clears the stored error information.
Sub DisplayErrorDetails()

    On Error GoTo ErrorHandler

    Dim result As Long
    result = CLng("ABC")

    Exit Sub

ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, vbExclamation

    Err.Clear

End Sub

The conversion of "ABC" to a number causes a runtime conversion error. The handler displays the error details.

6. Practical Example: Safe Division

Consider a macro that divides total sales by the number of employees. A zero employee count must be handled before division.

Sub CalculateAverageSales()

    Dim totalSales As Double
    Dim employeeCount As Long
    Dim averageSales As Double

    totalSales = 50000
    employeeCount = 0

    If employeeCount <= 0 Then
        MsgBox "Employee count must be greater than zero.", vbExclamation
        Exit Sub
    End If

    averageSales = totalSales / employeeCount

    MsgBox "Average Sales: " & Format$(averageSales, "0.00"), vbInformation

End Sub
Step-by-Step Explanation
  1. Declare variables for total sales and employee count.
  2. Assign the values used in the calculation.
  3. Check whether the employee count is valid.
  4. Exit the procedure if the count is zero or negative.
  5. Calculate and display the average only when valid.
Best practice: Prevent predictable errors with input validation. Use an error handler as an additional safeguard for unexpected runtime failures.

7. Error Handling with Cleanup

Some macros temporarily change Excel settings, open files, or allocate resources. A cleanup section helps restore settings even if an error occurs.

Sub SafeMacroWithCleanup()

    Dim previousScreenUpdating As Boolean
    Dim ws As Worksheet

    previousScreenUpdating = Application.ScreenUpdating

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("A1").Value = "Process completed"

CleanExit:
    Application.ScreenUpdating = previousScreenUpdating
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit

End Sub

How it works:

  1. The original screen-updating setting is saved.
  2. The macro performs its operation with screen updating disabled.
  3. If an error occurs, the handler displays the error message.
  4. Resume CleanExit transfers execution to the cleanup label.
  5. The original screen-updating setting is restored.
Keep cleanup code focused on restoring state. If a cleanup operation can itself fail, handle that possibility deliberately instead of silently ignoring all errors.

8. Raise a Custom Error

You can use Err.Raise to report a business-rule violation through VBA's normal error-handling mechanism.

Sub ValidateEmployeeSalary()

    Dim salary As Double

    On Error GoTo ErrorHandler

    salary = -5000

    If salary < 0 Then
        Err.Raise vbObjectError + 1001, _
                  "ValidateEmployeeSalary", _
                  "Salary cannot be negative."
    End If

    MsgBox "Salary is valid."
    Exit Sub

ErrorHandler:
    MsgBox "Validation failed: " & Err.Description, vbExclamation

End Sub

Explanation: vbObjectError + 1001 creates a custom error number in the user-defined error range. The handler displays the validation message.

9. Common Error-Handling Mistakes

Mistake Why It Is a Problem Better Approach
Ignoring every error Real failures may go unnoticed. Handle only expected errors and check outcomes.
Missing Exit Sub Normal execution may fall through into the handler label. Exit the procedure before the handler section.
Not restoring Excel settings Excel may remain in an unintended state. Use a cleanup label and restore saved settings.
Showing vague messages The user cannot understand what failed. Provide meaningful descriptions and relevant error details.
Using error handling for every validation Predictable input problems become harder to understand. Validate inputs before performing operations.

10. Practice Exercises

Exercise 1: Worksheet Validation

Write a macro that checks whether a worksheet named Employees exists and displays an appropriate message.

Exercise 2: Number Conversion

Accept a value from a user, validate it, convert it to a number, and handle conversion errors.

Exercise 3: File Handling

Create a macro that attempts to open a workbook and reports a useful message if the file cannot be opened.

Exercise 4: Custom Error

Raise a custom error when an employee's bonus is negative and handle it using an error-handling section.

Important Points to Remember

  • On Error GoTo transfers control to a handler.
  • On Error Resume Next continues after runtime errors.
  • On Error GoTo 0 disables the enabled handler.
  • Err.Number and Err.Description describe an error.
  • Use validation to prevent predictable errors.
  • Use cleanup sections to restore temporary application settings.
  • Do not silently suppress errors that need attention.

Summary

Error handling helps VBA programs respond to unexpected runtime problems. The main techniques include On Error GoTo, On Error Resume Next, On Error GoTo 0, and the built-in Err object.

Reliable macros combine input validation, meaningful error messages, controlled recovery, and cleanup logic to keep Excel automation predictable and maintainable.