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.
If you want to follow along with this tutorial, download the example spreadsheet.
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
Advertisements