Customizing the VBE Environment

If you're serious about becoming an Excel programmer, you'll be spending a lot of time with the VBE window. To help make things as comfortable as possible, the VBE provides quite a few customization options.

When the VBE is active, choose Tools Options. You see a dialog box with four tabs: Editor, Editor Format, General, and Docking. I discuss some of the most useful options on these tabs in the sections that follow. By the way, don't confuse this with the Excel Options dialog box, which you bring up by choosing Office Excel Options in Excel.

Using the Editor tab

Figure 7-6 shows the options that you access by clicking the Editor tab of the Options dialog box.

Figure 7-6: The Editor tab of the Options dialog box.

AUTO SYNTAX CHECK OPTION

The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you're entering your VBA code. The dialog box tells you roughly what the problem is. If you don't choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don't have to deal with any dialog boxes popping up on your screen.

I keep this setting turned off because I find the dialog boxes annoying, and I can usually figure out what's wrong with an instruction. But if you're new to VBA, you might find this assistance helpful.

REQUIRE VARIABLE DECLARATION OPTION

If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module that you insert:

 Option Explicit 

If this statement appears in your module, you must explicitly define each variable that you use. This is an excellent habit to get into, although it does require some additional effort on your part. If you don't declare your variables , they will all be of the Variant data type, which is flexible but not efficient in terms of storage or speed. I discuss variable declaration in more depth in Chapter 8.

Note  

Changing the Require Variable Declaration option affects only new modules, not existing modules.

AUTO LIST MEMBERS OPTION

If the Auto List Members option is set, VBE provides some help when you're entering your VBA code by displaying a list of member items for an object. These items include methods and properties for the object that you typed.

This option is very helpful, and I always keep it turned on. Figure 7-7 shows an example of Auto List Members (which will make a lot more sense when you actually start writing VBA code). In this example, VBE is displaying a list of members for the Application object. You can just select an item from the list and press Tab, thus avoiding typing it (or, double-click an item). Using the Auto List Members list also ensures that the item is spelled correctly.

Figure 7-7: An example of Auto List Members.

AUTO QUICK INFO OPTION

If the Auto Quick Info option is set, the VBE displays information about the arguments available for functions, properties, and methods while you type. This can be very helpful, and I always leave this setting on. Figure 7-8 shows this feature in action. It's displaying the syntax for the Cells property.

Figure 7-8: An example of Auto Quick Info offering help about the Cells property.

AUTO DATA TIPS OPTION

If the Auto Data Tips option is set, you can hover your mouse pointer over a variable, and VBE displays the value of the variable. This technique works only when the procedure is paused while debugging. When you enter the wonderful world of debugging, you'll definitely appreciate this option. I always keep this option turned on.

AUTO INDENT OPTION

The Auto Indent setting determines whether VBE automatically indents each new line of code by the same amount as the previous line. I'm a big fan of using indentations in my code, so I keep this option on. You can also specify the number of characters to indent; the default is four.

Tip  

Use the Tab key, not the space bar, to indent your code. Using the Tab key results in more consistent spacing. In addition, you can use Shift+Tab to unindent a line of code. These keys also work if you select more than one statement.

DRAG-AND-DROP TEXT EDITING OPTION

The Drag-and-Drop Text Editing option, when enabled, lets you copy and move text by dragging and dropping. I keep this option turned on, but I never use drag-and-drop editing. I prefer to use keyboard shortcuts for copying and pasting.

DEFAULT TO FULL MODULE VIEW OPTION

The Default to Full Module View option specifies how procedures are viewed . If this option is set, procedures in the code window appear as a single scrollable window. If this option is turned off, you can see only one procedure at a time. I keep this setting turned on.

PROCEDURE SEPARATOR OPTION

When the Procedure Separator option is turned on, the VBE displays separator bars between procedures in a code window ( assuming that the Default to Full Module View option is also selected). I like the visual cues that show where my procedures end, so I keep this option turned on.

Using the Editor Format tab

Figure 7-9 shows the Editor Format tab of the Options dialog box. The options on this tab control the appearance of the VBE itself.

Figure 7-9: The Editor Format tab of the Options dialog box.

CODE COLORS OPTION

The Code Colors option lets you set the text color (foreground and background) and the indicator color displayed for various elements of VBA code. This is largely a matter of individual preference. Personally, I find the default colors to be just fine. But for a change of scenery , I occasionally play around with these settings.

FONT OPTION

The Font option lets you select the font that's used in your VBA modules. For best results, stick with a fixed-width font (monofont) such as Courier New. In a fixed-width font, all characters are exactly the same width. This makes your code much more readable because the characters are nicely aligned vertically and you can easily distinguish multiple spaces.

SIZE SETTING

The Size setting specifies the size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and your eyesight. The default size of 10 (points) works for me.

MARGIN INDICATOR BAR OPTION

The Margin Indicator Bar option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise , you won't be able to see the helpful graphical indicators when you're debugging your code.

Using the General tab

Figure 7-10 shows the options available under the General tab in the Options dialog box. In almost every case, the default settings are just fine.

Figure 7-10: The General tab of the Options dialog box.

CROSS-REFERENCE  

The Error Trapping setting determines what happens when an error is encountered . If you write any error-handling code, make sure that the Break on Unhandled Errors option is set. If the Break on All Errors option is set, error-handling code is ignored (which is hardly ever what you want). I discuss error-handling techniques in Chapter 9.

Using the Docking tab

Figure 7-11 shows the Docking tab of the Options dialog box. These options determine how the various windows in the VBE behave. When a window is docked , it is fixed in place along one of the edges of the VBE window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big mess of windows that are very confusing. Generally, you'll find that the default settings work fine.

Figure 7-11: The Docking tab of the Options dialog box.

To dock a window, just drag it to the desired location. For example, you might want to dock the Project Explorer window to the left side of the screen. Just drag its title bar to the left, and you see an outline that shows it docked. Release the mouse and it is docked.

Note  

Docking windows in the VBE has always been a bit problematic . Often, you find that some windows simply refuse to be docked. I've found that if you persist long enough, the procedure will eventually work. Unfortunately, I don't have any secret window-docking techniques.