Microsoft KB Archive/210143

= ACC2000: Tips for Debugging Visual Basic for Applications Code =

Article ID: 210143

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210143



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
This article shows you tips that can help you debug your Visual Basic for Applications code, particularly when your code compiles correctly, but does not perform as you expect.

Debugging is the process that you use to find and resolve errors, or bugs, in your Visual Basic for Applications code. There are three types of errors that you can encounter: compile-time errors, run-time errors, and logic errors. Logic errors occur when the application does not perform as intended and produces incorrect results. Microsoft Access provides debugging tools that enable you to step through your code one line at a time, to examine or monitor the values of expressions and variables, and to trace procedure calls. All of these aid in finding and eliminating logic errors.

The general procedure for debugging Visual Basic for Applications code is to break larger procedures into smaller subprocedures to verify that the individual steps in each subprocedure work correctly, and then to combine the subprocedures one by one until the code works correctly.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

There are several different methods that you can use to debug your Visual Basic for Applications code. The main ones are these:

Use the Immediate Window
You can use the Immediate window to run individual lines of Visual Basic for Applications code or to check the values of variables. In the Immediate window, you can:
 * Test and debug Function and Sub procedures.
 * Check the value of a field, a control, a property setting, a variable, or an expression.
 * Display the result of an expression when the code is running.

To display the Immediate window in the Visual Basic Editor, either click Immediate Window on the Debug menu, or press CTRL+G.

To use the Immediate window, follow these steps:  Start Microsoft Access, open any database and create a new module.  Create the following Function procedure: Public Function PyrNum(ByVal intNumber As Integer) 'Returns the pyramid value of an integer, which is   'the sum of the integer and all smaller integers greater than 0 Do While intNumber > 0 PyrNum = PyrNum + intNumber intNumber = intNumber - 1 Loop

End Function  On the View menu, click Immediate Window. Type ? PyrNum(3), and then press ENTER.

The return value, 6, is displayed.

NOTE: If you are testing a Function procedure, type ? (, ..., ) and press ENTER, where   is the name of your function and   are any arguments it requires.

If you are testing a Sub procedure, type , ...,  , where   is the name of your procedure and   are any arguments it requires.

Use a Breakpoint to Suspend Execution of Code
If the first part of your code runs correctly but other parts do not, you may want to suspend execution of Visual Basic for Applications code, so that the procedure is still running but pauses at selected statements. You can do this by setting breakpoints. To set a breakpoint, follow these steps:   In the PyrNum function, click anywhere in the statement PyrNum = PyrNum + intNumber and then click the Debug menu and click Toggle Breakpoint.

Notice the colored dot in the left margin indicating the breakpoint line. You can remove or clear the breakpoint by clicking the dot, and you can set breakpoints at other lines by clicking at corresponding spots in the margin. </li> In the Immediate window, type ? PyrNum(3), and then press ENTER.

Notice that the code execution pauses at the breakpoint.</li> Point to the variable intNumber and notice that its current value appears as a screen tip.</li> Press the function key F5 or click the Continue button on the Standard toolbar to continue execution and again pause at the breakpoint. Again point to intNumber and notice the change in value.</li></ol>

Use the Debug.Print Statement
You can display the value of an expression in the Immediate window by using the Print method of the Debug object, followed by the expression. This lets you see a list of values that your variables take on as the procedure executes.

Good places to position Debug.Print statements include the following:

<ul>  At the beginning of a function, you can check the arguments passed to the function.

For example, to check the values of two arguments passed to the DoSomeCalcs function, place the following sample Debug.Print statement at the beginning of the function as follows: Function DoSomeCalcs(Arg1 as Single, Arg2 as Single)

Debug.Print "Arguments Passed: " & Arg1 & " and " & Arg2 .   . If the values of the arguments are not correct, their values are captured before the function runs and errors occur.

You can also use the Debug.Print statement at the beginning of each function if you have a complex application and you are not sure which function might be causing a problem. This lets you check the Immediate window to see the order in which functions are called. You can also click Call Stack on the View menu to list the current function call stack and to show the order in which the functions are called, as discussed below. </li>  In decision structures, you can check that you are using the correct logic for the function, for example: Function DueDate (anyDate) Dim Result as Variant Debug.Print "Function DueDate " & anyDate If Not IsNull(anyDate) Then Result = DateSerial(Year(anyDate), Month(anyDate) + 1, 1) Debug.Print "Result: " & Result Debug.Print "Weekday(Result): " & Weekday(Result) Select Case Weekday(Result) Case 1 'Sunday Debug.Print "Case 1" DueDate = Result + 1 Case 7: 'Saturday Debug.Print "Case 7" DueDate = Result + 2 Case 6: 'Friday Debug.Print "Case 6" DueDate = Result - 1 Case Else Debug.Print "Case Else" DueDate = Result End Select Else DueDate = "Date Missing" End If End Function </li>  In loops, you can check the values of variables.

If you use a breakpoint to check the value of a variable in a loop, it may be awkward see how values change as the loop executes. However, if you include the statement Debug.Print MyName in your loop structure, the value of variable MyName is output to the Immediate window each time the function cycles through the loop.

Debug.Print is a convenient way to monitor more than one value as well as to verify that the loop is executing the number of times you expect. If the loop executes three times, you should see three output lines in the Immediate window.

