17 Steps to Better Microsoft Access VBA Code

This is not a tip, rather it is a requirement for writing VBA code. If you do not use the Option Explicit statement at the top of every module in your project, you are asking for trouble when you debug your application. By specifying Option Explicit, you tell VBA to force you to declare every variable used. This makes it easy to avoid program errors because of misspelled variable names. For example, the following procedure is in a module that doesn’t have the Option Explicit statement. Can you find the bug?

Sub ShowBugs ()
  Dim intX As Integer
  Dim intY As Integer
  Dim intZ As Integer

  intX = 1
  imtY = 2 ' Whoops! Here's a typo…
  intZ = 3

  Debug.Print intX + intY + intZ
End Sub

When you run this code, you would expect it to assign the values of 1, 2, and 3 to the intX, intY, and intZ variables, and then print the sum of the three which should be 6. However, this procedure prints the value of 4 because of a spelling error. Instead of setting intY to 2, the code is actually setting imtY (with an "m") to 2. Because Option Explicit is not declared, the first reference to the variable named imtY declares the variable automatically. This means that this code compiles just fine and doesn’t become a problem until it is actually tested. On the other hand, if Option Explicit is specified, the above code would generate a compile error, making it easy to track down the error before you go any further in your development efforts.

Finally, consider this: if the bug in the example were simple to spot, what happens when you have the same bug buried in hundreds of lines of code? Using Option Explicit is the single most important thing you can do to write better VBA code.

You can have Option Explicit automatically inserted into every module you create from the module editor, select Tools, Option and check the Require Variable Declarations box.

There are several guidelines to keep in mind when you declare your variables with the Dim statement. First, organize your Dim statements to keep them all together within a procedure, or with the declarations section of a module. By placing all Dim statements at the top of a procedure, you can get a quick inventory of variables that belong to a procedure. You an also perform quality-control on your variables by visually scanning the entire list at once—you don’t have to hunt and peck through the entire module.

Next, keep each Dim statement on its own line. Consider the following code:

Dim intX As Integer, intY, intZ As Integer

A quick glance at the code may lead you to believe that three integer variables are being defined. However, a closer look reveals that intY is not being declared as an integer (it’s actually a variant.) If each of these variables were declared on a separate line, it would be quickly apparent that intY was not type cast. By applying this rule to your code, you also avoid the simple Dim syndrome. Consider the following code:

Dim a, b, c As String

The intent of this code was to declare three variables as strings. But VBA doesn’t work this way: only c is declared as a string—the others are declared as variants. VBA requires you to explicitly declare each variable. Otherwise, it is created as a variant which is less efficient.

You should also know that VBA lets you specify types for constants using this type of syntax:

Const strCustomer As String = "Customer"

To make life easier for beginning developers, VBA offers the Variant data type. This data type is very flexible because it can represent almost any type of data, and VBA automatically handles all conversions from one type to another. But these strengths are also weaknesses. Because the variant type must be capable of holding any type of data, it adds storage overhead—it is always more efficient to store data using a specific data type than it is to use a variant. Also, since VBA does automatic conversions on variants, you are not in control—when conversions happen, you may see unexpected results.

Most of Microsoft’s Basic variants offer the quaint (and archaic) type declaration character. By putting one of these pre-defined characters at the end of a variable or function name, you define the variable’s type or the function’s return value. For example, the following line of code:

Dim CName$

declares a variable called CName as a string. The dollar sign ($) character at the end is a type declaration character, and Access knows that a dollar sign signifies a string variable. The problem is that type declaration characters are old-fashioned. They still exist in VBA today primarily for backward compatibility. Good VBA code should avoid type declaration characters because they are considered obsolete. Also, there are only type declaration characters for a small subset of the data types that VBA supports.

You should also avoid the Def… statements, such as DefInt A-Z. These constructs are also obsolete, and can lead to code that is difficult to debug and maintain.

