Testing Debugging
LESSON WORKBOOK:
UserForms Lesson 7 2007.xlsx
This lesson will explore testing and what to do to when code fails and errors occur.
UserForm Test
The purpose of testing
For example, a button click usually results in an immediate action. If the action can be expected to be slow, and the user will see nothing happen for many seconds, then the response the user sees is not as expected. Some way should be found to warn the user that the action will be slow. On the other hand, the expected action must complete successfully. If the button clicked is to enter a record in a database, then it must be entered, and if not, the user must be informed that it did not complete successfully.
Initial State
Start the UserForm. Verify that each control is initialized to the correct value. If not, correct either with the static design or the UserForm_Initialize procedure.
Invalid, Incorrect, and Missing Data
Check TextBoxes, ListBoxes, and ComboBoxes. Validating data is the topic of Lesson 9. If there is a problem, search all code for statements where that data is changed. It may be a code design problem, but it also may be a typo or an unfinished code design change. The most difficult problems are where multiple event handlers touch the same data, and they don’t handle the data the same. In that case, a good programming technique is to move the data handling out of the event handlers and into a standard code module.
Control in Wrong State
Check CheckBoxes, OptionButtons, and ToggleButtons. If there is a problem, search all code for statements where the state of that control is changed.
Control Format Incorrect
Check controls whose appearance is to change during use. Examples might be a control that resizes with new data or the background color changes with state changes or errors. If there is a problem, search all code for statements where the format of the control is changed.
Test checklist
To thoroughly test your UserForm, every control must be tested, every handled event must be generated and the response checked, and appearance must be evaluated. A reasonably complete checklist:
Stepping Through Code
Instructions for opening and setting up VBE were covered in Lesson 2. This lesson focuses on how to use VBE to debug code.
If the steps in Lesson 2 were followed, the Debug and Edit toolbars will be visible and docked at the bottom of VBE, with Debug on the left. These toolbars will be needed below.
Open UserForms Lesson 7 2007.xlsx and the VBE. On the UserFormLessons6thru10 module, find the TestLogFile procedure and click inside it to prepare for the following steps.
Run Sub/UserForm
There are two identical icons, one on the Debug toolbar at the lower left, and one on the Standard toolbar on top. Clicking it runs the macro, along with any Stops, set breakpoints, etc.
Step Into
Click Step Into. A yellow bar highlights the macro name. A second click skips Dim statements and highlights the next step. A third click steps into the chgDirIfNeeded procedure.
Step Out
Rather than stepping through the chgDirIfNeeded procedure, click Step Out to return to the calling subroutine.
Dragging the Yellow Bar
The yellow bar has an arrow in the left margin. The yellow arrow can be clicked and dragged up or down within the current procedure. Care must be taken to drag to a step where conditions will be known. The yellow bar cannot be dragged to a comment or blank line.
One place unexpected results can occur is when dragging from outside a With/End With structure to inside it, references requiring the With to have run will fail.
Another is when dragging from inside a loop to its beginning to rerun the loop. The code must run through any initialization steps. If the code is written so that initialization steps are not immediately above the loop, the initialization steps can be executed from the Immediate Window.
Step Over
Drag the yellow bar up one step and click Step Over. The procedure will run and the code will step to the next statement. If the active statement is not a procedure call, Step Over acts the same as Step Into.
Reset
Code execution can be halted at any point by clicking the Reset icon. There are two, one on the Debug toolbar, and one on the Standard toolbar.
UserForm Debug
VBE (Visual Basic Editor)
The following are methods for locating code problems. MsgBox, Stop, and breakpoints halt code execution. The others let the code run to error or completion. Debugging by stopping code execution has the advantage that the code can be stepped through manually, to see what happens at each step. It has the disadvantage of stopping every time, whether or not the error is about to occur. Sometimes non-stopping methods are needed to localize the error, and stopping methods will be used to investigate conditions prior to the error.
If the code has stopped, on an error or on a Stop statement or on a breakpoint, the current line of code will be highlighted in yellow, before the line has executed. The value of a variable active at that point can be displayed by hovering the cursor over it. A text tip box will pop up with the Value property of the variable. This won’t work for other properties such as the address of a range or the name of ActiveWorkbook or ActiveSheet. For those, type in the Immediate Window Debug.Print and object.property. A “?” in the Immediate Window is shorthand for Debug.Print.
Where temporary statements are inserted in code, don’t indent. This will make clear they are temporary statements, and make them easier to find later for removal.
Sub SomeMacro()
' some code
MsgBox ' temporary statement
' more code
Stop ' temporary statement
' even more code
Debug.Print ' temporary statement
' the rest of the code
End Sub
Stop Statement
Stop
Insert Stop in the code. The statement is saved with the file, and so is in effect when the file is re-opened.
Breakpoint
There are three ways to set a breakpoint, two from toolbars and one direct.
The hand is the breakpoint icon in the Debug and Edit toolbars. Clicking in the left margin of the code pane can also set or reset the breakpoint. Breakpoint can’t be set on a comment or a blank line.
If the breakpoint is set, the line is brown, and there is a brown circle in the left margin.
If execution has stopped on a breakpoint, the yellow arrow will be on top of the brown circle. Space at the beginning of the line will be brown, and the rest of the line will be yellow.
Breakpoints are not saved with the file, and so have to be set again when the file is re-opened.
MsgBox
MsgBox "Just before failure point"
MsgBox is not often used in debug. It may be a quick way to look at one point, but often many points are needed, or the point is in a loop. The need to manually intervene on each occurrence will soon show this to be a very cumbersome way to debug. If MsgBox is just being used to stop the code, use Stop statements or breakpoints instead. If it is being used to display a value, use Debug.Print to show the value in the Immediate Window.
Debug Object
The Debug Object is a very simple object with no properties and only two methods, Assert and Print.
Debug.Assert stops when the expression evaluates to False
Debug.Assert expression
It performs the same as
If expression = False Then Stop
Debug.Print prints the list of text and variables following the statement. The semicolon list separator (;) concatenates the fields it separates, separated by a space if both fields are numeric. The comma list separator (,) starts the second field at a multiple of 14 spaces. The output of Debug.Print appears in the Immediate Window.
A Debug.Print statement ending in comma or semicolon does not finish the output line.
Sub temp1()
Dim x%
For x% = 1 To 5
Debug.Print x%; ' do not complete the output line
Next x%
Debug.Print ' complete the output line
End Sub
The output of the above is
Log File
See sheet Lesson 7 in UserForms Lesson 7 2007.xlsx. These procedures are already in Module UserFormLessons6thru10. Be careful when logging from inside loops. A very large file can be created very quickly.
Sub TestLogFile()
Dim fName$, x%
chgDirIfNeeded ' logfile goes in this folder
fName$ = "Lesson7 Log_File.txt"
For x% = 1 To 10
LogTransactionToLogFile fName$, x%, x% + 10, x% * x%
Next x%
End Sub
Sub LogTransactionToLogFile(fName$, n1, n2, n3)
Close #1 ' just to be sure it's closed
Open fName$ For Append As #1 ' will create new file if needed
Print #1, "Logfile Demo", "x%=" & n1, n2, n3
Close #1
End Sub
Watch Window
The Watch Window is for detecting when a cell changes to a target value. Since the cells of interest to a UserForm are linked to controls by the ControlSource property, their values are also available to the code by the Value property. Therefore Watch Window won’t be covered in this lesson.
Notes
Which Debug Method to Use?
Breakpoint is the quickest to use, but is not saved with the file.
Stop is next quickest, but is unconditional. It is saved with the file.
Debug.Assert is slower to implement because the expression has to be written to return a True or False.
Debug.Print takes time to implement, but is most informative because any information may be included in the output. The Debug.Print output in the Immediate Window survives after the UserForm closes, but not the closing of Excel. The Immediate Window buffer is of limited size, and earlier Debug.Print outputs are deleted as buffer memory is needed.
Log files survive after the UserForm closes, and also the closing of Excel. Log files are needed if the Immediate Window buffer is not large enough to hold all the Debug.Print output..
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.