First Published 24 Nov 2023 Last Updated 26 Nov 2023
This article explains the various option statements that can be used at the start of each code module in the visual basic editor (VBE).
Much of this article applies to any Office program, but there are specific differences for Access.
Most Access developers will have the two lines, Option Compare Database & Option Explicit at the start of almost all code modules:
I will discuss each of these lines in turn and explain their purpose together with the other Option statements that are available.
1. Option Compare
The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a code module.
If a module doesn't include one of those Option Compare statements in any Office program, the default text comparison method is Binary.
When you create a new code module in an Access database, by default the line Option Compare Database will be automatically entered.
Although this is almost always the most appropriate choice, it is important to understand each of the options in order to use them appropriately:
a) Option Compare Binary
This results in string comparisons based on a sort order determined by the code page. e.g. 1252 = ANSI Latin 1; Western European (Windows)
A typical binary sort order is shown in the following example:
A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø
b) Option Compare Text
This results in string comparisons based on a case-insensitive text sort order determined by your system's locale (language and country/region)
When the same characters are sorted by using Option Compare Text, the following text sort order is produced:
(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)
c) Option Compare Database
Unlike the other Compare statements, this can only be used within Microsoft Access
This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.
For example, if you use Option Compare Database in your module, and your database has a French locale ID, then the string "école" will be equal to "ecole", and both will
be less than "étude" in a comparison.
NOTE: Only ONE Option Compare statement can be used in any one code module
2. Option Explicit
Option Explicit is NOT added by default. This is because, by default, VBA does NOT require variable declaration.
Leaving this unticked in the VBE Options is one of the most fundamental mistakes that Access developers can make.
If this is unticked, code will compile even if variables have not been defined or spelt correctly.
Any undeclared variables will be treated as a variant datatype and given the value Empty when first used.
However, the code may not work correctly and finding the reasons will be most difficult than it needs to be.
For example, there are two variable errors in the short section of code below. Can you find them?
Sub ListModulesInExtDb()
Dim strFilePath As String
Dim objAcc As Access.Application
'full file path here
strFilePath = "G:\MyFiles\ExampleDatabases\CurrencyExchangeTracker\CurrencyExchangeTracker32.accde"
Set objAcc = GetObject(stFilePath)
For Each obj In objAcc.CurrentProject.AllModules
Debug.Print obj.Name
Next
Set objAcc = Nothing
End Sub
Both errors will be picked up when you compile if Option Explicit is used.
Ticking the Require Variable Declaration option will cause all new code modules created from then on to add the line Option Explicit as shown above.
Doing this means that when code is compiled any undeclared variables will be identified allowing you to correct errors more quickly.
It will also pick up any variable spelling errors that have occurred.
However, you will still need to manually add the line to all EXISTING code modules.
For that reason, it is highly recommended that the Require Variable Declaration option is ticked when a new database is created.
Better still, that setting will 'stick' for all new databases created subsequently. It will also apply to all other Office applications such as Excel and Word.
Ticking the option really is a 'no brainer' as the saying goes!
Despite numerous requests over time, the Microsoft team responsible for VBA code across all Office applications have not updated the default setting to tick this option.
It seems very unlikely that situation will change in the future.
Even so, it might be possible for the Access team to prompt developers to check the option on first creating a code module where it has not been set.
A request for this to be done has recently been made.
3. Option Private Module
The Option Private statement is less widely known. It is used at module level to indicate that the entire module is private.
What does that actually mean in practice?
With Option Private Module, module-level parts not declared Private are available to other modules in the project, but not to other projects or applications.
This has another benefit in that it can be used to fully hide details of constants, variables and procedures in an ACCDE file.
When an ACCDE file is created, the project is compiled to what is known as 'p-code' and the module code is no longer available to view or edit.
Clicking on a code module in the VBE, will cause this message to be displayed
However, many developers may not be aware that a lot of information can still be seen including a list of procedures and values of any constants that have been defined.
To prevent this, you can add the line Option Private Module to any standard or class module. This is from an ACCDB file
This is the result using this statement when the file is compiled as an ACCDE
This action is NOT allowed in form / report code modules.
To ensure sensitive code does remains private, place it in a standard/class module and use Option Private Module.
NOTE:
a) Adding this line in an ACCDB file has no effect on visibility. The code remains visible to all (unless the VBA project is password protected)
b) The process of converting to ACCDE is normally 'one-way'. It is vital for the developer to save a copy of the ACCDB file in order to do any further development work.
There is a specialist company called EverythingAccess that is able to reverse engineer ACCDE files back to ACCDB on request. However:
• they require proof of ownership before agreeing to perform the conversion to ACCDB
• due to the nature of the task, it is likely to be expensive.
4. Option Base
This is the least commonly used statement. It is used at the module level to declare the default lower bound for array subscripts.
However, because the default lower bound array value (base) is 0, the Option Base statement is never required.
If used, the statement must appear in a module before any procedures.
Option Base can appear only once in a module and must precede array declarations that include dimensions.
In the example below, the Option Base statement overrides the default base array subscript value of 0.
Option Base 2 ' Set default array subscripts to 2.
Dim Lower
Dim MyArray(15), TwoDArray(3, 4) ' Declare array variables.
Dim ZeroArray(0 To 5) ' Override default base subscript.
' Use LBound function to test lower bounds of arrays.
Lower = LBound(MyArray) ' Returns 2.
Lower = LBound(TwoDArray, 2) ' Returns 2.
Lower = LBound(ZeroArray) ' Returns 0.
5. Further Reading
These Microsoft Help articles cover all the VBE Option statements:
Option Compare Statement
Option Explicit Statement
Option Private Statement
Option Base Statement
These MS Help articles may also be useful:
VBE Glossary
Code Pages
6. Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 26 Nov 2023
|
Return to Access Blog Page
|
Return to Top
|