VBA Error Handling – A Complete Guide

VBA Error Handling

“Abort, Retry, Fail?” – MS-DOS error message circa 1986

This post provides a complete guide to VBA Error Handing. If you are looking for a quick summary then check out the quick guide table in the first section.

If you are looking for a particular topic on VBA Error Handing then check out the table of contents below(if it’s not visible click on the post header).

If you are new to VBA Error Handling, then you can read the post from start to finish as it is laid out in logical order.

A Quick Guide to Error Handing

On Error Goto 0When error occurs, the code stops and displays the error.
On Error Goto -1 Clears the current error setting and reverts to the default.
On Error Resume NextIgnores the error and continues on.
On Error Goto [Label]Goes to a specific label when an error occurs.
This allows us to handle the error.
Err ObjectWhen an error occurs the error information is stored here.
Err.NumberThe number of the error.
(Only useful if you need to check a specific error occurred.)
Err.DescriptionContains the error text.
Err.SourceYou can populate this when you use Err.Raise.
Err.RaiseA function that allows you to generate your own error.
Error FunctionReturns the error text from an error number.
Error StatementSimulates an error. Use Err.Raise instead.


The Webinar

Members of the VBA Vault can access the webinar for this article by clicking on the image below.

(Note: Vault members have access to the webinar archive.)

vba error handling video


Error Handling refers to code that is written to handle errors which occur when your application is running. These errors are normally caused by something outside your control like a missing file, database being unavailable, data being invalid etc.

If we think an error is likely to occur at some point, it is good practice to write specific code to handle the error if it occurs and deal with it.

For all other errors we use generic code to deal with them. This is where the VBA error handling statement comes into play. They allow our application to deal gracefully with any errors we weren’t expecting.

To understand error handling we must first understand the different types of errors in VBA.

VBA Error Handling

VBA Errors

There are three types of errors in VBA

  1. Syntax
  2. Compilation
  3. Runtime

We use error handling to deal with runtime errors. Let’s have a look at each of these error types so that it is clear what a runtime error is.


Syntax Errors

If you have used VBA for any length of time you will have seen a syntax error. When you type a line and press return, VBA will evaluate the syntax and if it is not correct it will display an error message.

For example if you type If and forget the Then keyword, VBA will display the following error message

VBA Error Handling

Some examples of syntax errors are

' then is missing
If a > b

' equals is missing after i
For i 2 To 7

