VBA

When working in the Visual Basic for Applications Integrated Development Environment (VBIDE, or “VB Editor” for short), you can choose a number of settings to make your programming experience better. I’ll show the settings I use along with my rationale for choosing them.

First, go to the IDE’s Tools menu, and choose Options, to bring up this dialog. 

Two of these settings are must do‘s: Require Variable Declaration should be checked and Auto Syntax Check should be unchecked.

Require Variable Declaration automatically inserts the line “Option Explicit” at the top of each new module created in your projects. This line ensures that all variables are declared prior to being used in a procedure. This forces you to declare variables, and therefore to give some thought to how they are declared and used, and it prevents the use of undeclared variables.

One practical benefit of this behavior is that it prevents use of misspelled variable names. In the function below, the variable bTest is assigned a value of true, and presumably the returned value of MyFunction is supposed to be the value of bTest. However, in the command that assigns the value of MyFunction, the variable is misspelled as bTset. Since this variable has not been declared or used yet, MyFunction picks up the default value of a Boolean variable, which is False. You might spend hours checking the algorithm of your functions without realizing you have a misspelled variable name. It can be even harder to find if the variables are named bTest1 and bTestl (numeral one and lower case ell).

Since the Option Explicit declaration requires variables to be declared before being used, it will not allow the variable bTset to be used. When the function is compiled or run, the undeclared variable will be highlighted and a message will explain why: “Variable not defined”.

Auto Syntax Check pops up an error message any time there is a compile error in a newly entered line in a code module. In the example below, the line “If bTest” was typed, and the Enter key was pressed. Since this constitutes a compile error (If without Then), the erroneous line was highlighted red, and a message box is displayed.

This message box becomes tedious very quickly. The text is highlighted red, and remains red until the syntax is corrected, so you can tell there’s an error. The message box is redundant. Unchecking Auto Syntax Check removes this annoying message box, but the red highlighting of erroneous code is retained.

The rest of the settings I describe are my own preferences, but here they are for what it’s worth. The dialog at the top of this post shows a couple settings I use. First, I use a custom tab of 2 spaces instead of 4. This keeps highly indented code from extending too far to the right, so I can read it in a module without horizontal scrolling, and so I can print it out without line wrapping.

Second, I suppress the display of a procedure separator. Probably few people uncheck this setting, but the routine I use to print out code extracts the text to Microsoft Word, where it is formatted and given informative headers and footers in preparation for printing (maybe sometime I’ll publish this utility). The separator doesn’t make the export into Word, so I’ve gotten into the habit of using simple text lines as separators, which are included in the Word document. I use an apostrophe with a line of equal signs as a major separator, and an apostrophe with a line of dashes as a minor separator. The minor separator might separate related procedures, while the major separator denotes unrelated procedures.

The other options I typically set relate to docking of VBAIDE windows.

I uncheck docking for the Immediate Window and the Locals Window. If I used the Watch Window much I’d probably uncheck it as well. For me, the whole VB window isn’t large enough to have lots of windows docking along its edges and infringing on my module space. I’d rather switch between windows to access these particular windows. I also never maximize a module window within the IDE, because I like knowing what ther windows are open. This is different from my normal behavior in an application, which is to keep windows maximized unless I’m specifically comparing the contents of different documents.

I’m sure everyone has their own preferences and ways of interacting with their programming environment. Aside from the first two settings, everything else here is my own opinion and my own good or bad habits.