Indefinite Looping: Do Until 0) Introduction: 1) Indefinite Looping with Do Until 2) The Step Option in For Loops: 3) Three other Indefinite Loops 4) Special VB Boolean Functions: 5) Examples of Indefinite Looping: 6) Indefinite Looping in the Cannon Shell Worksheet: 7) Summary: 0) Introduction: In this lecture we look at another statement in VB, specifically the Do Until loop, which is a form of "indefinite" looping. The other loop that we know, the For loop, is called a "definite" loop because once we know the values for the lower and upper bounds of the For loop, we know how many times it will go around the loop. With an "indefinite" loop, it goes around until some BOOLEAN condition is met, so at the start of the loop we don't really know how many times it will go around. We will also see three other, but less useful forms of indefinite looping, and study the Step option in For loops (for stepping by values other than +1 when Next is execute). We will study a few examples of code in which indefinite looping is needed 1) Indefinite Looping: Again, a For loop is a "definite" loop. If we know the lower- and upper-bound for the index variable, we know how many times the body of the loop will executed. For example, "For i = 1 to 5" will execute 5 times; generally, "For i = a to b" will execute b-a+1 times (it firstr executes for value a, and then for b-a more values). In an indefinite loop, we do not know how many times the body of the loop will execute. It depends on the data that the loop is processing. The general form of the Do Until loop (the most common of the indefinite loops) is. Do Until boolean-formula statement statement ... statment Loop Again, the statements inside the loop are called the "body" of the loop. VB executes this statement by first evaluating the boolean-formula: if it is TRUE the loop is finished; if it is FALSE VB executes the statements in the body of the loop. After VB executes the last statement in the body, it reaches the Loop statement (similar to the Next statement in For loops), at which time it goes back to the Do Until and re-calculates the boolean-formula. Again, if it is TRUE the loop is finished; and if it is FALSE VB executes the statements in the body of the loop again. So every time VB decides to execute the loop, after it executes the last statement in the body, it reaches Loop, at which time it goes back and re-calculates the boolean-formula, possibly executing the body of the loop more times. VB continually (re-)executes the body of the loop until the boolean-formula is finally TRUE, at which point is start executing statements AFTER the Loop statement. Note the the Loop statements means, "loop back to test the BOOLEAN formula again". Students sometimes initially confuse the If statement and Do Until statement, because both have boolean-formulas in them. Recall that an If statement uses the boolean-formula to decide which of two sequences of statements to execute. The Do Until loop repeatedly executes a sequence of statements until the boolean-formula calculates TRUE The Do Until loop is more general than the For loop: we can use the Do Until loop to write a For loop (but one that is verbose). The For loop For i = a To b statements Next i is equivalent to the the Do Until Loop i = a Do Until i > b statements '(same statements in For loop above) i = i + 1 Loop In the Do Until loop, we explicitly initialize the index-variable to a, the lower-bound of the For loop; then we loop until this index-variable exceeds b (the upper-bound of the For loop). Each iteration of the loop executes some statements (the body of the loop), followed by incrementing the index-variable by one, and then "looping" to recheck whether this now incremented value exceeds b (and repeating this process until it finally does). Hopefully each of these loops can help you better understand the other. We will see the need for an indefinte loop in some of the problems that we solve below. An "infinite loop" is one in which the boolean formula in an indefinite loop is never TRUE. Here is an example. i = 0 Do Until i > 10 MsgBox i Loop Because we do not explicitly increment i inside the loop body, i remains unchanged during each iteration, so i stays at the value 0, and it never exceeds 10. Typically in an indefinite loop, each iteration changes some variables in the boolean-formula so that it closer to being TRUE. There are NO infinite DEFINITE loops. 2) The Step Option in For Loops: In fact, the actual FOR loop allows us to specify a Step. For example, if we wrote For i = 1 To 5 Step 2 MsgBox i Next i The message boxes would display 1, 3, and 5. This is equivalent to the indefinite loop i = 1 Do Until i > 5 statements i = i + 2 'Note increments i by 2 Loop Also, we can use negative Steps to write a For loop that counts downward For example, if we wrote For i = 5 To 1 Step -1 MsgBox i Next i The message boxes would display 5, 4, 3, 2, and 1. This is equivalent to the indefinite loop i = 5 Do Until i < 1 statements i = i - 1 'Note decrement i by 1 Loop Generally, we use a For loop when we can, and a Do Until loop when we must (when a For loop isn't possible) 3) Three other Indefinite Loops There are 3 more variants of indefinite looping 1 )Do While boolean-formula statement statement ... statement Loop This variant executes the body of the loop WHILE the boolean-formula calculates TRUE (STOPPING the first time it is FALSE). Compare this to the Do Until loop, which executes the body of the loop UNTIL the boolean-formula calculates TRUE (CONTINUING while it is FALSE). Note that "Do While b-f" can be written equivalently as "Do Until NOT(b-f)" using the NOT operator to negate the value of the boolean-formula. Since it is normally easier to write a boolean-formula which is TRUE when you want the loop to stop, we prefer using the Do Until form. 2 )Do statement statement ... statement Loop Until boolean-formula This variant always executes the body of the loop at least once, and then checks the boolean-formula after each execution, stopping when the boolean-formula is first TRUE. Likewise 2 )Do statement statement ... statement Loop While boolean-formula This variant always executes the body of the loop at least once, and then checks the boolean-formula after each execution, stopping when the boolean-formula is first FALSE. Again, "Loop While b-f" can be written equivalently as "Loop Until NOT(b-f)" using NOT to negate the value of the boolean-formula. Most loops in programming should test a boolean-formula BEFORE the statements in their body are executed, even the first time, so the Do Until loop is still the most common indefinite loop in programming. And, if a loop is definite, then writing it as a For loop is more compact and less prone to making an error (like forgetting to increment the index-variable, which one has to do in an indefinite loop). So, for simple definite (sometimes called "counting loops") use a For loop; for indefinite loops using a Do Until loop. Don't worry about any of the other forms -->Using Step Into and a Locals Window, execute the DisplayLoop and --> DisplayForLoop subroutines; verify that you understand how these --> loops work 4) Special VB Boolean Functions: The subroutines below make use of some special boolean functions usable in VB code. We will call these function in Do Until loops and If statements. All take Cell/Range/Selection values as arguments and calculate boolean values IsEmpty : determines whether or not cell/range contains no values IsNumeric: determines whether or not cell/range contains numeric value IsError : determines whether or not cell/range contains error For example, we might use the boolean-formula IsEmpty( Cells(i,1).Value ) We can also write the boolean-formula Not IsEmpty( Cells(i,1).Value ) to compute the opposite of IsEmpty. 5) Examples of Indefinte looping: In this section we will use the indefinite Loop Until to solve problems not solvable with the definite For loop. We will be processing an unknown/arbitrary amount of data in a column. First, the AddColumnA subroutine adds up and displays in a MsgBox all the values in Column A. There can be any number of values in this column, with the bottom cell containing -1. This is called a "sentinel" value: it is marks the end of the data and should not participate in the sum. Sub AddColumnA() result = 0 i = 1 Do Until Cells(i, 1).Value = -1 result = result + Cells(i, 1).Value i = i + 1 Loop MsgBox (i - 1) & " Values; Sum = " & result End Sub Note that i is initialized to 1 (for row 1) and incremented by 1 inside the Do Until loop. Likewise, result is initialized to 0 and incrementd by Cells(i,1).Value inside the loop. This process continues until the value in the row we "would" add next is the sentinel. When the loop finishes, the subroutine displays the resulting sum in a MsgBox. Also, i will be one bigger than the actual number of values added (since it is referring to the sentinel row when the loop terminates). In fact, we could use a non-numeric value as a sentinel, changing the first line in the loop to: Do Until Cells(i, 1).Value = "No More", and replacing -1 in the Column A by the text No More. -->Using the Locals Window, Step into AddColumnA subroutine and see how it --> works -->Try it with both more and less data in the column (always placing -1 one --> past the last cell to process Second, the AddColumnB subroutine adds up and displays in a MsgBox all the values in Column B. Again, there can be any number of values in this column, but now the sentinel is just an empty cell. The only change between AddColumnA and AddColumnB is: Do Until IsEmpty(Cells(i, 2).Value) which uses the special boolean function introduced above (and the fact taht we acess Cells(i,2).Value because column B is the 2nd column. Sub AddColumnB() result = 0 i = 1 Do Until IsEmpty(Cells(i, 2).Value) result = result + Cells(i, 2).Value i = i + 1 Loop MsgBox (i - 1) & " Values; Sum = " & result End Sub -->Using the Locals Window, Step into AddColumnB subroutine and see how it --> works -->Try it with both more and less data in the column (always placing -1 one --> past the last cell to process Finally, the AddColumnC works like AddColumnB but it uses an If statement to ignore any cells that contain non-numerc values; it also needs another variable to keep track of how many numbers it actually added in the column, incrementing this number whenever it increments the result. Sub AddColumnC() result = 0 numbers = 0 i = 1 Do Until IsEmpty(Cells(i, 3).Value) If IsNumeric(Cells(i, 3).Value) Then result = result + Cells(i, 3).Value numbers = numbers + 1 End If i = i + 1 Loop MsgBox numbers & " Values; Sum = " & result End Sub To write a complicated subroutine like this one, we can start with a simpler subroutine solving a simpler and smaller problem, and extend it by adding the needed new specifications. In fact, let's generalize this subroutine once more. The subroutine AddColumn will add up all the values in the column below the selected cell, and put the sum one beyond the last value. Sub AddColumn() srow = Selection.Row scol = Selection.Column result = 0 i = srow Do Until IsEmpty(Cells(srow, scol).Value) If IsNumeric(Cells(i, scol).Value) Then result = result + Cells(i, scol).Value End If i = i + 1 Loop 'Cells(i,scol).Value is empty (we finished the loop) Cells(i,scol) = result End Sub 6) Indefinite Looping in the Cannon Shell Worksheet: As a final example we will return to the Cannon Shell problem (see the Cannon Shell worksheet in the workbook for this lecture). Using a Do Until loop, the following code fills in the Time, X(Time), and Y(Time) columns in the worksheet, with formulas -by copying/pasting,until the shell comes back to earth (the value in Column C, which represents Y(Time), becomes negative). We need to have the initial conditions in row 16, and the general formulas in row 17. We can adjust the sliders, and the chart will change as before, but we might find the curve doesn't go all the way down to the ground (when we increas the speed or angle). Pressing Update will start at line 17 and fill down the formula as far as necessary. Notice Do Until Cells(i, 3).Value < 0 loops until Column C row i is negative Range("A" & i & ":C" & i).Copy copies the formulas in row i, column A-C i = i + 1 increments to the next row, i+1 Range("A" & i & ":C" & i).Select selects columns A-C in the next row ActiveSheet.Paste paste copied formulas into selected cells How did I find out how to copy and paste formulas in VB: I recorded a macro and then generalized/edited the VB code it wrote. Here is the full subroutine, which appears in Module2 and can be run by press the Update button on the Cannon shell worksheet. Sub FillCannon() i = 17 ' Row 16 Initial Conditons; Row 17 First General Formula Do Until Cells(i, 3).Value < 0 Range("A" & i & ":C" & i).Copy i = i + 1 Range("A" & i & ":C" & i).Select ActiveSheet.Paste Loop Range("A1").Select 'Select A1 so Excel will show the chart at the top End Sub Still to Write: See FillCannon subroutine Formula Filling via Subroutine Cannon Shell: keep copying formulat until back to ground Macro: select copy select paste special formula ------------------------------------------------------------------------------ 8) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel. Understand how to write indefinite Do Until loops, and understand how they are executed (being able to write the Do Until loop equivalent of a For loop). Know the special VB Boolean functions, and be able to use them in Do Until loops and If statements.