ACC: Tips for Debugging Visual Basic for Applications Code

• Microsoft Access versions 7.0, 97

SUMMARY

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.

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

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.

Use a Breakpoint to Suspend Execution of Code

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:

   ?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

• 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

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.

Using the Calls Dialog Box to Trace Nested Procedures

Using the Locals Pane

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:

Keywords : kbprg PgmErr
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbinfo