Chapter 10: Debugging VBACode

While we certainly have not covered all topics, you now have a good foundation in using VBA programming to access data and objects within the Access environment. In the past few chapters we have looked at programming structures, procedures, and many objects associated with the ADO libraries. We have even used SQL to create recordsets.

In this chapter we examine what happens when you encounter an error. In the process, we will discuss the types of errors that can occur, the tools you have available to fix them, how to step through an error, and how to write code to handle errors.

Basics of Error Handling

If you have tried code examples from this book, you have probably run into something like this:

What is going on is that the VBA Editor is trying to help you out a bit.

As you leave a line, the editor looks at the code and tries to spot potential errors. The first thing it does is try to repair the errors without your even knowing about it. If it cannot, however, you will get a message similar to the one shown.

This is a very simple example of a design-time error. Examples of design-time errors are undefined variables, missing End statements (such as End If, or End With), and improper syntax. Most of these errors will be easy to fix, and they require little discussion. You simply need to look up the proper syntax or follow the message that the VBA Editor will give you as to the type of error.

The more difficult type of error to solve is the runtime error.

Runtime Errors

Once you have written a program well, nothing ever goes wrong. True or false? I don’t think it would come as a surprise to anyone that the answer is false. In fact, a lot can go wrong that has nothing to do with the quality of the coding. As an example, as mentioned in previous chapters, you could open databases in different locations. What would your program do if the database file was missing from the location you indicated? What would happen if the database file was where it should be, but someone modified it, and a table or form being called was missing?

Some errors occur during the design stage, which, as you have just seen, VBA catches as soon as you leave the erroneous line of code (provided it is a syntax error and not an error in logic). Some errors occur when the code actually runs. This type of error—a runtime error—is often an error in logic. For instance, the code may cause an array to go past the upper boundary setting (“out of bounds” error). You have no way of knowing that this is happening until the code runs.

What we are going to be talking about here is known in programming parlance as exceptions. An exception is something that causes an interruption to the normal flow of the program. We use the word “exception” because this problem is usually something that will occur infrequently.

As a programmer, you must anticipate the potential problems and provide code to help the program resolve the problem gracefully. Again, in programming parlance, we call this exception handling. Since VBA does not distinguish between errors and exceptions, I will use the term “error” to mean exception. As a programmer, you must be able to anticipate what could go wrong and write the necessary code to handle it.

Let’s begin with a simple coding example where we will intentionally cause an error to happen:

Sub errorTest()   Dim intNumerator As Integer   Dim intDenominator As Integer   Dim intResult As Integer   intNumerator = InputBox("Please enter a numerator", "Numerator")   intDenominator = InputBox("Please enter a denominator",_ "Denominator")   intResult = intNumerator / intDenominator   MsgBox "The result is " & intResult End Sub

As you can see, this is a pretty simple bit of coding that asks the user to input an integer for the numerator and an integer for the denominator, divides the two numbers, and then outputs the results to a message box.

You can go ahead and give it a try. But I want to break a commandment of mathematics. When you start the code, you should see the first input box. Let’s enter the number 11 for the numerator:

After selecting OK, you are presented with the denominator input box:

Notice that 0 is entered as the denominator. Division by zero is a mathematical impossibility, which most programs treat as an error. Once you click OK, you should get the following error box.

Notice that this gives you an error number (11) and an error description (“Division by zero”). You also have the option of ending the program or, by clicking the Debug button, going to what VBA “thinks” is the offending piece of code. (I don’t mean to make the last sentence sound cynical. However, as many programmers will point out, where the error is, and where the program crashes, can sometimes be two different places.) The error box is the point where the program crashed. VBA is assuming everything before it is correct (and, in this case, it is). Note that in some cases, you are also given the option to continue the program. Such is not the case with “division by zero.”

Do you really want your end user to see this error box? Wouldn’t you like a more graceful way of helping your user to get out of this?

Let’s make some modifications to the code as indicated by the gray shading shown here:

Sub errorTest()   Dim intNumerator As Integer   Dim intDenominator As Integer   Dim intResult As Integer   On Error GoTo mytrap   intNumerator = InputBox("Please enter a numerator", "Numerator")   intDenominator = InputBox("Please enter a denominator", "Denominator")   intResult = intNumerator / intDenominator   MsgBox ("The result is " & intResult)   Exit Sub mytrap:   MsgBox "You cannot divide by zero" End Sub

Before we examine the code, let’s once again run it and force the “divide by zero” message. This time, instead of getting the error box shown earlier, you should see this new message box.

Defining what has happened is a much more graceful way of handling the error. You do this by adding the command On Error, which instructs VBA where to go when it encounters an error. In this case, you are telling it to go to the line of code that is labeled MyTrap:

myTrap:   MsgBox "You cannot divide by zero" End Sub 

Notice that this line is identified with the label myTrap ending in a colon. This is called a line label. (You also could have instructed VBA to go to a line number, but since line numbers can easily change, that is not the best solution.) Line labels can be a handy tool for moving through code nonsequentially. As a little aside, please note that while line labels are used most often in error trapping situations, as is shown here, they could be used in any type of nonsequential programming situation.

In programming parlance, this whole process is called error handling, and in this case, myTrap is an error handler. It will only run when an error is encountered, and its only job is handling an error.

We have one other problem that we solved with this code. Programming code runs sequentially. Normally, the preceding code would hit the error handler, myTrap, whether the user divided by zero or not. You prevent this from happening by issuing a command to exit the sub procedure, Exit Sub, right after the calculation is completed and the results are shown, and right before the line label and the error-handling code. That way, you assure that the error handler is isolated and will only be called upon if an error occurs.

One other small problem needs to be solved. We have error handling and an error handler, but when we dismiss the error message, the program ends. Wouldn’t it be far better to have a way to return the user to the input box to reenter the denominator?

Let’s add a couple of lines to our code as follows:

Sub errorTest()   Dim intNumerator As Integer   Dim intDenominator As Integer   Dim intResult As Double   On Error GoTo mytrap   intNumerator = InputBox("Please enter a numerator", "Numerator") enterDenominator:   intDenominator = InputBox("Please enter a denominator", _   "Denominator")   intResult = intNumerator / intDenominator   MsgBox ("The result is " & intResult)   Exit Sub mytrap:   MsgBox ("You cannot divide by zero")   Resume enterDenominator End Sub 

Notice that in this case, we used the command Resume inside the error-handler code. Normally, Resume will return the user to the line where the error occurred. This is handy if you have some code inside the error handler that might fix the problem. However, in our case, it would return the user to the calculation. We need to go back an additional line to the point where the denominator is entered. In order to solve that, we put a line label just before the denominator entry point and then, in the error handler, instructed Resume to go back to that line label.

All of this has created a tidy little solution that is fairly easy to follow and understand. But what happens if there are more complicated situations in which multiple errors could occur? To solve that, we need to go behind the scenes a bit more.