A good programmer is not necessarily one who can get things right the first time. To be fully effective as a VBA programmer, you need to master the art of debugging—the process of troubleshooting your application. Debugging involves locating and identifying problem areas within your code and is a mandatory step in the application-development process. Fortunately, the Access 95 environment provides excellent tools to help you with the debugging process. Using the Access 95 debugging tools, you can step through your code, setting Watchpoints and Breakpoints as needed.
Utilizing the VBA debugging tools is significantly more efficient than taking random stabs at fixes to your application. A strong command of the Access 95 debugging tools can save you hours of trial and error. In fact, it can make the difference between a successfully completed application-development process and one that continues indefinitely with problems left unsolved.
The best way to deal with bugs is to avoid them in the first place. Proper coding techniques can really aid you in this process. The use of Option Explicit, strong-typing, naming standards, and tight scoping can help you to eliminate bugs in your code.
Option Explicit requires that all your variables be declared before they are used. Including Option Explicit in each Form, Code, and Report module helps the VBA compiler to find typos in the names of variables.
As discussed in detail in Chapter 8, "VBA 101: The Basics of VBA," the Option Explicit statement is a command that can be placed in the General Declarations section of any Code, Form, or Report module. The Option Explicit command can be manually inserted into each program, or it can be inserted automatically by selecting Require Variable Declaration from the Modules tab within Tools|Options.
Strong-typing your variables is discussed in Chapter 8. To strong-type a variable means to indicate what type of data is stored in a variable at the time that it is declared. For example, Dim intCounter As Integer initializes a variable that contains integers. If elsewhere in your code you assign a character string to intCounter, the compiler will catch the error.
Naming standards can also go a long way toward helping you to eliminate errors. The careful naming of variables makes your code easier to read and makes the intended use of the variable more obvious. Problem code tends to stand out when naming conventions have been judiciously followed. Naming standards are covered in Chapter 1, "Introduction to Access Development," and are outlined in detail in Appendix B.
Finally, giving your variables the narrowest scope possible reduces the chances of one piece of code accidentally overwriting a variable within another piece of code. You should use Local variables whenever possible. Use Module-Level and Global variables only when it is necessary to see the value of a variable from multiple subroutines or multiple modules. For more information about the issues surrounding variable scoping, consult Chapter 8.
Unfortunately, no matter what you do to prevent problems and errors, they still creep into your code. Probably the most insidious type of error is a logic error. A logic error is sneaky because it escapes the compiler. A logic error means that your code compiles but simply does not execute as planned. This type of error might become apparent when you receive a runtime error or when you don't get the results you expected. This is where the debugger comes to the rescue.
The Debug window serves several purposes. It provides you with a great way to test VBA and user-defined functions, it enables you to both inquire about and change the value of variables while your code is running, and it allows you to view the results of Debug.Print statements. To open the Debug window while in a Code, Form, or Report module, do one of two things:
Figure 16.1. The Debug window allows you to test functions and to inquire about and change the value of variables.
The Debug window allows you to test the values of variables and properties as your code is executing. This can be quite enlightening as to what is actual happening within your code.
To practice with the Debug window, you do not even need to be executing code. While in Design view of a form, report, or module, all you need to do is place yourself in the Code window. If the Debug window is not visible, click Debug Window button on the toolbar. To see how this works, it takes seven steps:
Your screen should look like Figure 16.2. You could continue requesting the values of properties or variables within your VBA code.
Figure 16.2. Using the Debug window to test the values of properties.
Not only can you display things to the Debug window, you can use the Debug window to modify the values of variables and controls as your code is executing. This feature becomes even more valuable when you realize that you can re-execute code within a procedure after changing the value of a variable. Here's how this process works:
Figure 16.3. Setting the values of properties using the Debug window.
The Debug window is an extremely valuable testing and debugging tool. The previous examples barely begin to illustrate its power and flexibility.
The Debug window displays the last 200 lines of output. As additional lines of code are added to the Debug window, older lines disappear. When you exit completely from Access and return to the Debug window, it will be cleared. If you want to clear the Debug window at any other time, follow three steps:
In addition to being able to test and set the values of properties and variables using the Debug window, you can test any VBA function. To test a VBA function, type the function and its arguments in the Debug window, preceded by a question mark. Here are some examples:
This example returns the month of the current date.
This example tells you the date one month after today's date.
This example tells you how many days exist between the current date and the end of the millennium.
In addition to allowing you to test any VBA function, the Debug window allows you to test any user-defined subroutine, function, or method. This is a great way to debug your user-defined procedures. To see how this works, take the following steps:
Notice the difference between how you call a function and how you call a subroutine. Because the function returns a value, you must call it using a question mark. On the other hand, when calling a subroutine, you use the Call keyword.
The ability to print to the Debug window is useful because you can test what is happening as your code is executing, without having to suspend code execution. It is also valuable to be able to print something to a window when you are testing, without interfering with the user-interface aspect of your code. You can test a form without being interrupted and then go back and view the values of variables and so on. Here's how the process works:
Figure 16.4. Using Debug.Print statements to print values to the Debug window.
You can invoke the Access debugger in several ways:
A Breakpoint is an unconditional point at which you want to suspend code execution. It is temporary in that it is in effect only while the database is open. In other words, Breakpoints are not saved with the database.
A Watchpoint is a condition under which you want to suspend code execution. For example, you might want to suspend code execution when a Counter variable reaches a specific value. A Watchpoint is also temporary; it is removed once you close the database.
A Stop statement is permanent. In fact, if you forget to remove Stop statements from your code, your application stops execution while the user is running it.
As mentioned, a Breakpoint is a point at which execution of code will be unconditionally halted. Multiple Breakpoints can be set in your code. You can add and remove Breakpoints as your code is executing.
A Breakpoint allows you to halt your code execution at a suspicious area of code. This enables you to examine everything that is going on at that point in your code execution. By strategically placing Breakpoints in your code, you can quickly execute sections of code that are already debugged, stopping only at problem areas.
Three steps are involved in setting a Breakpoint:
Now that your code is suspended, you can step through it one line at a time, change the value of variables, and view your call stack, among other things.
Keep in mind that a Breakpoint is actually a toggle. If you want to remove a Breakpoint, press F9 or select Breakpoint from the toolbar. Breakpoints will be removed when the database is closed, when another database is opened, or when you exit Access.
It is easiest to get to know the debugger by actually using it. The following example gives you hands-on experience setting and stopping code execution at a Breakpoint. The example is developed further later in the chapter.
[ic:tryit]Start by creating a form called frmDebug that contains a command button called cmdDebug. Give the button the caption "Test Code." Place the following code in the Click event of the command button:
Sub cmdDebug_Click () Call Func1 End Sub
Create a module called basFuncs. Enter three functions into the module:
Sub Func1 () Dim iTemp As Integer iTemp = 10 Debug.Print "We Are Now In Func1()" Debug.Print iTemp Call Func2 End Sub Sub Func2 () Dim sName As String sName = "Bill Gates" Debug.Print "We Are Now In Func2()" Debug.Print sName Call Func3 End Sub Sub Func3 () Debug.Print "We Are Now In Func3()" MsgBox "Hi There From The Func3() Sub Procedure" End Sub
Now you should debug. Start by placing a Breakpoint within the Click event of cmdDebug on the line that reads Call Func1. Here are the steps:
Figure 16.5. Code execution halted at a Breakpoint.
Access 95 gives you two main options for stepping through your code. Each one is slightly different. The Step Into option allows you to step through each line of code within a subroutine or function, whereas the Step Over option executes a procedure without stepping though each line of code within it. Knowing the right option to use to solve a particular problem is an acquired skill of experienced developers.
When you have reached a Breakpoint, you can continue executing your code one line at a time or continue execution until another Breakpoint is reached. To step through your code one line at a time, select Step Into from the toolbar, press F8, or select Run|Step Into.
The following example illustrates the process of stepping through your code, printing the values of variables to the Debug window, and modifying the values of variables using the Debug window.
[ic:tryit]You can continue the debug process from the Breakpoint that you set in the previous example. Step two times (F8). You should find yourself within Func1, about to execute the line of code iTemp = 10. (See Figure 16.6.) Notice that VBA did not stop on the line Dim iTemp As Integer. The debugger does not stop on variable declarations.
The Debug statements are about to print to the Debug window. Let's take a look. Open the Debug window. None of your code has printed anything to the Debug window yet. Press F8 (step) three more times until you have executed the line Debug.Print iTemp. Your screen should look like Figure 16.7. Notice the results of the Debug.Print statements.
Now that you have seen how you can display things to the Debug window, let's take a look at how you can use the Debug window to modify values of variables and controls. Start by changing the value of iTemp. Click the Debug window and type iTemp = 50. When you press Enter, you actually modify the value of iTemp. Type ?iTemp and you'll see that Access echoes back the value of 50.
Figure 16.6. The Debug window halted within Func1.
Figure 16.7. The Debug window with entries generated by Debug.Print statements.
Assume that you have reached a Breakpoint but realize that your problem is further down in the code execution. In fact, the problem is actually in a different function. You might not want to continue to move a step at a time down to the offending function. Use the Procedure drop-down to locate the questionable function, then set a Breakpoint on the line where you want to continue stepping. You are now ready to continue code execution until Access reaches this line. To do this, click Continue on the toolbar, press F5, or select Run|Continue. Your code continues to execute, stopping at the next Breakpoint. To see firsthand how this works, continue the Debug process with the next example.
[ic:tryit]Assume that you realize your problem might be in Func3. You do not want to continue to move a step at a time down to Func3. No problem. Use the Procedure drop-down to view Func3 (see Figure 16.9). Set a Breakpoint on the line that reads Debug.Print "We Are Now In Func3()". You are now ready to continue code execution until Access reaches this line. To continue execution, click Continue on the toolbar, press F5, or select Run|Continue. Your code continues to execute, stopping on the Breakpoint that you just set. Press F5 again. The code executes to completion. Return to the Form View window.
Figure 16.8. Using the Procedure drop-down to view another function.
Sometimes you already have a subroutine fully tested and debugged. You want to continue stepping through the routine that you are in, but you don't want to watch the execution of subroutines. In this case, you use Step Over. To step over a subroutine or function, click Step Over on the toolbar, press Shift+F8, or select Run|Step Over. The code within the subroutine or function that you are stepping over will execute, but you will not step through it. To experiment with the Step Over feature, follow the next example.
[ic:tryit]Click back on the open form and click one more time on the Test Code button. Because your Breakpoints are still set, you'll be placed on the line of code that reads Call Func1. Press F9 to remove this Breakpoint. Move to the basFuncs module window, which should still be open, and use the Procedure drop-down to move to Func3. If the basFuncs module is no longer open, you can use Shift+F2 to move to Func1, which is also in basFuncs, and then use the Procedure drop-down to move to Func3. Remove the Breakpoint from that routine as well. Step (F8) five times until you are about to execute the line Call Func2. Let's assume that you have tested Func2 and Func3 and know that they are not the cause of the problems in your code. With Func2 highlighted as the next line to be executed, click Step Over on the toolbar. Notice that Func2 and Func3 are both executed but that you are now ready to continue stepping in Func1. In this case, you are placed on the End Sub line immediately following the call to Func2.
After you have stepped through your code, watched the logical flow, and modified some variables, you might want to re-execute the code beginning at a prior statement. To do this, you can click anywhere in the line of code where you want to commence execution. Select Run|Set Next Statement. Notice that the rectangle indicating the next line of code to be executed is now over that statement. You can then step through the code using F8, or you can continue normal code execution using F5. Access allows you to set the next line to be executed within a procedure only. This feature can be used to re-execute lines of code or to skip over a problem line of code.
The following example walks you through the process of changing the value of a variable and then re-executing code after the value has been changed.
[ic:tryit]The last example left you at the last line of code (the End Sub statement) within Func1. You want to change the value of iTemp and re-execute everything. Go to the Debug window and type iTemp = 100. You need to set the next statement to print on the line that reads Debug.Print "We Are Now in Func1()". To do this, click anywhere in the line of code that says Debug.Print "We Are Now In Func1()". Open the Run menu and select Set Next Statement. Notice that the rectangle indicating the next line of code to be executed is now over that statement. Press F8 (step) two times. The code now executes with iTemp set to 100. Observe the Debug window again. Notice how the results have changed.
You have learned how to set Breakpoints, step through and over code, use the Debug window, set the next line to be executed, and continue to run until the next Breakpoint is reached. Once you have reached a Breakpoint, it is often important to see which functions were called to bring you to this point. This is where the Calls feature can help.
To bring up the Calls window, select the Calls button from the toolbar or select Tools|Calls. The window in Figure 16.9 appears. If you want to see the line of code that called a particular function or subroutine, double-click that particular function or click the function and then click Show. Although your execution point is not moved to the calling function or subroutine, you are able to view the code within the procedure. If you want to continue your code execution, press F8. You'll be moved back to the procedure through which you were stepping, and the next line of code will execute. If you Press F5, your code executes until another Breakpoint or Watchpoint is reached. If you want to return to where you were without executing additional lines of code, select Run|Show Next Statement. To test this process, perform the next example.
Figure 16.9. Using the Calls window to view the call stack.
[ic:tryit]Click the End button to stop your code execution if you are still in Break mode. Move to the procedure called Func3 in basFuncs. Set a Breakpoint on the line Debug.Print "We Are Now in Func3()". Run the frmDebug form and click the command button. You should be placed in Func3 on the line where the Breakpoint is set. Bring up the Calls window by selecting the Calls button from the toolbar. If you want to see the line of code that called Func2 from Func1, double-click Func1. Although your execution point is not moved to Func1, you are able to view the code within the procedure. To return to the next line of code to execute, select Run|Show Next Statement. Press F5, and the remainder of your code executes.
Sometimes it is not enough to use the Debug window to test the value of an expression or variable. You might want to keep a constant eye on the expression's value. A new feature in Access 95 is the ability to set Watchpoints. You can set a Watchpoint before running a procedure or while code execution is suspended. After a Watch expression is added, it appears in the Debug window. As you'll see, you can create several types of Watchpoints.
An Instant Watch is the most basic type of Watchpoint. To add an Instant Watch, highlight the name of the variable or expression that you want to watch and click the Instant Watch button on the toolbar. The Instant Watch dialog, shown in Figure 16.10, appears. You can click Add to add the expression as a permanent watch or select Cancel to view the current value without adding it as a Watchpoint. If you click Add, the Debug window will look like Figure 16.11. This window is discussed in more detail in the next section.
Figure 16.10. The Instant Watch dialog allows you to quickly view the value of a variable or to add an expression as a permanent watch point.
Figure 16.11. The Debug window with a Watch expression.
As you saw, you can add a Watch expression using the Instant Watch. Adding a Watchpoint this way does not give you full control over the nature of the watch, however. If you need more control over the watch, you must select Tools|Add Watch. The Add Watch dialog is shown in Figure 16.12.
Figure 16.12. The Add Watch dialog allows you to easily designate all of the specifics of a watch expression.
The Expression text box is used to enter a variable, property, function call, or any other valid expression. It is important to select the procedure and module in which you want the expression to be watched. Next, indicate whether you want to simply watch the value of the expression in the Debug window, break when the expression becomes True, or break whenever the value of the expression changes. The two latter options are covered in detail in the sections that follow.
The following example walks you through the process of adding a Watch, and viewing the Watch variable as you step through your code. It illustrates how a variable goes in and out of scope as well as changes value during code execution.
[ic:tryit]To begin, stop code execution if your code is running and remove any Breakpoints you have set. Click and drag over the sName variable in Func2 and select Tools|Add Watch. Click OK to accept the Func2 procedure as the context for the variable and basFuncs as the module for the variable. Set a Breakpoint on the line sName = "Bill Gates". Run the frmDebug form and click the command button. View the Debug window and notice that sName has the value of a zero-length string. Step one time and notice that sName is equal to "Bill Gates." Step three more times. Notice that although you are in the Func3 routine, sName still has the value Bill Gates. This is because the variable is still in memory in the context of basFuncs.Func2. Step four more times until you are back on the End Sub statement of Func2. The sName variable is still in context. Step one more time. The sName variable is finally out of context because the execution of Func2 has completed.
After you have added a watch, you might want to edit the nature of the watch or remove it entirely. The Edit Watch dialog is used to edit or delete a Watch expression:
Figure 16.13. The Edit Watch dialog allows you to modify the specifics of a watch once you have added it.
A powerful aspect of a Watch expression is that you can break whenever an expression becomes True. For example, you can break whenever a Public variable reaches a specific value. You might want to do this when a Public or Private variable is somehow being changed and you want to find out where. Consider the following code, found in the basFuncs module of CHAP16EX.MDB:
Sub ChangeGlobal1() gintCounter = 50 Call ChangeGlobal2 End Sub Sub ChangeGlobal2() gintCounter = gintCounter + 10 Call ChangeGlobal3 End Sub Sub ChangeGlobal3() Dim intCounter As Integer For intCounter = 1 To 10 gintCounter = gintCounter + intCounter Next intCounter End Sub
You might find that gintCounter is somehow reaching a number greater than 100 and you are not sure how. To solve the problem, add the Watchpoint pictured in Figure 16.14. Notice that the expression we are testing for is gintCounter > 100. We have set the Breakpoint to break the code whenever the expression becomes True. To test the code, type ChangeGlobal1 in the Debug window. The code should break in the ChangeGlobal3 routine, indicating that this routine is the culprit.
Figure 16.14. Defining a watch that will cause the code execution to breaking whenever the expression is True.
Instead of breaking when an expression becomes True, you might want to break whenever the value of the expression changes. This is a great way to identify the place where the value of a variable is mysteriously altered. Like Break When Expression Is True, this option is great for tracking down problems with Public and Private variables. Notice the Watchpoint being set in Figure 16.15. It is in the context of all procedures within all modules. It is set to break whenever the value of gintCounter is changed. If you execute the ChangeGlobal1 routine, you'll find that the code halts execution within ChangeGlobal1 immediately after the value of gintCounter is set to 50. If you press F5 to continue execution, the code halts within ChangeGlobal2 immediately after gintCounter is incremented by 10. In other words, every time that the value of gintCounter is modified, the code execution breaks.
Figure 16.15. Creating a watch that will cause code execution to break whenever the value of an expression has changed.
As you are testing, you often discover runtime errors that are quite easy to fix. When a runtime error occurs, a dialog box similar to the one pictured in Figure 16.16 appears.
Figure 16.16. The Runtime Error dialog box.
If you select Debug, you'll be placed in the Code window, on the line that generated the error. After rectifying the problem, click the Continue button on the toolbar or select Run|Continue.
For example, Figure 16.17 shows a divide-by-zero error. After Debug was selected from the Runtime Error dialog box, the value of int2 was set to 20. Code execution can now continue without error.
Figure 16.17. Debug mode after divide-by-zero error.
After an error has occurred, VBA often displays a message giving you the option of resetting your code. If you opt to reset your code, all variables (including Publics and Statics) lose their values. You can also select Reset from the toolbar. You must decide whether it is better to proceed with your variables already set or to reset the variables and then proceed.
Although the Access debugger is excellent, the debugging process itself is wrought with an array of potential problems:
As you develop the Time and Billing application, use the techniques you have learned to help solve any problems you might encounter. For now, we'll use the debugger to step through and learn more about the debugging process with one of the routines found within the Time and Billing application.
Open the frmClients form found in CHAP16.MDB in Design view. Place a Breakpoint on the line of code If IsNull(Me![ClientID]) Then found in the Projects button Click event (see Figure 16.18). Run the form and click the Projects command button. Step through the code and watch it execute. After a couple of steps, you should get the error pictured in Figure 16.19. This error a great example of the debugger interacting negatively with the environment. The code is attempting to issue a command from the Form Menu bar while you are in the Debug window. Remove the Breakpoint and add a Breakpoint on the DoCmd.OpenForm line. Run the form again. You should be launched into the Form_Open event of frmProjects. Continue stepping while watching the code execution. Test the value of expressions, if you like.
Figure 16.18. Setting Breakpoint in frmClients.
Figure 16.19. Error while stepping through frmClients.
If programming were a perfect science, there would be no reason to use a debugger. Given the reality of the challenges of programming, a thorough understanding of the use of the debugger is imperative. Fortunately, Access 95 provides an excellent tool to assist in the debugging process.
This chapter began by showing you how you can reduce the chance of bugs within your application in the first place. It then taught you how to use the Debug window to test and change the values of variables and properties. You learned how to employ the use of Watchpoints and Breakpoints, as well as how to view the call stack. All of these techniques help to make the process of testing and debugging your application a pleasant experience.