Debugging

Debugging is the process of stepping through the code line by line and checking the reaction of each line to help trace errors that may be difficult to find at run time especially logical errors.

The Debug toolbar allows users to step in, out, over or watch certain variables change state in a controlled manner and can be switched on or off in the Visual Basic Editor window.

1	Design Mode.
2	Run Sub/User Form starts the macros where the insertion point is or displays a Macro Dialog Box.
3	Break pauses the macro while it’s running and switches to break mode.
4	Reset current macro clearing all breaks, step into/over procedures and variables.
5	Toggle Breakpoint allows marking a line of code at which point a macro will stop.
6	Step Into a macro one line at a time.
7	Step Over a macro one line at a time ignoring any other sub routines.
8	Step Out over a macro and continue running the rest of that macro.
9	Locals Window is displayed showing all variables and expressions with values for the procedure currently running.
10	Immediate Window is displayed allowing pasting of code to the window and testing the code by using the ENTER key (cannot save contents).
11	Watch Window is displayed allowing drag ‘n’ drop of expressions into it to monitor their values.
12	Quick Watch displays a Dialog Box showing the current line of codes value.
13	Call Stack displays a Dialog Box listing all active calls statement to the current procedure. This option is used when using a step procedure.

The most effective way to debug a procedure is to learn some keystrokes and mark breakpoints in the code.

To add breakpoints, place the mouse pointer to the left grey margin at the point where you wish to pause the procedure and click once with the left mouse button, click button 5 (as above) or press F9 function key (toggles on/off).

When you run the procedure or press the F5 key, the procedure will pause at the first highlighted break:

At this point, users can either continue to run the remaining procedure (press F5 key) or step through line by line by pressing the F8 key.

By placing the mouse pointer over any variable or object property, the user will, after a few seconds, see the current value assigned.

Alternatively, by revealing the Locals Window (button 9 above), users can see all variables and property’s values:

After a few steps (F8 key):

Debugging between calling procedures can be controlled as the F8 key steps in order line by line across more than one procedure.

To step out of a sub procedure and carry on with the main procedure, press the SHIFT + F8 keys.

Debug.Print Command

A return value will be printed to the Immediate Window (button 10 above or CTRL + G).

Two ways to print an output value in the immediate window:

Debug.Print Expression
? Expression (within the Immediate Window)

Sub CalcPay()
    On Error GoTo HandleError

    Dim hours
    Dim hourlyPay
    Dim payPerWeek
    hours = InputBox("Please enter number of hours worked", _
                                        "Hours Worked")

    Debug.Print "hours entered " & hours

    hourlyPay = InputBox("Please enter hourly pay", "Pay Rate")
    payPerWeek = CCur(hours * hourlyPay)
    MsgBox "Pay is: " & Format(payPerWeek, "£##,##0.00"), _
                                                           , "Total Pay"
HandleError: 'any error - gracefully end

End Sub

The above will print the ‘hours’ variable to the immediate window:

If you set a breakpoint and have the Immediate Window visible, you can use a different method to reveal the current values of any known variable or property:

Type a question mark ( ? ) followed by a space and then the variable or property and press the enter key to reveal the output value.

Next Topic: Creating User Forms

Want to teach yourself Access? Free online guide at About Access Databases

Debugging

Debug.Print Command

Tip: Keyboard shortcuts are quick and simple. Learn F5, F8 and F9.

VBA Keywords: On Error GoTo, MsgBox, InputBox, Debup.Print & CCur.