Chapter 8 – Loops

In this chapter, we will learn different types of Loops used in VBA. We will also see practical example of each type of Loop.

• For Loop
• For Each Loop
• While Loop
• Do While Loop
• Exit For
• Exit Do

For variable = start To end Step value

--Do something--

Next variable

Example 1:

Code:

Sub For_Loop_Example_1()

    Dim iCounter As Integer
    
    'Run the loop from 1 to 10
    For iCounter = 1 To 10
        
        'Change the value of the cell to iCounter variable value
        Sheet1.Range("A" & iCounter).Value = iCounter
        
    Next

End Sub

Result:

Explanation: This For loop runs from 1 to 10 and change the value of the cell to iCounter variable value. By default For loop increments the counter by 1 hence the loop resulted in printing 1 to 10 counting on the sheet.

Example 2:

Code:

Sub For_Loop_Example_2()

    Dim iCounter As Integer
    
    'Run the loop from 2 to 11
    For iCounter = 2 To 11
    
        'Check if the number is odd or even.
        'If Mod 2 results 0 then it is Even number else Odd
        If Sheet1.Range("A" & iCounter).Value Mod 2 = 0 Then
            Sheet1.Range("B" & iCounter).Value = "Even"
        Else
            Sheet1.Range("B" & iCounter).Value = "Odd"
        End If
    Next

End Sub

Result:

Explanation: This loop runs from 2nd to 11th row of the sheet. In each iteration of the loop, it checks if the number in the row is divisible by 2. If the number is divisible by 2 then code writes Even in second column of the sheet else writes Odd.

Example 3:

Code:

Sub For_Loop_Example_3()

    Dim iCounter As Integer
    
    'Run the loop from 1 to 10
    For iCounter = 1 To 10 Step 3
        
        'Change the value of the cell to iCounter variable value
        Sheet1.Range("A" & iCounter).Value = iCounter
        
    Next

End Sub

Result:

Explanation: This For loop runs from 1 to 10 and change the value of the cell to iCounter variable value. By default For loop increments the counter by 1 however we have supplied step value to 3 hence the loop increments by 3 in one iteration.

For Each Object in ObjectCollection

--Do something--

Next

Example 1:

Code:

Sub For_Each_Loop_Example_1()

    Dim wksSheet As Worksheet
    
    'Loop through each sheet in the current workbook
    For Each wksSheet In ThisWorkbook.Worksheets
    
        'Protect the sheet with 123 password
        wksSheet.Protect "123"
        
    Next

End Sub

Result:

Explanation: The code loops through each sheet of the workbook (in which code is written) and protect the sheet with 123 password.

Example 2:

Code:

Sub For_Each_Loop_Example_2()

    Dim iCounter As Integer
    
    'Run the loop from 2 to 11
    For iCounter = 2 To 11
    
        'Check if the number is odd or even.
        'If Mod 2 results 0 then it is Even number else Odd
        If Sheet1.Range("A" & iCounter).Value Mod 2 = 0 Then
        
            'If it is even number then change the color of the cell to green
            Sheet1.Range("A" & iCounter).Interior.Color = vbGreen
        
        Else
        
            'If it is odd number then change the color of the cell to yellow
            Sheet1.Range("A" & iCounter).Interior.Color = vbYellow
            
        End If
    
    Next

End Sub

Result:

Explanation: This loop runs from 2nd to 11th row of the sheet. In each iteration of the loop, it checks if the number in the row is divisible by 2. If the number is divisible by 2 (even number) then code changes the cell color to green else cell color is set to yellow.

While --Condition--

--Do something--

Wend

Example 1:

Code:

Sub While_Loop_Example_1()

    Dim iCounter As Integer
    
    'Initiate the variable to start from row 2
    iCounter = 2
    
    'Run the loop until blank cell is not found
    While Sheet1.Range("A" & iCounter).Value <> ""
    
        'Using Format VBA function, convert the date in day format and write in column B
        Sheet1.Range("B" & iCounter).Value = Format(Sheet1.Range("A" & iCounter).Value, "dddd")
        
        'Increament the counter by 1 to move the next row
        iCounter = iCounter + 1
        
    Wend
    

