Reconnaissance Operations and Tools

When things aren't working right, before you can fix what is wrong, you usually need to gather more information. You can use this to decide on a course of action to correct the error. You can gather information about what is going on with your program in a number of ways including the Debug.Print statement, the MsgBox function, the Immediate Window, the Auto Quick Info option, the Call Stack, and the Locals window.

The Debug.Print statement and MsgBox function perform essentially the same function when you use them for debugging. To use either one of these, you need to embed them into your code at strategic places. You've already seen that Debug.Print prints its output to the immediate window. MsgBox displays its output on a small dialog box. Debug.Print requires you to be in the VBE to see the results while MsgBox shows itself in Excel. You could replace the Debug.Print statements in Listing 4.2 and achieve virtually the same result with one crucial difference—you need to respond to every call to MsgBox by pressing the Enter key.

Your Personal FBI (without the Suffocating Bureaucracy)

Although Debug.Print and MsgBox are easy to use and handy, they aren't adequate by themselves to perform mass inspection of variables. For Better Investigation of variables you can use the Locals window. I have got to tell you, I love the Locals window. You should learn to love it also. Here is why. The Locals window (see Figure 4.3) allows you to inspect the details associated with every variable that is currently in scope (see "Variable Scope and Lifetime" in Chapter 3). If the variable is an object variable, the Locals window allows you to inspect the values of that object's properties. All you need to do is display the Locals window in Break mode. The Locals window is so invaluable that it deserves a rule.

Debugging Rule #5: Use the Locals window for efficient debugging. Not only is the Locals window handy for debugging, but using it has the wonderful side effect of increasing your knowledge and familiarity with the Excel object model.

Occasionally you'll have problems with a program because you aren't using or referring to the correct object's property for the task at hand. Or maybe you're in the development process and you're just not sure what property to use. One remedy to this problem is to run your procedure in Break mode with the Locals window displayed. Once you have set a variable to refer to the correct object, you can inspect the values associated with the variable and locate the property containing the value that you are looking for. This can be much faster than trial and error or searching through documentation looking for the appropriate property (though it is not a bad idea to look at the documentation for the property once you've identified it—it might alert you to a potential 'gotcha').

As an example of this technique, take another look at Figure 4.3. The variable ws refers to a worksheet object.

WARNING Unless you are a glutton for punishment, when inspecting a worksheet object using the Locals window, don't expand the Cells collection. f you do, your computer will work for a long time trying to take inventory on the 16,777,216 cells on a worksheet.

Figure 4.3

The Locals window is great for mass examination of variables.

Figure 4.3

The Locals window is great for mass examination of variables.



J VBAProject.Module I.PracticeWttiLocals

I Expression




[0 Modulel

Modulel Modulel


Wor ksfieet/Sheet 1

-g Application


— AutoFilter




-0 Cells


— CirculnrRoference



— CodeName



-[+] Comments


— ConsolidationFunction



-(+} ConsolidationOplions

Variant A/ariant(1 to 3)

— ConsolidationSources



— Creator



-H CustomProperbes

CustomProperties/CuslomPropi 1

l— Dismay AutomaticPageBre False


l— DIspiavPageBreaks




1— DisplayRiqhtTcLen



Ad Hoc Inspection Tools

For simple ad hoc variable inspection in Break mode, you can use one of two available tools. First, there is the Auto Data Tips feature. I mentioned this feature in Chapter 2 in the "Helpful Editor Features" section. Auto Data Tips shows the value associated with variables when you hover the cursor over the variable in Break mode.

The other tool you can use is the statement evaluation feature of the Immediate window. For example, if you have a variable named x, you could see the value of x by typing ?x in the Immediate window and pressing Enter.

Why would you use these two tools when you can use the Locals window or Debug.Print? Relative to Debug.Print, the advantage of these two tools is that you don't have to plan ahead or add statements to your code to use them.

The Locals window, meanwhile, requires quite a bit of screen real estate to use effectively. I usually only display the Locals window when I really need it. If I only want to examine a few simple variables, it is often faster to just use Auto Data Tips or the Immediate window.

An advantage of using the Immediate window for ad hoc examination is that you can evaluate variables in the context of a function. Perhaps you have a string variable named sFullName that contains the value "Franklin, Benjamin" and you want to know the result of using the variable in an expression such as this.

? Trim(Right(sFullName, Len(sFullName) - InStr(1, sFullName, ","))) Benjamin

If your program isn't running correctly and you suspect that it is the result of using a function incorrectly or an expression that isn't logically correct, it is an excellent idea to use the Immediate window to test possible corrections using the variables your procedure uses. The alternative is to change the statement in question and reexecute your program—not nearly as efficient.

A second advantage of using the Immediate window for ad hoc examination is that you can change the value of your variables and then continue program execution. Maybe you have a For.. .Next loop that contains some sort of error within the body of the loop. If you don't discover or correct the error the first time through the loop, you could start the loop over or focus on a specific loop counter value by resetting the loop counter to another value. Consider the following code fragment.

Dim nIndex As Integer

For nIndex = 1 To 10 ' some statements Next

At any point, you could enter nIndex = 5 in the Immediate window and press Enter to change the value of nIndex to 5.

Was this article helpful?

0 0

Post a comment