Vba + on error goto

vba + on error goto

On Error GoTo 0 This statement is the default error handler which interrupts execution and displays a dialog box. If you switch to a. When an error occurs with On Error GoTo 0, VBA will stop executing code and display its standard error message box. vba runtime error 13. The On Error GoTo statement instructs VBA to jump to a line label and enter "error handling mode" whenever an unexpected error occurs at runtime.

Agree: Vba + on error goto

SQL MIRRORING ERROR 1418
Android imageview.setimageuri outofmemoryerror
Vba + on error goto
Sharp error code h5-01
Vba + on error goto

VBA Error Handling – A Complete Guide

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

Contents

A Quick Guide to Error Handing

ItemDescription
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.
Obsolete.
Error StatementSimulates an error. Use Err.Raise instead.

 

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

vba error handling video

 

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 vba + on error goto 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 + on error goto, VBA will vba + on error goto 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 missingIf a > b ' equals is missing after iFor 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 vba + on error goto you select Debug->Compile, vba + on error goto, 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->Compileit 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, vba + on error goto. 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.

' https://excelmacromastery.com/Sub OpenFile() Dim sFile AsString sFile = "C:\docs\data.xlsx"' Use Dir to check if file existsIf Dir(sFile) = ""Then' if file does not exist display message MsgBox "Could not find the file " & sFile ExitSubEndIf' Code will only reach here if file exists Workbooks.Open sFile EndSub

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 vba + on error goto 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 vba + on error goto 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 AsLong x = 6 y = 6 / 0 x = 7 EndSub

The second assignment line results in a divide by zero error, vba + on error goto. 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 avira error 559 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

' https://excelmacromastery.com/Sub UsingResumeNext() OnErrorResumeNextDim x As Long, y AsLong x = 6 y vba + on error goto 6 / 0 x = 7 EndSub

 

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 ResumeNext' Requires Reference:' Microsoft Outlook 15.0 Object LibraryDim Outlook As Vba + on error goto Set Outlook = New Outlook.Application If Outlook Is NothingThen MsgBox "Cannot create Microsoft Outlook session." _ & " The email will not be sent."ExitSubEndIfEndSub

In this code we are checking to see if Microsoft Outlook is available on a computer. All vba + on error goto 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 AsLong x = 6 y = 6 / 0 x = 7 Done: ExitSub eh: MsgBox "The following error occurred: " & Err.Description EndSub

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 vba + on error goto 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, vba + on error goto, 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: ExitSub eh: ' generate "Application-defined" error Error (1034) EndSub

 

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: ExitSub eh: On Error Goto eh_other ' generate "Application-defined" error Error (1034) ExitSub eh_other: Debug.Print"eh_other " & Err.Description EndSub

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

In the code below we add this line and vba + on error goto 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: ExitSub eh: ' clear errorOn Error Goto -1 On Error Goto eh_other ' generate "Application-defined" error Error (1034) ExitSub eh_other: Debug.Print"eh_other " & Err.Description EndSub

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 vba + on error goto 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 AsLong' Go to eh label if errorOn Error Goto eh ' this will ignore the error on the following lineOn Error ResumeNext x = 1 / 0 ' this will display an error message on the following lineOn Error Goto 0 x = 1 / 0 Done: ExitSub eh: Debug.Print Err.Description EndSub

 

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, vba + on error goto.

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:

PrivateSub Main() On Error Goto eh Dim i AsLongFor i = 1 To 3 ' Generate type mismatch error Error 13 Next i done: ExitSub eh: Debug.Print i, vba + on error goto, Err.Description EndSub

 
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:

PrivateSub Main() On Error Goto eh Dim i AsLongFor i = 1 To 3 ' Generate type mismatch error Error 13 continue: Next i done: ExitSub eh: Debug.Print i, Err.Description On Error Goto -1 ' clear the errorGoto continue ' return to the codeEndSub

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

PrivateSub Main() On Error Goto eh Dim i AsLongFor i = 1 To 3 ' Generate type mismatch error Error 13 continue: Next i done: ExitSub eh: Debug.Print i, Err.Description ' clear the error and return to the codeResumeNextEndSub

 

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 AsLong masm fatal error lnk1123 total = "aa" Done: ExitSub eh: Debug.Print"Error number: " & Err.Number _ & " " & Err.Description EndSub

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 vba + on error goto 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 AsLong val = "aa" Done: ExitSub eh: Debug.Print Erl EndSub

