Error Trapping, Do/While Loops, Loop Errors

1. How do we repeat lines of code over and over? What is an “infinite loop”? What is an “off-by-one” error? What is so dangerous about putting the condition at the bottom? Error Trapping,Do/While Loops, Loop Errors

2. How to make a random number appear in a cell in Excel: Private Sub cmdRandom_Click() Cells(1,1).Value = Rnd End Sub • This code places a random number in cell A1. • The function Rnd returns a random number between 0 and the upper limit specified. If you don’t put in an upper limit, VB assumes it is .9999 but stops short of one! • Note: Every time it is called, it should generate a different number. CS 105 Fall 2007

3. Filling many cells with the same random number Private SubcmdFillRange_Click() Range(“A1:B4").Value = Rnd End Sub • This code places the same random number in every cell in a range. • So now how can we put a different random number in each cell in a range? CS 105 Fall 2007

4. Filling many cells with the a unique random number for each Private Sub cmdFillRange_Click() Cells(1,1)Value = Rnd Cells(2,1)Value = Rnd Cells(3,1)Value = Rnd Cells(4,1)Value = Rnd End Sub • This code places a different random number in every cell in a range. • So now how can we put a different random number in each cell in a range without tediously writing each one? CS 105 Fall 2007

5. Do Loop This loop places a random number in a cell then moves to the next cell, where it does the same thing intCounter = 1 Do While intCounter < 11 Cells(intCounter, 1).Value = Rnd intCounter = intCounter + 1 Loop CS 105 Fall 2007

6. The “loop index” or counter • Each trip through the Body of the Loop is called an Iteration of the Loop. • The counter is a variable that is growing with each iteration. • If the counterdoes not exceed theFinal Value, statements in the loop body are executed. CS 105 Fall 2007

7. How many cells will display a number? Private Sub cmdRandom_Click() Dim intCounter as Integer intCounter = 1 Do While intCounter < 11 Cells(intCounter, 1).Value = Rnd intCounter = intCounter + 1 Loop CS 105 Fall 2007

8. Process Explained The counter starts at 1, so the iteration of the loop looks like this: Cells(1,1).Value = Rnd The code adds 1 to the counter. So the next time through, Cells(2,1).Value = Rnd and so on, stopping when intCounter = 11 CS 105 Fall 2007

9. The Do Loop • NOTE: Sometimes you don't want to repeat the body of a loop a predetermined number of times • You can continue to repeat the body of a loop while some condition continues to be true, or until some condition becomes true. • (a password, for example) • You can test the condition at the beginning of each iteration and/or at the end of each iteration. CS 105 Fall 2007

10. Flowchart of a Do/While Loop Start Initialize starting condition • 1st, check to see if loop should start • If so, execute code • Then test again • And so on… Is loop condition true? TRUE Execute statements in loop FALSE End CS 105 Fall 2007

11. Loops Make Code Repeat CS 105 Fall 2007

12. Examples of Do Loops (without # of times) Do WhileRange( ).Value <> Empty Add one to running total Loop Do Until Range( ).Value = Empty Add one to running total Loop Do Add one to running total Loop While Range( ).Value <> Empty Do Add one to running total Loop Until Range( ).Value = Empty

13. Do While Loop ‘we use the variant data type with IsNumeric Private Sub cmdWage_Click() Dim vntWage as Variant vntWage = InputBox("Enter Wage", "Wage", 1000) Do While IsNumeric(vntWage) = False MsgBox "Wage must be a number” vntWage = InputBox("Enter Wage", "Wage", 1000) Loop End Sub CS 105 Fall 2007

14. Do Until Loop Dim vntWage as Variant vntWage = InputBox("Enter Wage", "Wage", 1000) Do Until IsNumeric(vntWage) = True MsgBox "Wage must be a number” vntWage = InputBox("Enter Wage", "Wage", 1000) Loop CS 105 Fall 2007

15. Do Until Loop with Counter Dim vntWage as Variant Dim intCounter as Integer vntWage = InputBox("Enter Wage", "Wage", 1000) Do Until IsNumeric(vntWage) = True Or intCounter > 2 MsgBox "Wage must be a number” vntWage = InputBox("Enter Wage", "Wage", 1000) intCounter = intCounter + 1 Loop CS 105 Fall 2007

16. At the start, vntWage is Empty and intCounter is 0 CS 105 Fall 2007

17. Just for fun, we type the word “sample” into the InputBox CS 105 Fall 2007

18. Another InputBox comes up, and this time we enter “fun” . When the code reaches the word “Loop,” vntWage is “fun” and the counter is now 1 (0 +1) CS 105 Fall 2007

19. Watching values grow First iteration: intTotal = 0 intIndex = 1 Do While intIndex < 4 intTotal = intTotal + intIndex intIndex = intIndex + 1 Loop Second iteration: Do While intIndex < 4 intTotal = intTotal + intIndex intIndex = intIndex + 1 Loop 0 1 1 intTotal= 1 intIndex= 2 Iterations= 1 2 1 2 1 2 3 3 2 intTotal= 3 intIndex= 3 Iterations= 2 CS 105 Fall 2007

20. Working it through Dim intIndex As Integer Dim intTotal As Integer intIndex = 1 Do While intTotal < 4 intTotal = intTotal + intIndex intIndex = intIndex + 1 Loop is equivalent to intTotal = intTotal + 1 intTotal = intTotal + 2 intTotal = intTotal + 3 CS 105 Fall 2007

21. Working it through Dim intIndex As Integer Dim intTotal As Integer intTotal = 0 intIndex = 1 Do While intIndex < 4 intTotal = intTotal + intIndex intIndex = intIndex + 1 Loop 1 = 0 + 1 After the first interation, 3 = 1 + 2 intTotal is 1, then 3, 6 = 3 + 3 and finally 6. intIndex is 4 when the code stops. The loop had 3 iterations, when intIndex was 1, 2, and 3 CS 105 Fall 2007

22. Errors – off by one Loops are prone to logic errors. • Off-by-one errors • one too few loop passes (e.g. <was used instead of <= ) • one too many loop passes (e.g. <= was used instead of < ) CS 105 Fall 2007

23. Example -- off by one If you wanted 3 tries, you goofed! vntGuess = 0 intNumTries =3 Do WhileintNumTries > 1 vntGuess=InputBox(“Enter your guess”) Cells(intNumTries, 2).Value = vntGuess intNumTries = intNumTries – 1 ‘add If statement to check guess Loop CS 105 Fall 2007

24. Errors -- Never executes intNumTries = 2 Do While intNumTries >= 3 Range(“A2”).Value = Rnd Loop 2> 3 is FALSE , so the loop never executes—the loop has no iterations CS 105 Fall 2007

25. Errors – commands in wrong order DimintCounterasInteger intCounter = 1 ‘why do we make it one? Do UntilintCounter > 100 Cells( intCounter, 1).Value = intCounter * 2 intCounter = intCounter + 1 Cells(intCounter, 1).Interior.ColorIndex = 6 Loop When did you change intCounter? The first cell will not have a changed background color. CS 105 Fall 2007

26. Errors – Termination problems, 'infinite' loops • Loop termination - Some statement in the body of the loop must affect the test condition and cause termination. • Use Ctrl + Break to get out! • If the loop termination condition is never reached it becomes an infinite loop CS 105 Fall 2007

27. Example 'infinite' loop intCounter = 1 Do Until intCounter > 5 Cells( intCounter, 1).Value = intCounter *2 Loop intCounter = intCounter + 1 CS 105 Fall 2007

28. Another Infinite Loop CS 105 Fall 2007

29. Checking condition at the bottomof the loop Dim intCounter as Integer intCounter = 1 ‘why do we make it one? Dim intCounter as Integer intCounter = 1 Do Cells(intCounter, 1).Value = intCounter *2 intCounter = intCounter + 1 Loop UntilintCounter > 2 ‘how many times does this loop execute? CS 105 Fall 2007

30. Do Loop While (could be “fatal”)loop error Const mstrSECRETWORD as String = “dropitnow” Private Sub cmdDropAtomBomb_Click() Dim intRetries As Integer Dim strTry As String intRetries = 2 Do strTry = InputBox("Enter the password and click OK") intRetries = intRetries - 1 (Code That Drops The Atomic Bomb) Loop While strTry <> mstrSECRETWORD AND intRetries >1 CS 105 Fall 2007

31. To Summarize: • Loops can be based on conditions > = < <> • What is an “infinite loop”? • What is an “off-by-one” error? • What is so dangerous about putting the condition at the bottom? • Your moment of Zen—the Groundhog Day loop http://youtube.com/watch?v=l4TYRLjpjYI CS 105 Fall 2007