Loops and Exit Loops in VBA

1. VBA For Loop
2. VBA Do Until Loop
3. VBA Do While Loop
4. Utilizing the Exit Command to Force Stop the Looping in VBA

Loops in computer programming are very important. They allow repetitive tasks to be executed with a minimum of lines of code. The need for loops in computers arises for several reasons depending on the tasks to be performed.

Even though they are so powerful, yet they are simple in context and syntax.

In VBA, there are three loops that can be utilized.

1. For Loop - Loop within predefined or fixed limits
2. Do While Loop - Loops while the condition is True.
3. Do Until Loop - Loops until the condition is True.

VBA For Loop

Syntax:

For [ counter ] = [start] To [end]  Step [ step ]
    [ statements ]
    [ Exit For ]
Next [ counter ]

Parameters:

The For Loop Example:

This code block will accept an integer value as its starting number, then will decrease the number until zero using a For Loop. It will print the result of each iteration.

Sub ReduceToZero(numStart As Integer)

Dim i As Integer

For i = numStart To 0 Step -1


    Debug.Print "The number is now " & i
    If i = 0 Then

        Debug.Print ("Done!")

    End If

Next i

End Sub

Sub testSub()

Call ReduceToZero(10)

End Sub

Output:

The number is now 10
The number is now 9
The number is now 8
The number is now 7
The number is now 6
The number is now 5
The number is now 4
The number is now 3
The number is now 2
The number is now 1
The number is now 0
Done!

VBA Do Until Loop

Syntax:

Do Until [condition]
    [statements]
    [Exit Do]
Loop

Parameters:

Do Until Loop Example:

This code block will accept an integer value as its starting number, then will increase the number until ten using a Do Until loop. It will print the result of each iteration.

Sub IncrementToTen(numStart As Integer)

Dim i As Integer

i = numStart

Do Until i = 10 + 1

    Debug.Print "The number is now " & i
    i = i + 1
Loop

End Sub

Sub testSub()

Call IncrementToTen(-5)

End Sub

testSub Output:

The number is now -5
The number is now -4
The number is now -3
The number is now -2
The number is now -1
The number is now 0
The number is now 1
The number is now 2
The number is now 3
The number is now 4
The number is now 5
The number is now 6
The number is now 7
The number is now 8
The number is now 9
The number is now 10
Done!

VBA Do While Loop

Syntax:

Do while [condition]
    [statements]
    [Exit Do]
Loop

Parameters:

The Do While Loop Example:

This code block will accept an integer value as its starting number, then will increase the number until ten using a Do While loop. It will print the result of each iteration.

Sub IncrementToTen(numStart As Integer)

Dim i As Integer
i = numStart
Do While i < 10 + 1

Debug.Print "The number is now " & i
i = i + 1
Loop
Debug.Print "Done!"
End Sub

Sub testSub()

Call IncrementToTen(-9)

End Sub

testSub Output:

The number is now -9
The number is now -8
The number is now -7
The number is now -6
The number is now -5
The number is now -4
The number is now -3
The number is now -2
The number is now -1
The number is now 0
The number is now 1
The number is now 2
The number is now 3
The number is now 4
The number is now 5
The number is now 6
The number is now 7
The number is now 8
The number is now 9
The number is now 10
Done!

Utilizing the Exit Command to Force Stop the Looping in VBA

When dealing with loops, they are bound with conditions that the loop will continue to execute when fulfilled. However, a common scenario is that we need to exit the loop, even if the conditions are still met. A typical application for this is when dealing with Error-handling methods, the Stop-when-search technique. An immediate exit in the loop is needed to prevent further errors or save execution time.

Below code blocks will demonstrate the use of the Exit command to force the looping to stop in a Do Loop.

This code block aims to stop the loop when the quotient of two numbers is one. These two numbers are randomly generated between the upperbound and lowerbound integer values, the subroutine’s parameters. If the generated divisor (divis) is zero, then an error would occur since divizion by zero is not allowed. To avoid the occurrence, we will use the Exit command to terminate the loop.

Sub StopWhenQuotientIsOne(upperbound As Integer, lowerbound As Integer)

Dim divid As Integer
Dim divis As Integer
Dim isOne As Boolean

divid = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
divis = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Do Until isOne = True

    If divis = 0 Then
        Debug.Print "Illegal divisor. Exiting the Loop"
        Exit Do
    End If

Debug.Print divid / divis

If divid / divis = 1 Then

    isOne = True
    Debug.Print "Done! One is achieved."

End If

divid = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
divis = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Loop


End Sub

Sub testSub()

Call StopWhenQuotientIsOne(4, -4)

End Sub

testSub Output (1st Trial):

-4 
-0 
-0.666666666666667 
Illegal divisor. Exiting the Loop

testSub Output (4th Trial):

-2 
 0 
-3 
 1 
Done! One is achieved.

The below code block will demonstrate the use of Exit command to force the looping to stop in a For Loop.

On this example, StopWhenNegative subroutine requires two parameters. First is the startNum then EndNum. The loop will iterate from startNum till EndNum. If the counter goes negative, the loop will be terminated by the Exit command.

Sub StopWhenNegative(startNum As Integer, EndNum As Integer)

Dim i As Integer
 
For i = startNum To EndNum Step -1
 
    If i < 0 Then
        Debug.Print "Opps, negative values detected. Exiting loop."
        Exit For
    End If
Debug.Print "The current number is :" & i
Next i

End Sub

Sub testSub()

Call StopWhenNegative(10, -5)

End Sub

testSub Output:

The current number is :10
The current number is :9
The current number is :8
The current number is :7
The current number is :6
The current number is :5
The current number is :4
The current number is :3
The current number is :2
The current number is :1
The current number is :0
Opps, negative values detected. Exiting loop.