End Sub

Result:

Explanation: This while loop runs until a blank cell is not found in column A. In each iteration, it is using Format VBA function to convert the date in day format and writes in column B. At the end, iCounter is increment by one.

Example 2:

Code:

Sub While_Loop_Example_2()

    Dim iCounter As Integer
    
    'Initiate the variable to start from row 2
    iCounter = 2
    
    'Run the loop until blank cell is not found
    While Sheet1.Range("A" & iCounter).Value <> ""
    
        'Check if the number is odd or even.
        'If Mod 2 results 0 then it is Even number else Odd
        If Sheet1.Range("A" & iCounter).Value Mod 2 = 0 Then
        
            'If it is even number then change the color of the cell to green
            Sheet1.Range("A" & iCounter).Interior.Color = vbGreen
        
        Else
        
            'If it is odd number then change the color of the cell to yellow
            Sheet1.Range("A" & iCounter).Interior.Color = vbYellow
            
        End If
    
        'Increament the counter by 1 to move the next row
        iCounter = iCounter + 1
    
    Wend

End Sub

Result:

Explanation: This while loop runs until a blank cell is not found in column A. In each iteration of the loop, it checks if the number in the row is divisible by 2. If the number is divisible by 2 (even number) then code changes the cell color to green else cell color is set to yellow.

Do While --Condition--

--Do something--

Loop

Example 1:

Code:

Sub Do_While_Loop_Example_1()

    Dim iCounter As Integer
    Dim iSum As Integer
    
    'Initiate the variable to start from row 2
    iCounter = 2
    
    'Run the loop until blank cell is not found
    Do While Sheet1.Range("A" & iCounter).Value <> ""
    
        'Add the value of the cell in iSum variable
        iSum = iSum + Sheet1.Range("A" & iCounter).Value
        
        'Increament the counter by 1 to move the next row
        iCounter = iCounter + 1
        
    Loop
    
    'Store the sum in the blank cell
    Sheet1.Range("A" & iCounter).Value = iSum

End Sub

Result:

Explanation: This Do While loop runs until a blank cell is not found in column A. In each iteration of the loop, it adds the value of the cell in iSum variable. At the end of the loop, the code writes the value of iSum variable in the cell where iCounter is pointing. Note that iCounter variable will always point to a blank cell at the end of the loop.

Example 2:

Code:

Sub Do_While_Loop_Example_2()

    Dim iCounter As Integer
    
    'Initiate the variable to start from row 2
    iCounter = 2
    
    'Run the loop until blank cell is not found
    Do While Sheet1.Range("A" & iCounter).Value <> ""
    
        'Check if marks in Maths, English and Science are more than 33
        If Sheet1.Range("B" & iCounter).Value > 33 And Sheet1.Range("C" & iCounter).Value > 33 And Sheet1.Range("D" & iCounter).Value > 33 Then
        
            'Change the cell value in column E to Pass
            Sheet1.Range("E" & iCounter).Value = "Pass"
            
        Else
        
            'Change the cell value in column E to Fail
            Sheet1.Range("E" & iCounter).Value = "Fail"
            
        End If
        
        'Increament the counter by 1 to move the next row
        iCounter = iCounter + 1
    
    Loop

End Sub

Result:

Explanation: This Do While loop runs until a blank cell is not found in column A. In each iteration of the loop, it checks if the student’s marks are greater than 33 in all three subjects. If the student got more than 33 marks in all subjects then code writes ‘Pass’ in E column else ‘Fail’.

For variable = start To end Step value

--Do something--

If --Condition-- = True Then

Exit For

End If

Next variable

Example 1:

Code:

Sub Exit_For_Loop_Example_1()

    Dim iNumber As Integer
    Dim iCounter As Integer
    
    'Initiate the variable with a number
    iNumber = 22
    
    'Run the loop from 2 to number - 1
    For iCounter = 2 To iNumber - 1
        
        'Check if the number can be divided by the iCounter
        If iNumber Mod iCounter = 0 Then
            
            'If the number is dividable with iCounter then it is not a prime number
            MsgBox "It is not a Prime number"
            
            'Exit from the For loop
            Exit For
            
        End If
    Next

