Debugging | Beginning Access 2002 VBA (Programmer to Programmer)

So far in this chapter we've spent quite a lot of time discussing the different types of errors, and how to prevent them. Now you need to know how to track them down, and as you become more experienced , you'll find you get quite good at tracking down errors. In fact, you could say that making mistakes is a required part of the learning process, since it gives you first hand knowledge of the debugging process.

In this section we will learn how to use the VBA debugger to help track down errors in our code. The debugger enables us to pause the execution of code and then examine variables and logic line by line.

Program Execution

VBA provides a number of ways to help you navigate through your code while it's running. When tracking down errors, this allows you to start the debugging process at a place where you know the code works. You can then gradually narrow down the areas that need checking, until you find the error.

All of the useful debugging facilities are located in two places within the Visual Basic Environment. The first is the Debug toolbar:

If this toolbar isn't visible, select the Toolbars option on the View menu.

The second set of facilities, which duplicates some of the debug toolbar, is available from the Debug menu:

You can customize the toolbars and menus , but these are the default settings, and allow you a great deal of control over how you debug your program. Let's look at these facilities in more detail before we start using them.

Breakpoints

Breakpoints are the heart of debugging, as they allow you to mark lines where you would like to suspend the program. This allows you to temporarily stop the program, perhaps to check some variables, or to start stepping through the code line by line. You can set breakpoints on a line in several ways:

Clicking the Toggle Breakpoint button
Selecting the Toggle Breakpoint option from the Debug menu
Pressing F9
Clicking in the border at the left of the module window

