Run time error automation error excel vba

“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

The Webinar

Members of the Webinar Archives can access the webinar for this article by clicking on the image below.

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

Download the Error Handling Library

Introduction

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 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

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.

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.

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.

' https://excelmacromastery.com/
Sub OpenFile()
    
    Dim sFile As String
    sFile = "C:docsdata.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.

' https://excelmacromastery.com/
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

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

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

' https://excelmacromastery.com/
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

' https://excelmacromastery.com/
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

' https://excelmacromastery.com/
Sub UsingGotoLine()

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

The screenshot below shows what happens when an error occurs

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.

' https://excelmacromastery.com/
Sub TwoErrors()

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

Done:
    Exit Sub
eh:
    ' 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.

' https://excelmacromastery.com/
Sub TwoErrors()

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

Done:
    Exit Sub
eh:
    On Error Goto eh_other
    ' generate "Application-defined" error
    Error (1034)
Exit Sub
eh_other:
    Debug.Print "eh_other " & 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

' https://excelmacromastery.com/
Sub TwoErrors()

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

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

Note 1: There are probably rare cases where using On Error GoTo -1 is useful. In most cases using Resume Next is better as it clears the error and resumes the code at the next line after the error occurs.

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

' https://excelmacromastery.com/
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
  
Done:  
   Exit Sub
eh:
    Debug.Print Err.Description
End Sub

Resume Next

The Resume Next statement is used to clear the error and then resume the code from the line after where the error occurred.

If your code can have multiple errors and you want to keep detecting them then this line is very useful.

For example, in the following code we want to resume the code after the error has been reported:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
End Sub

 
We could use On Error Goto -1 to clear the code and then use a goto statement to go back to the code like this:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
continue:
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
    On Error Goto -1 ' clear the error
    Goto continue ' return to the code
End Sub

 
The Resume Next provides a nicer way of doing it and it always means the code is much clearer and easier to understand:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
continue:
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
    ' clear the error and return to the code
    Resume Next  
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

' https://excelmacromastery.com/
Sub UsingErr()

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

Done:
    Exit Sub
eh:
    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

' https://excelmacromastery.com/
Sub UsingErr()

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

Done:
    Exit Sub
eh:
    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

' https://excelmacromastery.com/
Sub UsingErr()

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

Done:
30        Exit Sub
eh:
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 JavaC#.

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

' https://excelmacromastery.com/
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")
    

Done:
    Exit Sub
eh:
    ' 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.If you want the clear the actual error you can use either On Error GoTo -1 or Resume Next

It is rare that you will need to use Err.Clear 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.

' https://excelmacromastery.com/
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
    Next

    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 Resume Next Or On Error GoTo -1 for more information about clearing the actual error.

Logging

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

' https://excelmacromastery.com/
Sub Logger(sType As String, sSource As String, sDetails As String)
    
    Dim sFilename As String
    sFilename = "C:templogging.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
' https://excelmacromastery.com/
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
Done:
    Exit Sub
eh:
    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:

' https://excelmacromastery.com/
Sub ReadingData()
    
    Logger "Information", "ReadingData()", "Starting to read data."
       
    Dim coll As New Collection
    ' add data to the collection
    coll.Add "Apple"
    coll.Add "Pear"
    
    If coll.Count < 3 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.

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 compatibility 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.

' https://excelmacromastery.com/
Sub SimDivError()

    On Error Goto eh
        
    ' This will create a division by zero error
    Error 11
    
    Exit Sub
eh:
    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:

' https://excelmacromastery.com/
Public Const ERROR_NO_ACCOUNTS As Long = vbObjectError + 514

Sub BuildReport()

    On Error Goto eh
    
    ' If error in ReadAccounts then jump to error
    ReadAccounts
    
    ' Do something with the code
    
Done:
    Exit Sub
eh:
    ' 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:DocsAccount.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

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.

There are the two helper subs, RaiseError and DisplayError. You can download the library below:

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.

' https://excelmacromastery.com/
Sub Topmost()

    On Error Goto EH
    
    Level1

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

Sub Level1()

    On Error Goto EH
    
    Level2

Done:
    Exit Sub
EH:
   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"

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

The result looks like this:

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

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.)

I am new to VBA (and Excel for that matter) so please keep that in mind when reviewing my code. This is also my first post here!

I am trying to complete and refine my file, but I have run into a error that I cannot seem to fix or even understand. I have searched this site (and many others) and found many people with this same error, but their resolutions are irrelevant and/or don’t solve my problem.

This is the error I receive:

«Automation Error. The object invoked has disconnected from its clients.»

