The While…Wend Statement is a logical function in Excel VBA. The statement is used to create a While loop. When you are uncertain about the necessity of loop body repetition, you can use the While Wend statement to create a while loop. In this article, you will learn to use the VBA While Wend statement in Excel with 4 related Examples.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
What is While…Wend Loop?
The While…Wend loop is a logical function used to create a While loop in VBA Excel.
When the condition becomes true, all the statements inside the While…Wend loop is executed. This process continues until the Wend keyword is faced.
But when the condition becomes false, all the statements inside the loop are skipped. Then the immediate next statement after the While…Wend loop gets executed.
You can nest one While Wend loops inside another. You can do this as much as you want. Each time the Wend keyword will follow the most recent While keyword.
💡 If you want more flexibility to do looping, check out the Do…Loop statement.
While…Wend Loop Syntax
Syntax
While condition(s)
[ statements ]Wend
Statement Parameters
condition(s)
After the While keyword, you have to specify the condition or a set of conditions.
A condition is basically a numerical or string expression. If the condition becomes true, then the statements inside the While and Wend keyboard will come into action. Otherwise, the next statement after the Wend keyword will get executed skipping the whole loop.
If there is no specified condition i.e. if the condition is Null. Then it will assume a False condition.
statements
In the statements section, you can use one or multiple statements. They will all come into action only when the condition becomes true.
4 Examples Regarding the While Wend Statement in Excel VBA
Example 1: Display All the Even Numbers From 0 to 10 Using the While Wend Statement in Excel VBA
In the very first example, I will show you how to display all the even numbers from 0 to 10 using the While Wend Statement in Excel VBA.
To do that,
❶ Create a new Excel Workbook first.
❷ Then press ALT + F11 keys to open VBA editor in Excel.
❸ After that create a new Module from the Insert menu.
❹ Copy the following code and save it in the VBA editor.
Sub WhileWendExmp1()
Dim x As Integer
x = 0
While x <= 10
MsgBox (x)
x = x + 2
Wend
End Sub
❺ Now hit the F5 key to run the above code.
This will bring up the Macros window.
❻ Hit the Run command.
You will see a message box appear. Keep clicking on the OK button or the Close icon, it will show all the even numbers from 0 to 10.
Read More: Excel Formula to Generate Random Number (5 examples)
Example 2: Show All the Numbers Having 5 As the Last Digit from 10 to 100 Using the While Wend Nested Statement in Excel VBA
Now I will show you how to show all the numbers having 5 as the last digit from 10 to 100 using the While Wend loop in VBA. To do that, I will use one While Wend loop nested inside another While Wend loop.
So let’s see how to do that.
❶ Open a new Excel Worksheet first.
❷ After that hit ALT + F11 keys to open VBA editor in Excel.
❸ Then create a new Module from the Insert menu.
❹ Copy the following code and Save it in the VBA editor.
Sub WhileWendExmp2()
Dim x As Integer
Dim y As Integer
x = 1
y = 5
While x < 10
While y < 6
MsgBox (x & "" & y)
y = y + 1
Wend
y = 5
x = x + 1
Wend
End Sub
❺ Now hit the Run Sub button or press F5 key to run the above code.
Then you will see a message box like the image below appear. It shows the first number in the range which is 15. To see the rest of the numbers, keep pressing the Close icon or the OK command.
Read More: How to Use VBA IsNumeric Function (9 Examples)
Similar Readings:
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
- Use the VBA Environ Function (4 Examples)
- How to Use VBA IsEmpty Function (5 Relevant Examples)
- Use VBA TimeValue Function (6 Relevant Examples)
- How to Use VBA Abs Function in Excel (9 Examples)
Example 3: Assign the Salary Amount for the Corresponding Employee Name Using the While Wend Statement in Excel VBA
We have a table of two columns. First column represents the Employee Names and the second column represents their corresponding Salary amount.
In this example, I will show you how to write a VBA code using the While Statement in Excel that automatically inserts the corresponding salary into the second column.
To do that,
❶ Press ALT + F11 keys to open VBA editor in Excel.
❷ After that go to Insert > Module.
❸ Copy the following code and Save it in the VBA editor.
Sub WhileWendExmp3()
Dim x As Integer
x = 5
While Len(Range("B" & CStr(x)).Value) > 0
If Cells(x, 2).Value = "James" Then
Cells(x, 3).Value = "$5000"
ElseIf Cells(x, 2).Value = "Jonas" Then
Cells(x, 3).Value = "$6000"
ElseIf Cells(x, 2).Value = "Rozer" Then
Cells(x, 3).Value = "$7000"
ElseIf Cells(x, 2).Value = "Ivan" Then
Cells(x, 3).Value = "$8000"
ElseIf Cells(x, 2).Value = "Jones" Then
Cells(x, 3).Value = "$9000"
End If
x = x + 1
Wend
End Sub
❹ To run the above code, press the F5 key. Or you can press ALT + F8 to open the Macro window. You can also go to the Developer tab and then click on Macros to open the Macro window.
❺ Select the WhileWendExmp3 function and hit the Run command.
After that, you will get your result in no time.
Related Content: How to Use VBA Case Statement (13 Examples)
Example 4: Format Cells Using the While Wend Statement in Excel VBA
We can use the While Wend Statement to Format Cells in Excel. I’m using a data table of two columns to change it’s background color and the font color.
So let’s see how we can do that using the While Wend loop in Excel VBA.
❶ First of all, open the VBA editor by pressing the ALT +F11 keys.
❷ Then open a new Module. You will find this option under the Insert menu.
❸ Now Copy the following code to the newly opened Module and Paste it there.
Sub WhileWendExmp4()
Dim x As Integer
x = 5
While Len(Range("B" & CStr(x)).Value) > 0
Range("B" & CStr(x)).Interior.Color = 6666666
Range("C" & CStr(x)).Interior.Color = 6666666
Range("B" & CStr(x)).Font.Color = vbRed
Range("C" & CStr(x)).Font.Color = vbRed
x = x + 1
Wend
End Sub
❹ Save the code by pressing CTRL + S.
❺ Now hit ALT + F8 keys to open the Macro window.
❻ From the function list, select WhileWendExmp4 and hit the Run command.
After doing all the steps accurately, you will see the background color of the table has been changed to the Olive color and the fonts have been changed to the Red color.
Read More: VBA If – Then – Else Statement in Excel (4 Examples)
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To open the Macro window, hit ALT + F8 keys.
- Hit the F5 key to run the VBA codes.
Conclusion
To sum up, we have discussed the usage of the VBA While Wend statement in Excel with 4 related examples. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