This is because vba + on error goto 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 AsLong 20 val = "aa" Done: 30 ExitSub eh: 40 Debug.Print Erl EndSub

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

' https://excelmacromastery.com/PublicConst ERROR_INVALID_DATA AsLong = vbObjectError + 513 Sub ReadWorksheet() On Error Goto eh If Len(Sheet1.Range("A1")) <> 5 Then Err.Raise ERROR_INVALID_DATA, "ReadWorksheet" _ vba + on error goto "The value in the cell A1 must have exactly 5 characters."EndIf' continue on if cell has valid dataDim id AsString id = Sheet1.Range("A1") Done: ExitSub eh: ' Err.Raise will send code to here MsgBox "Error found: " & Err.Description EndSub

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 AsLong' Continue if error as we will check the error numberOn Error ResumeNextFor i = 0 To 9 ' generate error for every second oneIf i Mod 2 = 0 Then Error (13) ' Check for errorIf Err.Number <> 0 Then count = count + 1 Err.Clear ' Clear Err once it is countedEndIfNextDebug.Print"The number of errors was: " & count EndSub

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 AsString sFilename = "C:\temp\logging.txt"' Archive file at certain sizeIf FileLen(sFilename) > 20000 Then FileCopy sFilename _ Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt")) Kill sFilename EndIf' Open the file to writeDim filenumber AsVariant filenumber = FreeFile Open sFilename For Append As #filenumber Print #filenumber, CStr(Now) & "," & sType & "," & sSource _ & "," & sDetails & "," & Application.UserName vba + on error goto Close #filenumber EndSub

You can use it like vba + on error goto Create unique error number' https://excelmacromastery.com/PublicConst ERROR_DATA_MISSING AsLong = 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"EndIf' other code here Done: ExitSub eh: Logger "Error", Err.Source, Err.Description EndSub

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 AsNew 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."EndIf Logger "Information", "ReadingData()", "Number of data items is " & coll.Count Logger "Information", "ReadingData()", "Finished reading data."EndSub

 

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 vba + on error goto they may exist in legacy code.

 

Error Function

The Error Function is used to print vba + on error goto 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 ExitSub eh: Debug.Print Err.Number, Err.Description EndSub

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

error-handling

 

The following code shows a simple implementation of this strategy:

' https://excelmacromastery.com/PublicConst ERROR_NO_ACCOUNTS AsLong = vbObjectError + 514 Sub BuildReport() On Error Goto eh ' If error in ReadAccounts then jump to error ReadAccounts ' Do something with the code Done: ExitSub eh: ' All errors will jump to here MsgBox Err.Source & ": The following error occured " & Err.Description EndSubSub ReadAccounts() ' EXPECTED ERROR - Can be handled by the code' Application can handle A1 being zeroIf Sheet1.Range("A1") = 0 Then Sheet1.Range("A1") = 1 EndIf' EXPECTED ERROR - cannot be handled by the code' Application cannot continue if no accounts workbookIf Dir("C:\Docs\Account.xlsx") = ""Then Err.Raise ERROR_NO_ACCOUNTS, "UsingErr" _ "There are no accounts vba + on error goto for this month."EndIf' UNEXPECTED ERROR - cannot be handled by the code' If cell B3 contains text we will get a type mismatch errorDim total AsLong total = Sheet1.Range("B3") ' continue on and read accountsEndSub

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.

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: ExitSub EH: vba + on error goto DisplayError Err.source, Err.Description, "Module1.Topmost", Erl EndSubSub Level1() On Error Goto EH Level2 Done: ExitSub EH: RaiseError Err.Number, Err.source, "Module1.Level1", Err.Description, Erl EndSubSub Level2() On Error Goto EH ' Error hereDim a AsLong a = "7 / 0" Done: ExitSub EH: RaiseError Err.Number, vba + on error goto, Err.source, "Module1.Level2", Err.Description, Erl EndSub

 

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

On Error statement

  • Article
  • 6 minutes to read

Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

Syntax

