Use IF statement to push Next statement if true

Question

I am trying to look at ways to if a statement is true, go to next for statement instead of carrying on code.

The issue is kicking off on If comment_Or_coupon Then goto Next rowHeader, because I can't go to the next rowHeader.

Sub UniqueFetchDBTabletest()

Dim tempList As Variant
Dim rowHeader As Variant
Dim comment_Or_coupon As Boolean

tempList = [RangeofIDCells]

For Each rowHeader In tempList:
    comment_Or_coupon = False
    If InStr(1, LCase(rowHeader), "comment", vbTextCompare) = 0 Then comment_Or_coupon = True
    If InStr(1, LCase(rowHeader), "coupon", vbTextCompare) = 0 Then comment_Or_coupon = True
    If comment_Or_coupon Then goto Next rowHeader

    ' else I want to continue doing code

Next rowHeader

End Sub

score 4 · Accepted Answer · answered Nov 20 '19 at 10:11

4

Instead, just nest all the code you didnt include in your question inside the If statement, and switch the condition to the opposite of the boolean:

If Not comment_Or_coupon Then
  ' i want to continue doing code
End If

answered Nov 20 '19 at 10:11

braX

11,506
5
20
33

score 0 · Answer 2 · answered Nov 20 '19 at 10:51

0

In order to make us understand your need, I think you must better describe what you need. If I correctly understand, next code should do what you want to do:

 For Each rowHeader In tempList:
        comment_Or_coupon = False
        If InStr(1, LCase(rowHeader), "comment", vbTextCompare) = 0 Then _
                                comment_Or_coupon = True: GoTo NextRowHeader
        If InStr(1, LCase(rowHeader), "coupon", vbTextCompare) = 0 Then _
                                comment_Or_coupon = True: GoTo NextRowHeader      

         ' you can do here whatever you want. This part of the code will be running only 
         ' if the previous two conditions are false. If you need 'comment_Or_coupon'
         ' boolean variable only for this reason, you can delete it.

NextRowHeader:
 Next rowHeader

answered Nov 20 '19 at 10:51

FaneDuru

38,298
4
19
27

Although using `GoTo` is considered very bad practice these days, and should be used only when absolutely necessary. – braX Nov 20 '19 at 16:13
@braX: Why is it a bad practice? Not only these days...When you are in a For Each loop, it is the best way to jump at the end of it. You also can avoid the case of reference missing in some cases, I think. – FaneDuru Nov 20 '19 at 17:52
1

Spaghetti code using `GoTo` is always bad practice. The `Do While` or `Do Until` loop constructs can be used instead of `For x =` or `For Each` Loops. Or you can `Exit For`. All of these have better control, readability and maintainability. Remember, your `GoTo` will work fine until it doesn't and then you will have difficulty in troubleshooting or understanding why that single line of code that you added screwed completely with your logic. – AJD Nov 20 '19 at 19:25
@AJD: But, in this specific case the trick was to not exit the loop. Just to jump to the end of the loop in some specific circumstances. Avoiding If - End if. Why GoTo will not work as expected ? I think that exactly for avoiding debugging confusions the label is automatically positioned to the editor most left position, independent of your wish. – FaneDuru Nov 20 '19 at 20:28
1

`If`.. `End If` will skip the extra code, and the `End If` should bring you out near the end of the loop. It ***should*** do that because you ***should*** code it that way. `GoTo` can be easily broken, especially if you continue down your paradigm. What if you want two exits? what if you put an extra line of code in - before the `Goto`, after the `GoTo` or around the `Label:`? From a maintenance perspective, it is too easy to start missing things because trying to follow `GoTo` logic branches (and the relevant values of variables) gets difficult and complicated. This is coding 101 these days. – AJD Nov 21 '19 at 03:56
1

