FMS Site Search

Setting Microsoft Access VBA Module Options Properly

Option Settings in the Declarations Section of Modules and Classes

In the top declarations section of each module and class, VBA has options to specify the way MS Access/Office (and VB6) should behave. Even though these are
very important, most experienced VBA/VB6 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 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:

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