If I click debug, end, or help, Excel crashes and (sometimes) reopens an recovered file. SO frustrating!

I have managed to locate the line of code that causes this:

templateSheet.Copy After:=indexSheet

templateSheet and indexSheet are defined references to specific worksheets

The gist of what happens within this part of my file:

I’ve created a userform and a form control button. The button shows the userform. The userform has two fields asking the user to enter names. The code (all in the userform) checks all worksheet names.

1. If the name exists, it tells the user to choose a different name.
2. If the name doesn’t exist, a hidden template sheet (templateSheet) is copied and pasted after the homepage sheet (indexSheet) and renamed based on the user input.
3. A table on the homepage gets a new row and a hyperlink to the new sheet is added.
4. There is additional code that adds values to cells on multiple sheets and formats that text.

All of this works perfectly for 21 runs. On the 22nd run, without fail, the automation error pops up and Excel crashes.

This happens on windows with Excel 2010, 2011, and 2016 (I’ve yet to test other versions on Excel) on a range of Windows versions. Bizzarly, the file works PERFECTLY on my 2013 MacBook pro with Excel 2011.. no errors at all.

The code I provide at the end of this post is the majority of the code within the file. At first, I thought it may be a memory issue but I think this is a pretty simple file, something excel and my desktop should be able to handle.

What I’ve done so far to try to fix it:

• Option explicit
• Keep templateSheet visible at all times
• Create a separate Excel template file and call that from the userform
• Changed .Activate and .Select to defined ranges
• Copy and paste the new template sheet without specifying where to put it
• Made sure all calls to sheets included specific «path» (ThisWorkbook.)

Inefficient workaround:

The only thing that prevents this error is code to save, close, and reopen the file. Obviously, this is time consuming and not efficient. I found this code online:

    wb.Save
    Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(filePath)
    wb.Close (True)

Finally:

As I stated, I am new to VBA, coding, and this site. Any suggestions to my code, relevant to this issue or not, are greatly appreciated. I have included all the code from my UserForm.

Private Sub OkButton_Click()

'Dont update the screen while the macro runs
Application.ScreenUpdating = False

    'Sheet and workbook variables
    Dim wb As Workbook
    Dim indexSheet As Worksheet, templateSheet As Worksheet
    Dim templateCopy As Worksheet, newSheet As Worksheet

    'Table and new row variables
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    'Variables to group shapes based on
    'need to hide or show them
    Dim hideShapes() As Variant, showShapes() As Variant
    Dim hideGroup As Object, showGroup As Object

    'Misc variables
    Dim i As Integer
    Dim exists As Boolean
    Dim filePath As String

    'Variables to assign ranges
    Dim scenarioRng As Range
    Dim traceabilityFocus As Range
    Dim testCaseRng As Range
    Dim statusRng As Range
    Dim newSheetTestCaseRng As Range
    Dim newSheetStatusRng As Range
    Dim newSheetFocus As Range
    Dim newSheetDateRng As Range

    'Create array of shapes based on visibility rules
    hideShapes = Array("TextBox 2", "Rectangle 1")
    showShapes = Array("TextBox 15", "TextBox 14", "TextBox 13", "TextBox 11", "StatsRec", "Button 10")

    'To reference Traceability Matrix sheet
    Set indexSheet = ThisWorkbook.Sheets("Traceability Matrix")
    'To reference Template sheet
    Set templateSheet = ThisWorkbook.Sheets("TestCase_Template")
    'To reference traceability matrix table
    Set Tbl = indexSheet.ListObjects("TMatrix")
    'Set hideShapes to a hide group
    Set hideGroup = indexSheet.Shapes.Range(hideShapes)
    'Set show shapes to a show group
    Set showGroup = indexSheet.Shapes.Range(showShapes)
    'To reference this workbook
    Set wb = ThisWorkbook
    'Get file path of this workbook and set it to string
    filePath = wb.FullName


    'If the userform fields are empty then show error message
    If ScenarioNameBox.Value = "" Or TestCaseNameBox.Text = "" Then
            MsgBox ("Please complete both fields.")
    'If the userform fields are completed and a worksheet with
    'the same name exists, set boolean to true
    Else
        For i = 1 To Worksheets.Count
        If ThisWorkbook.Worksheets(i).Name = TestCaseNameBox.Value Then
            exists = True
    End If
    'Iterate through all worksheets
    Next i

    'If test case name already exists, show error message
    If exists Then
        MsgBox ("This test case name is already in use. Please choose another name.")
    'If test case name is unique, update workbook
    Else
        'Copy template sheet to after traceability matrix sheet
        templateSheet.Copy After:=indexSheet 'LOCATION OF ERROR!!!
        'Ensure template sheet is hidden
        templateSheet.Visible = False

        'To reference copy of template
        Set templateCopy = ThisWorkbook.Sheets("TestCase_Template (2)")

        'Rename template sheet to the test case name
        templateCopy.Name = TestCaseNameBox.Value
        'To reference re-named template sheet
        Set newSheet = ThisWorkbook.Sheets(TestCaseNameBox.Value)
        'Show new sheet
        newSheet.Visible = True

        'Set focus to traceability matrix
        Set traceabilityFocus = indexSheet.Range("A1")

        'Add a new row
        Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

        'Set ranges for cells in traceability table
        Set scenarioRng = indexSheet.Range("B" & NewRow.Range.Row)
        Set testCaseRng = scenarioRng.Offset(0, 1)
        Set statusRng = testCaseRng.Offset(0, 1)

        'Set scenario cell with name and format
        With scenarioRng
            .FormulaR1C1 = ScenarioNameBox.Value
            .HorizontalAlignment = xlGeneral
            .Font.Name = "Arial"
            .Font.Size = 12
        End With

        'Set test case cell with name, hyperlink to sheet, and format
        With testCaseRng
            .FormulaR1C1 = TestCaseNameBox.Value
            .Hyperlinks.Add Anchor:=testCaseRng, Address:="", SubAddress:=newSheet.Name & "!A1", TextToDisplay:=newSheet.Name
            .HorizontalAlignment = xlGeneral
            .Font.Name = "Arial"
            .Font.Size = 12
        End With

        'Set trial status as Incomplete and format
        With statusRng
            'Set new test case to "Incomplete"
            .Value = "Incomplete"
            .Font.Name = "Arial"
            .Font.Size = 12
            .Font.Color = vbBlack
        End With

        'Show or hide objects
        hideGroup.Visible = False
        showGroup.Visible = True

        'Set ranges for cells in test case table
        Set newSheetTestCaseRng = newSheet.Range("C2")
        Set newSheetStatusRng = newSheet.Range("C12")
        Set newSheetDateRng = newSheet.Range("C5")

        'Insert test case name into table
        newSheetTestCaseRng.Value = TestCaseNameBox.Value
        'Add todays date to Date Created
        newSheetDateRng.Value = Date
        'Set status to "Incomplete"
        newSheetStatusRng.Value = "Incomplete"
        'End with cursor at beginning of table
        newSheet.Activate
        Range("C3").Activate


        'wb.Save
        'Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(filePath)
        'wb.Close (True)


        'Close the userform
        Unload Me

        End If

    End If

    'Update screen
    Application.ScreenUpdating = True

