There are 3 type of error which occurs in VBA
- Syntax Error
- Runtime Error
- Logical 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.
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 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.
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.
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