As shown, a breakpoint is identified by a red background, and a red dot in the border on the left of the module window (we know it's gray in the book).

All of these methods act as a toggle, so if there is already a breakpoint on the line it will remove the breakpoint. There is no limit to the number of breakpoints you can have. You cannot set breakpoints on variable declarations, but you can set them on the start and end lines of procedures. If you want to clear all the breakpoints you have set in one fell swoop, you can select Clear All Breakpoints from the Debug menu.

While the program is running and a line with a breakpoint is encountered , the program will halt and the breakpoint line will be highlighted. You can also halt the program by pressing Ctrl+Break while the program is running, which generates the following dialog:

At this point you can continue with the program, end the program, or switch into debug mode, which stops the program after the line that is currently being executed.

Assertions

An assertion is a statement used to test the value of an expression. When using the Debug.Print statement you probably saw Assert in the quick tips for Debug , allowing you to do Debug.Assert .

Assertions in VBA allow you to place expressions into your code that will be ignored if they are true, but will cause a breakpoint if they are false. For example, consider the following procedure:

 Public Sub TestAssertion(intNumber As Integer)   Debug.Assert intNumber = 10 End Sub

The expression we are testing for is if intNumber is 10 . If it is, nothing happens, but if it isn't, a breakpoint is generated on the line. This allows us to have conditional breakpoints, and can be quite useful if you only want to halt execution under certain conditions.

The Stop Statement

As well as breakpoints and assertions, you can use the Stop statement anywhere in your code to force VBA to suspend execution. This is exactly like putting a breakpoint on the line, and is quite useful as it allows you to clear all breakpoints from a program, but still make sure a program halts at a selected place. For example:

 Private Sub Foo()   Dim intI As Integer   Stop   For intI = 1 To 1000     ...   Next End Sub

This would ensure that the code is halted on the Stop line, just before the loop.

Don't forget to remove any Stop statements before you release your product.

Continuing Execution

You can use the Continue button to continue execution of a halted program. Execution will continue from the current line until user input is required, or a breakpoint is reached.

Stopping Execution

You can use the Reset button at any time to halt the execution of a running program. This doesn't place the program in debug mode, but stops all execution and resets all global and static variables.

Stepping through Code

Stopping at a breakpoint is all very well, but you really need to look at lines of code as they are executed. Being able to move through the code one line at a time is a great way to examine variables, see what's happening, and generally understand how the program is working. This allows you to see exactly where problems lie. There are several ways of stepping through code:

Symbol	Shortcut	Description
	F8	The Step Into button runs only the line that is currently highlighted, that is, the current line. If that line happens to be a procedure call, then it will step into the procedure, and allow you to continue single-stepping through the code within the procedure.
	Shift+F8	The Step Over button runs only the line that is currently highlighted, that is, the current line. If that line is a procedure, then the procedure is executed, but single-stepping of the procedure does not occur. The next line to be single-stepped will be the line after the procedure call.
	Ctrl+Shift+F8	The Step Out button continues execution of a procedure until the line after the calling procedure. So if Procedure A calls Procedure B and you accidentally step into Procedure B, when you really meant to step over it, you can step out, which will place you back in Procedure A, and the line after the call to Procedure B.
	Ctrl+F8	Run To Cursor continues execution from the current line and halts on the line that contains the cursor. It's a bit like setting a temporary breakpoint. This button isn't shown on the Debug toolbar by default, but can easily be added, or accessed through the shortcut or from the Debug menu.

The advantage of the Step Into method is that you can check every line in every procedure that is used. However, if you have a large number of procedures, then this is also a disadvantage as it's much slower to step through more lines of code, especially if the procedures contain loops . For example, with a loop counting from 1 to 5,000 you would have to step through the loop 5,000 times. Step Over allows you to run a procedure quickly, and is especially useful if you know the procedure works. This allows you to concentrate on debugging the procedures that you are unsure of. Using Run To Cursor is really useful for loops, since it allows you to continue execution until the line after the loop.

Re-running Lines of Code

When you start debugging programs, and are single stepping through code, you might find lines of code that are wrong. If you are in debug mode, then you can change lines of code while the program is running, and re-run the line. This allows you to fix problems without having to stop the program and start again, which is a real boon if you've got a complex procedure. There are some changes that will force the program to stop, such as deleting variables, but for many cases you can edit as you go.

To re-run a line of code you can just grab the arrow in the code window border that indicates the current line and drag it to the new line:

When the cursor is in the left margin, the cursor changes to an arrow and you can press the left mouse button, and without releasing the mouse button, drag it to the new line. Once on the new line release the mouse button, and the new line is highlighted as being the next line to be run.

Now if you continue running the program it will run from the new line. This technique is also good for skipping lines of code.

Skipping Lines of Code

As you've seen in the previous example, you can re-run lines of code, but a similar technique can be used to move the current line forward in a program, and skip the lines of code altogether. You can also place the cursor on the new line and press Ctrl + F9 or choose Debug Set Next Statement .

You should always be careful when skipping lines of code, especially if they contain procedures. Remember that this isn't like the Run To Cursor option where the code in between is run. Setting the next line forward in a program means that any lines you skip are not run, so if you are dependent upon what they do your code might not work in the way it is supposed to.

Changing Code

VBA allows you to change code while the program is stopped , so you can correct any errors as they occur. What you can't do is change variable definitions while a procedure is running, since this would impact on the way that VBA stored the variables. If you need to change variable definitions, but don't want to reset your program and start from the beginning, then you can only do this in procedures that are not currently being executed. So, you could finish the current procedure, step out of it, change the variables, and then step back into it again.

Changing code is not just limited to a single line. You can create whole chunks of code, use code structures and loops, and move the current line around as you see fit.

If you try to change variables, or make another change that might cause the program structure to change (such as adding or deleting procedures), then you'll get a warning message:

If you click the OK button your program will be stopped, just as if you'd pressed the Stop button. Pressing Cancel will undo the change you've just done that caused this warning.

Commenting Out Lines

Another way to skip lines of code is just to comment them out by putting a comment character ( ' ) at the front of the line. If you comment out the currently active line, then VBA sets the next line as the active line. Uncomment the line again, and VBA moves the current line back - it's clever enough to realize that the line hasn't been run.

Block Commenting

This is a trick that doesn't work while you're in debug mode, as it causes the program to halt, but it's pretty useful if you need to comment out a whole section of code. The normal way to do this would be to put a comment character at the beginning of each line, which is quite tedious . However, there's a much quicker way, using block commenting. This is a feature of the VBA editor that puts a comment at the beginning of a whole block of code. This doesn't appear on any menus by default so you will have to customize a menu to add this feature.

If you press the right mouse button when the cursor is over a toolbar or menu you can select the Customize option. Select the Commands tab, and then select Edit from the Categories list box. If you now scroll the Commands list a little you'll see two items - Comment Block and Uncomment Block . Just drag these and drop them onto a menu or toolbar of your choice - the Edit menu is a good place.

Once these are on the menu you can highlight a whole section of code and then select Comment Block from the menu. All of the selected code will have a comment character placed at the beginning of the line.

The Call Stack

The Call Stack is a list of procedures that are currently being executed. You might think it's obvious which procedure is being executed, but if you have highly-structured code, with several procedures, you might lose track of where you are. This is especially true if you have some generic procedures that are called from several places.

You can view the call stack by pressing the Call Stack button:

This shows what happens when we put a breakpoint in our Factorial routine. This is the current procedure so it is shown at the top. The first procedure called is shown at the bottom - in our case this is the Click event procedure for the cmdCalculate button. Any other procedures are shown in the order in which they were called, from the bottom upwards.

When this window is shown you can double-click (or select the procedure and click Show ) on any procedure to be taken to the code for that procedure. For example, if we show the code for the command button:

Here you can see that the first line in Factorial is highlighted, showing it is the current line, but that an arrow points to the line in the Click event that caused Factorial to be called. You can see this is a call to Combinations , which in turn calls Factorial .

The Immediate Window

You've already seen and used the Immediate window, but since you'll be using it a lot more we ought to just examine the things that are possible in it. Quite simply it allows you to examine variables, change variables, and even run procedures.

If you want to output information to the Immediate window you use the Debug.Print statement, followed by the details you want printed. Within the window you can use Debug.Print , Print , or ? to see a value.

The Locals Window

The Locals window contains all of the variables, and values, that are within the current scope. So that is all variables local to the current procedure, as well as global variables. For example, when our code was at a breakpoint in the Factorial function, the Locals window looks like this:

You can see there are three local variables, along with their values and data types. One, intNumber , is the function argument, and there are two local variables for the function name . Don't worry about this, as there are always two copies when using functions. At the top you can see Me , with a small plus sign next to it. As you know, Me corresponds to the current form, so clicking on the plus sign will expand Me to show you all of the properties for the form.

As you step through the program the values of the variables change, so you can see exactly what's happening. You can also double-click on the value and type in new values to see how that will affect things. Be careful though of using this window when examining a large and complex object (like a cell range in Excel) - it can take an eternity for VBA to iterate through all of the relevant child objects' properties when you click the button of the parent object to expand it.)

The Watch Window

The Watch window is similar to the Locals window, but is used only for variables you choose. In fact, it's very similar to a window for assertions, since you can specify variables and conditions, and specify whether a break point is to occur when those conditions are met. You can also set breakpoints for when a variable changes, which can be extremely useful if a variable is being changed, but you're not sure where. You might not think this could be a problem, but with a large program and lots of variables, it's easy to lose track. This does slow down execution of the program though, but this is generally not a problem when you are debugging.

Quick Watches

Quick watches are an easy way to add a watch to the Watch window. When the cursor is placed on a variable, or an expression is highlighted, you can press the Quick Watch button, or Shift + F9 to see the value of the variable or the result of the expression, then click the Add button to add it to the normal Watch window.

Hovering

This is the trick of holding the mouse pointer over a variable when the program is paused to see the contents:

This is great for quickly finding out the value of variables.

Try It Out-Debugging Code

In this example, we're going to be looking at two procedures. The first, ShowIces , will print all of the ice cream names to the Immediate window. The second, ShowIngredients , prints the ingredients for each ice cream. If you don't want to type this code in you can find it in the IceCream.mdb database, in the Chapter 12 Code module.

Create a new module, and add a global variable.
```
   Private m_db As Database   
```

Now create the first procedure:

   Public Sub ShowIces(blnQuantity As Boolean)     Dim recIces As Recordset     Set m_db = CurrentDb()     Set recIces = m_db.OpenRecordset("tblIceCream")     Do While Not recIces.EOF     Debug.Print recIces("IceCream")     ShowIngredients recIces("IceCreamID"), blnQuantity     recIces.MoveNext     Loop     recIces.Close     Set recIces = Nothing     End Sub

Now the second procedure:

   Private Sub ShowIngredients(lngIceID As Long, blnShowQuantity As Boolean)     Dim recIngredients As Recordset     Dim strSQL As String     strSQL = "SELECT tblIceCreamIngredient.Quantity, tblIngredient.Name " & _     "FROM tblIngredient INNER JOIN tblIceCreamIngredient " & _     "ON tblIngredient.IngredientID=tblIceCreamIngredient.fkIngredientID" & _     " WHERE tblIceCreamIngredient.fkIceCreamID = " & lngIceID     Set recIngredients = m_db.OpenRecordset(strSQL)     Do While Not recIngredients.EOF     Debug.Print vbTab; recIngredients("Name");     If blnShowQuantity Then     Debug.Print vbTab; recIngredients("Quantity");     End If     Debug.Print     recIngredients.MoveNext     Loop     recIngredients.Close     Set recIngredients = Nothing     End Sub

Now put a breakpoint on the first executable line in the first procedure. That's the one with CurrentDb() on it:
```
   Set m_db = CurrentDb()   
```
View the Immediate window (you can press Ctrl+G to open it if it is not already available, or select View Immediate Window ) and run our first function by typing in:
```
   ShowIces True   
```
When the code has stopped at the first line, view the Locals window:
Press F8 to step through the first line, and click on the plus sign next to Chapter 12 Code in the Locals window:

Notice that this has expanded to show all of the global variables in the module - in this case there's only one. You can see that recIces is a Recordset , and that because it hasn't been assigned yet, it has the value of Nothing .
Press F8 again to step through the next line of code. This opens the recordset on the ice creams table. Keep an eye on the Locals window:
Notice that recIces is no longer set to Nothing . If you expand recIces, you'll see all of the recordset information. You can also verify this from the Immediate window. Try typing this:
```
   ?recIces("IceCream")   
```
This prints out the value of the IceCream field for the current record.
Press F8 again, and hold the cursor over recIces :

This is a really quick way of getting to see what's in a variable.
Make sure the Debug toolbar is visible (just right-click over an existing toolbar and select Debug from the pop-up menu).
Press the Step Over button, or press Shift+F8 to step over the Debug.Print line. For a line that is not a procedure call it doesn't actually matter whether you Step Over or Step Into . If you look at the Immediate window, you'll see that the ice cream name has been printed out.
Now you are on a procedure line. Let's first step over, so you can see what happens, so press Shift+F8 again. Have a look at the Immediate window:

Notice that the ShowIngredients procedure has run, but that you didn't have to single step through it.
Press Step Into or Step Over four more times, so that you are back on the call to ShowIngredients . Now press Step Into to step into the procedure. See how you are now on the first line of the new procedure.
Press Step Into three times, until you are on the While statement. Keep an eye on the Locals window, just so you can see the values of the variables changing. Now click on the Wend statement and select Run To Cursor (either from the Debug menu, or by right-clicking for a pop-up menu). Notice how one set of ingredients has been printed to the Immediate window.
Select Step Out from the toolbar. This will run the rest of this procedure, and place us back in the calling procedure:
Press F8 three times, until you are back on the Debug.Print line. Highlight recIces("IceCream") and press the Quick Watch button, or Shift+F9 :

Notice how this shows another way of viewing the variable details. Press the Cancel button to close this dialog.
Switch into the Immediate window and type the following:
```
   blnQuantity=False   
```
This sets the value of the variable blnQuantity to False . Did you notice how the "auto list member" worked in the Immediate window, showing you the possible values for blnQuantity ? That's because VBA knows this is a Boolean variable and that it can only have one of two values - True or False .
Press Step Over twice and look at the Immediate window again:

Notice how the quantities are no longer shown. That's because we've changed the value of the Boolean variable that tells the ShowIngredients procedure whether the quantity should be printed.
Keep pressing Step Into until you are back in the ShowIngredients procedure. You want to be on the While statement.

OK, let's take a little breather here, and recap what we've done so far. Don't press any other keys or stop the program yet - we'll be continuing in a little while.

What we've done so far is use the stepping facilities to step over lines of code and step into procedures. Remember that Step Over doesn't mean the line isn't executed - it just means that if the line is a procedure call we don't step through the lines of that procedure.

You've also seen that the Locals window has a lot of information in it. Object variables, such as recIces , appear with a plus sign next to them, a bit like the Windows Explorer. You can use this plus sign to drill-down into the properties, examining the object in more detail.

You've also seen that you can use the Immediate window to not only view variables, but to change them as well. This allows you to perhaps correct variables that have the wrong values.

The process of stepping through each line and examining variables is time intensive , and often not a productive way to trap errors. For the next stage in the debugging process, we'll use watches, which allow us to run the program as normal, but watch individual variables, performing some action when these variables change. This allows us to target our debugging much more narrowly, and frees us from the drudgery of stepping through the code one line at a time.

Try It Out-Debugging Code

Highlight recIngredients.EOF and press the Quick Watch button. This shows the value of the EOF property is False , which is correct since we've only just opened the recordset, and we know there are some records in there.
Cancel the Quick Watch and highlight Not recIngredients.EOF , and press the Quick Watch button again.
Now the value shows as True . Again, this is correct because we are watching a different expression, this time one that gives the opposite of the EOF flag. This shows that you can include more than a single variable in a watch expression.
Press the Add button to make this watch permanent.
Don't step any more yet, but highlight recIngredients("Name") on the following line and press the Quick Watch button. When the watch appears press Add to add this watch.
Now have a look at the Watches window. You can view this by pressing the Watch window on the toolbar, or selecting the same options from the View menu:

This is a little like the Locals window, but you can only see variables that you have added.
Press Step Over , going round the loop a few times, and notice how the value of the ingredient in the Watches window changes.

If you accidentally loop through too many times, you can position the yellow arrow to the While Not recIngredients.EOF line at the start of the loop. Before you can continue looping again, you will need to type recIngredients.MoveFirst in the Immediate window.
Highlight recIngredients("Name") watch in the Watches window, and from the Debug menu select Edit Watch :

This allows you to edit the watch details. Notice that there are three Watch Types:
- Watch Expression , which is the type you've seen so far. This just shows the value in the watch window.
- Break When Value Is True , watches the expression, but if the value of the expression is True , the program halts and enters debug mode.
- Break When Value Changes , watches the expression, but if the value changes, the program halts and enters debug mode.
Click the last of these ( Break When Value Changes ).
Change the Expression so that it says recIngredients("Name").Value , and then click the OK button.
Press F5 to continue running the program. Notice that the program halts on the Wend statement. This is the line after the MoveNext , which changes the record in the recordset, so the break is working.

But why did we have to add the .Value to the end of the expression? Well, Value is the default property, so in general use you can omit it, but when you need to break in a Watch expression, you have to add it in.
Select this watch in the Watches window, and from the Debug window select Edit Watch (or use Ctrl + W for a quicker method).
Change the watch expression to recIngredients("Quantity") > 1 and press OK .
Press F5 to continue and see where it stops next. If you hover the cursor over recIngredients("Quantity") on the Debug.Print line you'll see the quantity is 1.33. So not only can you break on values, but on expressions too.
Press the Stop button. Notice how the Locals window is cleared, because there are now no active statements. The Watch window also changes to show < Out of Context > for the watches, because there is no active code.
From the Debug menu, select Clear All Breakpoints . This will clear all breakpoints, but not the breaks in the Watches window.
In the Immediate window run the procedure again, by typing:
```
   ShowIces True   
```
This will run the program until the Watch causes a break - remember that it is looking for a change in the Boolean value of RecIngredients("Quantity") > 1 .
Press the Call Stack button on the Debug toolbar:
This shows that we are in the ShowIngredients procedure, but this was called from the ShowIces procedure. Select ShowIces and click the Show button:

Here you can see the arrow indicating the line in the ShowIces procedure that called ShowIngredients . The current executable line, however, hasn't changed, so pressing any of the Step buttons , or continuing, will not continue from the arrow in the diagram above, but from the current line.
Press F8 to step and you're now switched back to ShowIngredients .
Place the cursor on the recIngredients.Close line and from the Debug menu select Set Next Statement . Notice how the highlight moves to that line. All of the code in the loop has been skipped .
Press the Stop button to halt the code.

Hopefully you can see that being able to step through the code in a variety of methods is extremely flexible. You can examine variables in several ways, monitor them closely, and even change them. As you use VBA, you'll become more familiar with using the various windows in the VBE.