VBA Programming Series...7

Customizing the VBA Environment

If you’re serious about becoming an Excel programmer, you’ll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible (no, please keep your shoes on), the VBE provides quite a few customization options.

When the VBE is active, choose Tools➪Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. I discuss some of the most useful options in the sections that follow.

Using the Editor tab

Figure shows the options accessed by clicking the Editor tab of the Options dialog box. Use the options in the Editor tab to control how certain things work in the VBE.

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 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 usually keep this setting turned off because I find the dialog boxes annoy-ing and I can usually figure out what’s wrong with a statement. Before I was a VBA veteran, I found this assistance quite 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 you insert:

Option Explicit

Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use.

Auto List Members option

If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This bit of magic is sometimes called “IntelliSense.”

This is one of the best features of the VBE, and I always keep it turned on. Figure shows an example (which will make lots more sense when you start writing VBA code).

Auto Quick Info option

If the Auto Quick Info option is set, VBE displays information about functions and their arguments as you type. This can be very helpful. Figure shows this feature in action, telling me about the arguments for the MsgBox function.

Auto Data Tips option

If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code.

Auto Indent setting

The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. I’m big on using indentations in my code, so I keep this option on.

Use the Tab key to indent your code, not the spacebar. Also, you can use Shift+Tab to “unindent” a line of code. If you want to indent more than just one line, select all the lines you want to indent. Then press the Tab key.

The VBE’s Edit toolbar (which is hidden by default) contains two useful but-tons: Indent and Outdent. These buttons let you quickly indent or “unindent” a block of code. Select the code and click one of these buttons to change the block’s indenting.

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 with your mouse. I keep this option turned on, but I never use it. I prefer to copy and move by using the keyboard.

Default to Full Module View option

The Default to Full Module View option sets the default state for new mod-ules. (It doesn’t affect existing modules.) If set, procedures in the Code window appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time. I keep this option turned on.

Procedure Separator option

When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a Code window. I like the idea of separator bars, so I keep this option turned on.

Using the Editor Format tab

Figure shows the Editor Format tab of the Options dialog box. With this tab, you can customize the way the VBE looks.

Code Colors option

The Code Colors option lets you set the text color and background color displayed for various elements of VBA code. This is largely a matter of per-sonal 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 such as Courier New. In a fixed-width font, all characters are exactly the same width. This makes your code more readable because the characters are nicely aligned vertically, and you can easily distinguish multiple spaces (which is sometimes useful).

Size setting

The Size setting specifies the point size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and how many carrots you’ve been eating.

Margin Indicator Bar option

This 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 shows the options available under the General tab in the Options dialog box. In almost every case, the default settings are just fine.

The most important setting is Error Trapping. I strongly suggest that you use the Break On Unhandled Errors setting (which is the default). If you use a different setting, your error-handling code won’t work. 

If you’re really interested in these options, click the Help button for details

Using the Docking tab

Figure shows the Docking tab. 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 program window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.