Train users to follow best practices when writing VBA code

Visual Basic for Applications (VBA) lets you automate tasks and provide functionality that you can't get via the regular features in Microsoft Office. Susan Harkins discusses seven programming best practices that you should focus on when training users to write efficient VBA code.

If you train users to do a little coding on their own, they may not hit you up with as many development requests. I think that teaching users Visual Basic for Applications (VBA) is a good place to start because it's easy to learn. VBA lets you automate tasks and provide functionality that you can't get via the regular features in Microsoft Office.

Here are seven programming best practices that you should focus on when training users to write efficient VBA code.

Add an Option Explicit statement to each module

Even if you're a veteran developer, some typos will still slip through. The technical solution is to add an Option Explicit statement to every module.

A quick example will show users how Option Explicit forces VBA to check referenced variables against declared variables. When VBA finds an undeclared variable, it's very specific about the problem, as shown in Figure A. VBA highlights the variable and tells you exactly what's wrong. In this case, the referenced variable has a couple of transposed characters. Figure A

Figure A

Figure A: Option Explicit finds undeclared variables before they can cause a problem.
Without Option Explicit, users might find it impossible to identify the problem when the function fails to return the expected results; or, even worse, users might not realize there's a mistake. As you can see in Figure B, with Option Explicit commented out, VBA returns an erroneous value. In this case, the result is 0 because a Long variable defaults to 0. This kind of mistake is hard to find -- even for an experienced developer. Figure B

Figure B

Figure B: With Option Explicit disabled, an undeclared variable doesn't return an error, which you might expect.

Since Microsoft Office 97, Option Explicit is enabled by default. If the feature has been disabled, here's how to enable it:

  1. In the Visual Basic Editor (VBE), choose Options from the Tools menu.
  2. Click the Editor tab.
  3. Check the Require Variable Declaration option in the Code Settings section.
  4. Click OK.

Teach users to keep Option Explicit enabled, as well as how to use Option Explicit to prevent erroneous data and hard-to-debug typos.

Pick a naming convention and stick with it

A naming convention is a set of rules for naming objects and variables. Since this article is about VBA, I'll focus on the variables. Variable names should identify the data type and purpose.

Users may want to dismiss a naming convention as a nuisance and unimportant, but inform them that consistent naming practices make code easier to work with for the following reasons:

  • You don't have to memorize every little detail about the code because the variable's name exposes a great deal about it.
  • It makes it easier for others to decipher and modify their code.
  • It's easier to modify code months down the road because consistently named variables are self documenting.
  • It's easier to identify problems if they use meaningful variable names.

For example, a variable named Variable1 or value2 doesn't offer a hint at the variable's purpose. In contrast, FirstName, LastName, and StreetName are descriptive and meaningful names. Once the variable's purpose is apparent, denote the variable's data type. Most VBA developers prefer a three-character prefix, such as obj, str, lng, int, and so on. FirstName and LastName become strFirstName and strLastName. It might seem redundant in this case because names are string values, but encourage users to include the data type prefix. Notice that the final names use a mixture of upper and lower case, and each prominent word begins with an uppercase character -- this is known as camel style, which improves readability. You should also advise users to avoid using abbreviations, even if they seem obvious.

There are many naming conventions. If there are no corporate rules in place about naming conventions, users can create a simple rule of their own. The rules aren't as important as convincing users that they need to consistently apply a naming convention.

Microsoft offers more details about naming conventions and VBA prefixes.

Comment appropriately

Comments are invaluable when, months down the road, you forget why the code was written. For instance, this simple comment does a good job of defining the function's purpose:

' This function reduces product price by a predetermined discount. 

Without this comment, you would waste time trying to decipher the code.

The following guidelines will help users write efficient and meaningful comments:

  • Add a comment to the beginning of most procedures (both functions and subs) to explain their general purpose. The comment only needs to be a quick synopsis. If a procedure only has one or two lines, a comment usually isn't necessary.
  • Don't comment every statement in a procedure. Most functions and expressions are self documenting.
  • Add comments to explain decisions that defy general standards.
  • Use proper punctuation and grammar in comments. A phrase that seems clear to you might confuse someone else.
  • If a procedure is long, some people find it helpful to comment a series of steps when the code changes its purpose, but this isn't strictly necessary.
  • Add comments to document modifications and enhancements, including the modifier's name and the date modified.

Write easy-to-read code

One of the simplest ways to enhance your code's readability is to indent multi-line statements. You can space or use tab to indent. The VBE default tab comprises four spaces. To alter the amount of spaces in a tab, choose Options from the Tools menu and click the Editor tab (which is the default choice). Enter the appropriate number of characters in the Tab Width control, as shown in Figure C. Figure C

Figure C

Figure C: Determine the number of spaces when indenting code using a tab.

