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
xlf-for-next-im
Fig 1: ForNextDiscFactor - Immediate Window



  • This example was developed in Excel 2013 :: VBA 7.1
  • Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]