On error resume next means

on error resume next means

GoTo 0 means whenever the run time error occurs, excel or VBA should display the error message box saying the kind of error it has encountered. When it is referenced by an On Error GoTo label statement, an error handler is enabled. When this enabling occurs, a run-time error results in program control. On Error Resume Next statement doesn't fix the runtime errors but it simply means that program execution will continue from the.

On error resume next means - very pity

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.

QTP - Error Handling



There are various ways of handling errors in QTP. There are three possible types of errors, one would encounter, while working with QTP. They are −

  • Syntax Errors
  • Logical Errors
  • Run Time Errors

Error Types

Syntax Errors

Syntax errors are the typos or a piece of the code that does not confirm with the VBscripting language grammar. Syntax errors occur at the time of compilation of code and cannot be executed until the errors are fixed.

To verify the syntax, use the keyboard shortcut Ctrl+F7 and the result is displayed as shown below. If the window is not displayed one can navigate to "View" → "Errors".

Error Handling

Logical Errors

If the script is syntactically correct but it produces unexpected results, then it is known as a Logical error. Logical error usually does not interrupt the execution but produces incorrect results. Logical errors could occur due to variety of reasons, viz- wrong assumptions or misunderstandings of the requirement and sometimes incorrect program logics (using do-while instead of do-Until) or Infinite Loops.

One of the ways to detect a logical error is to perform peer reviews and also verify the QTP output file/result file to ensure that the tool has performed the way it was supposed to do.

RunTime Errors

As the name states, this kind of error happens during Run Time. The reason for such kind of errors is that the script trying to perform something is unable to do so and the script usually stops, as it is unable to continue with the execution. Classic examples for Run Time Errors are −

  • File NOT found but the script trying to read the file
  • Object NOT found but the script is trying to act on that particular object
  • Dividing a number by Zero
  • Array Index out of bounds while accessing array elements

Handling Run-Time Errors

There are various ways to handle errors in the code.

1. Using Test Settings − Error handling can be defined the Test Settings by Navigating to "File" >> "Settings" >> "Run" Tab as shown below. We can select any of the specified settings and click "OK".

Error Handling

2. Using On Error Statement − The ‘On Error’ statement is used to notify the VBScript engine of intentions to handle the run-time errors by a tester, rather than allowing the VBScript engine to display error messages that are not user-friendly.

  • On Error Resume Next − On Error Resume Next informs the VBScript engine to process executing the next line of code when an error is encountered.

  • On error Goto 0 − This helps the testers to turn off the error handling.

3. Using Err Object − Error object is an in-built object within VBScript that captures the run-time error number and error description with which we are able to debug the code easily.

  • Err.Number − The Number property returns or sets a numeric value specifying an error. If Err.Number value is 0 then No error has occurred.

  • Err.Description − The Description property returns or sets a brief description about an error.

  • Err.Clear − The Clear method resets the Err object and clears all the previous values associated with it.

Example

'Call the function to Add two Numbers Call Addition(num1,num2) Function Addition(a,b) On error resume next If NOT IsNumeric(a) or IsNumeric(b) Then Print "Error number is " & err.number & " and description is : " & err.description Err.Clear Exit Function End If Addition = a+b 'disables error handling On Error Goto 0 End function

4. Using Exit Statement − Exit Statements can be used along with Err object to exit from a test or action or iteration based on the Err.Number value. Let us see each one of those Exit statements in detail.

  • ExitTest − Exits from the entire QTP test, no matter what the run-time iteration settings are.

  • ExitAction − Exits the current action.

  • ExitActionIteration − Exits the current iteration of the action.

  • ExitTestIteration − Exits the current iteration of the QTP test and proceeds to the next iteration.

5. Recovery Scenarios − Upon encountering an error, recovery scenarios are triggered based on certain conditions and it is dealt in detail in a separate chapter.

6. Reporter Object − Reporter Object helps us to report an event to the run results. It helps us to identify if the concerned action/step is pass/fail.

