MS Excel: How to use the WHILE...WEND Statement (VBA)

This Excel tutorial explains how to use the Excel WHILE...WEND statement to create a WHILE loop in VBA with syntax and examples.

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.

The WHILE...WEND statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Subscribe

---

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

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

While condition
   {...statements...}
Wend

Parameters or Arguments

condition

The condition is tested 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.

Returns

The WHILE...WEND statement creates a WHILE loop in VBA.

Note

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

Applies To

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

Type of Function

• VBA function (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 As Integer
   Dim 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.

Example#1 from Video

In the first video example, we are going to use the While...Wend statement to loop through the products in column A and update the appropriate application type in column B.

Sub totn_while_loop_example1()
   
   Dim LSearchRow As Integer

   LSearchRow = 2

   While Len(Range("A" & CStr(LSearchRow)).Value) > 0

      If Cells(LSearchRow, 1).Value = "Excel" Then
         Cells(LSearchRow, 2).Value = "Spreadsheet"

      ElseIf Cells(LSearchRow, 1).Value = "Access" Then
         Cells(LSearchRow, 2).Value = "Database"

      ElseIf Cells(LSearchRow, 1).Value = "Word" Then
         Cells(LSearchRow, 2).Value = "Word Processor"

      End If

      LSearchRow = LSearchRow + 1

   Wend

End Sub

Example#2 from Video

In the second video example, we have a list of participants in column A and we'll use the While...Wend statement to format each of the values in column A with a dark blue background and a white font color.

Sub totn_while_loop_example2()

   Dim LSearchRow As Integer

   LSearchRow = 2

   While Len(Range("A" & CStr(LSearchRow)).Value) > 0

      Range("A" & CStr(LSearchRow)).Interior.Color = 6567712
      Range("A" & CStr(LSearchRow)).Font.Color = vbWhite

      LSearchRow = LSearchRow + 1

   Wend
   
End Sub