Looping of your Code

The term looping refers to repeating a block of VBA statements numerous times. VBA provides various looping command for repeating code to make correct decision making. We will go through them in following topics:

For -Next Loop

The simplest type of loop is a For-Next loop. Here’s the syntax for this structure:

Structure

  For counter = start To end [Step stepval]
    [statements]
    [Exit For]
    [statements]
  Next [counter]

The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how this works, keep reading.

For-Next example

The following example shows a For-Next loop that doesn’t use the optional Step value or the optional Exit For statement. This routine loops 10 times and uses the VBA MsgBox function to show a number from 1 to 10:

Example

Sub ShowNumbers1()
  Dim i As Integer
  For i = 1 to 10
    MsgBox i
  Next i
End Sub

In this example, I (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because I didn’t specify a Step value the MsgBox method uses the value of I as an argument. The first time through the loop, i is 1 and the procedure shows a number. The second time through (i = 2), the procedure shows a number, and so on.

---

For-Next example with a step

You can use a Step value to skip some values in a For-Next loop. Here’s the same procedure as in the preceding section, rewritten to insert random numbers

Example

Sub ShowNumbers2()
  Dim i As Integer Step 2
  For i = 1 to 10
    MsgBox i
  Next i
End Sub

The count starts out as 1 and then takes on a value of 3, 5, 7, and 9. The final Count value is 9. The Step value determines how the counter is incremented. Notice that the upper loop value (9) is not used because the highest value of Count after 9 would be 11, and 11 is larger than 10.

---

For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately. Here’s the same procedure as in the preceding section, rewritten to insert random numbers.

Example

Sub ShowNumbers3()
  Dim i As Integer Step 2
  For i = 1 to 10
    If i = 5 Then
      MsgBox “This is a mid value”
      Exit For
    End If
    MsgBox i
  Next i
End Sub

This routine performs the as earlier but when the variable I reached to 5, it shows a message, stating that this is a mid value and exit from the loop.