Read this detailed tutorial on Error Handling in VBA to understand different types of errors and how to handle them in your programs:
Errors have always been a part of our life in every field. Coding will also have errors but we should know how to handle them effectively.
In this tutorial, let’s understand the different ways that VBA provides to handle errors and perform the execution smoothly.
=> Visit Here To See All Excel VBA Tutorials
What You Will Learn:
- What Is An Error
- Error Handling In VBA
What Is An Error
Error is basically a mistake in the code you have written and that mistake or error does not make your code work as expected and thereby gives you an unexpected output.
Types Of Errors
There are 3 main types of Errors in VBA
- Syntax Errors
- Runtime Errors
- Compile Errors
We all know that Syntax is an integral part of any programming language. VBA checks for any syntax errors each time when you hit enter and displays a dialog showing the expected syntax.
Let’s consider an example of missing a syntax in an IF statement.
Sub syntax() Dim i As Integer if i=1 End Sub
In the above example, we have missed adding a Then at the end of the If statement, hence we have an error dialog showing the same.
Note: Make sure that Auto Syntax Check is enabled under Tools -> Options as shown below. If it’s not enabled you will just see the error line in red without any error dialog.
Compile errors are similar to syntax errors but these are not identified while writing the code, rather it is identified only when the code is executed.
Consider the below example, where we have written a For loop without a Next statement.
This error is not identified when the code was written and saved. The error is displayed only during execution.
Sub compile() Dim i As Integer For i = 0 To 10 result = i + 1 End Sub
Consider another example where we have used option explicit statement but have not declared the variable.
Option Explicit Sub compile() Dim i As Integer For i = 0 To 10 result = i + 1 End Sub
This error occurs when the data used in the code has a problem. For example, you are trying to read a file that doesn’t exist or write a string value to a cell that is expecting a number value.
Let’s consider a simple example to enter a value greater than 300 to a variable of data type byte.
Option Explicit Sub runtime() Dim i As Byte i = 300 End Sub
In the below example we are trying to open a file that doesn’t exist.
Option Explicit Sub runtime() Dim i As Integer Workbooks.Open Filename:="D:\text.txt" i = 300 End Sub
VBA provides a feature that helps you to find all the compile errors using Debug -> Compile VBAProject.
When you use Compile VBAProject, the VBA compiler goes through each line of the code, stops, and shows a dialog when it finds an error. You can correct the error and repeat the steps.
Once all the errors are fixed and there are no more compile errors, then the Compile VBAProject option is disabled.
Error Handling In VBA
On Error Statements
On Error Statements activate the error handling in VBA. Using this statement you can instruct VBA to perform certain actions when the code encounters an error.
Given below are the 3 On Error Statements:
- On Error Resume Next
- On Error GoTo 0
- On Error Goto Line
#1) On Error Resume Next
This command will instruct the compiler to ignore any error in the code and execute the next step. You should be very cautious while using this, as you might get into serious trouble by ignoring certain Errors.
Consider the example of calculating the percentage. The format of Excel is shown below.
Let’s write a function using On Error Resume Next.
Function Percentage() As Double On Error Resume Next Dim sh As Worksheet Dim ObtainedMarks,TotalMarks As Integer Set sh=ActiveSheet For i=2 To sh.UsedRange.Rows.Count ObtainedMarks=sh.Cells(i, 2) TotalMarks=sh.Cells(i, 3) sh.Cells(i, 4)=(ObtainedMarks/TotalMarks)*100 Next i End Function
In the above sample, Excel notices that the total marks for row 1 are entered as a string and row 3 as zero.
When you execute the above code, though there are errors in the Total_Marks in lines 1 and 3 the code will not throw any error, rather it would just leave the percentage column blank.
The resulting table will be as shown below.
Hence On Error Resume Next statement is only used when you are aware of the error that you are going to encounter and it’s alright to ignore that.
#2) On Error GoTo 0
By using this VBA will enable the default settings of Error handling i.e. VBA will stop the execution at the line which contains an error and display an error dialog with a description, as we have seen in the discussion on types of errors.
Now you must be thinking as to what is the point of using it in the code. Well, this is used along with On Error Resume Next.
Suppose you have 10 lines of code and in the first 4 lines you are expecting a known issue and you wish VBA to ignore the same, so you decide to use On Error Resume Next.
But for the rest of the lines, you want VBA to stop the execution if it encounters an error and that is when On Error GoTo 0 comes in handy. It resets the error settings and stops execution when there is an error.
Let’s rewrite the above code to reset the error settings only if the i value is 4.
Function Percentage() As Double On Error Resume Next Dim sh As Worksheet Dim ObtainedMarks, TotalMarks As Integer Set sh = ActiveSheet For i = 2 To sh.UsedRange.Rows.Count ObtainedMarks = sh.Cells(i, 2) TotalMarks = sh.Cells(i, 3) If i = 4 Then On Error GoTo 0 End If sh.Cells(i, 4) = (ObtainedMarks / TotalMarks) * 100 Next i End Function
In the above code if i is 4 then error handling is reset. So you if execute this code, then for the 1st row, it will not throw any error, for the 2nd row it will update the percentage but for the 3rd row i value becomes 4 and hence if block will be executed and then the error message will be displayed.
#3) On Error GoTo Label
The above 2 methods will not really help you to handle the error in the way you wish. On Error GoTo Label will help you to write the code and handle the error in your desired way.
You need to instruct the compiler to go to a specified label whenever there is an error in the code. For the same percentage calculation example, if you execute the code using the GoTo label as shown below, then the errors can be identified at the early stages.
Function PercentageGoToLabel() As Double On Error GoTo ErrorCode Dim sh As Worksheet Dim ObtainedMarks,TotalMarks As Integer Set sh=ActiveSheet For i=2 To sh.UsedRange.Rows.Count ObtainedMarks=sh.Cells(i, 2) TotalMarks=sh.Cells(i, 3) sh.Cells(i, 4)=(ObtainedMarks/TotalMarks)*100 Next i Exit Function ErrorCode: sh.Cells(i, 4)=Err.Description End Function
In the above example, when the code encounters an error then the lines under the label “Errorcode” will be executed and the error description will be added to the percentage cell in the worksheet. (More about Err. The description will be explained later in this tutorial)
Note: Exit Sub or Exit function is used to ensure that if there are no errors then the code under the Label is not executed. If you miss adding the Exit Sub/function, then the code under the Label will always execute even though there are no errors.
The result table will be as shown below.
Notice the result that the code exited upon entering the first error, and it skipped going to other rows. This is because the moment the code goes to the error code, it will only execute the lines inside it and the lines below the error label. Hence in our example, it skipped the other 2 rows in the worksheet.
VBA provides you with Resume statements to solve this issue.
These statements will instruct VBA to resume the execution from the specified line.
There are 3 types of Resume Statements
- Resume Next
- Resume [Label]
This will resume the execution at the same line where the error had occurred. You can use this only if you are sure that the error is fixed in the error code under the GoTo label.
Otherwise, you will end up in an infinite loop.
Sub resumeExample() On Error GoTo Label Dim number, total, result As Integer number = 444 total = 0 result = (number / total) * 100 Debug.Print result Exit Sub Label: total = 500 Resume End Sub
In the above example, you can see that the result = (number/total)*100 will encounter a divide by zero error. Hence control will go to error Label and here the total value is set to 500. The resume statement will instruct VBA to resume execution from the same line which caused the error,
So the result = (number/total)*100 will be executed again but this time with the total as 500.
The result is shown below.
#2) Resume Next
This will resume the execution at the immediate next Line after the line which caused the error.
Let’s use Resume Next in our example and check the results.
Sub resumeNextExample() On Error GoTo Label Dim number, total, result As Integer number = 444 total = 0 result = (number / total) * 100 Debug.Print " Value of total is " & total & " and value of result is " & result total = 500 result = (number / total) * 100 Debug.Print " Value of total is " & total & " and value of result is " & result Exit Sub Label: Resume Next End Sub
In the above example when the result = (number/total)*100 is executed, the control will go to an error code called Label. The error code just instructs VBA to resume execution from the next line which assigns the total value as 500.
The result is as shown below.
#3) Resume Label
This is similar to GoTo Label, where the control will resume from the label specified.
Let’s rewrite the percentage calculation code using Resume Label.
Function PercentageGoToLabel() As Double On Error GoTo ErrorCode Dim sh As Worksheet Dim ObtainedMarks,TotalMarks As Integer Set sh=ActiveSheet For i=2 To sh.UsedRange.Rows.Count ObtainedMarks=sh.Cells(i, 2) TotalMarks=sh.Cells(i, 3) sh.Cells(i, 4)=(ObtainedMarks/TotalMarks)*100 GoTo Loopincreament Exit Function ErrorCode: sh.Cells(i, 4)=Err.Description Resume Loopincreament Loopincreament: Next i End Function
Hereafter, the error code is executed and we have instructed VBA to resume the execution from the code under the label Loopincreament.
|Miran||456||0||Division by zero|
Multiple Error Handler
It is always recommended to include multiple error handlers in the procedure so that you can capture all the errors and fix them effectively to achieve smooth execution and desired results.
For example: Let’s write a code to handle type mismatch error and a Divide by 0 error in a different way using 2 different Error handlers.
Sub MultiErrorExample () On Error GoTo Err1 Dim a,b,sum as integer a = 100 b = "ab4" sum = a+b Debug.Print " Sum of a and b is " &sum On Error GoTo Err2 number =0 result = 10/number Exit Sub Err1: Debug.Print Err.Description " has occurred " b= 200 Debug.Print " changing the value of b from ab4 to " &b Resume Exit Sub Err2: Debug.Print Err.Description " has occurred , quit from the Sub " Exit Sub End Sub
In the above example, the error occurs because there is a string value stored in an integer variable and then it is trying to calculate the sum of a and b.
As there is a type mismatch error, the control jumps to Err1 and prints the Error description, it also changes the value of b to 200 and Resumes the execution from the line that caused the error, and prints the sum.
When the code reaches result = 10/number, it jumps to the error code Err2 and it just prints the error description and exits from the Sub.
The result is as shown below.
Err Object Properties
In the previous examples, we have used Err. The description will give us the error description. Likewise, for all run-time error, VBA gives the information that identifies the error and is called Error object properties.
There are 5 properties of an error.
Number: VBA has given a unique number for every error. VBA error can take any number between 0 to 65535. It reserves 0 to 512 number to identify the system error. So if you are raising your own error then you can use numbers 513 to 65535.
Description: This gives the error description as we have seen in our examples above. Like Type mismatch, Overflow Error gives an idea for the programmer to identify and fix the errors.
Source: This gives details about the error location. Helps to identify the source of the error, like a class module name, a procedure name, etc. The Default is set to project. class.
HelpFile: This gives the location and the name of the file of the helpfile. If HelpFile is not specified, then path and filename for the Visual Basic Help file are used.
HelpContext: This is used along with helpfile to identify the context number of the error in the file.
Err Object Method
VBA gives 2 methods that will help us in creating custom errors as well as in clearing the error object properties.
Sometimes you as a programmer may want to write your own error to give the error properties you desire. This can be achieved using Err. Raise method.
Err.Raise number, source, description, helpfile, help context
Except for number, all the other arguments are optional.
As mentioned before, number 0 to 512 is used by VBA for generating system errors. Hence while generating custom errors, always use the numbers from 513 to 65535.
Consider the below example in which we are trying to enter a value in a Sheet named MySheet without using Err. Raise
Sub RaiseMessage() Sheets("MySheet").Range("A1").Value = "Error Raise example" End Sub
When you execute the above code, VBA gives you the below error.
Looking at the error, we won’t understand what and where exactly the issue is.
Now let’s rewrite the same code and create our own custom Error using the Err. Raise method.
Sub RaiseMessage() Dim Flag As Boolean Dim Sheet As Worksheet For Each Sheet In ActiveWorkbook.Worksheets If Sheet.Name = "MySheet" Then Flag = True Exit For End If Next Sheet If Flag = False Then Err.Raise 600, _ Description:=" Sheet Name ""MySheet"" not found" Else Sheets("MySheet").Range("A1").Value = "Error Raise example" End If End Sub
The result is as shown below.
So any new programmer can clearly understand what the problem is and fix it quickly.
Note: While giving the Number to your custom error code in a class module, you need to add your error number to vbObjectError.
Err.Raise Number:= vbObjectError + 600, _
Description:= “Sheet Name “”MySheet”” not found”
Err Object resets itself automatically when either of these is true – Exit Sub, Exit Function, Resume statement, On Error statement is executed.
But if you want to clear the error object explicitly then use the Err. Clear method. This will reset the properties of the Err object.
Function Percentage() As Double On Error Resume Next Dim sh As Worksheet Dim ObtainedMarks,TotalMarks As Integer Set sh=ActiveSheet For i=2 To sh.UsedRange.Rows.Count ObtainedMarks=sh.Cells(i, 2) TotalMarks=sh.Cells(i, 3) If Err.Number <> 0 Then MsgBox "Error Number is : " & Err.Number & vbNewLine & "Error Description is " &Err.Description Err.Clear End If sh.Cells(i, 4)=(ObtainedMarks/TotalMarks)*100 Next i End Function
The result is shown below.
Suppose you don’t clear the error properties after entering the IF block, then VBA will continue to retain the error properties.
In our example in the first row itself, VBA will throw a Type mismatch error so if you don’t clear that, though there is no error in the 2nd row, VBA will still throw an error as it has retained Err. Number as 13. Hence it is necessary to clear the error number.
Frequently Asked Questions
Q #1) How do you handle errors in VBA?
Answer: VBA provides Error Handler statements like
On Error Resume Next – Recommended using only when you are aware of the potential errors in the procedure.
On Error GOTO Label – This allows you to handle the error in the way you desire. As soon as you enter, the error is encountered, and control will jump to the label specified in the procedure.
Resume, Resume Next, Resume Label statements allow you to decide where the execution should be resumed after the error is handled.
Q #2) What are error handlers in Visual Basic?
Answer: Visual Basic provides Error Handling statements that help the programmer identify the error and allows us to fix them so that the execution of the procedure goes smoothly and obtains the desired result.
Q #3) What are the 3 Types of Errors?
- Syntax Errors: Error occurred due to the problem in the syntax. Usually identified while writing the code. For example, using Then for an If statement.
- Compile Error: Error occurred while compilation. Identified during the execution of the code. For example, missing Next in the For statement.
- Runtime Error: Error occurred while running the code mainly due to data issues. For example, divide by zero error.
Q #4) How do you stop VBA error messages?
Answer: Open the Excel workbook.
Go to Tools -> Options
Uncheck Auto Syntax Check.
This will prevent VBA from showing up in the dialog when there is a syntax error, but it will continue to show the error code in red color.
Q #5) What is On Error GoTo 0 in VBA?
Answer: By using this, VBA will disable the error handlers used in the procedure i.e. the procedure will stop the execution at the line that contains an error and displays an error dialog. This is used along with On Error Resume Next.
Suppose you have 10 lines of code and in the first 4 lines, you are expecting a known issue and hence you wish VBA to ignore the same. So you use On Error Resume Next. But for the rest of the lines, you want VBA to stop the execution if it encounters an error. This is when on error GoTo 0 is used to reset the settings.
In this tutorial, we have understood how to deal with errors in the code. We have learned the different types of errors and saw how you can resolve them. We have discussed ON Error statements and Resume statements that are useful to catch the error and fix them accordingly.
We have also learned about Err Object properties that give the details about the error that occurred during execution and Err Object methods to raise our custom error and also clear the error properties.
=> Check ALL Excel VBA Tutorials Here