ForNext Loops and VBA

1
For/Next Loops and VBA
What is a For-Next loop? What is a counter?
What does Next do?
2
For-Next Loop

• Allows a loop to be executed a fixed number of
  times.
• This loop places a random number in a cell then
  moves to the next cell, where it does the same
  thing
• For intCounter 1 To 10
• Cells(intCounter, 1).Value Rnd
• Next intCounter

3
Flowchart For/Next Loop

• Initialize the counter
• Increment the counter
• Test the counter

START
Initialize index variable
Counter within range?
Statements in loop
TRUE
FALSE
Increment loop index
END
4
The counter is the loop index

• Each trip through the Body of the Loop is called
  an Iteration of the Loop.
• The counter variable is the loop index, keeping
  track of iterations.
• Next adds one (1) to the counter variable by
  default.
• If the counter does not exceed the Final Value,
  statements in the loop body are executed.

5
For/Next on Spreadsheet example

• Private Sub cmdRandom_Click()
• Dim intCounter as Integer
• For intCounter 1 To 10
• Cells(intCounter , 1).Value Rnd
• Next intCounter
• End Sub
• (understood Step 1)

6
Process Explained

• The counter starts at 1, so the iteration of the
  loop looks like this
• Cells(1,1).Value Rnd
• Next adds 1 to the counter. So the next time
  through,
• Cells(2,1).Value Rnd
• and so on, stopping when intCounter 11

7
For-Next Loop Example

• 'compute the total of numbers
• Private Sub cmdComputeSum_Click()
• Dim intNum As Integer
• Dim intTotal As Integer
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum
• lblTotal.Caption "The total is " intTotal
• End Sub

8
Computing the Sum of Numbers

• The Loop
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum
• is equivalent to
• intTotal intTotal 1
• intTotal intTotal 2
• intTotal intTotal 3
• intTotal intTotal 4

9
What the values are

• First iteration
• intTotal 0
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum
• Second iteration
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum

1
1
0
intTotal 1 intNum 2 Iterations 1
2
2
1
3
intTotal 1 3 intNum 2 3 Iterations 1 2
3
10
What the values are

• Third iteration
• intTotal 0
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum
• Fourth iteration
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum

3
6
3
intTotal 6 intNum 4 Iterations 3
4
4
10
6
5
intTotal 10 intNum 5 Iterations 4
11
What the values are

• Dim intNum As Integer
• Dim intTotal As Integer
• intTotal 0
• For intNum 1 To 4
• intTotal intTotal intNum
• Next intNum
• 1 0 1 After the first interation,
  intTotal is 1, then
• 3 1 2 3, then 6, and finally
  10.
• 6 3 3
• 10 6 4 intNum is 5 when the code
  stops
• 
  The loop had 4 iterations
  when intNum was 1, 2, 3, and 4

12
One Step, Two Step, Minus Step, etc.
13
For/Next with Step Two
Why do we make the counter 1 at the start? Is
there a cell A0?

• Dim intCounter as Integer
• For intCounter 1 To 10 Step 2
• Range("A" intCounter).Value Rnd
• Next intCounter
• End Sub
• Same as basic For-Next Loop, except that at the
  end of
• each iteration the counter is incremented by the
• value of the Step, rather than by 1.
• The loop executes when intCounter is 1, 3, 5, 7,
  and 9
• The final value of intCounter is 11

14
For/Next on Spreadsheet example

• Private Sub cmdRandom_Click()
• Dim intCounter as Integer
• For intCounter 10 To 1
• Range("A" intCounter).Value Rnd
• Next intCounter
• End Sub
• intCounter (10) is compared to 1, and since 10 is
  greater than one, it never executes.

15
For/Next and Step - 2

• Private Sub cmdRandom_Click()
• Dim intCounter as Integer
• For intCounter 10 To 1 Step -2
• Range("A" intCounter).Value Rnd
• Next intCounter
• End Sub

16
Exit For and Error Trapping

• You may need to break out of a For/Next loop
• Use an If statement and Exit For
• Of course, this statement has to be
• INSIDE the loop.
• You cannot exit a loop if you are not in it (-

17
Watching the loop execute
18
Errors with For-Next Loops

• 'Poor programming
• For intCounter 1 To 10 Step 1
• (code)
• intCounter 1
• Next intCounter
• Leave your index/counter alone!!

19
Why we use a Nested Loop

• Private Sub cmdClickMe_Click()
• Dim intRow As Integer
• Dim intCol As Integer
• intCol 1
• For intRow 1 To 10
• Cells(intRow, intCol).Value intRow intCol
  
• Next intRow
• End Sub

20
Long Way to Fill in a Multiplication Table

• intCol 1
• For intRow 1 To 10
• Cells(intRow, intCol).Value intRow intCol
  
• Next intRow
• intCol 2
• For intRow 1 To 10
• Cells(intRow, intCol).Value intRow intCol
  
• Next intRow
• intCol 10
• For intRow 1 To 10
• Cells(intRow, intCol).Value intRow intCol
  
• Next intRow

21
Nested Loop, Cont.

• Private Sub cmdClickMe_Click()
• Dim intRow As Integer
• Dim intCol As Integer
• For intCol 1 To 10
• For intRow 1 To 10
• Cells(intRow, intCol).Value intRow
  intCol
• Next intRow
• Next intCol

22
Remember this function from an Excel lecture?

• Public Function Reverse(strWord As String) As
  String'This function reverses the letters in a
  word Dim intLastLetter As Integer Dim
  intCounter As Integer Reverse ""
  intLastLetter Len(strWord) For intCounter
  intLastLetter To 1 Step -1 Reverse
  Reverse Mid(strWord, intCounter, 1) Next
  intCounter End Function

23
Reverse Functionhas one parameter, returns a
value

• Public Function Reverse(strWord As String) As
  String
• your code goes here
• End Function

24
How does this function work?

• Public Function Reverse(strWord As String) As
  String Dim intLastLetter As Integer Dim
  intCounter As Integer Reverse ""
• intLastLetter Len(strWord)
• For intCounter intLastLetter To 1 Step
  -1 Reverse Reverse Mid(strWord,
  intCounter, 1) Next intCounter
• End Function

25
Mid(strWord, intCounter, 1) means

• Mid("tomato",3,1) returns "m"
• Mid("tomato",3,2) returns "ma"
• Mid function arguments are
• Mid(String, start position, number of
  letters)

26
Name of function will hold output of function

• For the function Reverse, in the code, Reverse
  holds what the function will return
• For intCounter intLastLetter To 1 Step -1
  Reverse Reverse Mid(strWord, intCounter,
  1)Next intCounter
• For example, when the loop is finished, Reverse
  holds the reversed word, and that's what goes
  onto the spreadsheet

27
Debugging
Debug toolbar
Step through the procedure
Locals window
Immediate window
28
Debugging, cont.

• What do these terms mean?
• Breakpoint
• Step into
• Run-time error

29
Watching the loop progress
30
To Summarize

• What is a For-Next loop?
• What is a counter?
• What does Next do?
• Your moment of Zen
• http//youtube.com/watch?v4Ty2CUvWJyE