End Sub

===========================================================================

Update:

Using the code provided by @DavidZemens the error acts differently. Normally, the userform closes after each sheet is created. @DavidZemens suggested leaving the form open so the user can make as many sheets as they need in one go. This method allows me to create a seemingly unlimited amount of sheets WITHOUT error. Read: at the 22 sheet mark, there is no error.

However, if I manually close the userform after making more than 22 sheets and then reopen it to create a new sheet, the automation error pops up again and excel crashes.

The new code that causes this error is here:

 With templateSheet
        .Visible = xlSheetVisible
        .Copy Before:=indexSheet 'ERRORS HERE!!
        .Visible = xlSheetVeryHidden

Another thing worth mentioning: In the project explorer it lists all my sheets with their names. But, there are extra sheets in there that have the workbook icon next to them. I did not create any of there workbooks or worksheets and my macros do not create or even call any workbook other than ThisWorkbook.

VBA Automation Error

In this Article

This tutorial will explain what a VBA Automation Error means and how it occurs.

Excel is made up of objects – the Workbook object, Worksheet object, Range object and Cell object to name but a few. Each object has multiple properties and methods whose behavior can be controlled with VBA code. If the VBA code is not correctly programmed, then an automation error can occur. It is one of the more frustrating errors in VBA as it can often pop up for no apparent reason when your code looks perfectly fine!

(See our Error Handling Guide for more information about VBA Errors)

Referring to a Variable no Longer Active

An Automation Error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.

When we run the code above, we will get an automation error. This is due to the fact that we have opened a workbook and assigned a variable to that workbook. We have then closed the workbook but in the next line of code we try to activate the closed workbook. This will cause the error as the variable is no longer active.

If we want to activate a workbook, we first need to have the workbook open!

Memory Overload

This error can also sometimes occur if you have a loop and you forget to clear an object during the course of the loop. However, it might only occur sometimes, and not others- which is one of the reasons why this error is can be so annoying.

Take for example this code below:

