VBA Language Settings - MS Access

In this section, we discuss the different settings in the VBA environment that affect how the language operates. You can customize how the VBA Editor behaves as you write code by making adjustments in the Options dialog box. There are also some options that have a more subtle impact on your development. To access the Options dialog box, click Tools | Options, as shown in Figure.

VBA Editor Options are available from the Tools menu.

VBA Editor Options are available from the Tools menu.

From the perspective of writing code, one option that some developers choose to switch off is Auto Syntax Check. With this option enabled, the VBA Editor monitors the syntax of your code as you type. When it detects an error, it displays a warning message box. Some developers find that this slows them down, thus they switch off the option. But even when this option is disabled, syntax errors are still highlighted in colored code; only the message boxes are not shown. We would recommend that when starting out you leave this option active and later evaluate whether it helps or hinders your entering code.


Comments that you add either at the beginning of a line or after a code statement are indicated by a single quotation mark before the comment. The line will then appear colored green. To remove the comment, remove the single quote. Comments are used extensively in this book to document operations, but you can also use a comment quotation mark to disable a line of code. VBA also has a feature for defining blocks of code as comments without the need to repeatedly enter the single quotation mark on each line. To do this, first highlight the lines of code that you want to mark as a comment, right-click the Editor menu bar, and then select the Edit menu bar option. The new menu bar has two buttons: one for commenting, and the other for uncommenting blocks of code. Setting Option Explicit

You can use the VBA Editor Options dialog box shown in Figure to alter the behavior of the environment.

Selecting the Require Variable Declaration option will reduce programming errors.

Selecting the Require Variable Declaration option will reduce programming errors.

When Require Variable Declaration is selected and a new module is created, the Option Explicit directive is added at the top of a module (Option Compare Database is explained in the next section):

Looking in the modUtils module, the Option Explicit keyword at the top of the module is commented out (this simulates what happens when you do not have the Require Variable Declaration option selected when creating a module) to demonstrate how difficult it can be to spot errors when this option is not selected, as shown in Figure .

Because you are not forcing all variables to be declared, any typing mistakes are assumed to be referencing new variables.


Notice how the function can be tested from the Immediate window and always returns the incorrect value, 0. Looking at the Locals window, you can see that there are two currency rate variables, one called cr and the other cr1.

This is an error: cr1 was typed when it was supposed to be cr, but this is difficult to spot.

If you now uncomment the Option Explicit statement at the top of the module and try to run the code, a warning appears indicating that the variable cr is not defined. Then, after adding a Dim statement that declares the variable cr and trying again to run or compile the code, another warning displays, this time indicating that the variable cr1 has not been declared, as shown in Figure; this would allow the programming error to be spotted and corrected.

Uncommenting Option Explicit forces the declaration of the variable cr, which renders the cr1 typing error easily detected.

Uncommenting Option Explicit forces the declaration of the variable cr, which renders the cr1 typing error easily detected.

Once you have added Option Explicit to the module, rather than running the code to see the problem, you only need to select Debug |Compile from the menus to find any undefined variables.The combination of forcing variables to be declared and regularly compiling your code (which is explained in the next section) during development will help you avoid many programming errors.

Selecting Option Compare

In the previous section, you saw that the first line in any module is Option Compare Database. This line determines how VBA performs string comparisons, which are carried out character by character, reading from the left, as shown in the following:

Option Compare {Binary | Text | Database}

The following gives a brief description of each of the properties for the Option Compare declaration:

  • Binary Specifies that “AAA” is less than “aaa”, the letter ‘A’ comes before ‘a’ (case sensitive A...Z, then a...z).
  • Text Specifies that “AAA” = “aaa” when comparing a string (case insensitive).
  • Database This is unique to Access. It means use local settings for comparisons(behaves similar to the Text option for most systems).

Comparing “a” with “ab” will give the answer that “a” is less than “ab” (when all characters match, the shorter string is less than a longer string), and “abc” is less than “ac” (because b comes before c).

In modOption, we have provided a function called modOption _OrderWords. To test this procedure,comment out the appropriate Option Compare statement at the top of the module, and then type the examples shown in Figure into the Immediate window (the first result is shown with Option Compare Binary, the second with Option Compare Text).

The modOption _OrderWords function uses the Split string function to split apart a string into an array of words.

modOption _OrderWords function uses the Split string function to split apart a string into an array of words.

There is also a string function called StrComp that can be used to perform string comparisons without needing to change the Option Compare for the entire module, if you need to use different comparison techniques

Compiling Code

VBA is an interpreted computer language; this means that the program code is not preconverted into machine-dependent executable code. The code is written in a text form and then prepared in a tokenized form called p-code (once compiled) the p-code is processed for execution at runtime. This has the advantage of allowing great flexibility in debugging and altering code as it executes.

Compiling the code prepares the p-code prior to runtime, which means that the code will execute a little bit faster. But the biggest bonus is that it allows you to detect and correct errors at an early point during development.

