Five Ways to Turn Runtime Errors into Compile Errors in VBA

Mike Wolfe -

Compile errors are better than runtime errors.

Some Bugs are Better than Others
Not all bugs are created equal. Avoid the expensive ones by making more of the ones that are easy to find and fix.
No Longer SetMike Wolfe

That's all well and good, but it leads to an obvious follow up question.  How does one turn a runtime error into a compile error?

Here is a list of techniques you can use to turn a runtime error into a compile error:

  1. Use Option Explicit
  2. Avoid Variant and Object types
  3. Use constants in place of literals
  4. Avoid "stringly-typed" code
  5. Use early binding rather than late binding

Use Option Explicit

When you add Option Explicit to the top of your code module, the compiler will require that you declare every variable before using it.  This is a Good Thing™ because it means that you can worry less about typos when naming and using your variables.  

Why You Should Always Use Option Explicit in VBA
Don’t let typos and logic errors ruin your VBA code. Read our latest blog post to learn about the importance of Option Explicit and how to use it.
No Longer SetMike Wolfe

Avoid Variant and Object Types

These "catch-all" data types tell you nothing about the values that they contain.  They also tell the compiler nothing about the values they contain.  And that means the compiler can't perform type checking to help keep you from shooting yourself in the foot.

Strongly Typed Collections in VBA
Get better type safety and access to IntelliSense inside For Each loops by using “strongly-typed collections” in place of the VBA Collection type.
No Longer SetMike Wolfe

Use Constants in Place of Literals

Not only do constants help you write self-documenting code, they also help you avoid errors from typos in your code.

How to Construct a Bit Mask in VBA
What’s the safest way to construct a bit mask in VBA? Your preferred approach may be more dangerous than you realize.
No Longer SetMike Wolfe
Pay particular attention to the sections on magic numbers and the "Addition of Constants."

Avoid "Stringly-Typed" Code

Scott Hanselman discussed this topic in a blog post several years ago:

When you are using a strongly typed language but instead your types are stringly typed, you are passing strings around when a better type exists.

Here's an example of how you can turn "stringly-typed" OpenArgs values into strongly-typed values using Data Transfer Objects in VBA:

“Complex” DTOs in VBA
Can you use the OpenArgs parameter to pass multiple values to forms and reports with compile-time checking? You can if you use DTOs.
No Longer SetMike Wolfe

Use Early Binding rather than Late Binding

The standard advice when it comes to early and late binding is that you should develop using early binding but deploy using late binding.  In fact, I provide some of that advice myself!  However, as I list at the bottom of the linked article, there are some real disadvantages to using late binding.

When you rely on late binding in your VBA code, you give up four big advantages:
• Type safety
• IntelliSense
• Named constants
• Event handling

My existing advice still holds for automating Microsoft Office products (to avoid type library version compatibility issues with Excel, for instance).  That said, I have started deploying apps with early binding left in place for very stable libraries that I know will be available on the end users' computers, including:

  • Microsoft Scripting Runtime
  • Microsoft VBScript Regular Expressions 5.5