Advance to Next Item from Within a For-Next Loop

Question

I have a For-Next loop. Within the loop I test several different criteria and if any test fails, then I am ready at that point to skip the remainder of the code in the loop and advance to the "next" item. The way I currently handle this is with a GoTo statement that takes me to the line right before "Next". I'd prefer not to use a GoTo statement, is there another way to advance to the "next" item from within a For-Next loop? TIA!

For x = 1 to 10
    Test 1
    If Test 1 fails then
        GoTo Line1
    End if

    Test 2
    If Test 2 fails then
        GoTo Line1
    End if
    .
    .
    .
    If all tests pass then
        add item to array
    End if
Line1:    
Next

Bathsheba · Answer 1 · 2014-04-14T14:46:31.837

Unfortunately there is no continue-like statement in a for loop in vba. (The related control structure Exit For does exist but that's of no help here).

And it's good that you have reservations on using a GoTo: they do make code hard to follow.

Your best bet is to put the code in the loop in a separate function and use Exit Function within that function at appropriate points. You can even then relay error codes back to the caller so helping the code to scale.

score 1 · Accepted Answer · answered Apr 14 '14 at 16:04

1

Here's a workaround for the lack of a continue keyword:

For x = 1 to 10
    Do
        Test 1
        If Test 1 fails then
            Exit Do
        End if

        Test 2
        If Test 2 fails then
            Exit Do
        End if
        .
        .
        .
        If all tests pass then
            add item to array
        End if
    Loop While False
Next

answered Apr 14 '14 at 16:04

z̫͋

1,531
10
15

Thanks to everyone who responded, there is a lot of useful information in all of the answers. Given how my code is structured, this answer best fit the bill. – ron Apr 14 '14 at 18:54

score 0 · Answer 3 · answered Apr 14 '14 at 14:49

0

You can use if else ladder :

For x = 1 to 10
if test 1 
else if test 2
else if test 3
. 
.
.
.
else
  add item to array
end if
Next

Besides GoTo there is not any direct way to jump in between your code, Hope this might help.

answered Apr 14 '14 at 14:49

Chirag Bansal

84
5

That's fine if the tests are mutually exclusive. – Bathsheba Apr 14 '14 at 14:53
1

In VBA you want to use `ElseIf` to construct logical ladders, as `Else If` would have a slightly different meaning – RBarryYoung Apr 14 '14 at 14:57

score 0 · Answer 4 · answered Apr 14 '14 at 16:34

If you don't have too many tests, you could you use the Not condition and build a nested If statement. This should have almost the same effect as what you're asking for, since any failed test would end that If statement and move the code to the next X in your loop without executing the following tests.

Here's an example of what I mean -- a two test loop that builds an array containing the numbers 5 through 10:

Sub TestConditionalPass()

    Dim intX As Integer
    Dim intArray() As Integer
        ReDim intArray(1)

    For intX = 1 To 10
        If Not intX -2 < 1 Then
            If Not intX * 2 < 10 Then
                ReDim Preserve intArray(UBound(intArray) + 1)
                    intArray(UBound(intArray)) = intX
            End If
        End If
    Next intX

End Sub

score 0 · Answer 5 · edited May 23 '17 at 12:21

Since you are preforming an action only if all tests succeed, then do the if-statement with ands. VBA doesn't short circuit the tests, (i.e. it will evaluate each test case, even if the first one returns false.) I would suggest encapsulating each test in a function that returns a Boolean to keep your code tidy.

If Test1()  and _
   Test2()  and _
   testn()  THEN
    add item to array
End if

Another way is to make use of a Boolean variable in your code, like this

Dim Success as Boolean
Success=True
Test 1
If Test 1 fails then
    Success=false
End if

Test 2
If Test 2 fails then
    Success=false
End if
.
.
.
If Success then
    add item to array
End if

If the tests are expensive, you can add an inner if statement checking to see if we need to evaluate the next test like this

 If Success Then
    If Test n fails then
       Success=false
    End If
End if

Advance to Next Item from Within a For-Next Loop

5 Answers5