One keyword, Option Explicit, performs a valuable task when you compile the code in your application. If you have Option Explicit in all your modules and you compile the code, it will stop at every instance where you have used a variable but have not declared the variable; this action is available on the Debug menu, as shown in Figure. If the option is grayed out, this means that all the code in the your database is compiled.

Regularly compiling your code ensures that errors are quickly identified.

Regularly compiling your code ensures that errors are quickly identified

Another option that you might consider is Compile On Demand (click Tools | Options (General)). If you leave this option selected, when you run a procedure in a module (the default is on), only that procedure is checked; other procedures will be checked as they are demand loaded (required to be used). If you had another procedure in the same module with an undeclared variable, this would not be detected. Clearing the option means that all the procedures in the current module are checked when you attempt to run any procedure in that module. Some developers prefer to not to use this option; however, if you regularly use the Debug | Compile option, then all code in the application is compiled anyhow, which renders the decision to enable or disable the Compile On Demand option as not important.

The following application property can be used to check whether an application is in a compiled state:


The /Decompile command line switch

Over a period of time, Access databases can bloat (which means the database size increases more than expected). Regularly compacting and repairing a database reduces the database bloat. There is an additional bloat that can occur in the VBA code, and one method for reducing this bloat is to decompile and then recompile the database. Advice as to when and whether you should regularly decompile a database varies, but a good rule-of-thumb is to only decompile if you believe you have significant bloat, or if the application is behaving in an unexpected way. This can assist in resolving an obscure problem.

To decompile a database, you need to open the database by using a link for Access that includes the /Decompile command line switch, as shown in the following:

"C:Program Files (x86)Microsoft OfficeOffice14MSACCESS.EXE" /Decompile

You can create a shortcut on the desktop to do this by browsing to the installed directory of Microsoft Office and right-clicking and dragging the MSACCESS.EXE program into an appropriate folder (selecting to create a shortcut). Next, right-click this new shortcut, select Properties, and then edit the Target property to include the /Decompile switch. Launching Access by using the shortcut then means that the first database that you open will automatically be decompiled. You can then compile, and then compact and repair the database.

Conditional Compilation

Keywords prefixed with a # symbol (#Const, #If, #Else, #End If) are called compiler directives. These special keywords allow you to include or exclude blocks of code based on a flag.

One use for these directives is if you are building an application that needs to run in different versions of Access and you want a simple method for setting a flag to ensure that code that is only available in a particular product version is compiled. This saves you from having to manually comment out code that might not be available in that particular product version.

An example of this is the RecordSet2 object, which handles attachment data and is not available in versions prior to Access 2007. In these earlier versions, you would need to use the older RecordSet object.

Another use for compiler directives is to switch on debugging statements to help when resolving code problems, as demonstrated in the following:


References inform Access about the core libraries of objects that will be used in the VBA project. With different versions of Access, you get different default references. This is because different versions of Access need different versions of the libraries (and also in part because Microsoft has changed default recommendations concerning which libraries you should be using; notably Data Access Objects (DAO), which is the default in older versions and newer versions after Access 2003, and ActiveX Data Objects (ADO), which is the default in Access 2000/2002, when the default was changed).

Later in this book, we will be adding new entries to the references when we work with external libraries, such as ADO, ActiveX, and the other Office products. In the VBA Code Editor, click Tools | References to see your current references. They should appear as shown in Figure .

The standard references that are added when you create a new database with Access 2010.

The standard references that are added when you create a new database with Access 2010.

References can be changed and alternative libraries can be loaded. You need to take care when doing this, but if you do make a mistake, then references can be added back in. If you save your .accdb file in an .mdb format (assuming you have no new product features that prevent you from doing this), then the references will be changed as older libraries are loaded, as is shown in Figure.

References when converting an .accdb to .mdb format or creating an .mdb file with Access 2010.

References when converting an .accdb to .mdb format or creating an .mdb file with Access 2010.

The references shown in Figure are now replaced with those shown in Figure, which means that the new Access database engine object library is replaced with the older DAO library, and an additional reference to Visual Basic for Applications Extensibility is added (which we would recommend that you consider removing by clearing the reference if moving the database to older environments)

If you move a database to an environment that does not have a required reference library, the library is tagged as missing. The easiest way to detect a missing library is to try to compile the code in the new environment. If you need to programmatically check references, the following code can be used:

In this example, the IsBroken property is used to detect a broken or missing reference. Missing or broken references can be a serious problem that you need to resolve.

Visual Basic for Applications Extensibility

Oddly, as of this writing, a reference to Visual Basic for Applications Extensibility is only added to a project when you convert an . accdb to an . mdb, yet it is only applicable on a computer that has Access 2010 installed; if the . mdb was transferred to a computer with an older version of Access, the reference would be missing.

It is a tribute to the flexibility of VBA that this library, which appears in the object browser as VBIDE, provides a programmable interface to the VBA integrated developer environment (IDE), as shown in Figure .

VBIDE references in the object browser.

VBIDE references in the object browser.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics