what is the "continue" statement for VBA

[yeekhoon]

Hi

does anyone know what this the continue statement for VBA? A continue statement is used to end the current loop iteration and return control to the loop statement.

Thanks

Regards
Yee KHoon

 

[Sycdan]

Apologies for the thread necro, but I recently needed this functionality and came up with what I feel is a more flexible solution than using GOTO, or any of the other options mentioned here so far:

Sub ContinueTest()

Dim i As Integer

'Outputs 1 & 10
For i = 1 To 10: Do

If i > 1 And i < 10 Then
'Continue the for loop
Exit Do
End If

Debug.Print i

'Never let the DO execute more than once
Exit Do: Loop

'Perform any actions here that must be done at the end of
'every iteration, whether or not you "continued".

Next

End Sub

Essentially you are setting up a single-iteration DO within a FOR, and just exiting it if/when you determine you don't need to finish the rest of the code in the FOR block, then the code automatically jumps to the next iteration of the FOR.

 

[RoryA]

To be honest I can't see any benefit in that. Care to elaborate?

 

[Sycdan]

To be honest I can't see any benefit in that. Care to elaborate?

Sure, Rory.

If there are multiple possible reasons, at different points in your for loop, for which you might want to skip to the next iteration, I feel that nesting IFs can get quite messy and is less versatile.

'Totally fictional example: an outer and inner loop, each going from 1 to 10
'When outer is odd, we only want to output inner 1-5; when outer is even, only inner 6-10
'We also want to prevent any output when outer is 3 or inner is 6

Sub ExampleWithContinues()

    For outer = 1 To 10: Do
        
        If outer = 3 Then Exit Do
    
        For inner = 1 To 10: Do
        
            If inner = 6 Then Exit Do
            If outer Mod 2 = 1 And inner > 5 Then Exit Do
            If outer Mod 2 = 0 And inner <= 5 Then Exit Do
            
            Debug.Print "Outer: ", outer, "Inner:", inner
        
        Exit Do: Loop: Next

    Exit Do: Loop: Next

End Sub

Sub ExampleWithIfs()

    For outer = 1 To 10
    
        If outer <> 3 Then
        
            For inner = 1 To 10
            
                If inner <> 6 Then
            
                    If (outer Mod 2 = 1 And inner <= 5) Or _
                        (outer Mod 2 = 0 And inner > 5) Then
                    
                        Debug.Print "Outer: ", outer, "Inner:", inner
                    
                    End If
                
                End If 'inner <> 6
            
            Next
        
        End If 'outer is <> 3

    Next

End Sub

Speaking to the versatility, if it came to be that you needed to further restrict the above scenario so that there's no output when outer is 2 and inner is 8, adding/editing the nested ifs would be more of a chore than just putting this inside the inner loop:

If outer = 2 and inner = 8 Then Exit Do

 

[Rick Rothstein]

This thread is too long to read, so I don't know if this was covered earlier or not, but this is how you would implement the equivalent of Continue in VB/VBA...

  For X = Whatever To WhateverElse
    ....
    .... <<< code >>>
    ....
    If SomeCondition Then GoTo Continue
    ....
    ....  <<< more code >>>
    ....
Continue:
  Next

 

[Sycdan]

This thread is too long to read, so I don't know if this was covered earlier or not, but this is how you would implement the equivalent of Continue in VB/VBA...

  For X = Whatever To WhateverElse
    ....
    .... <<< code >>>
    ....
    If SomeCondition Then GoTo Continue
    ....
    ....  <<< more code >>>
    ....
Continue:
  Next

I agree, Rick, that is a perfectly viable way to do it, and I have certainly used Gotos to great effect. However, if, for whatever reason, you had to do this multiple times in one routine, this alternative option may end up a little cleaner than having several arbitrarily-named line labels:

  For X = Whatever To WhateverElse: Do
    ....
    .... <<< code >>>
    ....
    If SomeCondition Then Exit Do
    If SomeOtherCondition Then Exit Do
    ....
    ....  <<< more code >>>
    ....
  Exit Do: Loop: Next

 

[Rick Rothstein]

