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

Syntax:

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FFFFE0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#FFCC66″ border_style=”solid” padding=”10″ margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]While [condition]
[statements]
…………
[statements]
Wend
[/one_full]

Example:

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.
[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FFFFE0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#FFCC66″ border_style=”solid” padding=”2″ margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]

iCount=1
sum = 0
While iCount <= 10
sum = sum + i
iCount = iCount + 1
Wend

[/one_full]

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.

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

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FFFFE0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#FFCC66″ border_style=”solid” padding=”10″ margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]
Do While [condition]
[statements]
[Exit Do]
[statements]
Loop
[/one_full]

Syntax: Type 2: Where condition is checked at the End

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FFFFE0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#FFCC66″ border_style=”solid” padding=”10″ margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]
Do
[statements]
…………
[statements]
Loop While [condition]
[/one_full]

Example:

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

Sub Example1()
    iCount = 1
    Sum = 0
    Do While iCount <= 10
        Sum = Sum + iCount
        iCount = iCount + 1
    Loop
    MsgBox Sum
End Sub

[highlight] Result := Sum = 55[/highlight]

Example 2: Condition at the end of the loop

Sub Example2()
    iCount = 1
    Sum = 0
    Do
        Sum = Sum + iCount
        iCount = iCount + 1
        Loop While iCount <= 10
    MsgBox Sum
End Sub

[highlight] Result := Sum = 55[/highlight]
 

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.

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

[highlight] Result := Nothing…No result[/highlight]

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

[highlight] Result := MessageBox : This is the 1st iteration[/highlight]

 
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”.

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FFFFE0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#FFCC66″ border_style=”solid” padding=”10″ margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial
[/one_full]