Comprehensive Overview of VBA in Excel (Part 3)

While the default settings on Visual Basic Editor are sufficient and useful for most users, you can also fine-tune the interface and add some additional functionalities if desired.

In the article Exploring the Depths of VBA in Excel (Part 3) below, Mytour will demonstrate how to customize some options in VB Editor.

1. Customizing VB Editor

To customize the VB Editor environment, click on Tools in the menu bar and then select Options.

At this point, the Options dialog box will appear on the screen, displaying all the customizable options in the VB Editor. The Options dialog box has 4 tabs as shown below, each tab containing different customization options for the Visual Basic Editor.

1.1 Editor Tab

Default settings work quite well in most cases. If proficient in VBA, we can customize the VB Editor using some of these options.

- Auto Syntax Check:

When working with VBA in Excel, even if syntax errors occur, a pop-up window containing error descriptions will appear on the screen, as shown below:

Disabling this option will prevent pop-up windows from appearing even when syntax errors occur. However, the text color in the code will change to indicate errors.

- Require Variable Declaration:

Mytour strongly recommends activating this option. When working with VBA, variables are used to hold various data types and objects.

Activating this option will automatically insert the 'Option Explicit' command at the top corner of the code window. This forces us to declare all variables we're using in our code. If a variable is not declared and the code is executed, it will display an error message as shown below:

In the example above, we utilize the variable Var without declaring it, so when the code runs, it returns an error message.

This option is quite useful in cases where there are multiple variables.

Note: Activating this option does not affect current modules.

- Auto List Member:

This option proves useful if we intend to retrieve a list of method properties for an object.

For instance, if we wish to delete a worksheet (Sheet1), we must employ the line Sheet1.Delete.

Upon entering code, it will display all methods and properties associated with the Workbook object as depicted below:

The Auto List feature enables:

- Quickly select properties and methods from a list, saving time.

- Display all properties and methods that we might not be aware of.

- Avoid spelling errors.

- By default, this option is activated.

- Option: Auto Quick Info Options

When entering a function in an Excel spreadsheet, it will display some information about the function, such as required arguments.

Similarly, when entering a function in VBA, it will display some information as shown below. However, to ensure these details are displayed, make sure the Auto Quick Info option is activated (activated by default).

- Auto Data Tips Options:

When hovering over each line of code and placing the mouse pointer on a variable name, it will display the variable's values.

This option proves quite useful in case we want to debug code or locate lines with loops.

In the above example, when hovering over the 'var' variable, it displays the variable's value.

By default, this option is also activated.

- Auto Indentation:

As VBA code can be lengthy and messy, using indentation enhances code readability. When writing code, we can indent by using the Tab key.

This option ensures that when indenting, pressing Enter, the next line will be indented like the previous line.

In the example above, when writing Debug.Print line and pressing Enter, the next line will be indented like the first line.

If desired, you can modify the indentation value, or it can remain at the default value.

- Drag and Drop Text Editing:

When this option is activated, it allows us to select a block of code to drag and drop.

This helps us save a significant amount of time because there's no need for cutting and pasting, just selecting and dragging code snippets.

This option is also activated by default.

- Default to Full Module View:

When this option is activated, we can view all procedures within a module in one list.

If this option is disabled, we can only see one module at a time and must select the module we want to view from the menu at the top right corner of the code window.

This option is activated by default, Mytour recommends keeping the default settings.

- Procedure Separator:

If this option is activated, we will see a line between 2 procedures. By default, this option is also activated, Mytour recommends keeping the default settings.

1.2 Tab Editor Format

By utilizing options within the Editor Format tab, we can customize the code interface within the code window.

To customize settings, firstly, we select an option in the Code Colors box. Once the option is selected, we can modify the foreground, background, and indicator color for that option. Additionally, we can also set the font style and size in this tab.

Note: Size and font style settings will be retained for all code types, meaning all code types displayed in the Code Colors box.

When the Margin Indicator Bar option is activated, it will display a small margin bar to the left of the code. To set breakpoints, we simply click on the margin to the left of the code line we want to make a breakpoint.

By default, the Margin Indicator Bar option is activated.

1.3 Tab General

Tab General also includes numerous options, but we cannot modify these options.

Among them, the Error Handling option is crucial. By default, the Break on Unhandled Errors option is selected, Mytour recommends keeping this default setting.

This option means that if the code encounters an error and we haven't handled the error, the code will halt. However, if errors are handled (such as using the On Error Resume Next or On Error Goto options), the code won't crash and will continue running.

1.4 Tab Docking

In this tab, we can specify which window to dock. Docking means we can adjust the position of the window so it doesn't appear in surrounding positions, and we can view all different windows simultaneously.

Mytour recommends keeping these default settings unchanged.

In this article, Mytour has just guided you on how to customize the VB Editor. You can check out the article All About VBA in Excel Part 2 to see what topics are covered. Additionally, if you have any doubts or questions needing clarification, readers can leave their comments below the article for discussion.