How To Use The While Wend Loop In Excel VBA Code
We use loops in Excel when we have to run several condition checks repeatedly in Excel VBA. Sometimes we use if , end if loop which is a very simple and most used loop.
If loop:
“Process if condition is true”
End if
Then we have a for loop if we know exactly how many times the loop must run:
For loop:
For I = 1 to N
Process.
Next i
In this loop it will run the process for N number of times.
Now sometimes we have certain condition so that we must keep the loop running as many times till the condition is not true, here comes the While Wend loop:
Syntax:
While condition(s)
[statements 1]
[statements 2]
…
[statements n]
Wend
It will keep running till condition is true:
First, the While condition is tested.
- if the condition is FALSE, the program immediately jumps to the command immediately after the word Wend
- if the condition is TRUE, then all commands inside the loop (ie between the words Do While and Loop) are executed.
Example 1: While Wend Loop basic example
Sub While_wend_Example()
Dim countA: countA = 100
While countA < 110 ‘ Test value of Counter.
MsgBox “The Current Value of the Counter is : ” & countA
countA = countA + 1 ‘ Increment Counter.
Wend
End Sub
This loop will keep running till we have the condition as true : countA value is less than 110
So this loop will run 10 times:
Iteration1 : CountA = 100
Iteration2 : CountA = 101
Iteration3 : CountA = 102
Iteration4 : CountA = 103
Iteration5 : CountA = 104
Iteration6 : CountA = 105
Iteration7 : CountA = 106
Iteration8 : CountA = 107
Iteration9 : CountA = 108
Iteration10 : CountA = 109
For next iteration CountA = 110 so condition will become false and the loop ends.
Example 2: Calculating the factorial of numbers in a While Wend Loop
Sub Factorial()
x = 0
y = 0
While x < 10
x = x+1
y = y*x
Wend
ActiveDocument.Content= “Factorial result of ” & x & ” = ” & y
End Sub
Example 3: While Wend Loop to calculate SUM in Excel.
Sub SumWhileWend()
n = InputBox(“Enter n value:”)
Dim j As Single
Dim i As Integer
i = 1
While (i <= n)
j = j + i
i = i + 1
Wend
MsgBox j
End Sub
Template
Further reading: Basic concepts Getting started with Excel Cell References