I agree, Rick, that is a perfectly viable way to do it, and I have certainly used Gotos to great effect. However, if, for whatever reason, you had to do this multiple times in one routine, this alternative option may end up a little cleaner than having several arbitrarily-named line labels:

  For X = Whatever To WhateverElse: Do
    ....
    .... <<< code >>>
    ....
    If SomeCondition Then Exit Do
    If SomeOtherCondition Then Exit Do
    ....
    ....  <<< more code >>>
    ....
  Exit Do: Loop: Next

First off, I did not plan on having "several arbitrarily-named line labels" in my code... just the one Continue label to jump to for all conditions needing to do the Continue operation. Second, I am not so sure I like your Do..Loop proposal. As you have structured it, the only way for the For loop to iterate is for some tested condition to be True so that an Exit Do statement gets executed. Not all loops that implement a Continue anticipate it being used... it is possible for a For..Next loop to iterate completely without ever executing a Continue statement depending on the conditions being examined within the loop... my code allows for that structure and I do not believe that your code does.

 

[Sycdan]

First off, I did not plan on having "several arbitrarily-named line labels" in my code... just the one Continue label to jump to for all conditions needing to do the Continue operation. Second, I am not so sure I like your Do..Loop proposal. As you have structured it, the only way for the For loop to iterate is for some tested condition to be True so that an Exit Do statement gets executed. Not all loops that implement a Continue anticipate it being used... it is possible for a For..Next loop to iterate completely without ever executing a Continue statement depending on the conditions being examined within the loop... my code allows for that structure and I do not believe that your code does.

Rick, I apologise, as you seem to have taken some measure of offense, which was absolutely not my intent. I was merely providing another method by which to achieve the thing in question, and stating that I think having multiple arbitrary line labels in one routine is a bit messy. I say arbitrary, because "Continue:" is really just a bookmark for the Next statement and may as well just be a line number, whereas something like "ErrorHandler:" would denote a block of code with a very specific purpose, that only gets executed under certain circumstances. I was not implying that you were planning anything in particular!

With regard to the salient point in your reply:
My loop does not expect to have a continue, it is just prepared for one. It will actually execute every iteration unless it is interrupted, just as a normal For loop would, due to the fact I put "Exit Do" right before "Loop". Granted, I put 3 statements on one line, as they all share a common purpose and are very short, but this layout makes things a bit clearer:

For X = Whatever To WhateverElse
  Do
    <<< code >>>
    If SomeCondition Then Exit Do 'Continue to the Next statement
    If SomeOtherCondition Then Exit Do 'Continue to the Next statement
    <<< more code >>>
    Exit Do 'Ensures the loop will never execute more than once
  Loop
Next

Again, apologies if I ruffled any feathers, I just wanted to provide another possible solution to a problem.

 

[JenniferMurphy]

This thread is too long to read, so I don't know if this was covered earlier or not, but this is how you would implement the equivalent of Continue in VB/VBA...

  For X = Whatever To WhateverElse
    ....
    .... <<< code >>>
    ....
    If SomeCondition Then GoTo Continue
    ....
    ....  <<< more code >>>
    ....
Continue:
  Next

Rick,

Thanks for this solution. I run into this all the time. I usually end up writing a huge If statement, which is ugly and error prone. Your solution is the best I have found as a work-around for this glaring hole in VBA. This should be in a FAQ somewhere.

 

[MehtaRahulC]

VB6 , VBA doesnt have continue. Following HACK is what I have been using since VB3 days ie year 1992. I have been using "for dummy = 1 to 1 : exit for : next dummy" loop. The "exit for" inside dummy inner loop implements continue for actual outer loop

Sub Print_all_integers_except_multiples_of_3()
Dim i As Integer, dummy As Integer
For i = 1 To 100
For dummy = 1 To 1 ' dummy only to implement Continue For i
If i Mod 3 = 0 Then Exit For ' implements Continue For i
Debug.Print i
Next dummy
Next i
End Sub

So inner dummy loop has only one iteration. And Exit For statement inside the inner dummy loop will implement "Continue For". Also, I dont indent For Dummy loop. So it becomes easily visible that this dummy loop is "dummy". And sometimes, I use variable name "Continue1" instead of Dummy to make it more obvious.