10 ways to screw up your VBA code

It's easy to get enticed into cutting corners when you write VBA code -- but that's likely to create problems down the road. Susan Harkins looks at some misconceptions that lead to bad practices, flawed code, and buggy apps.

Writing code can be a creative or mind-numbing task. But whether you love or hate your work, chances are you take shortcuts that you shouldn't. Most shortcuts violate at least one coding best practice rule. They almost all can lead to bugs or erroneous data. My advice: Don't take these shortcuts when writing VBA code!

Here are some misconceptions that lead people to take unfortunate shortcuts. Although a few of them apply specifically to the VBA language or IDE, most of them apply to writing code in general.

Note: This article is also available as a PDF download.

1: I don't need an Else clause

Several VBA statements, If...Then...Else, Select Case, and so on, include an Else clause. This clause follows all of the specific decision-making conditions and it's at the end for a reason. It's the last opportunity to do something. Many developers bypass this last chance thinking it's unnecessary. The conditions are so tightly specified that there's simply no remaining condition to handle. Of course, the logic might be sound, but you should always expect the unexpected.

Including an Else clause is easy and provides an extra layer of error trapping. You might display a generic error so that the user knows that an expected decision or action did not occur. Or you might log it and send an email to the administrator or in-house developer. Just don't let the event pass unnoted. Otherwise, the inaction will work its way into producing erroneous data that might be difficult, even impossible, to troubleshoot. An unexecuted Else clause is better than the alternative.

2: GoTo is a valid statement; I use it often

GoTo is a valid statement, but used incorrectly it produces hard-to-follow code and often hides errors and poor program design. Don't use GoTo as an easy out when you can't think of a better strategy. Do use itwhen you truly need a straightforward method for redirecting the flow. When you reach for GoTo, ask yourself this question: Is there any other way to handle this redirect? If there is — thinking loops — don't use GoTo. (I have never needed a GoTo in VBA development. Not one time.)

3: The compiler is a waste of time

Unlike other compilers, the VBA compiler doesn't produce a standalone module you can execute outside Office. Rather, the VBA compiler is actually a syntax checker. Compiling your code is a quick and easy way to catch syntax errors before you actually run the code. Why should you bother? The syntax checker often provides more insightful information about the error than VBA, so you can quickly fix the problem.

4: There won't be any errors to handle

Most developers aren't so arrogant as to really believe their code is perfect, but some do take error handling rather lightly. Error handling is just as important as your design and logic. Don't dismiss it. If anything, be heavy-handed when it comes to error-handling routines. An unhandled error usually means a phone call to you, because the application has ceased to work. Using appropriate error-handling routines, you can:

  • Share information with your users, including instructions for correcting the error immediately.
  • Help the application recover from the error immediately and silently; users will never even know.
  • Track the error so you can fix it.

5: My users will enter the right data

Depending on users to do anything other than crash your application will break your heart. That's not criticism aimed at users, either. Users aren't stupid, but it isn't their job to make sure everything works properly — that's your job. You can't depend on them to enter the right type of data. That's where validating data comes in. You can use table properties at the basic level, but most likely, you'll end up using a variety of VBA routines to make additional checks. It's probably the single most important task your program will complete, so don't skimp and don't depend on users to not make mistakes. Catch their errors and correct them using validating routines.

6: Naming conventions are for sissies

When you create a variable, it's a good idea to identify it by data type and purpose. Most VBA developers add a three-character prefix, or tag, to identify the data type. For instance, a string data type for storing last names might be strLastName. The prefix identifies the variable's data type, and LastName identifies the variable's purpose. Some developers find this additional tag an unnecessary nuisance, so they don't add them. In some cases, the data type is obvious — what else would LastName be but a string? Even though adding the tag does take just a wee bit of time, the benefits are worth it:

  • The tag is self-documenting.
  • When debugging or modifying code, you know the variable's data type instantly.
  • Months after the application is in production and you're long gone, the person maintaining the application will find tagged variables much easier to manage.

7: There won't be any null values

Null values have a way of mucking up the works, no matter what precautions you take. In truth, null values can be helpful if you handle them correctly. It all depends on your perspective. However, assuming you don't have to accommodate them because there won't be any is a disaster looking to happen. VBA provides several tools for finding and working with null values:

  • Use IsNull() to determine whether an expression or value is Null. You can't use comparisons, such as var = Null or var <> Null; direct comparisons always return Null (Transact-SQL sometimes returns False).
  • In Access, use Nz() to return a value other than Null when encountering Null.
  • Use the Variant data type if you need to work with Null variables; it's the only data type that can store Null.

8: I'm the only one using the app, so I embedded the password

Passwords and user id values should never be embedded (stored) in code. You might be the only person planned or authorized to use an application, but that doesn't mean a thing. Intentions are worthless. Even if an embedded password makes things easier for you — and even if users complain about having to enter information to log on — always prompt users (that includes you) for their user id and password via a dialog.

9: I tested it when I wrote it; everything's fine

Testing a procedure when you write it isn't enough. Developers are usually the worst testers. They simply don't think like users so they don't make the same kind of creative, often downright innovative, requests that users do. You have to put someone other than yourself into a practice production environment. Find a few people who know nothing about the app and dare them to break it. They will.

10: I don't document; I just write code

If you're the only person who will ever modify your application's code, you can probably skip the documentation part — after all, you're only punishing yourself. However, most developers want to know a little about the code they're modifying if they didn't write it themselves:

  • The routine's purpose/task/goal
  • A short definition of passed values and arguments (if any)
  • An explanation and perhaps even a bit of justification for odd decisions that break good practice rules
  • Who wrote the original code and when; who modified the code and when — so they can find you and pick your brain, see that you're fired, or see that you get a great big fat raise!


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks