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:
The value of any variable may be displayed as shown:
- ?straina(3): ?straina(4): ?straina(5)
- 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:
Values may also be displayed using the statement Debug.Print, rather than “?” :
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
The Debug.print command can also be used within the code to send data to the immediate window:
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”
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:
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:
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.