Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Basic VBA Debugging Techniques

Run Mode vs. Break Mode

A running application can exist in one of two states. Run mode is exactly what its name would suggest. The application is running normally. In break mode an application is still technically running, but execution has been interrupted. Break mode can be triggered by an unhandled runtime error, a Stop statement, or a break point placed within the code.

In the first group of topics in this section we discuss how the global VBE Error Trapping setting affects how an application will enter break mode. The global error trapping settings are located under the VBE Tools > Options > General > Error Trapping menu.

Break on All Errors

This setting is reasonably self-explanatory. When you have selected the Break on All Errors setting, all error handlers will be ignored. The moment any runtime error occurs, an error message will display and you will have the option to end the program or enter break mode on the line of code that caused the error.

Break in Class Module

If an error occurs within a class module procedure that contains an error handler, this setting is equivalent to the Break on Unhandled Errors setting that we cover next. This is to say it will not cause code execution to halt in response to the error. If an error occurs within a class module procedure that does not contain an error handler, code execution will be interrupted on the line of code in the class module procedure that generated the error. If you've ever experienced a runtime error on a line of code such as Userform1.Show, using this setting will bring you to the line of code within the UserForm class module that actually caused the error.

Break on Unhandled Errors

This setting causes code to break on errors only where there are no error handlers anywhere in the call stack above the procedure in which the error occurred. This is an important distinction. Even if an error occurs in a procedure without an error handler, if that procedure was called by another procedure that does contain an error handler the calling procedure's error handler will handle the error. Code execution will only break when there are no error handlers anywhere in the call stack above the procedure where the error occurred. We cover the call stack in more detail in The Call Stack section later in this chapter.

Keep in mind that the error trapping setting is an application-level setting that is persistent in all versions of Excel higher than Excel 97. There is no way to detect or change this setting within your VBA code. Therefore, when you are having strange problems with the error handling behavior on a specific user's computer, the first thing you should do is determine what error trapping setting is currently specified for that instance of Excel.

Debug Mode

Debug mode refers to the state in an application in which error handling has been intentionally bypassed in some fashion. Debug mode is usually built in to the error handling system used by the application. We covered debug mode as a design feature of an error handling system in Chapter 12 VBA Error Handling.

Placing an application into debug mode can also be as simple as changing the VBE Error Trapping setting to Break on All Errors. This is not a robust solution for implementing debug mode, however, because many nontrivial applications deliberately generate runtime errors that are designed to be ignored as a normal part of program execution. The Break on All Errors setting doesn't distinguish between errors that are a normal part of code execution and those that represent bugs, it just breaks on any of them. If you need to get past a "normal" error to reach an error caused by a bug, you will need a more sophisticated debug mode implementation, such as the one described next.

User-Defined Debug Mode

A user-defined debug mode typically involves a public constant that can be used to disable or modify the behavior of error handling on an application-wide basis. In the error handling system we demonstrated in Chapter 12 VBA Error Handling, debug mode was implemented with the following public constant defined in the MErrorHandler module:

Public Const gbDEBUG_MODE As Boolean = False

When set to False, the gbDEBUG_MODE constant has no effect and application error handling proceeds normally. When set to True, the gbDEBUG_MODE constant causes the error handler within the procedure where the error occurred to drop into a Stop statement. As we will see in the next section, this initiates break mode and enables us to debug the error.

The gbDEBUG_MODE constant is also used to disable error handling in other contexts. For example, some procedures may not have formal error handling. Procedures that are wrapped entirely in On Error Resume Next are the most common example. When a procedure is constructed in this manner, it implies that any errors that might occur within it are expected and should be ignored. This is not always a valid assumption, so we need some way of conditionally disabling On Error Resume Next. The standard way to accomplish this is shown in Listing 16-1.

Listing 16-1. Conditionally Disabling On Error Resume Next

If Not gbDEBUG_MODE Then
On Error Resume Next
End If

The code shown in Listing 16-1 would appear at the top of the procedure in question. If our gbDEBUG_MODE constant has been set to True, all error bypassing is disabled. When the gbDEBUG_MODE constant is set to False, the procedure functions normally, ignoring any errors that occur in the course of its execution.

The Stop Statement

When VBA encounters a Stop statement in your program, code execution is halted at the statement and break mode is initiated. You can then use any of the standard debugging techniques that will be discussed throughout this chapter to step over the Stop statement and debug your program. The Stop statement can be used as part of a larger debug mode infrastructure, as described in Chapter 12 VBA Error Handling, or it can be added to your code on an ad hoc basis when you are attempting to debug errors in very specific locations.Chapter 12 VBA Error Handling, its project is protected and it has been left in debug mode, the program will enter an infinite loop any time a runtime error occurs.

As shown in Listing 16-2, this is because the Stop statement is skipped. Rather than halting at the Stop statement, the Resume statement that immediately follows the Stop statement is executed. This causes VBA to re-execute the line of code that generated the error. This triggers the error handler again and causes the Resume statement to be executed again, ad infinitum.

