How to Customize the VBA Environment through the Editor Tab

By Michael Alexander

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, VBE provides quite a few customization options.

When VBE is active, choose Tools→Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. Take a moment to explore some of the options found on each tab.

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

The Editor tab in the Options dialog box.

The Editor tab in the Options dialog box.

The Auto Syntax Check option

The Auto Syntax Check setting determines whether 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 dialog boxes popping up on your screen.

The Require Variable Declaration option

If the Require Variable Declaration option is set, the VBE will insert an Option Explicit statement at the beginning of each new VBA module you add. When the Option Explicit statement appears in your module, you must explicitly define each variable you use.

The 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 feature is one of the best in VBE.

The Auto Quick Info option

If the Auto Quick Info option is selected, VBE displays information about functions and their arguments as you type. This behavior is similar to the way Excel lists the arguments for a function as you start typing a new ­formula.

The 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. This option is turned on by default and is often quite useful. There is no reason to turn off this option.

The Auto Indent setting

The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the preceding line. Most Excel developers are keen on using indentations in their code, so this option is typically kept on.

By the way, use the Tab key, not the spacebar, to indent your code. Also, you can press Shift+Tab to outdent a line of code. If you want to indent more than just one line, select all lines you want to indent and then press the Tab key.

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

The 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.

The Default to Full Module View option

The Default to Full Module View option sets the default state for new ­modules. (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.

The Procedure Separator option

When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a code window. Separator bars provide a nice visual line between procedures, making it easy to see where one piece of code ends and another starts.