And, having seen how some people indent (or don't indent) their code - there is no guarantees that the label will stick out! Of course, if you wish to continue to fly in the face of experience and learn the lessons the hard way, feel free! Remind me in 5 years time to ask about your views on `GoTo` again :-). – AJD Nov 21 '19 at 03:58
I learned about this myth and I tried to understand it. Experimenting also and trying to understand the potential bad things able to happen. I program in VBA from more than 15 years and initially I tried to avoid using or GoTo, because of that myth. But I started to use it when served better my intentions and nothing wrong happened. So, I can have as many 'exits' I want! But they must be located before the GoTo or after its label. Anyhow, in the presented case you couldn't use such 'Exits' because the code between If and End If does not run. I can also put as many lines I want after the label. – FaneDuru Nov 21 '19 at 09:08
@AJD: Without indenting, the code is infinitely more difficult to be debugged if you use If .. End If (with a lot of others If ..End If in between). In this case, having an error you must visually check what is happening there. It is obviously easier to find a label, I think. But making the long story short, I do not care about "I should code in that way". I can accept that it may be something dangerous which I do not faced/know. But, can you explain **Why** is it better? Otherwise, with all respect, I consider we are just losing time. – FaneDuru Nov 21 '19 at 09:16
Simple search reveals many links. But that requires reading and understanding all the arguments. And, simply put - your example of `GoTo` in the above code is a bad use of `GoTo` - for all the reasons people suggest `GoTo` is still useful your code has none of them. https://www.harrisgeospatial.com/Learn/Blogs/Blog-Details/ArtMID/10198/ArticleID/15289/Why-should-GOTO-be-avoided https://stackoverflow.com/q/46586/9101981 https://www.drdobbs.com/jvm/programming-with-reason-why-is-goto-bad/228200966?pgno=1 https://en.wikipedia.org/wiki/Goto http://www.cs.utexas.edu/users/EWD/ewd02xx/EWD215.PDF – AJD Nov 21 '19 at 18:58
https://stackoverflow.com/q/16599982/9101981 https://stackoverflow.com/q/52705264/9101981 https://www.quora.com/Why-is-the-goto-statement-in-C-advised-to-avoid – AJD Nov 21 '19 at 19:10
@AJD: I would conclude that you are knocking to an open door giving those example. On my test, both of them prove that not using `GoTo` is a myth or a pretentious approach. I think it's a pity to fight on the issue. I can admit that it may not be the best approach, but since the main invoked issue/argument on the subject is the not very well code readability, I state that I can handle this aspect even better than in `If - End If` cases... I like learning and if you have some other links trying to bring arguments, not statements I will read them with attention. Thanks, anyhow! – FaneDuru Nov 21 '19 at 20:56
I have taken the water to the horse, but I cannot make the horse drink. Don't look at those articles for reasons to reinforce your current views - look at those articles for the arguments against your current views and see how that fits in with your example above. It behoves all of us providing answers to practice the best code hygiene that we can. Avoiding `IF ,,, End If` for ideological reasons as you are is exhibiting the very behaviour that you seem to railing against for the 'GoTo myth'. – AJD Nov 22 '19 at 00:42
@AJD: I do not say that you are not right. I try to say that you maybe are not right... I would like to emphasize the idea that I am not against, I do not try to avoid `IF ... End If`. I use `GoTo` very seldom but, when it does its "job" better (on my taste, of course), then I use it. Without bothering of the "myth"... I used to believe the "myth", but only some years before. – FaneDuru Nov 22 '19 at 08:22
@AJD: In this very case we are talking about, nothing can persuade me that the solution was wrong. All the argumentation regarding some eventual Exits does not stand. The piece of code between `If ... End If`, anyhow is jumped by the code. On the contrary, supposing that condition in the piece of code using `GoTo` is false, and you need "exits" in the following code, you can also use the `label`, in case you need to reach the end of the loop, remaining inside it. Besides `Exit For`and `Exit Sub` which exits the loop. Based on this discussion I only can promise to check the "myth" deeper. :-) – FaneDuru Nov 22 '19 at 08:23

Use IF statement to push Next statement if true

2 Answers2