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

Tags:

Vb Example