When to indent is mostly up to the author, but the following guidelines should help:

  • Indent paired statements such as If...End, Select Case, With, For...Next, and so on.
  • Indent related operations such as BeginTran...CommitTrans, AddNew, Update, and so on.
  • Indent the first and subsequent lines to isolate each function, making them easier to discern from other procedures in the same module.

The following cmdPrint sub procedure indents all the code to separate it from the function's stub lines and to distinguish the If statement's action clauses.

Private Sub cmdPrint_Click()

  'Open filtered report.

  Dim frm As Form

  Set frm = Forms!frmReconcilePayments

  If intfilterType = acApplyFilter Then

    DoCmd.OpenReport "rptOnFilterTest",

acViewPreview, , frm.Filter


    DoCmd.OpenReport "rptOnFiltertest", acViewPreview

  End If

End Sub

VBA doesn't require indents, but indents help distinguish the code's flow and make it more readable. Following the indents makes it easier to pinpoint specific pieces of code.

Another readability issue is finding a specific block of code among numerous lines of uninterrupted code. Inserting a blank line to separate tasks and changes in logic can help. Teach users to think of their code like a book. When the code seems to start a new paragraph, insert a blank line. In other words, add a blank line when the tasks or thought changes. If that's too difficult for users to grasp, teach them to insert a blank line before each comment. Initially, users won't use this tip much because their procedures will be short; as they progress, you may need to remind them.

Improve efficiency with constants

Users new to VBA aren't ready for constants. As they improve their skills, you should emphasize efficiency, and constants definitely fall into that category.

A constant stores a value that seldom changes. In fact, you can't programmatically change the value of a constant -- you must manually update the value. For that reason, the first lesson you must stress is not to declare and define a constant unless the value is stable.

Teach advanced users how to set stable values apart as a constant; that way, when and if the value needs updating, they have only one statement to modify -- the constant's declaration statement. For example, you might declare a generic discount value as an Integer data type, as follows:

Const conDiscount As Double = .01

Dropping the reference conDiscount into an expression or function is the same as hard coding the value .01. The best part is, users only have one statement to update when the discount amount changes.

Validate data with data types

Users familiar with Excel and Access probably know that a data type defines data by limiting it. For users who aren't as familiar with Excel and Access, a quick example will clarify what this means.

The simple procedure in Figure D shows what can happen when VBA tries to evaluate inappropriate data. Because the code passes Variant values, there's no way for VBA to determine if the passed values will work as expected. Figure E illustrates how using a numeric data type (Integer) prevents an error when a text value slips through the cracks. Users need to know how to handle this error when it occurs. Figure D

Figure D

Figure D: Variant data types leave data wide open for mistakes.
Figure E

Figure E

Figure E: Appropriate data types help reject inappropriate values before code tries to evaluate them.

In addition, teach users to choose the smallest data type that will get the job done. If you expect only Boolean (Bit or YesNo) values, use a Boolean data type and handle the error that occurs when a non-Boolean value slips through.

VBA's Variant data type accepts any value. A Variant data type should be used sparingly and only when users must accommodate unknown values. Keep in mind that VBA automatically converts data types when necessary using a set of internal rules -- there's no way to control how VBA converts a Variant unless you explicitly convert the data type.

Handle errors

After writing a few successful procedures, users might be ready to paint a big S for Super Coder on their shirts. Despite your users' confidence, their code will contain errors. Reassure them that bugless code is an admirable yet unachievable goal.

The three categories of errors are:

  • Design-time: These are the simplest to resolve. The VBE alerts you when it doesn't like something.
  • Logic: These insidious monsters give no warning and are much harder for users to find. The code runs -- it just doesn't get the job done.
  • Runtime: These occur when VBA can't process the code because something the code needs isn't as expected (e.g., a file that doesn't exist in the specified folder, there's no CD in the drive, and so on). Users can plan for and handle runtime errors, but they usually can't prevent them. When runtime errors occur, VBA displays an error number and description and then exits the procedure. Users can click Debug to open the module and study the statement that's causing the problem; this is helpful when debugging, but it's a nuisance during normal use. Show users how to include a generic error handling routine, as follows:
Sub|Function name()

  On Error GoTo errHandler

  Code that does something

  Exit Sub|Function


  MsgBox Err.Number & ": " & Err.Description,

vbOKOnly, "Error"

End Sub|Function

This routine does little more than VBA does on its own, but it does keep VBA from disrupting work. There are more complex error handling routines, but this is a good start for beginners. As users become more adept, show them how to test for errors and add appropriate code to the error handling routine.

Let 'em rip!

When you're teaching users VBA basics, be sure to educate them about good coding practices along the way. Not everyone will appreciate the advice and some will even ignore you, but the few who take the experience seriously, will thank you some day.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at You can reach her at