Listing 16-2. The Perils of Leaving Debug Mode Active

ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop     ' This will be ignored in a protected project.
Resume
Else
Resume ErrorExit
End If
End Function

If you are very lucky, the user will understand how to press Ctrl+Break to halt the loop. In most cases, however, the only option they will understand is Ctrl+Alt+Del or worse, the Off button. Always remember to disable debug mode prior to shipping your code.

Conditional Compilation Constants

We mention conditional compilation constants very briefly in this section for the sake of completeness. Conditional compilation constants are designed to enable you to compile different versions of your code for different platforms that it will run on. Because VBA is an interpreted rather than a compiled programming language, however, the usefulness of conditional compilation constants is limited.

A conditional compilation constant could be substituted for the normal constant used to control debug mode, but there are no significant benefits to doing so. The single situation in which conditional compilation constants are truly useful in Excel VBA is when you need to write an application that can be run on both the PC and Mac platforms using the same code base. This situation is beyond the scope of this book.

Conditional compilation constants are defined in the VBE using the Tools > VBAProject Properties menu (where VBAProject is the actual name of your project). In Figure 16-1 we have created a conditional compilation constant named DEBUG_MODE that could act as a substitute for our gbDEBUG_MODE VBA constant.

Figure 16-1. Creating Conditional Compilation Constants

The control where conditional compilation constants are defined is located at the bottom of the General tab of the Project Properties dialog. Conditional compilation constants defined in the Project Properties dialog have public scope. These constants can only be assigned integer values. They can function as Boolean values using the rule that zero = False and nonzero = True. You can define multiple conditional compilation constants by separating each constant definition in the Project Properties dialog with a colon character.

Conditional compilation constants cannot be treated like standard VBA constants. They have their own special set of statements with which they must be used. The DEBUG_MODE conditional compilation constant would be used in a procedure as shown in Listing 16-3.

Listing 16-3. Using Conditional Compilation Constants

#If DEBUG_MODE Then
On Error GoTo 0
#Else
On Error Resume Next
#End If

The # character before the programming statements used with the conditional compilation constant is required. There are only a few such VBA programming language constructs designed to work with conditional compilation constants.

As you can see, for debugging purposes, conditional compilation constants are no different than normal VBA constants. For this reason we suggest that you use conditional compilation constants only for situations where you truly need conditional compilation. Debugging in VBA is not one of them.

Using Break Points (F9)

Break points are selected positions within your code at which program execution will automatically stop and enter break mode. Break points are conceptually very similar to the Stop statement. The difference is that break points can be added and removed with the click of a mouse or a keyboard shortcut, and they are not saved with your code. If you set break points in your code and then save your project, you will find that they disappear if you close and re-open the project. Stop statements will remain.

As implied above, break points can be set in one of two ways: using your mouse, or using keyboard shortcuts. Figure 16-2 shows a break point being set using the mouse.

Figure 16-2. Setting a Break Point with Your Mouse

[View full size image]

Each code module has a gray bar running down the left side. This is called the margin indicator bar. Clicking the margin indicator bar adds a break point to your code at the point where you clicked. Clicking an existing break point in the margin indicator bar removes that break point.

Setting break points using keyboard shortcuts is just as easy. To set a break point, place the cursor anywhere on the line of code where you want the break point to be located and press the F9 key. A break point will be added to that line of code. Pressing F9 while the cursor is located on an existing break point will remove that break point. Break points can only be set on executable lines of code. Code comments, blank lines and variable declarations are examples of places where you cannot set a break point.

After you have set a break point, run your application as you would normally. When code execution reaches the break point, it will stop and enter break mode. You can then use the debugging techniques discussed in the following sections to step over the break point and debug the problem you are having with your code.

Stepping Through Code

The fundamental skill you must master to become proficient at debugging is stepping through your code. The term "stepping through your code" implies a one-way, deterministic process. This is not the case. Stepping through code can involve moving backward or forward through your code as well as skipping sections of code or allowing sections of code to run but then halting when they have completed.

We discuss the various techniques used to step through code in detail in this section. Keep in mind that the whole point of stepping through code is to see what the code is doing. When you step through code you are duplicating exactly what your program does when it is running normally, but you are doing it one line of code at a time. In later sections we explain in detail how you determine what your code is doing once you are stepping through it.

Every code stepping feature in VBA has both a keyboard shortcut and a toolbar button equivalent. To become truly efficient at code debugging you must learn the keyboard shortcuts. For this reason we cover only the keyboard shortcuts required to initiate each technique. It is a simple matter to examine the VBE Debug toolbar and discover the equivalent toolbar buttons for each keyboard shortcut we discuss. You can display the VBE Debug toolbar by choosing View > Toolbars > Debug from the VBE menu. We provide a comprehensive list of debugging-related keyboard shortcuts at the end of the chapter.