All VBA variables and procedures are scoped to be available to different portions of the program. For example, a variable declared as Public in a standard Access module can be read or assigned from any procedure in any module in the database. This is the broadest possible scope. On the other hand, a variable declared as Private within a procedure or passed as a procedure parameter has the narrowest scope—it is only seen by the procedure. Between these two extremes, you also have module level scoping. When deciding on the scope of a variable, use the narrowest scope possible. If the variable only needs to be used by one procedure, make it private to that procedure. If the variable must be seen by many procedures in the same module, make it a module level variable. Only in rare occasions should you create Public variables. Code with narrow scoping throughout is much easier to debug and maintain. They are also much easier to move or copy.

There are times you need to convert data from one type to another. VBA makes this deceptively simple by often performing automatic data type conversions for you. But, this is not necessarily a good thing. Often, these automatic conversions can introduce changes in the data that you do not expect. This is especially true when converting numbers between integers and doubles. Also, the semantics and behavior of automatic data type conversion is often semi-documented or under-documented, and can change between versions of a language. For these reasons, you should use explicit VBA conversion functions in your code such as CInt, CDbl, CStr, etc. Relying on VBA to do automatic conversions can lead to bugs that are very hard to find.

One of the more important tips for variables is to explicitly declare a data type. The same rule holds true for functions. When you create a procedure as a function, you are implicitly telling VBA that the procedure returns a value. If you do not explicitly declare the return type of the function with the As keyword, VBA always casts the function’s return value into a variant. As with variables, this can lead to subtle conversion and logic bugs in your code.

It also prevents the code compilation from detecting incorrect variable assignments. For instance, if you assign the function to a variable of type string, this is a compile error if the function explicitly returns a number, but not a problem if nothing is specified. Your application will then crash when that line of code is encountered. Every function should have its return value explicitly set.

When an untrapped error occurs in your VBA application, a number of interesting and unwanted things can happen. First, a dialog displays the error message, usually with OK, Cancel and End buttons. If your application’s user is savvy enough to understand the cryptic message displayed, he or she is then unceremoniously dumped into the VBA source code at the line where the error occurred. Such behavior is hardly the hallmark of a professional application. To make matters worse, the source code may be secured, causing an even more cryptic error message to appear. Because of this, error handling is a crucial part of your application development efforts.

Every procedure, no matter how small or insignificant, should have some type of error handling. At the most rudimentary level, there should be an On Error Goto statement that points VBA to a label within the procedure. This label should contain code that, at a minimum, displays a meaningful error message.

You may also want to consider creating a procedure stack. A stack is simply a list of procedures, and the order they are executing in. This list is useful when your application encounters an unanticipated error. When such an error occurs, you can inspect the procedure stack, and see exactly which procedures were executing, and in which order. A stack is typically implemented with an array. Initialize the array when your application starts. Then, at the beginning of each procedure, place the procedure’s name as the last element of the array, moving all other array elements up by one. Just before the procedure exits, it should remove its name from the array. Although this approach requires extra coding effort, it can be well worth it when trying to debug applications when the application and user are at a remote site.

Finally, Access supports the Erl function. This function returns the line number of the line where the last error occurred. You can use this function as part of your error handling routines to report exactly where the error occurred. This is extremely useful and can often eliminate the need to get information from the user when you have the error message and exact line number.

Unfortunately, this powerful technique only works if you've added line numbers to your code. One of the features in our Total Visual CodeTools product does this. It can add line numbers to all your code (along with error handling to procedures that lack it), so you can have a more robust application.

For more information on this topic, visit Pinpointing the Exact Line Where A Crash Occurs.

VBA provides many constructs for controlling the flow of your program. Unfortunately, some of these constructs are vestiges of the early BASIC days when programs were non-modular linear lists with required line numbers. These constructs, namely Goto, Gosub and Return, have little validity in modern VBA programs. They often lead to messy jumps and difficult to understand logic. If you want to write well structured code, you should never use the Gosub…Return statements, and only use the Goto statement for error handling.

Equally important, no procedure should ever have more than one exit point (Exit Sub or Exit Function commands.) If a procedure can exit from more than one place, logic errors, unclosed objects, and mishandled errors can occur. Finally, consider adopting the convention where every one of your procedures has a specific error label and a specific exit label.

