Even when your code is perfectly constructed, the user might not always do what you would expect.  For this reason, you need to insert error handlers into your code whenever there might be a reason that an error might occur.

Handling an error when incorrect data is inputted by the user

If you were to populate an integer with a text string as in the procedure below…

Sub TestError
Dim intA as Integer
intA = InputBox("Please enter a value", "Testing errors")
End sub

When you run the routine, an input box will ask you to enter a value.

Enter some text.

Because text was entered, and not a value, an error will be returned.

Clicking on debug will show you the error, and resting your mouse on the line of code will give you the values or any variables or constants that occur in that line of code.

In the above example, due to the fact that the variable is an integer variable, it will not accept a text string – we therefore have a type mismatch.  We need to trap this error to make sure that the user does not type text into this particular text box – but only types a number.

We therefore need to create an error handler at the top of the procedure using On Error Goto.

Sub TestError1()
On Error GoTo errorhandler

And at the end of the procedure before End Sub, we need to put an Exit Sub, and then put the label errorhandler, and a message box with the error message.

Exit Sub
errorhandler:
MsgBox ("Please make sure a number is inputted")
End Sub

Therefore is anyone does type text into the input box, a message box will appear telling them to type a number:

Your entire code would therefore look like this:

Sub TestError1()
On Error GoTo errorhandler
Dim intA As Integer
    intA = InputBox("Please enter a value", "Testing errors")
    Exit Sub
errorhandler:
    MsgBox ("Please make sure a number is inputted")
End Sub

Handling an error when incorrect data comes from a spreadsheet

You may encounter an error when your macro is looking for a specific object, like a worksheet, but fails to find it.

Sub TestWorksheet
Sheets(3).Activate
End sub

However, our workbook may only contain 2 sheets!

Therefore, this error would occur when the procedure is run.

To avoid this happening, we once again need an error trap.  The error line goes just below the Sub line

Sub TestWorksheet()
	On Error GoTo ErrorHandler

And we then need a label called ErrorHandler, and a message to let us know what has occurred.

Exit Sub
ErrorHandler:
Msgbox ("Sheet does not exist!")
End Sub

Exit Sub
ErrorHandler:
Msgbox ("Sheet does not exist!")
End Sub

And when you run the code, you’ll get the following message:

Examining the error code syntax

• You always need to have On Error GoTo at the beginning of a procedure, below the procedure name.
• The On Error GoTo line need to refer to a LABEL that the code will jump to should an error occur.  The label above is ErrorHandler but it can be any text that you like (eh, continue, carryon etc).
• The label but have a colon after it eg: ErrorHandler:
• There MUST be an EXIT SUB above the label line – otherwise when the code is run and an error DOES NOT occur, the error message will show anyway.

       For example, in the code below, the Exit Sub has been commented out so the code continues to the error handler.

The correct code looks as per the example below:

Sub TestWorksheet()
'error trap line
On Error GoTo ErrorHandler
'go to sheet three
    Sheets(3).Activate
'exit the sub if sheet three is not found
    Exit Sub
'error handle label
ErrorHandler:
'error handle activity if error exists
    MsgBox ("Sheet does not exist")
End Sub

How to avoid errors

• Design your application carefully by writing down what you want the application to achieve and the relevant events that you are going to create.  Write down the way you want your code to respond to each event and give each event procedure and general procedure a well-defined purpose.
• Comment your code generously – it will make it a lot easier to go back and analyze and understand.
• Explicitly refer to variable and constants.  Declare variables with the relevant data type.  Use Option Explicit to avoid spelling mistakes in variable names.
• Develop a consistent naming scheme for variables, controls and other objects.
• Use functions like IFERROR for additional error handling

See also: How to handle unpredictable input in input boxes