On Error GoToline
On Error Resume Next
On Error GoTo 0

The On Error statement syntax can have any of the following forms:

StatementDescription
On Error GoTolineEnables the error-handling routine that starts at line specified in the required lineargument.

The line argument is any line label or line number.

If a run-time error occurs, control branches to line, making the error handler active.

The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.
On Error Resume NextSpecifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred and execution continues. Use this form rather than On Error GoTo when accessing objects.
On Error GoTo 0Disables any enabled error handler in the current procedure.

If you don't use an On Error statement, any run-time error that occurs is fatal; that is, an error message is displayed and execution stops.

An "enabled" error handler is one that is turned on by an On Error statement; an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler vba + on error goto active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. Control returns to the calling procedure.

If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no inactive, enabled error handler is found, the error is fatal at the point at which it actually occurred.

Each time the error handler passes control back to a calling procedure, that procedure becomes the current procedure. After an error is handled by an error handler in any procedure, vba + on error goto, execution resumes in the current procedure at the point designated by the Resume statement.

Note

An error-handling routine is not a Sub procedure or Function procedure. It's a section of code marked by a line label or line number.

Error-handling routines rely on the value in the Number property of the Err object to determine the cause of the error. The error-handling routine should test or save relevant property values in the Err object vba + on error goto any other error can occur or before a procedure that might cause an error is called. The property values in the Err object reflect only the most recent error. The error message associated with Err.Number is contained in Err.Description.

On Error Resume Next causes execution to continue with the statement immediately following the statement that caused the run-time error, vba + on error goto, or with the statement immediately following the most recent call out of the procedure containing the On Error Resume Next statement. This statement allows execution to continue despite a run-time error. You can place the error-handling routine where the error would occur, vba + on error goto, rather than transferring control to another location within the procedure. An On Error Resume Next statement becomes inactive when another procedure is called, so you should execute an On Error Resume Next statement in each called routine if you want inline error handling within that routine.

Note

The On Error Resume Next construct may be preferable to On Error GoTo when handling errors generated during access to other objects. Checking Err after each interaction with an object removes ambiguity vba + on error goto which object was accessed by the code. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error (the object specified in Err.Source).

On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.

To prevent error-handling code from running when no error has occurred, place an Exit Sub, Exit Function, or Exit Property statement immediately before the error-handling routine, as in the following fragment:

Here, the error-handling code follows the Exit Sub statement and precedes the End Sub statement to separate it from the procedure flow. Error-handling code can be placed anywhere in a procedure.

Untrapped errors in objects are returned to the controlling application when the object is vba + on error goto as an executable file. Within the development environment, untrapped errors are only returned to the controlling application if the proper options are set, vba + on error goto. See your host application's documentation for a description of which options should be set during debugging, how to set them, and whether the host can create classes.

If you create an object that accesses other objects, you should try to handle errors passed back from them unhandled. If you cannot handle such errors, map the error code in Err.Number to one of your own errors, and then pass them back to the caller of your object. You should specify your error by adding your error code to the vbObjectError constant. For example, if your error code is 1052, assign it as follows:

Note

System errors during calls to Windows dynamic-link libraries (DLL) vba + on error goto Macintosh code resources don't raise exceptions and cannot be trapped with Visual Basic error trapping. When calling DLL functions, vba + on error goto, you should check each return value for success or failure (according to the API specifications), and in the event of a failure, check the value in the Err object's LastDLLError property. LastDLLError always returns zero on the Macintosh.

Example

This example first uses the On Error GoTo statement to specify the location of an error-handling routine within a procedure. In the example, an attempt to delete an open file generates error number 55. The error is handled in the error-handling routine, and control is then returned to the statement that caused the error. The On Error GoTo 0 statement turns off error trapping.

The On Error Resume Next statement is then used to defer error trapping so that the context for the error generated by the next statement can be known for certain. Note that Err.Clear is used to clear the Err object's properties after the error is handled.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Object Doesn’t Support this Property or Method (Error 438)

In Microsoft Excel VBA, error handling is one of the critical tasks. If you are a coder, you know the importance of error handling to build a perfect application. Any blunder in a statement can hamper your VBA code in a lot of ways. So, you must be cautious to handle those errors while executing a VBA code. There are a lot of run-time errors you can face while using the Vba + on error goto code in Excel. To solve one of them, we use the On Error Resume Next statement.

In this tutorial, you will learn to use the On Error Resume Next statement in Excel VBA. This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.


Download Practice Workbook


Error Handling in Excel VBA

While working with Microsoft Excel VBA, you will face a lot of errors in your sub-procedure. When VBA cannot execute a statement, it throws a run-time error.

Excel automatically deals with these errors, so when a run-time error emerges, it displays a default error vba + on error goto like the following:

Error Handling in Excel VBA

Now, you can deal with these variously with some VBA statements. I will discuss them boot0 done boot1 error later sections.

Read More:How to Fix #REF! Error in Excel (6 Solutions)


On Error Statements in VBA

To handle run time errors, we instruct Excel with the On Error statement. It decides what kind of operations we want to do next immediately. Basically, we disable these errors by these kinds of error handling.

We use three kinds of On Error statements(syntax) in Excel VBA.

  • On Error GoTo line
  • On Error Resume Next
  • On Vba + on error goto GoTo 0

When you find an error, use the On Error command. Because if you don’t utilize an On Error declaration, those run-time errors will be disastrous. It will show an error prompt and stop the execution.

When we use an On Error statement, we turn on an vba + on error goto error handler, vba + on error goto. The “active” error handler is an enabled handler that starts the error handling operation, vba + on error goto. If a mistake arises while an error handler is involved, the current method’s error handler can’t endure the error. After that, the control returns to the calling procedure.

If the calling process has an enabled error handler, it gets triggered to manage the error. If your calling system’s error handler is correspondingly engaged, control gives back via earlier calling procedures until error cannot open/read repomd.xml file for repository finds an enabled but inactive error handler, vba + on error goto. If it can not find any idle enabled error handler, this means the error is catastrophic at its occurring point.

Each time the error handler gives authority back to a calling procedure, that procedure evolves the existing procedure. Execution restarts in the present procedure at the moment selected by the Resume statement when an error handler handles errors in any procedure.


‘On Error Resume Next’ in VBA

Now, the On Error Resume Next statement tells VBA to ignore any lines of code having errors, and proceed immediately to the following line of code. After that, the Excel VBA codes will skip the line or lines vba + on error goto contain errors in them, and move on to the following sequence of code.

The On Error Resume Next statement forces implementation to resume with the command instantly pursuing the line of codes that induced the run-time error. This statement permits an execution to skip even if there is a run-time error. If you think a particular line of code can produce an error, place the error-handling routine there rather than putting it in another location within the procedure. The On Error Resume Next statement becomes idle when your code calls another procedure. So, when you need a matched error handling in that routine, you have to run an On Error Resume Next command in each named pattern.

It is reasonable when the line of code that vba + on error goto can skip is not necessary to the flourishing running of the macro. But remember, it can be detrimental if you use it incorrectly as it may provide unintended results.

Remember:

The On Error Resume Next statement doesn’t fix the runtime errors. It basically ignores the errors where your VB execution will resume from the statement which has generated the runtime error.

Take a look at the following code:

We tried to divide 5 with 0 and 1. Let’s run the code. It will show the following output:

It produces a run-time error. We can not divide a number by 0. When you debug the code, you will see the following:

‘On Error Resume Next’ in VBA

When the VB program finds an error, vba + on error goto, it immediately stops the procedure. It doesn’t execute the following line.

Now, let’s implement the On Error Resume Next statement before the error statement:

After running the code, you will see the following:

Excel vba ‘On Error Resume Next’ in VBA

As you can see, VBA ignores the line that produces the error and proceeds immediately to the following line of code. In this way, you can use the On Error Resume Next statement to handle the error in Excel VBA.


Examples of ‘On Error Resume Next’ in VBA

In the following sections, I am going to provide you with two examples of the On Error Resume Next statement that you can implement into your Excel worksheet using VBA. I recommend you learn and apply all these to your workbook. It will definitely increase your Excel knowledge.


1. ‘On Error Resume Next’ Statement to Hide Worksheets

Now, in this example, I will show you a VBA code that will hide all the worksheets of your active workbook.

Take a look at the following screenshot:

