How to use While Wend Loop in Excel VBA?

In this article, we are going to see about While Wend loop in Excel VBA using a suitable example.

Implementation :

In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. 

The Developer Tab can be enabled easily by a two-step process :

• Right-click on any of the existing tabs in the top of the Excel window.
• Now select Customize the Ribbon from the pop-down menu.

• In the Excel Options Box, check the box Developer to enable it and click on OK.

• Now, the Developer Tab is visible.

Now click on the Visual Basic option in the Developer tab and make a new module to write the program using the Select Case statement.

Developer  -> Visual Basic -> Tools -> Macros

• Now create a Macro and give any suitable name.

• This will open the Editor window where can write the code.

While Wend Loop

In a while loop all the statements will execute inside the loop until the provided condition becomes FALSE. The loop terminates only when the condition becomes FALSE. In Excel the keyword While is used to start the while loop and Wend is used to end the while loop.

1. The statements inside the while loop execute when the condition is TRUE.

2. When the condition is FALSE, the loop terminates and the control moves to the line after the keyword Wend.

The syntax is :

While boolean_condition(s)/expression(s)
    Statement 1
    Statement 2
    Statement 3
    ...
    Statement n
Wend

Flow Diagram

Example : Print the age of all the employees in an organization whose age falls between thirty to forty years.

Code :

Sub While_Age_Emp()
'Initialize and declare the age of the employee
Dim Age As Integer: Age = 30
'Condition to print the age of employees between 30 to 40 years
While Age < 39
    Age = Age + 1
    MsgBox ("Age:" & Age)
Wend
End Sub

The condition for the above code is Age < 39. When the age of the employee becomes 40 the condition becomes FALSE and the loop terminates. So, it will print the ages from 31 to 39 respectively in the message box of Excel.

Output :

Age : 31

Age : 32

Age : 33

Age : 34

Age : 35

Age : 36

Age : 37

Age : 38

Age : 39