Values that are hard-coded into program code make that program code difficult to understand and difficult to update. Consider the following code:

Dim intCost As Integer
Dim intQuantity As Integer

intCost = intQuantity * 1.712

What does this code do? That’s obvious: it multiplies a quantity by 1.712 to get the cost. But what is 1.712? These types of magic numbers offer no information about what is being accomplished, and make the program difficult to decipher. If the number needs to be changed, it may also be very difficult to find. It is much better to declare a constant for the number.

Next, consider the following code:

If Err.Number = 2048 Then
  MsgBox "Error 2048 occurred", 16, "Acme Application"
Else
  MsgBox "Error 2048 didn't occur", 16, "Acmo Application"
Endif

In this example, hard-coded values cause two problems. The first is caused by the hard-coded number 16. What does 16 represent? What type of icon is displayed by the MsgBox function? Second, because "Acme Application" is hard-coded twice, it is easy to introduce typos, such as the one shown in the second MsgBox statement. Finally, by hard-coding literal strings throughout your program, you make it very hard to easily make global changes.

You can avoid these problems by replacing hard-coded values with centralized constants or variables, or by placing values in a table. When your application starts, it can read all text values and magic numbers from the table into memory, making them readily available to your program. When you remove all hard-coded values, you reduce the potential for errors, and make the program more maintainable. This last point can be crucial if you need to face the issue of internationalization. When you need to translate your application to another language, you will really appreciate code that has all literals and text stored in a central location.

Although the relative merits of different naming conventions can cause heated arguments among developers, the use of naming conventions is generally accepted as a good programming practice. Naming conventions add information to variable, object, and procedure names, typically using some type of prefix or suffix notation. This type of notation requires you to add several characters to the beginning or end of a variable name to identify its type. For example, each string variable you create would be prefixed with the characters "str", identifying those variables as strings. There are many different naming conventions for VBA, split mainly between Visual Basic and Access developers. Beyond code, people also use naming conventions for Access objects (e.g. tables begin with "tbl", queries "qry", forms "frm", command buttons "cmd", etc.).

The naming convention you choose is not as important as your commitment to use the convention consistently and constantly. Naming conventions don’t work well unless you use them throughout your code, so pick a convention and stick to it.

It may seem obvious that as a developer you should choose variable and procedure names that convey that object’s purpose. But often, in the heat of programming, it is all to easy to resort to cryptic one-letter names like x and y. For readability, you should avoid such a temptation. Generally, one letter variable names are reserved for throwaway variables such as loop counters; beyond that, you should avoid informationless names.

Unfortunately, object naming is often at odds with the goal of making code compact—for documentation purposes, you want names that are long enough to adequately describe the variable or procedure. However, you don’t want names that are so long that they make your code unreadable or difficult to type. Somewhere between the two extremes lies a happy medium: use names that are long enough to convey their purpose, but not so long that code becomes unmanageable.

If you have ever tried to decipher another developer’s code (or your old code), regardless of language, you are no doubt aware of the value of proper commenting. Many times, the purpose of a given piece of code is not readily apparent by reading the code itself. Comments go a long way to providing the information that makes the process of understanding, enhancing, and debugging code much easier.

There are various levels of commenting in VBA coding. These levels closely follow the levels of scoping:

Application (global) comments

Comments at this level explain the flow of the entire application and cover how different components and modules interact. Typically, these comments are kept outside of the application’s project database, but can be included in a global module as comment text.

Module level comments

Module level comments explain the purpose and contents of a module. Information typically includes a list of the procedures in the module, their purpose and relationship, and revision comments for the module as a whole.

Procedure level comments

Comments for each procedure typically include a brief description of what the procedure does, a definition of its parameters and return value, revision information, and any special situations it requires.

Line level comments

Comments on or above a specific line of code explain the purpose of a variable, or a particular sequence of operations. Typically, it is best to include line level comments on the line immediately preceding the line of code. Comments on the same line as the code itself can be difficult to maintain and make code harder to read.

