17 Steps to Better VBA Code
by Dan Haught, Vice President of Product Development
September 11, 1997
Introduction
Writing code is easy. Writing good code is hard. So what is good code
anyway? Generally, we think of the following attributes:
- The code works (no bugs)
- The code is documented
- The code is maintainable
- The code runs quickly
Unfortunately, meeting these goals is often time-consuming and
difficult. To help with this process, we at FMS have compiled a list of
things that we use on a daily basis—tips and techniques that make it
easier for us (and you) to write code that comes closer the lofty goal
of defect-free maintainable code.
Coding style and practices are a heated topic among developers,
approaching an almost religious fervor. The contents of this article
should not be construed as the final word—rather, it is a exposition of
the standards we at FMS have found useful in the job of writing code.
There are guidelines contained here that you may not agree with, which
is fine; don't follow them. However, it is important to understand the
concepts involved, so let’s dive right in.
- Use Option Explicit
- Declare Variables
Correctly
- Avoid Variants
- Don't Use
Type Declaration Characters
- Use Narrow Variable
Scoping
- Convert Data Types
Explicitly
-
Explicitly Type Cast Function Return Values
- Implement Robust
Error Handling
- Pay Attention to
Program Control
- Use
Constants To Avoid Hard-coded Values
- Use Variable
Naming Conventions
- Choose
Meaningful Variable Names
- Add Comments
- Use Standard Indentation
- Avoid Single-Line
If Constructs
- Use Select Case
Correctly
- Don’t Use
Stop Statements for Debugging
- Conclusion
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.
Copyright © 1998, FMS Inc. All rights
reserved. This information may not be republished, reprinted or
retransmitted in any form without the express written permission of FMS
Inc. The information provided in this document is provided "as is"
without warranty of any kind.
Back to Main Technical Papers Page
|
