Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-2: VBA for Excel for the Errors
(Levels: Intermediate)

There are two types of errors that can occur when working with VBA for Excel, the VBA errors and the Excel errors. The VBA errors are those caught by VBA that trigger the error dialog window and that are generated when there are typos or logical errors in the code. The Excel error are the ones that VBA doesn't catch and that you handle with "IF" statements.

Handling Excel  Errors

An Excel error happens when  for example you are asking a user to submit a number  in cell A1 of a worksheet  and that the number  submitted is either too small or too large. In the following example you are expecting a user to submit a number between 100 and 200. If the value is smaller than 100 you want to tell the user but you agree to let the VBA procedure go on. If the value is greater than 200 you want to tell the user and stop the procedure. Here is the code:

Sub proTestExcelErrors()

          If Range("A1").Value < 100 Then
                  MsgBox "Value submitted is to small."
        ElseIf Range("A1").Value > 200 Then
                  MsgBox "Value submitted is to large."
                  Exit Sub
        End If

          ' here is the rest of the procedure.
                  MsgBox "The procedure goes on."

End Sub

VBA for Excel Errors

A VBA error happens when VBA doesn't understand or accept what you have written. A VBA error happens when:

- the name of an object, a property or a method is misspelled,
- an invalid method or property is called for a certain object. For example you cannot write ActiveSheet.Value because the sheet has no value. It has a name, it can be visible or not but it has no value.
- an invalid value is sent to a variable. For example you cannot store a text ("Peter") in a variable that you have declared as Integer (Dim varVariable as Integer).
- a value is not found by the Find method ( Selection.Find...). For example, you are looking for "Peter" in column A but it is not there.
- a variable has not been declared while working with the Option Explicit activated.
- and more.

It is extremely important to handle these errors for programmers who develop huge programs or video games that will be distributed on a large scale. That is why there are huge testing teams to test and try to find bugs in programs before they are released in the public. And even then.....Microsoft Internet Explorer....security bugs....

So the first step in handling errors is to test you program.

When you are developing VBA procedures in Excel for yourself it is not really important to handle errors. You work with your program and correct them as they happen. If you develop programs for a few people with whom you have close links you can forget about errors also. But if you are developing programs for large scale distribution you must handle the errors remembering the it will add a lot to the cost of your application. You can even form you own testing team.

VBA Errors Discovered while Typing the Procedure, while Trying to Run the Procedure
or while Running the Procedure

A lot of errors are caught by the Visual Basic Editor (VBE) and you are alerted not as you write the code but as you try to get to the next line while writing the code. The font of the line turns to red and a message box tells you what is wrong. Other errors are not detected as you write the code but as you try to run your VBA procedure or as you run it.

While typing

The VBE points at a lot of errors as you write the code. Try to write Range("A1" .Value for example. You will be alerted to the fact that a parenthesis is missing with the message box saying "Expected: End of Statement" (image below). You get alerted for missing parentheses, double quotes, commas, periods and other "separators" . But VBE will not alert you for typos like Range("1").Value or anything that is between double quotes Range("namedField").Value. The VBE won't alert you either if  you misspell the name of a variable.

You correct these errors and then you can run your macros step by step with the F8 key or by going to the menu "Tools/Macro/Macros" . At this point other VBA errors will be identified by the VBE.

While Trying to Run the Procedure

A second set of errors is  identified by the VBE as soon as you try to run the procedure. If   there are  some specific errors in the code the execution doesn't even begin. A different dialog window appears depending if you are running the procedure from the VBE, from Excel or from Excel when the procedure is password protected. The type of errors in this second category include the absence of End Sub, a End If missing, a Next or Loop missing an undeclared variable and some others. When these errors are identified by the VBE and the dialog window appears you don't have to worry about some of the code having been executed. The execution doesn't even start.

If you were trying to execute the procedure from Excel and that the procedure is password protected (protection activated) the following type of message  box appears.

Click on "OK" , go to the VBE, unlock the project and run the procedure.   The first line of code is highlighted in yellow, the problem is highlighted in blue and a different message box appears. Fix the problem, click on the blue arrow on the toolbar to resume the execution or click on the blue square on the toolbar and restart from the VBE or from Excel..

if you were trying to execute the procedure from the VBE the following type of message  box appears. If you were trying to execute the procedure from Excel and that the procedure is not password protected or the protection has been deactivated, the VBE opens  and the following type of message  box appears.

The first line of code in the code window of the VBE is highlighted in yellow and the problem is highlighted in blue. Fix the problem, click on the blue arrow on the toolbar to resume the execution or click on the blue square on the toolbar and restart from the VBE or from Excel.

While Running the Procedure

A third set of errors is  identified by the VBE as it tries to execute a certain incorrect  sentence in your procedure. This type of error includes  when you are asking VBA to open a workbook that it can't find, when you are asking VBA to activate a sheet that doesn't exit or when you are using the Find method and the value that you are looking for is not found.

From wherever the procedure has been started the following message box appears. If the procedure  is protected by a password and the protection is active the "Debug" button is not active (gray font) in which case the only solution is to end the procedure at the point where the error was found. This means that part of the task has been accomplished with whatever problems this creates.

If the procedure is not password protected you can click on "Debug" . The VBE will open and the faulty sentence will be highlighted in yellow.   Fix the problem, click on the blue arrow on the toolbar to resume the execution or click on the blue square on the toolbar and restart from the VBE or from Excel.

If the procedure is being tested from the VBE you can click on "Debug" . The faulty sentence will be highlighted in yellow. Fix the problem, click on the blue arrow on the toolbar to resume the execution or click on the blue square on the toolbar and restart from the VBE or from Excel.

Handling VBA Errors

Here is the basic way to handle an error.   The first thing you create is an address where VBA will jump if there is an error. In this example the address is addJump: with the NOT OPTIONAL colon at the end. Below addJump: is what is supposed to happen of there is an error and above is the procedure that should run if there are no errors. Notice that the last line of code in the section above addJump: is Exit Sub because if there are no errors you don't want the rest of the code to be executed.

Example 1:

Sub proTestErrorHandler()

        On Error GoTo addJump
        Workbooks.Open "xxxxxx"
        Exit Sub

addJump:

        MsgBox "An error has occurred, call Peter at 1 613-749-4695 "

End Sub

Example 2:

Sub proTestErrorHandlerNoError()

        On Error GoTo addJump
        MsgBox "Everything is fine."
        Exit Sub

addJump:

        MsgBox "An error has occurred, call Peter at 1 613-749-4695 "

End Sub

Copy/Paste both examples in a module of your own and run them. The first example will generate a message box saying An error has occurred, call Peter at 1 613-749-4695  because an error has occurred. The workbook xxxxxx can't be found. The second example will generate a message box saying Everything is fine.

If you run the procedure without the error handler:

Sub proTestErrorHandler()

          Workbooks.Open "xxxxxx"

End Sub

You end up with the following message box from VBA.

If you just want errors to be ignore you write On Error Resume Next  at the beginning  of the procedure. Copy/Paste the following procedure in a module of your own and run it. It will generate a message box saying An error has occurred but we have ignored  it.

Sub proTestErrorHandlerIgnore()

          On Error Resume Next
          Workbooks.Open "xxxxxx"
          MsgBox "An error has occurred but we have ignored  it."

End Sub

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple