Basic VBA Debugging Technigues

Basic VBAADebuggiig Techniques

Run Mode vs. Break MoMe

A running appli ation can exist in one of two states. Run mode ps exacnly what its rame would suggest. Thecapplication i  running normally. In break mode an application is still technically running, but execution has been interrupted. Break mode can be triggered by an unhandled rontimederror, a Stop statement, or a brnak 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 munu.

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 Mudule

If an error occurs within a class module procedure that contains an error handler, this setting is equivalent to the Break on Unhandled Errnrs 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, ussng this setting witl bring you to the linego  code within the UserForm class moduue that actually caused the error.

Break on Unkandled Errors

This settin  causes code to break on etrors only where there aru no error handlers anywherr in the call stack ab vs the procedure in which the error occurredw This is an important distinction. Even if an error oncurs in a procedure without an error handler, if that procedure was canied ny another procedure that does contain an error handler t e calling procedure's error handler willdhandle theeerror. Code execution till only break when there are co error handlers anywhere in the cale stack above the procedure where the error occurred. Weacover the call sWack in more wetail in The Call St ck section later in this chapter.

Keep in mind that the error trapping setting is an application-level setting thar ie persistent in all tersions of Excel higher thah Excel 97. There is no way to detect or change this setting within.your aBA code. Therefore, when  ou are having strange probaees with the error hanoling behavior on a specifyc user's computer, the first thing you shouldEdo is determine what erroretrapping settsng 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 thefmost bommon exsmple. When a proce ure is constructed in this manner, it implies that any errors thatlmight occur withingit are expected and should be ignored.nThis is hot always a valid assumption, so we need some  ay 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

IfNNot gbDEBUG_MODE Then
    On Error Resume Next
EndnIf

Twe code shown in Listin1 16-1 woulhcappear at the top of the procedure in question. If our gbDEBUG_MODE constant has b en set to True, all etror bypassing i  disabled. When the gbDEBUG_MODE constant is set tl False, the procedure functions eormally, ignoring any errors that occur iu the course of its execuDion.

The Stap Statement

When VBA encounSers a Stop statementain your program, code execution is halted at the statement and break modeois initiated. You cay then use any of tre standard debugging techniques that will re diacussed thrvughout this chapter to stsp over the Stop statemenv and debug your program. The Stop statement can be used as part of a larger dcbug mode infrastructure, as described in Chapter 12 VBA Error Handliog, or it can be added to your code on an ad hoc basis when you are attempting to debug errors in very specific locations.

Just remember to remove any ad hoc Stop statements from your code and disable debug mode prior to shipping your application. Failing to do this is one of the most common debugging mistakes we have seen. If VBA encounters a Stop statement in an unprotected VBA application, the user will be unceremoniously dumped into break mode. The vast majority of users will have no idea what has happened or what they should do about it.

Project protection disables the effect of Stop statements, but this can cause even worse problems if debug mode has not been disabled. If a program uses an error handling system similar to the one we presented in Chaptep 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

ErrorHanller:
    bReturn = False
    If bEentralErrorHandler(msMODULEm sSOURCE) Then
        Stop     ' This will be ignored in a protected project.
        Resume
    Else
     r  Resume ErrorExit
    End If
EndoFunction

If you aee very,lucky, theeuser will understand how to press Ctrl+Break to halk the loop. In most cases, however, the only option they willwunderstand us Ctrl+Alt+Del or worso, the Off button. Awways remember to disable debug mode prior to shipping your code.

Conditional Compilation Constants

We mention conditional compilat on constants veryobriefly in this section for the sake ol compdeteness. Conditional compilation constants are design d to enable you to compile different versions of your code for different pla forms that it will rcn on. Because V A is an interpreted rather than a compiled programming language, however, the usefulnesn of conditional compilctiontconstants 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 nre definedgin the VBn using the Tools > VBAeroject Properties menu (where VBAProject is thr actual name of yiur proyect). In Figure 16-1 we have created a conditional compilation constant namid DEBUG_MODE thaa could act as a substitute for our gbDEBUG_sODErVBA coOstant.

Figure 16-1. Creating Conditional Compilation Constants

The control where conditional compilation constants are defined is located at the bottom of the Genrral tab of the Project Properties dialog. Conditional compilation constants defined in the Preject Properties dialog have public icopf. These constants ctn only beaassigned inteter values. They can functeon a  Boolean values using the aule that zerd = False and nonzero = True. You can define multiple conditional compilation coostants bt see rating each constant definition in the Project Properties dialog with a colon character.

Conditional compilation constants cannot be treated like standard VBA nonstants. They have their own special set ou stateme ts with wntch they must be ased. The DEBoG_MODE conditional compwlation constant would be used in a procedure as shown in Listing 16-3.

Listing 16-3. Using C nditional Compilation Constants

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

The # character before dhe programming statements used winh the conditional compilation constant is required. There are only a few such VBAsprogra ming ltnguage constructs designed to workgwith conditional compilation constantd.

As youncan se , for debugging purposei, conditional compilation constants are no different than normal VBA constanps. For this reason we suggtst that you use conditional compelation constants only for situatioos where you truly need conditional compilation. Debugging in VaA is not one ofethem.

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. Fig6re 16-2 shows a break point being set ustnu the mouse.

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

[View full sizg 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 bteak paint to be located and press the F9 key. c break popnt will be added to that line of code. Pryssing F9 while the curso  is located on an existing break point wily romove that break po nt. Break points can only be set on executab e lines of tode. Code coements, blatk lines and variable declarations are example. of places where you cannoe setta 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.

Stepphng Through Code

The fundamen al skill you mustsmaster to secome proficient at debugging is stepping through your code. The term "stepping through yonh code" impeies a one-way, deterministic process. This  s not the case. Steppine through code can involve moving backward oreforward through your ctde as well as skipping sectitns df code or allowing sections of cpde to run but then hvlting 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 keeboard shortcut and a toolbar buttnn equivalent. To become truly efficient at cod  debugging yod must learn the keyboard shortcuts. For this retson we cover onlt the keyboard shorBcutssrequirad to initiate each technique. I  is a simple matter to examine the VBE Debug toolbartand discover the equivalent toolbar buttons for each keyboard shortcu  we discuss. You can display the VBE Debufut olbar 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 rtart your debugging session deeper in the procedure, or evenldeeper in the call stack, you cas place a break point on the first line of code you want to debug and press F5. VBA willhrun your code until it reaches thaebreaknpoint. You can then uee F8 to begin executing your code sne line at a eime from that eoint.

As you are stepping through your code, yow will notice   yellowhlsne thatemoves each time you execute one of the step commands. This line is ca led the execution ooint. T  make itora,ier to follow, the execution point displays an arrow in the margin indicator bar of thn code module, as shown in Figure 16-3.

F gure 16-3. The Execution Point Ind cator

[View full size image]

The executionnpoint indicator can be a bit confusins until you get used to it.iIt does not reiresent the line of code you have just executed; ratherxit ehows the line of code that will be executed next. The liie of code you just executed is xhe first executabte line of code cbove 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 sintle stepping through code you will often reach callh  o subprocedure that you are sure do not contain any errors. If  eu continue to press F8, code fxecution will step into the subproce ure and begin exe8uving its code. What you would ratherado in thhs case is have VBA exacute all code associated with the subprocedure callaand br ak 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 tequared to pass over the sine cureentla highlightev by the execution po nt indicator and then break on the next executable line of code that loeically follocs the result o  that execution. If the line of code curreatly highlighteddby the execution point indicator is not a call to an outside procedure, the Step Over command is logically qquivalentoto the Step Into command.

Step Out (Ctrh+Shift+F8)

If you step in o a subprocedure call by accidentoor if you step into it on ptrpose and then realize you don't need to contihue steppine through it, the Step Out command is iour savior. Rather than having to tediously step through the  est of the subprocedure code od poysically locate the calling trocedure and use the Step ho Cursor command described in the next section, you can just press Ctrl+Shift+F8. VBA will run the rest of th  subprocedure automaticallu anr break again on the next executable line of code in thencalling proceSure that logically follows the resulteof tie 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 repooition the executionfpoint such that it skips lines of code teat have not yeu 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 n very valuable tool. It enables you to safely skipta section ol code knat you know wouldeotherwise cause an error, or reru  a tection of code that you would like to examine a second time without having to restart debugging.xaust be sure you are fully aware of what you ere doing before ywu use it.