Step by Step tutorial for While Loop Excel VBA

.

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]

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

5 Comments

  1. ciscocell

    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?

    Reply
    • Vishwamitra Mishra

      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.

      Reply
  2. Atik

    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

    Reply
  3. Abhishek

    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

    Reply
  4. Raj Gautam

    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.

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro How to Use For...Next Loop in Excel Macro - [...] While Loop [...]
  2. Learn Excel Macro Excel Macro Tutorial : Basics of VBA (Basic Syntaxes required while coding) - [...] You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While and DO…
  3. Tutorial for Beginners - How to Use For Loop in Excel VBA - LearnExcelMacro.com - […] While Loop […]
  4. Complete VBA Guide to Interact with Text Files with Examples - LearnExcelMacro.com - […] 3. To read every line from Excel and write it in textFile, I have used Do..While loop with Until…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest