vba loops code example
Example 1: 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 2: vba while loop
i = 0
While i < 100
'Code here is executed 100 times.
i = i + 1
Wend
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Notes: The While loop is terminated with the 'Wend' keyword.
'
' The While loop must include a Boolean expression directly after
' the 'While' keyword and the loop will execute for as long as the
' Boolean expression evaluates to True. This expression should be
' based on something that has the POTENTIAL TO CHANGE on each cycle
' through the loop. For example, 'i' in the above code.
'
' The While expression is evaluated at the beginning EACH cycle through
' the loop. The expression DOES NOT NEED to be NUMERIC; but it must
' evaluate to True or False. The While loop will be skipped entirely if
' the expression evaluates to False before the loop begins.
' The following code demonstrates how the above While loop
' functions (without actually using the 'While' and 'When' keywords):
i = 0
LoopStart:
If (i < 100) = False Then GoTo LoopEnd
'Code here is executed 100 times.
i = i + 1
GoTo LoopStart
LoopEnd:
' This is for demonstration purposes only. Although these two loops are
' equivalent, using the 'GoTo' statement should be avoided. The While loop
' accomplishes the exact same task, but cleanly, without the
' line labels and without the 'GoTo' statements.
'
'Note: VBA does not include an 'Exit While' statement to terminate the loop
' early. VBA does include the 'Exit Do' statement for the Do loop and
' also the 'Exit For' statement for the For loop. But it is possible
' to exit the While loop early with a 'GoTo' statement, but again, the
' 'GoTo' statement should be avoided. It's also possible to exit
' the While loop with the 'Exit Sub' and 'Exit Function' statements.
' But of course, these last two statements will not only exit the
' While loop, they will also immediately exit the current procedure.
'
'Note: From an efficiency standpoint, the For loop may be a better choice
' than the While loop because the For loop's range is calculated only
' once, before the For loop starts. By contrast, the While loop's
' expression is evaluated before each and every cycle of the loop.
'
'
'Example 3: vba for loop
' For VBA//VB/Visual Basic only
' ================================================================
' METHOD: FUNDAMENTAL
'EXAMPLE
Dim iTotalPrints As Integer
iTotalPrints = 5
For iCount = 1 To iTotalPrints
MsgBox Prompt:=iCount, Title:="Method: Fundamental"
Next
'SYNTAX
' For <your-variable-to-be-used> = <first-number> To <last-number>
' 'Code that uses <your-variable-to-be-used>
' Next
' ================================================================
' METHOD: WITHIN AN ARRAY
'EXAMPLE
Dim sTextList(4) As String 'Note: doesn't have to be of "String" data-type
sTextList(0) = "aaa"
sTextList(1) = "bbb"
sTextList(2) = "ccc"
sTextList(3) = "ddd"
sTextList(4) = "eee"
For Each sSeparateText In sTextList
MsgBox Prompt:=sSeparateText, Title:="Method: Within an Array"
Next
'SYNTAX
' For Each <your-variable-to-be-used> In <your-array-of-texts>
' 'Code that uses <your-variable-to-be-used>
' Next