Setting VBA Module Options Properly

Provided by: Luke Chung, President of FMS, Inc.

In the top declarations section of each module and class, VBA has options to specify the way Access should behave. Even though these are very important, most experienced VBA developers use the default options and rarely think about them.

However, if you are taking over or debugging someone else’s code, their modules may not have these settings, which can lead to unnecessary confusion when the code doesn't behave the way you expect. So it’s important to know them, what they do, and how to best use them:

Option Explicit
Option Compare Database
Option Private Module

We are actually presenting four important tips:

Tip 1: Make sure Option Explicit is automatically added to new modules

Option Explicit is the most important of these. Option Explicit requires all variables and constants to be declared (defined) before using them. Variables can be declared through Dim and Static statements in procedures, and the Global and Private commands in the Declarations section. By requiring these variable declarations, VBA can catch mistakes such as typos when you compile as opposed to when the broken code is run. Being able to generate compile time errors rather than runtime errors is a fundamental part of writing better code.

Option Explicit is automatically added to your code if you have Variable Declarations turned on. By default, Access/Office ships with this option turned off, so be sure to turn it on. To set it, from the VBA IDE, go under the Tools, Options menu and its Editor tab:

VBA Tools, Options menu: Require Variable Declaration Should be Checked

Make sure the Require Variable setting is checked. When this is checked, every time you create a new module or class, the “Option Explicit” line is included.

Additional Tip

You should also turn off the Auto Syntax Check option so the editor doesn't prompt and stop you when you leave a line that's not complete.

Tip 2: Verify and add Option Explicit to existing modules that lack it

To make sure that all the modules in your application, including code behind forms and reports, has Option Explicit specified, use Total Visual CodeTools. One of the many options in its Code Cleanup feature is adding "Option Explicit" to modules that lack it. You can also add your custom error handling code to procedures that lack error handling.

As part of its detailed code analysis, our Total Access Analyzer detects and flags modules without Option Explicit so you can address it. This is just one of 300 Best Practices detected by Total Access Analyzer.

Tip 3: Make sure Option Compare is set correctly

Option Compare specifies how string comparisons are evaluated in the module such as case sensitive vs. insensitive comparisons (e.g. should "A" = "a" be True or False?).

By default, Access/VBA uses:

This is a case insensitive comparison and respects the sort order of the database. In VB, which doesn't have the Database option, it's the same as the Text option:

That means, "A" = "a", which are both less than "B".

For exact (case sensitive) comparisons, so "A" is not the same as "a", use:

Option Compare Binary

If you are debugging code and confused because you can't understand seemingly valid text comparison failing when it works in another module, be sure to check the module's Option Compare setting. For instance, if strValue below is "YES", the evaluation below differs based on the Option Compare setting:

If strValue = "Yes" Then

In general, you should use the default Option Compare Database for your Access VBA code. If you need to make a case insensitive comparison, use the StrComp function with the vbBinaryCompare option:

StrComp(string1, string2, vbBinaryCompare)

That way you can move the code into any module and always have case sensitive comparisons without worrying about the Option Compare setting.

Tip 4: Use Option Private Module for library databases

If you create library databases so you can call the code from another database, consider using the Option Private Module command. This option lets you hide modules from external users and makes it easier to see your exposed function calls, which reduces the list of options when you use the built-in Microsoft IntelliSense. With this command, all procedures in its module are not exposed by the library. The procedures can still be public within the library and called by other modules. They are simply not listed outside it.

For example, in our Total Access Emailer and Total Access Statistics runtime libraries, we allow developers to call our routines to launch email blasts or statistical calculations. Since we have lots of modules each with many public functions, it would be overwhelming and confusing to filter through all those procedures just to find the ones you need. By using Option Private Module for all our modules with the exception of one, we can expose just our public functions. And only those functions appear when referencing our library:

IntelliSense showing the available functions from the Total Access Emailer runtime library