Skip to content

Excel VBA Error Trapping and Handling

    Run-time Errors

    When your code encounters a run-time error it will display a nasty warning to your users.  You want to try if at all possible to stop these error warnings appearing.

    For the database shown below I need a procedure to calculate two things:

    1. The number of years a product has been stocked – a valid date first stocked is needed

    2. The price including VAT – a valid unit price is needed

    There is missing data for both of these calculations.  We could deal with this problem using an IF structure but for the purpose of demonstration we are going to use error handlers.

    This is the code as it stands with no error handling included.

    Sub NoErrorHandling()
    Const VAT As Single = 0.2
    Dim rg As Range, ProductField As Range
    Dim PriceIncVAT As Currency
    Dim MissingUnitPrice As Single
    Dim MissingDateFirstStocked As Date
    Set ProductField = Range("A2", Range("A2").End(xlDown))
    
    'Calculate the unit price inc VAT
    For Each rg In ProductField
        With rg.Offset(0, 4)
            .Value = rg.Offset(0, 3) * (1 + VAT)
            .NumberFormat = "£#,##0.00"
        End With
    Next rg
    
    'Calculate the number of years the product has been kept in stock
    For Each rg In ProductField
        rg.Offset(0, 2) = Format((Date - rg.Offset(0, 1)) / 365.25, "0.0")
    Next rg
    
    End Sub
    

    Pretty soon it encounters an error. Product 5 does not have a valid Unit Price.

    On Error Resume Next

    One way of avoiding the Run-time errors altogether is to use the following statement.

    On Error Resume Next

    Place this statement immediately above the code that is going to produce an error: it this tells the code to ignore errors and resume code execution.  Let’s look at an example.

    Sub OnErrorResumeNext()
    Const VAT As Single = 0.2
    Dim rg As Range, ProductField As Range
    Dim PriceIncVAT As Currency
    Dim MissingUnitPrice As Single
    Dim MissingDateFirstStocked As Date
    Set ProductField = Range("A2", Range("A2").End(xlDown))
    
    'Ignore all errors encountered
    On Error Resume Next
    
    'Calculate the unit price inc VAT
    For Each rg In ProductField
        With rg.Offset(0, 4)
            .Value = rg.Offset(0, 3) * (1 + VAT)
            .NumberFormat = "£#,##0.00"
        End With
    Next rg
    
    'Calculate the number of years the product has been kept in stock
    For Each rg In ProductField
        rg.Offset(0, 2) = Format((Date - rg.Offset(0, 1)) / 365.25, "0.0")
    Next rg
    
    End Sub
    

    The code runs until it has completed all instructions and ends up doing a pretty good job of calculating the relevant fields.  Any calculations it can’t do it skips over and leaves a blank cell – see the results below.

    Using On Error Goto Statements

    An On Error Goto statement allows you to jump to a named piece of code that will handle your error.  The named piece of code will then run instead of the Run-time error.

    In our example the error handling code will ask the user whether they would like to supply any missing data.

    If the user supplies a value, it is entered into the database and the calculation is made.

    An Error Goto statement looks like this…

    On Error GoTo VATCalcError

    …where VATCalcError is the name given to the error handling code.

    Error handling code is normally placed at the end of the procedure as shown in the code below.

    Sub ErrorGoto()
    Const VAT As Single = 0.2
    Dim rg As Range, ProductField As Range
    Dim PriceIncVAT As Currency
    Dim MissingUnitPrice As Single
    Dim MissingDateFirstStocked As Date
    Set ProductField = Range("A2", Range("A2").End(xlDown))
    
    'If For Each Next loop encounters an error go to VATCalcError
    On Error GoTo VATCalcError
    
    For Each rg In ProductField
        With rg.Offset(0, 4)
            .Value = rg.Offset(0, 3) * (1 + VAT)
            .NumberFormat = "£#,##0.00"
        End With
    Next rg
    
    'Cancel the Error GoTo statement above for the remainder of the procedure
    On Error GoTo 0
    
    'If For Each Next loop encounters an error go to YrsStockedCalcError
    On Error GoTo YrsStockedCalcError
    
    For Each rg In ProductField
        rg.Offset(0, 2) = Format((Date - rg.Offset(0, 1)) / 365.25, "0.0")
    Next rg
    
    'Cancel the Error GoTo statement above for the remainder of the procedure
    On Error GoTo 0
    Exit Sub
    
    'ERROR HANDLING
    '------------------------------------------------------------------
    VATCalcError:
    If MsgBox(rg & " does not have a valid unit price." & vbNewLine & _
    "Would you like to enter a unit price now?", vbYesNo) = vbYes Then
        MissingUnitPrice = Application.InputBox(Prompt:="Enter the Unit Price for " & rg, Type:=1)
        rg.Offset(0, 3) = MissingUnitPrice
        With rg.Offset(0, 4)
            .Value = rg.Offset(0, 3) * (1 + VAT)
            .NumberFormat = "£#,##0.00"
        End With
    
        'After the user has given a unit price resume the For Each Next loop
        Resume Next
    End If
    'If the user did not give a unit price resume the For Each Next loop
    Resume Next
    
    YrsStockedCalcError:
    If MsgBox(rg & " does not have a valid Date First Stocked." & vbNewLine & _
    "Would you like to enter a date now?", vbYesNo) = vbYes Then
        MissingDateFirstStocked = Application.InputBox(Prompt:="Enter the Date First Stocked for " & rg, Type:=1)
        rg.Offset(0, 1) = MissingDateFirstStocked
        rg.Offset(0, 2) = Format((Date - rg.Offset(0, 1)) / 365.25, "0.0")
        
        'After the user has given a date resume the For Each Next loop
        Resume Next
    End If
    'If the user did not give a date resume the For Each Next loop
    Resume Next
    
    End Sub
    

    When you name an area of code always suffix the name with a colon and then start your code on the next line, as shown below.

    VATCalcError:
    Code starts here
    Resume Next
    Exit Sub
    

    The Resume Next statement means ‘go back to the point in the code that the error occurred and resume next code execution’.  This means that once error handling is complete the rest of the code in your procedure can carry on.

    Exit Sub is important if you have more than one piece of error handling code.  Without Exit Sub or Resume Next the procedure will continue to run any code beneath it.

    Using On Error GoTo 0

    Use the following code to restore normal handling behaviour in your procedure.  Place the statement at the end of the piece of code that you add an error handler to. If you don’t do this any errors that occur will be handled by the most recent On Error Goto statement.

    On Error GoTo 0

    Specifying Error Handling for Different Run-time Error Types

    In this example we have a very simple procedure that calculates the average daily sales

    This is our data…

    …and this is our code.

    Sub AverageSales()
    Dim AverageSales As Range, StartDate As Range, EndDate As Range, TotalSales As Range
    Dim AverageSalesCalc As Currency
    Set StartDate = Range("A2")
    Set EndDate = Range("B2")
    Set TotalSales = Range("C2")
    Set AverageSales = Range("D2")
    AverageSalesCalc = TotalSales / (EndDate - StartDate)
    AverageSales = AverageSalesCalc
    End Sub
    

    There are two errors that could occur in this scenario:

    1. If the worksheet does not contain a start date and end date, Run-time error 11 would be displayed which is the divide by zero error – you can’t divide by zero in Excel.

    2. The other error that might occur is if a text value is accidently entered as a start date or end date or total sales value, in which case the Run-time error 13 would be displayed.

    These numbers are important as you can use them to identity what type of error has occurred and then deal with the error accordingly.   To reference the Run-time error number use…

    Err.Number

    With an If statement we can specify what happens when a particular run-time error occurs.

    CalculationErrors:
    If Err.Number = 13 Then MsgBox _
    "Check that your Dates and Total Sales Figures are not text."
    If Err.Number = 11 Then MsgBox _
    "Looks like you haven't entered a start or finish date."
    

    Here’s the complete code including error handling.

    Sub AverageSales()
    Dim AverageSales As Range, StartDate As Range, EndDate As Range, TotalSales As Range
    Dim AverageSalesCalc As Currency
    Set StartDate = Range("A2")
    Set EndDate = Range("B2")
    Set TotalSales = Range("C2")
    Set AverageSales = Range("D2")
    
    On Error GoTo CalculationErrors:
    AverageSalesCalc = TotalSales / (EndDate - StartDate)
    AverageSales = AverageSalesCalc
    
    CalculationErrors:
    If Err.Number = 13 Then MsgBox "Check that your Dates and Total Sales Figures are not text."
    If Err.Number = 11 Then MsgBox "Looks like you haven't entered a start or finish date."
    End Sub