The variable is declared as an Object, and then the SET keyword is used to assign an image to the object. The object is then populated with an image and inserted into the Excel sheet with some formatting taking place at the same time. We then add a loop to the code to insert 100 images into the Excel sheet. Occasionally this causes an automation error, but sometimes it doesn’t – frustrating, right?

The solution to this problem is to clear the object variable within the loop by setting the object to NOTHING – this will free the memory and prevent the error.

DLL Errors and Updating Windows

Sometimes the error occurs and there is nothing that can be done within VBA code. Re-registering DLL’s that are being used, making sure that our Windows is up to date and as a last resort, running a Registry Check as sometimes the only things that may work to clear this error.

A good way of avoiding this error is to make sure that error traps are in place using the On Error Go To or On Error Resume Next routines.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

Excel automation fails second time code runs

Symptoms

While running code that uses Automation to control Microsoft Excel, one of the following errors may occur:

In Microsoft Excel 97 and in later versions of Excel, you receive one of the following error message:

Error message 1

Run-time error ‘1004’:
Method ‘ ‘ of object ‘_Global’ failed

Error message 2

Application-defined or object-defined error

In Microsoft Excel 95, you receive one of the following error messages:

Error message 1

Run-time error ‘-2147023174’
OLE Automation error

Error message 2

Run-time error ‘462’:
The remote server machine does not exist or is unavailable.

Cause

Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than one time.

Resolution

To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.

Status

This behavior is by design.

More Information

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

The following steps illustrate how to reproduce this issue and how to correct the issue.

Steps to reproduce the behavior

Start a new Standard EXE project in Visual Basic. Form1 is created by default.

On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.

Place a CommandButton control on Form1.

Copy the following code example to the Code Window of Form1.

On the Run menu, click Start, or press F5 to start the program.

Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.

Click the CommandButton control again. Notice that you receive one of the error messages that are discussed in the «Symptoms» section.

Note The error message occurs because the code refers to the method of the cell without preceding the call with the
xlSheet object variable.

Stop the project and change the following line of code:

Change the line of code to resemble the following line of code.

Run the program again. Notice that you can run the code multiple times without receiving an error message.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

167223 Microsoft Office 97 Automation Help file available

189618 You may receive the «Run-time error ‘-2147023174’ (800706ba)» error message or the «Run-time error ‘462’» when you run Visual Basic code that uses Automation to control Word

Источник

Vba automation error что это

Вроде как на ровном месте (т.е. до сегодня пару лет работало) вот такая строчка кода:

Set fs = CreateObject(«Scripting.FileSystemObject»)

стала вызывать вот такую ошибку:

Run-time error ‘-2147024770 (8007007e)’:
Automation error

Собственно, это использовалось для последующей проверки ссуществования файла (FileExists). И вот.
Подскажите, пожалуйста, что тут может быть? Библиотеки слетели? Куда смотреть?

Originally posted by rtttv
[b]Собственно, это использовалось для последующей проверки ссуществования файла (FileExists). И вот…

Так всегда происходит , когда люди пренебрегают родными функциями и используются посторонние библиотеки.

Посмотрите, есть ли ссылки на библиотеки Microsoft Scripting Runtime

Спасибо. Именно это — Microsoft Scripting Runtime! Сбилась (в системе) регистрация библиотеки scrrun.dll. А в Excel (Tools — References) «галка» на неё и не стояла. И сейчас не стоит, но всё работает!Т.е. системные библиотеки сами подтягиваются? А вообще в такой ситуации нужно устанавливать reference?

Так всегда происходит , когда люди пренебрегают родными функциями и используются посторонние библиотеки.

Originally posted by rtttv
[b]А в Excel (Tools — References) «галка» на неё и не стояла. И сейчас не стоит, но всё работает

Это кстати, позволяет избежать проблем, связанных с различными версиями (при передаче файла другим лицам)

что Naeel Maqsudov предлагал . Application.Run

Первоисточник конечно всегда лучше, но это действительно было давно, к тому же, на другом форуме, да и ответ был довольно краток и сводился к тому, что вызывать процедуры можно так :

Источник

Excel automation fails second time code runs

Symptoms

While running code that uses Automation to control Microsoft Excel, one of the following errors may occur:

In Microsoft Excel 97 and in later versions of Excel, you receive one of the following error message:

Error message 1

Run-time error ‘1004’:
Method ‘ ‘ of object ‘_Global’ failed

Error message 2

Application-defined or object-defined error

In Microsoft Excel 95, you receive one of the following error messages:

Error message 1

Run-time error ‘-2147023174’
OLE Automation error

Error message 2

Run-time error ‘462’:
The remote server machine does not exist or is unavailable.

Cause

Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than one time.

Resolution

To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.

Status

This behavior is by design.

More Information

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

The following steps illustrate how to reproduce this issue and how to correct the issue.

Steps to reproduce the behavior

Start a new Standard EXE project in Visual Basic. Form1 is created by default.

On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.

Place a CommandButton control on Form1.

Copy the following code example to the Code Window of Form1.

On the Run menu, click Start, or press F5 to start the program.

Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.

Click the CommandButton control again. Notice that you receive one of the error messages that are discussed in the «Symptoms» section.

Note The error message occurs because the code refers to the method of the cell without preceding the call with the
xlSheet object variable.

Stop the project and change the following line of code:

Change the line of code to resemble the following line of code.

Run the program again. Notice that you can run the code multiple times without receiving an error message.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

167223 Microsoft Office 97 Automation Help file available

189618 You may receive the «Run-time error ‘-2147023174’ (800706ba)» error message or the «Run-time error ‘462’» when you run Visual Basic code that uses Automation to control Word

Источник

Vba automation error что это

прошу помочь!
Выскочила такая «бяка» при запуске ранее рабочего макроса. Подозреваю, что что-то с настройками самого Ёкселя (либо ВБА), поскольку эксельный файл с этим макросом даже не хочет сохраняться («Документ не сохранен»). Хотя файлы с другими макросами вполне работоспособны. Офис-2003, в настройке безопасности доверялки подключены.

зы. Сколько уж зарекался оставлять свой комп коллегам

Automation error (Error 440)

When you access Automation objects, specific types of errors can occur. This error has the following cause and solution:

An error occurred while executing a method or getting or setting a property of an object variable. The error was reported by the application that created the object.
Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement.

ошибка при установке (получении) свойств обьекта.
рекомендуют использовать On Error Resume Next чтобы в свойствах Err получить более детальную информации о произошедшем.

автору на заметку — это не решит проблемы, но должно поднять настроение. при первом прочтении — я плакал.

Источник

One of the more frustrating errors to occur in VBA is the Automation Error.  It can often pop up for no apparent reason despite your code looking perfect.

Reasons for This Error

There are a variety of reasons that this can occur.

Microsoft Office is made up of Objects – the Workbook object, Worksheet Object, Range object and Cell object to name just a few in Excel; and the Document Object in Word.  Each object has multiple properties and methods that are able to be programmed to control how the object behaves. If you do not program these methods correctly, or do not set a property correctly, then an automation error can occur.

It can be highly annoying as the code will run perfectly for a while, and then suddenly you’ll see this!

You may get a number of different messages for this error.

The Object Has Disconnected from Its Client

An automation error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.  Make sure any object variables that you are referring to in your code are still valid when you call the property and methods that you are controlling them with.

Excel Error Load Form

An automation error could occur within Excel if you load a form while another object (like the worksheet or range object) are still referenced.  Remember to set your object references to NOTHING after you have finished writing the code to control them with.

Error Hiding/Unhiding Sheets In Excel

There are 3 ways to control the visible property in an Excel sheet in VBA – xlSheetVisible, xlSheetHidden or xlSheetVeryHidden.   An automation error could occur if you are trying to write information to a sheet which has the xlVeryHidden property set – make sure you set the sheet to visible in the code before you try to write anything to the sheet using Visual Basic Code.

Error 429 and 440

If you receive either of these errors, it can mean that your DLL files or ActiveX controls that you might be using are not correctly registered on your PC, or if you are using an API incorrectly.  It can also occur if you are running using 32-bit files on a 64-bit machine, or vice versa. 

Automation Error When Running Macro Enabled Workbook Over A Network

If you have a workbook open over a network, and you get an automation error when you run a macro, check your network connections – it could be that the network path is no longer valid.   Check you network paths and connections and make sure that they are all still working.

ADODB Automation Error

This error can occur when you are programming within Microsoft Access and are using the ADODB.Connection object.  It can be caused by any number of reasons from conflicting DLL files to a registry corruption, or simply that you did not set the recordlist object to nothing when you were finished using it!

Common Causes and Things to Check

Here are some reasons you might be getting the error. Perhaps you are…

• Trying to write information to hidden sheets or cells.
• Trying to write information to other documents or workbooks.
• Referring to objects that do not exist.
• Needing to install an update to Office, or the correct .Net framework.
• Loading objects (like pictures) into memory using a loop, and failing to clear the memory between each load (set Pic = Nothing).
• A Corrupt Registry – this is a hard one, as it often means that you need to remove Office entirely from your machine and then re-install it.  

Ways to Solve It

Error Trapping