'Syntax: Reporter.ReportEventEventStatus, ReportStepName, Details, [ImageFilePath] 'Example Reporter.ReportEvent micFail, "Login", "User is unable to Login."

Previous Page Print PageNext Page  

Error handling does not involve finding errors in your scripts. Instead, use error handling techniques to allow your program to continue executing even though a potentially fatal error has occurred. Ordinarily, all runtime errors that are generated by the VBScript engine are fatal, since execution of the current script is halted when the error occurs. Error handling allows you to inform the user of the problem and either halt execution of the program or, if it is prudent, continue executing the program.

The On Error Resume Next Statement

There are two main elements to error handling in VBScript. The first is the statement, which informs the VBScript engine of your intention to handle errors yourself, rather than to allow the VBScript engine to display a typically uninformative error message and halt the program. This is done by inserting a statement like the following at the start of a procedure:

On Error Resume Next

This tells the VBScript engine that, should an error occur, you want it to continue executing the program starting with the line of code which directly follows the line in which the error occurred. For example, in the simple WSH script:

On Error Resume Next x = 10 y = 0 z = x / y Alert z

a “Cannot divide by Zero” error is generated on the fourth line of code because the value of is 0. But because you’ve placed the statement in line 1, program execution continues with line 5. The problem with this is that when an error is generated, the user is unaware of it; the only indication that an error has occurred is the blank Alert box (from line 5) that’s displayed for the user.

Tip

A particular statement is valid until another statement in the line of execution is encountered. This means that if Function A contains an statement, and Function A calls Function B, but Function B does not contain an statement, the error handling from Function A is still valid. Therefore, if an error occurs in Function B, it is the statement in Function A that handles the error; in other words, when an error is encountered in Function B, program flow will immediately jump to the line of code that followed the call to Function B in Function A. When Function A completes execution, the statement it contains also goes out of scope. This means that, if the routine that called Function A did not include an statement, no error handling is in place.

This is where the second element of VBScript’s error handling comes in. VBScript includes an error object, named Err, which, when used in conjunction with , adds much more functionality to error handling, allowing you to build robust programs and relatively sophisticated error handling routines.

The Err object is part of the VBScript language and contains information about the last error to occur. By checking the properties of the Err object after a particular piece of code has executed, you can determine whether an error has occurred and, if so, which one. You can then decide what to do about the error —you can, for instance, continue execution regardless of the error, or you can halt execution of the program. The main point here is that error handling using and the Err object puts you in control of errors, rather than allowing an error to take control of the program (and bring it to a grinding halt). To see how the Err object works and how you can use it within an error handling regimen within your program, let’s begin by taking a look at its properties and methods.

Like all object properties, the properties of the Err object can be accessed by using the name of the object, Err, the dot (or period) delimiter, and the property name. The Err object supports the following properties:

Number

The Number property is an integer value that contains an error code value between and 65535, representing the last error. If the value of is 0, no error has occurred. The line of code like the following, then, can be used to determine if an error has occurred:

If Err.Number <> 0 Then

Although the properties of the Err object provide information on the last error to occur in a script, they do not do so permanently. All the Err object properties, including the Number property, are set either to zero or to zero-length strings after an End Sub, End Function, Exit Sub or Exit Function statement. In addition, though, you can explicitly reset to zero after an error by calling the Err object’s Clear method. The WSH script in Example 4.8 illustrates the importance of resetting the Err object after an error occurs.

Example 4-8. Failing to Reset the Err Object

Dim x, y ,z On Error Resume Next x = 10 y = 0 z = x / y If Err.Number <> 0 Then MsgBox "There's been an error #1" Else MsgBox z End IF z = x * y If Err.Number <> 0 Then MsgBox "There's been an error #2" Else MsgBox z End If End Sub

The division by zero on the fifth line of the script in Example 4.8 generates an error. Therefore, the conditional statement on line 6 evaluates to , and an error dialog is displayed. Program flow then continues at line 12. Line 12 is a perfectly valid assignment statement that always executes without error, but the Err.Number property still contains the error number from the previous error in line 5. As a result, the conditional statement on line 13 evaluates to , and a second error dialog is displayed. Despite the two error messages, though, there’s only been a single error in the script.

The Err object can be reset by using the Clear method (which is discussed in the next Section 4.2.2.2).

Description

The Description property contains a string that describes the last error that occurred. You can use the Description property to build your own message box alerting the user to an error, as the WSH script in Example 4.9 shows.

Example 4-9. Using the Description Property to Display Error Information

Dim x, y ,z On Error Resume Next x = 10 y = 0 z = x / y If Err.Number <> 0 Then MsgBox "Error number " & Err.Number & ", " & _ Err.Description & ", has occurred" Err.Clear Else MsgBox z End If z = x * y If Err.Number <> 0 Then MsgBox "Error No:" & Err.Number & " - " & _ Err.Description & " has occurred" Err.Clear Else Alert z End If
Source

The Source property contains a string expression that indicates the class name of the object or application that generated the error. You can use the Source property to provide users with additional information about an error; in particular, about where an error occurred.

The value of the Source property for all errors generated within scripted code is simply “Microsoft VBScript runtime error.” This is true of all VBScript scripts, whether they’re written for Active Server Pages, Windows Script Host, Internet Explorer, or Outlook forms. Obviously, this makes the Source property less than useful in many cases. However, you can assign a value to the Source property in your own error handling routines to indicate the name of the function or procedure in which an error occurred. In addition, the primary use of the Source property is to signal an error that is generated by some other object, like an OLE automation server (like Microsoft Excel or Microsoft Word) or a COM component.

The two methods of the Err object allow you to raise or clear an error, in the process simultaneously changing the values of one or more Err object properties. The two methods are:

Raise

The Err.Raise method allows you to generate a runtime error. Its syntax is:[1]

where is the numeric code for the error you’d like to generate. At first glance, generating an error within your script may seem like a very odd thing to want to do! However, there are times, particularly when you are creating large, complex scripts, that you need to test the effect a particular error will have on your script. The easiest way to do this is to generate the error using the Err.Raise method and providing the error code to the parameter, then sit back and note how your error handling routine copes with the error, what the consequences of the error are, and what side effects the error has, if any. The client-side script in Example 4.10, for instance, allows the user to enter a number into a text box, which is passed as the error code value to the Err.Raise method. If the value of the error code is nonzero, an Alert box opens that displays the error code and its corresponding description. Figure 4.6, for instance, shows the Alert box that is displayed when the user enters a value of 13 into the text box.

Example 4-10. Calling the Err.Raise Method

<HTML> <HEAD> <TITLE>Using the Err Object</TITLE> <SCRIPT LANGUAGE="vbscript"> Sub cmdButton1_OnClick On Error Resume Next errN = Document.frm1.errcode.value Err.Raise(errN) If Err.Number <> 0 Then Alert "Error No:" & Err.Number & " - " & Err.Description Err.Number = 0 End If End Sub </SCRIPT> </HEAD> <BODY BGCOLOR="white"> <CENTER> <H2>Generating an Error</H2> <P> <FORM NAME="frm1"> Enter an Error Code &nbsp; <INPUT TYPE="text" NAME="errcode"> <INPUT TYPE="button" NAME="cmdButton1" VALUE="Generate Error"> </CENTER> </BODY> </HTML>

At present there is no definitive list of VBScript runtime error codes available from Microsoft. Table 4.1 lists a few of the most common runtime errors.

Tip

An Error Code Generator (, , and ), which allows you to generate a complete list of current VBScript error codes, can be found on the O’Reilly Visual Basic web site at http://vb.oreilly.com.

Table 4-1. Some Common VBScript Error Codes

Error Number

Description

5

Invalid procedure call

6

Overflow

7

Out of memory

9

Subscript out of range

11

Division by zero

13

Type mismatch

Figure 4-6. Generating a Type mismatch error at runtime

Clear

The Clear method clears the information that the Err object is storing about the previous error; it takes no parameters. It sets the values of to and the Err object’s Source and Description properties to a null string.

Get VBScript in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.

Start your free trial

