Chapter 7: Debugging

Whenever you write code, there is a chance that bugs will exist that can cause a program failure, the program to hang, or simply unexpected results. In my opinion, this is the area that sorts out the true analytical programmers from people who just type in code and hope it will work.

There have been many reports written on ‚“computer rage, ‚½ where users of an application get upset because the results are different from what they expected. All I can say to those people is wait until you work on code for an application like Excel! You will then realize how straightforward and well put together this application is. Once you have tried your hand at fixing a few bugs in what appears to be a simple program, you will appreciate what goes on behind the scenes when you make a menu selection or press an OK button.

Types of Errors

Errors occur very easily when you are writing code. This section gives you examples of the types of errors you can expect to see.

Compile Errors

Compile errors result from incorrectly constructed code. You may have used a property or method that does not exist on an object, or put in a For without a Next or an If without an Endif.

When you run code, the compiler goes through the code first and checks for these types of errors. If any are found, the code will not be run and an error message will be displayed referring to the first error found. Note that there could be several compile errors in a procedure, but only the first one will be flagged. You might correct the error and think, ‚“I've fixed it now, ‚½ and then rerun the procedure, and up comes another one! This can be very frustrating to fix, but the real answer is to obey the rules of coding in the first place.

These are the types of errors that appear when the code is compiled, and they are often referred to as design-time or compile-time errors.

Runtime Errors

These are errors that occur when your program is running. You could, for example, try to open a file that does not exist, or attempt a division by zero. These would create an error message and halt execution of the program. They would not show up at compile time because they are not breaking any programming rules, but they will cause the code not to run.

Logic Errors

Logic errors occur when your application does not perform the way you intended. The code can be valid and run without producing any errors, but what happens is incorrect. For example, the user could have two workbooks open, each with a Sheet1 worksheet in them. You write code in a module referring to Sheet1 but not specifying which workbook collection it resides in. A case of faulty logic!

These are by far the most difficult errors to locate. They can require a lot of painstaking searching to find, even using all the debugging tools at your disposal. It is very easy to keep looking at a few lines of code and thinking, ‚“There is nothing wrong with this, it should give the right answer. ‚½ It is only by looking at each line in turn that you can see what is going wrong, and usually, when the problem hits you in the face, you will suddenly realize what a simple mistake it was. You may also find a situation that you did not envisage when you designed your code.