The VBA Editor

If you are going to create VBA code, you are going to spend most of your time in the VBA Editor. We will take a quick tour here and then look at what the various tools do in greater detail throughout the book.

If the editor is not open already, select Tools | Macro | Visual Basic Editor. The editor will look something like Figure 5-2. It is divided into distinct windows to help you handle various aspects of your project. The upper-left-hand corner is the Project window. This window helps you maneuver to the various code modules associated with your project. One of the main uses of this window is to find and open the various modules that hold your code.

Figure 5-2: The VBA Editor

The VBA Editor is divided into distinct windows to help us handle various aspects of our project.

In the upper-left-hand corner is the Project window, as shown in the following illustration.

This window helps us maneuver to the various files associated with our project. One of the main uses of this window is to find out and open the various modules which hold our code.

Right below the Project window is the Properties window, shown in Figure 5-3. In the example, this shows the various properties that can be set for frmCustomer. Setting them in a menu saves a lot of coding. Along the bottom of the VBA Editor is the Immediate window. You use this to test your code. In the course of this book, we will spend a lot of time in this window.

Figure 5-3: The Properties window

Along the bottom of the VBA Editor is the Immediate Window. We would use this to test our code. In the course of this book, we will be spending a lot of time using this window.

The large center area is where the real work is done. This is the Code window, where you will type your procedures and functions. Right above the Code window are two list boxes. The example shows a form module (we created it in Chapter 3 and have not created any standard modules yet) for the frmCustomer object. The list box on the left displays the objects associated with frmCustomer and is shown in Figure 5-4.

Figure 5-4: The Objects list box

The list box on the right (see Figure 5-5) displays the procedures associated with this form. Here you see the objects we discussed in the previous chapters. Notice that the form’s procedures coincide with events, for example, Click, Exit, Enter, KeyPress, and so on.

Figure 5-5: The Procedures list box

Putting the VBA Editor to Work

Up to this point, the only module in our VBA Editor is a form module that was created when we converted a macro to VBA code in Chapter 3. For the moment, let’s imagine that the form module is there with absolutely no code on it, and that you want to create a click procedure for the cmdFirst button. How would you do it? Let’s walk through the process.

First, you select the cmdFirst object from the left list box, as shown here.

Once you select it, the VBA Editor automatically sets up the sub procedure (remember, it is not returning anything) in the Code window as follows:

Private Sub cmdFirst_Click() End Sub

The procedure now has the opening and closing lines. Notice that VBA assumed that you wanted it to be a Click event. This is the default event for a command button. You could have selected a different event in the right list box, and the editor would have changed it to whatever event you chose.

All you need to do is add the implementation between the opening and closing lines of the sub. Click between the two lines. As a matter of programming style, I like to indent the code within the sub for clarity. To do this, I press SPACEBAR three times.

Using all lowercase letters (you will see why in a second), type docmd and then a period (the dot). Notice that the VBA Editor gives you a list of available methods for the DoCmd object, as shown here.

You want the GoToRecord method. You can get to that by pressing G on your keyboard, which should bring you to the GoToControl method. Just click the down arrow until you get to GoToRecord, and then press the SPACEBAR. (Do not press ENTER!)

Once you press the SPACEBAR to select GoToRecord, your Code window should look like this:

Notice that it is now showing the next argument, ObjectType, as well as a list of possible choices. For the time being, I have to ask you to take me on faith. If I were to explain the meaning of each option at this point, we would get terribly off track.

You don’t need an object type at this point. Just press the comma on your keyboard. The bolded option should change from ObjectType to ObjectName:

For now, put “ “ there as an empty name. Once you have put the empty quotes in, press comma again.

You should now have a list of possible values:

Note that these are not methods. There is a different icon to the left. It is a good idea to become familiar with these icons. Scroll up and select acFirst from the list and press the SPACEBAR.

You are finished building the statement. Using your mouse or arrow key, move off the line you just created. Notice that the docmd changed to DoCmd. Even though VBA is not a case-sensitive language, the VBA Editor examines the line to make sure all the syntax is correct and, if it is, converts the case to programming standards automatically.

Your finished code should look as follows:

Private Sub cmdFirst_Click()   DoCmd.GoToRecord , "", acFirst End Sub

The editor did much of the work for you.

There are a lot more options and features available in the VBA Editor; so many, in fact, that Chapter 7 is devoted to a discussion of the editor. In subsequent chapters you will see additional features as your coding becomes more sophisticated.