How to implement Error Handling in VBA projects?

Error Handling implementation is one of the best practice to write VBA code. Thus, error handling is very important part of every macro and it ease to handle unexpected exceptions in excel macros. Including error handling code in macro refers to good practice of coding. Because in case of any unexpected exceptions macro code doesn't break. Thus, it is very essential to have error handling in macro to avoid fatal unexpected error occurs in the code. So that we should ensure about proper termination for execution.


There are three kinds of On Error statements used for error handling in VBA projects:


1) On Error Goto 0:

This form of error handling is the default in VBA. Whenever any runtime error occurs VBA should display its standard runtime error message box. which contains Continue, End, Degub and Help options in error message box

2) On Error Resume Next:

This is most commonly used form. It instruct to VBA to ignore the error and resume execution on the next line of code



Sub Test_Err()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' http://vbatricksntips.blogspot.com
'
On Error Resume Next
div = 1 / 0    ' Cause an error
If Err.Number <> 0 then
div=0
End If
End Sub

3) On Error Goto <label>:

This form instruct VBA to transfer execution to the line following the specifed line label. Whenever an error occurs, code exection immediately goes to the line followed by the line label.


Sub Test_Error()
'
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

On Error GoTo ErrHandler:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    'Put your code here with or without any error

Application.ScreenUpdating = True
Application.DisplayAlerts = True

ErrHandler:

If Err.Number <> 0 Then

    MsgBox "Error Number is: " & Err.Number & vbNewLine & vbNewLine & "Please check for Below error Description:" & vbNewLine & Err.Description, vbCritical
    Exit Sub

End If

End Sub