Error Handling

There are 3 type of error which occurs in VBA

1. Syntax Error

2. Runtime Error

3. Logical Error

Syntax Error:

Syntax errors are those error which occurs due to incorrect syntax. For example, we are writing IF condition in our code and we missed End If in the last then it will show a compile Error.

See the below code

Sub Syntax_Error()

Dim x As Integer

x = 10

If x > 5 Then

MsgBox "X is grather than 5"

End Sub

When we will run the above code it will display below given error.

Syntax Error in VBA

Before running our code we should always compile our code. To compile the code in the Visual Basic window go to Debug and click on Compile VBA Project or press Alt+D+L. If there is any syntax error available in the code it will display that.

Runtime Error:

Runtime errors are occurred during the execution of code. They are also called exceptions. For example, if we asign the text value in Integer variable.

Sub Syntax_Error()

Dim x As Integer

x = "ABC"

End Sub

When we will run above given code it will show a Type mismatch error because x is an integer and we are trying to assign “ABC” text in x.

Run Time Error

Logical Errors:

Logical errors can be the most difficult type of errors to track down. These errors are not the result of a syntax or runtime error.

You cannot catch those errors, because it depends on your business requirement what type of logic you want to put in your program.

For example, dividing a number by zero or a script that is written which enters into infinite loop.

Error Handling:

We should always try to prevent the expected errors while writing the code and should display a user friendly message to user.

For example, we have to open a workbook “MyProject.xlsx” from a path then we always should check whether “MyProject.xlsx” file is available in that location or not.

Sub Error_Handling()

Dim myfile As String

myfile = "C:\Users\UserName\Desktop\Projects\MyProject.xlsx"

' check if file exists or not

If Dir(myfile) = "" Then

' if file does not exist display message

MsgBox myfile & " is not available"

Exit Sub

End If

' this code will run when file is available

Workbooks.Open myfile

End Sub

On Error Statement

There are four different ways to use this statement

• On Error Goto 0 : the code stops at the line with the error and displays a message.

• On Error Resume Next : the code moves to next line. No error message is displayed.

• On Error Goto label : the code moves to a specific line or label. No error message is displayed. This is the one we use for error handling.

• On Error Goto -1 : clears the current error.

To handle an unexpected error we can use On Error Goto label. See the below example.

Sub Error_Handling()

On Error GoTo err_msg

Dim x As Integer

Dim y As Integer

Dim z As Integer

x = 10

y = 0

z = x / y

Exit Sub

err_msg:

MsgBox "Error! Error Number:-" & Err.Number & " Error Description:- " & Err.Description

End Sub

Error Handling in VBA

Next Chapter>>ADODB Connection