Why Debugging Is Important
A good programmer is not necessarily one who can get things right the first time. To be fully effective as a Visual Basic for Applications (VBA) programmer, you need to master the art of debugging, the process of troubleshooting your application. Debugging involves locating and identifying problem areas within your code and is a mandatory step in the application-development process. Fortunately, the Access 2010 Visual Basic Editor (VBE) provides excellent tools to help you with the debugging process. Using the Access 2010 debugging tools, you can step through your code, setting watchpoints and breakpoints as needed.
Using the VBA debugging tools is significantly more efficient than taking random stabs at fixes to your application. A strong command of the Access 2007 debugging tools can save you hours of trial and error. In fact, it can be the difference between a successfully completed application-development process and one that continues indefinitely with problems left unsolved.
Avoiding Bugs
The best way to deal with bugs is to avoid them in the first place. Proper coding techniques can really aid you in this process. Using the Option Explicit statement, strong-typing, naming standards, and tight scoping can help you eliminate bugs in your code.
Option Explicit
Option Explicit requires that you declare all your variables before you use them. Including Option Explicit in each Form, Code, and Report module helps the VBA compiler find typos in the names of variables.
The Option Explicit statement is a command that you can manually insert into the General Declarations section of any Code, Form, or Report module. If you prefer, you can have Access automatically insert the Option Explicit statement. To accomplish this, select Require Variable Declaration from the Editor tab after choosing Tools, Options from within the Visual Basic Editor. After you select that setting, Access inserts an Option Explicit statement in the General Declarations section of all new modules. This setting does not affect existing modules.
Strong-Typing
Strong-typing a variable means indicating at declaration time the type of data you will store in a variable. For example, Dim intCounter As Integer initializes a variable that contains integers. If elsewhere in your code you assign a character string to intCounter, the compiler will catch the error.
Naming Standards
Naming standards can also go a long way toward helping you eliminate errors. The careful naming of variables makes your code easier to read and makes the intended use of the variable more obvious. Problem code tends to stand out when you have judiciously followed naming conventions.
Variable Scoping
Finally, giving your variables the narrowest scope possible reduces the chances of one piece of code accidentally overwriting a variable within another piece of code. You should use local variables whenever possible. Use module-level and global variables only when it is necessary to see the value of a variable from multiple subroutines or multiple modules.
Bugs Happen!
Unfortunately, no matter what you do to prevent problems and errors, they still creep into your code. Probably the most insidious type of error is a logic error. A logic error is sneaky because it escapes the compiler; your code compiles but simply does not execute as planned. This type of error might become apparent when you receive a runtime error or when you don’t get the results you expected. In these cases, the debugger comes to the rescue.
Harnessing the Power of the Immediate Window
The Immediate window serves several purposes. It provides you with a great way to test VBA and user-defined functions, it enables you to inquire about and change the values of variables while your code is running, and it enables you to view the results of Debug.Print statements. To open the Immediate window while in the Visual Basic Editor, do one of three things:
Note:
An advantage of pressing Ctrl+G is that this keystroke combination invokes the Immediate window without a Code window being active. You can click the Immediate window toolbar button or choose View, Immediate window only from within the VBE.
Figure 1 shows the Immediate window.
A good programmer is not necessarily one who can get things right the first time. To be fully effective as a Visual Basic for Applications (VBA) programmer, you need to master the art of debugging, the process of troubleshooting your application. Debugging involves locating and identifying problem areas within your code and is a mandatory step in the application-development process. Fortunately, the Access 2010 Visual Basic Editor (VBE) provides excellent tools to help you with the debugging process. Using the Access 2010 debugging tools, you can step through your code, setting watchpoints and breakpoints as needed.
Using the VBA debugging tools is significantly more efficient than taking random stabs at fixes to your application. A strong command of the Access 2007 debugging tools can save you hours of trial and error. In fact, it can be the difference between a successfully completed application-development process and one that continues indefinitely with problems left unsolved.
Avoiding Bugs
The best way to deal with bugs is to avoid them in the first place. Proper coding techniques can really aid you in this process. Using the Option Explicit statement, strong-typing, naming standards, and tight scoping can help you eliminate bugs in your code.
Option Explicit
Option Explicit requires that you declare all your variables before you use them. Including Option Explicit in each Form, Code, and Report module helps the VBA compiler find typos in the names of variables.
The Option Explicit statement is a command that you can manually insert into the General Declarations section of any Code, Form, or Report module. If you prefer, you can have Access automatically insert the Option Explicit statement. To accomplish this, select Require Variable Declaration from the Editor tab after choosing Tools, Options from within the Visual Basic Editor. After you select that setting, Access inserts an Option Explicit statement in the General Declarations section of all new modules. This setting does not affect existing modules.
Strong-Typing
Strong-typing a variable means indicating at declaration time the type of data you will store in a variable. For example, Dim intCounter As Integer initializes a variable that contains integers. If elsewhere in your code you assign a character string to intCounter, the compiler will catch the error.
Naming Standards
Naming standards can also go a long way toward helping you eliminate errors. The careful naming of variables makes your code easier to read and makes the intended use of the variable more obvious. Problem code tends to stand out when you have judiciously followed naming conventions.
Variable Scoping
Finally, giving your variables the narrowest scope possible reduces the chances of one piece of code accidentally overwriting a variable within another piece of code. You should use local variables whenever possible. Use module-level and global variables only when it is necessary to see the value of a variable from multiple subroutines or multiple modules.
Bugs Happen!
Unfortunately, no matter what you do to prevent problems and errors, they still creep into your code. Probably the most insidious type of error is a logic error. A logic error is sneaky because it escapes the compiler; your code compiles but simply does not execute as planned. This type of error might become apparent when you receive a runtime error or when you don’t get the results you expected. In these cases, the debugger comes to the rescue.
Harnessing the Power of the Immediate Window
The Immediate window serves several purposes. It provides you with a great way to test VBA and user-defined functions, it enables you to inquire about and change the values of variables while your code is running, and it enables you to view the results of Debug.Print statements. To open the Immediate window while in the Visual Basic Editor, do one of three things:
- 1
- Click the Immediate window tool on the Debug toolbar.
- 2
- Choose View, Immediate window.
- 3
- Press Ctrl+G.
Note:
An advantage of pressing Ctrl+G is that this keystroke combination invokes the Immediate window without a Code window being active. You can click the Immediate window toolbar button or choose View, Immediate window only from within the VBE.
Figure 1 shows the Immediate window.
The Immediate window enables you to test functions and to inquire about and change the values of variables.
Note:
The Debug tools are available on a separate toolbar. To show the Debug toolbar, right-click any toolbar or menu bar and select Debug from the list of available toolbars.
Testing Values of Variables and Properties
The Immediate window enables you to test the values of variables and properties as your code executes. This feature can be quite enlightening as to what is actually happening within your code.
To practice with the Immediate window, you do not even need to be executing code. To invoke the Immediate window while in a form, report, or module, press Ctrl+G. To see how this works, follow these steps:
- 1
- Run the frmClients form in the sample database
- 2
- Press Ctrl+G to open and activate the Immediate window. Access places you in the VBE within the Immediate window.
- 3
- Type ?Forms!frmClients.txtClien
tID.Value and press Enter. The client ID of the current client appears on the next line.
- 4
- Type ?Forms!frmClients.txtCompa
nyName.Vis ible and press Enter. The word True appears on the next line, indicating that the control is visible.
- 5
- Type ?Forms!frmClients.txtConta
ctTitle.Ba ckColor and press Enter. The number associated with the BackColor of the Contact Title text box appears on the next line.
Your screen should look like the one shown in Figure 2. You can continue to request the values of properties or variables within your VBA code.
Setting Values of Variables and Properties
You can not only display things in the Immediate window, but you also can use the Immediate window to modify the values of variables and controls as your code executes. This feature becomes even more valuable when you realize that you can re-execute code within a procedure after changing the value of a variable. Here’s how this process works:
- 1
- Invoke the Immediate window, if necessary. Remember that you can do this by pressing Ctrl+G.
- 2
- Type Forms!frmClients.txtContac
tTitle.Val ue = “Hello” in the Immediate window. Press Enter. The contact title of the current record changes to Hello.
- 3
- Type Forms!frmClients.txtIntroD
ate.Visibl e = False. Press Enter. Access hides the txtIntroDate control on the frmClients form.
- 4
- Type Forms!frmClients.txtClient
ID.BackCol or = 123456. Press Enter. The background color of the txtClientID control on the frmClients form turns green. The Immediate window and your form now look like those shown in Figures 3 and 4, respectively.
The results of using the Immediate window to set the values of properties are shown here.
The Immediate window is an extremely valuable testing and debugging tool. The examples here barely begin to illustrate its power and flexibility.
Caution:
Changes you make to data while working in the Immediate window are permanent. On the other hand, Access does not save changes you make to the properties of controls or the values of variables with the form or report.
Some people think that data changes made in the Immediate window are not permanent. In other words, if you modify the last name of a customer, they believe that the change will not be permanent (but, of course, it is). Other people think that if they change the BackColor property of a control, the change will persist in the design environment (but, of course, it won’t).
Clearing the Immediate Window
The Immediate window displays the last 200 lines of output. As you add additional lines of code to the Immediate window, older lines disappear. When you exit completely from Access, it clears the Immediate window. If you want to clear the Immediate window at any other time, follow these three steps:
- 1
- With the Immediate window active, press Ctrl+Home to go to the top of the Immediate window.
- 2
- Hold down your Shift key and press Ctrl+End to go to the last statement in the Immediate window.
- 3
- Press Delete.
Practicing with the Built-In Functions
In addition to being able to test and set the values of properties and variables using the Immediate window, you can test any VBA function. To do so, type the function and its arguments in the Immediate window, preceded by a question mark. This code returns the month of the current date, for example:
?datepart(“m”,date)
This code tells you the date one month after today’s date:
?dateadd(“m”,1,date)
This code tells you how many days exist between the current date and July 4th, 2011:
?datediff(“d”,date(),#7/4/
Executing Subroutines, Functions, and Methods
In addition to enabling you to test any VBA function, the Immediate window lets you test any user-defined subroutine, function, or method. This is a great way to debug your user-defined procedures. To see how this works, follow these steps:
- 1
- Open the basExamples module found in the sample database.
- 2
- Invoke the Immediate window if it is not already visible.
- 3
- Type ?ReturnInitsFunc(“Bill”,”G
ates”). This calls the user-defined function ReturnInitsFunc, sending “Bill” as the first parameter and “Gates” as the second parameter. The value B.G. appears in the Immediate window. This is the return value from the function.
- 4
- Type Call ReturnInitsSub(“Bill”,”Gat
es”). This calls the user-defined subroutine ReturnInitsSub, sending “Bill” as the first parameter and “Gates” as the second parameter. The value B.G. appears in a message box.
Notice the difference between how you call a function and how you call a subroutine. Because the function returns a value, you must call it using a question mark. On the other hand, when calling a subroutine, you use the Call keyword.
Note:
You also can call a subroutine from the Immediate window by using this syntax:
RoutineName Parameter1, Parameter2, ....
Notice that, when you omit the Call keyword, you do not need to enclose the parameters in parentheses.
Printing to the Immediate Window at Runtime
The capability to print to the Immediate window is useful because you can test what is happening as your code executes without having to suspend code execution. It also is valuable to be able to print something to a window when you are testing, without interfering with the user-interface aspect of your code. You can test a form without being interrupted and then go back and view the values of variables and so on. Here’s how the process works:
- 1
- Type Call LoopThroughCollection in the Immediate window. This calls the user-defined subroutine LoopThroughCollection. The values Skating, Basketball, Hockey, and Skiing appear. The routine prints these values to the Immediate window.
- 2
- Open the frmDebugPrint form in Form view.
- 3
- Press Tab to move from the First Name field to the Last Name field.
- 4
- Press Tab to move back to the First Name field.
- 5
- Type your first name.
- 6
- Open the Immediate window. Notice that the routine sent all the statements to the Immediate window (see Figure 5). I coded these Debug.Print statements in all the appropriate form and control events.
Note:
Although it is good practice to remove Debug.Print statements after you have completed the debugging process, you can safely deploy your applications without removing them. Your users will never know that the statements are in your code unless they view the Immediate window. The Debug.Print statements result in only a minor degradation in performance.
You just learned how to utilize the Immediate window to help you with the process of debugging an application. In Part 2 of this article, you’ll learn how to step through code so that you can determine exactly what is happening as your code executes.