Knowledge Base

ACC: Tips for Debugging Visual Basic for Applications Code

Article ID: 142999

Article Last Modified on 1/19/2007

APPLIES TO

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q142999

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

SUMMARY

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

Debugging is a 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 may encounter: compile-time errors, run-time errors, and logic errors. Logic errors occur when the application doesn't 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.

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

MORE INFORMATION

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

Using the Debug Window

You can use the Immediate pane in the Debug Window to run individual lines of Visual Basic for Applications code or to check the values of variables. In the Debug 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; and display the result of an expression when the code is running. For an example of how to use the Debug window, follow thesesteps:

Open the module that contains your function in Design view.
On the View menu, click Debug Window.
Run your function in the Immediate pane of the Debug Window by typing the following line and pressing ENTER:

?<FunctionName>()

where <FunctionName> is the name of your function. Be sure to place all arguments expected by your function inside the parentheses.

Use a Breakpoint to Suspend Execution of Code

When you suspend execution of Visual Basic for Applications code, the code is still running, but pauses between running statements. To make Visual Basic for Applications pause execution of your code, you can set a breakpoint. For example, if the first part of your code runs correctly, but other parts do not, follow these sample steps to find the part that does not run correctly:

To set a breakpoint right after the part of your code that you know works correctly, move the insertion point to the first line of code where you want to set a breakpoint.
Click Toggle Breakpoint on the Debug menu (or the Run menu in version 7.0.). The line appears in bold type, indicating that there is a breakpoint set at that line.
Run the function in the Debug window. When code execution reaches the breakpoint, processing is suspended and the breakpoint line is selected. You can then check the value of variables in the code.

For example, to check the value of a variable called MyName, type the following in the Debug window, and then press ENTER:

?MyName

The value of MyName appears in the Debug window. If the value is correct, check other variables. If the value is incorrect, check the previous lines of code to see where this variable is assigned an incorrect value.

Using the Debug.Print Statement

You can view the results of an expression in the Debug window by entering the Print method of the Debug object, followed by the expression. You can display the Debug window anywhere in Microsoft Access 7.0 or 97 by pressing CTRL+G. You can use a question mark (?) as shorthand for the Print method in the Debug window.

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

The beginning of functions, to 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

      End Function
```
If the values of the arguments are not correct, the problem occurs before the function runs. You can also use the Debug.Print statement at the beginning of each function if you have a complex application and you're not sure which function might be causing a problem. This enables you to check the Debug window to see the order in which functions are called and to determine which function is last. In Microsoft Access 97, you can also view function or subroutine calls by clicking the Build button in the Debug window. In Microsoft Access 7.0, you can click Calls on the Tools menu to list the current function call stack and to show the order in which the functions are called.

Decision structures, to check that you are using the correct logic for the function. For example, the following code sample uses a Select Case statement to evaluate the value of a variable. The code also uses a Debug.Print statement to verify that it is doing what you expect:

      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

Run this function in the Debug Window by typing the following line and then pressing ENTER:

?DueDate(#10/1/95#)

Note that the following results appear in the Debug window:

Function DueDate 10/1/95
Result: 11/1/95
Weekday(Result): 2
Case Else
11/1/95

These results show that you are using the correct logic for this function. If you receive different results (the wrong case, for example), then you can check the values of other variables and expressions to determine where the problem is.

In loops, to check the values of variables. If you use a breakpoint to check the value of a variable in a loop, you cannot see how the value of the variable changes as the loop executes. However, if you include the statement Debug.Print MyName in your loop structure, the value of variable MyName appears in the Debug window each time the function cycles through the loop.

This method is also a good way to verify that the loop is executing the number of times you expect. If the loop executes four times, you should see four values for the value in the Debug window.
Where clauses, to check the values of criteria in SQL statements. For example, the following sample function creates a dynaset from a SQL statement. If there is a problem in a SQL statement (such as in this function), it can be difficult to locate the problem. However, this function uses the Debug.Print statement to display the SQL statement as Microsoft Access sees it and uses it:
```
      Function TestMe()
         Dim db As Database, rs As 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 Debug window by typing the following line and then pressing ENTER:

?TestMe()

Note that the following result appears:

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 Database, rs As 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 corrected function in the Debug window, note that the following statement appears:

select * from orders where [employeeid]=5;

Set a Watch Expression in Visual Basic for Applications Code

A watch expression is an expression that you monitor in the Debug window. You can observe the values of the watch expressions that you select in the Watch pane. You can also perform an Instant Watch to see the value of an expression that hasn't been specified as a Watch expression. To add a Watch expression to the Watch pane, follow these steps:

Open the Debug window by pressing CTRL+G.
On the Debug menu (or the Tools menu in version 7.0), click Add Watch.
In the Expression box, type a variable, property, function call, or other valid expression.
In the Context box, set the scope of the expression that you are watching by selecting the Procedure name and the Module name.
In the Watch Type box, click the option to determine how you want to evaluate the expression.

In Microsoft Access 97, 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.

When the execution of your code is suspended, you can click Quick Watch on the Debug menu in Microsoft Access 97 to check the value of an expression that hasn't been defined as a Watch expression. You can also select an expression in your code and click the Quick Watch for it. (In Microsoft Access 7.0, you can use the Instant Watch command. You can also click the Instant Watch button on the Visual Basic toolbar to create a Watch expression from text that you've selected in the Module window.)

Using the Calls Dialog Box to Trace Nested Procedures

The Calls dialog box displays a list of all active procedure calls. These calls are the procedures in an application that are started but not completed. You can use the Calls dialog box to trace the operation of an application as it runs a series of procedures. You can view the Calls from the Debug window by clicking the Build button.

The Calls dialog box lists all the active procedure calls in a series of nested procedure calls. It places the earliest active procedure call at the bottom of the list and adds subsequent procedure calls to the top.

You can use the Show button in the Calls dialog box to display the statement that calls the next procedure listed in the Calls dialog box. If you choose the current procedure in the Calls dialog box and then click Show, Visual Basic for Applications displays the current statement at which execution was suspended.

Using the Locals Pane

The Locals pane in the Debug window has three columns: Expression, Value, and Type (or Context in version 7.0). 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 Debug window in order to test the behavior of your module. The Type column shows the type of the current module-level object.

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

REFERENCES

For more information about the Debug window, search the Help Index for "Debug window."

For more information about debugging Visual Basic for Applications code, search the Help Index for "debugging code."

Additional query words: vba

Keywords: kbfaq kbinfo kbprogramming KB142999

Microsoft KB Archive/142999

Contents