Last Updated on January 11, 2023 by token
VBA Error handling – How to use it?
In this article you will learn what VBA error handling is and how to use it correctly. In VBA, as in any other programming languages errors appear. They may be caused by incorrect syntax or logic of the program. Errors that may occur in VBA can be group into:
- Syntax errors
- Logic errors
- Runtime Errors
- Object errors
Each of the above errors can be diagnosed and handled in the VBA code. The most important things is error number and its description.
VBA error handling – Error Handling
When you want to handle error in Excel VBA, you need to wrote short application called Handler, which is the code that triggers a specific action after the error occurred. How to write error handler? The short syntax of the error handling code is described below:
On Error { GoTo [ line | 0 | -1 ] | Resume Next }
Resume Next
By entering the On Error Resume Next code at the beginning of the program, you inform VB to ignore all errors. So, when the error occurs, the program will skip it and go to the next line of code. This is very useful instruction, for example when you are creating automatic reports based on Excel.
'OfficeInside.Org Sub ErrorHandling() On Error Resume Next 'typical error - division by 0 MsgBox 1 / 0 MsgBox "Example message" End Sub
GoTo Line
This is the command to refer to the called Handler. At the time of the error, the program using the VBA GoTo instruction jumps to the code block in which the details of the error are described. You are using here the function Err determine the error number , a description of the error, a reference to the relevant section in the help file. After handling an error, you can clear it using the Err.Clear command. A specific example of error handling with the help is written here:
'OfficeInside.Org
Sub ErrorHandling2()
On Error GoTo Error_handler
'typical error - division by 0
MsgBox 1 / 0
MsgBox "Example message"
Error_handler:
MsgBox "Error occured" & vbCrLf _
& "error number: " & Err.Number & vbCrLf _
& "error description: " & Err.Description & vbCrLf _
& "help file: " & Err.HelpFile & vbCrLf _
& "error number in help file: " & Err.HelpContext & vbCrLf _
& "source: " & Err.Source
Err.Clear
End Sub
GoTo 0
Disables handling of errors by Handler. For example, part of your code should be handled by a handler, and the other part should not. Using the On Error GoTo 0 instruction, you disable the previously declared error handling. Below is an example. In the first case, the divide by zero error was handled, in the second it was not.
'OfficeInside.Org
Sub ErrorHandling3()
On Error GoTo Error_handler
'typical error - division by 0
MsgBox 1 / 0
MsgBox "Example message"
Error_handler:
MsgBox "An error occured"
Err.Clear
On Error GoTo 0
Debug.Print 1 / 0
End Sub
Below is the file with the program codes:
The article is part of the Excel vba course. The whole course can be found at this link: VBA course.
