FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:

Microsoft Access Developer Center

Table Design

Query Design

Form Design

Form Tips and Mistakes

Form Navigation Caption

Using a RecordsetClone

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys

Combo Box Top Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppressing Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Adding Buttons to the Quick Access Toolbar

Collapsing the Office Ribbon for more space

VBA Programming

Using Nz() to Handle Nulls

Avoiding Exits in the Body of a Procedure

Shortcut Debugging Keys

Setting Module Options

Math Rounding Issues

Rename a File or Folder

Source Code Library

Microsoft Access Modules Library

Royalty-Free VBA Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual Source Safe


Runtime Downloads

Simulating Runtime

Prevent Close Box

Disable Design Changes

Broken References

Missing Package & Deployment Wizard

Terminal Services and RemoteApp Deployment

Avoid Program Files Folder

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Microsoft Access Help

MS Access Developer Programming

More Microsoft Access Tips

Technical Papers

Microsoft Access Tools

Connect with Us

Email NewsletterEmail Newsletter Signup

FMS Development Team BlogDeveloper Team Blog

Facebook PageFacebook

Twitter with FMSTwitter

FMS Support SiteSupport Forum



Setting Microsoft Access VBA Module Options Properly

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

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
Option Compare Database
Option Private Module

We are actually presenting four important tips:

Tip 1: Require Variable Declaration

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 Options Require Variable Declaration
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.

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:

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.

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:

Microsoft IntelliSense with Access Library
IntelliSense showing the available functions from the Total Access Emailer runtime library

Microsoft Access ModulesAdditional Resources

Check out our Total Visual SourceBook code library with 225+ modules and classes you can add to your Microsoft Access, VBA, and VB6 projects royalty-free.


Contact Us  l   Web questions: Webmaster   l   Copyright © FMS, Inc., Vienna, Virginia
Celebrating our 28th Year of Software Excellence