The Do – Loop Until Statement | VBA Iteration Statements

In everyday life, we often need to repeat certain actions. For instance, the French celebrate the storming of the Bastille every 14^th of July. Likewise, your program may need to repeat one or more statements several times. Iteration statements like the Do – Loop Until statement make such looping possible in VBA.

Introducing the Do – Loop Until Statement

The Do Until – Loop statement sets up an indefinite loop that repeats a group of statements until a condition is met. In other words, it loops through a group of statements until a condition is True (or while it is False).

It is almost identical to the Do Until – Loop statement. But, unlike that statement, it tests its control expression (numeric or string type) after executing an iteration, not before.

So, it always runs its loop body statements at least once. And that is regardless of the Boolean that the control expression returns. Also, the loop body statements only run again if the expression returns False.

So, even if the very first expression test succeeds (i.e., returns True), the loop body’s statements run at least once.

The header image above shows the Do – Loop Until statement’s syntax. Moreover, the flowchart below illustrates its logic flow.

Salient Points on Usage

There are several vital things to note about the Do – Loop Until statement:

• The Do statement runs the loop body statements, without testing the control expression. Afterward, the Loop Until statement checks that the control expression evaluates to False. If so, the execution point returns to the Do statement.
• The cycle repeats until the control expression evaluates to True. Moreover, the loop also ends when execution encounters an Exit Do statement in the loop body. Those are usually placed in Selection statements, e.g. If – Then and Select Case statements.
• Once the loop ends, the execution point moves to the statement right after the Loop Until statement.
• It is possible that the control expression never returns a Boolean that stops the loop. Also, execution may not meet an Exit statement in the loop body. In that case, an endless or infinite loop runs. However, you can end such loops by pressing the Esc key or the Ctrl + Break keyboard combination at runtime.
• You can nest these loops within one another, in fact, VBA allows up to 16 levels of nesting. In that case, an Exit Do statement in a lower-level loop sends control to the line right after that level’s Loop Until statement.

The Do – Loop Until Statement in Action

The image below lists the last ten winners of the U.S. Open women’s singles championship. They are listed in a Range of Cells on a worksheet (named Sheet1) of a Microsoft Excel workbook.

Including the table headers, the range begins at Cells(3,1) and ends at Cells(14,4) – or Range(“A3:D14”). So, the data table has four (4) columns and twelve (12) rows, including the header row.

The sample code below illustrates the Do – Loop Until statement’s usage. It finds out the last year, in the past 10 years, that a player of a specific nationality won the championship.

In brief, the sample code above does the following:

• Declares String variables str_Nation and str_Year (code window line #9). These hold the user’s nationality of interest and the most recent year a player of that nationality won, respectively;
• Declares Integer variables int_Row and int_Column (line #10). These track the row and column indices of worksheet cells, respectively;
• Collects the user’s nationality of interest using the Show and Hide methods of a UserForm (lines #13-15). It also uses the Text property of the form’s sole ListBox control in the data collection (line #14). Moreover, it uses the With block and the dot operator to avoid fully qualifying each form control reference in the block. The image and sample code below show the UserForm and its code;

• Assigns initial values to the row and column tracking variables, int_Row and int_Column (lines #18-19). This is the initialization process that always precedes a Do loop. Since the players’ nationality starts in worksheet Cells(4,4), both variables should be set to ‘4.’ But the row index is set to ‘3,’ so that the control expression’s first test, which runs after the first iteration, occurs at the 4th row;
• The Do statement starts the loop (line #24) and the loop body statement increases the row index by ‘1’ (line #26). Now the row index is ‘4,’ so the first control expression (i.e., nationality) test happens at the 4th row;
• The Loop Until statement tests the control expression for a nationality match outcome (line #27). A True return confirms a nationality match, whereas a False return confirms the contrary. The execution point returns to the Do statement if the expression returns False;
• Repeats the above steps as it loops through worksheet Cells(4,4) to Cells(14,4).
• Ends the loop when the loop’s control expression evaluates to True. So, the loop ends when a nationality match occurs;
• Assigns a year from the worksheet cell on the same row but in column ‘B’ to variable str_Year (line #30). Recall that this column holds the championship years;
• Finally, it displays the result via a MsgBox function (lines #34-36) as shown in the image below. Also, a line continuation character (underscore, _) splits the function across three lines.

In the sample code above, at least one iteration always runs. This is so even if the first nationality in column ‘D’ matches the user’s nationality of interest. That’s because the loop tests the control expression after each iteration (line #27). So, the first iteration of loop body statements still runs.

Moreover, the sample code above always finds a nationality match. That’s because the UserForm only lets the user select nationalities from a pre-set list.