For example, you can modify the PyrNum function like this Public Function PyrNum(ByVal intNumber As Integer) Dim intPyr As Integer ' Returns the pyramid value of an integer, which is   ' the sum of the integer and all smaller integers greater than 0 Do While intNumber > 0 PyrNum = PyrNum + intNumber Debug.Print intNumber, PyrNum intNumber = intNumber - 1 Loop

End Function to log the changes in PyrNum and intNumber as the loop executes.

Run this function in the Immediate window by typing ? PyrNum(3) and pressing ENTER. You will see the following output: <pre class="fixed_text">  3             3 2            5    1             6    6  The final 6 is the return value of the function, and not output from the Debug.Print statement. </li>  In SQL  Where clauses, you can check the values of criteria.

For example, the following function creates a dynaset from a SQL statement. A problem in a SQL statement (such as in this function), can be difficult to locate. However, this function uses Debug.Print to display the SQL statement as Microsoft Access sees it and uses it: Function TestMe Dim db As DAO.Database, rs As DAO.Recordset Dim empnum As Long Dim strSQL As String Set db = CurrentDb empnum = 5 strSQL = "select * from orders where [employeeid]=empnum" Debug.Print strSQL Set rs = db.OpenRecordset(strSQL) End Function Run this function in the Immediate window by typing ? TestMe and then pressing ENTER. You will see the following appear in the Watches window:

select * from orders where [employeeid]=empnum;

This Where condition shows [employeeid] = empnum, not [employeeid] = 5 as you assigned it. The cause is that the variable empnum needs to be exposed outside the SQL string. To fix this particular problem, change the Where condition to concatenate the empnum variable, as follows: Function TestMe Dim db As DAO.Database, rs As DAO.Recordset Dim empnum As Long Dim strSQL As String Set db = CurrentDb empnum = 5 strSQL = "select * from orders where [employeeid]=" & empnum & ";" Debug.Print strSQL Set rs = db.OpenRecordset(strSQL) End Function When you run the function, the following appears in the Watch window: <pre class="fixed_text">   select * from orders where [employeeid]=5; This is the correct SQL Where clause. </li></ul>

Set a Watch Expression in Visual Basic for Applications Code
A Watch expression is an one that you monitor in the Watch window You can also use Quick Watch to see the value of an expression that has not previously been specified as a Watch expression.

To add a Watch expression to the Watch window, follow these steps:  If it is not already open, open the module containing the PyrNum function.</li> If the Immediate window is not open, open it by pressing CTRL+G.</li> On the Debug menu, click Add Watch.</li> In the Expression box, type PyrNum .</li> In the Module box, select, where   is the name of the module you have just created. In the Procedure box, select PyrNum.</li> In the Watch Type box, click Watch Expression.</li>  Press CTRL+SHIFT+F9 to clear all breakpoints, then set a breakpoint, as described above, at the line Do While intNumber > 0 in the PyrNum function. </li> In the Immediate window, type ListPyrs 3 and press ENTER.</li> <li>After the execution pauses at the breakpoint, press function key F8 repeatedly and notice the change in value of PyrNum in the Watch Window.</li></ol>

You can change the value of a variable on which you set a watch. You can also restrict the scope used to watch variables defined for a specific procedure or a specific module, or globally in the code. You can also add a Watch expression by selecting an expression in your code and clicking Quick Watch on the Debug menu.

View the Call Stack to Trace Nested Procedures
The Call Stack dialog box displays a list of all active procedure calls. These are the procedures in an application that have started but not completed. You can use the Call Stack dialog box to trace the operation of an application as it runs a series of procedures. You can view the Call Stack from the Immediate window by clicking Call Stack on the View menu. The earliest active procedure call is placed at the bottom of the list and subsequent procedure calls are added to the top.

You can use the Show button in the Call Stack dialog box to display the statement in one procedure that has called the procedure listed above it. If you choose the current procedure in the Call Stack dialog box and then click Show, the Visual Basic Editor displays the statement at which execution was suspended.

To see the calls in the PyrNum example above, follow these steps: <ol> <li> Add the following new procedure to the module containing PyrNum: Public Sub ListPyrs(intMax As Integer) Dim i As Integer For i = 1 To intMax Debug.Print i, PyrNum(i) Next i End Sub </li> <li> Press CTRL+SHIFT+F9 to clear all breakpoints, then set a breakpoint in the Sub ListPyrs procedure, at the following line: For i = 1 To intMax </li> <li>In the Immediate window, type ListPyrs 3 </li> <li>On the View menu, click Call Stack. Notice that ListPyrs is the only active procedure. Click Close.</li> <li>Press the function key F8 twice, to step into the function PyrNum. Again, on the View menu, click Call Stack.

Notice that there are now two calls listed, the most recent being PyrNum, at the top of the stack.</li> <li>Select the line listing ListPyrs and then click Show.

Notice that the Visual Basic Editor displays the ListPyrs procedure, and indicates the line that calls the PyrNum function.</li></ol>

Use the Locals Window
The Locals window is similar to the Watch window and normally displays all variables and objects in the current procedure. It has three columns: Expression, Value, and Type.

The Expression column begins with the current module (for a standard module), or the current instance of a class (for a class module). The Expression column is organized as a hierarchical tree of information starting from the current module to display all of the module-level variables in the current module.

The Value column shows the values of the current module objects. You can change the value of a module-level variable in the Immediate window in order to test the behavior of your module.

The Type column shows the data type of the current module-level object.

Inspecting a selected variable's value in the Locals window can be very helpful in debugging your module, as can changing a variable's value in the Locals window Value column to observe what effect it has on other parts of your module.

<div class="references_section">