VBA - how to conditionally skip a for loop iteration

Question

I have a for loop over an array. What I want to do is test for a certain condition in the loop and skip to the next iteration if true:

For i = LBound(Schedule, 1) To UBound(Schedule, 1)
    If (Schedule(i, 1) < ReferenceDate) Then
        PrevCouponIndex = i
        Continue   '*** THIS LINE DOESN'T COMPILE, nor does "Next"
    End If
    DF = Application.Run("SomeFunction"....)
    PV = PV + (DF * Coupon / CouponFrequency)
Next

I Know I can do:

 If (Schedule(i, 1) < ReferenceDate) Then Continue For

but I want to be able to record the last value of i in the PrevCouponIndex variable.

Any ideas?

Thanks

Pyrargyrite

You said: "I Know I can do: If (Schedule(i, 1) < ReferenceDate) Then Continue For" Are you sure about that? Continue is not a VBA keyword. – Celeriac

@Celeriac - no not sure, but saw in examples somewhere (cpearson?) – Pyrargyrite

may have been a VB.NET example – Denominationalism

Continue is a VB keyword (in VS2017) but not for this, unfortunately. Probably saw an example in C# (or C++ or C). – Inclusive

Answer 1

Couldn't you just do something simple like this?

For i = LBound(Schedule, 1) To UBound(Schedule, 1)
  If (Schedule(i, 1) < ReferenceDate) Then
     PrevCouponIndex = i
  Else
     DF = Application.Run("SomeFunction"....)
     PV = PV + (DF * Coupon / CouponFrequency)
  End If
Next

Tradesman

Indeed, is exactly what I have done :) But still it bugs me I have to wrap stuff in the Else piece. Thanks – Pyrargyrite

+1 @RichardH well you have to use an IF for the test so this isn't that expensive codewise. You should though ensure that the most common outcome is that Schedule(i, 1) is less than ReferenceDate to avoid executing the Elsemore often than necessary. Otherwise use (ReferenceDate>=Schedule(i, 1)). (if the test is 50/50 then no need for optimisation) – Boni

Just might get a bit messy with numerous nested ifs... if for example you need to check quite a few Application.Match results within each iteration for not finding a matching before using the results. But so be it, there are worse things in life! – Senter

Answer 2

VBA does not have a Continue or any other equivalent keyword to immediately jump to the next loop iteration. I would suggest a judicious use of Goto as a workaround, especially if this is just a contrived example and your real code is more complicated:

For i = LBound(Schedule, 1) To UBound(Schedule, 1)
    If (Schedule(i, 1) < ReferenceDate) Then
        PrevCouponIndex = i
        Goto NextIteration
    End If
    DF = Application.Run("SomeFunction"....)
    PV = PV + (DF * Coupon / CouponFrequency)
    '....'
    'a whole bunch of other code you are not showing us'
    '....'
    NextIteration:
Next

If that is really all of your code, though, @Brian is absolutely correct. Just put an Else clause in your If statement and be done with it.

Celeriac

Thanks, that's a good tip re the GoTo (VBA - beaming you back to 1964) – Pyrargyrite

GoTo is EVIL! Do not use GoTo or your code goes to hell! Seriously though, please do not use GoTo in your code. – Aubergine

@George: GoTo can be abused (which is why I qualified my statement; see judicious), but it is not inherently evil. Seriously though, it is impossible to write robust VBA without the Goto statement simply because you need it for error handling (i.e., On Error Goto). – Celeriac

@mwolfe02: On Error Goto is OK (a limitation of the language that's too old to fix it by brining it inline with try/catch), but anywhere else Goto should be avoided at all costs. Use other control flow mechanisms. This is recommended by most programmers and Microsoft too. In the past 15-20 years of my VB6/VBA experience, I've never used Goto once other than in On Error case. msdn.microsoft.com/en-us/library/69whc95c.aspx – Aubergine

@George: What I'm recommending here is a workaround for another limitation of the language (no Continue statement). One can argue that the use of Continue in other languages should be avoided and therefore should be avoided here as well. In some ways, the link you posted makes my point. The link is to the GoTo statement in VB.Net. VB.Net has both structured error handling and Continue For/Continue Do statements. There is truly no need for GoTo in VB.Net; I suspect it was left in place largely to support easier conversion of existing VBA/VB6 code. – Celeriac

@mwolfe02: There is no need for Goto in VB6 either. Brian had a perfect replacement for Goto with an Ifcontrol flow mechanism. Goto would over-complicate and confuse things. If you disagree, please post a piece of code where Goto works better than alternative control flow mechanism. – Aubergine

@Mat's Mug I do not believe that tradeoff is beneficial. I'd rather have nesting (which isn't a big deal in most cases) than have a messy and unpredictable control flow. Nesting is not gonna break your code, GoTo may. What I am saying is, you might have an OK solution in a very small and isolated scenario. The risk, however, is great if you decide to expand the code in the future. Its just not worth it. – Aubergine

@Aubergine I've seen nesting that doesn't break code, but wrecks one's brain ;) – Fractional

@Mat's Mug I can definitely agree to that! – Aubergine

GoTo seems to be the most logical way to overcome VBA’s lack of the continue statement. So I typed it! It works well! Just this once won’t hurt…Everyone else is doing it; so it can’t be that bad…I can quit using GoTo anytime I like… – Tepic

Answer 3

You can use a kind of continue by using a nested Do ... Loop While False:

'This sample will output 1 and 3 only

Dim i As Integer

For i = 1 To 3: Do

    If i = 2 Then Exit Do 'Exit Do is the Continue

    Debug.Print i

Loop While False: Next i

Ku

interesting ..better than using goto! – Geraud

This is awesome – Hagerman

This should be the answer – Ruebenrueda

Very elegant and nice – Lowson

Clever! I would hate to be the guy that comes across that with no comments though. lol – Alpheus

By far the better answer. Especially with comments. – Faythe

I agree that this solution has a certain cleverness, but disagree that it's elegant. It actually obscures the point of what's being accomplished, makes the coding much less self-evident, requiring commentary to explain it (as noted by Caltor). – Hyozo

This is very clever (though I don't necessarily mean that as a compliment...Don't Write Clever Code). This is the sort of nonsense you get when you treat good general advice ("Don't use GoTo in your code") as an inviolable rule never to be broken. – Celeriac

Answer 4

Continue For isn't valid in VBA or VB6.

From this MSDN page it looks to have been introduced into VB.Net in VS 2005./Net 2.

As the others have said there's not really an option other than to use Goto or an Else.

Gymnasiarch

Answer 5

Hi I am also facing this issue and I solve this using below example code

For j = 1 To MyTemplte.Sheets.Count

       If MyTemplte.Sheets(j).Visible = 0 Then
           GoTo DoNothing        
       End If 


'process for this for loop
DoNothing:

Next j 

Destructive

Not sure why this had been down-voted and the next answer has over 100 up votes, and they are the same answer! – Rome

Probably because this answer was written 5 years after that answer, and is the exact same concept. Why should this receive upvotes? – Cesium

Answer 6

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.

• Rahul Chimanbhai Mehta , [email protected] , [phone]

Aho

Answer 7

Maybe try putting it all in the end if and use a else to skip the code this will make it so that you are able not use the GoTo.

                        If 6 - ((Int_height(Int_Column - 1) - 1) + Int_direction(e, 1)) = 7 Or (Int_Column - 1) + Int_direction(e, 0) = -1 Or (Int_Column - 1) + Int_direction(e, 0) = 7 Then
                Else
                    If Grid((Int_Column - 1) + Int_direction(e, 0), 6 - ((Int_height(Int_Column - 1) - 1) + Int_direction(e, 1))) = "_" Then
                        Console.ReadLine()
                    End If
                End If

Pecoraro