Loops and Exit Loops in VBA

Glen Alfaro Feb 06, 2023 VBA VBA Loops

VBA For Loop
VBA Do Until Loop
VBA Do While Loop
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.

For Loop - Loop within predefined or fixed limits
Do While Loop - Loops while the condition is True.
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:


`[ counter ]`	Required. Typically an integer or long variable that holds the current value of the counter
`[ start ]`	Required. The start value of the counter
`[end]`	Required. The end value of the counter
`[ statements ]`	The code block to execute while inside the loop
`[ Exit For ]`	Optional. Force the looping to stop and exit the loop code block
`[ step ]`	Optional. The increment of the counter every iteration. If not declared, the value is `1`.

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:


`[condition]`	Required. The condition that once true will exit the code block
`[statements]`	Required. The code block to execute
`[Exit Do]`	Optional. Force the looping to stop and exit the code block

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:


`[condition]`	Required. The condition that needs to be true to execute the code block.
`[statements]`	Required. The code block to execute
`[Exit Do]`	Optional. Force the looping to stop and exit the code block

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.

Enjoying our tutorials? Subscribe to DelftStack on YouTube to support us in creating more high-quality video guides. Subscribe

VBA For Loop

VBA Do Until Loop

VBA Do While Loop

Utilizing the Exit Command to Force Stop the Looping in VBA

VBA `For` Loop

VBA `Do Until` Loop

VBA `Do While` Loop

Utilizing the `Exit` Command to Force Stop the Looping in VBA