For...Next loop

# VBA controlling code execution (3)

## VBA's For...Next statement

The statement structure is contained within the For...Next key words. Highlighted lines 8 and 13 of the syntax window.

SYNTAX: Select Case statement
''  ========================================
''  For...Next Statement
''
''  Repeats a group of statements a specified number of times.
''
''  Syntax
''
''  For counter = start To end [Step step]
''  [statements]
''  [Exit For]
''  [statements]
''
''  Next [counter]
''  ========================================


Optional parameters or arguments are shown by the square brackets [].

### Using VBA's For...Next statement

Using a sub procedure allows execution of the procedure with the F5 key. Output is sent to the Immediate Window.

Code 1: Sub procedure ForNextDiscFactor
Sub ForNextDiscFactor()
Dim i As Integer        ' Counter
Dim DiscRate As Double
Dim DiscFactor As Double

DiscRate = 0.1
Debug.Print "=========================="
Debug.Print "Print time: " & Time

For i = 0 To 20
DiscFactor = (1 + DiscRate) ^ -i
Debug.Print "Period - " & i & " discount factor = " & Format(DiscFactor, "#0.00000")
Next

End Sub



Code 1 by line number:

1. Line 1: Procedure declaration: a Sub procedure named ForNextDiscountFactor. No arguments - the parentheses () are empty
2. Line 2: Declare a variable named i as type Integer
3. Line 3: Declare a variable named DiscRate as type Double
4. Line 4: Declare a variable named DiscFactor as type Double
5. Line 6: Assign the value 0.1 to the variable named DiscRate. This must be in decimal form
6. Line 7: Send a string of = characters to the Immediate Window
7. Line 8: Send the text string Print time and computer time to the Immediate Window
8. Line 10: Start of the loop.
9. Line 11: Assigns the value of (1 + 0.1)^-0 to the DiscFactor variable
10. Line 12: Send a string of characters to the Immediate Window. The final output is shown in figure 1
11. Line 13: End of the loop. This can also be written as Next i. This matches the For i in line 11, and can make nested loops easier to read