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 find typos in the names of variables.

As discussed in detail in Chapter 7, “VBA: An Introduction,” the Option Explicit statement is a command that can be inserted manually into the General Declarations section of any Code, Form, or Report module. If you prefer, it can be inserted automatically by selecting Require Variable Declaration from the Editor tab after choosing Tools|Options from within the Visual Basic Editor. After it is inserted, Option Explicit is placed in the General Declarations section of all new modules. Existing modules are not affected.

Strong-typing your variables is discussed in Chapter 7. Strong-typing a variable means indicating what type of data is stored in a variable at the time 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 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 followed judiciously. Naming standards are covered in Chapter 1, “Access as a Development Tool,” and are outlined in detail in Appendix B, “Naming Conventions.”

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, see Chapter 7.

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; 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 Immediate window enables you to test the values of variables and properties as your code executes. This can be quite enlightening as to what is actually happening within your code.

To practice with the Immediate window, you do not even need to be executing code. To invoke the Immediate window while in a form, report, or module, press Ctrl+G. To see how this works, follow these steps:

Your screen should look like the one shown in Figure 15.2. You can continue to request the values of properties or variables within your VBA code.

Figure 15.2. Using the Immediate window to test the values of properties.

Not only can you display things in the Immediate window, you can use the Immediate window to modify the values of variables and controls as your code executes. 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 15.3. Setting the values of properties using the Immediate window.

The Immediate window is an extremely valuable testing and debugging tool. The examples here barely begin to illustrate its power and flexibility.

The Immediate window displays the last 200 lines of output. As additional lines of code are added to the Immediate window, older lines disappear. When you exit completely from Access and return to the Immediate window, it is cleared. If you want to clear the Immediate window at any other time, follow these three steps:

In addition to being able to test and set the values of properties and variables using the Immediate window, you can test any VBA function. To do so, type the function and its arguments in the Immediate window, preceded by a question mark. This code returns the month of the current date, for example:

?datepart("m",date)

This tells you the date one month after today’s date:

?dateadd("m",1,date)

This tells you how many days exist between the current date and the end of the millennium:

