Excel 2003 VBA Programmer's Reference

7

Writing Bulletproof Code

In a sense, bulletproof code is like Superman and bulletproof vests. Superman is bulletproof unless he is in the presence of kryptonite. Bulletproof vests are bulletproof too unless someone shoots armor-piercing bullets (or interestingly, a compound bow) at it. Being bulletproof can be considered fictional and impossible and something practical, but with physical limits.

It is technically impossible with the current technology to write an algorithm that mathematically proves the efficacy of an application, and so, it is probably impossible to prove that any application is completely error free, or bulletproof. Yet Superman is believed to be the man of steel, undefeatable, and law enforcement officials prudently wear bulletproof vests. So, we too must persevere when we write code for our applications. In this chapter, we will demonstrate techniques that convey robustness and help protect your application from having a bad day—crashing, deleting files, or worse—and in the event something goes wrong you will be better prepared and able to diagnose and solve the problem. Additionally, these techniques are portable and reusable from application to application.

We will build on decades old, proven strategies for bulletproofing code using the basic tools built into VBA. Generally, you will learn (and have at your disposal) techniques for flagging assumptions that fail, tracing the actual course the CPU takes through your code, and leave “breadcrumbs” throughout ...