DEV Community

Seungjoo Kwag
Seungjoo Kwag

Posted on

One Thing For Each Next Lacks

One of the interesting things about the For~Next Loop in VBA is the Step.

For counter [ As datatype ] = start To end [ Step step ]
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]
Enter fullscreen mode Exit fullscreen mode

Step defaults to 1, but Step 2 will cause the loop to run while incrementing the counter variable by 2, and Step -1 will cause the loop to run in reverse, decrementing by 1.

For index As Integer = 1 To 5
    Debug.Write(index.ToString & " ")
' Output: 1 2 3 4 5

For number As Double = 2 To 0 Step -0.25
    Debug.Write(number.ToString & " ")
' Output: 2 1.75 1.5 1.25 1 0.75 0.5 0.25 0
Enter fullscreen mode Exit fullscreen mode

However, For Each~Next, which iterates over the objects in a collection, does not have a Step. For example, you can't iterate over the range of cells A1:A100, skipping even or odd rows. However, you can create such a loop with the Do Loop and Offset methods instead of For Each. The following loop iterates between A6 and A22, taking only the addresses of the even rows.

  Set rngBegin = Sheet1.Range("A6")
  Set rngEnd = Sheet1.Range("A22")
  Set rng = rngBegin

  Debug.Print rngBegin.Address
  Debug.Print rngEnd.Address
  step = 2
    Debug.Print rng.Address
    Set rng = rng.Offset(step, 0)
    If rng.Address = rngEnd.Address Then Exit Do
Enter fullscreen mode Exit fullscreen mode

This is off topic, but I should have written "If rng Is rngEnd Then Exit Do" instead of "If rng.Address = rngEnd.Address Then Exit Do". There is no runtime error when executed, but it does not exit at rngEnd, i.e. A22, as expected.
The cell addresses of rng and rngEnd in the last iteration are both 'A22', but they are not actually the same object variable. When declaring the object variables, we did the following

  Dim rng As Range
  Dim rngEnd As Range
Enter fullscreen mode Exit fullscreen mode

These two variables are on the stack, but they will have different memory addresses. Therefore, although they may have the same cell address, 'A22', they will have different addresses, so they cannot be the same object.

Top comments (0)