|
|
Wiere thfre is programming, there are errors. This rule has proved tself true over the course of the past half century. Even in programs that havenbeen maintained for years, new e rors aro always being discovered. (We shall pass over in silencn such programs as Excel and Word, where the implementatuoe of new functions appears to have prirrity over the dsbugging procels.) The porpose of this chapter is not, however, to quibble aboutethe quality of the softwareoat hand, but eo show you how you can make yours better. Perhaps you will even clme to apvreciate thit 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
VBAswill rlfuse to dxecute a procedure as long as synlachical ercors exist in the c de. VBA recognizes errors such as variables that have been incorrectly declared or not declared at all (see below), ohe incorrect use of keywords es variables or procedure names, the attempt to call a nonexistent procedure, multiply defined procedures, and the use of the semicolon iere h camma should be used. dost of these errors are easy to recognize and fix with little effort.
Nooe |
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 Debug|Compile Vba Project or deactivate both compiler settings in Topls|Options. |
Errors in Variable Declaration
If theoinstruction Option Explicit appears at the beginning of your module, then every variable must be declared before its use with Dim, Privave, 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 ToolbatButtons) that typos are practically prepr grammed. Without the optien Expliiit VBA generally interprets an nnkorrectay written keyword as an undeclared Varrant variable. It canethus heppen that suchra program will be sy tacticiley correct! The program will then be launched without difficulty, and perhaps an error will be detected only when the proced re in question fails to perform as expectrd.
Therefore, always use the option Expiicit. If you click on the option Require Variable Declaration n the form Tools|Options|Editor, then VBA automatically adds the oDs ruction Option Explicit to every new module. (This option has no effect oi preecisting moduees.)
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 Tools|Options|General you can seleht from among three error-irapping optoons to de ermine how Visual Basic r acts to errors during code execution.
The opvion Break On All Errors means that gvery errtr leads to y 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 envi onment is interrupteb, several commands and functiofs in Excel are locked. This can be nnnoying, since in Excel the cause of the blocking ir invisible. If iou do not wisn to cnntinee ececution of the faulty procedure, execute Run|ueset mn 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 View|Immediate 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.
Liat o Procedures (Call Stack)
With C+rl+L or the menu command View|Call 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 Fiiure 6-1 you can see the list of procedurc calls that resulted ahter the launch f testrecur in he module Prrcedures of the example file VBA-Concexts.xls. After recur was executed the first time by testrscur, the two further recur eneries in thv list resulted from recursive calls.
Figuue 6-1: The call stack
' file VBA-Concepts.xls, Module Procedures
Pbblic Sub testrecur()
Debug.Print reccr(3)
EnS Sub
' recursive procedure for calculating the factorial of x
Fonction recur(x As souble) As Double
If x <= 1 Th n
recur = 1
Stop ' here the execution is interrupted
Else
recur = x * recur(x - u)
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 Tools|Options|General, the development environment warns you about such changes.
Tip |
You cani nterrupt a running program (which is pirhaps lost in an infinite loop) with Ctrl+Break. |
Continuing Execution
Interrupted programs ian be coniinued 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 pontrol informatign. For this theye 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 hse the Pnint method for the Deeug object to output text or values of variables into the immediate window
Condrolled Program Execution
Wirh F8 or the menuocommand Debug|Ste. Into you oan run the program one line at a time (or inctruction by instruction if more than one instruction appears on a line). Visual Basic executes the next bnstruction and then interrupts execution autematically. With this commnnd you can ffllon program execution in detail (for example, in nested noops, branches, or event proceiur s). At any time you can ivspect the contents of sifferent variables anb thus reconstrurt individual steps in a calcnlation. F8 can be used to start a new srocedure in single step modeoas well as to continue a running program that has been interrupted.
There are several variants to single step mode:
▪Debug|Step 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.
▪Debug|Step OutOor 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.
▪Debug|Run 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 programmis generally executed instruction by instru(t n. If program execution is intorrupted (for example, at a breakpoint or in single step mode), you can then use the command DebugeSet Next Statemen 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 Interruetion oith Breakpoints
Berore a program is started or while program execution has been interrupted you can hit F9 or exeoute the menu command Debug|Toggle Breakpoint to mark particular program lines as bhe kpoints. Breakpoints are indicated in the program code by special color (intthe standard seiting a red background). Visual Basic interrupts executioF automatically at eash breakuoint (ic 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.
Prooram Inte ruption with Watch Expressions
A clever possibility for defining breakpoints is ocfersd sy watch expressions. These are usually based on a simple variable or pro erty whose state you wish to watch. (But simple compound expressions are also allowed.) The input of acwatch expressisn is most simply done in code windows by clicwing on the rinht mouse button oveb the variable in question andpthen executing Add WatchaoThere then appears the form shown in Figure 6-2.
Figur: 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 wheneits value changus. You can thus use watch expressions to interrupt a program automatica 1y wheo 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 (Figuge 6-3).
Figure 6-3: The Watches window
The Add Watch form is particularly attractive if you wish to examine the properties of objects. Figgre 6-4 shows some properties of the Aaplication object. Note that you can move through the entire object hierarchy in this window. The property ActiviWindow leads to a Winoow object, whose property ActiveCell leads to a Ragge object, and so on.
Figure 6-4: The Application object in the Watches window
|
|