VBA While-Wend Statement

The While…Wend statement is a powerful looping mechanism in VBA that allows for repetitive code execution until a certain condition is met. It is commonly used in macros for tasks such as data validation, formatting, and data manipulation. In this blog post, we will provide an overview of the While…Wend statement, its syntax, examples, important notes and remarks, and conclude with our thoughts and feedback.

VBA While…Wend Statement

Purpose

The purpose of the While…Wend statement is to execute a block of code as long as a specific condition is met. It is similar to the ‘Do While’ statement, but it utilizes a ‘Wend’ keyword instead of an ‘End’ keyword. This statement is commonly used in scenarios where the number of iterations is unknown or when the loop needs to stop at a specific condition.

Syntax

The syntax for the While…Wend statement is as follows:

While condition
‘code to be executed
Wend

The ‘While’ keyword is followed by the condition, which can be any logical expression that evaluates to either True or False. If the condition is True, the statements inside the loop will be executed repeatedly until the condition becomes False. The ‘Wend’ keyword marks the end of the loop and transfers control back to the ‘While’ statement.

Examples of  VBA While…Wend Statement

Validating User Input

One of the most common use cases for the While…Wend statement is for user input validation. In this example, we will ask the user to input a number and check if it falls between a specific range.

Sub userInput()
Dim num As Integer
num = InputBox("Enter a number between 1 and 10.")

While (num < 1 Or num > 10)
num = InputBox("Invalid input. Please enter a number between 1 and 10.")
Wend
End Sub

In this code, the ‘While’ loop will continue to execute until the user enters a valid number between 1 and 10.

Formatting Cells Based on a Condition

Sometimes, we want to format cells based on a specific condition. The While…Wend statement can be used in such scenarios. In the following example, we want to format all cells in column A that contain the word “Sales” with a specific background color.

Sub formatCells()
Dim i As Integer

i = 1

While Cells(i, 1) <> ""
If InStr(Cells(i, 1), "Sales") <> 0 Then
Cells(i, 1).Interior.Color = RGB(255, 255, 0) 'yellow color
End If
i = i + 1
Wend
End Sub

In this code, the ‘While’ loop will continue to execute until the cells in column A are not empty. If the cell contains the word “Sales”, the interior color will be changed to yellow.

Copying Data to Another Worksheet

The While…Wend statement is also useful for copying data from one worksheet to another based on a condition. In this example, we will copy all rows from Sheet1 to Sheet2 where the value in column A is greater than 10.

Sub copyData()
Dim i As Integer

Sheets("Sheet1").Activate 'activate sheet1

i = 1

While Cells(i, 1) <> ""
If Cells(i, 1).Value > 10 Then
Range("A" & i & ":C" & i).Copy Destination:=Sheets("Sheet2").Range("A" & i & ":C" & i) 'copy row to sheet2
End If
i = i + 1
Wend
End Sub

Note that the loop will stop when the cells in column A are empty.

Creating a Custom Message Box

You can also use the While…Wend statement to create a custom message box that repeats until the user clicks on a specific button. In the following example, we will create a message box with three buttons: Yes, No, and Cancel.

Sub customMessageBox()
Dim response As Variant

response = MsgBox("Do you want to continue?", vbYesNoCancel) 'display message box

While response <> vbYes 'while response is not "Yes"
response = MsgBox("Do you want to continue?", vbYesNoCancel)
Wend
End Sub

The loop will continue until the user clicks on the “Yes” button.

Generating Random Numbers

The While…Wend statement can also be used to generate a specific number of random numbers. In this example, we will generate ten random numbers between 1 and 100.

Sub randomNum()
Dim i As Integer

i = 1

While i <= 10 'execute loop 10 times
Cells(i, 1).Value = Int((100 - 1 + 1) * Rnd() + 1) 'generate random number between 1 and 100
i = i + 1
Wend
End Sub

Important Notes & Remarks

• If the condition is never met, the loop will continue indefinitely, resulting in an infinite loop. It is important to include a statement inside the loop that will eventually make the condition False.
• Always use the ‘Wend’ keyword at the end of the loop. Using ‘End While’ may result in an error.
• If there is only one statement inside the loop, it is not necessary to use a ‘Wend’ keyword. Instead, you can use a colon (:) after the statement to indicate it is the end of the loop.

In conclusion, we have provided a comprehensive guide to the While…Wend statement in VBA. We have discussed its purpose, syntax, and examples. We hope this post has been informative and has helped you understand the potential of this looping statement.