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:
- Line 1: Procedure declaration: a
Subprocedure named ForNextDiscountFactor. No arguments - the parentheses () are empty
- Line 2: Declare a variable named i as type
- Line 3: Declare a variable named DiscRate as type
- Line 4: Declare a variable named DiscFactor as type
- Line 6: Assign the value 0.1 to the variable named DiscRate. This must be in decimal form
- Line 7: Send a string of
=characters to the Immediate Window
- Line 8: Send the text string
Print timeand computer time to the Immediate Window
- Line 10: Start of the loop.
- Line 11: Assigns the value of
(1 + 0.1)^-0to the DiscFactor variable
- Line 12: Send a string of characters to the Immediate Window. The final output is shown in figure 1
- 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