How to Handle Runtime Errors in VBA Macro with On Error GoTo Label

On Error GoTo Label to Handle Runtime Errors in VBA Macro
Error Handling refers to the code that is written to handle errors which occur when your application is running. These errors are normally caused by something outside your control like a missing file, database being unavailable, data being invalid etc.
If we think an error is likely to occur at some point during run time, it is good practice to write specific code to handle the error if it occurs and deal with it.

Mostly, the Error Handling technique is used to handle the Run time Errors.
To understand error handling we must first understand the different types of errors in VBA.

Syntax Errors:
The most common errors detected automatically by VBA system .When you type a line and press return, VBA will evaluate the syntax and if it is not correct it will display an error message.
Eg:
If you type 'If' and forget the Then keyword.

Compile Errors :
Compile Errors are recognized by the VBA compiler as being illegal and they are highlighted as errors before your macro even starts to run.
If a Compile Error is an incorrectly formatted line of VBA code, then  the Vba Editor will immediately detect and highlight this, as soon as you attempt to move your cursor away from the specific line of code.
Alternatively, a compile error may be detected at the time you attempt to run your macro but before execution has started.

Eg:
Type Mismatch,
For without Next,
If statement without corresponding End If statement ,
Select without End Select

Runtime Errors:
Runtime errors occur during the execution of your code, and cause the code to stop running.
This type of VBA error is also generally easy to fix, as you will be given details of the nature of the error, and shown the location where the code has stopped running.
Eg:
File Not Found Error,
Path/File Access Error,
Copy Method of Range Class Failed Error

Logical Errors:
Logical Errors, also known as 'bugs', occur during the execution of the VBA code, and allow the code to continue to run to completion. However, the 'bug' may cause the macro to perform unexpected actions or return an incorrect result .
These errors are the most difficult to detect and fix, as there is no way that the VBA compiler can identify and 'point to' the error, in the way that it does for compile and runtime errors.
These types of errors generally occurs by missing the logic in the code designed.
We can identify these errors by observing each step(By pressing ‘F8’) of a Macro running, with Local Window and Watch Widows.

Mostly, the Error Handling technique is used to handle the Run time Errors.
The VBA On Error statement is used for error handling. This statement performs some action when an error occurs during runtime.

There are four different ways to use this statement :
1. On Error Goto 0 – The code will clears the current Error Handler once it finished the handling and then re-enables the Error Checking in further lines of code to stop at the line with the error and displays a message.
2. On Error Resume Next – The code will ignores the Error and moves to next line. No error message will be displayed.
3. On Error Goto [label] – The code moves to a specific line or label, where we defined the Error Handling mechanism. No error message is displayed. This is the one we use for error handling.
4. On Error Goto -1 – Clears the current Error Handler.

Example :

In the following Macro we have defined various Error Handling Techniques.

Sub VBAerrorDebug()
Dim InpMsg As VbMsgBoxResult
Dim FindTotal As String

'On Error Resume Next

On Error GoTo ErrHand1:

Sheets("MySheet").Select 'Error 1-Sheet Does not Exist
Sheets("Sheet1").Range("A1:C6").Copy
'Clearing the above Error Handler1 and then Re-Enabling the Error Checking in Further Lines of Code
On Error GoTo 0 'This must declare after you handled the Error to clear the above error Handler1 and the re-enable the Error checking again in the further lines of Code.

On Error GoTo ErrHand2:
Sheets("MySheet").Range("A1").Paste 'Error 2-Paste Method Failed
ActiveSheet.Range("A1").Select
'Clearing the above Error Handler2 then Re-Enabling the Error Checking in Further Lines of Code
On Error GoTo 0 'This must declare after you handled the Error.

TryAgain:
FindRegion = InputBox("Search for the Region to know Net Sales", "Find a Region Net Sales")

On Error GoTo ErrorHand3: 
ActiveSheet.Cells.Find(FindRegion).Select 'Error 3-Search String not Found
MsgBox "The Region " & FindRegion & " Net Sales is : " & Selection.Offset(0, 2).Value, vbOKOnly, "Search Result"
'Clearing the above Error Handler3 then Re-Enabling the Error Checking in Further Lines of Code
On Error GoTo 0 'This must declare after you handled the Error.

Exit Sub

ErrHand1: ' Error Handler 1
Sheets.Add.Name = "MySheet"
'Raising your Own Custom Error Message
'Err.Raise 123, "WorkSheet", "Work Sheet Not Found"

'Displaying Default Error Message Details
MsgBox "Error Number :  " & Err.Number & vbCrLf & _
"Error Description :  " & Err.Description _
, vbOKOnly, "Details of Error Handled"
Resume Next 'The will take back the execution of code to the next line where it stops.

ErrHand2: 'Error Handler 2
Sheets("MySheet").Range("A1").Activate
ActiveSheet.Paste
Resume Next

ErrorHand3: 'Error Handler 3

UserResponse = MsgBox("Search Region Not Found", vbRetryCancel, "Search Result")
If UserResponse = vbRetry Then Resume TryAgain

End Sub

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------