Vba skip error

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:

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

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.

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.

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.

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:

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.

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:

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

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

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.

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.

Sample Code

Enter your email address below to download the sample workbook.

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

More Excel VBA Posts

Automating and Emailing Pivot Table Reports

Checking Values in Range Objects With VBA

Static Variables in VBA

Save Chart as Image

Clear Downstream Dependent Data Validation Lists

Excel Status Bar

Excel Progress Bar for VBA

Finding File Meta Data Using FileSystemObject

Automatically Add Items to Data Validation List

Excel End of Period Dates

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Animating Excel Charts

Dynamic Data Validation Lists in Userforms

Show Report Filter Pages for Power Pivot PivotTables

Charting Real Time Data in Excel

Select Multiple Items from Drop Down (Data Validation) List

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Automating and Emailing Pivot Table Reports

Searching for Data With a User Form

Checking Values in Range Objects With VBA

Reader Interactions

Nice explanation! Good clear examples! Thanks

Thanks John, glad you found it useful.

Hi Philip
Error handling in VBA doesn’t seems to be fully understood by almost anyone, and there are not many Blogs on it, so this Blog of yours is a useful Blog to have.

I am just slightly put off by your last bit about the On Error GoTo -1
In particular, your statement …”… This resets the current error. It’s the equivalent of using Err.Clear….”
I expect you didn’t mean to say they are similar things, but it might be read like that by the unwary….

Here is my take on it, mostly what I have got from experimenting….

On Error GoTo -1 takes Excel out of the so called “exception state”. It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of “clearing the exception”.
The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ Resume, ( Resume; Resume Next; Resume [label] ) ,
_ changing the error handler
So those 3(5) things have the same effect on, and the same relevance to, Err.Clear as On Error GoTo -1 does/ is. They all do like Err.Clear as a secondary thing in addition to their main thing.

It is a common mistake to see Err.Clear being regarded as the same as, or similar to, On Error GoTo -1

On Error GoTo -1 is not equivalent of using Err.Clear. It does ( also ) clear the error object, (equivalent of using Err.Clear ).

It seems that every time I answer a forum question or make a comment or read a Blog on error handling in VBA, I find some other small feature of it that I had previously missed.
It seems that to fully understand error handling in VBA, it needs an explanation that is too long for a Blog, but possibly too small for a book. I am not sure yet if anyone fully understands the subject.
Chip Pearson and a few others who followed his examples have completely ignored the On Error GoTo -1 and exception state issue, although he did touch on the exception state in his comment to your Blog….
There is an awful lot of similar mistakes in coding caused by not understanding all the issues, in particular caused by not considering the effects of the “exception state”.
Missing that last bit of important understanding seems to cause almost everybody to trip up, and end up getting something wrong, even some of the best experts.

P.S.
I can go a long way in explaining what I was getting at with a very simple example. This following example has solved a similar Forum question that I must have answered now about a hundred times.
The mistake it illustrates is made by newbies and experienced professionals alike:
The code snipped below simulates a typical coding problem whereby somebody suddenly notices that it’s not handling an error more than once.
At first glance one might expect that this macro will loop 10 times, thereby handling an error 10 times.. It doesn’t. On the second loop, the default VBA error handler pops up and code execution stops…

Sub OnlyTrapOneError()
Dim Rslt As Double, Cnt As Long
For Cnt = 1 To 10
‘ Some coding not expected to error
‘
On Error GoTo sKip ‘ I might be expecting the next code line to error, so I have introduced my error handling to handle it
Let Rslt = 1 / 0 ‘
‘
sKip:
On Error GoTo 0 ‘ I am not expecting any more errors , so good practice is to remove my error handling
‘
‘ Some other coding not expected to error
Next Cnt
End Sub

That simple macro is using good coding practice in that it only uses ( enables ) the error handler when it might need it. In other words we “turn it on” with
On Error GoTo sKip
and “turn it off” ( return to default VBA error handling) with
On Error GoTo 0

So far so good.
But the problem is that mostly when an error occurs, VBA coding goes into what is referred to as an “exception state”. The two main characteristics of the “exception state” are:
_ In this state further errors are handled by the default VBA error handler
_ Any attempts to enable another error handler are ignored.
( We can say those two things in a more simple way: VBA is busy in a state handling the first error and cant handle more than one at a time…)

One solution to the problem is to also include an
On Error GoTo -1
A good place for it would be before ,or after, the On Error GoTo 0
It makes no difference if it’s before or after: those two On Error statements don’t effect each other in any way***.

An alternative solution would be just to replace the On Error GoTo 0 with On Error GoTo -1. But that is slightly less good practice, since we have are error handler active for more of the coding than we probably need it.
BTW, if we did chose to just replace the On Error GoTo 0 with On Error GoTo -1, then we could move the On Error GoTo Skip to outside the Loop, so it’s only done once.
This last solution would work because On Error GoTo -1 does not disable the error handler:
On Error GoTo 0 disables the error handler, ( and BTW, On Error GoTo 0 does this in the normal or “exception state” ***)
_._______
In a recent forum post, the OP who I explained this all to came up with a simple summary I liked:
on error goto 0 — disables error trapping but does not reset the error state. Any new error trapping is ignored until the state is reset
on error goto -1 — resets the error state, restoring error trapping as it was before the error occurred
_.______

