Do Loops

Do loops will execute a given block of code repetitively based on the value of a conditional expression. All Do-Loops require the keywords Do and Loop, plus one additional keyword (While or Until) depending on the desired action. The keywords are used to build four basic representations of the Do-Loop. The first two representations use the keyword Until with a conditional statement that determines if, and how many times the code inside the loop executes. With the conditional statement at the end of the loop, the code inside the loop executes at least one time.

'Block of code executes at least once and continues to loop if condition is false.

Loop Until (condition)

When the conditional statement is at the beginning of the loop, the code inside the loop will not execute unless the logic of the conditional statement allows it. When using Until , the code inside the loop executes if the conditional statement is false.

Do Until (condition)

'Block of code executes only if condition is false.

Loop

The next two representations of the Do-Loop use the keyword While with a conditional statement that determines if, and how many times the code inside the loop executes. When While is used, the code inside the loop executes when the conditional statement is true.

'Block of code executes at least once and continues to loop if condition is true.

Loop While (condition)

When deciding on which representation of the Do-Loop to use, ask yourself whether you need the code inside the loop to execute at least once. If you do, then put the conditional at the end. The choice of While or Until depends on the logic of the conditional expression.

Do While (condition)

'Block of code executes only if condition is true.

Loop

Beware of creating loops that never stop repeating, otherwise known as infinite loops. When constructing your Do-Loop, create it with a conditional expression that will change its logical value (true to false and vice versa) at some point during the code's execution within the loop. It is easier to create an infinite loop than you might think. The following example is suppose to find the first occurrence of the string Flintstone in the first column of a worksheet, output a message to the screen, and then quit.

If (Cells(I, "A").Value = "Flintstone") Then

MsgBox ("Yabba Dabba Do! I found a Flintstone in row " & Str(I)) End If I = I + 1

Loop Until (Cells(I, "A").Value = "Flintstone")

You can use the Cells property to return all or just one cell on a worksheet. Using the Cells property without any parameters returns all cells on the worksheet.

ActiveSheet.Cells

To return a specific cell, you can specify a row and column index. For example, the following line of code returns cell Dl.

ActiveSheet.Cells(1,4)

The Cells property is convenient for using inside of loops when the indices for the row and column are replaced with looping variables. Alternatively, you can specify the column parameter with a string.

ActiveSheet.Cells(1,"D")

The loop will always fail for two reasons. First, if the string Flintstone does not appear in the first column of the worksheet, then the loop is infinite because the conditional statement at the end of the loop (Cells(I, "A").Value = "Flintstone") will never be true. Second, even if the string Flintstone does appear in the first column of the worksheet, the output from the MsgBox() function will not appear because the conditional statement at the end of the loop will be true before the conditional statement associated with the If/Then structure.

If you find your program stuck in an infinite loop, use Ctrl-Alt-Break to suspend program execution.

In most cases you can construct a loop with logical expressions that will work with both While or Until, so using one or the other is simply a matter of personal preference. The following Do-Loops have the exact same function, but the first loop uses While and the second uses Until .

If (Cells(I, "A").Value = "Flintstone") Then

MsgBox ("Yabba Dabba Do! I found a Flintstone in row " & Str(I))

Loop While (Cells(I, "A").Value <> "")

If I change the conditional operator to =, then I change the logic of the conditional statement, so I must use the keyword Until to get the same result from the loop.

If (Cells(I, "A").Value = "Flintstone") Then

MsgBox ("Yabba Dabba Do! I found a Flintstone in row " & Str(I)) End If I = I + 1

Loop Until (Cells(I, "A").Value = "")

Both of these loops search the first column for the string Flintstone. Once the desired string is found, a message box outputs a statement with the index of the worksheet row in which the string was found. In both examples, the Do-Loop continues until an empty cell is found. Both loops will execute at least once because the conditional expression is at the end of the loop. Neither loop will be infinite because Excel will always add empty rows to the end of a spreadsheet as more rows of data are added.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment