Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop.
——————————————————————————————–
Contents:
The For Each … Next Statements
The Do While … Loop Statements; The Do … Loop While Statements
The Do Until … Loop Statements; The Do … Loop Until Statements
More Examples of using VBA Loops
——————————————————————————————–
Loops are one of the most basic and powerful programming tools in VBA, and used across most programming languages. Loops are used to repeat a block of code as many times as required, until a given condition remains true or a specific point (or value) is reached, after which the the next section of code is executed. A loop enables you to write a few simple lines of code and achieve a far more significant output, just by repetition.
There are three basic kinds of VBA Loops (subdivided into 6 loops as below):
The For Loop
The For … Next Statements
The For Each … Next Statements
The Do While Loop
The Do While … Loop Statements
The Do … Loop While Statements
The Do Until Loop
The Do Until … Loop Statements
The Do … Loop Until Statements
The For … Next Loop repeats a block of code a specific number of times.
For counter_variable = start_value To end_value
[block of code]
Next counter_variable
This is explained with the help of a simple example:
Sub forNext1()
Dim i As Integer
Dim iTotal As Integer
iTotal = 0
For i = 1 To 5
iTotal = i + iTotal
Next i
MsgBox iTotal
End Sub
The counter variable is “i”, which is required to be declared. The start_value of the counter is 1, and its end_value is 5, both numeric values. Mentioning “Step” keyword is optional – this is a numeric value by which the counter is incremented each time the loop is run. The default step value is 1, unless specified. The Next statement increments the counter by the step value, and returns to the For statement, which repeats the block of code if the counter value does not exceed the “end” value of 5. If counter is equal to “end” value, the loop will continue; it stops when the “end” value is exceeded. The block of code which is repeated in this loop is: “iTotal = i + iTotal”.
Stepwise explanation:
In this example, the counter increments by the default step value of 1, and in the first loop (where i = 1), iTotal adds up to 1 (adds 1 to its initial value of zero);
The Next statement increments the counter by 1 (i = 2) and returns to For statement, and in the second loop iTotal adds up to 3 (adds 2 to its previous value of 1);
The Next statement increments the counter by 1 (i = 3) and returns to For statement, and in the third loop iTotal adds up to 6 (adds 3 to its previous value of 3);
The Next statement increments the counter by 1 (i = 4) and returns to For statement, and in the fourth loop iTotal adds up to 10 (adds 4 to its previous value of 6);
The Next statement increments the counter by 1 (i = 5) and returns to For statement, and in the fifth loop iTotal adds up to 15 (adds 5 to its previous value of 10);
After executing the fifth loop, the Next statement increments the counter (ie. i) value to 6 and returns to the For statement, but this does not get executed because its value has gone beyond the “end” value of 5 specified here.
MsgBox will display the value 15.
In the above example, if the For statement is changed to “For i = 1 To 5 Step 2”:
The counter increments by the default step value of 2, and in the first loop (where i = 1), iTotal adds up to 1 (adds 1 to its initial value of zero);
The Next statement increments the counter by 2 (i = 3) and returns to For statement, and in the second loop iTotal adds up to 4 (adds 3 to its previous value of 1);
The Next statement increments the counter by 2 (i = 5) and returns to For statement, and in the third loop iTotal adds up to 9 (adds 5 to its previous value of 4);
After executing the third loop, the Next statement increments the counter (ie. i) value to 7 and returns to the For statement, but this does not get executed because its value has gone beyond the “end” value of 5 specified here.
MsgBox will display the value 9.
Negative step values (count backword from a higher to a lower value): in the above example, the For statement is changed to “For i = 5 To 1 Step -1”:
In this example, the counter decrements by the step value of -1, and in the first loop (where i = 5), iTotal adds up to 5 (adds 5 to its initial value of zero);
The Next statement decrements the counter by -1 (i = 4) and returns to For statement, and in the second loop iTotal adds up to 9 (adds 4 to its previous value of 5);
The Next statement decrements the counter by -1 (i = 3) and returns to For statement, and in the third loop iTotal adds up to 12 (adds 3 to its previous value of 9);
The Next statement decrements the counter by -1 (i = 2) and returns to For statement, and in the fourth loop iTotal adds up to 14 (adds 2 to its previous value of 12);
The Next statement decrements the counter by -1 (i = 1) and returns to For statement, and in the fifth loop iTotal adds up to 15 (adds 1 to its previous value of 14);
After executing the fifth loop, the Next statement decrements the counter (ie. i) value to -1 and returns to the For statement, but this does not get executed because its value has gone beyond the “end” value of 1 specified here.
MsgBox will display the value 15.
The For Each … Next Statements
The For Each … Next Loop repeats a block of code for each object in a group. It repeats execution of a block of code, for each element of a collection. The loop stops when all the elements in the collection have been covered, and execution moves to the section of code immediately following the Next statement.
For Each object_variable In group_object_variable
[block of code]
Next object_variable
Example:
This loops through each worksheet in the workbook, and the code protects each with a password. Here, ws is the Worksheet Object variable, and the group or collection are all the Worksheets in this Workbook.
Sub forEach1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=“123”
Next ws
End Sub
Example:
This loops through each cell in the Range(“A1:A10”), and the code sets background color of yellow in each. Here, rCell is the Range Object variable, and the group or collection are all Cells in the Range(“A1:A10”).
Sub forEach2()
Dim rCell As Range
For Each rCell In ActiveSheet.Range(“A1:A10”)
rCell.Interior.Color = RGB(255, 255, 0)
Next rCell
End Sub
Nesting Loops:
You can nest loops by putting one loop within another loop, upto unlimited number of times. The counter variable for each loop must be unique. You can nest one kind of loop within another different kind of loop. In a For Loop, it is necessary that the inner loop be completed before the Next statement of the outer loop is encountered . You can also nest one kind of control structure within another kind viz. you can nest an IF statement within a WITH block which can itself be nested within a For … Each Loop. However, control structures cannot be overlapped viz. each nested block has to close & terminate within its outer nested level.
Example:
Example of nesting an IF statement within a WITH block which is nested within a For … Each Loop. The code loops through each cell in the range A1:A10, and if cell value exceeds 5, the background color of that cell is set as Yellow, else Red for values of 5 and less.
Sub nestingLoops()
Dim rCell As Range
For Each rCell In ActiveSheet.Range(“A1:A10”)
With rCell
If rCell > 5 Then
.Interior.Color = RGB(255, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End With
Next rCell
End Sub
The Exit For Statement
You can exit the For Loop (both For … Next and For Each … Next Statements) early, without completing the full cycle, by using the Exit For statement. The Exit For statement will immediately stop execution of the existing loop and execute the section of code immediately following the Next statement, and in the case of inner nested level it will stop and execute the next outer nested level. You can have any number of Exit For statements in a loop. It is particularly useful in case you want to terminate the loop on reaching a certain value or satisfying a specific condition, or in case you want to terminate an endless loop at a certain point.
Example:
If Range (“A1”) is blank, iTotal will add up to the value 55. If Range(“A1”) contains the value 5, the loop will terminate and exit when the counter (ie. i) reaches 5, and iTotal will add up to 15 (Note that the loop runs for the counter value of 5, and thereafter exits the loop).
Sub exitFor1()
Dim i As Integer
Dim iTotal As Integer
iTotal = 0
For i = 1 To 10
iTotal = i + iTotal
If i = ActiveSheet.Range(“A1”) Then
Exit For
End If
Next i
MsgBox iTotal
End Sub
The Do While … Loop Statements; The Do … Loop While Statements
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 the condition turns False. The condition can be tested either at the start or at the end of the Loop. “The Do While … Loop Statements” test the condition at the start, while “The Do … Loop While Statements” test the condition at the end of the Loop. If the condition is tested at the start of the Loop, the block of code does not execute if the condition is not met initially (and the loop does not run even once) whereas if the condition is tested at the end, the Loop runs atleast once.
The Do While … Loop Statements (The condition is tested at the start, in this Loop)
Do While [Condition]
[block of code]
Loop
The Do … Loop While Statements (The condition is tested at the end, in this Loop)
Do
[block of code]
Loop While [Condition]
These two statements are explained with the help of examples.
Example 1:
The condition (i>5) is tested at the start, and because it is not met, the loop does not execute even once. iTotal will return zero.
Sub doWhile1()
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
Example 2:
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 doWhile2()
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
Example 3:
Replace blank spaces with underscores in a Range of Cells, using VBA loops; or Remove blank spaces in a Range of Cells, using VBA loops.
Sub doWhile3()
‘Replace blank spaces with underscores in a Range of Cells, using VBA loops; or Remove blank spaces in a Range of Cells, using VBA loops.
Dim rCell As Range
Dim strText As String
Dim n As Integer
‘rCell is a Cell in the specified Range which contains the strText
‘strText is the text in a Cell in which blank spaces are to be replaced with underscores
‘n is the position of blank space(s) occurring in a strText
For Each rCell In ActiveSheet.Range(“A1:A5”)
strText = rCell
‘the VBA InStr function returns the position of the first occurrence of a string within another string. Using this to determine the position of the first blank space in the strText.
n = InStr(strText, ” “)
Do While n > 0
‘blank space is replaced with the underscore character in the strText
strText = Left(strText, n – 1) & “_” & Right(strText, Len(strText) – n)
‘Use this line of code instead of the preceding line, to remove all blank spaces in the strText
‘strText= Left(strText, n – 1) & Right(strText, Len(strText) – n)
n = InStr(strText, ” “)
Loop
rCell = strText
Next
End Sub
You can exit the Do While Loop early, without completing the full cycle, by using the Exit Do statement. The Exit Do statement will immediately stop execution of the existing loop and execute the section of code immediately following the Loop statement, and in the case of inner nested level it will stop and execute the next outer nested level. You can have any number of Exit Do statements in a loop. It is particularly useful in case you want to terminate the loop on reaching a certain value or satisfying a specific condition, or in case you want to terminate an endless loop at a certain point. It is similar to the Exit For statement used to exit the For Loop.
Example:
If Range (“A1”) is blank, iTotal will add up to the value 55. If Range(“A1”) contains the value 5, the loop will terminate and exit when the counter (ie. i) reaches 5, and iTotal will add up to 10 (Note that the loop does not run for the counter value of 5, and exits the loop on reaching this value).
Sub exitDo1()
Dim i As Integer
Dim iTotal As Integer
iTotal = 0
Do While i < 11
iTotal = i + iTotal
i = i + 1
If i = ActiveSheet.Range(“A1”) Then
Exit Do
End If
Loop
MsgBox iTotal
End Sub
The Do Until … Loop Statements; The Do … Loop Until Statements
The Do Until Loop repeats a block of code indefinitely until the condition is met and evaluates to True. The condition can be tested either at the start or at the end of the Loop. “The Do Until … Loop Statements” test the condition at the start, while “The Do … Loop Until Statements” test the condition at the end of the Loop. If the condition is tested at the start of the Loop, the block of code does not execute if the condition is met initially itself (and the loop does not run even once) whereas if the condition is tested at the end, the Loop runs atleast once.
The Do Until … Loop Statements (The condition is tested at the start, in this Loop)
Do Until [Condition]
[block of code]
Loop
The Do … Loop Until Statements (The condition is tested at the end, in this Loop)
Do
[block of code]
Loop Until [Condition]
These two statements are explained with the help of examples.
Example 1:
Sub doUntil1()
‘Colors the empty cells yellow, until a non-empty cell is encountered. If the first cell is not empty, the code will not execute because the condition of “Not Empty” is mentioned at the start of the loop.
Dim rowNo As Integer
rowNo = 1
Do Until Not IsEmpty(Cells(rowNo, 1))
Cells(rowNo, 1).Interior.Color = RGB(255, 255, 0)
rowNo = rowNo + 1
Loop
End Sub
Example 2:
Sub doUntil2()
‘Colors the empty cells yellow, until a non-empty cell is encountered. If the first cell is not empty, the code will still execute atleast once because the condition of “Not Empty” is mentioned at the end of the loop.
Dim rowNo As Integer
rowNo = 1
Do
Cells(rowNo, 1).Interior.Color = RGB(255, 255, 0)
rowNo = rowNo + 1
Loop Until Not IsEmpty(Cells(rowNo, 1))
End Sub
The Exit Do Statement
You can exit the Do Until Loop early, without completing the full cycle, by using the Exit Do statement. It is similar to as in the Do While Loop, mentioned above.
More Examples of using VBA Loops:
——————————————————————————————————————–
Sub loopExample1()
‘enter +ive and -ive numbers alternatively, in a column. Refer Image 1.
Dim n As Integer
For n = 1 To 10 Step 2
ActiveSheet.Cells(n, 1) = n * 2
Next n
For n = 2 To 10 Step 2
ActiveSheet.Cells(n, 1) = n * -2
Next n
End Sub
——————————————————————————————————————–
Sub loopExample2()
‘enter numbers in even rows only. Refer Image 2.
Dim n As Integer
n = 1
Do While n < 11
If n Mod 2 = 0 Then
ActiveSheet.Cells(n, 1) = n * 3
End If
n = n + 1
Loop
End Sub
——————————————————————————————————————–
Sub pyramidEx1()
‘Create a number pyramid with VBA loops. Refer Image 3.
Dim iRow As Integer, n As Integer, strText As String
‘indicates number of rows
For iRow = 1 To 5
‘each row is having number of digits which equal to the row number
For n = 1 To iRow
strText = strText & iRow
Next n
‘to avoid creating line feed after the fifth row
If iRow < 5 Then
strText = strText & vbCrLf
End If
Next iRow
ActiveSheet.Range(“A15”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx2()
‘Create a number pyramid with VBA loops. Refer Image 4.
Dim iRow As Integer, n As Integer, strText As String
‘indicates number of rows
iRow = 5
‘each row is having equal number of digits as the row number
For n = 1 To iRow
‘to avoid creating line feed after the last row
If n < iRow Then
‘the VBA “String” function creates a string consisting of a single character repeated a specified number of times. Note that the VBA function of Format() returns a string/text value.
strText = strText & String(n, Format(n, “0”)) & vbCrLf
Else
strText = strText & String(n, Format(n, “0”))
End If
Next n
ActiveSheet.Range(“A17”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx3()
‘Create a number pyramid with VBA loops. Refer Image 5.
Dim iRow As Integer, n As Integer, strText As String
For iRow = 1 To 5
For n = 1 To iRow
strText = strText & iRow * 2 – 1
Next n
If iRow < 5 Then
‘Chr(10) provides line feed/new line
strText = strText & Chr(10)
End If
Next iRow
ActiveSheet.Range(“A21”) = strText
‘MsgBox strText
End Sub
————————————————————————————————————————
Sub pyramidEx4()
‘Create a number pyramid with VBA loops. Refer Image 6.
Dim iRow As Integer, n As Integer, strText As String
For iRow = 5 To 1 Step -1
For n = 1 To iRow
strText = strText & iRow
Next n
If iRow > 1 Then
strText = strText & vbCrLf
End If
Next iRow
ActiveSheet.Range(“A23”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx5()
‘Create a number pyramid with VBA loops. Refer Image 7.
Dim iRow As Integer, n As Integer, m As Integer, strText As String
For iRow = 1 To 5
m = iRow
For n = 1 To iRow
strText = strText & m & ” “
m = m + 1
Next n
If iRow < 5 Then
strText = strText & vbCrLf
End If
Next iRow
ActiveSheet.Range(“A25”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx6()
‘Create a number pyramid with VBA loops. Refer Image 8.
Dim iRow As Integer, n As Integer, m As Integer, strText As String
m = 1
For iRow = 1 To 4
For n = 1 To iRow
strText = strText & m & ” “
m = m + 1
Next n
If iRow < 4 Then
strText = strText & vbCrLf
End If
Next iRow
ActiveSheet.Range(“A27”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx7()
‘Create a number pyramid with VBA loops. Refer Image 9.
Dim iRow As Integer, n As Integer, m As Integer, strText As String
For iRow = 1 To 5
m = iRow
For n = 1 To iRow
strText = strText & m & ” “
m = m + 2
Next n
If iRow < 5 Then
strText = strText & vbCrLf
End If
Next iRow
ActiveSheet.Range(“A29”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx8()
‘Create a pyramid with VBA loops. Refer Image 10.
Dim iRow As Integer, n As Integer, strText As String
iRow = 5
For n = 1 To iRow
‘to avoid creating line feed after the last row
If n < iRow Then
‘the VBA “String” function creates a string consisting of a single character repeated a specified number of times. Chr(10) provides line feed/new line.
strText = strText & String(n, “#”) & Chr(10)
Else
strText = strText & String(n, “#”)
End If
Next
ActiveSheet.Range(“A31”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub pyramidEx9()
‘Create a pyramid with VBA loops. Refer Image 11.
Dim iRow As Integer, n As Integer, strText As String
iRow = 5
For n = 1 To iRow * 2 Step 2
iRow = iRow – 1
If n < 8 Then
strText = strText & String(iRow, “-“) & String(n, “*”) & vbCrLf
Else
strText = strText & String(iRow, “-“) & String(n, “*”)
End If
Next
ActiveSheet.Range(“A33”) = strText
‘MsgBox strText
End Sub
——————————————————————————————————————–
Sub palindromeCheck1()
‘Code to check if string is a Palindrome. A palindrome is a word, phrase, number, line or sequence that reads the same forward as it does backward.
‘Note: Code does not differentiate upper and lower case, and does not ignore spacing.
‘This code divides the text length by 2 and checks letters on each side with the other, to determine if text is Palindrome or not.
Dim strText As String, n As Integer
strText = “Racecar”
‘remove this line to make the check case-sensitive
strText = UCase(strText)
‘divide text length by 2 to check letters on each side with the other
For n = 1 To Len(strText) / 2
If Mid(strText, n, 1) <> Mid(strText, Len(strText) – n + 1, 1) Then
MsgBox “Not a Palindrome”
Exit Sub
End If
Next n
MsgBox “Palindrome”
End Sub
——————————————————————————————————————–
Sub palindromeCheck2()
‘Code to check if string is a Palindrome. A palindrome is a word, phrase, number, line or sequence that reads the same forward as it does backward.
‘Note: Code does not differentiate upper and lower case, and does not ignore spacing.
‘This code builds the reverse text and checks the original and reverse texts, to determine if Palindrome or not.
Dim strText As String, revText as String, n As Integer
strText = “Level”
‘remove this line to make the check case-sensitive
strText = LCase(strText)
revText = “”
For n = Len(strText) To 1 Step -1
‘building the reverse text to compare with original text
revText = revText & Mid(strText, n, 1)
Next n
If strText = revText Then
MsgBox “Palindrome”
Else
MsgBox “Not a Palindrome”
End If
End Sub