Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 7 - Error Handling

Lesson 7.1 - The On Error Statement

Topic 7.1.1 - Ignoring Errors

By far the easiest way to handle a run-time error is to have your code ignore it! While this simple approach won't be appropriate in every case, it's a good way to introduce the concept of run-time errors and the use of the On Error statement.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above. You'll find a worksheet containing a number of shapes which you can click on to move around the worksheet:

You can click on an arrow shape to move in the direction indicated by the arrow.

We want to ensure that the user doesn't experience an error message if they attempt to move beyond the boundary of the worksheet:

What happens if we attempt to move above the top row of the worksheet for example?

Identifying a Run-Time Error

Run-time errors are problems that occur when your code attempts to perform an instruction but, for some reason, can't. A run-time error is easy to spot as you'll be presented with a dialog box when it occurs. You can generate a run-time error in the example workbook by attempting to move beyond the edge of the worksheet:

Select cell A1 and then click the arrow shape shown here to attempt to move up and left, beyond the edge of the worksheet.

When the code fails, you'll be presented with a run-time error dialog box explaining what has gone wrong:

The error message provides some basic information about what has gone wrong.

You can click End on the error message to stop the procedure at the point it has failed. It's often more useful to click Debug to see which instruction has caused the failure:

Click the Debug button on the dialog box shown above to see the instruction which caused the error highlighted in yellow.

Once you have identified the instruction which has caused the run-time error, you can reset the procedure by choosing Run | Reset from the menu.

Using the On Error Statement

To prevent the error message appearing, we can add an On Error statement which will instruct the procedure to ignore any run-time errors generated by the instruction. In the Move_Up_And_Left subroutine, add a new line above the only instruction in the procedure:

Begin the new line with the On Error statement.

The On Error statement instructs the procedure what to in the event that a run-time error is caused by any subsequent instructions in the procedure. In this example, the subroutine contains only one other instruction. If the instruction fails it means that we can't move to the cell that is indicated. If that's the case, we want the procedure to ignore the instruction and continue at the next available line of code. We can achieve this by adding Resume Next at the end of the line:

Resume Next tells the code to ignore the instruction which caused the error and proceed as though nothing has gone wrong.

Testing the Code

Return to Excel and select cell A1 then click the button which selects the cell above and to the left:

Click the button to move up and left.

This time you won't see an error message. You should check that the code still works when you have a different cell selected. Try selecting cell B2 and clicking the button again:

Clicking the button with cell B2 selected will move to cell A1.

To practise ignoring run-time errors:

Select a cell in row 1 of the worksheet and then click the arrow pointing upwards:

Select a cell in the top row of the sheet and click the up arrow.

Click Debug on the error message that appears:

Click Debug to see which instruction has caused the error.

Identify the instruction which caused the error and then choose Run | Reset from the menu:

The line which caused the error will be highlighted in yellow.

Add an On Error statement above the line which caused the error which tells the subroutine to ignore the error:

Sub Move_Up()

On Error Resume Next

ActiveCell.Offset(-1, 0).Select

End Sub

Return to Excel and check that clicking the up arrow no longer causes an error message to appear when you are already at the top of the worksheet.
Add an On Error statement to each of the other subroutines in Module1 to make sure that run-time errors are ignored in each procedure.
Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Identifying a Run-Time Error

Using the On Error Statement

Testing the Code

Head office

London

Manchester