Step Into (F8)

Stepping into code can be initiated in one of two ways. If you need to examine code execution from the beginning of an entry point procedure, you can place your cursor anywhere inside that procedure and press F8 to begin stepping through its code.

Alternatively, if you want to start your debugging session deeper in the procedure, or even deeper in the call stack, you can place a break point on the first line of code you want to debug and press F5. VBA will run your code until it reaches the break point. You can then use F8 to begin executing your code one line at a time from that point.

As you are stepping through your code, you will notice a yellow line that moves each time you execute one of the step commands. This line is called the execution point. To make it easier to follow, the execution point displays an arrow in the margin indicator bar of the code module, as shown in Figure 16-3.

Figure 16-3. The Execution Point Indicator

[View full size image]

The execution point indicator can be a bit confusing until you get used to it. It does not represent the line of code you have just executed; rather it shows the line of code that will be executed next. The line of code you just executed is the first executable line of code above the execution point indicator.

The reason VBA debugging works this way will become apparent as you gain experience debugging. Having a clear marker on the line of code that will be executed next tends to be much more valuable than having a marker on the line of code you just executed. The latter is easily determined from the former, but not necessarily vice versa.

With each press of the F8 key the line of code currently highlighted by the execution point indicator will be executed and the execution point indicator will move to the line of code that logically follows based on the results of executing the previous line. This may or may not be the next physical line of code depending on how your program is structured.

Step Over (Shift+F8)

While single stepping through code you will often reach calls to subprocedure that you are sure do not contain any errors. If you continue to press F8, code execution will step into the subprocedure and begin executing its code. What you would rather do in this case is have VBA execute all code associated with the subprocedure call and break again on the line of code that immediately follows it. This is accomplished using the Step Over command, whose keyboard shortcut is Shift+F8.

The Step Over command will execute all code required to pass over the line currently highlighted by the execution point indicator and then break on the next executable line of code that logically follows the result of that execution. If the line of code currently highlighted by the execution point indicator is not a call to an outside procedure, the Step Over command is logically equivalent to the Step Into command.

Step Out (Ctrl+Shift+F8)

If you step into a subprocedure call by accident or if you step into it on purpose and then realize you don't need to continue stepping through it, the Step Out command is your savior. Rather than having to tediously step through the rest of the subprocedure code or physically locate the calling procedure and use the Step To Cursor command described in the next section, you can just press Ctrl+Shift+F8. VBA will run the rest of the subprocedure automatically and break again on the next executable line of code in the calling procedure that logically follows the result of the subprocedure call.

Step to Cursor (Ctrl+F8)

This option would be more accurately called "run to cursor." Whether you are already in break mode or you are just initiating a debugging session, you can simply place your cursor on the line of code where you want execution to break and press the Ctrl+F8 keyboard shortcut. VBA will run your code from the beginning until it reaches the location of your cursor, at which point it will enter break mode. This option works almost exactly like placing a break point on the line. The only difference is that Step to Cursor is transient. As soon as you move the cursor, the step to cursor point changes.

This option is most useful when you are single stepping through your code and you encounter a section of code you are sure does not contain any errors. Just use the arrow keys to move the cursor down to the first executable line of code beyond this section and press Ctrl+F8. VBA will run all the code between the current execution point and the line marked by the cursor, entering break mode again at the line marked by the cursor. This enables you to avoid tediously single stepping through sections of code where it is not necessary.

Changing the Execution Point, or Set Next Statement (Ctrl+F9)

There are times when you want to either skip lines of code that are about to be executed or retrace the execution steps that got you to where you are. One of the most amazing things about the VBA debugger is that it enables you to do both of these things. You can move the execution point backward and forward as you please using the Set Next Statement command. The execution point can also be dragged to different positions using your mouse. After you have repositioned the execution point you can resume stepping through your code from that point using the commands covered in the previous section.

The difference between changing the execution point using the Set Next Statement command and the step commands that we have previously covered is the following:

If you reposition the execution point such that it skips lines of code that have not yet been executed, the lines you skipped will not be executed.

If you reposition the execution point such that it resumes execution above the point where it is currently positioned, all of the lines of code between the new position of the execution point and the line of code directly above the previous position of the execution point will be executed a second time.

As you can imagine, you must have a very firm understanding of what your code does and where it makes sense to change the execution point to avoid spurious errors or garbage results. For example, if you skip a line of code that sets an object variable and then attempt to execute a line of code that uses the object variable, you will obviously get an "Object variable or with block variable not set" error. Similarly, if you move the execution point backward such that you rerun a block of code that increments a variable, the value of that variable will be incremented beyond the value it would normally reach, resulting in potentially garbage data.

The ability to change the execution point is a very valuable tool. It enables you to safely skip a section of code that you know would otherwise cause an error, or rerun a section of code that you would like to examine a second time without having to restart debugging. Just be sure you are fully aware of what you are doing before you use it.