The While – Wend Statement | VBA Iteration Statements

In everyday life, we often need to repeat certain actions. For instance, Americans celebrate their independence every 4^th of July. Likewise, your program may need to repeat one or more statements several times. Iteration statements like the While – Wend statement make such looping possible in VBA.

Introducing the While – Wend Statement

The While – Wend statement sets up an indefinite loop that repeats a group of statements while a condition is met. In other words, it loops through a group of statements while a condition is True (or until 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 True.

If the very first expression test fails (i.e., returns False), the loop body’s statements never run at all.

When it comes to VBA iteration statements, the While – Wend statement is the runt of the litter. In fact, its inclusion in VBA is mainly for compatibility with earlier BASIC programming languages. Moreover, it is less structured and flexible than its doppelganger, the Do While – Loop statement.

So, while it works perfectly well, it is not a recommended looping construct. You should avoid using it as much as possible in modern VBA programs.

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

Salient Points on Usage

There are several vital things to note about the While – Wend statement:

• The While statement checks that the control expression evaluates to True. If so, the program runs the statements in the loop body. Afterward, the Wend statement (short for While End) returns the execution point to the While statement.
• The cycle repeats until the control expression evaluates to False. At that point, the loop ends.
• Once the loop ends, the execution point moves to the statement right after the Wend statement.
• Unlike other iteration statements, you can’t use Exit statements in the While – Wend statement. This is a major drawback of the While – Wend statement.
• It is possible that the control expression never returns a Boolean that stops the loop. Since Exit statements are illegal here, 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.
• Avoid using jump statements (e.g., GoTo statement) in While – Wend statements as they are error-prone.
• You can nest these loops within one another, in fact, VBA allows up to 16 levels of nesting.

The While – Wend 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 While – Wend 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 While 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 While – Wend statement (lines #24-27). Also, the loop’s control expression tests for a no-match outcome (line #24). So, a False return confirms a match, whereas a True 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 False. 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.