Five tips for writing better VBA code

You can produce efficient, reliable, and easy-to-maintain code by following a few best practices. Susan Harkins reviews some essential aspects of good VBA code writing.

Writing dependable and efficient code is the goal of every competent developer. You want code that performs well and is easy to maintain. Most VBA developers have a bag of tricks or best practices they employ to reach that goal. Here are five of my favorites.

1: Use the most appropriate data type

Variable data types are a 101 topic — part of the basic foundation of good coding. It's also an area where some developers cut corners: They don't really matter anymore, so I'll make everything a Variant. Whether from ignorance or a lack of initiative, this decision couldn't be more wrong because:

  • VBA automatically converts data types following an internal set of rules you can't control. That means you can't guarantee the converted data type will be what you need.
  • Variants consume more storage than data types that are more specific.
  • Variants don't reject invalid data — there is no such thing to a Variant.

It's true that declaring data types to conserve resources isn't as important as it once was, but data types are bigger than the memory they consume. They also ensure the validity of your data. For instance, an Integer data type will accept only a valid integer value. If you pass a decimal or an alpha character, VBA will reject it. That's the first step to protecting the validity of your data. Specifying a data type won't eliminate all errors, but it will ensure that the defined value is the right type.

2: Use constants

A constant stores a value that seldom or never changes. You can't change the value of a constant while code is executing; you can change it only during design time. That protects the validity of the value. In addition, constants are easier to maintain, even if the value occurs only a few times throughout your code — you know exactly where to go to update the value. For instance, if you refer to a discount value of 10 percent throughout a dozen or more procedures, you might create the following constant:

Const conDiscount As Single = 0.1

Then, when the discount value changes, you have only one update to make. You open the module and change the Const statement. That's it. And it's certainly easier than finding and updating several variables throughout your modules. What if you miss one? Use constants to protect and maintain stable values.

3: Comment your code

Commenting your code won't make it run any better, but it will make maintaining it much easier. A simple, grammatically correct comment can mean the difference between a quick and easy update and a hair-pulling Why did I do that???? update. When you're writing the code, all the factors are current in your mind. Months later, you won't recall the small detail that sent you in a particular direction.

Comments only seem like a nuisance... until you need a reminder. For competent commenting, follow these simple guidelines:

  • Add a descriptive and meaningful, but brief, comment to the beginning of each procedure. (Be reasonable though, if a procedure is short and self-documenting, it probably doesn't need a comment.)
  • Don't try to explain every single statement, as many are self-documenting.
  • Include comments to explain decisions that aren't apparent.
  • Include comments that identify and explain anomalies.
  • Use complete, grammatically correct sentences and punctuation.

4: Control the flow

Some VBA statements, such as Select Case and If...Then...Else, make decisions. When writing these types of statements, be sure to control all the possibilities, not just the ones you anticipate. For instance, the following Select Case example allows for three possibilities:

Select Case val
  Case 1
  Case 2
  Case 3
End Select

What happens if val equals 4, 10, or xyz? Nothing happens. Depending on your code, that could be (and most likely will be) a big problem. To avoid loss of control, always use the Else component as follows:

Select Case val
  Case 1
  Case 2
  Case 3
  Case Else
    MsgBox "Can't evaluate the current value:  " & val, vbOkOnly
End Select

In this case, the message displayed is just a generic response so you can follow the flow. You'll probably want to exert a bit more control or offer more help. Of course, you could avoid the problem altogether by ensuring that only the values 1, 2, and 3 are passed to val, but don't bypass the Else component, regardless. A bit of extra protection can't hurt, and including Else produces the most comprehensive solution.

5: Keep it simple

When writing code, less is often more. In other words, keep your code as simple as possible. It will be easier to write and maintain. VBA's If...Then...Else statement is a good example of this strategy. This statement evaluates a value or expression and responds accordingly. If you're evaluating a single-statement for a simple true or false condition, use the simpler IIF() function instead. The following If...Then...Else statement illustrates this point — it's just overkill for the job at hand:

If val = 1 Then
  i = 1
  i = 0
End if

Using IIF() for such a simple comparison is more efficient, easier to write, and certainly easier to maintain:

i = IIF(val = 1, 1, 0)
If you need to evaluate only one statement, IIF() will probably be adequate. Always rely on the simplest solution possible.


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