VBA Tutorial Login  

Free! Excel VBA Online Tutorial

   

Excel VBA

Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop.

User Rating:  / 16
PoorBest 
Details

 

Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop.

 

 

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 Loop

 

The For … Next Statements

 

The ForNext 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 EachNext 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

 

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
n = InStr(strText, " ")
'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.


Do While n > 0


strText = Left(strText, n - 1) & "_" & Right(strText, Len(strText) - n)
'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


n = InStr(strText, " ")
Loop


rCell = strText


Next


End Sub

 

 

 

The Exit Do Statement

 

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

 

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

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.