Five Ways to Turn Runtime Errors into Compile Errors in VBA
Compile errors are cheaper and easier to fix than runtime errors. Here are five ways to turn potential runtime errors into compile errors.
Compile errors are better than runtime errors.
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:
- Use constants in place of literals
- Avoid "stringly-typed" code
- 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.
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.
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.
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:
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
• 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