Error trapping is one of the best ways to solve this problem.  You can use On Error Resume Next – or On Error GoTo Label – depending on your needs at the time.  If you want the code to carry on running and ignore the error, use On Error Resume Next.  If you want the code to stop running, create an error label and direct the code to that error label by using On Error GoTo label.

Clear the Memory

Another way to solve the problem is to make sure you clear any referred objects either in a loop or at the end of your code.  For example, if you have referred to a picture object, set the picture object to NOTHING, or if you have referred to a Worksheet object, set the Worksheet object to NOTHING once you have run the code for that picture of worksheet.

The code example below will insert a picture into a worksheet.  It may or may not give you an automation error!

Sub InsertPicture()
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:dataimage" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Next i
End Sub

The variable is declared as an OLEObject, and then the SET keyword is used to assign an image to the object.  The object is then populated with an image and inserted into the Excel sheet – some formatting taking place at the same time. I have then added a loop to the code to insert 100 images into the Excel sheet.  Occasionally this causes an automation error, but sometimes it doesn’t – frustrating, right?

The error often occurs when Excel runs out of memory – assigning an object over and over again without clearing the object could mean that Excel is struggling with memory and could lead to an automation error.

We can solve this problem 2 ways:

1. Set the object to NOTHING after it is inserted each time
2. Add an error trap to the code.

Sub InsertPicture()
On Error Resume Next
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:dataimage" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Set shp = Nothing
	Next i
End Sub

Make sure your PC is up to date

A third way to solve the problem is to install the required update to Office or Windows, or the latest version of the .Net framework.  You can check to see if there are any updates available for your PC in your ‘Check for updates’ setting on your PC.

In Windows 10, you can type ‘updates’ in the search bar, and it will enable you to click on “Check for updates“.

It is always a good idea to keep your machine up to date.

Run a Registry Check

If all else fails, there are external software programs that you can download to run a check on the registry of your PC.

As you can see from above, this error often has a mind of its own – and can be very frustrating to solve.  A lot of the times trial and error is required, however, writing clean code with good error traps and referring to methods, properties and objects correctly often can solve the problem.

Just like any other programming language, VBA has no luck when it comes to errors and you have to deal with them, no matter what. They may come from different sources, like bad coding, impossible operations (like zero division), or unexpected errors.

The best way to deal with it is to have a proper understanding of all the possible outcomes of the result that you could get with code. Look at the below example where we have a VBA code that calculates the square root of the number using the value which you have in the selected cell.

Sub Square_Root()
ActiveCell.Value = ActiveCell.Value ^ (1 / 2)
End Sub

But if the active cell has a value other than a number, you’ll get a run-time error, just like below.

Error Settings in VBA (Error Trapping)

In the VBA option, you can configure the setting to deal with errors before you start writing codes. To open the VBA settings, go to Tools ➤ Options ➤ Generals ➤ Error Trapping. Here you have three options which you can use.

• Break on All Errors: If you have this option activated, VBA will stop the code for all types of errors even if you have used all kinds of error handling techniques.
• Break-in Class Module: With this option, VBA will stop all your codes that are not handled by any technique. And if you’re using objects such as Userforms, it will also break within those objects and highlight the exact line where the error is.
• Break on Unhandled Errors: This is the default setting that helps you to know about all the errors where you are not using any error handling technique and stop the code for all the unhandled errors. (But, if you’re using objects such as Userforms, this will not highlight the line causing the error in the object but will only highlight the line that’s referring to that object).

Types of VBA Errors

To understand VBA errors, you can split them into four categories, and below is the explanation of these types of errors.

1. Syntax Errors

While writing VBA code you need to follow a particular Syntax and when you skip it or don’t write it in the way it should be you can face SYNTAX error (also called Language error). It’s like typos that you do while writing your codes.

Well, VBA helps you by pointing out these errors by showing an error message. You just need to make sure you have “Auto Syntax Check” activated in your VB editor.

Go to the Tool ➤ Options and make sure to tick the “Auto Syntax Check”. With this, whenever you make a SYNTAX error, VBA will show an error message.

But if “Auto Syntax Check” is disabled VBA still highlights the line of code with the error but won’t show the error message.

2. Compile Errors

It comes when you write code to perform an activity, but that activity is not valid or can’t be performed by VBA. The best example is where you have a code using the IF statement but missed to add END IF at the end of the statement and now when you run this VBA will show you a compilation error message.

Apart from this, there are some other examples of compile errors:

• Using For without Next (For Next).
• Select without End Select (Select Case).
• Not Declaring a Variable when you have “Option Explicit” enabled.
• Calling a Sub/Function that does not exist.

