Error Debugging

Now that I have covered several basic ways to write VBA code and to control the flow of the program, let’s look at the types of errors you may encounter, how to debug them, and how to write error handlers to handle them.

Types of Errors

The types of errors include: syntax errors, compile errors, runtime errors, and logic errors, each of which will be discussed in turn.

Syntax Errors

Syntax errors are errors that occur because the code you wrote does not comply with the required syntax. Figure 2-30 illustrates a syntax error that occurred because the As part of the Dim statement is missing.

Figure 2-30

Compile Errors

Compile errors are errors discovered on compilation that may have a correct code syntax, but for some reason will not compile. Figure 2-31 illustrates a compiler error that has occurred because a CalculateTax function cannot be located. In this particular example, the reason for the compile error is that the function is incorrectly listed as CalculateTax instead of CalculateSalesTax.

Figure 2-31

Runtime Errors

Runtime errors occur at runtime when the code executes. Runtime errors can include errors that occur because of something that it not allowed or supposed to happen. For example, a line of code that tries to assign a variable of one data type to an incompatible value could result in a runtime error. The example in Figure 2-32 runs the CalculateSalesTax function and passes string values as parameters, instead of the required currency and double values. The result is a runtime error, which in this example is a Type Mismatch error.

Figure 2-32

Logic Errors

Logic errors are flaws in the logic of your code. Logic errors can be the hardest to find because they are syntactically correct but can only be discovered by testing your code and ensuring it produces the desired and appropriate result.

Debugging Your Code

You certainly need the capability to debug and correct your VBA code as you encounter errors. As you may recall from Chapter 1, the Visual Basic Editor provides several ways to help you debug and correct your code. Now we’ll look at some of these methods in more detail.

Using Breakpoints to Step through Code

Breakpoints can be set on one or more lines of code. When a line of code that has an associated breakpoint is reached, code execution stops and you can then choose to Step Into, Step Over, or Step Out of the code. Selecting the Step Into option from the Debug menu will run the current line of code at the breakpoint. The Step Over option will skip the current line of code at the breakpoint and move on to the next line of code.

Try It Out-Setting and Using Breakpoints

It’s your turn to set a breakpoint and use it to step through code.

1. Navigate to the TestLoop procedure you created previously in the modBusinessLogic standard module.

2. Set a breakpoint on the Debug.Print and the Next intCounter lines of code, as shown in Figure 2-33. You can set a breakpoint by pointing and clicking the cursor just to the left of the line of code where you want to add a breakpoint.

   
   Figure 2-33

3. Next, open the Immediate Window and run the TestLoop procedure. The code will stop execution at the first breakpoint, as shown in Figure 2-34.

   
   Figure 2-34

4. While at the breakpoint, use the Immediate Window to inquire about the current value of the intCounter variable. To do so, type ? intCounter and press Enter, as shown in Figure 2-35.

   
   Figure 2-35

5. The current value of intCounter is displayed in the Immediate Window. You should press F5 or select Run Continue to keep executing the lines of code. Execution will stop on each breakpoint.

How It Works

The breakpoint enables you to closely monitor and determine the values of variables and take other actions that will allow you to test and review your code most effectively. This example allows you to see how the value of intCounter increments as the loop repeats.

Using the Immediate Window

As you are well aware at this point, you can use the Immediate Window to call procedures and to display the results of a line of code. You can also use the Immediate Window to assign new values to variables. You have already learned that the Debug.Print statement writes output directly to the Immediate Window. The Debug.Print statement can be used for your testing purposes.

Using the Locals Window

The Locals Window can be used to see a list of all the objects and variables that are used in the current procedure. The values in the list are updated every time you suspend execution with a breakpoint or step through code.

Try It Out-Using the Locals Window

Now, let’s walk through an example of using the Locals Window.

1. Display the Locals Window by selecting View Locals Window.

2. Run the TestLoop procedure again from the Immediate Window.

3. The code execution will again stop when the first breakpoint is reached. Notice that the values in the Locals Window are updated with the current value of intCounter.

4. Press F5 or select Run Continue to keep executing the lines of code. You should see that the values of intCounter in the Locals window change as you walk through the code, as shown in Figure 2-36.

Figure 2-36

How It Works

The Locals Window allows you to closely monitor and determine values of variables. This example allows you to see how the value of intCounter increments as the loop repeats.

Using the Watch Window

The Watch Window can be used to see a list of objects and variables in the current procedure. The Watch Window can be used to monitor the value of variables as breakpoints are encountered, just as the Immediate and Locals Windows can. The Watch Window also provides additional features, such as allowing you to add a Watch that will cause the code execution to break when a certain event or value occurs. This enables you to have the system break for you automatically when the certain event occurs without having to set a predetermined breakpoint.

Try It Out-Adding a Watch

Let’s look at an example of adding a watch using the Watch Window.

1. From the Debug menu in the Visual Basic Editor, choose Add Watch, as shown in Figure 2-37.

   
   Figure 2-37

2. On the Add Watch dialog box, specify intCounter as the Expression, TestLoop as the procedure, and modBusinessLogic as the Module.

3. Click the OK button on the Add Watch dialog box, and a Watch Window will appear.

4. Run the TestLoop procedure again from the Immediate Window. You should see the values in the Watch Window change when the first breakpoint is encountered.

How It Works

The Watch Window allows you to closely monitor and determine values of variables and also to cause code to automatically enter a break mode when certain events happen. The example shown in Figures 2-38, 2-39, and 2-40 illustrates how the value of intCounter increments as the loop repeats, just as the prior examples did.

Figure 2-38

Figure 2-39

Figure 2-40

Using the Call Stack

A call stack can be used to trace through the process of nested procedures. You can use the call stack window when VBA is in break mode by selecting View Call Stack. However, you have to create your own call stack if you are not in Debug Mode. You have various ways to create your own call stack, such as to write various messages to the Immediate Window as you enter certain procedures to see the order in which they were called.