Learn Access Now!      Chapter 19      Previous Section in Chapter 20      Next Section in Chapter 20      Chapter 21

Chapter 20: Using Visual Basic for Applications

(This is section 4 of 6 in this chapter)

Testing Your Procedure

Once you have created a procedure, you need to test it out. VBA provides several different tools you can use to do this. These tools include:

Using the Immediate Window

Perhaps the most important tool you can use to test your procedures is the Immediate window. This window lets you see the results of your procedures right away. Using the window, you can uncover problems before they get buried among dozens of different procedures you may develop. For example, you can test your functions to see what values they are returning.

Remember that the Immediate window appears at the bottom of the VBA Editor workspace. (If it is not visible for some reason, you can display it by choosing Immediate Window from the View menu.) You can select the window by clicking your mouse in the window. Access displays a cursor and then waits for your command. You can switch to another VBA Editor window by simply clicking the mouse in the desired window. Later, you can hop back to the Immediate window by clicking your mouse within it. (This is the same process you use to select different windows anywhere in the Windows environment.)

To use the Immediate window to test out a procedure, simply type a question mark followed by the name of the procedure. For example, if you want to test out the function developed in the previous section, enter the following:

?ChangeSalesByZip("43772", 3)

As soon as you press ENTER, Access executes the procedure. Remember--this function will change the information in your database, so be careful with how you use it. When the function returns, it prints the number of records changed, as shown in Figure 20-6.

Figure 20-6 Using the Immediate window.

To display a variable's value, type in the name of the variable preceded by a question mark. For example, if you want to display the contents of the MyVar variable (assuming you have such a variable defined), type in the following:

?MyVar

You should note that before a procedure is executed, the value of all variables is undefined, meaning you cannot print them. In addition, after a procedure is executed, VBA wipes out the contents of all variables. This means that the only time you can meaningfully print the value assigned to a variable is while a procedure is executing. Typically, this is done after a breakpoint is reached, as discussed shortly.

Using Debug.Print

Another way you can use the Immediate window is with Debug.Print. VBA lets you use Debug.Print to display the contents of any variable, as your program runs. Using Debug.Print, you can watch closely a variable's value to see how your code changes the variable. By tracking a variable's value in this way, you may detect errors in your code.

To display a variable value in this way, you include Debug.Print in a line of code. For example, if you want to determine the value of the TempStore variable, you can use the following line of code:

Debug.Print "Value of TempStore is " & TempStore

If TempStore contains a value of 4, when VBA executes this line, Access will display the following in the Immediate window:

Value of TempStore is 4

Debug.Print has no other effect on your program; everything else remains the same. Debug.Print is particularly useful when you're debugging, since you don't have to bother with stopping and restarting your program.

Setting Breakpoints

Another testing tool which VBA provides for you is a breakpoint--a place you specify within your program where you want the program's execution to stop. Once your program stops, you can use the Immediate window to display information about variables or to test out other procedures, if you desire. VBA lets you set breakpoints on any line of code in your program or within any procedure of your program.

To set a breakpoint, position the cursor on the program line where you want VBA to stop. Then, select the Toggle Breakpoint option from the Debug menu or click your mouse on the toolbar Breakpoint tool. VBA highlights the program line to indicate it has set the breakpoint. (Figure 20-7 shows an example of a breakpoint in a program.)

Figure 20-7 A breakpoint within a program.

When you later run the program and VBA encounters the line at which you have set the breakpoint, VBA will do three things:

Normally, you set breakpoints so you can inspect the status of the program while it is executing. This is the time when you can print out the contents of variables, as discussed earlier in this chapter. You can also use other VBA menu commands (those under the the Debug menu) to step through your procedure one instruction at a time. If you are done inspecting the program, you can also choose the Run command from the Run menu. This causes the procedure to continue executing as if no breakpoint had been encountered.

To later remove a breakpoint, stop your program and position the cursor on the line that contains the breakpoint. Then, perform any of the actions you used to first set the breakpoint: select the Toggle Breakpoint option from the Debug menu or click your mouse on the toolbar Breakpoint tool. This removes the breakpoint, which you can see because VBA removes the highlight from the program line.

Note: It is not unusual when you are testing your program to have several different breakpoints set. If you want to remove all the breakpoints in your program at the same time, you can do so by choosing the Clear All Breakpoints option from the Debug menu.

Learn Access Now!      Chapter 19      Previous Section in Chapter 20      Next Section in Chapter 20      Chapter 21