The Do Until – Loop Statement | VBA Iteration Statements

In everyday life, we often need to repeat certain actions. For instance, Indians thrill the world with Diwali festivities held every October or November. Likewise, your program may need to repeat one or more statements several times. Iteration statements like the Do Until – Loop statement make such looping possible in VBA.

Introducing the Do Until – Loop 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 tests the condition or control expression (numeric or string type) before running each iteration. So, execution enters the loop’s body only if the expression returns False.

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

The header image above shows the Do Until – Loop 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 Until – Loop statement:

• The Do statement first checks that the control expression evaluates to False. If so, the program runs the statements in the loop body once. Afterward, the Loop statement returns the execution point 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 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 statement.

The Do Until – Loop 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 Until – Loop 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. Both variables are set to ‘4,’ since the players’ nationality starts in worksheet Cells(4,4);
• Loops through worksheet Cells(4,4) to Cells(14,4) using a Do Until – Loop statement (lines #24-27). Also, the loop’s control expression tests for a nationality match outcome (line #24). So, a True return confirms a match, whereas a False return confirms the contrary;
• Increases the worksheet row index by ‘1’ (line #26), if the nationalities don’t match. So, it moves to the next row before starting the next iteration;
• 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.

No iterations run at all if the first nationality in column ‘D’ matches the user’s nationality of interest. That’s because the loop tests the control expression before each iteration (line #24). If the first nationality matches, the loop’s condition is met and it ends.

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.