Here, we have four worksheets. We will hide all of them using the following VBA code:

When you execute the following code, you will see the following run-time error:

Examples of ‘On Error Resume Next’ in VBA

Excel shows this error because you can not hide all the sheets in a workbook. So, you have to ignore the error. To do this, you have to implement the On Error Resume Next statement in your line of code.

After completing the execution of the VBA code, you will see the following output:

Examples of ‘On Error Resume Next’ in VBA

In the end, you won’t see any errors after execution. So, our On Error Resume Next statement worked really fine in the VBA code.

Read More:Errors in Excel and Their Meaning (15 Different Errors)


2. VLOOKUP Function with ‘On Error Resume Next’ in VBA

In this example, I will show an example of the VLOOKUP function in VBA. Now, this VBA code also includes the On Error Resume Next statement.

Take a look at the following screenshot:

Here, you can see some people’s names and their ages. In the adjacent table, we will use the VLOOKUP to find the person’s name and age.

Type the following code to do this:

Now, run the macro. You will see the following error:

Now, this is a run-time error. Why does it happen? Take a look at the dataset again:

VLOOKUP Function with ‘On Error Resume Next’ in VBA

As you can see, there is no data for “Aaron” vba + on error goto “Emma”. That’s why it only executes the VLOOKUP for the first entry, vba + on error goto. After that, it stops the execution. Now, if you want to ignore the error and proceed to find the rest of the ages, use the On Error Resume Next statement.

After running the VBA code, you will see the following output:

VLOOKUP Function with ‘On Error Resume Next’ in VBA

As you can see, by applying the On Error Resume Next command, we ignored the error and found the rest of the persons’ ages. Our VBA code didn’t find any data of Aaron and Emma. That’s why it ignored those values and returned the rest of the values in the Excel worksheet.

