Chapter 6: Debugging, Protection When Errors Arise

Where there is programming, there are errors. This rule has proved itself true over the course of the past half century. Even in programs that have been maintained for years , new errors are always being discovered . (We shall pass over in silence such programs as Excel and Word, where the implementation of new functions appears to have priority over the debugging process.) The purpose of this chapter is not, however, to quibble about the quality of the software at hand, but to show you how you can make yours better. Perhaps you will even come to appreciate that even Microsoft is not perfect.

This chapter is divided into two sections. The first describes the possibilities for analyzing programs with bugs in them. For this purpose VBA offers an excellent work environment, which among other things allows for step-by-step execution of programs, conditional breakpoints, and keeping track of variables . The second section of this chapter shows how you can protect your program against going out of control when an error does occur (for example, when the user uses the program incorrectly).

6.1 Debugging Aids

Syntax Control

Errors Reported Before Program Execution

VBA will refuse to execute a procedure as long as syntactical errors exist in the code. VBA recognizes errors such as variables that have been incorrectly declared or not declared at all (see below), the incorrect use of keywords as variables or procedure names , the attempt to call a nonexistent procedure, multiply defined procedures, and the use of the semicolon where a comma should be used. Most of these errors are easy to recognize and fix with little effort.

Note  

Many errors, such as typographical errors in naming methods and properties, can be caught only at compilation time (and sometimes not until the code is executed). However, in the default setting only those parts of the program are compiled that are actually needed. Thus it can happen that syntactical errors will not be discovered for a long time, that is, until the procedure in question is executed.

It is generally a good idea to uncover all syntactical errors before the program is executed. For this you can either convert the entire project to pseudocode with DebugCompile Vba Project or deactivate both compiler settings in ToolsOptions .

Errors in Variable Declaration

If the instruction Option Explicit appears at the beginning of your module, then every variable must be declared before its use with Dim , Private , or Public . This may appear to be unnecessary extra work, but it is an important and efficient mechanism for avoiding typographical errors. It is precisely in the unpleasant and longwinded keywords (such as the method ToolbarButtons ) that typos are practically preprogrammed. Without the option Explicit VBA generally interprets an incorrectly written keyword as an undeclared Variant variable. It can thus happen that such a program will be syntactically correct! The program will then be launched without difficulty, and perhaps an error will be detected only when the procedure in question fails to perform as expected.

Therefore, always use the option Explicit . If you click on the option Require Variable Declaration in the form ToolsOptionsEditor, then VBA automatically adds the instruction Option Explicit to every new module. (This option has no effect on preexisting modules.)

If you give the type of your parameters explicitly in your procedures (and you should), then this type must match exactly the type of the variable passed when the procedure is called. Otherwise, an error alert will result.

Reaction to Errors

In ToolsOptionsGeneral you can select from among three error-trapping options to determine how Visual Basic reacts to errors during code execution.

The option Break On All Errors means that every error leads to a program break even if this error is caught by On Error . (Information on programming errorhandling routines appears in the next section.) This option is very useful to the extent that often errors that you had not even thought of during program development remain hidden by On-Error routines. Thus the option Break On All Errors deactivates all On-Error instructions.

With the other options (Break In Class Module, Break On Unhandled Errors) an error leads to a break only if there is no error-handling routine. These two options differ only when you are testing class modules.

Tip  

When program execution is interrupted , the content of variables is automatically shown in the code windows when the mouse is passed over the variable name .

Tip  

When program execution in the development environment is interrupted, several commands and functions in Excel are locked. This can be annoying, since in Excel the cause of the blocking is invisible. If you do not wish to continue execution of the faulty procedure, execute RunReset in the development environment.

The Immediate Window as a Debugging Tool

An important assistant in debugging is the immediate window. You can open the immediate window before a procedure is launched with ViewImmediate Window (or Ctrl+G ) and then execute the procedure with an instruction in the immediate window. For a subprogram without parameters it suffices merely to give the name of the procedure and hit Return. If parameters are required, then you will have to input some valid values. In the case of functions you must note that the parameters are placed in parentheses and that the return value of the function will be calculated. The easiest way to proceed is to prefix the function call by "?" (the abbreviation for Print ): VBA then prints the result on the next line of the immediate window.

List of Procedures (Call Stack)

With Ctrl+L or the menu command ViewCall Stack you open a dialog that lists the procedures whose execution has led to the current procedure being called. The list is sorted in reverse order: At the top is the current procedure, and below it the procedure from which the current procedure was called, and so on down the list. In the case of recursive procedures (those that call themselves ) it can happen that the same procedure name will occur repeatedly in the call stack. Thus the Call Stack dialog allows you to determine how the current procedure came to be called. A double click on one of the procedures alters the current context for variables in the immediate window.

