EDUCBA

Home VBA VBA Resources VBA Logical Functions VBA Break for Loop

VBA Break for Loop

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 12, 2023

VBA Break for loop

 

 

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, the code often keeps running without an end or break. It is recommended to avoid this kind of situation to use some breaks or exit functions by which we can exit from the loop with the measurable output from the macro.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use Excel VBA Break For Loop?

We will learn how to use a VBA Break for Loop with a few examples in Excel.

You can download this VBA Break For Loop Excel Template here – VBA Break For Loop Excel Template

Example #1 – VBA Break For Loop

We will see an example where the criteria arrangement will not be broken when using For loop. 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 select Module from the Insert menu option.

New Module

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 any other name defining its meaning.

Code:

Sub VBABreak1()

End Sub

Break loop example 1

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

Break loop example 2

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

Break loop example 3

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

Break loop example 4

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 to calculate.

Code:

Sub VBABreak1()

   Dim A As Integer
   A = 10

   For A = 0 To A Step 2

End Sub

Break loop example 5

Step 7: To print the value stored in A, we will use a message box to fetch its value. You can also use any form of a sentence to make the message box more meaningful, which is optional. We have used it 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

Break loop example 6

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. The output in every step will be from 0 to 10 in the step gap of 2, as shown below.

break vba 1

Step 9: Now, at last, close For loop and also use Next at the end. The “Next” is used when 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

Break loop example 7

Step 10: This shows that the loop is incomplete, and we must fill in the exit criteria to exit from For loop. And for that, we will use the If-End Loop condition. So consider writing the If loop for the 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

VBA IF condition

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 it 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

VBA Break

Step 12: Again, use the name message box we used for For loop and 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

MsgBox 2

Step 13: Again, compile the code and run it. For loop will start by showing a message with the value 0 and then followed by 2, 4, and ending it 40. This means For loop got broken at the 3rd iteration and took the last value as 40.

break vba 2

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, sequentially, as shown below.

Code:

Sub VBABreak2()

End Sub

VBA Break loop example 1

Step 2: Now define a DIM A as Integer. Using Integer for tests allows us to implement the code faster and debug easily.

Code:

Sub VBABreak2()

  Dim A As Integer

End Sub

Dim Integer

Step 3: Start a For loop for a 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

For condition

Step 4: Now give a range or location from where the numbers are printed in sequence. We used ThisWorkBook to select the current opened file and Worksheets(1) for selecting the first sheet of an opened workbook, as shown below. 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

Worksheet

Step 5: Run the above code manually or using the shortcut key F5. We can see that the code has given a number count from 1 to 20.

Break loop Out

Step 6: Now, to break the loop 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

New If End Condition

Step 7: Write the criteria; if A is greater than 9 (Or any number but less than 20), Exit For as shown below. We suppose For loop should get broken when the 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

Exit For

Step 8: Now, compile the code to find the error. If no error is found, then run it. We will see that Integer A values from 1 to 20 have been filled till the code exited or broken at position 9.

break vba 3

Pros of VBA Break For Loop

  • Exit or break can be used not only for For loop but in many other loops, such as Do-While.
  • Most VBA programmers commonly use this loop condition out of all the available options.
  • For loop is relatively 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 essential 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 to preserve the written code.

Recommended Articles

This has been a guide to VBA Break For Loop. Here we discuss how to use Excel VBA Break For Loop, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. Excel VBA Macro
  4. VBA Transpose