ACC: Tips for Debugging Visual Basic for Applications Code

This article was previously published under Q142999
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
This article lists tips that can help you debug your Visual Basic forApplications code. This article assumes that your code compilescorrectly, 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 errorsthat 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 isto break larger procedures into smaller sub-procedures to verify that theindividual steps in each sub-procedure work correctly, and then to combinethe sub-procedures one by one until the code works correctly.
There are several different methods that you can use to debug your VisualBasic 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 individuallines of Visual Basic for Applications code or to check the values ofvariables. In the Debug window, you can test and debug Function and Subprocedures, check the value of a field, a control, a property setting, avariable, or an expression; and display the result of an expression whenthe code is running. For an example of how to use the Debug window, followthesesteps:

  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:
    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 codeis still running, but pauses between running statements. To make VisualBasic for Applications pause execution of your code, you can set abreakpoint. For example, if the first part of your code runs correctly, butother 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.
  2. 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.
  3. 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:
    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 enteringthe Print method of the Debug object, followed by the expression. You candisplay the Debug window anywhere in Microsoft Access 7.0 or 97 bypressing CTRL+G. You can use a question mark (?) as shorthand for thePrint 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:
    Note that the following results appear in the Debug window:
    Function DueDate 10/1/95
    Result: 11/1/95
    Weekday(Result): 2
    Case Else
    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:
    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 theWatch pane. You can also perform an Instant Watch to see the value of anexpression that hasn't been specified as a Watch expression. To add aWatch 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.
  4. In the Context box, set the scope of the expression that you are watching by selecting the Procedure name and the Module name.
  5. 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 whichyou set a watch. You can also restrict the scope used to watch variablesdefined for a specific procedure or a specific module, or globally in thecode.

When the execution of your code is suspended, you can click Quick Watchon the Debug menu in Microsoft Access 97 to check the value of anexpression that hasn't been defined as a Watch expression. You can alsoselect an expression in your code and click the Quick Watch for it. (InMicrosoft Access 7.0, you can use the Instant Watch command. You can alsoclick the Instant Watch button on the Visual Basic toolbar to create aWatch 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 notcompleted. You can use the Calls dialog box to trace the operation of anapplication as it runs a series of procedures. You can view the Calls fromthe Debug window by clicking the Build button.

The Calls dialog box lists all the active procedure calls in a seriesof nested procedure calls. It places the earliest active procedure callat 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 thestatement that calls the next procedure listed in the Calls dialog box.If you choose the current procedure in the Calls dialog box and thenclick Show, Visual Basic for Applications displays the current statement atwhich 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 withthe current module (for a standard module), or the current instance of aclass (for a class module). The expression column is organized as ahierarchical tree of information starting from the current module todisplay all of the module-level variables in the current module. TheValue column shows the values of the current module objects. You canchange the value of a module-level variable in the Debug window inorder to test the behavior of your module. The Type column shows thetype of the current module-level object.

Inspecting a selected variable's value in the Locals pane can be veryhelpful in debugging your module, as can changing a variable's value inthe Locals pane Value column to observe what effect it has on other partsof 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."

Article ID: 142999 - Last Review: 12/04/2015 13:07:07 - Revision: 3.3

Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbfaq kbinfo kbprogramming KB142999