Run Time Embarrassments

Run-time errors are the most noticeable errors that your end users will encounter. Hopefully you uncover and correct all of these errors in development and testing. Run-time errors occur when your code encounters conditions that either you didn't expect to occur or you didn't think to handle. Examples of run-time errors might include the following:

♦ Dividing by zero

♦ Trying to open a nonexistent workbook

♦ Referring to a nonexistent worksheet

Run-time errors are harder to detect than syntax errors for two reasons. First, the code is flawless from the point of view of the compiler, so it can't automatically detect them. Second, you'll find it hard to envision every possible condition your program might encounter. Even if you could, where do you draw the line between what is likely to happen versus what is not?

This second point gets to the prime strategy for dealing with run-time errors—the best offense is a good defense. In order to prevent run-time errors, you need to consider what types of conditions are likely to occur and write your code to check at run-time for potential problems that would cause a run-time error.

Debugging Rule #3: The best offense against buggy programs is good defense. As an example of what I mean by a good defense, consider a common need in Excel programming— the need to refer to a worksheet by name. Let's say that when you developed your program, you assumed that you'd have a worksheet named Income Statement and throughout your code you had code snippets such as the following:

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Income Statement")

Now, if you haven't taken precautions to prevent your end users from changing the name of the Income Statement worksheet, this code is extremely risky. What happens if the name of the worksheet changes or is deleted? You got it—a run-time error.

In order to deal with situations like this, you need to write code defensively. In this case, you could write a function that makes sure that a worksheet named Income Statement exists before you explicitly refer to it. You could also employ a protection strategy on the Income Statement worksheet so that its name can't be changed. Finally, you could put some error-handling code in your procedure. Because this chapter is aimed more at correcting buggy programs, I'll not get into the details of these strategies at this point. Rest assured, however, once I get into covering the Excel object model in later chapters, I'll provide you with a significant number of code examples that will give you a nice inventory of procedures you can use for defensive programming purposes.

0 0

Post a comment