Excel VBA Break For Loop
VBA Break is used when we want to exit or break the continuous loop which has certain fixed criteria. For loop is within the scope range defines the statements which get executed repeatedly for a fixed number of time. Sometimes when we use any loop condition in VBA, it often happens that the code keeps on running without an end or break. To avoid this kind of situations it is recommended to use some breaks or exit functions by which we can exit from the loop with the measurable output from the macro.
How to Use Excel VBA Break For Loop?
We will learn how to use a VBA Break for Loop with few examples in Excel.
Example #1 – VBA Break For Loop
We will see an example where when using For loop the criteria arrange will not be broken. But to fix this we will use the If-End loop. For this, we need a Module where we will write this code.
Follow the below steps to use Break for Loop in Excel VBA.
Step 1: Open a new module go to the VBA window and in that select Module from the Insert menu option.
Step 2: This will take us to a new module in VBA. Now in that module write Subcategory in the name of VBA Break For Loop or in any other name which defines its meaning.
Code:
Sub VBABreak1() End Sub
Step 3: Now define a DIM as any alphabet or word. This gives it an identity. Here we are using the alphabet “A” below.
Code:
Sub VBABreak1() Dim A End Sub
Step 4: Now assign it as Integer which is used for numbers only as shown below.
Code:
Sub VBABreak1() Dim A As Integer End Sub
Step 5: Now assign a numeric value to Integer A. Let’s say it is 10 as shown below.
Code:
Sub VBABreak1() Dim A As Integer A = 10 End Sub
Step 6: Now start a For loop and consider any starting value of A. It is better to take this value as Zero. This becomes easy for calculation.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 End Sub
Step 7: And to print the value stored in A, we will use a message box that will fetch its value. You can use any form of a sentence as well to make the message box more meaningful which is optional. We have used it on as shown below.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 MsgBox ("The value is A is : " & A) Next End Sub
Step 8: Now compile the code step-by-step by using functional key F8 and after that run the code by clicking on the Play button as shown below. We will see the output in every step will be from 0 to 10 in the step gap of 2 as shown below.
Step 9: Now at last close For Loop and also use Next at the end. The “Next” is used when we are using numbers in the code.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 MsgBox ("The value is A is : " & A) Exit For Next End Sub
Step 10: This shows that the loop is not complete and we need to fill the exit criteria as well to exit from For loop. And for that, we will use If-End Loop condition. So consider writing If loop for same Integer A. Here choose the limit till you want to run the loop. We have considered it as 4.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 MsgBox ("The value is A is : " & A) If A = 4 Then Exit For Next End Sub
Step 11: Now let’s add one more criterion in which we will use how many times it would get multiplied by any number. Let’s say it is 10. You can choose any other number instead of 10. But using 10 makes easy to understand the multiplication loop.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 MsgBox ("The value is A is : " & A) If A = 4 Then A = A * 10 Exit For Next End Sub
Step 12: Now again use name message box as we have used for For loop and at lastly close the If Loop with End if statement as shown below.
Code:
Sub VBABreak1() Dim A As Integer A = 10 For A = 0 To A Step 2 MsgBox ("The value is A is : " & A) If A = 4 Then A = A * 10 MsgBox ("The value is A is : " & A) Exit For End If Next End Sub
Step 13: Again compile the code and run it. For Loop will start from showing a message with value 0 and then followed by 2, 4 and ending it 40. Which means For Loop got broken when at 3rd iteration and took the last value as 40.
Example #2 – VBA Break For Loop
In this example, we will apply For loop and will see how to break the loop once it satisfies the criteria.
Step 1: Open a new module from the Insert menu option and give it a Subcategory in any name or better in a sequential manner as shown below.
Code:
Sub VBABreak2() End Sub
Step 2: Now define a DIM A as Integer. Using Integer for test allow us to implement the code faster and debug easy.
Code:
Sub VBABreak2() Dim A As Integer End Sub
Step 3: Now start a For loop for defined integer and give it a range from any row count starting from 1. We have chosen till 20.
Code:
Sub VBABreak2() Dim A As Integer For A = 1 To 20 End Sub
Step 4: Now give a range or location from where the numbers are getting printed in sequence. We used ThisWorkBook to select current opened file and Worksheets(1) for selecting the first sheet of an opened workbook as shown below. Now to close the For loop write Next at the end as shown below.
Code:
Sub VBABreak2() Dim A As Integer For A = 1 To 20 ThisWorkbook.Worksheets(1).Cells(A, 1) = A Next End Sub
Step 5: Run the above code manually or using the shortcut key F5. We can see that the code has given number count from 1 to 20.
Step 6: Now to break the loop in between from 0 to 20, we will use If-End if loop condition where we will give the criteria to fall in as shown below.
Code:
Sub VBABreak2() Dim A As Integer For A = 1 To 20 If End If ThisWorkbook.Worksheets(1).Cells(A, 1) = A Next End Sub
Step 7: Now write the criteria, If A is greater than 9 (Or any number but less than 20) then Exit For as shown below. We suppose For Loop should get broken when loop reaches 9.
Code:
Sub VBABreak2() Dim A As Integer For A = 1 To 20 If A > 9 Then Exit For End If ThisWorkbook.Worksheets(1).Cells(A, 1) = A Next End Sub
Step 8: Now compile the code to find the error. If no error found then run it. We will see for integer A values from 1 to 20 have been filled till the code exited or broken at position 9.
Pros of VBA Break For Loop
- Exit or Break can be used not only for For loop but in many other loops as well such as Do-While.
- This is most frequently used among all the loop conditions available in VBA.
- For Loop is quite easy and can work even without Exit criteria.
Things to Remember
- For loop can work without giving the Exit criteria. It can work fine until the criteria without Exit is breaking the loop to end.
- It is important to Break any loop which doesn’t satisfy the exit criteria by using Exit in the loop.
- After writing the code, save the file as Macro Enable Excel so that written code will be preserved.
Recommended Articles
This has been a guide to VBA Break For Loop. Here we discuss how to use Excel VBA Break For Loop along with practical examples and downloadable excel template. You can also go through our other suggested articles –
