Error Handling In VBA – Tutorial With Practical Examples

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

1. Syntax Errors
2. Runtime Errors
3. Compile Errors

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

Example:

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 Error

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

Runtime Error

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

Compile VBAProject

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:

1. On Error Resume Next
2. On Error GoTo 0
3. 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 1^st row, it will not throw any error, for the 2^nd row it will update the percentage but for the 3^rd 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.

Resume Statements

These statements will instruct VBA to resume the execution from the specified line.

There are 3 types of Resume Statements

1. Resume
2. Resume Next
3. Resume [Label]

#1) Resume 

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.

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.

Syntax: Err.Number

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.

Syntax: Err.Description

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.

Syntax: Err.Source

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.

Syntax: Err.Helpfile

HelpContext: This is used along with helpfile to identify the context number of the error in the file.

Syntax: Err.HelpContext

Err Object Method

VBA gives 2 methods that will help us in creating custom errors as well as in clearing the error object properties.

1. Err.Raise
2. Err.Clear

Err.Raise

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.

Syntax:

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.

Example:

Err.Raise Number:= vbObjectError + 600, _
Description:= “Sheet Name “”MySheet”” not found”

Err.Clear

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 2^nd 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?

Answer:

1. 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.
2. Compile Error: Error occurred while compilation. Identified during the execution of the code. For example, missing Next in the For statement.
3. 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.

Example:

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.

Conclusion

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