Read More: [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet


Turn Off ‘On Error Resume Next’ with Excel VBA

Now, you may be in a situation where you want to ignore errors for a particular segment of the VBA code. Remember, if you use the On Error Resume Next statement in a VBA code, it will skip all the errors after that. Now, if you want to turn that off and enable the error handling for another segment, use On Error GoTo 0. It will enable error handling again.

Generic usage:

Sub error_handling()

To ignore errors

On Error Resume Next

// lines of codes

To turn on Error handling

On Error GoTo 0

//lines of codes

End sub

Take a look at the following VBA code:

We used the code previously for the VLOOKUP function. Here added an extra chunk of code to demonstrate you. Our code will speedcore and terror mp3 the errors while performing the VLOOKUP but it will activate the error handling after the On Error GoTo 0 statement.

Turn Off ‘On Error Resume Next’ with Excel VBA

Read More:Excel VBA: Turn Off the “On Error Resume Next”


VBA ‘On Error GoTo’

Previously I discussed handling the errors using the On Error method. Our whole article was all about On Error Resume Next. Now, there are also two types of error handling that I will discuss in the following sections.


1. VBA On Error GoTo 0

The On Error Goto 0 statement is Excel’s inbuilt setting if your codes don’t have an error handler in them. It basically implies that when VBA finds an error with On Error GoTo 0, it will halt running the code and show its traditional error message box.

On Error GoTo 0 statement basically turns off the error handling in the present procedure. It doesn’t define line 0 as the beginning of the error-handling code, even if the method incorporates a line numbered vba + on error goto a look at the following code:

We have already shown you this code, vba + on error goto. This code basically hides all the worksheets in your current workbook. Now, vba + on error goto, I indeed have an extra piece of code with On Error GoTo 0 to show the error. If you run the code, you will see the following:

VBA ‘On Error GoTo’

It shows this error because we cannot have sheets with the same name in the active workbook.


2. VBA On Error GoTo line

Now, you can also instruct Excel to run another segment of code if it finds any error by using the On Error GoTo line. It tells Excel to execute something after finding an error.

The line argument is any line tag or line number. If our code causes any run-time error, it will move to the line, causing the error handler active in the execution. Remember, your defined line must be in the exact procedure as the On Error statement; otherwise, it will cause a compile error.

Take a look at the following code:

You saw the code in the previous example. When we used On Error GoTo 0, it caused an error, vba + on error goto. But, here we replaced that with the On Error GoTo line statement.

Now, run the code and you will see the following:

VBA On Error GoTo line

As you can see, it doesn’t show us the standard error dialog box. Instead, it shows the custom message box that we created in the error_handler segment. When Excel finds any error, it jumps to the  error_handler segment and shows us the message box.

We also used an Exit Sub in the procedure. If there is no sheet named “VLOOKUP“, our VBA code will rename the active sheet. Then, we have to finish the execution here because we do not need to carry on to the error handler and display the message box.


VBA ‘On Error’ Not Working in Excel

Sometimes, no matter how hard you try, the On Error method won’t work. Previously, we implemented the On Error method to handle the errors. But, sometimes it will show errors even if you use On Error Resume Next or On Error GoTo 0. There might be several reasons in your code that you have to fix. I am not going to show you that.

The basic reason for VBA ‘On Error’ Not Working in Excel is turning on the “Break on All Errors” option in Excel.

Follow these steps to solve that:

📌 Steps

  • First, press Alt+F11 on your keyboard to open the VBA editor.
  • Now, click on Tools > Options.

  • After that, vba + on error goto, click on the General tab in the Options dialog box.

VBA ‘On Error’ Not Working in Excel

  • Here, you can see, “Break on All Errors” is already checked. It basically prevents you from handling the errors.
  • To change it, select the option “Break on Unhandled Errors” and click on OK.

VBA ‘On Error’ Not Working in Excel

I hope it will fix your problem of VBA “On Error” not working in Excel.

Read More:Reasons and Corrections of NAME Error in Excel (10 Examples)


💬 Things to Remember

✎ On Error Resume Next doesn’t fix the errors. It basically ignores the error and proceeds to the next statement.

Excel traps and stores the run-time errors in Err object. When we use the On Error Resume Next statement, it clears Err object properties.

You can turn off the On Error Resume Next statement in your VBA code in Excel by adding the On Error GoTo 0 statement.


Conclusion

To conclude, vba + on error goto, I hope this tutorial has provided you with a piece of useful knowledge to use the On Error Resume Next in Excel VBA, vba + on error goto. I recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Object Required (Error 424) Invalid Procedure Call or Argument (Error 5)

Vba + on error goto - mine

How to Include Error Handling in VBA Code

For Microsoft® Access, Excel and Word, how to include error handling in VBA code to intercept and process errors if and when they occur.

Last updated on 2021-01-23 by David Wallis.


Preface

Errors occur during the execution of a VBA function or sub procedure due to a variety of reasons, including the use of incorrect code and the macro running under circumstances for which it was not intended.

Introducing error trapping in all your VBA procedures puts you in control of errors and in a position to direct the code to make appropriate responses.

By managing errors you can spare your users from being thrown into a panic at not knowing how to respond to threatening looking error messages, like this one.

Excel error message

Failure to include error handling may result in unwelcome and undesirable behaviour of the software. Your users could be faced with loss of changes they have made to their data or application, or with their application freezing and refusing to function at all.


Simple Error Handler

There are a number of ways in which you might choose to include code for error handling in a macro. This is the skeleton code for a simple way:

Sub YourProcedureName()

On Error Goto errHandler

your code here

procDone:

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

Resume procDone

End Sub

The On Error statement turns on error trapping. Information about any error that occurs subsequent to this statement is stored in a VBA error object named Err.

In the event of an error, the On Error Goto errHandler statement instructs the procedure to stop executing your code at the point at which the error occurs and to pick it up again at the errHandler label.

MsgBox Err.Number & ": " & Err.Description sends a message box to the screen displaying information about the nature of the error:

  • Err.Number is a unique identification number for the error object drawn from VBA’s library of errors
  • Err.Description is a description of that error.

Resume procDone instructs the macro to resume executing at the procDone label.


Refined Error Handling Code

Let’s assume you have wrapped a new procedure in the error-handling code described above. As you test your macro an error results. Therefore you are presented with the message box from which you learn the error number and the nature of the error.

Now you are in a position to revise your error handler to respond to this specific error (in this example the error number 1234):

Sub YourProcedureName()

On Error Goto errHandler

your code here

procDone:

Exit Sub

errHandler:

Select Case Err.Number

Case 1234

your code for handling error 1234

Case Else

' All outstanding errors

MsgBox Err.Number & ": " & Err.Description

End Select

Resume procDone

End Sub

As you test your macro and uncover other potential errors, you can extend the Select Case statement by including other Cases accordingly. In the following refinement of the macro, the messages to users are made specific:

Sub YourProcedureName()

On Error Goto errHandler

Dim msg$, title$, icon&

your code here

procDone:

Exit Sub

errHandler:

icon& = vbOKOnly + vbCritical

Select Case Err.Number

Case 53

title$ = "Missing File"

msg$ = "Macro cannot locate an essential file."

msg$ = msg$ & vbNewline & vbNewLine

msg$ = msg$ & "Please notify IT support."

Case Else

title$ = "Unanticipated Error"

msg$ = Err.Number & ": " & Err.Description

msg$ = msg$ & vbNewline & vbNewLine

msg$ = msg$ & "Please make a note of this message"

End Select

MsgBox Err.Number & ": " & Err.Description

Resume procDone

End Sub

If when your procedure completes its run you want the user to be given a message about any error, then here is a further refinement of your code:

Sub YourProcedureName()

On Error Goto errHandler

Dim msg$, title$, icon&

your code here

title$="Macro Run Completed"

msg$ = "Please continue using the workbook"

icon& = vbOKOnly + vbExclamation

procDone:

MsgBox msg$, icon&, title$

Exit Sub

errHandler:

icon& = vbOKOnly + vbCritical

Select Case Err.Number

Case 53

title$ = "Missing File"

msg$ = "Macro cannot locate an essential file."

msg$ = msg$ & vbNewline & vbNewLine

msg$ = msg$ & "Please notify the developer."

Case Else

title$ = "Unanticipated Error"

msg$ = Err.Number & ": " & Err.Description

msg$ = msg$ & vbNewline & vbNewLine

msg$ = msg$ & "Please make a note of this message."

End Select

Resume procDone

End Sub

For users who are adept at computer usage, as developer you will get targeted information about an error if you refine your error handler even further:

errHandler:

icon& = vbOKOnly + vbCritical

Select Case Err.Number

Case 53

title$ = "Missing File"

msg$ = "Macro cannot locate an essential file."

msg$ = msg$ & vbNewline & vbNewLine

msg$ = msg$ & "Please notify the developer."

Case Else

title$ = "Unanticipated Error"

msg$ = _

"An unexpected error has occurred in your program." & _

vbCrLf & vbCrLf & _

"Please send a screen print of this message to the developer." & _

vbCrLf & vbCrLf & _

"If you're unable to, please make a note of these details:" & _

vbCrLf & vbCrLf & _

"Calling Proc: NameOfYourProcedure" & _

vbCrLf & _

"Error Number: " & Err.Number & _

vbCrLf & _

"Description: " & Err.Description & _

vbCrLf & vbCrLf & _

"Program will now … "

End Select

Resume procDone

The Case Else block responds to any error for which you have not specifically coded. So the bulk of the message it displays is information useful to you, the developer.

NameOfYourProcedure is the name you’ve given to the procedure in which you’ve included this error handler.

Program will now … is your message to the user about what will happen as a result of the error. You’ll adapt this message as you deploy the error handler throughout your code modules.


Take Care—Test to Destruction

Always test your macros fully before putting them into service. Best to test on a back-up copy of your application.

“Pretty good testing is easy to do (that’s partly why some people like to say ‘testing is dead’ — they think testing isn’t needed as a special focus because they note that anyone can find at least some bugs some of the time). Excellent testing is quite hard to do.”

James Bach (Satisfice, Inc.)

Testing takes time. To the time I estimate for writing the code, I add 30% at least for the testing.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

On Error GoTo 0

This statement is the default error handler which interrupts execution and displays a dialog box.

PublicSub MySubroutine()
    OnErrorGoTo 0

This is an example of the error message that is displayed:
SS

PublicSub DefaultErrorHandler()
    OnErrorGoTo 0
'do something
    Charts("Chart 1").Activate 'this generates a run-time error
'execution stops and a dialog box is displayed
EndSub

If you switch to a different type of error handling you can switch back to the default by using the "On Error Goto 0" statement.


Err Object

There is no need to include the following line of code at the end of a procedure (or function) as error handling in a procedure (or function) is automatically disabled when the procedure (or function) exits.
Resets the Err object and resets the Err.Number to zero.

   OnErrorGoTo 0
EndSub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions LimitedTopPrevNext
VBA Error 400 Object Doesn’t Support this Property or Method (Error 438)

On Error statement

  • Article
  • 6 minutes to read

Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

Syntax

On Error GoToline
On Error Resume Next
On Error GoTo 0

The On Error statement syntax can have any of the following forms:

StatementDescription
On Error GoTolineEnables the error-handling routine that starts at line specified in the required lineargument.

The line argument is any line label or line number.

If a run-time error occurs, control branches to line, making the error handler active.

The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.
On Error Resume NextSpecifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred and execution continues. Use this form rather than On Error GoTo when accessing objects.
On Error GoTo 0Disables any enabled error handler in the current procedure.

If you don't use an On Error statement, any run-time error that occurs is fatal; that is, an error message is displayed and execution stops.

An "enabled" error handler is one that is turned on by an On Error statement; an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. Control returns to the calling procedure.

If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no inactive, enabled error handler is found, the error is fatal at the point at which it actually occurred.

Each time the error handler passes control back to a calling procedure, that procedure becomes the current procedure. After an error is handled by an error handler in any procedure, execution resumes in the current procedure at the point designated by the Resume statement.

Note

An error-handling routine is not a Sub procedure or Function procedure. It's a section of code marked by a line label or line number.

Error-handling routines rely on the value in the Number property of the Err object to determine the cause of the error. The error-handling routine should test or save relevant property values in the Err object before any other error can occur or before a procedure that might cause an error is called. The property values in the Err object reflect only the most recent error. The error message associated with Err.Number is contained in Err.Description.

On Error Resume Next causes execution to continue with the statement immediately following the statement that caused the run-time error, or with the statement immediately following the most recent call out of the procedure containing the On Error Resume Next statement. This statement allows execution to continue despite a run-time error. You can place the error-handling routine where the error would occur, rather than transferring control to another location within the procedure. An On Error Resume Next statement becomes inactive when another procedure is called, so you should execute an On Error Resume Next statement in each called routine if you want inline error handling within that routine.

Note

The On Error Resume Next construct may be preferable to On Error GoTo when handling errors generated during access to other objects. Checking Err after each interaction with an object removes ambiguity about which object was accessed by the code. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error (the object specified in Err.Source).

On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.

To prevent error-handling code from running when no error has occurred, place an Exit Sub, Exit Function, or Exit Property statement immediately before the error-handling routine, as in the following fragment:

Here, the error-handling code follows the Exit Sub statement and precedes the End Sub statement to separate it from the procedure flow. Error-handling code can be placed anywhere in a procedure.

Untrapped errors in objects are returned to the controlling application when the object is running as an executable file. Within the development environment, untrapped errors are only returned to the controlling application if the proper options are set. See your host application's documentation for a description of which options should be set during debugging, how to set them, and whether the host can create classes.

If you create an object that accesses other objects, you should try to handle errors passed back from them unhandled. If you cannot handle such errors, map the error code in Err.Number to one of your own errors, and then pass them back to the caller of your object. You should specify your error by adding your error code to the vbObjectError constant. For example, if your error code is 1052, assign it as follows:

Note

System errors during calls to Windows dynamic-link libraries (DLL) or Macintosh code resources don't raise exceptions and cannot be trapped with Visual Basic error trapping. When calling DLL functions, you should check each return value for success or failure (according to the API specifications), and in the event of a failure, check the value in the Err object's LastDLLError property. LastDLLError always returns zero on the Macintosh.

Example

This example first uses the On Error GoTo statement to specify the location of an error-handling routine within a procedure. In the example, an attempt to delete an open file generates error number 55. The error is handled in the error-handling routine, and control is then returned to the statement that caused the error. The On Error GoTo 0 statement turns off error trapping.

The On Error Resume Next statement is then used to defer error trapping so that the context for the error generated by the next statement can be known for certain. Note that Err.Clear is used to clear the Err object's properties after the error is handled.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

VBA Subscript Out of Range Runtime Error 9