For i to debugprint Test inext i The result of running this is shown in Figure




for i = 1 to 5:debug.print "Test '

' & i:next i








Figure 7-26. Testing loops in the Immediate window

Figure 7-26. Testing loops in the Immediate window

The Locals Window

The Locals window lets you see all current variables and their values in break mode in one place. To see the Locals window at work, we are going to run an existing macro in the DebugExample01.xlsm file against the sales data on Sheetl.

We saw this data and code in our 1-MacroExample01.xlsm file in Chapter 1. In this example, the data has been slightly modified and will generate an error that we'll track down using the tools explained thus far.

1. In the Excel window, open Worksheetl in the DebugExample01.xlsm file.

2. Open the Developer ribbon and click the Macros command to open the Macros dialog box.

3. Run the AddSalesTotal macro.

A type mismatch error occurs, as shown in Figure 7-27.

Microsoft Visual Basic

Run-time error '13':

Type mismatch


1 Debug |


1 1

Figure 7-27. Type mismatch error 13

4. Click the Debug button to put the code in break mode.

5. Open the Locals window by choosing View > Locals Window.

The Locals window will open, as shown in Figure 7-28.

Looking at the Locals window in Figure 7-28, we see the values for the GetSalesTotal function and for the variables currReturn, temp, and cell. We also see the values for the range we pass into the function, RangeToTotal.

GetSalesTotal and currReturn both have a value of 0, as they are not set until the end of the function. The variable temp, which is set during the loop that walks the range, has a value of 303 so far.

The cell variable is a bit different, as is the RangeToTotal variable. These variables are references to range objects. This means that they have more than just a value. They have properties—and lots of them!

6. Click the plus sign to the left of the cell variable.

7. Scroll down until you see the Value2 property.

I (General)


Function GetSalesTotal(RangeToTotal As Range) As Currency Dim currReturn As Currency Dim cell As Range Dim temp As Currency

For Each cell In RangeToTotal temp = temp + cell.Value Next cell currReturn = temp GetSalesTotal = currReturn End Function


VB APro ject. Module2. GetSalesTotal


1 Value

1 Type

[+] Module2


[+] RangeToTotal








Hi cell





Figure 7-28. The Locals window displays values for all in-scope variables.

Figure 7-28. The Locals window displays values for all in-scope variables.

The Value2 property stores the current cell's value. In this case, it's the string N/A. It looks like someone didn't have a value for DVD sales in the East region, and entered a string rather than leaving it blank or entering a 0. We've found our problem.

Before we fix the issue, let's take a look at a feature that's available from the Locals window as well as the View menu: the call stack. The call stack lists any procedures that are currently running, starting with the first procedure you ran. In this case, we ran the AddSalesTotal macro, which in turn called the GetSalesTotal function.

Click the ellipsis button (...) at the top-right corner of the Locals window to open the Call Stack window (you can also choose View > Call Stack or press Ctrl+L). The Call Stack window for our currently running code is shown in Figure 7-29.

Figure 7-29. The Call Stack window shows all functions called in the current process.

You'll notice in Figure 7-29 that the most current procedure call is at the top of the stack. Once GetSalesTotal completes and execution returns to AddSalesTotal, it will be removed from the stack.

8. Close the Call Stack window.

With the code still in break mode, we're going to look at one last method of checking variable values. Then we'll fix our problem and run the code successfully.

The Code Window

The code window also has the ability to show us the values of variables. By simply holding your mouse pointer over any in-scope variable, you can see its value in a tool tip-style pop-up.

In the VBE code window, move the mouse pointer over any variable to see its value. Figure 7-30 shows the mouse hovering over the cell.Value variable, showing its value, N/A.

Figure 7-30. Checking variable values in the code window

Stop the code from running by clicking the Reset button on the toolbar, as shown in Figure 7-31.

Microsoft Visual Basic - 1DebugExample01 .xlsm [break] - [Module2 (Code)]

File Edit View Insert Format Debug Run

Tools Add-Ins Window Help

IBB'HI * ^aAlfl c 'Z 1 > jj

^ M. %< ¡5* 'd? * ® Ln 22, CoJ 14


Project - VBAProject |

Reset |al)

- 1 GetSalesTotal

Figure 7-31. The Reset button stops code execution.

Figure 7-31. The Reset button stops code execution.

Let's fix the error and rerun the code. On Sheetl in Excel, change the value of cell D4 to 0, or leave it blank and run the AddSalesTotal macro again. Figure 7-32 shows Sheetl with the total added after successfully running AddSalesTotal.

Figure 7-32. The worksheet after fixing the data
0 0

Post a comment