Using the VBA Editor and Debugging Code

The Microsoft Visual Basic for Applications (VBA) Editor is more than a simple editing tool for writing program code. It is an environment in which you can test, debug, and develop your programs. Understanding the unique way in which the editor allows you to make modifications to application code while the execution of the code is paused will help you to learn how to quickly develop your applications and master the techniques for debugging code.

In addition to changing code on-the-fly as it executes, you can switch across to the Microsoft Access 2010 application window while your code is paused, create a query, run the query, copy the SQL to the clipboard, and then swap back to the programming environment to paste the SQL into your code.

To successfully work with VBA, you need an understanding of the language, the programming environment, and the objects that are manipulated by the code. Getting started means dipping into different topics as you begin to build sufficient knowledge to effectively use VBA.

To begin, open the sample database, VBAEnvironment.accdb, which opens the startup form, frmVBAStartsHere, shown in Figure .

The startup form, frmVBAStartsHere.

The sample database contains program code with errors intentionally integrated into it. The frmVBAStartsHere form is designed to show how the code will break into Debug mode when it encounters an error. As you work through this chapter, you will fix these errors. Click the button labeled Look At The Table Of Contacts. A pop-up window appears, as shown in Figure .

In this Access pop-up window, you can either end the code execution or click Debug to investigate the error.

If you click the End button, the program code stops executing. But as you want to debug the code, click the Debug button. Entering the VBA Editor When entering debugging mode, the program stops in the VBA editor and highlights the line of code at which it failed in yellow, as shown in Figure .

Choosing Debug opens the VBA Editor and highlights the program code line that generated the error.

In this example, the problem is a simple spelling error. The database contains a form called frmContacts, not fromContacts. Access displays an error message that fully describes the problem. It also provides you with the opportunity to edit the text to correct the misspelling, as shown in Figure.

Code stopped at the error line. Notice in the Project Explorer pane on the left that the entry form _frmVBAStartsHere is highlighted. This tells you that you are viewing the form’s code module.

DoCmd.OpenForm is a command that allows the program code to open the specified form. DoCmd is a shorthand way of saying, ”do the macro command.” After correcting the misspelling, you can either press the F5 key or click the Continue button on the toolbar to allow the program to continue execution. Figure demonstrates the results after continuing to execute the code, which now opens the frmContacts form.

After correcting the programming error, you can see the result of executing DoCmd.OpenForm, which opens the requested Access form.

The Application and VBA Code Windows Notice that in your Windows task bar there are two windows open: one window containing your Access application interface, and in the second window, the VBA Editor. When working with application code you can normally switch between the Editor and the application windows, as shown in Figure .

With the VBA editor open, you have two windows for Access, and you can switch between the application window and the VBA Editor window.

If you choose to close the forms you will be prompted to save the changes that you have made to the code on the form, as shown in Figure.

The prompt to save changes to the frmVBAStartsHere form.

Code written in a form or report class module is normally related to events on the form or report, and not normally shared in any other part of the application.