VBA Error Handling

VBA Error handling is the process of anticipating, detecting, and resolving runtime errors that occur during code execution. The on-Error statement is used to handle errors in VBA.

There are three types of On Error statements:

  1. On Error GoTo 0
  2. On Error Resume Next
  3. On Error GoTo Line

The on Error GoTo 0 statement is VBA\’s default setting, which stops executing code and displays a standard error message box when an error occurs.

Sub test()
MsgBox 15 / 6
MsgBox 14 / 0
MsgBox 15 / 3
End Sub

Sub test_Resume_Next()
On Error Resume Next
MsgBox 15 / 6
MsgBox 14 / 0
MsgBox 15 / 3
End Sub

\'Another example with steps
Sub ex1()
Range(\"A1\").Value = \"welcome\"
Range(\"A2\").Value = \"to 2023\"
End Sub

Sub S1()
Range(\"Asdxff1\").Value = \"welcome\"
Range(\"A2\").Value = \"to 2023\"
Range(\"A3\").Value = \"This is new \"
End Sub


Sub S2()
On Error Resume Next
Range(\"Asdxff1\").Value = \"welcome\"
Range(\"A2\").Value = \"to 2023\"
Range(\"A3\").Value = \"Tsis is new \"
End Sub

\' if we want to know types of error and cause of error use err method

Sub S3()
On Error Resume Next
Range(\"Asdxff1\").Value = \"welcome\"
Range(\"A2\").Value = \"to 2023\"
Range(\"A3\").Value = \"Tsis is new \"
MsgBox Err.Number
MsgBox Err.Description
End Sub


Sub S4()
On Error Resume Next
Range(\"Asdxff1\").Value = \"welcome\"
Range(\"A2\").Value = \"to 2023\"
Range(\"A3\").Value = \"2024 \"

If Err.Number = 1004 Then
MsgBox \"We have found one error in program that is given after\"
MsgBox \"Range name is wrongly written\"
End If

End Sub