' missing right parenthesis
b = left("ABCD",1

Syntax errors relate to one line only. They occur when the syntax of one line is incorrect.

Note: You can turn off the Syntax error dialog by going to Tools->Options and checking off “Auto Syntax Check”. The line will still appear red if there is an error but the dialog will not appear.


Compilation Errors

Compilation errors occur over more than one line. The syntax is correct on a single line but is incorrect when all the project code is taken into account.

Examples of compilation errors are:

  • If statement without corresponding End If statement
  • For without Next
  • Select without End Select
  • Calling a Sub or Function that does not exist
  • Calling a Sub or Function with the wrong parameters
  • Giving a Sub or Function the same name as a module
  • Variables not declared(Option Explicit must be present at the top of the module)

The following screenshot shows a compilation error that occurs when a For loop has no matching Next statement.

VBA Error Handling


Using Debug->Compile

To find compilation errors, we use Debug->Compile VBA Project from the Visual Basic menu.

When you select Debug->Compile, VBA displays the first error it comes across.

When this error is fixed, you can run Compile again and VBA will then find the next error.

Debug->Compile will also include syntax errors in it’s search which is very useful.

If there are no errors left and you run Debug->Compile , it may appear that nothing happened. However, “Compile” will be grayed out in the Debug menu. This means your application has no compilation errors at the current time.


Debug->Compile Error Summary

  • Debug->Compile finds compilation(project wide) errors.
  • It will also find syntax errors.
  • It finds one error each time you use it.
  • When there are no compilation errors left the Compile option will appear grayed out in the menu.


Debug->Compile Usage

You should always use Debug->Compile before you run your code. This ensures that your code has no compilation errors when you run it.

If you do not run Debug->Compile then VBA may find compile errors when it runs. These should not be confused with Runtime errors.


Runtime Errors

Runtime errors occur when your application is running. They are normally outside of your control but can be caused by errors in your code.

VBA Error Handling

For example, imagine your application reads from an external workbook. If this file gets deleted then VBA will display an error when your code tries to open it.

Other examples of runtime errors are

  • a database not being available
  • the user entering invalid data
  • a cell containing text instead of a number

As we have seen, the purpose of error handling is to deal with runtime errors when they occur.


Expected Versus Unexpected Errors

When we think a runtime error could occur we put code in place to handle it. For example, we would normally put code in place to deal with a file not being found.

The following code checks if the file exists before it tries to open it. If the file does not exist then a user friendly message is displayed and the code exits the sub.

Sub OpenFile()
    Dim sFile As String
    sFile = "C:\docs\data.xlsx"
    ' Use Dir to check if file exists
    If Dir(sFile) = "" Then
        ' if file does not exist display message
        MsgBox "Could not find the file " & sFile
        Exit Sub
    End If
    ' Code will only reach here if file exists
    Workbooks.Open sFile
End Sub

When we think an error is likely to occur at some point, it is good practice to add code to handle the situation. We normally refer to these errors as expected errors.

If we don’t have specific code to handle an error it is considered an unexpected error. We use the VBA error handling statements to handle the unexpected errors.


Runtime Errors that are not VBA Errors

Before we look at the VBA Handling there is one type of error we must mention. Some runtime errors are not considered errors by VBA but only by the user.

Let me explain this with an example. Imagine you have an application that requires you to add the values in the variables a and b

result = a + b


Let’s say you mistakenly use an asterisk instead of the plus sign

result = a * b

This is not a VBA error. Your code syntax is perfectly legal. However, from your requirements point of view it is an error.

These errors cannot be dealt with using error handling as they obviously won’t generate any error. You can deal with these errors using Unit Testing and Assertions. I have an in-depth post about using VBA assertions – see How to Make Your Code BulletProof.


The On Error Statement

As we have seen there are two ways to treat runtime errors

  1. Expected errors – write specific code to handle them.
  2. Unexpected errors – use VBA error handling statements to handle them.


The VBA On Error statement is used for error handling. This statement performs some action when an error occurs during runtime.

There are four different ways to use this statement

  1. On Error GoTo 0 – the code stops at the line with the error and displays a message.
  2. On Error Resume Next – the code moves to next line. No error message is displayed.
  3. On Error GoTo [label] – the code moves to a specific line or label. No error message is displayed. This is the one we use for error handling.
  4. On Error GoTo -1 – clears the current error.

Let’s look at each of these statements in turn.

On Error GoTo 0

This is the default behavior of VBA. In other words, if you don’t use On Error then this is the behavior you will see.

When an error occurs, VBA stops on the line with the error and displays the error message. The application requires user intervention with the code before it can continue. This could be fixing the error or restarting the application. In this scenario no error handling takes place.

Let’s look at an example. In the following code, we have not used any On Error line so VBA will use the On Error GoTo 0 behavior by default.

Sub UsingDefault()

    Dim x As Long, y As Long
    x = 6
    y = 6 / 0
    x = 7

End Sub

The second assignment line results in a divide by zero error. When we run this code we will get the error message shown in the screenshot below

VBA Error Handling

When the error appears you can choose End or Debug

If you select End then the application simply stops.
If you select Debug the application stops on the error line as the screenshot below shows

VBA Error Handling

This behaviour is fine when you are writing VBA code as it shows you the exact line with the error.

This behavior is unsuitable for an application that you are given to a user. These errors look unprofessional and they make the application look unstable.

An error like this is essentially the application crashing. The user cannot continue on without restarting the application. They may not use it at all until you fix the error for them.

By using On Error GoTo [label] we can give the user a more controlled error message. It also prevents the application stopping. We can get the application to perform in a predefined manner.


On Error Resume Next

Using On Error Resume Next tells VBA to ignore the error and continue on.

There are specific occasions when this is useful. Most of the time you should avoid using it.

If we add Resume Next to our example Sub then VBA will ignore the divide by zero error

Sub UsingResumeNext()

    On Error Resume Next
    Dim x As Long, y As Long
    x = 6
    y = 6 / 0
    x = 7

End Sub


It is not a good idea to do this. If you ignore the error, then the behavior can be unpredictable. The error can affect the application in multiple ways.You could end up with invalid data. The problem is that you aren’t aware that something went wrong because you have suppressed the error.

The code below is an example of where using Resume Next is valid

Sub SendMail()

   On Error Resume Next
    ' Requires Reference:
    ' Microsoft Outlook 15.0 Object Library
    Dim Outlook As Outlook.Application
    Set Outlook = New Outlook.Application

    If Outlook Is Nothing Then
        MsgBox "Cannot create Microsoft Outlook session." _
                   & " The email will not be sent."
        Exit Sub
    End If
End Sub

In this code we are checking to see if Microsoft Outlook is available on a computer. All we want to know is if it is available or not. We are not interested in the specific error.

In the code above, we continue on if there is an error. Then in the next line we check the value of the Outlook variable. If there has been an error then the value of this variable will be set to Nothing.

This is an example of when Resume could be useful. The point is that even though we use Resume we are still checking for the error. The vast majority of the time you will not need to use Resume.


On Error GoTo [label]

This is how we use Error Handling in VBA. It is the equivalent of the Try and Catch functionality you see in languages such as C# and Java.

When an error occurs you send the error to a specific label. It is normally at the bottom of the sub.

Let’s apply this to the sub we have been using

Sub UsingGotoLine()

    On Error GoTo eh
    Dim x As Long, y As Long
    x = 6
    y = 6 / 0
    x = 7
    Exit Sub
    MsgBox "The following error occurred: " & Err.Description
End Sub

The screenshot below shows what happens when an error occurs

VBA Error Handling

VBA jumps to the eh label because we specified this in the On Error Goto line.

Note 1: The label we use in the On…GoTo statement, must be in the current Sub/Function. If not you will get a compilation error.

Note 2: When an error occurs when using On Error GoTo [label], the error handling returns to the default behaviour i.e. The code will stop on the line with the error and display the error message. See the next section for more information about this.


On Error GoTo -1

This statement is different than the other three. It is used to clear the current error rather than setting a particular behaviour.

When an error occurs using On Error GoTo [label], the error handling behaviour returns to the default behaviour i.e. “On Error GoTo 0”. That means that if another error occurs the code will stop on the current line.

This behaviour only applies to the current sub. Once we exit the sub, the error will be cleared automatically.

Take a look at the code below. The first error will cause the code to jump to the eh label. The second error will stop on the line with the 1034 error.

Sub TwoErrors()

    On Error Goto eh
    ' generate "Type mismatch" error
    Error (13)

    Exit Sub
    ' generate "Application-defined" error
    Error (1034)
End Sub


If we add further error handling it will not work as the error trap has not been cleared.

In the code below we have added the line

On Error Goto eh_other

after we catch the first error.

This has no effect as the error has not been cleared. In other words the code will stop on the line with the error and display the message.

Sub TwoErrors()

    On Error Goto eh
    ' generate "Type mismatch" error
    Error (13)

    Exit Sub
    On Error Goto eh_other
    ' generate "Application-defined" error
    Error (1034)
Exit Sub
    Debug.Print "ehother " & Err.Description
End Sub

To clear the error we use On Error GoTo -1. Think of it like setting a mouse trap. When the trap goes off you need to set it again.

In the code below we add this line and the second error will now cause the code to jump to the eh_other label

Sub TwoErrors()

    On Error Goto eh
    ' generate "Type mismatch" error
    Error (13)

    Exit Sub
    ' clear error
    On Error Goto -1
    On Error Goto eh_other
    ' generate "Application-defined" error
    Error (1034)
Exit Sub
    Debug.Print "ehother " & Err.Description
End Sub

Note 1: There are probably rare cases where using On Error GoTo -1 is useful. I have personally never needed to use this line. Remember that once you leave the sub the error will be cleared anyway.

Note 2: The Err Object has a member Clear. Using Clear clears the text and numbers in the Err object, but it does NOT reset the error.


Using On Error

As we have seen, VBA will do one of three things when an error occurs

  • Stop and display the error.
  • Ignore the error and continue on.
  • Jump to a specific line.

VBA will always be set to one of these behaviors. When you use On Error, VBA will change to the behaviour you specify and forget about any previous behavior.

In the following Sub, VBA changes the error behaviour each time we use the On Error statement

Sub ErrorStates()

    Dim x As Long
    ' Go to eh label if error
    On Error Goto eh
    ' this will ignore the error on the following line
    On Error Resume Next
    x = 1 / 0
    ' this will display an error message on the following line
    On Error Goto 0
    x = 1 / 0
   Exit Sub
    Debug.Print Err.Description
End Sub


The Err Object

When an error occurs you can view details of the error using the Err object.

When an runtime error occurs, VBA automatically fills the Err object with details.

The code below will print “Error Number: 13 Type Mismatch” which occurs when we try to place a string value in the long integer total

Sub UsingErr()

    On Error Goto eh
    Dim total As Long
    total = "aa"

    Exit Sub
    Debug.Print "Error number: " & Err.Number _
            & " " & Err.Description
End Sub

The Err.Description provides details of the error that occurs. This is the text you normally see when an error occurs e.g. “Type Mismatch”

The Err.Number is the ID number of the error e.g. the error number for “Type Mismatch” is 13. The only time you really need this is if you are checking that a specific error occurred and this is only necessary on rare occasions.

The Err.Source property seems like a great idea but it does not work for a VBA error. The source will return the project name, which hardly narrows down where the error occurred. However, if you create an error using Err.Raise you can set the source yourself and this can be very useful.


Getting the Line Number

The Erl function is used to return the line number where the error occurs.

It often causes confusion. In the following code, Erl will return zero

Sub UsingErr()

    On Error Goto eh
    Dim val As Long
    val = "aa"

    Exit Sub
    Debug.Print Erl
End Sub

This is because there are no line numbers present. Most people don’t realise it but VBA allows you to have line numbers.

If we change the Sub above to have line number it will now print out 20

Sub UsingErr()

10        On Error Goto eh
          Dim val As Long
20        val = "aa"

30        Exit Sub
40        Debug.Print Erl
End Sub

Adding line numbers to your code manually is cumbersome. However there are tools available that will allow you to easily add and remove line numbers to a sub.

When you are finished working on a project and hand it over to the user it can be useful to add line numbers at this point. If you use the error handling strategy in the last section of this post, then VBA will report the line where the error occurred.


Using Err.Raise

Err.Raise allows us to create errors. We can use it to create custom errors for our application which is very useful. It is the equivalent of the Throw statement in Java\C#.

The format is as follows

Err.Raise [error number], [error source], [error description]

Let’s look at a simple example. Imagine we want to ensure that a cell has an entry that has a length of 5 characters. We could have a specific message for this

Public Const ERROR_INVALID_DATA As Long = vbObjectError + 513

Sub ReadWorksheet()

    On Error Goto eh
    If Len(Sheet1.Range("A1")) <> 5 Then
        Err.Raise ERROR_INVALID_DATA, "ReadWorksheet" _
            , "The value in the cell A1 must have exactly 5 characters."
    End If
    ' continue on if cell has valid data
    Dim id As String
    id = Sheet1.Range("A1")

    Exit Sub
    ' Err.Raise will send code to here
    MsgBox "Error found: " & Err.Description
End Sub

When we create an error using Err.Raise we need to give it a number. We can use any number from 513 to 65535 for our error. We must use vbObjectError with the number e.g.

Err.Raise vbObjectError + 513


Using Err.Clear

Err.Clear is used to clear the text and numbers from the Err.Object. In other words, it clears the description and number.

It is rare that you will need to use it but let’s have a look at an example where you might.

In the code below we are counting the number of errors that will occur. To keep it simple we are generating an error for each odd number.

We check the error number each time we go through the loop. If the number does not equal zero then an error has occurred. Once we count the error we need to set the error number back to zero so it is ready to check for the next error.

Sub UsingErrClear()

    Dim count As Long, i As Long

    ' Continue if error as we will check the error number
    On Error Resume Next
    For i = 0 To 9
        ' generate error for every second one
        If i Mod 2 = 0 Then Error (13)
        ' Check for error
        If Err.Number <> 0 Then
            count = count + 1
            Err.Clear    ' Clear Err once it is counted
        End If

    Debug.Print "The number of errors was: " & count
End Sub

Note 1: Err.Clear resets the text and numbers in the error object but it does not clear the error – see On Error GoTo -1 for more information about clearing the actual error.



Logging means writing information from your application when it is running. When an error occurs you can write the details to a text file so you have a record of the error.

The code below shows a very simple logging procedure

Sub Logger(sType As String, sSource As String, sDetails As String)
    Dim sFilename As String
    sFilename = "C:\temp\logging.txt"
    ' Archive file at certain size
    If FileLen(sFilename) > 20000 Then
        FileCopy sFilename _
            , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt"))
        Kill sFilename
    End If
    ' Open the file to write
    Dim filenumber As Variant
    filenumber = FreeFile 
    Open sFilename For Append As #filenumber
    Print #filenumber, CStr(Now) & "," & sType & "," & sSource _
                                & "," & sDetails & "," & Application.UserName
    Close #filenumber
End Sub

You can use it like this

' Create unique error number
Public Const ERROR_DATA_MISSING As Long = vbObjectError + 514

Sub CreateReport()

    On Error Goto eh
    If Sheet1.Range("A1") = "" Then
       Err.Raise ERROR_DATA_MISSING, "CreateReport", "Data is missing from Cell A1"
    End If

    ' other code here
    Exit Sub
    Logger "Error", Err.Source, Err.Description
End Sub

The log is not only for recording errors. You can record other information as the application runs. When an error occurs you can then check the sequence of events before an error occurred.

Below is an example of logging. How you implement logging really depends on the nature of the application and how useful it will be.

Sub ReadingData()
    Logger "Information", "ReadingData()", "Starting to read data."
    Dim coll As New Collection
    ' Read data
    Set coll = ReadData
    If coll.Count < 10 Then
        Logger "Warning", "ReadingData()", "Number of data items is low."
    End If
    Logger "Information", "ReadingData()", "Number of data items is " & coll.Count
    Logger "Information", "ReadingData()", "Finished reading data."

End Sub

Having a lot of information when dealing with an error can be very useful. Often the user may not give you accurate information about the error that occurred. By looking at the log you can get more accurate information about the information.


Other Error Related Items

This section covers some of the other Error Handling tools that VBA has. These items are considered obsolete but I have included them as they may exist in legacy code.


Error Function

The Error Function is used to print the error description from a given error number. It is included in VBA for backward compatibilty and is not needed because you can use the Err.Description instead.

Below are some examples

' Print the text "Division by zero"
Debug.Print Error(11)
' Print the text "Type mismatch"
Debug.Print Error(13)
' Print the text "File not found"
Debug.Print Error(53)


Error Statement

The Error statement allows you to simulate an error. It is included in VBA for backward compatibility. You should use Err.Raise instead.

In the following code we simulate a “Divide by zero” error.

Sub SimDivError()

    On Error Goto eh
    ' This will create a division by zero error
    Error 11
    Exit Sub
    Debug.Print Err.Number, Err.Description
End Sub

This statement is included in VBA for backward compatibility. You should use Err.Raise instead.


A Simple Error Handling Strategy

With all the different options you may be confused about how to use error handling in VBA. In this section, I’m going to show you how to implement a simple error handling strategy that you can use in all your applications.


The Basic Implementation

This is a simple overview of our strategy

  1. Place the On Error GoTo Label line at the start of our topmost sub.
  2. Place the error handling Label at the end of our topmost sub.
  3. If an expected error occurs then handle it and continue.
  4. If the application cannot continue then use Err.Raise to jump to the error handling label.
  5. If an unexpected error occurs the code will automatically jump to the error handling label.

The following image shows an overview of how this looks


The following code shows a simple implementation of this strategy

Public Const ERROR_NO_ACCOUNTS As Long = vbObjectError + 514

Sub BuildReport()

    On Error Goto eh
    ' If error in ReadAccounts then jump to error
    ' Do something with the code
    Exit Sub
    ' All errors will jump to here
    MsgBox Err.Source & ": The following error occured  " & Err.Description
End Sub

Sub ReadAccounts()
    ' EXPECTED ERROR - Can be handled by the code
    ' Application can handle A1 being zero
    If Sheet1.Range("A1") = 0 Then
        Sheet1.Range("A1") = 1
    End If
    ' EXPECTED  ERROR - cannot be handled by the code
    ' Application cannot continue if no accounts workbook
    If Dir("C:\Docs\Account.xlsx") = "" Then
        Err.Raise ERROR_NO_ACCOUNTS, "UsingErr" _
                , "There are no accounts present for this month."
    End If

    ' UNEXPECTED ERROR - cannot be handled by the code
    ' If cell B3 contains text we will get a type mismatch error
    Dim total As Long
    total = Sheet1.Range("B3")
    ' continue on and read accounts
End Sub

This is a nice way of implementing error handling because

  • We don’t need to add error handling code to every sub.
  • If an error occurs then VBA exits the application gracefully.


A Complete Error Handling Strategy

The above strategy has one major drawback. It doesn’t provide any information about the error. It is better than having no strategy as it prevents the application crashing. But that is the only real benefit.

VBA doesn’t fill Err.Source with anything useful so we have to do this ourselves.

In this section, I am going to introduce a more complete error strategy. I have written two subs that perform all the heavy lifting so all you have to do is add them to your project.

The purpose of this strategy is to provide you with the Stack* and line number when an error exists.

*The Stack is the list of sub/functions that were currently in use when the error occurred.

This is our strategy

  1. Place error handling in all the subs.
  2. When an error occurs, the error handler adds details to the error and raises it again.
  3. When the error reaches the topmost sub it is displayed.

We are simply “bubbling” the error to the top. The following diagram shows a simple visual of what happens when an error occurs in Sub3

Error Handling - bubbling

The only messy part to this is formatting the strings correctly. I have written two subs that handle this, so it is taken care of for you.

These are the two helper subs:

Option Explicit

Private Const LINE_NO_TEXT As String = "Line no: "
Dim AlreadyUsed As Boolean

' Reraises an error and adds line number and current procedure name
Sub RaiseError(ByVal errorNo As Long _
                , ByVal src As String _
                , ByVal proc As String _
                , ByVal desc As String _
                , ByVal lineNo As Long)

    Dim sSource As String

    ' If called for the first time then add line number
    If AlreadyUsed = False Then
        ' Add error line number if present
        If lineNo <> 0 Then
            sSource = vbNewLine & LINE_NO_TEXT & lineNo & " "
        End If

        ' Add procedure to source
        sSource = sSource & vbNewLine & proc
        AlreadyUsed = True
        ' If error has already been raised simply add on procedure name
        sSource = src & vbNewLine & proc
    End If
    ' Pause the code here when debugging
    '(To Debug: "Tools->VBA Properties" from the menu.
    ' Add "Debugging=1" to the     ' "Conditional Compilation Arguments.)
#If Debugging = 1 Then
    Debug.Assert False
#End If

    ' Reraise the error so it will be caught in the caller procedure
    ' (Note: If the code stops here, make sure DisplayError has been
    ' placed in the topmost procedure)
    Err.Raise errorNo, sSource, desc

End Sub

' Displays the error when it reaches the topmost sub
' Note: You can add a call to logging from this sub
Sub DisplayError(ByVal src As String, ByVal desc As String _
                    , ByVal sProcname As String, lineNo As Long)

    ' Check If the error happens in topmost sub
    If AlreadyUsed = False Then
        ' Reset string to remove "VBAProject" and add line number if it exists
        src = IIf(lineNo = 0, "", vbNewLine & LINE_NO_TEXT & lineNo)
    End If

    ' Build the final message
    Dim sMsg As String
    sMsg = "The following error occurred: " & vbNewLine & Err.Description _
                    & vbNewLine & vbNewLine & "Error Location is: "
    sMsg = sMsg & src & vbNewLine & sProcname

    ' Display the message
    MsgBox sMsg, Title:="Error"

    ' reset the boolean value
    AlreadyUsed = False

End Sub


An Example of using this strategy

Here is a simple coding example that uses these subs. In this strategy, we don’t place any code in the topmost sub. We only call subs from it.

Sub Topmost()

    On Error Goto EH

    Exit Sub
    DisplayError Err.source, Err.Description, "Module1.Topmost", Erl
End Sub

Sub Level1()

    On Error Goto EH

    Exit Sub
   RaiseError Err.Number, Err.source, "Module1.Level1", Err.Description, Erl
End Sub

Sub Level2()

    On Error Goto EH
    ' Error here
    Dim a As Long
    a = "7 / 0"

    Exit Sub
    RaiseError Err.Number, Err.source, "Module1.Level2", Err.Description, Erl
End Sub

The result looks like this

error handling output

If your project has line numbers the result will include the line number of the error

error handling output line


Error Handling in a Nutshell

  • Error Handling is used to handle errors that occur when your application is running.
  • You write specific code to handle expected errors. You use the VBA error handling statement On Error GoTo [label] to send VBA to a label when an unexpected error occurs.
  • You can get details of the error from Err.Description.
  • You can create your own error using Err.Raise.
  • Using one On Error statement in the top most sub will catch all errors in subs that are called from here.
  • If you want to record the name of the Sub with the error, you can update the error and rethrow it.
  • You can use a log to record information about the application as it is running.


What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)


Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook


  1. Hi Paul, this is a very useful and informative post. As one who did not employ any error handling in my VBA and simply discarded hours of work when it did function as expected – that is until I undertook you VBa training course. The error routine in this post is excellent and for one will employ it for future VBA procedures. Thanks very much – much appreciated.

  2. Nice post and a solid dose of knowledge again.
    Now my question is on the part with the logging procedure. I’ve noticed things that were unknown to me like:
    Open sFilename For Append As #filenumber
    Close #filenumber
    After searching the web resources, it seemes to me, that they are similar to thing I do with these statements (they are just examples, so don’t try to seek any bigger logic there):
    Dim fso as New FileSytsemObject
    Dim report as TextStream
    Set report = fs.CreateTextFile(“\\kermit\logistics\reports\log_rotation.txt”)
    report.WriteLine(‘something stupid here’)
    report.OpenTextFile(“\\kermit\logistics\reports\log_rotation.txt”, ForAppending)
    fs.CopyFile “K:\someOldJunk.txt” “H:\somNewJunk.txt”

    ok, so am I right, that these things can basically do the same? Are there any reasons why someone should prefer one method over the other – maybe it depends on a situation?

    1. Hi Tomek,

      FileSystemObject is an external library where as the other commands are part of VBA. They do perform the same tasks.

      Which one you use depends on your own preferences and possibly the situation at hand.

  3. Thanks for the detailed writeup, Paul; I have bookmarked this site. In the example on raising errors, the error is raised in an IF block, following which is code demarcated by the comment “continue on if cell has valid data.” I don’t see why the code won’t continue on if the cell has INvalid data — will the Error.Raise statement effectively exit the function?

    1. Hi John,

      Thanks for your comment.

      The Err.Raise statement generates an error. When we use “On Error Goto [Label]” the Err.Raise will go searching for the Label.

      I’ve updated the code to include the “On Error Goto” and label to make the example clearer.


  4. Hi Paul, thanks for your post!
    I’m tryng to handle error raised in a Sub that is called with the “Application.Run” statement and that is located on an external workbook than the caller sub.

    I write a sample code of this:
    [ The caller sub in Module1 in File1.xls ]
    Sub Main_Sub()
    On Error GoTo EH
    Call Application.Run(“‘File2.xls’!ExternalSub”)
    ‘Call ExternalSub ‘(This line is temporarly commented out)
    ‘(some lines to handle error)
    End Sub

    ‘[Called sub in Module1 in File2.xls]
    Sub ExternalSub()
    On Error GoTo 0 ‘(that means no error handling in this sub)
    Err.Raise 600, “Description of custom error”
    End sub

    The result is that the error handler of the Main_Sub is ignored, so the running code breaks on the “Err.Raise 600, …” line inside the ExternalSub.

    If, instead, I write the ExternalSub in a module the same File1.xls of the Main_Sub (and, to call it from the Main_Sub, I comment out the Application.Run line and uncomment the “Call ExternalSub” line), the error is correctly handled by the Main_Sub and the code runs after the EH label.

    Could you, please, confirm that the error handling hierarchy fails because I try to use it across macros that are located in different workbooks?

    Thanks in advance.

    1. Hi Sabato,

      The error handling will not work across workbooks.
      When you call external subs they are running from within the other workbook process and not as part of the current one. You can think of them as different applications.


  5. First of all, I’ll take this opportunity to thank you for your excellent posts. I’ve learned a lot from them!

    Using ActiveWorkbook.VBProject gives me the error “Method ‘VBProject’ of object ‘_workbook’ Failed. This error can be taken care of by providing some permission for Macros. But that would have to be per user setting. Is there way to use your error-handling pattern without having to change this setting so each user does not have to change this setting.

    1. The user has to set permissions for the Macros for security reasons. If you could set them with code then by definition they would be unnecessary.

      However, these permissions need to be only set once by the user.

      The best you can do is check for that particular error number. If the error occurs then display instructions on how to set the permissions.

  6. Well, Paul, your site is definitely my favorite pub. I come back every time I can and I stay until I fall under the desk 😉
    Thank so much for all the stuff you post and gratulate for the top quality of didactic.

      1. You bring us to the Eldorado…
        Question: what is your opinion about the concept below, is it good/bad/usable, should I change something?
        Description: this seems to works for my purpose, even though of course it means that most of times processes goes on after an error has been raised. These 2 procedures are actually the simplified version. In my apps I then have a module named dp (as keyword for debug.print) dedicated to the log of every start, end, error and values for every Sub/Function/Property to the immediate and to a text file. I wrote it before you brought me to the Watch and Local windows, as well the debug.assert method (again I’m deeply grateful). Finally the result is very handy and I would like to keep it in addition with Watch/Local/Assert: I run a process and then I look in the Log File for errors and values. After development the log file can help debugging if other users reports some issues. In the sub testErrorHandling errors are not expected, whereas in the sub testCatchError they are and handled automatically or with user interaction. I tried to add a On Error GoTo -1 (excluded in code as comment), but then it runs in to a error loop (20 Resume without error)

        Private Sub testErrorHandling()
        On Error GoTo ErrorHandling

        Error (68)
        Error (75)

        Exit Sub
        With Err
        Debug.Print .Number, .Description
        End With
        Resume Next
        End Sub
        Private Sub testCatchError()
        On Error GoTo ErrorHandling

        Dim xPath As String
        Dim xDirectory As String
        Dim xErr As Integer

        With Application
        xPath = ThisWorkbook.Path & .PathSeparator & “vbLogFolder”
        End With

        xDirectory = Dir(xPath, vbDirectory)

        If xErr = 68 Then
        MkDir (xPath)
        End If

        ‘do other stuff

        Exit Sub
        xErr = Err
        With Err
        Debug.Print .Number, .Description
        End With
        Resume Next
        End Sub

        Thanks and kind regards

        1. Hi Curzio,

          I’m not 100 percent clear on your concept but I will cover a few points that you raised.

          Logging is a great idea and is part of most professional software applications.

          Logging to the Immediate Window can be useful but it is limited because using the debug tools are better to fix the problem.

          The best use of Logging is when we give the application to a user. We can log both errors, warnings and information to a text file. If there is an error we can look in the log file to see what occurred.

          Using Resume Next to handle a general error is not a good idea. Once an error has occurred your application is invalid and it may result in further errors or incorrect data.

          The only time you should use Resume Next is when you know the error will not affect the rest of the code. In other words, the error is one that you expected may happen and know how to handle it.

          I hope this helps.

  7. Hi there,

    I’ve been trying your code for the complete error handling but I just can’t get it to work properly. Going through it line by line, it runs fine until the “Err.Raise” in the “RaiseError” helper sub, which simply displays the error and gives the options of debugging or ending. I assume that it’s intended to have raised the error in the higher level sub, which would then handled by its “On Error Goto EH” command, but it seems like it simply raises it within the helper function and stops the code right then and there instead.

    I’ve tried this in Excel 2003 and 2016 and it’s the same thing. Is there something I’m missing here? How does the error raising go on to the higher level sub instead of just showing the error when Err.Raise is called in the helper sub?

  8. Please ignore my earlier comment about code not working. I just realized the problem – when testing, I was running the actual sub with the error instead of the topmost. . . . . . . . . . . Wow, I can’t believe how long it took me to realize that.

    Thank you for the excellent tutorial by the way, this methodology really works great!

      1. Paul Kelly – Nice work. Excellent communication and very practical. I’m a novice and just stared programming high level languages and I’ve always thought that err catching and such was the most important and time consuming aspect of programming.

  9. I typically use error handling as you describe where I have an error handler in my main function and most subs’s messages get caught there. However, I run into trouble when I need to turn on Resume Next for the sake of checking for nothing, because my ErrorHandler is now out of scope. What’s the solution for that? I have a simple example below.

    Sub Main()
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False
    Call OpenFile
    ‘ Do more stuff

    GoTo CleanExit

    MsgBox Err.Description

    Application.ScreenUpdating = True
    End Sub

    Sub OpenFile
    On Error Resume Next
    Set objFile = objFSO.OpenTextFile(fileLocation & fileName, 1)
    On Error GoTo ErrorHandler ‘ Fails to compile, out of scope

    If objFile Is Nothing Then
    Call Err.Raise(2009, , “Out File doesn’t exist.”)
    End If
    End Sub

    1. Hi Cassie,

      Use OnError Goto 0 instead of OnError Goto ErrorHandler after the Set ObjFile line.

      You can use the Dir function to check if a file exists. It won’t find other file errors though.

      Sub OpenFile()
          If Dir(fileLocation & Filename) = "" Then
              Call Err.Raise(2009, , "Out File doesn’t exist.")
          End If
          Set objFile = objfso.OpenTextFile(fileLocation & Filename, 1)
      End Sub
  10. Hi Paul. Excellent site. I have learned a lot!!!
    I had the same problem that Suresh found but I didn’t realize that because he mentions ActiveWorkbook.VBProject and in your code is ThisWorkbook.VBProject on the RaiseError Sub. After I solved the issue I found that it was the same problem.

    It would be nice to explain in your post that for the RaiseError Sub to work the developer needs to follow those steps.

    1. Hi David,

      The issue Suresh had was that he wanted to do those settings in the code rather than getting the user to manually set them.

      I will update the post and mention the steps as you suggested.


  11. Hi Paul,
    Over the years I’ve been lazy to properly use On Error. I ended up using Resume next. Every time I tried to research and learn online, I couldn’t find properly site to explain it clearly like yours. Thanks

    I have couple of questions.
    1) Let say Top Most proc A has error handler with calls proc B without error handler. And proc B calls proc C which also doesn’t have error handler. If I learnt correctly if there is error in proc C it goes back to proc A error handler. What if proc A error handler has resume or resume next statement after correcting error, does it go back to proc C and continue?

    2) Any suggestion on how to add error handler block to workbook with 1800 macros with 38,000 line of code?

    1. 1. It will resume with the line after the call.
      2. There is no easy way. You could write a macro using the VBProject to add error handling to each macro in the project.

  12. hello , Im working with Excel vba automating lots of searches on an intranet with web browser control (on internet explorer) , but I got the error “Run time error -2147417848 (800 10 108) automation error , I dont know how to solve it , I put an error handler but this didnt solve the problema , I would be very glad If you could help me , I have been for 2 weeks looking for information and thinking about this I can´t find the solution ! help please 🙂

    1. Hi Daniela,

      That is a generic error which can have a lot of causes. One of the causes is if you disconnect from the web connection and then try to read from the web after this.

      I would have to examine the specific problem to be able to fix it.


  13. I have a CREATE PDF function that just stopped working this past week after working for years… Any ideas of how to troubleshoot? The areas, “SelectedSchool_M1” and “ReportAreaM1” are respectively a cell that is used to help give unique name of PDF and the range to print as pdf.

    1. Hi Jonathan,

      You can put a breakpoint on a line in the code using F9.

      Run the code – it will pause at the breakpoint. Then us F8 to step through the code, You can check the values of the variables using the watch window.


  14. Hi Paul
    I’ve successfully implemented your error handling strategy and it works perfectly. It’s also caused me to question my occasional use of On Error Resume Next rather than testing for a latent error condition and coding round it!
    One question: would it be possible to include the code associated with Userforms within the scope of your error handling strategy? I’ve had a play but it seems the error reporting chain breaks once the userform is loaded (.show).
    Many thanks

    1. Hi Tim,

      The chain doesn’t actually break.

      VBA is an event-driven language. For example, when you click on a button, then this button event is the topmost function. Therefore the DisplayError error handling call should go here.

      Anywhere you have an event it needs to have the DisplayError call rather than the RaiseError call.


  15. Just wanted to say thanks! Been having a massive issue with an error handle, I send the user to an error label and then based on a code generated during the script its meant to send the user back to an area to retry – works the first time but then stopped working, figured it was something to do with clearing the error but no tutorial or example code told me how until I came across this one, so simple as well, On Error Goto -1. Many thanks, you saved me a heap of frustration!!!!

  16. Hi Paul,

    The “lineno” parameter of the RaiseError routine is not used anywhere. Should “Erl” be replaced with “lineno”, like below, or is the “lineno” parameter even needed?

    If lineno 0 Then
    sSource = vbCrLf & “Line no: ” & lineno & ” ”
    End If

    1. Hi Jamie,

      It should be lineNo instead of Erl. It will still work with Erl but it is better to use LineNo.
      I’ve updated the code to reflect this.


  17. Sorry, but I do not think i was able to communicate my question to you properly. let me put it this way say i have written code in VBA, say to divide two numbers when divisor is Zero the system throws an error at this point i need to identify the line of VBA code were the error is occurring via msgbox. Is this possible?
    Challenge 1. The VBA code has no line number.
    Challenge 2 . I need to get the partial or full text of codes from the line of Error is occurring. Please let me know if i am still not clear.

    1. Hi Divya,

      The code I have provided will display the line number. However, you need to add line numbers to your code.

      Doing this manually is not feasible. I use MZ Tools which has a feature that will automatically add/remove line number to your project, module or procedure.

      It also has many other useful features but it is not free.


  18. Hi Paul, I think my error handling function is not enabled or turned on in the setting (tools>references, if that is where to find it….). Could you tell me where excatly the location is?

  19. Hi Paul,

    I think (of course, if i can suggest) You should add an info about ‘Resume’ and ‘Resume Next’ statements. Without these (in my opinion), the tutorial is not complete.

    Best regards,

      1. Hi Paul again,

        I was thinking about “alone” statments ‘Resume’ and ‘Resume Next’ like following.
        The same functionality (just like in Your section), but in different context.

        Sub AA()

        Dim a As Long
        Dim b As Long
        Dim c As Long

        On Error GoTo adam

        a = 0
        b = 2
        c = b / a

        Exit Sub

        Resume Next

        End Sub


        Sub BB()

        Dim a As Long
        Dim b As Long
        Dim c As Long

        On Error GoTo adam

        a = 0
        b = 2
        c = b / a

        Exit Sub


        End Sub

        Best regards,

        1. Hi Adam,

          It’s generally better to avoid the resume statements.

          If the error is unexpected then you try to clean up and end the process.

          If the error is expected then you handle it without using the error handling mechanism.


  20. Thank you very much Paul for your presiously work.
    My question is that:
    When I copy and paste your code about a complete Error Handling strategy in my vba editor, I receive this message:
    Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module

    and a line: Public Const MARKER As String = “NOT_TOPMOST” is red. I think that something doesn’t work well.

    Can you help me?
    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *