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 variable = start To end Step value
--Do something--
Next variableExample 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 SubResult:
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 SubResult:
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 Loop
For Each Object in ObjectCollection
--Do something--
NextExample 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 SubResult:
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 SubResult:
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 Loop
While --Condition--
--Do something--
WendExample 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 SubResult:
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 SubResult:
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 Loop
Do While --Condition--
--Do something--
LoopExample 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 SubResult:
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 SubResult:
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’.
Exit For
For variable = start To end Step value
--Do something--
If --Condition-- = True Then
Exit For
End If
Next variableExample 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 SubResult:
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 SubResult:
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.
Exit Do
Do While --Condition--
--Do something--
If --Condition-- = True Then
Exit Do
End If
LoopExample 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 SubResult:
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 SubResult:
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.
