As we’ve mentioned already, loops are used to repeat a set of statements as many times as necessary until a given condition is met. They help to refract your code, reducing the number of specifically coded actions that must be performed to improve the speed and efficiency of your applications.
Flow Diagram
Things to Consider Before Using a VBA for Loop in Excel
A VBA For Loop is a flexible and broad-ranging programming method that allows your code to perform an action sequentially and only stop when (and when) certain conditions are met. There are a few things to consider before creating this type of macro in Excel. These include:
1. If you want to have you’re For loop end before the final value is reached, you will need to add an Exit For a statement to your code, which will exit the loop and continue on to the next line of code outside the loop if your code continues. As an example, you could stop the loop when you push a button.
2. A VBA For Loop can be used to cycle through several numerical values in sequence simply with a For Next Loop, while a more complex VBA For Each Loop can cycle through more complex objects (such as workbooks in your spreadsheet) in sequence.
3. VBA For Loops can be embedded within other logical statements, such as an If or Do While statement, so you can build more complex decision-making into your code.
See More: VBA Loops
Syntax
The For loop executes a block of code a specific number of times.
For counter = start To end [Step count] [statement 1] [statement 2] .... [statement n] [Exit For] [statement 11] [statement 22] .... [statement n] Next
Example
Sub VBAForLoop() Dim i As Integer For i = 1 To 10 MsgBox i Next i End Sub
Explanation
- The counter variable is i, which is need to be declared.
- The Start_value of the counter is 1, and its End_value of the counter is 10, the two values stated are the numeric values.
- The keyword “Step” is optional, and it is a numeric value by which the counter is incremented each time when the loop is run.
- The next statement is increment the counter by the step value and returns to the for the statement, which repeats the code if the counter value does not exceed the “End_value” of 10.
- Assuming the counter is equal to the End_value, the loop will be continued, or it will be stopped only when the End_value exceeds.
Nested For Next Loops
A nested loop is a loop that is encased in another and is commonly used for working in two dimensions, such as restarted printing stars in rows and columns, as shown below.
You can use the nested for loop to get the more complex automation done in excel. There would be a “For Next” loop within a “For Next” loop.
For example: Suppose you want to read all the worksheets name in all open workbooks in your system.
Sub VBAForLoop_WorksheetName() Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks(i).Worksheets.Count MsgBox Worksheets(j).Name Next j Next i End Sub
Exit For Statement
When we need to exit the For loop based on a set of criteria, we use an Exit For statement. When Exit For is executed, the control jumps to the next statement immediately after the For loop.
Flow Diagram
Example
In the following example, Exit For is used. If the Counter’s value reaches 4, the For Loop is exited, and the control moves to the next statement immediately after the For Loop.
Sub VBAForLoop_Exit() Dim i As Integer For i = 1 To 10 If i > 5 Then Exit For End If Next i End Sub