3. Runtime Errors

A runtime error occurs at the time of executing the code. Remember the example, I shared with you above when the code calculated the square root of a number.

When a runtime error occurs while running code, it stops the code and shows you the error dialog box and that error box talks about the nature of the error you have. Let’s say you have written a code that opens a workbook from the location which you have specified but now that workbook is relocated or deleted by someone.

So, when you run the code, VBA will show you a runtime error as it can’t find that file on that location. The message you get in a run time error describes the reason which helps you to understand the reason for the error.

And when a runtime error occurs it stops the execution of the code. If you click on the “Debug” button it shows you the line of code that has that error by highlighting it yellow. Or you can click on the “End” button to stop the code to execute and close the error message.

4. Logical Error

It’s not an error but a mistake while writing code. These types of errors sometimes can give you nuts while finding them and correcting them.

Let’s say you write code and while declaring a variable you used the wrong data type, or you have used the wrong calculation steps. In this case, your code will work fine, and you won’t find this error easily. The best way to deal with this kind of problem is to run each line of code one by one.

Using Debug Tools in VBA

VBA provides you a set of tools to debug your code and remove bugs from your codes.

1. Compile VBA Project

In Visual Basic Editor, there’s an option that you can use instantly after completing your code. These compile options scan each line of your code and show a message box if there is an error in your code.

Note: Compile VBA option only traces Syntax and Compile errors, not runtime errors as these errors only rise when a code is running. To use Compile VBA Project, go to ➤ Debug ➤ Compile VBA Project.

Once you run “Compile VBA Project” and you have no error in your code, the options will be greyed out.

2. Run Each Line of Code One by One

This is how I do it. When I complete a code, I simply run it line by line to check if there’s an error occurring. It may take time, but it helps you to get to about all the errors (Syntax, Compile, and Run-Time).

On the “Debug Toolbar”, there’s a button “Step In” which you can use to execute a code line by line or you can simply press F8 to execute a single line and then press it again to execute the next line in the code.

Using “On ERROR” Statement to Handle VBA Errors

It’s important to check your codes and find possible errors in all the debugging ways you have. But, the best and most effective way is to create error-handling statements that can deal with an error and make your code flawless while executing. Let’s learn about these statements. When an error occurs in a VBA code the best possible ways to handle that error can be:

• Let the VBA ignore the error and execute the code
• Let a special set of statements to run when an error occurs.

In both solutions, you can use “On Error” statements. Below four “On Error” statements that you can use. And now, let’s look at each statement one by one.

1. On Error Resume Next

This simple line of code lets VBA continue executing the code despite the occurrence of an error. The IDEA is simple: Move to the next line of the code if there’s an error found somewhere while executing.

In the below code, you have two lines of code:

• The first line says the value  of cell A1 is 25 divided by 0
• And the second line says the value cell A2 is 10 divided by 5

Now there’s a problem with the code which you have inline one. As you know if you divide anything with 0 the result will be an error.  So, when you run this code VBA will show an error message “Run-time error ‘11’ Division by Zero” and stop the execution.

But when you add the “On Error Resume Next” at the very beginning of the code and run the code, VBA simply skips that line of code where the error occurs and continues with the second line and add that value in cell A2.

Sub myDivide()
On Error Resume Next
    Range("A1").Value = 25 / 0
    Range("A2").Value = 10 / 5
End Sub

So, whenever you want your code to get executed despite an error occurring anywhere simply use the “On Error Resume Next” statement in your code.

But here’s one more thing you need to note down: It will only skip errors that occur after it.

Let’s say if an error occurs at line 5 and you have added “On Error Resume Next” on line 8 then it would not skip that error.  So, the best way is to add it as the first line of the code in the procedure.

2. On Error GoTo 0

It’s the default behavior of VBA that when an error occurred it stops the execution of the code.

Well, using “On Error GoTo 0” make no difference in your code. VBA will simply stop the code and show a message with a description of the error. Then why would I bother to use it? Smart Question. Let’s use the example you have used above in “On Error Resume Next”.

In this code whenever an error will occur VBA will resume to the next line of code and run it and you won’t see any error message. But let’s say you have more lines in your code and you don’t want to surpass those lines if there’s an error in the code.

So, if you enter “On Error GoTo 0” after the second line of code it will restore the VBA’s default error handler which shows error messages each time an error occurs.

3. On Error GoTo [Label]

Think about a place in a building where you can head up in an emergency. In the same way, using “On Error GoTo [Label]”, you can simply create a separate block of code in your main code to deal with an error.

