Loops in VBA Excel

Loops in any coding plays vital role handling and controlling the code executions. Looping or repeating task requires looping the code Thtill you get the required result.

Loops executes code repeatedly till the condition is true. Loop always helps you to optimize you code execution faster and accurately, also helps you to avoid multiple steps.

 

Looping through in excel dynamically is possible in VBA, there are multiple method to loop macro steps until the condition is met. 

1. FOR - NEXT

2. FOR - EACH - NEXT

3. DO WHILE - LOOP

4. DO UNTIL - LOOP

5. WHILE - WEND 

These loops gives you the end result same, however as you know that every loops need a specific stop to complete the execution of code. We will learn one by one with examples.

1. FOR - NEXT Statement.

    The FOR - NEXT Loops repeats code execution for specific number of times passed through code. 

Syntax:

      For    Counter_variable = Start point to end point

          Your Code here

     Next 

Example:

     

     Sub ForNextLoop()

           DIM iCtr       As Integer

           Dim iSum      As Integer

       iSum = 0

           FOR iCtr  = 1 To 10

               iSum = iCtr + iSum

           NEXT iCtr

       Msgbox "The sum of Values from 1 to 10 is... " & iSum

     End Sub

In this example variable iCtr denotes that For-Next will execute summation code from 1 to10

iSum variable store the result coming from sum of values from variable (iCtr + iSum).

iSum =0 denotes that initial value of variable is 0

Now when iCtr value = 1 then iSum = 1 + 0

When code loops over for next execution, iCtr values becomes 1

iSum = 1 + 1

next execution iCtr will become 2

iSum = 2 +1

and so on... this way total calculation of values from 1 to 10 will be 55

2. FOR - EACH - NEXT Statement

Loop repeats the execution of code for each object in a set of object. For each element in collection is executed. Loop stops only when all elements in set of collection is executed. Stops when all steps are satisfied within the loop

Syntax:

      FOR Obj_variable in Group_Obj_Variable

      Your Code here

     Next Obj_variable

Example:

        Protect Each worksheet in an active workbook with password as "Summary"

      Sub Execute_FOREACHNEXTLoop()

         DIM sht        As Worksheet         

         FOR EACH sht In ThisWorkbook.Worksheets

              sht.Protect Password:="Summary"

         NEXT sht

      Msgbox "All worksheets in this workbook are now protected."

     End Sub

In this example all your sheets in this workbook gets password protected, irrespective of number of sheets may it be 2 or 100 sheets.

 Let us take one more example to make you understand better

Sub ForEachLoopExecute()
'Code will color each cell object in a set of range object
   
    Dim aCell             As Range
    Dim AllCell           As Range
   
    Set AllCell = ActiveSheet.Range("B2:B20")
   
    For Each aCell In AllCell
        aCell.Interior.Color = RGB(123, 234, 0)
    Next aCell

End Sub

Nesting Loops - These loops are very sensitive to conditions supplied in code, if the condition is satisfied you get the result. You can nest loops within loops till you have multiple conditions. The Counter variable for each loop has to be unique. In Nested Loops, it becomes necessary that the inner loop must be completed before Next Statement of outer Loop is encountered. Let us try this example.

Let us assume that you have a set of range of values 1 to 20 and you wanna to highlight the cells of which value is less than 20

The Loop you should apply is

Sub ForEachLoopExecute()
   
'Code will color each cell object in a set of range object
   
    Dim aCell As Range
    Dim AllCell As Range
   
    Set AllCell = ActiveSheet.Range("B2:B20")
    For Each aCell In AllCell
        With aCell
            If aCell.Value < 5 Then
                .Interior.Color = RGB(255, 255, 0)  'Color the cell with Yellow
            Else
                .Interior.Color = RGB(255, 0, 0) 'Color the cell with Red
            End If
        End With
Next aCell

End Sub

Another example Nested LOOP - FOR NEXT within FOR Next Statement

Sub createtablemultiply()
Cells.ClearContents
Dim i As Integer, j As Integer

'j = 1

For j = 1 To 5
    For i = 1 To 10
        'Cells(i, j).Value = i * j
        ActiveCell.Value = i * j
        ActiveCell.Offset(1, 0).Select
    Next i
        ActiveCell.Offset(-10, 1).Select
Next j


End Sub

3. DO-WHILE-Loop / DO-LOOP-WHILE Statement

    There are two methods in this type of Loop

   DO WHILE [Condition]

        Your Code here

   LOOP

    The DO-WHILE-LOOP repeats a block of code indefinitely while the specified condition continues to be met and evaluated to TRUE and stops when condition turns to FALSE. The condition can be tested either at the start or at the end of Loop. In this case the condition is tested at the start of LOOP.

   DO

       Your Code here

   LOOP WHILE [Condition]

    The DO-LOOP-WHILE test the condition at the end of Loop and continues till the Condition is TRUE and Stops loop when condition turns to FALSE.

Example:

The Condition i>5 is tested at the start and condition does not meet, so loop does not execute even once and iTotal returns to 0 

Sub DoWhileLOOP()
      Dim i As Integer
      Dim iTotal As Integer
        i = 5
       iTotal = 0
   
         Do While i > 5
             iTotal = i + iTotal
             i = i - 1
         Loop
MsgBox iTotal

End Sub
 

Another Example:

The condition (i>5) is tested at the end,and because it is met, the loop executes but only once after which the value of i reduces to 4 and the Loop ends. iTotal returns the value 5. 

Sub DoWhileLOOP()
    Dim i       As Integer
    Dim iTotal  As Integer

i = 5
iTotal = 0
        Do
            iTotal = i + iTotal
            i = i - 1
        Loop While i > 5
MsgBox iTotal
End Sub
 

4. DO-UNTIL-LOOP / DO-LOOP-UNTIL Statement

    DO-UNTIL-LOOP repeats the execution of code indefinitely until the condition is met and evaluates to TRUE. The condition can be tested either start of execution of code or at the end. 

DO - LOOP UNTIL tests the condition at the end of code. 

Syntax:

    DO UNTIL [Condition]

       Your Code goes here

    LOOP

Example:

    In this example DO UNTIL ISEmpty(Cells(iRow,2)) tests the condition if cell range iRow,2 is  is empty at the start of code execution and if condition is TRUE, Code exit out of LOOP, else execution of code continues till the Condition is TRUE.

Sub DOUntilLOOP()

Dim iRow As Integer
iRow = 2
    Do Until IsEmpty(Cells(iRow, 2))
            Cells(iRow, 2).Value = iRow * 2
            Cells(iRow, 2).Interior.Color = RGB(123, 232, 0)
        iRow = iRow + 1
    Loop

End Sub

Example - DO-LOOP-UNTIL Statement tests the condition at the end of code execution and if it is TRUE the execution Stops and continues till condition is met.
 

Sub DoLOOPUntil()

Dim iRow As Integer
iRow = 2
    Do
            Cells(iRow, 2).Value = iRow * 2
            Cells(iRow, 2).Interior.Color = RGB(123, 232, 0)
        iRow = iRow + 1
    Loop Until IsEmpty(Cells(iRow, 2))

End Sub

5. WHILE-WEND Statement - This Statement is similar to DO UNTIL - DO WHILE

Sub WhileWend()
Dim LTOTAL As Integer
LTOTAL = 1

Sheets("Sheet1").Activate
While LTOTAL < 10
      Cells(LTOTAL, 2).Value = LTOTAL
      LTOTAL = LTOTAL + 1
Wend


End Sub

Try executing these types of sample code and improve your understanding on VBA LOOPS