End Sub

Result:

Explanation: The procedure is checking if the given number is a Prime Number. Loop runs from 2 to iNumber -1 (here iNumber is 22). In each iteration of the loop, the code is checking if iNumber is divisible by the loop counter. If the number is divisible by iCounter that means it is not a prime number because prime number can only be divided by 1 or the number itself. When it is found that the number is not a prime number, it is showing message to user that it is not a prime number. Post identifying that it is not a prime number there is no sense to further run the loop; hence Exit For code is used to exit from the loop.

Example 2:

Code:

Sub Exit_For_Loop_Example_2()

    Dim iNumber As Integer
    Dim iCounter As Integer
    
    'Initiate variable
    iNumber = 3
    
    'Run the loop from 1 to 10
    For iCounter = 1 To 10
        
        'Multiple the number with counter and update in column A
        Sheet1.Range("A" & iCounter).Value = iNumber * iCounter
        
        'If the cell value is greater than 20 then exit from the loop
        If Sheet1.Range("A" & iCounter).Value > 20 Then
            Exit For
        End If
    Next

End Sub

Result:

Explanation: This For loop runs from 1 to 10. In each iteration of the loop, it first multiple the counter with number variable (here it is 3). The multiple result is written in A column of the sheet. Post this, it is checking if the number in the cell is greater than 20. If the cell value is greater than 20 then it is exiting from the loop using Exit For code.

Do While --Condition--

--Do something--

If --Condition-- = True Then

Exit Do

End If

Loop

Example 1:

Code:

Sub Exit_Do_While_Loop_Example_1()

    Dim iNumber As Integer
    Dim iSum As Integer
    Dim bContinue As Boolean
    
    'Set the variable to True
    bContinue = True
    
    'Run the while loop until bContinue variable is True
    Do While bContinue = True
        
        'Ask user to enter a number to add
        iNumber = InputBox("Enter a number to add")
        
        'Add number in Sum variable
        iSum = iSum + iNumber
        
        'Ask if the user wants to continue. If user enters No then exit from the loop
        If InputBox("Do you want to continue? Yes/No") = "No" Then
            Exit Do
        End If
    Loop

    'Show the sum to user
    MsgBox "Sum of the numbers is " & iSum

End Sub

Result:

Explanation: In this Do While loop we are adding the numbers entered by user and showing the sum of all numbers at the end of the loop. In the loop we are asking the user to enter a number, the given number is added in iSum variable. Post that, we are asking the user if he wants to continue. If the user enters No then call Exit Do code to exit from the loop. At the end of the loop, show the sum of the numbers to user.

Example 2:

Code:

Sub Exit_Do_While_Loop_Example_2()

    Dim iEnglish As Integer
    Dim iMaths As Integer
    Dim iScience As Integer
    Dim bContinue As Boolean
    
    'Set the variable to True
    bContinue = True
    
    'Run the while loop until bContinue variable is True
    Do While bContinue = True
            
        'Ask user to enter marks
        iEnglish = InputBox("Enter your marks in English")
        iMaths = InputBox("Enter your marks in Maths")
        iScience = InputBox("Enter your marks in Science")
        
        'If user's marks are greater than 33 in all three subject then show Pass else Fail message box
        If iEnglish > 33 And iMaths > 33 And iScience > 33 Then
            MsgBox "You are Pass"
        Else
            MsgBox "You are Fail"
        End If
        
        'Ask if the user wants to continue. If user enters No then exit from the loop
        If InputBox("Do you want to continue? Yes/No") = "No" Then
            Exit Do
        End If
        
    Loop

End Sub

Result:

Explanation: In this Do While loop we are checking if the user is pass or fail. In the loop we are first asking the user to enter his marks in three subjects. If user has got greater than 33 marks in all subjects then pass message box is displayed to the user else fail message box is displayed. Post that, we are asking the user if he wants to continue. If the user enters No then call Exit Do code to exit from the loop.