The Debug tools - VBA For Excel

The VBE contains some powerful tools for debugging your program. One of the most important of these is the provision of a Watch window combined with the setting of Breakpoints.This the facilities available on the Debug toolbar.

The Debug toolbar

The Debug toolbar

The Watch window (the glasses icon)

The Watch window lets you monitor one or more of the variables or cells or whatever as the program runs.Clicking this icon or choosing Add Watch from the Debug menu will prompt the user with a dialog box. In the case of the validateWeekSales program we are interested in watching the range variable cell, since this is going to contain the values of the input used in the tests. As you can see in Figure, this has been entered in the Expression box (all other list boxes and radio buttons have been left with the default values unchanged).

The Add Watch window

The Add Watch window

Click OK and you will see a split horizontal window VBE display. Notice that the bottom window (title Watches)is the Watch window,and displays four columns:

  • The first column displays the name of the expression (i.e. cell).
  • The second column displays the value. Before running the macro it is shown to be < Out of Context > since it does not know what its value is until the program is running.
  • The third column displays the type. Again, the current value is Empty since a value will not be assigned until the program is running.
  • Finally, the fourth column displays the context. The display tells us that the context is othersalesmanmacros.validateWeekSales.In other words,this macro is stored in the othersalesman macros module and is called validate Week Sales. Having set the watch we now need to set a breakpoint so that we can pause during each loop excution to see what is happening to the cell.
  • The Watches window before running the program

    The Watches window before running the program

Toggle Breakpoint:(the hand icon)

There is little point in opening a Watch window, unless you can slowthe program operation down enough to get a chance to see what is happening during program execution. A breakpoint does exactly that: it sets a program execution break at a line of code as a point at which the program will pause. It is a forced execution break in the program, and you can set breakpoints wherever you want. Select Toggle Break point on the Debug menu and choose breakpoints carefully, because if,for example, you are Watching a cell in loop,then clearly a breakpoint wouldhaveto be positioned somewhere inside the loop,otherwise it would not achieve its objective.To remove the breakpoint, select it and use Debug > Toggle Breakpoint.

The Break point line(With cell.Interior)and the Watch window.

The Break point line(With cell.Interior)and the Watch window.

With regard to the validateWeekSales macro, a good place to set a breakpoint would be on line If Not IsNumeric(cell) Then. This is because this line is inside the loop and therefore will stop during each loop execution.To turn this line into a b reakpoint, position the cursor over this line and select Toggle Breakpoint from the Debug menu. Note that the line w ill be displayed with a brown background colour, indicating that it is a breakpoint.the break point line in the VBE module window and the Watch window during the first program loop. Notice that the cell value is 54 as expected.

Tracing

The process of executing one line at a time is known as tracing or code stepping. The VBA debugger provides some facilities relating to tracing. These are:Step Into, Step Over and Step Out.

Step Into

The Step Into button on the Debug toolbar lets you step through the statements in the procedure one at a time in order to see the sequence in which the statements are executed. Click the Step Into button or use the option on the Debug menu to start, then continue to click Step Into to step through the macro. Each time that you click this button the statement that is about to be executed is highlighted.

Step Over

Step Into works by stepping through a program on line at a time in sequence. However, if one sub calls another, then you might want the debugger to step over the called sub. This can be done by using the Step Over option. This means that it will execute the called sub all at once, without stepping through it. As an example, consider the two procedures that are named GetRange and CountRange as shown in Listings. The purpose of the GetRange procedure is to enable the user to enter a cell range, and the procedure would select the cell range on the worksheet. Moreover, the procedure would then call another procedure called CountRange whose purpose would be to output to the user the number of cells in the selected range.

To see how this works,if we examine the GetRange procedure, we see that the user range is obtained by using a range variable, Rng,stored from the InputBox function (line 4).Line 5 then checks to see that the user has selected a range using the If statement. If not, then an Operation Cancelled message appears, otherwise the selected range is highlighted on the worksheet (line 8),and then on line 9, we can see that the CountRange procedure is called. This means that program control will immediately transfer directly to the CountRange procedure.

At this point, it is said to step into CountRange, and the If TypeName(Selection) = "Range" Then line will be executed, followed by the other lines in the procedure in the sequence. Once this procedure has been completed, control will then return to the GetRange procedure to the point following the transfer to CountRange, i.e., line 10.If we were to click the Step Into button successively during execution, then we would step into each line of both procedures. However, if we were to click the Step Over button, then it would step over any called procedures during execution.You might use this option, if you are confident about the called procedures,but wish to debug the main procedure.

Step Out

If you want to discontinue stepping through a sub, you can click Step Out which will skip through the remaining steps. You would normally use this when perhaps what you have seen has enabled you to reach a conclusion on the nature of the problem.

The calling procedure GetRange

The calling procedure GetRange

The called procedure CountRange

The called procedure CountRange


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

VBA For Excel Topics