ID: Q142999
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
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.
There are several different methods that you can use to debug your Visual Basic for Applications code. The main ones are as follows.
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:
1. Open the module that contains your function in Design view.
2. On the View menu, click Debug Window.
3. 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.
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:
1. 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.
7.0.). The line appears in bold type, indicating that there is a
breakpoint set at that line.
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.
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:
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.
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.
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.
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;
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:
1. Open the Debug window by pressing CTRL+G.
2. On the Debug menu (or the Tools menu in version 7.0), click Add Watch.
3. In the Expression box, type a variable, property, function call, or
other valid expression.
watching by selecting the Procedure name and the Module name.
evaluate the expression.
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.)
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.
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.
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:
Keywords : kbprg PgmErr kbfaq
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfoLast Reviewed: November 22, 1998