How to use the WHILE...WEND Statement (VBA)

Description

The Microsoft Excel WHILE...WEND statement is used to create a WHILE loop in VBA. You use a WHILE loop when you are not sure how many times you want to execute the VBA code within the loop body. With a WHILE loop, the loop body may not execute even once.

Syntax

The syntax to create a WHILE loop using the WHILE...WEND statement in Microsoft Excel is:

WHILE condition
   {...statements...}
END;

Parameters or Arguments

condition

The condition is test each pass through the loop. If condition evaluates to TRUE, the loop body is executed. If condition evaluates to FALSE, the loop is terminated.

statements

The statements of code to execute each pass through the loop.

Note

• See also the FOR...NEXT statement to create a FOR loop in VBA.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• VBA statement (VBA)

Example (as VBA Function)

The WHILE...WEND statement can only be used in VBA code in Microsoft Excel.

Let's look at how to create a WHILE loop in Microsoft Excel.

WHILE Loop

For example:

Sub While_Loop_Example

   Dim LTotal As Integer

   LTotal = 1

   While LTotal < 5
      MsgBox (LTotal)
      LTotal = LTotal + 1
   Wend

End Sub

In this example, the WHILE loop is controlled by the condition While LTotal < 5. This condition is tested each pass through the loop. If the condition is TRUE, then the VBA code would display a message box with the value of the LTotal variable. If the condition is FALSE, then the loop is terminated. This code would display 4 message boxes with the following values: 1, 2, 3, and 4. Once LTotal reaches 5, it will terminate the loop.

Double WHILE Loop

You can nest WHILE loops in VBA. This allows you to have a double loop with 2 different conditions that will be evaluated.

For example:

Sub Double_While_Loop_Example

   Dim LCounter1, LCounter2 As Integer

   LCounter1 = 1
   LCounter2 = 8

   While LCounter1 < 5
      While LCounter2 < 10
         MsgBox (LCounter1 & "-" & LCounter2)
         LCounter2 = LCounter2 + 1
      Wend
      LCounter2 = 8
      LCounter1 = LCounter1 + 1
   Wend

End Sub

Here we have 2 WHILE loops. The outer WHILE loop is controlled by the LCounter1 variable. The inner WHILE loop is controlled by the LCounter2 variable.

In this example, the outer WHILE loop would loop 4 times (starting at 1 and ending at 4) and the inner WHILE loop would loop 2 times (starting at 8 and ending at 9). Within the inner loop, the code would display a message box each time with the value of the LCounter1-LCounter2. So in this example, 8 message boxes would be displayed with the following values: 1-8, 1-9, 2-8, 2-9, 3-8, 3-9, 4-8, and 4-9.

You will notice that as we exit the inner WHILE loop, we reset the counter for the inner WHILE loop back to 8 with the statement LCounter2 = 8. This is to ensure that the inner WHILE loop will execute again, as LCounter2 would equal 10 after exiting the inner WHILE loop the first time. If we left LCounter2 at a value of 10, he condition for the inner WHILE loop code would evaluate to FALSE and terminate the loop every subsequent time.