Following on from the opening post in this series (Debugging VBA) I will now look at the main debugging tools provided in the Visual Basic Editor (VBE):

The Immediate Window allows interaction with the code while it is running.  In the VBE select View-Immediate Window, or Ctrl-G to display the window.  Some examples of using the window are shown in the screen shots below:

Display variable values in the Immediate Window (Click any image for full size view)

The value of any variable may be displayed as shown:

• ?straina(3): ?straina(4): ?straina(5)

Note that:

• I have placed a break point in the code (click in the left margin, or press F9 with the cursor on the required line) at the line immediately following the one where the last array value of interest is assigned.
• Each command is followed by a “:” so that the three array members may be entered without pressing the Enter key.  The immediate window performs the command on the current line as soon as the Enter key is pressed, but a number of commands may be concatenated with the colon symbol.
• The commands may be entered directly or copied and pasted from the spreadsheet or a text editor.
• If the routine is a UDF it may be initiated by selecting the function on the spreadsheet, pressing F2 (Edit), and then pressing Enter (or Ctrl-Shift-Enter for an array function).
• Unlike the Locals Window, the values in the Immediate Window may be copied and pasted to the spreadsheet for easy comparison with calculated values.  In the screen shot the values have been copied to column M.
• Immediate Window results remain available after the routine has completed.

The same results can be obtained, with less typing, using a For Loop:

Immediate window using a For Loop

Values may also be displayed using the statement Debug.Print, rather than “?” :

Immediate Window with Debug.Print

It is also possible to enter more complex commands in the Immediate Window.  In the example below the array values are subtracted from the values calculated on the spreadsheet, to check that the result is equal to zero, or very close to it.  The full line is:

• For i = 3 to 5: ? Range(“J6:J8”).cells(i-2,1).Value – Straina(i): Next i

Immediate Window with data read from the spreadsheet in a For Loop

The Debug.print command can also be used within the code to send data to the immediate window:

Using Debug.Print commands in the VBA code

The other main VBE window used in debugging is the Watch Window, allowing the value of specific variables to be monitored.  The Watch Window is opened from the View menu, and is initially blank.  A watch point is added through the Debug-Add Watch menu, which opens the Add Watch dialog, having the following features:

• The expression to be watched is entered, which may be either a single variable, or a more complex expression.
• The applicable Procedure and Module for the watch point are assumed to be those at the current location in the VBE edit window, but these can be changed.
• The “Watch Type” may be set to one of either “watch expression”, “break when value is true”, or “break when value changes”

Add Watch Dialog

Alternatively a Watch Value Point may be inserted through the Debug – Quick Watch menu (or Shift-F9), with the cursor located at a variable in the Edit window.  This will insert a watch point for the selected variable, which may then be edited by right-clicking on the variable line in the Watches window.  A third way to add (or remove) watch points is through the right click menu in the Watches window.

Use of the Watches window, with three watch points, is shown in the screen shot below:

Watches window with three watch points

Note that in this case, there are no specified break points, but when the function was initiated it ran through until the first watch point had been  evaluated, where execution was stopped because this point had been specified as “break when value changes”.  The next two points were then evaluated by using the F8 key to step through the code, giving the results in the screen shot above.

A watch point may also be specified as a complete array, rather than a specified array position.  This is shown in the screen shot below:

Watch point on an Array

In this case the watch point has been specified as watch only, and a break point has been inserted below the point in the code where array values 3 to 6 are evaluated.  As with the Locals window, the array initially appears as a single summary line, and must be expanded to view the values for each item.