VBA Break For Loop

What is Excel VBA Break For Loop?

VBA Break For loop is used when we wish to exit or break a continuous loop for certain criteria. It usually happens that some loops may become continuous and will corrupt the code if not broken. Thus, the Break For loop is used to break the infinite loop.

In the below example, we will break the For loop after the number reaches 5 and the control moves to the next statement after the For Loop. Write the below code in VB editor to check the function of the break For loop.

Code:

Sub Exit_Loop()
Dim x As Integer
For x = 1 To 5
If Cells(x, 1).Value = “” Then
Cells(x, 1).Value = x
Else
Exit For
End If
Next x
End Sub

Run the code by clicking the “Run” option in the VB ribbon and we will see the loop will stop executing after the number reaches 5.

How to Break/Exit Loops in VBA?

#1 – Break For Next Loop

Example: In this example, we will print the multiples of 5 till the value 10 and then give a condition to break the VBA loop. Please follow the below steps for your reference.

Step 1: Open the VB editor using Alt + F11 and insert a new module through Insert->Module. Start writing the sub-procedure and define x as an integer.

Step 2: Provide the value for x from 1 to 10.

Step 3: Now let us use the IF loop for inserting serial numbers from 1 to 10 in a loop. For this, we use the statement – If Cells(x, 2).Value = “”.

Then, each value of x is multiplied by 5. – Cells(x, 2).Value = x * 5 :

Step 4: We have provided a condition to check for an empty cell. If the above condition is not True, then Exit the For loop.

Code:

Sub Exit_Loop()
Dim x As Integer
For x = 1 To 10
If Cells(x, 2).Value = “” Then
Cells(x, 2).Value = x * 5
Else
Exit For
End If
Next x
End Sub

Step 5: Run the above code using F5 or the Run option in the VB ribbon and we will see the above code exit the loop after 50.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – Break Do Until Loop

In this section, we will look at how to exit the “Do Until” loop.

Step 1: Open the VB editor and Insert a new Module. Start with the sub-procedure and define the x as Long VBA data type.

Step 2: Use Do…Until to write the value of x till 15. If we want to exit the loop after x is greater than 7, mention this in the code as shown below:

Step 3: Write the below code to exit the loop as shown below:

Code:

Sub Exit_DoUntil_Loop()
Dim x As Long
x = 1
Do Until x = 15
If x < 8 Then
Cells(x, 1).Value = x
Else
Exit Do
End If
x = x + 1
Loop
End Sub

Step 4: Run the code either by pressing F5 or by selecting the Run option in the VB ribbon. We will see the loop is exited after the value 7.

Examples

Example #1

In this example, we will use Exit For to exit the loop once our criteria are satisfied. The criteria we set is as follows.

If the value of i reaches 6, we multiply it by 5 and exit the loop. Also, the results will be seen the message box.

Step 1: Open the VB editor and insert a new Module. In the Module, start with the sub procedure. Define a variable ‘a’ and assign it a value 10.

Step 2: We specify a condition where ‘i’ is a counter variable and is incremented by 2. For this, we write as, “For i = 0 To a Step 2.”

Step 3: Define a condition such that if i value reaches 6, it will be multiplied by 5 and will exit the loop after printing the value in the message box.

Code:

Sub VBA_BreakForLoop_Ex()
Dim a As Integer
a = 10
For i = 0 To a Step 2
MsgBox (“The value is i is : ” & i)
If i = 6 Then
i = i * 5
MsgBox (“The value is i is : ” & i)
Exit For
End If
Next
End Sub

Step 4: Run the code using the Run option in the VB ribbon to see the result as shown below. It will give us the value of i from 0 to increment of 2 till 6 and will multiply by 5 to give 30 value and will exit the loop.

Example #2

In this example, let us use the For loop and break out of it once it satisfies the criteria.

Step 1: Open a new module in VB editor and start the code by sub-procedure.

Step 2: Define an integer and its value.

Step 3: Start with a For loop for the defined integer and provide it a range; here, we have given a range from 1 till 20.

Step 4: Mention the criteria; When the value of i is 12, use Exit For as shown below.

Step 5: If the condition is not met, the following statement increases i by 1.

Step 6: The first line of the code which will be executed after exiting the For loop is the message box with the value of i.

Sub Break_Loop()
Dim i As Integer
For i = 1 To 20
If i = 12 Then
Exit For
End If
Next i
MsgBox “The loop is Exited as the value of i=12”
End Sub

Step.7: Run the code and we will see the message box pops up with the value of i=12.

Important Things to Note

• Exit or Break can be used for the For loop and many others such as Do…while.
• When Exit For is used in VBA, the control moves to the next statement instantly after the For Loop and will continue execution.
• We can also use Shortcut keys such as ESC button to break the infinite loop
• ESC button will only work if the code is not executing on a separate thread.
• VBA array size limit is of 60 dimensions.
• In VBA, to get the array size, we need to count the number of elements present in that array. To do so, we need to know the lowest element and the highest element. For this, the UBOUND and LBOUND functions are present which return the upper bound and lower bound, respectively.
• VBA Array size can be changed using variables. To do so, determine the array size and store it in the variable size. We can use the worksheet function CountA for this task
• To increase the size of array, a dynamic array is used. It is a place where we can resize the array and add more value to it while running the code.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand VBA Break For Loop, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Break For Loop. Here we explain how to Exit/Break the continuous Loop using Exit For in VBA with examples & downloadable excel template. You can learn more from the following articles –

• VBA Do Loop
• VBA Set Range
• RegEx in VBA