In previous Article, we had seen about For Next Loop. In this article we are going to learn about While and Do While Loop. While loop is also quite useful in Excel VBA programming. Here in this article, I will explain each and every aspect related to While loop with an Example.
Now you must be wondering, why is this another loop? What is difference between this While Loop and For Loop.
Major difference between For and While Loop?
Answer is very simple: When you already know the number of iteration before you run the loop, then you can simply use For Loop. In case you do not know – How many times your loop is going to run, then you should use While loop ro Do.. while loop. Do not worry about these two similar names While and Do while loop, I keep mentioning. You will learn about them here.
1. While … Wend Loop in Excel VBA
Let’s take the same example what we discussed in previous Article with For..Next Loop.. Calculate the Sum of all Numbers from 1 to 10 Natural Numbers.
You can see that in While Loop, unlike For loop, we need to increment the Counter variable value by your own. In for loop, you can see, that we need not to mention iCount= iCount + 1 to increment the value of i by 1 in every iterations.
Did you know?
Unlike FOR Loop, you do not need to know the exact number of iteration while writing the While Loop code. All you need to know the criteria when your loop should end.
Unlike FOR Loop, in while loop, you need to increase the iteration value by writing an explicit statement. For loop increases the value automatically by 1, each time it completes the iteration.
DO … While Loop in Excel VBA:
There are two ways of using DO…While Loop.
1. You can put the Condition before getting in to the Loop. Note: This is same as using normal while loop as explained above.
2. You can check the condition at the end of the loop. It means, in this case Loop will be executed AT LEAST once even if the Condition is failing at the first time itself.
Let’s have a look on the Syntaxes of above two ways of using Do..While loop.
Syntax: Type 1 – Where condition is checked at the beginning
Syntax: Type 2: Where condition is checked at the End
Let’s take the same example what we discussed above. Calculate the Sum of all Numbers from 1 to 10 Natural Numbers.
Example 1: Condition in the starting of the loop
Result := Sum = 55
Sub Example1() iCount = 1 Sum = 0 Do While iCount <= 10 Sum = Sum + iCount iCount = iCount + 1 Loop MsgBox Sum End Sub
Example 2: Condition at the end of the loop
Result := Sum = 55
Sub Example2() iCount = 1 Sum = 0 Do Sum = Sum + iCount iCount = iCount + 1 Loop While iCount <= 10 MsgBox Sum End Sub
Both the examples are giving the same result : 55
That means, there is no difference in putting the condition at the beginning or at the end of the Do While loop, when condition is true at least ONE time
Difference between both way of defining do while loop
Let’s take an example where condition is not true even for the first iteration. Let’s see the difference in both way of using Loops – 1. Condition put in the beginning of the Loop and 2. Condition put at the end of the loop.
Result := Nothing…No result
Sub ConditionFalseForTheFirstTimeItself_1() Dim i As Integer, j As Integer i = 2: j = 2 Do MsgBox "This is the 1st iteration" Loop While i < j End Sub
Result := MessageBox : This is the 1st iteration
Sub ConditionFalseForTheFirstTimeItself_2() Dim i As Integer, j As Integer i = 2: j = 2 Do While i < j MsgBox "This is the 1st iteration" Loop End Sub
With the above two examples you can clearly see the difference. 2nd function is displaying the message box with message while 1st one is not displaying anything.
What is Until keyword in While Loop?
In Do While loop, Until keyword is also used like While keyword. But the question is – Are they both same? Answer is NO.
In simple words: While runs till the condition becomes “False” whereas Until is completely opposite – It runs till the condition is True.
While keyword terminates the loop as soon as the condition is False
Until keyword terminates the loop as soon as the condition is True
So let’s see how to use Until keyword in Do while loop. Usage of Until is exactly same as while. All you need to do is replace the keyword While with Until. That is it. And ofcourse while setting the condition, you have got to be careful – which keyword are you using.
Refer the below Example:
Sub Example() iCount = 1 Sum = 0 Do Sum = Sum + iCount iCount = iCount + 1 Loop Until iCount > 10 MsgBox Sum End Sub
But you can see that when you are using the keyword Until then you need to change the Condition. For both While or until you can not use the same condition. Because “Until” is like reverse of “While”.
Important to know…
One important point to note that, Until keyword can be used only in Do … While loop. In regular while …wend loop, you can not replace While keyword with Until.