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 your code ensuring that every path, branch, and loop your code takes has been tested. The result is that fewer things will go wrong, and when they ultimately do, you will have all of the information at your disposal necessary to quickly diagnose and solve the problem, and get back to work.

Using Debug.Print

The Debug object contains a couple of methods. The first stone in our foundation of testing and debugging tools is the Debug.Print method. The other, Debug Assert, will be discussed in the next section.

Simplicity itself, Debug.Print accepts a string message and sends this string message to the Immediate window. In addition, the Debug.Print method only performs its little dance when you are running the code in the debug mode (with the VBE open). This makes the Debug.Print statement ideally suited for tracing your code's actual progress, instead of supposed progress.

Like the inimitable Ronald Reagan said, “Trust, but verify.” We are smart programmers and we trust that our code will wind its way in the order we meant it to go, but code has a relentless desire to go where we tell it to go. Debug.Print will take the guesswork out of where our code has been.

We will be building a modest toolset around Debug.Print. You can place Debug.Print statements in any property or method, and such a statement might look like this:

Debug.Print “You can leave your hat on”

However, a more practical use would be to display the name of the object and method and some useful text indicating what transpired immediately before or after the statement was executed. Here is a method that is defined in an imaginary worksheet named sheet1 and a useful Debug.Print statement:

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  Debug.Print “Entered Sheetl.CalculateFuelConsumped”

  CalculateFuelConsumed = Duration * GallonsPerHour

  Debug.Print “Exiting Sheet1.CalculateFuelConsumped, result = “ & _
    CalculateFuelConsumed & “ gallons”

End Function

The preceding method calculates fuel consumed as a product of duration and fuel consumed per hour. The Debug.Print statement as used in the preceding function sends the output shown in Figure 7-1 to the Immediate window.

Figure 7-1

Keep in mind as we progress that the Debug object only performs its dance when debugging in the VBE. As a result we never need to remove our debug code, and users never know it is there. The benefit here is that the debug code is ready to leap into action the moment the code stumbles on some difficulty, rather than we, beleaguered programmers, having to add and remove debug code between debug cycles or periods of maintenance and growth.

We'll come back to the Debug.Print statement in the section titled Tracing Errors.

Using Debug.Assert

The only other method of the Debug object is the Assert method. Assert acts as the sheriff, a programmer's hired gun. Assert is designed to stop code execution in the IDE if a test passed to the Assert method fails.

When programmers are writing code we have expressed and unexpressed assumptions about the state of our application at every given statement. An example of an expressed assumption is a check to see if a file exists before we try to open it for reading. An unexpressed assumption might be that the disk drive will not be reading from a corrupt disk cluster when it tries to read the file. Either way, each programmer is most likely to know about expressed and unexpressed assumptions only when initially writing the code or when some unexpressed assumption rears its ugly head. And, when you assume these things, it is a good idea to have a sheriff on patrol. Debug.Assert is an Excel VBA programmer's best sheriff.

As you write each method of substance it is a good idea to add conditional code that makes sure certain invariant parameters are adhered to. For example, dividing by 0 is a bad thing. So, if you are employing division, then checking to ensure the denominator is non-zero is a good idea. However, because you never want to divide by 0 it is also a good idea to instruct your sheriff to assert that no denominator is 0. Keep in mind that the Debug object is a tool for programmers. Debug.Assert will suspend an application's execution if the test condition fails, but it only runs in the VBE while debugging. For this reason it never replaces reasonable and prudent conditional checks, rather it augments them from the perspective of the programmer. An If-check will not suspend the application if the check fails, but a Debug.Assert statement will. Consequently, the If-condition will prevent your code from exploding, and the Debug check will tell you while testing of an impending explosion. Used in conjunction, a Debug.Assert and conditional check will appear joined at the hip, as demonstrated in the following fragment:

Public Sub DivideByZero(ByVal Numerator As Double, _
  ByVal Denominator As Double)

Dim result As Double

Debug.Assert Denominator <> 0
If (Denominator <> 0) Then
  result = Numerator / Denominator
Else

 ' do something else
 End If
End Sub

If the assertion fails in the VBE, then execution will be suspended on the line Debug.Assert Denominator <> 0, and the programmer will immediately be notified that a consumer—a programmer using the DivideByZero subroutine—has violated an inviolate assumption, a Denominator of 0 (see Figure 7-2).

In the next section, we will employ the Debug.Print and Debug.Assert methods to build our reusable toolbox for bulletproofing code. If you are very busy you can skip over A Brief History of PC Debugging and go straight to Creating Reusable Tools with the Debug Object.

A Brief Exemplar of PC Debugging

History is important, if for nothing else then for the perspective it provides. Interestingly, the history of microcomputers has really only spanned the last 20 years or so. (Remember the first IBM PC began selling in August of 1981.) Hence, if you were working as a programmer in 1981 then you are a living historian of the hyperconsolidated history of microcomputers.

Figure 7-2

As the story goes, a guy named Tim Paterson sold a disk operating system he had written to Bill Gates for $25,000. That disk operating system became MS-DOS. In conjunction with the disk operating system is the PCs BIOS, or basic input output system. These two things provided the basic building blocks that permit your computer to run and programmers to write computer programs.

