On error resume next set

on error resume next set

VBA On Error Resume Next is an error handler statement. If the error occurs while running the code, you can use this statement to resume the next line of. How To Use On Error Resume Next Often when using ASP or Active Server Pages with VBScript you will find it necessary to check for errors when you do certain. You include statements in a script to explicitly manage the flow of control when an error occurs. On Error Resume Next.

Authoritative message: On error resume next set

ERROR 20 LICENSE WAS NOT OBTAINED MAYA
On error resume next set
REASON 413 USER AUTHENTICATION FAILED ERROR
On error resume next set
Invalid Procedure Call or Argument (Error 5) VBA Error 400

On Error GoTo . Resume Statement

Enables an error-handling routine after an error occurs, or resumes program execution.

Syntax:

On Error Statement diagram

Parameters:

GoTo Labelname: If an error occurs, enables the error-handling routine that starts at the line "Labelname".

Resume Next: If an error occurs, program execution continues with the statement that follows the statement in which the error occurred.

GoTo 0: Disables the error handler in the current procedure.

Local: "On error" is global in scope, and remains active until canceled by another "On error" statement. "On Local error" is local to the routine which invokes it. Local error handling overrides any previous global setting. When the invoking routine exits, the local error handling is canceled automatically, and any previous global setting is restored.

The On Error GoTo statement is used to react to errors that occur in a macro.

Example:

Impressum (Legal Info)

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 on error resume next set 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 on error resume next set 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 on error resume next set 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, on error resume next set. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error on error resume next set object specified in Err.Source).

On Error GoTo 0 disables error handling in the current procedure. It on error resume next set specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0, on error resume next set. 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, on error resume next set, 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 vba error resume next not working 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 azerbaycan neft akademiyasindaki terror 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 on error resume next set 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, on error resume next set. 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 On error resume next set VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Automation error (Error 440)

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, on error resume next set. 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 canon mp 190 error e42 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() error - exit 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 On error resume next set Resume Next 'Delete the file Kill FilePath 'But if the file is read-only I can't delete it steam startup failed with error 108 solucion 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 nnodb operating system error number 995 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 sql error code 500 Write #1, "Some data" Close #1 End Sub

If you do use On On error resume next set 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, on error resume next set, 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 & "on error resume next set, " & 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, on error resume next set, 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, on error resume next set, 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, on error resume next set. 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 on error resume next set you a description of the on error resume next set CheckErrObject() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error GoTo ErrHandler 'Delete the file Kill FilePath on error resume next set 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 on error resume next set 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 = setuproot error mounting /proc / 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, on error resume next set. 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 activex run time error 429 windows 7 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, on error resume next set, 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 On error resume next set -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

Privacy Policy on error resume next set

On error resume next set - messages all

On Error GoTo ... Resume Statement

Enables an error-handling routine after an error occurs, or resumes program execution.

Syntax:

On Error Statement diagram

Parameters:

GoTo Labelname: If an error occurs, enables the error-handling routine that starts at the line "Labelname".

Resume Next: If an error occurs, program execution continues with the statement that follows the statement in which the error occurred.

GoTo 0: Disables the error handler in the current procedure.

Local: "On error" is global in scope, and remains active until canceled by another "On error" statement. "On Local error" is local to the routine which invokes it. Local error handling overrides any previous global setting. When the invoking routine exits, the local error handling is canceled automatically, and any previous global setting is restored.

The On Error GoTo statement is used to react to errors that occur in a macro.

Example:

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

Home ➜ VBA ➜

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

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

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

Error Settings in VBA (Error Trapping)

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

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

Types of VBA Errors

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

1. Syntax Errors

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

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

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

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

2. Compile Errors

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

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

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

3. Runtime Errors

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

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

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

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

4. Logical Error

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

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

Using Debug Tools in VBA

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

1. Compile VBA Project

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

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

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

2. Run Each Line of Code One by One

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

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

Using “On ERROR” Statement to Handle VBA Errors

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

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

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

1. On Error Resume Next

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

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

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

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

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

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

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

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

2. On Error GoTo 0

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

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

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

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

3. On Error GoTo [Label]

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

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

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

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

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

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

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

4. On Error GoTo -1

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

O VBA! Live in Present

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

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

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

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

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

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

Err Object

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

Properties

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

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

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

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

Methods

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

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

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

Quick Tips on Error Handling

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

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

There’s More

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

Type Mismatch (Error 13) Out of Memory (Error 7) Overflow (Error 6)

0 Comments

Leave a Comment