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

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. — mwolfe02, Dec 30 '11 at 14:59
@mwolfe02 - no not sure, but saw in examples somewhere (cpearson?) — Richard H, Dec 30 '11 at 16:21
Continue is a VB keyword (in VS2017) but not for this, unfortunately. Probably saw an example in C# (or C++ or C). — Zeek2, Nov 11 '20 at 09:04

score 215 · Answer 1 · edited Sep 30 '15 at 23:08

215

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.

edited Sep 30 '15 at 23:08

Brian

6,910
8
44
82

answered Dec 30 '11 at 15:05

mwolfe02

23,787
9
91
161

20

Thanks, that's a good tip re the GoTo (VBA - beaming you back to 1964) – Richard H Dec 30 '11 at 16:23
4

GoTo is EVIL! Do not use GoTo or your code goes to hell! Seriously though, please do not use GoTo in your code. – George Feb 24 '15 at 18:08
8

@George: GoTo can be abused (which is why I qualified my statement; see [judicious](http://dictionary.reference.com/browse/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`). – mwolfe02 Feb 25 '15 at 00:55
1

@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. https://msdn.microsoft.com/en-us/library/69whc95c.aspx – George Feb 25 '15 at 16:30
5

@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. – mwolfe02 Feb 25 '15 at 16:39
@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. – George Feb 25 '15 at 16:58
1

@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. – George Mar 03 '15 at 17:38
6

@George I've seen nesting that doesn't break *code*, but wrecks one's *brain* ;) – Mathieu Guindon Mar 03 '15 at 17:40
@Mat's Mug I can definitely agree to that! – George Mar 03 '15 at 18:54
2

`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… – Joe Feb 21 '19 at 19:38

score 42 · Answer 2 · answered Feb 26 '18 at 08:21

42

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

answered Feb 26 '18 at 08:21

AHeyne

3,377
2
11
16

2

interesting ..better than using goto! – ozmike Apr 18 '18 at 00:58
1

This is awesome – Kubie Nov 19 '18 at 17:25
2

This should be the answer – Stian Ulriksen Nov 20 '18 at 21:04
Very elegant and nice – Alexis Sánchez Tello Apr 16 '19 at 12:08
13

Clever! I would hate to be the guy that comes across that with no comments though. lol – Caltor May 07 '19 at 14:55
By far the better answer. Especially with comments. – Mark Walsh Jan 17 '20 at 22:49
9

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). – pstraton Apr 16 '20 at 23:09
2

This is very clever (though I don't necessarily mean that as a compliment...[Don't Write Clever Code](https://nolongerset.com/dont-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. – mwolfe02 Jun 29 '22 at 04:17

score 33 · Accepted Answer · answered Dec 30 '11 at 15:02

33

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

answered Dec 30 '11 at 15:02

Brian

6,910
8
44
82

4

Indeed, is exactly what I have done :) But still it bugs me I have to wrap stuff in the Else piece. Thanks – Richard H Dec 30 '11 at 16:22
4

+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 `Else`more often than necessary. Otherwise use `(ReferenceDate>=Schedule(i, 1))`. (if the test is 50/50 then no need for optimisation) – brettdj Dec 31 '11 at 01:17
1

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! – JeopardyTempest Aug 26 '18 at 00:43

score 15 · Answer 4 · answered Dec 30 '11 at 15:08

15

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.

answered Dec 30 '11 at 15:08

Jon Egerton

40,401
11
97
129

score 4 · Answer 5 · answered May 05 '16 at 04:48

4

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

answered May 05 '16 at 04:48

Singaravelan

809
3
19
32

Not sure why this had been down-voted and the next answer has over 100 up votes, and they are the same answer! – rryanp Oct 12 '16 at 14:02
5

Probably because this answer was written 5 years after that answer, and is the exact same concept. Why should this receive upvotes? – Tyler StandishMan Apr 13 '17 at 19:25

Rahul Mehta · Answer 6 · 2023-05-23T13:55:18.607

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 , MehtaRahulC@yahoo.com , +91-98251-27780

score -2 · Answer 7 · answered Jan 30 '17 at 20:56

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

VBA - how to conditionally skip a for loop iteration

7 Answers7

Linked