Do Loops DoWhile and DoUntil

Visual Basic has two types of Do loop statements that repeat a sequence of statements either as long as or until a certain condition is true.

The Do...While loop lets you repeat an action as long as a condition is true. This loop has the following syntax:

Do While condition statement1 statement2 statementN


When Visual Basic encounters this loop, it first checks the truth value of the condition. If the condition is false, the statements inside the loop are not executed. Visual Basic will continue to execute the program with the first statement after the Loop keyword. If the condition is true, the statements inside the loop are run one by one until the Loop statement is encountered. The Loop statement tells Visual Basic to repeat the entire process again, as long as the testing of the condition in the Do While statement is true.

Let's now see how you can put the Do.. .While loop to good use in Microsoft Excel. In Chapter 4, you learned how to make a decision based on the contents of a cell. Let's take it a step further and see how you can repeat the same decision for a number of cells. The decision is to apply bold formatting to any cell in a column, as long as it's not empty.

1. Open a new workbook and name it Chap06.xls.

2. Switch to the Visual Basic Editor screen, and change the name of the new project to Repetition (Chap06.xls).

3. Insert a new module into the Repetition project and rename it DoLoops.

4. Enter the following procedure:

Sub ApplyBold()

Do While ActiveCell.Value <>"" ActiveCell.Font.Bold = True ActiveCell.Offset(1, 0).Select

Loop End Sub

5. Enter any data (text or numbers) in cells A1:A7.

6. Select cell A1.

7. Choose Tools | Macro | Macros. In the Macro dialog box, doubleclick the ApplyBold procedure (or highlight the procedure name and click Run).

When you run the ApplyBold procedure, Visual Basic first evaluates the condition in the Do While statement—ActiveCell.Value <>"". The condition says: perform the following statements as long as the value of the active cell is not an empty string (""). Because you have entered data in cell A1 and made this cell active (see step 6 above), the first test returns true. So Visual Basic executes the statement ActiveCell.Font.Bold = True, which applies bold formatting to the active cell. Next, Visual Basic selects the cell in the next row (see the Offset property in Chapter 2). Because the statement that follows is the Loop keyword, Visual Basic returns to the Do While statement and again checks the condition. If the newly selected active cell is not empty, Visual Basic repeats the statements inside the loop. This process continues until the contents of cell A8 are examined. Because this cell is empty, the condition is false, so Visual Basic skips the statements inside the loop. Because there are no more statements to execute after the Loop keyword, the procedure ends.

Let's look at another Do...While loop example. Curious to find out how to display today's date and time in Microsoft Excel's status bar? Here's how to do this for ten seconds:

Sub TenSeconds() Dim stopme stopme = Now + TimeValue("00:00:10") Do While Now < stopme

Application.DisplayStatusBar = True Application.StatusBar = Now Loop

Application.StatusBar = False End Sub

In the above procedure, the statements inside the Do.While loop will be executed as long as the time returned by the Now function is less than the value of the variable called stopme. The variable stopme holds the current time plus ten seconds (see the online help for other examples of using the built-in TimeValue function).

The statement Application.DisplayStatusBar tells Visual Basic to turn on the display of the status bar. The next statement places the current date and time in the status bar. While the time is displayed (and this lasts only ten seconds), the user cannot work with the system (the mouse pointer turns into the hourglass). After the ten seconds are over (that is, when the condition Now < stopme evaluates to true), Visual Basic leaves the loop and executes the statement after the Loop keyword. This statement returns the default status bar message "Ready."

A loop is a programming structure that causes a section of a program code to execute repeatedly. VBA provides several structures to implement loops in your procedures: Do...While, Do...Until, For...Next, For...Each, and While...Wend.

The Do...While loop has an alternative syntax that lets you test the condition at the bottom of the loop in the following way:

Do statement1 statement2 statementN Loop While condition

When you test the condition at the bottom of the loop, the statements inside the loop are executed at least once. Take a look at this example:

Sub SignIn()

Dim secretCode As String Do secretCode = InputBox("Enter your secret code:") If secretCode = "sp1045" Then Exit Do Loop While secretCode <> "sp1045" End Sub

