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 n As Integer
Dim nTotal As Integer


nTotal = 0


For n = 1 To 5


nTotal = n + nTotal


Next n

 

MsgBox nTotal


End Sub

 

 

The counter variable is "n", 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: "nTotal = n + nTotal".

 

Stepwise explanation:

 

In this example, the counter increments by the default step value of 1, and in the first loop (where n = 1), nTotal adds up to 1 (adds 1 to its initial value of  zero);

 

The Next statement increments the counter by 1 (n = 2) and returns to For statement, and in the second loop nTotal adds up to 3 (adds 2 to its previous value of 1);

 

The Next statement increments the counter by 1 (n = 3) and returns to For statement, and in the third loop nTotal adds up to 6 (adds 3 to its previous value of 3);

 

The Next statement increments the counter by 1 (n = 4) and returns to For statement, and in the fourth loop nTotal adds up to 10 (adds 4 to its previous value of 6);

 

The Next statement increments the counter by 1 (n = 5) and returns to For statement, and in the fifth loop nTotal adds up to 15 (adds 5 to its previous value of 10);

 

After executing the fifth loop, the Next statement increments the counter (ie. n) 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 n = 1 To 5 Step 2":

 

The counter increments by the default step value of 2, and in the first loop (where n = 1), nTotal adds up to 1 (adds 1 to its initial value of  zero);

 

The Next statement increments the counter by 2 (n = 3) and returns to For statement, and in the second loop nTotal adds up to 4 (adds 3 to its previous value of 1);

 

The Next statement increments the counter by 2 (n = 5) and returns to For statement, and in the third loop nTotal adds up to 9 (adds 5 to its previous value of 4);

 

After executing the third loop, the Next statement increments the counter (ie. n) 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 n = 5 To 1 Step -1":

 

In this example, the counter decrements by the step value of -1, and in the first loop (where n = 5), nTotal adds up to 5 (adds 5 to its initial value of  zero);

 

The Next statement decrements the counter by -1 (n = 4) and returns to For statement, and in the second loop nTotal adds up to 9 (adds 4 to its previous value of 5);

 

The Next statement decrements the counter by -1 (n = 3) and returns to For statement, and in the third loop nTotal adds up to 12 (adds 3 to its previous value of 9);

 

The Next statement decrements the counter by -1 (n = 2) and returns to For statement, and in the fourth loop nTotal adds up to 14 (adds 2 to its previous value of 12);

 

The Next statement decrements the counter by -1 (n = 1) and returns to For statement, and in the fifth loop nTotal adds up to 15 (adds 1 to its previous value of 14);

 

After executing the fifth loop, the Next statement decrements the counter (ie. n) 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, iCell is the Range Object variable, and the group or collection are all Cells in the Range("A1:A10").

 

 

Sub forEach2()


Dim iCell As Range


For Each iCell In ActiveSheet.Range("A1:A10")


iCell.Interior.Color = RGB(255, 255, 0)


Next iCell


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 iCell As Range


For Each iCell In ActiveSheet.Range("A1:A10")


With iCell


If iCell > 5 Then
.Interior.Color = RGB(255, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If


End With


Next iCell


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, nTotal will add up to the value 55. If Range("A1") contains the value 5, the loop will terminate and exit when the counter (ie. n) reaches 5, and nTotal will add up to 15 (Note that the loop runs for the counter value of 5, and thereafter exits the loop).

 

Sub exitFor1()


Dim n As Integer
Dim nTotal As Integer


nTotal = 0


For n = 1 To 10


nTotal = n + nTotal


If n = ActiveSheet.Range("A1") Then
Exit For
End If


Next n

 

MsgBox nTotal


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 (n>5) is tested at the start, and because it is not met, the loop does not execute even once. nTotal will return zero.

 

 

Sub doWhile1()

Dim n As Integer
Dim nTotal As Integer


n = 5
nTotal = 0


Do While n > 5


nTotal = n + nTotal


n = n - 1


Loop

 

MsgBox nTotal


End Sub

 

 

 

Example 2:

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

 

Sub doWhile2()

 

Dim n As Integer
Dim nTotal As Integer


n = 5
nTotal = 0


Do


nTotal = n + nTotal


n = n - 1


Loop While n > 5


MsgBox nTotal


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 iCell As Range
Dim textString As String
Dim n As Integer


'iCell is a Cell in the specified Range which contains the textString
'textString 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 textString


For Each iCell In ActiveSheet.Range("A1:A5")
textString = iCell
n = InStr(textString, " ")
'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 textString.


Do While n > 0


textString = Left(textString, n - 1) & "_" & Right(textString, Len(textString) - n)
'blank space is replaced with the underscore character in the textString


'textString = Left(textString, n - 1) & Right(textString, Len(textString) - n)
'Use this line of code instead of the preceding line, to remove all blank spaces in the textString


n = InStr(textString, " ")
Loop


iCell = textString


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, nTotal will add up to the value 55. If Range("A1") contains the value 5, the loop will terminate and exit when the counter (ie. n) reaches 5, and nTotal 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 n As Integer
Dim nTotal As Integer


nTotal = 0


Do While n < 11


nTotal = n + nTotal


n = n + 1


If n = ActiveSheet.Range("A1") Then
Exit Do
End If


Loop


MsgBox nTotal


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