Tools for Debugging Code

As stated earlier, many times the runtime error will be caused by poor programming logic. For instance, code may be out of sequence, or a variable value may not be set before it is called upon. These are sometimes referred to as logic errors.

The VBA Editor offers a number of tools to track the sequence of events in your code and detect the value of variables at any given point.

The Immediate Window and Breakpoints

The Immediate window offers a variety of tracing and debugging tools that are quite easy to use. For testing purposes, let’s use the following simple code example:

Sub addNumbers()   Dim intNumber1 As Integer   Dim intNumber2 As Integer   Dim intSum As Integer   intNumber1 = 5   intNumber2 = 3   intSum = intNumber1 + intNumber2    Debug.Print "The sum of the two numbers is: " & intSum End Sub

This sets two variables, adds them, and prints the results to the Immediate window.

Up to this point, we have run procedures by clicking on the Run button. However, you can also run the procedure by simply typing its name into the Immediate window, as shown here:

It makes no difference if you run the procedure from the Run button in the toolbar or type the name in the Immediate window. The results are the same.

If this was all the Immediate window could do, its usefulness would be limited. However, let’s look at a couple of handy features that can help you trace problems in your code.

You can set a breakpoint in the code. This tool allows you to set a point for the code to halt and wait for you to tell it to resume operation. You can set a breakpoint by clicking in the margin to the left of the line where you want to stop the code.

In this example, let’s stop the code where the two variables are added and assigned to the sum. You should see the code highlighted at the breakpoint:

If you now run the procedure, the code will halt at the breakpoint. You have several ways to check the assignments to the variables. As an example, you can put the mouse pointer right over the variable name and a tool tip will show you the assigned value. This is shown in Figure 10-1.

Figure 10-1: The assigned value to intNumber1

You could also test the value in the Immediate window by typing a question mark followed by the name of the variable you are testing. You would then press ENTER to get the following result:

Once the values are established, you can continue operation of the code by clicking on the Continue button (the same button as the Run button) in the Standard toolbar.

The Immediate window also lets you override the assigned value. While the code is halted at the breakpoint, you can type the name of the variable you want to change and assign a new value. Then press ENTER. Once the code is resumed, the results should reflect the new value, for example:

The breakpoint can be taken off the code the same way it was put on: by clicking in the margin.

The breakpoint feature offers another advantage. You can reassign the sequence. As an example, suppose you set the breakpoint to be the intNumber1 assignment. Then further suppose you want to bypass the intNumber2 assignment and go directly to the addition. When you run the code and it hits the breakpoint, you can take the margin indicator and drag it to the line where you want to resume the code. Then resume the code.

You can also build a breakpoint directly into the code using the keyword Stop.

Assert

You could also use the Assert member of the Debug object to test the value of an object. In the following code, we are going to ask the Assert method if the value assigned to intNumber1 is 5. We will then ask if intNumber2 is 7. We know in the first case it will return true, and in the second case it will return false.

Sub addNumbers()   Dim intNumber1 As Integer   Dim intNumber2 As Integer   Dim intSum As Integer   intNumber1 = 5   intNumber2 = 3   Debug.Assert intNumber1 = 5   Debug.Assert intNumber2 = 7   intSum = intNumber1 + intNumber2   Debug.Print "The sum of the two numbers is: " & intSum End Sub

If you run the procedure now, you get the results shown in Figure 10-2. It skipped over the first Assert because it was true, but flagged the false value of the second assertion.

Figure 10-2: The Assert method flagging the false value

The Locals Window

With a breakpoint set, the Locals window will show any objects and variables assigned in the procedure, as well as the current values assigned to them. As an example, if you set a breakpoint at the addition point and run the procedure, the code will halt there. You then select View | Locals Window to open the Locals window, shown in Figure 10-3.

Figure 10-3: The Locals window

Here you find the assigned values, at that point, for the two variables. Since the code was halted before the addition took place, intSum is showing a value of 0. You can also click on one of the values, change it, and press ENTER. This will assign a new value to the variable in much the same way that you did in the Immediate window earlier.

The Locals window also allows you a rather unique way to trace your code. Select View | Toolbars | Debug. This opens the Debug toolbar. One of the buttons, Step Into, allows you to walk through the code one line at a time.

You can open the Locals window and then use the Step Into button to walk through the code. As you press the button and advance the code one line at a time, you will see the values of the variables change in the Locals window.

If a variable is an array, it will be shown with a [+] to the left of it. When you click on that [+], the tree will expand to show the values of individual elements of the array.

The Watch Window

The way the Watch window works is similar to the Assert method you saw earlier. It allows you to set a condition to test for and stop the code if the condition is not met.

Let’s assume you want to test to see if intNumber2 was assigned a value of 3. Start off by highlighting the statement. Then select Debug | Add Watch. Figure 10-4 shows the dialog box that opens.

Figure 10-4: Adding a Watch

You can then tell it to just watch the expression, break if the expression is true, or break if the value changes. Once you make the decision as to what action to take, the following window opens when you select OK.

The value will remain “out of context” until the procedure runs. Once it runs, the value will show and the appropriate action will be taken depending on your choices, as Figure 10-5 shows.

Figure 10-5: The code flagged and the Watch window showing true