Onerror VBA
First though, there is an Option in the Tools menu > Options which tells the debugger how to react to errors. If you have this Option set to Stop on All Errors, the following code won't work because VBA will throw up the error and stop the code. This behavior only occurs when running code in the IDE. If the code were run ,say, from the Macros menu, this would not happen...
The Option is in the Tools menu > Options, on the General tab, on the right side, Error Trapping. Since we're not using a Class Module, you can click either Break in Class Modules or on Unhandled Errors. Since we will be "Handling" errors, the debugger won't stop/break the code, as we'll see...
The idea here is that if there is an error, such as assigning a String to an Integer variable, instead of the user getting an error message, we can handle the error. It goes like this...
Public Sub OnError_VBA_Code()
' The following line is saying: If an Error occurs, Go to (continue executing
' code from) the "Onerror_VBA:" label
' (the label is a few lines below from here).
On Error GoTo Onerror_VBA
Dim MyInt As Integer
' Here we try to put a String value in an Integer variable.
' (In real life, this could happen by a user entering a String in an Input Box
' that asked for a number).
MyInt = "A9"
' As will be explained, when the code at Onerror_VBA runs, just before it would exit/end
' the sub a "Resume Next statement tells VBA to resume execution with the line that
' follows the line that caused the error,
' On Error GoTo 0 "means" cancel Error Trapping.
On Error GoTo 0
' [Code that we don't want to trap errors goes here]
' "On Error Resume Next" means, if an error is encountered, just keep going.
On Error Resume Next
' Here, we cause the error, but keep going anyway.
MyInt = "A9"
' When an error occurs, the Err object holds the error.
' All errors have a number. If the Err.Number is 0 there is no error.
If Err.Number = 13 Then
' Display the error number and its description.
MsgBox "Error " & Err.Number & ": " & Err.Description
' Reset Err to "No error" by "Clear"ing it.
Err.Clear
End If
' Here, we would have been at the End Sub line had we not used error handling and
' so want to Exit/End the Sub. I.e. Go to the End Sub line.
Exit Sub
Onerror_VBA:
' Here, we display the error. (Above, we handled the error within the code sequence).
MsgBox "Error " & Err.Number & ": " & Err.Description
' As mentioned above, if we arrived here (because there was an error) Resume with the line
' following the line that got us here.
Resume Next
End Sub
This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more and on how to build a success-guaranteed site with no technical skills.
Return from On Error to VBA Code Samples
