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.
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
[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”.
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.
[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]
hi there is Wend the same as next?
and when you make a statement is there a format that you have to use or is it open game?
Hi Ciscocell,
your first question about Wend and Next, yes you can say Wend is like Next only. All the Statements are executed until Next or Wend is not encountered.
The only difference in Next and Wend is that… Wend does not increase any Counter automatically for each loop while "Next" increments the counter by "1" each time by default.
Your second question is not much clear, can you please clarify.
I have created this code, everything is ok. Btu this calculation is working up to row 12. But I need to calculate up to blank row. what is the problem here. please help me. where I will use the do while code and what will be the code, please give me the full code.
Sub ScoreMacro()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
Dim Rank As Integer
Dim Clients As Integer
Dim Products As Integer
Dim Margin As Double
Dim RankScore As Integer
Dim ClientScore As Integer
Dim ProductScore As Integer
Dim MarginScore As Integer
Dim CurScore As Integer
Dim TotalScore As Integer
Dim TotalScorePercent As Double
Dim MaxScore As Integer
Dim StartRow As Integer, i As Integer
Dim ColumnBeforeStart As Integer, j As Integer
‘number of columns to be added before Product Rank column
ColumnBeforeStart = 2
StartRow = 7
i = StartRow
j = ColumnBeforeStart + 1
While ws.Cells(i, j) “”
‘ set initial score for current row
TotalScore = 0
‘Get current Rank
If Not (IsNumeric(ws.Cells(i, j))) Then
MsgBox “Rank in row ” & i & ” is not numeric value”
Exit Sub
End If
Rank = ws.Cells(i, j)
‘Process Rank rules
If (Rank >= 0) And (Rank = 101) And (Rank = 501) And (Rank = 1001) And (Rank = 0) And (Clients = 51) And (Clients = 101) And (Client 300) Then
CurScore = -20
End If
‘ Set ClientsScore
ws.Cells(i, j + 3) = CurScore
TotalScore = TotalScore + CurScore
‘Get current Products Value
If Not (IsNumeric(ws.Cells(i, j + 4))) Then
MsgBox “Products value in row ” & i & ” is not numeric value”
Exit Sub
End If
Products = ws.Cells(i, j + 4)
‘Process Products rules
If (Products = 1) And (Products = 3) Then
CurScore = -15
End If
‘ Set ProductsScore
ws.Cells(i, j + 5) = CurScore
TotalScore = TotalScore + CurScore
‘Get current Margin Value
If Not (IsNumeric(ws.Cells(i, j + 6))) Then
MsgBox “Margin value in row ” & i & ” is not numeric value”
Exit Sub
End If
Margin = ws.Cells(i, j + 6)
‘Process Margin rules
If (Margin = 0) Then
CurScore = -30
ElseIf (Margin >= 0.01) And (Margin = 0.11) And (Margin = 0.31) And (Margin = 0.51) Then
CurScore = 25
End If
‘ Set MarginScore
ws.Cells(i, j + 7) = CurScore
TotalScore = TotalScore + CurScore
‘ add total score to current row
ws.Cells(i, j + 8) = TotalScore
‘ I suppose that maxScore is 100 = sum of all max points
‘ in each category
MaxScore = 100
‘ Get TotalScore in percents
TotalScorePercent = TotalScore / MaxScore
‘ add total score to current row
ws.Cells(i, j + 9) = TotalScorePercent
i = i + 1
Wend
End Sub
I want to select multiple excel files from a folder and send it to another folder with converting it to CSV format. Please suggest a VBA code for that.
Thanks
Hi,
Is there a way to write Do While loop in reverse order. Ex. print numbers from 10 to 1.
Please suggest with a simple example.