?datediff("d",date(),#12/31/2999#)

In addition to enabling you to test any VBA function, the Immediate window lets you test any user-defined subroutine, function, or method. This is a great way to debug your user-defined procedures. To see how this works, follow these 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.

RoutineName Parameter1, Parameter2, ....

The capability to print to the Immediate window is useful because you can test what is happening as your code executes without having to suspend code execution. It also is 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 15.4. Using Debug.Print statements to print values to the Immediate window.

When you reach 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, click Step Into on the Debug toolbar, press F8, or choose Debug|Step Into.

The following example illustrates the process of stepping through your code, printing the values of variables to the Immediate window, and modifying the values of variables using the Immediate window.

TRY IT

You can continue the debug process from the breakpoint you set in the previous example. Step two times (press F8). You should find yourself within Func1, about to execute the line of code intTemp = 10. (See Figure 15.6.) Notice that VBA did not stop on the line Dim intTemp As Integer. The debugger does not stop on variable declarations.

Figure 15.6. The Immediate window with code halted within Func1.

The Debug statements are about to print to the Immediate window. Take a look by opening the Immediate window. None of your code has printed anything to the Immediate window yet. Press F8 (step) three more times until you have executed the line Debug.Print intTemp. Your screen should look like Figure 15.7. Notice the results of the Debug.Print statements.

Figure 15.7. The Immediate window with entries generated by Debug.Print statements.

Now that you have seen how you can display variables and the results of expressions to the Immediate window, take a look at how you can use the Immediate window to modify values of variables and controls. Start by changing the value of intTemp. Click the Immediate window and type intTemp = 50. When you press Enter, you actually modify the value of intTemp. Type ?intTemp, and you’ll see that Access returns the value of 50. You also can see the value of intTemp in the Locals window. Notice in Figure 15.8 that the intTemp variable appears along with its value and type.

Figure 15.8. The Immediate and Locals windows after modifying the value of intTemp.

Suppose that you have reached a breakpoint, but you realize that your problem is farther down in the code execution. In fact, the problem is actually in a different function. You might not want to continue to move one step at a time down to the offending function. Use the Procedure drop-down menu to locate the questionable function, and then set a breakpoint on the line where you want to continue stepping. You now are ready to continue code execution until Access reaches this line. To do this, click Continue on the Debug toolbar, press F5, or choose Run|Continue. Your code continues to execute, stopping at the next breakpoint. To see how this works, continue the Debug process with the next example.

TRY IT

Suppose that you realize your problem might be in Func3. You do not want to continue to move one step at a time down to Func3. No problem. Use the Procedure drop-down menu to view Func3, as shown in Figure 15.9. Set a breakpoint on the line that reads Debug.Print "We Are Now In Func3()". You are ready to continue code execution until Access reaches this line. Click Continue on the Debug toolbar, press F5, or choose Run|Continue. Your code continues to execute, stopping on the breakpoint you just set. Press F5 again. The code finishes executing. Return to the Form View window.

Figure 15.9. Using the Procedure drop-down menu 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 Debug toolbar, press Shift+F8, or choose Debug|Step Over. The code within the subroutine or function you are stepping over executes, but you do not step through it. To experiment with the Step Over feature, follow the next example.

TRY IT

Click the open form and click the Start Debug Process button one more time. Because your breakpoints are still set, you are placed on the line of code that reads Call Func1. Select Clear All Breakpoints from the Debug menu, or use the Ctrl+Shift+F9 keystroke combination to remove all breakpoints. Step (press F8) five times until you are about to execute the line Call Func2. Suppose 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 now are ready to continue stepping in Func1. In this case, you are placed on the End Sub line immediately following the call to Func2.

TRY IT

The Step Out feature is used to step out of the procedure you are in and to return to the procedure that called the line of code you are on. You use this feature when you have accidentally stepped into a procedure that you realize is fully tested. You want to execute all the code called by the procedure you are in and then step out to the calling procedure so that you can continue with the debugging process. To test how this works, follow this example.

The quickest and easiest way to view the value contained within a variable is to use Auto Data Tips, which is an option for working with modules. This feature is available only when your code is in Break mode. While in Break mode, simply move your mouse over the variable or expression whose value you want to check. A tip appears with the current value. To set the Auto Data Tips option from the Visual Basic Editor (VBE), choose Tools|Options, click the Editor tab, and check the option for Auto Data Tips, which is under the Code Settings options.

A quick watch is the most basic type of watch. To add a quick watch, highlight the name of the variable or expression you want to watch and click the Quick Watch button on the toolbar. The Quick Watch dialog box, shown in Figure 15.12, appears. You can click Add to add the expression as a permanent watch or choose Cancel to view the current value without adding it as a watch. If you click Add, the Watches window appears, like the one in Figure 15.13. This window is discussed in more detail in the next section.

Figure 15.12. The Quick Watch dialog box enables you to quickly view the value of a variable or add an expression as a permanent watch.

Figure 15.13. The Watches window with a Watch expression.

As you saw, you can add a Watch expression using a quick watch. Adding a watch 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 choose Debug|Add Watch. The Add Watch dialog box appears, as shown in Figure 15.14.

Figure 15.14. The Add Watch dialog box enables you to easily designate all the specifics of a Watch expression.

In the Expression text box, 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 Immediate 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 following sections.

TRY IT

The next 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, and changes value, during code execution.

After you add a watch, you might want to edit the nature of the watch or remove it entirely. You use the Edit Watch dialog box to edit or delete a Watch expression. Follow these steps:

A powerful aspect of a Watch expression is that you can break whenever an expression becomes True. You can break whenever a Public variable reaches a specific value, for example. You might want to do this when a Public or Private variable somehow is being changed, and you want to find out where. Consider the following code, located in the basFuncs module of CHAP15EX.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 somehow is reaching a number greater than 100, and you are not sure how. To solve the problem, add the watch shown in Figure 15.16. Notice that the expression you are testing for is gintCounter > 100. You have set the breakpoint to break the code whenever the expression becomes True. To test the code, type ChangeGlobal1 in the Immediate window and press Enter. The code should break in the ChangeGlobal3 routine, indicating that this routine is the culprit.

Figure 15.16. Defining a watch that will cause the code execution to break 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 Value Is True, the Break When Value Changes option is great for tracking down problems with Public and Private variables. Notice the watch being set in Figure 15.17. It is in the context of all procedures within all modules. It is set to break whenever the value of gintCounter changes. 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 the value of gintCounter is modified, the code execution breaks.

Figure 15.17. Creating a watch that will cause code execution to break whenever the value of an expression changes.