Using VBA Editor Options

A number of features of the VBA Editor and the VBA programming environment are configurable through the VBA Editor Options dialog box. You access the dialog box by selecting Tools | Options. Figure 7-11 shows the dialog box with the Editor tab selected. In most instances, the default options will work fine. However, let’s take a close look at these options and learn about some of the ways you can customize your environment.

Figure 7-11: VBA Editor Options with the Editor tab selected

Editor Tab

The settings on the Editor tab are the primary options you will be concerned with while working with your code.

Auto Syntax Check

You may have run into the Auto Syntax Check option already. As soon as you leave a line of code (using either the ENTER or arrow keys or the mouse), the checker will examine the line for proper syntax. If it finds anything irregular, it will try to fix it. For instance, it will remove unnecessary spaces, change casing to conform to programming standards, and possibly add missing closing quotation marks and parentheses.

Note

Remember that VBA is not case sensitive, but code is easier to read if you follow accepted programming standards for capitalization.

If the checker cannot make all of the corrections, you will get a message something like the one shown here:

It will give you an indication, which may not always be clear, of what is wrong and how to fix it.

Without this feature turned on, the defective code would be displayed in a different color only.

Require Variable Declaration

We have already seen the Require Variable Declaration option in Chapter 5. It places the statement Option Explicit in the General Declarations area when a new module is open. This requires that you formally declare all variables before you use them. As stated in Chapter 5, I feel that this is important because failure to do so could promote misspellings of variable names, which could lead to all sorts of errors. For instance, you could call a variable myName in one place and, inadvertently, use myNmae in another place. By being forced to declare the variable first, this sort of error can be averted.

Auto List Members

The Auto List Members feature will give you a listing of relevant members when you type the dot after the object name in the Code window. (Remember, members are properties, methods, and events associated with an object.)

The Auto List Members will not come up unless there has been a reference established to the object using the References dialog box discussed earlier. As a very simple example, let’s say you type the object Debug and click the dot. You will see the list shown in Figure 7-12.

Figure 7-12: Results from Auto List Members

As we progress, we will be referring to this feature often.

Auto Quick Info

You can use the Auto Quick Info feature to see the proper syntax for a function. Again, you have probably seen this already when you tried the MsgBox or InputBox functions. In order for this feature to work properly, a reference to the library containing the function must have been added to the project using the References dialog box discussed earlier. Figure 7-13 shows Auto Quick Info for the MsgBox function.

Figure 7-13: Auto Quick Info for the MsgBox function

In the process of building the function’s syntax, an Auto List Member could pop up where members are required.

Auto Data Tips

The Auto Data Tips option is an invaluable tool for debugging when stepping through a program and for testing. When you roll your mouse over a variable name, the current value assigned to that variable will be displayed.

For instance, as shown in Figure 7-14, if I use the VBA Editor in a debug mode and step through the code past the assignment for intNumber1, then point to intNumber1 in the calculation a couple of lines down, I can take advantage of this feature to be certain that the assignment is being correctly made.

Figure 7-14: Using Auto Data Tip

Auto Indent/Tab Width

Using Auto Indent, once you indent a line, subsequent lines will retain the indentation. This will make the code a little easier to read. You can use either the SPACEBAR or the TAB key to indent.

The default setting for Tab Width is 4. You can change this in the text box if you wish.

Window Settings

Under Window Settings on the Editor tab, you have three options:

• Drag and Drop Text Editing allows you to move code around by highlighting it and dragging it to a new location in much the same way as you would in Microsoft Word.

• Default to Full Module View allows all the procedures in a module to be displayed in a scrollable Code window. However, if this is turned off, you will see only one procedure at a time. As you will see, you can toggle between these two modes using a button in the lower left side of the Code window.

• Procedure Separator will draw a horizontal line across the Code window to show a clear separation of procedures.

Editor Format Tab

On the Editor Format tab of the Options dialog box, as shown in Figure 7-15, you can set the font, size, and colors displayed in the Code window.

Figure 7-15: Editor Format tab

Remember, these features have absolutely no effect on how the code operates. They just help visibility and the ability to spot errors quickly. In most cases, the default settings are more than adequate.

General Tab and Docking Tab

The General tab, shown in Figure 7-16, handles the grid settings for setting up a form in Design View. However, you would use these only if you are creating a user interface for a non-Access database. It is not relevant to Microsoft Access. As a result, you will rarely be concerned about this feature. The General tab does have some features we will be examining when we discuss debugging.

Figure 7-16: General tab

The Docking tab, shown in Figure 7-17, allows you decide which windows will be docked, and which will float, when the VBA Editor is open. Leaving these checked will keep your VBA Editor nicely organized.

Figure 7-17: Docking tab