Option Statements

VBA has a few module level utility statements known as options that are used for naming conventions, string comparisons and other internal settings. First off, you may have already noticed the Option Compare Database statement located in the general declarations area of the VBE code window.

Per Microsoft Access Help, Option Compare Database is defined as "this statement results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur." This statement can be modified to either Option Compare Binary or Option Compare Text instead of Option Compare Database. If your VBE code module does not include an Option Compare statement, VBA will default to Option Compare Binary, which results in string comparisons based on a character's internal binary representation.

The next option statement, Option Explicit, is more important to beginning VBA programmers as it forces you to explicitly declare all variables before you can use them. This is a huge; I mean HUGE service to even seasoned VBA programmers. By forcing the explicit declaration of variables, you are saved an often painful process of misspelling or misrepresenting variables that ultimately lead to program or compile error.

Unless you tell Microsoft Access to make it so, the Option Explicit statement may not appear by default in your VBE code module. To have this statement provided in each of your code modules, simply access the Options window from the VBE Tools menu and select the Require Variable Declaration setting as demonstrated in Figure 2.8.

The next option clause is the Option Base statement, which is manually typed into the general declarations area of each code module. In a nutshell, the Option Base statement defines the lower bounds for arrays. VBA arrays are by default 0-based arrays, but can start at 1 using an Option Base statement as seen next.

Option Base 1

I discuss arrays and their upper and lower bounds in more detail in subsequent chapters.

Requiring variable declaration in the Options window.

0 0

Post a comment