The BIOS comprises things called interrupts. Interrupts are essentially global functions loaded as part of the BIOS when your computer starts up, and they are still there today just buried under mountains of complex Windows code. Interrupts are numbered beginning with 0, 1, 2, 3, and so on. You might imagine that the lowest numbered interrupts came first, followed by higher numbered interrupts as features were added later. For example, the DOS interrupt is 0×21 (interrupts are generally numbered as hexadecimal numbers, 33 as a decimal number). Following the notion that lower numbers preceded higher numbers would you be surprised that interrupt 0 is the divide by 0 interrupt? Division is pretty important to programmers and division by 0 must have been a real problem if it was supported by one of the first basic system services.

It's not important to know what interrupts 1 and 2 do. Interrupt 3 is more applicable to our discussion because it is the debug interrupt. It supports breaking in the middle of program execution; it is likely the underpinnings for the Stop statement in VBA and breakpoints. (Early debugging tools called interrupt 3 the soft-ice interrupt; as in, put the program on ice.)

These basic features still exist and are employed by your computer still doing their original jobs, albeit with a lot of very nice code on top of them. You can verify that these basic capabilities exist by opening a command prompt, running the debug.exe utility and writing some basic machine code. (Be careful though. These basic capabilities are very powerful.)

Using Figure 7-3 as a guide and typing the following debug instructions and assembler code, you can try a little assembly program. (This is a great reminder of how much better VBA programmers have it now, too.)

Figure 7-3

debug
nhello.com
a100
jmp 110
db “Hello, World!$”
mov dx, 102
mov ah, 9
int 3
int 21
int 20

rcx
1a
w
g
q

In order, the instructions and code are as follows:

• Debug runs the debug.exe utility when typed at the command prompt. This is actually a very basic debugger and editor, but ultimately can be very powerful.
• nhello.com is the name instruction. This step provides the target file name for the debug.exe utility to write the output to.
• a100 is the assemble instruction. We are telling the debugger we are ready to start writing code.
• jmp 110 is our old friend the GOTO instruction in assembly language.
• db “Hello, World!$” demonstrates how to declare a string variable.
• mov dx, 102 literally places the value 102—a hexadecimal value—in your PC's CPU's DX register. Registers are analogous to the lowest level variables. They are actually switches in microprocessor, and your PC is using them millions of times a second. In this step we are pointing the DX register at our string variable
• mov ah, 9 is actually a function in this context. Collectively, we are preparing a function call where the argument is the string “HelloWorld!” ($ is the string terminator) and the function is function 9.
• int 3 is a breakpoint. Our code will run to the statement containing int 3, then execution will be suspended, and the microprocessor state will be displayed. (Refer to the bottom part of Figure 7-3.)
• int 21 is actually the DOS interrupt. Interrupt 21, function 9 is the write string function.
• int 20 is the end of program interrupt. It tells the microprocessor that the program ends here and control should return to the operating system.
• The blank line is intentional. The blank line takes us out of the programming mode back to the instruction mode.
• rcx is an instruction to the debugger to dump and edit a value for the CX register. We are literally using the CX register to tell the debugger how many bytes to write.
• 1a is a hexadecimal number (26 in decimal). We want the debugger to write our 26 bytes of assembly language.
• w is the write instruction.
• g is like F5 in VBA; it is the run in the debug mode instruction.
• q is quit. Type q and press the Enter key and the debug.exe utility exits.

