Saturday, 14 October 2017

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.
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.

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.

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
'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
'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.

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

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


  1. It'ѕ a shame yyou ԁߋn't have a donate button! І'd
    certаinly donate tto tһis excellent blog! Ι guess foor now i'll settle fοr book-marking and adding үоur RSS feed to mү Google account.
    I look forwqrd tօ brand neᴡ updates and ѡill share thіѕ
    site with my Facebook group. Chat sоon!

  2. Hey there! Do you use Twitter? I'd like to follow youu if that
    would be ok. I'm undoubtedly enjoying your blog annd look forward to new updates.

  3. Fascinating blog! Is your theme custom made or did you download it freom somewhere?

    A theme like yours with a few siple tweeks would really make my blog shine.
    Please let me know where you got your design. Thanks

  4. It's not my first time to pay a quick visit this web page, i am
    visiting this web site dailly aand get pleasant information from here everyday.

  5. This is my first time visit at here and i amm in fact happy to read all at single place.


Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts