for next loop vba code example
Example 1: vba for next loop
For i = 1 to 100
'Code here is executed 100 times.
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Notes: The For loop is terminated with the 'Next' keyword. There is no need
' to include the counter variable as part of Next, for example: "Next i".
'
' However, there is nothing wrong with doing so and some programmers feel
' including the counter variable improves readability. I don't.
'
' The speed of the loop is not affected by this decision. However,
' the speed of the loop IS affected by the choice of the type of variable
' used as the counter. A Long (&) is the quickest variable type
' to choose for the counter variable.
'
' In the above example, the range to iterate over is hard coded with
' constant values, 1 to 100. Variables or even expressions can be
' used intead of constant values. This adds significant dynamicism
' to the For loop.
'
' BUT, the variables or expressions used in the For loop range are
' evaluated only ONCE (before the loop starts). This makes
' the execution of the For loop efficient.
'
' However, this also means that changing the value of these variables
' DURING the For loop execution does NOT alter the original range
' that the For loop iterates over:
a = 1
b = 100
For i = a to b
b = 5
'This For loop still iterates 100 times
Next
' A For loop can be exited EARLY by use of the 'Exit For' statement:
For i = a to b
If str = "abc" Then Exit For
Next
' When 'Exit For' executes, execution resumes on the very next
' line of code following the 'Next' statement. If the For loop
' was nested inside of another loop, the nearest outer loop now
' continues execution. If the For loop is within a 'Function' or
' a 'Sub', there are two other options to exit the For loop early.
' Both 'Exit Function' and 'Exit Sub' will immediately terminate
' the For loop... but they will also immediately end the execution
' of, and immediately exit from, the current procedure.
' In the above sample code, if the variable 'str' ever equals
' "abc", then the For loop will instantly terminate, even if
' the range in not fully iterated.
'
' By default the For loop iterates in steps of positive ONE. This
' can be adjusted with the optional 'Step' keyword:
For i = 1000 to 0 Step -10
'This loop iterates from 1000 to 0 (reverse iteration) by 10s.
'So i will equal 1000, 990, 980, 970... 0.
Next
' Step values can be positive or negative. VBA's compiler will not prevent
' you from using a ZERO value for the Step value, but don't do this unless
' you really want to. It creates an INFINITE loop with the value of the
' counter variable ALWAYS equal to the start value.
'
' Step values can be variables or expressions that evaluate to any numeric
' type. But be careful about floating point values. Floating point
' Step values may work... even for a long time... and then fail
' unpredictably (which can make debugging extremely difficult). Integer
' Step values are safe, always. And again, the Long (&) integer will
' allow the For loop to iterate quickest.
'
' If the start value (constant, variable, expression) of the iteration
' range is greater than the end value (constant, variable, expression)
' then the loop is SKIPPED entirely. This behavior is accurate when the
' Step value is POSITIVE. The reverse is true when the Step value
' is NEGATIVE.
'
'
'Example 2: vba for loop
For i = 1 to 100
'Code here is executed 100 times.
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Notes: The For loop is terminated with the 'Next' keyword. There is no need
' to include the counter variable as part of Next, for example: "Next i".
'
' However, there is nothing wrong with doing so and some programmers feel
' including the counter variable improves readability. I don't.
'
' The speed of the loop is not affected by this decision. However,
' the speed of the loop IS affected by the choice of the type of variable
' used as the counter. A Long (&) is the quickest variable type
' to choose for the counter variable.
'
' In the above example, the range to iterate over is hard coded with
' constant values, 1 to 100. Variables or even expressions can be
' used intead of constant values. This adds significant dynamicism
' to the For loop.
'
' BUT, the variables or expressions used in the For loop range are
' evaluated only ONCE (before the loop starts). This makes
' the execution of the For loop efficient.
'
' However, this also means that changing the value of these variables
' DURING the For loop execution does NOT alter the original range
' that the For loop iterates over:
a = 1
b = 100
For i = a to b
b = 5
'This For loop still iterates 100 times
Next
' A For loop can be exited EARLY by use of the 'Exit For' statement:
For i = a to b
If str = "abc" Then Exit For
Next
' In the above sample code, if the variable 'str' ever equals
' "abc", then the For loop will instantly terminate, even if
' the range in not fully iterated.
'
' When 'Exit For' executes, execution resumes on the very next
' line of code following the 'Next' statement. If the For loop
' was nested inside of another loop, the nearest outer loop now
' continues execution. If the For loop is within a 'Function' or
' a 'Sub', there are two other options to exit the For loop early.
' Both 'Exit Function' and 'Exit Sub' will immediately terminate
' the For loop... but they will also immediately end the execution
' of, and immediately exit from, the current procedure.
' By default the For loop iterates in steps of positive ONE. This
' can be adjusted with the optional 'Step' keyword:
For i = 1000 to 0 Step -10
'This loop iterates from 1000 to 0 (reverse iteration) by 10s.
'So i will equal 1000, 990, 980, 970... 0.
Next
' Step values can be positive or negative. VBA's compiler will not prevent
' you from using a ZERO value for the Step value, but don't do this unless
' you really want to. It creates an INFINITE loop with the value of the
' counter variable ALWAYS equal to the start value.
'
' Step values can be variables or expressions that evaluate to any numeric
' type. But be careful about floating point values. Floating point
' Step values may work... even for a long time... and then fail
' unpredictably (which can make debugging extremely difficult). Integer
' Step values are safe, always. And again, the Long (&) integer will
' allow the For loop to iterate quickest.
'
' If the start value (constant, variable, expression) of the iteration
' range is greater than the end value (constant, variable, expression)
' then the loop is SKIPPED entirely. This behavior occurs when the
' Step value is POSITIVE. The reverse is true when the Step value
' is NEGATIVE.
'
'
'Example 3: vba for next
Dim Words, Chars, MyString
For Words = 10 To 1 Step -1 ' Set up 10 repetitions.
For Chars = 0 To 9 ' Set up 10 repetitions.
MyString = MyString & Chars ' Append number to string.
Next Chars ' Increment counter
MyString = MyString & " " ' Append a space.
Next Words