In Figure 6-1 you can see the list of procedure calls that resulted after the launch of testrecur in the module Procedures of the example file VBA-Concepts.xls . After recur was executed the first time by testrecur , the two further recur entries in the list resulted from recursive calls.

Figure 6-1: The call stack

 ' file VBA-Concepts.xls, Module Procedures Public Sub  testrecur()  Debug.Print recur(3) End Sub ' recursive procedure for calculating the factorial of x Function  recur  (x As Double) As Double   If x <= 1 Then     recur = 1     Stop ' here the execution is interrupted   Else     recur = x * recur(x - 1)   End If End Function 

Program Changes in a Running Program

Since Excel 97 it has been possible to make changes in a running program and then continue execution. Needless to say, this is a very practical method of debugging. However, continuing the program is not possible if the structure of the program changes, such as in the declaration of a parameter of a currently active procedure. If you activated the option Notify Before State Loss in ToolsOptionsGeneral, the development environment warns you about such changes.

Tip  

You can interrupt a running program (which is perhaps lost in an infinite loop) with Ctrl+Break .

Continuing Execution

Interrupted programs can be continued with F5 or F8 (single step mode). This holds as well for programs in which an error has occurred. However, a continuation makes sense only if the cause of the error can be eliminated (which is seldom the case). An example: A division by zero occurs in a program from the instruction a=b/c . You can execute c=1 in the immediate window and then continue with F5 .

Control Output in the Program

In many cases it is useful to have the program output control information. For this there are two possibilities:

• You can use the command MsgBox to output an arbitrary (short) text in a small window. As soon as you press the OK button of this window, execution continues.

• You can use the Print method for the Debug object to output text or values of variables into the immediate window

Controlled Program Execution

With F8 or the menu command DebugStep Into you can run the program one line at a time (or instruction by instruction if more than one instruction appears on a line). Visual Basic executes the next instruction and then interrupts execution automatically. With this command you can follow program execution in detail (for example, in nested loops , branches, or event procedures). At any time you can inspect the contents of different variables and thus reconstruct individual steps in a calculation. F8 can be used to start a new procedure in single step mode as well as to continue a running program that has been interrupted.

There are several variants to single step mode:

• DebugStep Over or Shift+F8 normally executes only a single instruction. However, if in this instruction a function or procedure is called, then this procedure will be executed in its entirety.

• DebugStep Out or Ctrl+Shift+F8 executes all instructions up to the end of the current procedure. If in the process other procedures are called, these will also be executed to the end.

• DebugRun To Cursor or Ctrl+F8 works similarly, but execution is again interrupted at the line of the current procedure in which the cursor is located. This command is frequently useful to avoid the necessity of setting a breakpoint.

Skipping or Repeating Instructions

A program is generally executed instruction by instruction. If program execution is interrupted (for example, at a breakpoint or in single step mode), you can then use the command DebugSet Next Statement or Ctrl+F9 to determine the line at which execution will continue. It is not possible to select a line located outside of the current procedure. The command Set Next Statement is particularly suitable for executing program lines that have already been executed or for skipping over program lines.

Program Interruption with Breakpoints

Before a program is started or while program execution has been interrupted you can hit F9 or execute the menu command DebugToggle Breakpoint to mark particular program lines as breakpoints. Breakpoints are indicated in the program code by a special color (in the standard setting a red background). Visual Basic interrupts execution automatically at each breakpoint (in fact, just before this line is executed).

Breakpoints are of great use in checking critical program segments. Simply set a breakpoint in the first line of a procedure in which you suspect an error to be lurking. As soon as this procedure is reached in the course of program execution, Visual Basic interrupts execution. You can now investigate variables in the immediate window or continue the program in single step mode.

Program Interruption with Watch Expressions

A clever possibility for defining breakpoints is offered by watch expressions. These are usually based on a simple variable or property whose state you wish to watch. (But simple compound expressions are also allowed.) The input of a watch expression is most simply done in code windows by clicking on the right mouse button over the variable in question and then executing Add Watch. There then appears the form shown in Figure 6-2.

Figure 6-2: Defining a watch expression

You then choose from among three forms of watching. The simplest variant is Watch Expression, by which Visual Basic indicates the current value of the variable in the immediate window. With the other two modes the program is interrupted when the entire expression assumes the value True or when its value changes. You can thus use watch expressions to interrupt a program automatically when the value of a variable is, say, greater than 100.

In the Add Watch form you can also determine from what context the variable is to be read (that is, which module, which procedure; this question is important, since there can exist like-named variables in different contexts). All defined watch expressions are shown in the Watches window (Figure 6-3).

Figure 6-3: The Watches window

The Add Watch form is particularly attractive if you wish to examine the properties of objects. Figure 6-4 shows some properties of the Application object. Note that you can move through the entire object hierarchy in this window. The property ActiveWindow leads to a Window object, whose property ActiveCell leads to a Range object, and so on.

Figure 6-4: The Application object in the Watches window