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 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
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 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.
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.
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.
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 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.
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 called procedure CountRange
VBA For Excel Related Tutorials
|Microsoft Excel Tutorial||Microsoft Word Tutorial|
|Microsoft Powerpoint Tutorial||Microsoft Entity Framework Tutorial|
|Microsoft Azure Tutorial||Microsoft Project Tutorial|
|Advanced Excel Charts Tutorial||Advanced Excel Functions Tutorial|
|Excel Dashboards Tutorial||Excel Data Analysis Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
VBA For Excel Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
Vba For Excel Tutorial
Computers And Vba
Recorded Excel Macros
Introduction To Vba
Introduction To Objects
Decisions In Vba
Debugging And Testing
Subs And Functions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.