Actually, “On Error GoTo [Label]” is a far better and more convenient way to deal with errors. In the below code, you have “On Error GoTo Oh!Error” now in this line statement the word “Oh!Error” is the label.

If you look at the end of the code where you have a specific starting with the label name and then a code for a message box with a message about the code.

Now, what happens if an error occurs the VBA will jump to the label “Oh!Error” and run the block of code which you have after that label.

But there’s one thing you need to take care of: If an error doesn’t occur even then the label you have in your code will get executed. There are two things you need to do:

• First, make sure to add your Error label at the end of the code.
• Second, add an “Exit Sub” before the error label.

With this, you’ll benefit in both situations. Let’s say if an error occurs and VBA jumps to the label you specified there would only be code from the label itself to code. And if an error doesn’t occur “Exit Sub” statement which you have before the label will exit the procedure without executing the error label.

4. On Error GoTo -1

Before we get into this, let me share something with you.  When an error occurs in a code VBA stores that error log in its memory and only clears it when the routine ends.

O VBA! Live in Present

To deal with the second error in a VBA code you need to clear the first error from VBA’s memory. In the below code, you have two “On Error GoTo [Label]” statements that deal with errors from two different blocks of code.

But if you run this code, when the second error VBA won’t jump to the label which you have defined and instead show the error message “Type Mismatch”.

Sub Square_Root()
On Error GoTo myError1
Range("A1").Value = Range("A1").Value ^ (1 / 2)
myError1:
MsgBox "There's some problem with the value you have in the cell A1."
On Error GoTo myError2
Range("A2").Value = Range("A2").Value ^ (1 / 2)
myError2:
MsgBox "There's some problem with the value you have in the cell A2."
End Sub

To fix this problem you can use “On Error GoTo -1” which makes VBA remove the current error from its storage memory.

Sub Square_Root()
 On Error GoTo myError1
 Range("A1").Value = Range("A1").Value ^ (1 / 2)
 myError1:
 MsgBox "There's some problem with the value you have in the cell A1."
 On Error GoTo -1
 On Error GoTo myError2
 Range("A2").Value = Range("A2").Value ^ (1 / 2)
 myError2:
 MsgBox "There's some problem with the value you have in the cell A2."
 End Sub

Now when you run this code, “On Error GoTo -1” removes the error from the memory and VBA deals with the error in the second statement as you want.

What Else Do I Need to Know to Handle Errors in VBA?

Apart from using error handling techniques, there are a few other things that you can use to deal with errors in a better way.

Err Object

When an error occurred while executing of code, you can use the Err object to get details about that error. There are a few properties and methods which you can use with Err object. Let’s learn them one by one.

Properties

Below are the properties which you can use with the Err object:

• Err.Number: With an error occurred there’s a number stored in the Err Object. In the below code, when occurred the message box will show the error number.  
• Err.Description: This property shows the description of the error which can help you to understand the reason for the error.
• Err.Source: This property shows you in which project the error has occurred.
• Err.HelpContext: This property returns the help context id for the error in the help file.
• Err.HelpContext: This is a string value for the location of the help file.

Normally when you are dealing with errors using error handling techniques you won’t be using the Err Object that much in your codes. But below is a simple example to use it.

Sub Square_Root()
On Error GoTo myError1
    Range("A1").Value = Sqr(Range("A1").Value)
Exit Sub
myError1:
    MsgBox "There's some problem with the value you have in the cell A1." & vbCrLf & _
                "Error Number: " & Err.Number  & vbCrLf & _
                "Error Description: " & Err.Description
End Sub

When you run the above code, and if an error occurred, it will show a message box with the error number and description of the error.

Methods

With Err Object there are two methods that you can also use.

• Err.Clear: This method clears the error number and error description from VBA’s memory (It’s different from “On Error GoTo -1” as it doesn’t completely reset the error).
• Err.Raise: With this method, you can generate a run time error in your code intentionally, and below is the syntax which needs to follow:

Err.Raise [number], [source], [description], [helpfile], [helpcontext]

Quick Tips on Error Handling

Here are a few quick tips which you can use to deal with VBA errors in a better way.

• Use “On Error Resume Next” only when you know for sure about an error to occur and it’s OK to skip the line of code with an error and it’s safe to skip to the next line.
• The best way to deal with run-time errors is by using “Error Handler” with “On Error GoTo [Label]”. This ensures that whenever the error occurs you will know about it, but it won’t show that nasty error message.
• Whenever you use the error handler make sure to use “Exit Sub” before it.

There’s More

Below are some of the links which could be useful for you to learn more about error handling in VBA, make sure to check out all of these:

Type Mismatch (Error 13) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400 | VBA Subscript Out of Range Runtime Error 9