Setting Microsoft Access VBA Module Options Properly

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 Declaration" 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 whenever you leave a line that's not complete.

You can manually go through each module (including code behind forms and reports) to verify the Option Explicit command is specified, then add it if it's missing.

For a more automated approach to ensure all the modules in your application have Option Explicit specified, use our Total Visual CodeTools product. One of the many options in its VBA Module Code Cleanup feature is adding "Option Explicit" to every module that lacks it. It can also standardize your indentations and 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.

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:

Option Compare Database

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:

Option Compare Text

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.

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