Notice that by the time the condition is evaluated, Visual Basic has already executed the statements one time. In addition to placing the condition at the end of the loop, the SignIn procedure shows how to exit the loop when a condition is reached. When the Exit Do statement is encountered, the loop ends immediately.

Tip 6-2: Avoid Infinite Loops

If you don't design your loop correctly, you get an infinite loop—a loop that never ends. You will not be able to stop the procedure by using the Escape key. The following procedure causes the loop to execute endlessly because the user forgot to include the test condition:

Sub SayHello()

MsgBox "Hello."


To stop the execution of an infinite loop, you must press Ctrl+Break. When Visual Basic displays the message "Code execution has been interrupted," click End to end the procedure.

Another handy loop, Do...Until, allows you to repeat one or more statements until a condition becomes true. In other words, Do.Until repeats a block of code as long as something is false. Here's the syntax:

Do Until condition statementl statement2 statements


Using the above syntax, you can now rewrite the previously written ApplyBold procedure in the following way:

Sub ApplyBold2()

Do Until IsEmpty(ActiveCell) ActiveCell.Font.Bold = True ActiveCell.Offset(1, 0).Select

Loop End Sub

The first line of this procedure says to perform the following statements until the first empty cell is reached. As a result, if the active cell is not empty, Visual Basic executes the two statements inside the loop. This process continues as long as the condition IsEmpty(ActiveCell) evaluates to false. Because the ApplyBold2 procedure tests the condition at the beginning of the loop, the statements inside the loop will not run if the first cell is empty. You will get the chance to try out this procedure in the next section.

Similar to the Do.While loop, the Do.Until loop has a second syntax that lets you test the condition at the bottom of the loop:

Do statementl statement2 statements Loop Until condition

If you want the statements to execute at least once, place the condition on the line with the Loop statement no matter what the value of condition.

Let's try out the following example, which deletes each empty sheet from a workbook.

1. Enter the DeleteBlankSheets procedure, as shown below, in the DoLoops module that you created earlier: Sub DeleteBlankSheets() Dim myRange As Range Dim shcount As Integer shcount = Worksheets.Count Do

Worksheets(shcount).Select Set myRange = ActiveSheet.UsedRange If myRange.Address = "$A$1" And _ Range("A1").Value = "" Then

Application.DisplayAlerts = False

Worksheets(shcount).Delete Application.DisplayAlerts = True

End If shcount = shcount - 1 Loop Until shcount = 1

End Sub

2. Manually insert three worksheets into the current workbook. On one of the sheets, enter some data in cell A1. On another sheet, enter some data in cells B2 and C10. Do not enter any data on the third sheet.

3. Run the DeleteBlankSheets procedure.

When you run this procedure, Visual Basic deletes the selected sheet whenever two conditions are true—the UsedRange property address returns cell A1 and cell A1 is empty. The UsedRange property applies to the Worksheet object and contains every non-empty cell on the worksheet, as well as all the empty cells that are among them. For example, if you enter something in cells B2 and C10, the used range is $B$2:$C$10. If you later enter data in cell A1, the UsedRange will be $A$1:$C$10. The used range is bounded by the farthest upper-left and farthest lower-right non-empty cell on a worksheet.

Because the workbook must contain at least one worksheet, the code is executed until the variable shcount equals one. The statement shcount = shcount-1 makes sure that the shcount variable is reduced by one each time the statements in the loop are executed. The value of shcount is initialized at the beginning of the procedure with the following statement:


Notice also that when deleting sheets, Excel normally displays the confirmation dialog box. If you'd rather not be prompted to confirm the deletion, use the following statement:

Application.DisplayAlerts = False

When you are finished, turn the system messages back on with the following statement:

Application.DisplayAlerts = True

Tip 6-3: Counters

A counter is a numeric variable that keeps track of the number of items that have been processed. The DeleteBlankSheets procedure shown above declares the variable shcount to keep track of sheets that have been processed. A counter variable should be initialized (assigned a value) at the beginning of the program. This ensures you always know the exact value of the counter before you begin using it. A counter can be incremented or decremented by a specified value. See other examples of using counters with the For...Next loop later in this chapter.

+1 0


Post a comment