This tutorial focuses on the meaning and implementation of , , and . These critical components add structure to the VBA statement and any explicit handlers using .

In brief, goes to the next executable line, turns off the currently enabled error handler, and turns off the current exception. Of course, these things need a bit more explanation, otherwise we wouldn’t have written an entire tutorial about them!


Basic Error Handling Overview

For a more thorough exploration of basic error handling techniques, read our article dedicated to the On Error GoTo statement.

Error handling in VBA is disabled by default, so it must be turned on with the statement. If you want to explicitly handle errors, label a section of code and direct the execution flow to that section:

The first line in the snippet turns on (enables) the handler, and if an error occurs in that section, execution flow moves to the part of the code with the label .

To shut off (disable) the active handler, use . Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using , which automatically turns off the handler.

Calling another subroutine does not exit the current subroutine so any active handlers will be stacked in the new subroutine!

Error handling code must be placed before the statement, so in order to avoid it running with the regular code, it should come after an statement.

It’s wise to check inputs for the correct data types, formatting, and common issues like a divisor of zero. This reduces the prevalence of runtime errors before they can even arise.


How to Use

plays a dual role:

  1. as a stand-in for a custom error handler that is marked by a label
  2. to return execution flow to the main body of the code after a custom handler completes

We’re going to talk about each of these roles in the next two sections.

Defer Handling

You can defer handling errors by using , like in this snippet:

If you replace with , you can defer error handling. If an error is raised, execution simply skips that line of code and goes to the next one. If the next line raises an error, execution just skips that line, too. This definitely has some benefits, and we use it quite a lot on this site, like when checking if a file exists, but you need to be careful when applying it. If used to ignore a specific deficiency you know exists, you could mistakenly skip over an entire section of code without realizing it.

Using to defer handling has its purposes, such as eventually running code that forcibly rectifies the offending variables and objects. But I’d strongly recommend either explicitly handling errors with or avoiding errors by checking inputs with IF statements, where appropriate.

The main problem with the “ignore the errors” method is that users will not notice that the code is broken. It will run as long as deferment is active. However, some expected result will simply be incorrect or not be displayed at all. This leads to frustrated users trying to figure out what they did wrong when it was really a runtime problem with your code. Even worse, they may just accept the erroneous output, no matter how outlandish.

to defer errors has its place, but use it wisely. A good habit is to warn others (including your future self) that you’re using it. Clearly state this in the comments at the top module and describe why you’re doing it. This habit at least makes others aware that unnoticed errors may be lurking in the code.

Return Execution to the Main Block

is not, however, without a positive side. The same statement can return control to the main code block after entering a custom handler. This means you don’t have to end a subroutine every time a custom handler is employed.

This code snippet predicts that a divide by zero error may be triggered and fixes it with some user interaction. Once the input is received and the new result calculated, the statement returns execution to the line immediately following the line that triggered the error.

If you’d like to return to the same line that triggered the error, you can simply use , like this:

Using instead of has the added benefit of naturally looping until the user inputs a valid divisor for .

Hidden Role

and actually play a third role when in the error handling block: once executed, they reset the current exception to , meaning the active error handler can be used again.

Think of the error handler as a single-use tool, which must be reset before it can be used again. We know that ending and exiting the subroutine resets the handler. What this hidden role means is that and also resets the error handler.


Using and

With VBA, only one error handler can be active at a time. This makes sense, as an error will immediately trigger the active handler, and VBA cannot guess between multiple handlers. To VBA, an error is an error is an error. You can distinguish errors using the object and a statement, but that’s for our next tutorial.


Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.


Enable and Disable Handlers

Use to completely turn off error handling in a subroutine. This effectively resets the error handling in that sub to the default, which is no handling at all. You may decide to do this when you only have one handler, like above, which would not make sense with a different type of error. Alternatively, you may want VBA to warn the user so they can relay error codes to you.

Note that you do not need to disable a handler before enabling another. The new one simply takes over. However, for clarity, I like to turn off a handler when its section of code ends. In this code snippet, it’s very clear which handlers go with which parts of the code.

Clearing Exceptions

A particular error instance is called an exception. In the following code, there is one type of error but two exceptions, because each instance of the error throws an exception:

When the first error is raised, VBA directs flow immediately to the active error handler.

The single error handler rule applies even in the error code! Once an error has been raised, the error handler is completely “full”. In order to handle another error, the handler must be emptied first. Normally this is done by exiting the handler with or . However, there is another method, which uses .

Let’s return to our divide by zero handler . You could try some calculations in the handler:

But what happens if the user inputs zero again? The full error handler cannot take another exception and leads to a fatal error, killing the program.

You can reset the handler - that is, remove the current exception - by using :

However, there is a flaw here! The clears the current exception, and the statement sets off an infinite loop. Execution does NOT return to the original code section.

Since using can be extremely confusing, I’d recommend not using it at all. If you are adamant about , use it as a last resort and finish the error handling code with .


Now you can use and or effectively. Unless absolutely necessary, I’d recommend steering clear of , as it can greatly complicate the logic of your program. I’d also recommend judiciously employing deferment, because it can mean segments of your code just don’t do anything at all.

Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.


VBA Cheat Sheets

Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.

Get your cheat sheets


This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog. Visit him on LinkedIn and his personal page.

Error Handling in VBA

error handling in vba

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message like this:

vba runtime error

But you can change this and instruct Excel to allow your code to deal with run-time errors.

NOTE : I’m going to use the terms sub, function and procedure interchangeably. For the purposes of this article they all mean the same thing – a chunk of code written to do a particular thing.

The On Error Statement

To instruct Excel what to do when an error occurs, you use the On Error statement. You can use On Error in four ways:

On Error GoTo 0 On Error Resume Next On Error GoTo [label] On Error GoTo -1

On Error GoTo 0

This is the default mode and is already turned on when you start writing your code. You don’t need to use an On Error GoTo 0 statement at the start of your VBA.

In this mode VBA displays the standard style error message box, and gives you the choice to Debug the code (enter VBA editor and use debugging tools) or End code execution.

You would use On Error GoTo 0 to turn default error handling back on if you have previously told VBA to deal with errors in some other way e.g. by using On Error Resume Next.

On Error Resume Next

On Error Resume Next tells VBA to continue executing statements immediately after the statement that generated the error.

On Error Resume Next allows your code to continue running even if an error occurs. Resume Next does not fix an error, it just ignores it. This can be good and bad.

The Good?

If you know that your code could generate an error, then using Resume Next can prevent an interruption in code execution.

For example, we want to create a file, but I want to make sure a file with the same name doesn’t already exist. To do this, I will attempt to delete the file, and of course if it doesn’t already exist, an error will occur.

I don’t care if an error occurs. If it does, the file doesn’t exist and that’s fine for what I want to do. So before I attempt to delete the file, I instruct VBA to ignore the error.

Sub DeleteFile() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error Resume Next 'Delete the file Kill FilePath 'If the file doesn't exist the Kill statement 'generates an error. 'But I have assumed that is ok as it indicates that 'I am ok to create a new file with the same filename 'This is a bad assumption - see the next sub 'UnhandledError() End Sub

The Bad

But hang on. What if the file I am trying to delete is read only? If it is I will get an error when I try to delete it. I’ve assumed that I will only get an error if the file isn’t there. So an error caused by trying to delete a read only file will get missed.

And If I then try to create a new file with the same name, or open it for writing data to it, I will generate more errors and they will be missed too.

Sub UnhandledError() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error Resume Next 'Delete the file Kill FilePath 'But if the file is read-only I can't delete it 'and the Kill statement generates an error 'I can't now create a new file with the same name 'Trying to Open the file for writing data to it will 'also generate an error but it will be missed as 'we've told VBA to continue executing code if an 'error occurs Open FilePath For Output As #1 Write #1, "Some data" Close #1 End Sub

If you do use On Error Resume Next you should immediately turn default error handling back on (or turn on your custom error handler – see below)