I should say finally that I agree with most people that one should try to avoid using error handling in coding to solve a possible expected problem , and instead use, if at all possible some other way that does not invlove error handling.
Run time error handling in VBA should be used intelligently. That is rather difficult in the practice since very few people, not even some the most experienced professionals, fully understand it.

This is a fine article on error handling in VBA. Most people just put “On Error Resume Next” at the top of the procedure and assume that any errors will be fixed automatically. A very bad assumption, but I’ve seen it more times than I like to think. There are a few minor points that I would add to your article. The first is that when code execution drops into an error handling block (e.,g “On Error Goto ErrHandler”), ALL error handling within the error handler is turned off, and if an run-time error occurs within the error handling block, you get an untrappable run-time error. For example, examine the following code.

Dim X As Long
Dim Y As Long

On Error GoTo ErrHandler

X = 10
Y = 0
10: Debug.Print X / Y

20:
ErrHandler:
Debug.Print «Last successful label: » & Erl
On Error Resume Next
30: Debug.Print X / Y

The code will raise an Div/0 error at line 10, at which execution transfers to ErrHandler: . VBA is now running in “error mode” and the Div/0 error at line 30 cannot be trapped. It will always raise an untrappable error. The code above also illustrates the use of the little-known Erl statement. Its value is updated every time a numeric line label is encountered, and can be read in an error handling block to indicate the last successful line label. In the code above, the debug message will show that line 10 was that last successfully labeled line. I put line labels to separate logically connected blocks of code, so I can easily see more or less where the error arose. There are any number of free add-ins that will put a line number on every line of code, and Erl will then identify the exact line of code. I think this is overkill. I put in a line label every 20 or so lines, depending on the structure of the code. Note that the line labels must be numeric (e.g, 10: . Erl will not recognize alphabetic labels (e.g,. ErrHandler: .

Next, should ALWAYS ALWAYS ALWAYS set error handling in the Options dialog to Break In Class Module. Suppose you have code to show a user form:

If a run time error occurs within UserForm1’s object module, the debugger will highlight the line above as the source of the error. Clearly, this line is not an error, and no matter how many hours you stare at that line, you won’t find an error. But if you set error handling to Break In Class Module, the debugger will take you inside UserForm1’s object module directly to the line of code that really is the source of the error.

I just thought you would find this interesting and I think it would be a worthwhile addition to your fine article on error handling. I usually code in VBNET or C# and use Try/Catch/Finally structures for error handling, and when I have to go into VBA, On Error in all its variations seems extremely primitive.

Thanks for reading the article and leaving such a long and detailed reply, much appreciated.

You make good points about both the un-trappable errors and enabling Break In Class Module. Nothing worse than being shown a line of code that hasn’t actually generated the error.

In the section ‘Error Handling With Multiple Procedures’ I tried to convey what you are saying about having a ‘last chance’ error handler, but I like your example of doing this if using code you didn’t write yourself.

I write a fair bit of PHP so I am familiar with Try/Catch and having that would indeed be a big improvement for VBA.

Great article. It helped me a lot with handling errors in my code.

I am a newbie and don’t have much knowledge about VBA. I am working on a module that throws an error inside the error handler. Can you guys please elaborate on how to handle if an run-time error occurs within the error handling block. I thought another error handler inside the first error handler would help but as Chip said it’s not going to work.

I am trying to scrape prices and there are more than 2 condition for scraping it. 1st one is taken care by the ‘ErrHandler1’ but I can’t get though how to make my code skip the error in ‘ErrHandler1’ and execute the code in ‘ErrHandler2’. It would be great if you guys can help me out.

Hard to give you advice without seeing the code and recreating the error.

Please post a topic on our forum and attach your workbook with a description of how the error is generated.

Thank you for this. I am working on improving error handling in my code. It seems to me it might be helpful to have an “error log” created that captures the values of various variables, the error number and description etc. Hopefully, I can figure this out.

I guess in an ideal world you’d be able to anticipate any errors and deal with them in your error handling routines. But for unforeseen circumstances, you could write the values of variables and any error data to a file and use that as your log.

Источник

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

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

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

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

Error Settings in VBA (Error Trapping)

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

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

Types of VBA Errors

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

1. Syntax Errors

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

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

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

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

2. Compile Errors

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

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

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

3. Runtime Errors

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

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

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

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

4. Logical Error

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

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

Using Debug Tools in VBA

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

1. Compile VBA Project

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

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

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

2. Run Each Line of Code One by One

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

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

Using “On ERROR” Statement to Handle VBA Errors

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

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

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

1. On Error Resume Next

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

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

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

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

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

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

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

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

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

2. On Error GoTo 0

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

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

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

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

3. On Error GoTo [Label]

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

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

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

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

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

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

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

4. On Error GoTo -1

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

O VBA! Live in Present

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

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

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

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

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

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

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

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

Err Object

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

Properties

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

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

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

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

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

Methods

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

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

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

Quick Tips on Error Handling

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

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

There’s More

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

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