After you exit, you will see that you have a program named hello.com in the temp folder or wherever you entered the instructions. Go ahead and type Hello at the command prompt. Notice that the text Hello, World! is written to and the command prompt returns. Also, notice that the breakpoint was ignored. Does this behavior seem familiar? It is. This is the same kind of behavior the Debug object exhibits. Debugging in the VBE and Debug object runs; outside of the VBE the Debug statements are ignored. You are now an honorary sheriff. You have some good insight right down to the hardware. (All that is left is the mechanical engineering. Fortunately, we don't have to go quite that far.) Now you know that division by zero and breakpoints and such were real concerns for early programmers, and you also know that these same capabilities are still used by your computer even though they have been made prettier by the much nicer environment of the VBE.

Creating Reusable Tools with the Debug Object

One of my favorite programming books was Dave Thielen's No Bugs! published by Addison-Wesley. The book was written for the C programming language after a successful release of MS DOS 5.0. Although C is a lower level language than VBA, you will be surprised that many of the core debugging aids are based on the same basic concepts presented in Dave Thielen's book, and you can employ these techniques using VBA and the Debug object. The reason you want to do this is that they work without question.

In this section, we will create three powerful, manageable tools that will systematically help you eliminate bugs, be confident that you have done so, and when something does go wrong, you will be able to diagnose and fix the problem post haste. The tools are Trace, Trap, and Assert, and just as Microsoft has built the Debug and Stop on top of fundamental BIOS capabilities (see the previous section, A Brief History of PC Debugging) we will layer our implementation on top of the Debug object and Stop statement.

Tracing Code Execution

Tracing is a matter of placing lines of code in your solution that output some information describing the route your code is following as it runs and the state of the code. This information can be so useful in debugging that many development tools automatically trace code execution by building a call stack—tracing method calls—showing the order of execution. VBA supports tracing with Debug.Print but does not automatically keep track of the method call order; we have to do that ourselves. With just a little effort we can build our own Trace routine and describe the kind of data we want, yielding a consistent informative result every time. From an exportable module named DebugTools.vb here is the Trace method as we implemented it:

Option Explicit
#Const Tracing = True
#Const UseEventLog = False

Private Sub DebugPrint(ByVal Source As String, _
  ByVal Message As String)

#If UseEventLog Then

#Else
  Debug.Print “Source: “ & Source & “ Message: “ & Message
#End If

End Sub

Public Sub Trace(ByVal Source As String, _
  ByVal Message As String)

#If Tracing Then
   Call DebugPrint(Source, Message)
#End If

End Sub

In the preceding code fragment a compiler constant was defined: Tracing. The DebugPrint method is a generic method that accepts two strings, named Source and Message, respectively. Notice that we have stubbed out something controlled by the constant UseEventLog and the preexisting Debug.Print statement. (We'll come back to the UseEventLog branch in Writing to the EventLog later in this chapter.) The last method, Trace, accepts the same two arguments: Source and Message. Trace checks the value of the Debugging compiler constant. If it is True then DebugPrint is called.

A reasonable person might ask: Why are we using compiler constants and wrapping Debug.Print when you told me that the Debug.Print automatically shuts off when we are not running our code in the VBE? Glad you asked.

In our opinion, programming is a lot like building an onion from the inside out. Programming begins with a modest core, for example, your computer's basic input output system. Gradually, layers are wrapped around existing layers, slowly and judiciously adding complexity. (More subjectivity here.) These layers should be modest, easy to test, and provide useful amounts of new behavior. The new behavior does not have to be complex or large, in fact, it probably shouldn't be. The reason for this is that minor changes are easier to implement, less likely to introduce additional defects, and can be easily assessed for their utility. Put another way, if you see great globs of code—large monolithic behaviors—that try to do too much then you are probably looking at a future headache.

Singular behaviors wrapped around existing singular behaviors act like laminated wood: they combine to create something for strong and useful, yet surprisingly simple at each layer. This is what good, useful, debuggable, bulletproof code will look like. Unfortunately, last time we checked, this aspect of programming is not taught in colleges and universities. (Pardon me, if your school was the exception.) The reason for this is that our business is still quite new and not everyone has the same general opinion. (However, we would like to point out that many successful practitioners do use the same or an analogy similar to the onion layering analogy.) It takes practice to write layered, singular code, but that's what we are here for.

In keeping with the notion of layering complexity, our Trace method is easy to use. It takes two string parameters. In addition, it permits us to turn tracing on and off separately. From the VBE's behavior, we have expanded the definition of tracing to include a different respository. Instead of just logging to the Immediate window we have included a stub or logging to a more persistent resource, the EventLog. The next step is to figure out how to use the Trace behavior.

The programmer has to decide what to trace. You can trace everything but this gets tedious. Instead, we would subjectively elect to add Trace statements in places that are especially important to our solution and definitely add them wherever a bug crops up. Once you add a Trace statement leave it there even after the code is working well. When you change the code, new errors might rear their ugly heads, but you will already have tracing code left in place. One additional benefit is that the Trace statements also provide clues as to what kinds of things you or others were interested in and the problem areas in the past. That is, the Trace acts like breadcrumbs that you or others can follow in the future. Here is our CalculateFuelConsumed method from earlier in the chapter with the Debug.Print statements replaced by our tidier Trace method:

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  Call Trace(“Sheet1.CalculateFuelConsumped”, _
    “Duration=” & Duration & “ GallonsPerHour=” & GallonsPerHour)

  CalculateFuelConsumed = Duration * GallonsPerHour
  Call Trace(“Sheet1.CalculateFuelConsumped”, “Result=” & CalculateFuelConsumed)

End Function

The end result is consistent Trace statements and a dependable and neatly formatted output. Figure 7-4 shows the Immediate window after calling the new CalculateFuelConsumed method.

Figure 7-4

Trapping Code Execution Paths

The next technique is called Trapping. We have to check all of the pathways our code might take, because if we don't—both If and Else conditions, all of the methods and properties, and while loops that are and aren't entered—then how can we be sure that some path we haven't checked doesn't lead us to the impending danger?

The Trap idiom is used to mark branches in our code to make sure that we have devised a test for every nook and cranny of our code and that we get the expected or desired results. The Trap idiom is layered on top of the Stop statement, and like the Trace method is wrapped to include some flexibility. Here is the code for the Trap idiom that we can add to our DebugTools module. We will then describe how to set the Traps:

Option Explicit

#Const Tracing = True
#Const Debugging = True
#Const UseEventLog = False

Private Sub DebugPrint(ByVal Source As String, _
   ByVal Message As String)

 #If UseEventLog Then

 #Else
   Debug.Print “Source: “ & Source & “ Message: “ & Message
 #End If

 End Sub

 Public Sub Trap(ByVal Test As Boolean, _
   ByVal Source As String, _
   ByVal Message As String)
 #If Debugging Then

   If (Test) Then
        Call DebugPrint(Source, Message)
    Stop
      End If

 #End If

 End Sub

The Trap uses a const named Debugging and the DebugPrint method we have already seen. We won't redescribe the purpose of these two elements. Let's look at the Trap method.

Trap uses the same two arguments as Trace, Source, and Message. The Source and Message arguments are used to log which traps have been sprung and to help us find the Trap statement to disable it. All that happens when the Trap is sprung is that the source and message are logged and the program breaks (see Figure 7-5).

Figure 7-5

After the Trap is sprung we can use the value of Source to find the caller and comment out that Trap statement. By commenting out the Trap we clearly indicate that the specific path containing that Trap has been tested, and we can move on to other code branches to test. Again, we leave the Trap in place for future testing. For example, to ensure that we have devised a test for the CalculateFuelConsumed method, we can add a Trap statement. Here is how the CalculateFuelConsumped method looks now:

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  Call Trap(“Sheet1.CalculateFuelConsumed”, “Tested”)

  Call Trace(“Sheet1.CalculateFuelConsumed”, _
     “Duration=” & Duration & “ GallonsPerHour=” & GallonsPerHour)

  CalculateFuelConsumed = Duration * GallonsPerHour

  Call Trace(“Sheet1.CalculateFuelConsumed”, “Result=” & CalculateFuelConsumed)

End Function

When you devise a test for CalculateFuelConsumed comment out the statement containing the call to Trap.

Someone sensitive to neatness might not like all of the debugging and testing code intermingled with the actual labor-producing code. If you are such a person, use a consistent strategy for separating the real code from the testing code. A strategy we use is to add a private shadow method with the prefix Do. We place the real code in Do and the test code in the unprefixed method. As a result, evaluating the algorithm can be maintained separately from the bulletproof vest, the tracing, trapping, and in a moment, asserting code. Here is the revision:

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  'Call Trap(“Sheet1.CalculateFuelConsumed”, “Tested”)

  Call Trace(“Sheet1.CalculateFuelConsumed”, _
    “Duration=” & Duration & “ GallonsPerHour=” & GallonsPerHour)

  CalculateFuelConsumed = DoCalculateFuelConsumed(Duration, FuelConsumedPerhour)

  Call Trace(“Sheet1.CalculateFuelConsumed”, “Result=” & CalculateFuelConsumed)

End Function

Private Function DoCalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  DoCalculateFuelConsumed = Duration * GallonsPerHour

End Function

A casual observer might think all of this extra code is clumsy, and it is. But, as with wearing a bulletproof vest, it is nice to have the extra protection, and you'll be happy you wore it. The total effect of the code is that it shows a practiced, considered approach to a professional job, and once the basic tools are in place—DebugTools—and the habit is formed, this code is very easy to write. If you are so fortunate as to have different levels of programmers working with you, then you could write the actual behavior and junior programmer could wrap it up in the debugging and testing blanket, or it could be added later if problems are encountered.

Asserting Application Invariants

The last technique in our triad is the assertion. We happen to like policemen, but if all of these metaphors for law enforcement trouble you, keep in mind that what we are trying to convey is that programming is about what you tell the computer to do and what you are willing to enforce. Without public and private methods there is no way to safeguard data. Without debugging techniques there is no easy way to ensure things aren't going to go well. So, comparatively, like a policeman at a parade, we are trying to ensure as much as possible that things go well and the code we write behaves itself.

Assert is your highly trained, bulletproof-vest wearing, uniformed professional police officer. When you write code you will have basic assumptions about the state and behavior of your code. The Assert behavior is your way of ensuring that your assumptions are never violated. Sort of like a utopia where people don't drink and drive, litter, or commit generally annoying acts of mayhem and violence.

Use an Assert where you need to make sure things are going as expected. To be consistent, as with the other techniques, we will wrap the basic Assert up into DebugTools module to make sure we know what we expected and when our expectations are not met. Here is the code:

Public Sub Assert(ByVal Test As Boolean, _
  ByVal Source As String, ByVal Message As String)

#If Debugging Then
 If (Not Test) Then
      Call DebugPrint(Source, Message)
      Debug.Assert False
    End If
#End If

End Sub

Our Assert uses the Debugging constant defined earlier in the chapter and the DebugPrint method. Our Assert takes a Boolean test, and the Source and Message arguments. First, we check to make sure we are in the debugging mode. Then, we check to see if our assumption succeeded or failed. If the assumption failed, then we log the message and call the basic Assert, which suspends application execution. Next, we will show the assertion used in our CalculateFuelConsumed method, properly insuring that both of the GallonsPerHour and Duration are non-negative. (We have included the conditional checks in the Do method too.)

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  'Call Trap(“Sheet1.CalculateFuelConsumption”, “Tested”)

  Call Trace(“Sheet1.CalculateFuelConsumed”, _
    “Duration=” & Duration & “ GallonsPerHour=” & GallonsPerHour)

  Call Assert(Duration > 0, “Sheet1.CalculateFuelConsumed”, “Duration > 0”)
  Call Assert(GallonsPerHour > 0, “Sheet1.CalculateFuelConsumed”, _
    “GallonsPerHour > 0”)

  CalculateFuelConsumed = DoCalculateFuelConsumed(Duration, FuelConsumedPerhour)

  Call Trace(“Sheet1.CalculateFuelConsumption”, “Result=” & CalculateFuelConsumed)

End Function

Private Function DoCalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  If (Duration > 0 And GallonsPerHour > 0) Then
    DoCalculateFuelConsumed = Duration * GallonsPerHour
  Else
    ' Raise an error here
  End If

End Function

In the revised example we added two calls to Assert. The first ensures that Duration is greater than 0, as well as does the second. Note that we also added the runtime checks in the DoCalculateFuelConsumed method. As a result, this method is self-documenting, the name tells you what it does, and it is as bulletproof as we know how to make it. We have added a Trap to remind us to test the method. We can do a global search on Trap for any uncommented Traps to indicate where we still need to do testing. We have two Trace statements, so we know exactly where and in what order this method is used, and finally, we have asserted that are two parameters are within an acceptable range.

Here is one more tip before we move on. What do you do if you write a bunch of code and add Traps and Traces, but find that these Traps are never sprung and the Trace statements never show up? The answer is that you get rid of the dead weight by putting your code on a diet. That is, remove the unused code. It is just taking up precious brain cycles every time you or someone else reads it, but you know it isn't really needed.

Notice that the last revision of our method has two branch statements and an Else condition that only contains a comment. The answer is that we need to add a Trap to each branch to ensure both paths are tested, and we need to talk about raising error conditions. We will talk about raising error conditions in the next section; here is the final revision for this section that shows you how to handily incorporate the Trap statements for each branch:

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

   'Call Trap(“Sheet1.CalculateFuelConsumption”, “Tested”)

   Call Trace(“Sheet1.CalculateFuelConsumed”, _
     “Duration=” & Duration & “ GallonsPerHour=” & GallonsPerHour)

   Call Assert(Duration > 0, “Sheet1.CalculateFuelConsumed”, “Duration > 0”)
   Call Assert(GallonsPerHour > 0, “Sheet1.CalculateFuelConsumed”, _
      “GallonsPerHour > 0”)

   If (Duration > 0 And GallonsPerHour > 0) Then
     Call Trap(“Sheet1.CalculateFuelConsumed”, _
        “Duration > 0 And GallonsPerHour > 0”)
     CalculateFuelConsumed = DoCalculateFuelConsumed(Duration, GallonsPerHour)
  Else
    Call Trap(“Sheet1.CalculateFuelConsumed”, “Duration > 0 And
GallonsPerHour > 0 is False”)
    ' Raise Error here
  End If
   Call Trace(“Sheet1.CalculateFuelConsumption”, “Result=” & CalculateFuelConsumed)

End Function

Private Function DoCalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

DoCalculateFuelConsumed = Duration * GallonsPerHour

End Function

Notice that the final revision puts the If checks with the assertions and moves all of the debugging code in the outer method (the one not prefixed with Do). As a result, our algorithm is very clean and comprehensively protected.

A reasonable person might ask, do you really write all of this code for every method? The answer is no. We have written a couple of million lines of code and are pretty confident that a one-line method is so easy to test, debug, and probably write that we would not add much debugging code to something so simple. In fact, we try to keep all of our code as simple as the DoCalculateFuelConsumed method. In addition to ensuring fewer bugs, it ensures that we don't have to write a lot of comments or debugging and testing code. What we are trying to convey is that if you are just getting started you might not be so confident, or if you get yourself in a pickle, then you may have doubts about how to get out of that pickle. Ultimately, you have to decide how much debugging and testing code is enough. This subjective part is what makes good programming so hard to do.

Raising Errors

There are many good programmers that will disagree with us. Good. That's what keeps life interesting, and helps create new ideas. This is one area where thousands, perhaps millions of programmers may disagree. We hope to convince you that we are right, and they are wrong.

Years ago semantically weaker languages like C returned error codes whenever something went wrong. These error codes were simply integers, usually negative, that had some contextual but arbitrary meaning. Consequently, you will see a lot of code that returns an integer, which is some arbitrary value that means something in context. Programming like this though means everything is a function, all real return values have to be passed as ByRef arguments, and the meaning of an error code does not travel with the error; that knowledge is maintained somewhere else. Thus, if a function returns -1 to indicate an error then the consumer of that code has to go somewhere else to figure out what that -1 means. Here is our DoCalculateFuelConsumed method revised to return an error code. (Don't write code this way.)

Private Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double, ByRef Result As Double) As Integer

  If (Duration > 0 And GallonsPerHour > 0) Then
    Result = Duration * GallonsPerHour
    CalculateFuelConsumed = 0
  Else
    CalculateFuelConsumed = -1
  End If

End Function

In the revision, the return value indicates success or value and the actual result of the algorithm is a parameter. This immediately makes it difficult to use the function inline because we have to declare an extra argument for storing and retrieving the real value. Now to call CalculateFuelConsumed we have to write a lot of extra code:

Dim Result As Double
If( CalculateFuelConsumed(7, 1.5, Result) = 0) Then
  ' Yeah, it worked we can use Result
Else
  ' Sigh, it failed again Result is meaningless
End If

Folks, this is an especially pessimistic way to program. We are almost completely eliminating the coolness of the function return value, and we are programming as if our code is always on the verge of failing. But as you continue reading this chapter on bulletproofing, you'll know that your code is going to fail much less. So let's get back the cool factor of functions and write optimistic code where failure is an infrequent nuisance rather than a frequent guest. We accomplish this feat by getting rid of error codes and raising errors only when they occur:

Option Explicit

Public Function CalculateFuelConsumed(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double) As Double

  If (Duration > 0 And GallonsPerHour > 0) Then
    CalculateFuelConsumed = Duration * GallonsPerHour
  Else
    Call CalculateFuelConsumedError(Duration, GallonsPerHour)
  End If

End Function

Private Sub CalculateFuelConsumedError(ByVal Duration As Double, _
  ByVal GallonsPerHour As Double)

  Const Source As String = “Sheet2.CalculateFuelConsumed”
  Dim Description As String

  Description = “Duration {” & Duration & “} and GallonsPerHour {” & _
   GallonsPerHour & “} must be greater than 0”

   Call Err.Raise(vbObjectError + 1, Source, Description)
 End Sub

 Public Sub Test()

  On Error GoTo Catch
    MsgBox CalculateFuelConsumed(-8, 1.5)

  Exit Sub
Catch:
  MsgBox Err.Description, vbCritical

End Sub

CalculateFuelConsumed performs our calculation. If either Duration or GallonsPerHour are bad then an error is raised by calling the helper subroutine CalculateFuelConsumedError. The Helper method creates a nicely formatted description and raises the error. An error is raised by calling Err.Raise and passing an optional error number, source string, description, help file name and help file context. By convention, we add a contextual error number to the constant vbObjectError (as shown) to prevent our error numbers from colliding with VBA's native error codes.

To consume the new CalculateFuelConsumed method we write an On Error Goto label statement. We always try to use some work consistently, like Catch or Handle. Next, we call the method, and add a statement to exit the method, Exit Sub for subroutines. Exit Function for functions, and Exit Property for properties. Finally, very optimistically at the end, we add the label and the error handling code. In the example we just tell the user what went wrong with the MsgBox function.

We don't have to beat a dead horse, but if someone asks you why this approach is better then you can tell them it's better because:

• We can use functions as they were intended, to return calculated results.
• We aren't adding do nothing error code that pessimistically employs conditional statements to see if things went okay. We can assume they always will run fine.
• We will have a safety net that catches any error not just our error codes. (For example, what if we forgot to check a zero denominator in a division statement. The code would divide by zero and not one of our error codes. The error code approach would completely miss this error; the on Error Goto statement would not.)
• The Error object carries the meaning of the error along with it rather than requiring that meaning be construed from just an arbitrary error code.

Clearly, if one style is better in just one way then it is better overall. Raising errors is better than returning error codes in several, non-subjective ways.

Let's look at writing error handlers next.

Writing Error Handlers

There are three forms of On Error. There is the On Error GoTo label which branches to an arbitrary line number of text label followed by a colon. There is the On Error Resume Next that branches to the line immediately following the line which caused the error, and there is the On Error GoTo 0 statement that simply clears the error.

On Error Goto Line Number

You have already seen On Error Goto used with the Catch label in the preceding section. It is important to exit the method immediately preceding the label, otherwise the label and consequently, the error code will always run. Use Exit Sub to exit subroutines. Use Exit Function to exit functions, and Exit Property to exit properties. But what if you want the error code to always run? You might.

This technique is called a resource protection block. Computers have a finite number of things collectively called resources. A resource might be a database connection, file, or a network socket. If you create instances of these resources then you must ensure they get cleaned up. You can, by using the resource protection block idiom. The way it works is simple: use an On Error Goto label statement after the resource is created and intentionally do not place an exit statement before the label. In this way the error handling code—in this instance resource protecting code—is always run whether or not there is an error. Here is what it looks like with the code that creates a new file and writes some text to it. (This is not the preferred way to write to files. Use the FileSystemObject for that purpose.)

Public Sub ProtectThisResource()

  Open ThisWorkbook.Path & “dummy.txt” For Output As #1
  On Error GoTo Finally

     Print #1, “This file will always be closed”

Finally:

   Close #1

   If (Err.Number <> 0) Then MsgBox Err.Description

End Sub

The basic rhythm of a resource protection block is create the resource, insert the On Error Goto label statement, attempt to use the resource, and finally cleanup (both the resource and any error). In the example, we open a text file, set the On Error Goto Finally statement, attempt to use the resource, and then clean up every time. Notice, there is no exit statement.

On Error Resume Next

On Error Resume Next can be used to ignore an error and continue with the next statement. This technique is used immediately preceding a statement that can pass or fail. Kind of like a statement that isn't that important. We don't use On Error Resume Next that much because we'd rather not write statements that don't matter. If it doesn't matter, get rid of it.

Both Resume and Resume Next can be used by themselves. You will see Resume used where an error is handled and you want the code to try again. For example, if we added an extra error handling block to the ProtectThisResource method in the event the file couldn't be opened because its ReadOnly attribute was set, then we might change the attribute to the make the file readable and call Resume to try to open the file again. Resume Next is simply an instruction to skip the line of code that caused the error and proceed to the next line. You might see this in an error handler that doesn't care if an error causing line is skipped or not. The following method shows how we can use a resource protection block in conjunction with an error handling block and specifically recover from a write-protected file and resume trying to open the file:

Public Sub ProtectThisResource()

  Dim FileName As String
  FileName = ThisWorkbook.Path & “dummy.txt”

  On Error GoTo Catch
  Open FileName For Output As #1

  On Error GoTo Finally

    Print #1, Time & “ This file will always be closed”

Finally:

  Close #1
  If (Err.Number <> 0) Then MsgBox Err.Description
  Exit Sub

Catch:
  If (Err.Number = 75) Then
    Call SetAttr(FileName, vbNormal)
    Resume
  End If

End Sub

The first On Error Goto Catch will handler write-protected files and try a second time to open a file after the file attributes have been cleared. The second On Error Goto ensures that the file is finally closed.

The sample method looks kind of complicated, and it is important to note that a lot of other things might go wrong. What if the disk is corrupted? What if we run out of memory because the file is too big? What if the file is locked by another program? All of these things might go wrong. Quite frankly this is why writing robust programs is so hard. It is very difficult to cover every possibility at every juncture. The programmer has to subjectively anticipate what might reasonably go wrong and try to circumvent those failures, but eventually we have to be done and give the code to the users. Some very smart people have said that writing computer software is the hardest thing that people try to do. We are inclined to agree.

On Error GoTo 0

On Error Goto 0 disables error handlers in the current procedure. This is another statement we don't use that often. You will likely encounter it though. Just think of On Error Goto 0 as an off switch for error handlers at the procedural level.

Using the Err Object

The Err object contains information about the most recent error. Included in this information is the error code, the origin of the error, a description about the error, and a link to a Help file document, if available.

Err is what is referred to as a Singleton object. This means that there is only one in the entire application. And, because it is an instance of a class it has methods and properties. You can invoke Err.Raise to throw an error, and Err.Clear to clear an error. The remaining properties, except for one we haven't discussed, are the values used to initialize the error. The additional property is the LastDllError property. LastDllError returns the Hresult—the error code—of an error returned from a DLL. You will need to use this property when you call into methods in external DLLs, such as those found in what is collectively referred to as the Windows API.

Scaffolding

Before we get to the section on the EventLog, let's take a minute to talk about where and when to write test code. The technique we employ is called Scaffolding. If programming is like adding stories to a building, then Scaffolding is adding test code to each story before you proceed to the next one. Scaffolding is simply the process of adding test code to ensure that new code stands alone and hasn't introduced errors on top of preexisting good code.

For example, we created the DebugTools module for general reuse. This code should and does stand alone, but we can't be sure of that until we test the debug code. To do so, we have added one test method for each public method in the DebugTools module that runs the Trace, Assert, and Trap code, making sure the code produces the desired results. (It would be sadly ironic if the debug code introduced the bugs.) Here is the complete listing for the DebugTools module with the scaffold test code shown in bold font:

Option Explicit

#Const Tracing = True
#Const Debugging = True
#Const UseEventLog = False

Public Sub Trap(ByVal Source As String, _
  ByVal Message As String)

#If Debugging Then

 Call DebugPrint(Source, Message)
 Stop

#End If
End Sub

Private Sub DebugPrint(ByVal Source As String, _
  ByVal Message As String)

#If UseEventLog Then

#Else
  Debug.Print “Source: “ & Source & “ Message: “ & Message
#End If

End Sub

Public Sub Assert(ByVal Test As Boolean, _
  ByVal Source As String, ByVal Message As String)

#If Debugging Then
  If (Not Test) Then
    Call DebugPrint(Source, Message)
    Debug.Assert False
  End If
#End If

End Sub

Public Sub Trace(ByVal Source As String, _
   ByVal Message As String)

#If Tracing Then
  Call DebugPrint(Source, Message)
#End If

End Sub

Public Sub TraceParams(ByVal Source As String, _
  ParamArray Values() As Variant)

  Dim Message As String
  Dim I As Integer

  For I = LBound(Values) To UBound(Values)
    Message = Message & “ “ & Values(I)
  Next I

  Call Trace(Source, Message)

End Sub

#If Debugging Then

Public Sub TrapTest()
   Call Trap(“Sheet1.CallTrap”, “Test Trap”)
End Sub

Public Sub AssertTest()
   Call Assert(False, “AssertTest”, “Assertion Failure”)
End Sub

Public Sub TraceTest()
   Call Trace(“Sheet1.TraceAssert”, “Trace Test”)
End Sub

Public Sub TestSuite()
   ' Comment out when each test passes
   TrapTest
   AssertTest
   TraceTest
End Sub

#End If

The test code is only available when Debugging is True. TestSuite calls each of our individual test methods, TrapTest, AssertTest, and TraceTest. Each of TrapTest, AssertTest, and TraceTest calls each of the debugging methods, and we can examine the perspective results in the Immediate window and step through the code to ensure we are getting the desired results before making the code generally available for other projects or to other programmers.

Scaffolding is easy. However, it is important to do it as you go. Test each layer as you add complexity rather than trying to test everything at once when you think you are all done. Layering your tests is as valuable as layering your code. Each new piece will rest firmly on a strong and sound underpinning.

Writing to the EventLog

The EventLog is a system resource. It is important and valuable enough that it is an integral part of Microsoft's new .NET Framework. (.NET is a framework of code for VB, C#, C++, and many other programming languages.) It is important enough as a general tool that Microsoft has incorporated in the important sounding Exception Management Application Block (EMAB) and the Enterprise Instrumentation Framework (EIF). You will have to go online for more information about the EMAB and EIF because they aren't currently available to use directly, but the EventLog is, so we'll talk about the EventLog.

The EventLog is a local system service that acts like a repository for information about the state of applications, security, and the running computer in general. It is useful for diagnostics because the EventLog persists between application runs. Consequently, even if your application crashes radically, messages to the EventLog will still be there, helping you diagnose and fix what went wrong. Think of it this way: the Immediate window is cool but the EventLog is forever.

The following code is available in EventLog.bas. The code uses six Windows API methods to connect to the Windows EventLog and simplify writing errors to the EventLog to a single method WriteEntry. Clearly, we have taken some of the flexibility out of using the EventLog, but we want to use it just to log errors at this point:

Option Explicit

Private Const GMEM_ZEROINIT = &H40 ' Initializes memory to 0

Private Const EVENTLOG_ERROR_TYPE = 1
Private Const EVENTLOG_WARNING_TYPE = 2
Private Const EVENTLOG_INFORMATION_TYPE = 4

Declare Function RegisterEventSource Lib “advapi32.dll” Alias
“RegisterEventSourceA” (ByVal MachineName As String, ByVal Source As String) As
Long

Declare Function ReportEvent Lib “advapi32.dll” Alias “ReportEventA” (ByVal Handle
As Long, ByVal EventType As Integer, ByVal Category As Integer, ByVal EventID As
Long, ByVal UserId As Any, ByVal StringCount As Integer, ByVal DataSize As Long,
Text As Long, RawData As Any) As Boolean

Declare Function DeregisterEventSource Lib “advapi3 2.dll” (ByVal Handle As Long) As
Long

Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (ByVal Destination As
Any, ByVal Source As Any, ByVal Length As Long)

Declare Function GlobalAlloc Lib “kernel32” (ByVal Flags As Long, ByVal Length As Long) As Long

Declare Function GlobalFree Lib “kernel32” (ByVal hMem As Long) As Long

Public Sub WriteEntry(ByVal Message As String)

  Dim Handle As Long
  Dim EventSource As Long

On Error GoTo Finally
  Handle = GlobalAlloc(GMEM_ZEROINIT, Len(Message) + 1)
  Call CopyMemory(Handle, Message, Len(Message) + 1)
  EventSource = OpenEventSource(“vbruntime”)

  Call ReportEvent(EventSource, EVENTLOG_ERROR_TYPE, _
   0, 1, 0&, 1, 0, Handle, 0)

Finally:
  If (Handle <> 0) Then Call GlobalFree(Handle)
  If (EventSource <> 0) Then CloseEventSource (EventSource)
End Sub

Public Function OpenEventSource(ByVal Source As String) As Long
  ' Use the local machine
  OpenEventSource = RegisterEventSource(“.”, Source)
End Function

Public Sub CloseEventSource(ByVal EventSource As Long)
  Call DeregisterEventSource(EventSource)
End Sub
Sub LogEventTest()
  Call WriteEntry(“This is a test!”)
End Sub

After adding the EventLog module we can change the value of UseEventLog to True and call WriteEntry to write the error information to the EventLog. Use the EventLog Viewer (Start Run Eventvwr.msc) to view the Application log in the Event Viewer. The logged entries will be listed under the vbruntime source, as shown in Figure 7-6.

Figure 7-6

Consider the EventLog.bas module a teaser for Chapter 16. (We can't stuff everything into one chapter, so we'll reserve the discussion about using the Windows API for Chapter 16, Programming with the Windows API.)

Summary

Doing anything well requires a method to the madness. Doing anything well and fast is a matter of practice, habit, and a good set of tools. In this chapter, we demonstrated how we could build some useful tools for debugging and testing based on the Debug object, Stop statement, and the Windows EventLog. If you get in the habit of instrumenting your code with these debug tools, then you will write better code the first time and faster than ever.

The techniques we covered include using Assert, Trace, and Trap, and we introduced using the Windows API to write errors to the EventLog. You can easily fill in any gaps in understanding how to use the Windows API by reading Chapter 16.