Error Trapping, Do/While Loops, Loop Errors

1. Error Trapping,Do/While Loops, Loop Errors 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?

2. How to make a random number appear in a cell in Excel: Private SubcmdRandom_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 Spring 2010

3. Filling many cells with the same random number Private SubcmdFillRange_Click() End Sub • So now how can we put a different random number in each cell in a range? CS 105 Spring 2010

4. Filling many cells with a unique random number for each Private SubcmdFillRange_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 Spring 2010

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 Spring 2010

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 counter does not exceed the Final Value, statements in the loop body are executed. CS 105 Spring 2010

7. How many cells will display a number? Private SubcmdRandom_Click() DimintCounter as Integer intCounter = 1 Do WhileintCounter < 11 Cells(intCounter, 1).Value = Rnd intCounter = intCounter + 1 Loop CS 105 Spring 2010

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

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 Spring 2010

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 Spring 2010

11. Loops Make Code Repeat CS 105 Spring 2010

12. Examples of Do Loops (without # of times) Do While [Logical Condition] Add one to running total Loop Do Until[Logical Condition] Add one to running total Loop Do Add one to running total Loop While[Logical Condition] Do Add one to running total Loop Until[Logical Condition] CS 105 Spring 2010

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 Spring 2010

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 Spring 2010

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 Spring 2010

16. Debugging Debug toolbar Step through the procedure Locals window Immediate window CS 105 Spring 2010

17. Debugging, cont. • What do these terms mean? • Breakpoint • Step into • Run-time error CS 105 Spring 2010

18. 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 Spring 2010

19. 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 Spring 2010

20. 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 Spring 2010

21. What is a logic error in coding? • A. Code that does not run • B. Code that runs, but does not get the result that what we wanted!! CS 105 Spring 2010

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 Spring 2010

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

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 Spring 2010

25. Errors – commands in wrong order Dim intCounter asInteger intCounter = 1 ‘why do we make it one? Do Until intCounter > 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 Spring 2010

26. Errors – Termination problems, 'infinite' loops • Loop termination - Some statement in the body of the loop mustaffect 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 Spring 2010

27. Yes, you get an Infinite Loop CS 105 Spring 2010

28. Example 'infinite' loop intCounter = 1 Do Until intCounter > 5 Cells( intCounter, 1).Value = intCounter *2 Loop intCounter = intCounter + 1 CS 105 Spring 2010

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 Spring 2010

30. Do Loop While (could be “fatal”)loop error Const mstrSECRETWORDas String = “dropitnow” Private SubcmdDropAtomBomb_Click() DimintRetriesAs Integer DimstrTryAs String intRetries = 2 Do strTry = InputBox("Enter the password and click OK") intRetries = intRetries - 1 DropBomb‘ Call Sub to Drop the Bomb Loop WhilestrTry <> mstrSECRETWORDANDintRetries >1 CS 105 Spring 2010

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? CS 105 Spring 2010