Be careful when using On Error Resume Next. You are better off seeing if an error occurred by checking the Err object (see below). Doing this can tell you the error number and help you figure out exactly what happened.

On Error GoTo [LABEL]

If an error occurs, this transfers code execution to the line following the label. This is typically used to specify your own error handling code.

None of the code between the line generating the error and the label is executed.

Error Handlers

So you write your own error handling code and use On Error GoTo [LABEL] to instruct VBA to use your code to deal with errors.

You can place your error-handling code anywhere in a procedure, but typically it is placed at the end.

Your error handler should either fix the error and resume code execution, or terminate the routine gracefully.

Sub ErrorHandler() Dim num As Integer On Error GoTo ErrHandler num = 1 / 0 MsgBox "This line is not executed" Exit sub ErrHandler: MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description End Sub

As VBA will execute each line of code in turn going from top to bottom, if no error is generated then it will execute your error handling code when it gets to that point in your sub.

To prevent this happening, use an Exit Sub, Exit Function, or Exit Property statement before your error handling routine.

In the example above, if the value assigned to num was valid e.g. num = 1/1, then we don’t want the code beneath ErrHandler: executed. So just before the ErrHandler: label, I've used an Exit Sub statement.

Multiple Error Handlers

You can have more than one error handler in a routine, but only one of them can be active at any time.

You could have something like:

Sub MultipleErrorHandlers() On Error GoTo ErrHandler1 [some code] On Error GoTo ErrHandler2 [some code] Exit Sub ErrHandler1: [ErrHandler1 Code] Exit Sub ErrHandler2: [ErrHandler1 Code] Exit Sub End Sub

If an error occurs between On Error GoTo ErrHandler1 and On Error GoTo ErrHandler2 then the ErrHandler1 code is executed.

If an error occurs after On Error GoTo ErrHandler2 then the ErrHandler2 code is executed.

NOTE: Notice that at the end of each error handling routine is an Exit Sub statement. If I didn’t use these, when the ErrHandler1 code is finished executing, VBA could just continue on down to the next line and execute the ErrHandler2 code as well.

Strictly speaking I don’t need the Exit Sub at the end of the ErrHandler2 code, as it is the last line in the sub, but it is a good habit to get into.

Err object

When an error occurs the Err object contains information about the error like the error number and a description of the error.

As any given line of code can generate multiple errors it’s a good idea to examine the Err object to determine what you want to do in your code.

Err.Number gives you the error number, and Err.Description gives you a description of the error.

Sub CheckErrObject() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error GoTo ErrHandler 'Delete the file Kill FilePath Open FilePath For Output As #1 Write #1, "Some data" Close #1 Exit Sub ErrHandler: Select Case Err.Number Case 53 ' File doesn't exist Err.Clear ' Clear the error Case 75 ' File is Read Only MsgBox "Error Number : " & Err.Number & vbCrLf & vbCrLf & Err.Description Exit Sub Case Else ' Code to handle other errors End Select Resume Next ' Continue executing code after line that generated error End Sub

Resume

The Resume statement tells VBA to resume executing code at a specified point. Resume can only be used in an error handling routine, any other use will generate an error.

Resume takes three forms:

Resume Resume Next Resume [label]

Using just Resume causes execution to resume at the same line of code that caused the error. If you haven’t fixed the error, your code will begin an infinite loop as it switches between the line of code generating the error and the error handling routine.

If you look at the example sub Resume_Next() which is below, num = 1 / 0 causes a Divide by 0 error. I’ve instructed VBA to use my error handler, called ErrHandler.

In ErrHandler I’ve attempted to fix the error by assigning num the value 1. If I then used only Resume, all that would happen is that VBA would go back to num = 1 / 0 and another Divide by 0 error would be generated.

Instead, I use Resume Next to carry on executing code at the line after the one causing the error.

In doing so I have handled the error by assigning the value 1 to num, and execution will continue without another error at the line result = num / 1

Sub Resume_Next() Dim num As Integer Dim result As Integer On Error GoTo ErrHandler num = 1 / 0 result = num / 1 Exit Sub ErrHandler: num = 1 Resume Next End Sub

Resume [label] passes code execution to the line with that label.

Sub Resume_Next() Dim num As Integer Dim result As Integer On Error GoTo ErrHandler num = 1 / 0 result = num / 1 MyLabel: ‘Code execution starts again from here result = num * 1 Exit Sub ErrHandler: num = 1 Resume MyLabel End Sub

Whenever you use Resume it clears the Err object.

Error Handling With Multiple Procedures

Every sub/function doesn’t have to have an error handler. If an error occurs, VBA will use the last On Error statement to determine what happens.

If an On Error statement has been used in a procedure and an error occurs in that procedure, then that error is handled as I've just described.

But if an error occurs in a sub that hasn’t used an On Error statement, VBA goes back through procedure calls until it finds an On Error directive.

Let’s look at an example with three subs.

SubOne() calls SubTwo(). SubTwo calls SubThree(), and has some code of its own to execute. SubThree() carries out a calculation.

Sub SubOne() On Error GoTo ErrHandler SubTwo Exit Sub ErrHandler: MsgBox "Error caught in SubOne" End Sub Sub SubTwo() SubThree MsgBox "No errors here" End Sub Sub SubThree() Dim num As Integer num = 1 / 0 End Sub

SubOne() has an error handler routine, ErrHandler, and has instructed VBA to use it.

SubTwo() will display a message on screen after it’s call to SubThree() has finished.

However SubThree() generates a Divide by 0 error.

SubThree() hasn’t used an On Error statement to tell VBA what to do if an error occurs, so VBA goes back to SubTwo(). That also doesn’t have an On Error statement so VBA goes back to SubOne().

Here we have our error handler and the code in it is executed.

Note that the message "No errors here" in SubTwo() is not displayed because that line of code is not executed.

When SubThree() generated an error, code execution went back to the error handler in SubOne() and any code in SubTwo() after the call to SubThree() is missed out.

On Error GoTo -1

This resets the current error. It’s the equivalent of using Err.Clear.

You can see that in this sub, after the Divide By 0 error is generated, after On Error GoTo -1 is used, Err.Number is 0 (no error) and Err.Description is empty.

Sub GoToMinus1() Dim num As Integer On Error Resume Next num = 1 / 0 'If error is Divide by Zero If Err.Number = 11 Then MsgBox "Error Code : " & Err.Number & " , " & Err.Description On Error GoTo -1 MsgBox "Error Code : " & Err.Number & " , " & Err.Description End If End Sub

Sample Code

Download this workbook for all the sample code used here plus several other examples.

Filed Under: Excel VBATagged With: Excel VBA

Syntax

On Error Resume Next On Error Goto 0

Description

Enables or disables error handling within a procedure. If you don’t use an statement in your procedure, or if you have explicitly switched off error handling, the VBScript runtime engine handles the error automatically. First, it displays a dialog containing the standard text of the error message, something many users are likely to find incomprehensible. Second, it terminates the application, so any error that occurs in the procedure produces a fatal runtime error.

Rules at a Glance

  • When a runtime error occurs in the routine in which the statement occurs, program execution continues with the program line following the line that generated the error. This means that, if you want to handle an error, this line following the line that generated the error should call or include an inline error-handling routine.

  • When a runtime error occurs in any routine called by the routine in which the statement occurs, or by its subroutines, program execution returns to the statement immediately after the subroutine call in the routine containing the statement.

  • When used in an ASP page for IIS 5.0, disables ASP’s own error handling.

  • You disable error handling by using the statement.

Programming Tips and Gotchas

  • If you have no error handling in your procedure, the VBScript runtime engine traces back through the call stack until ...

Get VBScript in a Nutshell, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.

Start your free trial

on error resume next means

But: On error resume next means

On error resume next means
Remote insight board interface error hp
ERROR XAMPL PASSING FILE
PROXIFIER ISA 502 PROXY ERROR
ERROR E5 DE LA IMPRESORA CANON MP190

watch the thematic video

VBA Error Handling Explained in Plain English (with examples)

0 Comments

Leave a Comment