Control structures should be indented to improve readability and reduce programming errors. Paired structures such as If..End If, Do..Loop, For..Next, With..End With, While..Wend, Select Case .. End Select, Type..End Type, etc. should all use indenting to clearly show where they begin and end. Also, you should choose a tab width setting and stick with it—there is nothing worse than trying to work with an application that has several different tab width settings.

Fixing indentations can be a tedious task. One of the features of our Total Visual CodeTools product is the automated cleanup of your indentations. Visit Sample VBA/VB6 Code Formatting and Standardization for more information.

By default, Access uses four spaces, but if your code has lots of nesting, two spaces may work better for you. This can be set from the VBA IDE under the Tools, Options menu selection and checking the Auto Indent box.

Indentation can also aid the visual grouping of related operations, not just control structures. For example, consider using indentation to group AddNew…Update operations, Set..Close, and BeginTrans…CommitTrans operations. This makes it easier to see when the code enters and exits these "paired" operations.

Placing an If condition and its action on the same line leads to code that is difficult to read and to maintain. Instead of this:

If fInitialized Then DoCmd.OpenForm "frmOpen"

do this:

If fInitialized Then
  DoCmd.OpenForm "frmOpen"
End If

The Select Case construct makes it easy for you program to branch based on multiple possible values for a variable or expression. There are two things to be aware of when using Select Case. The first is a matter of style. Avoid putting an action on the same line as a Case statement. The following code samples illustrate this.

Bad Style which is difficult to read and maintain:

Select Case intType
  Case acTable : strType = "Table"
  Case acQuery: strType = "Query"
End Select

A Better Way which is easier to read and expand in the future:

Select Case intType
  Case acTable
    strType = "Table"
  Case acQuery
    strType = "Query"
End Select

In the first example, it is difficult to quickly see which is the condition and which is the action. Also, when you come back later to add code to the action clause, you end up having to format it correctly anyway—the multiple actions won't fit on a single line anymore! Save yourself future problems by formatting Select Case blocks correctly at the outset.

Here's another tip that can save hours of frustration as you try to debug a complex application. Unless you always have a Case Else clause in your Select Case blocks, unanticipated values will not be acted upon.

For example, assume your application allows the user to add new employee categories. Your application is also has VBA code that gives each employee a raise at the end of the year based on their job type. Lets assume that the user adds "Computer Programmer" as a category and a few people are hired that year as Computer Programmers. Finally, this is what your Select Case statement to handle raises looks like:

Select Case intEmployeeType
  Case EmpType_Manager
    intRaise =10
  Case EmpType_Clerical
    intRaise = 5
  Case EmpType_Driver
    intRaise = 2
End Select

Guess who isn't getting a raise this year? If you follow the practice of always adding a Case Else clause, problems like the one shown above are easier to handle. For example, the above code could be re-written to prompt the user for the raise amount in the case of new employee types:

Select Case intEmployeeType
  Case EmpType_Manager
    intRaise =10
  Case EmpType_Clerical
    intRaise = 5
  Case EmpType_Driver
    intRaise = 2
  Case Else
    Beep
    intRaise = InputBox("Please enter raise amount.")
End Select

At first blush, the Stop statement appears to be an ideal candidate for debugging. You insert the Stop keyword into your code at the required place, run it, and VBA obliges by halting execution when it gets to that line. The problem is that you may not always remember to remove all Stop statements before your code enters production use. There is nothing more embarrassing than seeing your code open up at a Stop statement while a user is running your application—all because you forgot to remove the one innocuous statement. A better alternative is to use breakpoints. These perform the same function as the Stop statement, but are not saved with the code. When you exit Access, the breakpoints are discarded, ensuring that your code will not stop unexpectedly.

Writing robust, maintainable bug-free programming code is a difficult task. Computer languages are inherently difficult to use, and make it much to easy for bugs to appear. However, with a disciplined approach and a good set of guidelines, your efforts will be rewarded with programs that work, and work right.

Total Access Emailer 2022
Email Everyone in Your Access List!

Total Visual Agent 2021
Automate Access Database Chores!

Updated Microsoft Access to SQL Server Upsizing Center with whitepapers, resources, and SQL Server Express Downloads