ACC2000: Tips for Debugging Visual Basic for Applications Code

The information in this article applies to:

Microsoft Access 2000
Microsoft Visual Basic for Applications

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

This article applies to a Microsoft Access database (.mdb) and 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:

http://www.microsoft.com/partner/referral/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://support.microsoft.com/directory/overview.asp

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 ? FunctionName(arg1, ..., argN) and press ENTER, where FunctionName is the name of your function and arg1 ... argN are any arguments it requires.

If you are testing a Sub procedure, type SubNamearg1, ..., argN, where SubName is the name of your procedure and arg1 ... argN 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.

In the Immediate window, type ? PyrNum(3), and then press ENTER.

Notice that the code execution pauses at the breakpoint.

Point to the variable intNumber and notice that its current value appears as a screen tip.

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.

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:

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.

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

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:

   3             3

   2             5

   1             6

The final 6 is the return value of the function, and not output from the Debug.Print statement.

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:

    select * from orders where [employeeid]=5;

This is the correct SQL Where clause.

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.

If the Immediate window is not open, open it by pressing CTRL+G.

On the Debug menu, click Add Watch.

In the Expression box, type PyrNum.

In the Module box, select modulename, where modulename is the name of the module you have just created. In the Procedure box, select PyrNum.

In the Watch Type box, click Watch Expression.

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.

In the Immediate window, type ListPyrs 3 and press ENTER.

After the execution pauses at the breakpoint, press function key F8 repeatedly and notice the change in value of PyrNum in the Watch Window.

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:

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

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

In the Immediate window, type ListPyrs 3

On the View menu, click Call Stack. Notice that ListPyrs is the only active procedure. Click Close.

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.

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.

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.

REFERENCES

For more information about the Immediate window, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type use the Immediate window of the visual basic editor while working with visual basic in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about breakpoints, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type use a breakpoint to suspend running of visual basic code in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about watch expressions, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type set a watch expression in visual basic code in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about debugging VB code, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type debug visual basic code in the Office Assistant or the Answer Wizard, and then click Search to view the topic.