1

Is it posible to update the for loop condition?
I need to loop from row 1 to lastrow of one sheet, when a condition is meet I need switch sheet and loop to end of that sheet.

Just for demo: 

lr = 5
For i = 1 To lr
    If i = 3 Then
        lr = 500
        i = 50
    End If
Next i

This code ends at i=51 because the for loop has not updated it's condition before it ends at the Next i.
Can this be solved some how?

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • 1
    use a [`while`](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/while-end-while-statement) loop instead? the make your exit condition monitor a boolean flag variable and manually set that to false when you want to exit – Dan Apr 05 '18 at 13:23
  • Maybe... Can a while loop update itself? `EDIT: it can!` – Andreas Apr 05 '18 at 13:24
  • It's not updating itself - the condition is based on a variable that get's checked each iteration. If your for loop did that it would lead to pretty confusing code I would think. – Dan Apr 05 '18 at 13:32
  • @Dan Why would it be confusing? If you need to update the loop conditions then you have no choice. I know I have used the same thing at some point in PHP too. – Andreas Apr 05 '18 at 14:01
  • 2
    Right, PHP doesn't have any "annoying flaws" at all. – Mathieu Guindon Apr 05 '18 at 14:10
  • 1
    @Andreas it's unexpected behaviour for a for-loop, the whole point of them is to loop through a simple incremental iteration. If you aren't sure of the limit upfront then you just shouldn't be using a for-loop. Even the way it reads in English makes it sound like a predefined number of iterations. Deviating from norms in programming leads to confusion and errors (not to mention it will take the next person a lot of extra and unnecessary thinking to understand what you've done). – Dan Apr 05 '18 at 14:20
  • 1
    @Dan: Andreas and I are on the other side of the fence on this one as I suspect he has the same background as I. That said though, once you know this, you know it for ever. – Bathsheba Apr 05 '18 at 14:27
  • @Bathsheba so long as no uses those `GoTo` solutions :) but many languages these days don't even have for-loops anymore (Python, Swift) but rather foreach or for in where you iterate over a collection that you definitely can't change. I just think it's a confusing idiom to state upfront what you are iterating over and then change that internally. – Dan Apr 05 '18 at 14:33
  • @Andreas Have a look at the language guide: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/looping-through-code under the *choosing a loop to use* section it states *For...Next: Using a counter to run statements a* ***specified number of times*** – Dan Apr 05 '18 at 15:04
  • @Dan I see your point in that it may make the code hard to follow but that is what comments are for. I don't think the code will be any easier to understand with an while loop or any other loop. The "problem" remains. Each sheet has one week of data. I need to start at, for example Wednesday and loop one week forward. So I loop til last row or "Monday", whichever comes first then I go to the next sheet and find start point and last row and keep looping. And also as stated in comments below VBA has a fantastic debugging and steping through code that can make any code as clear as a diamond :-) – Andreas Apr 05 '18 at 16:58

4 Answers4

6

No, VBA precomputes lr so subsequent changes to lr have no effect. If you come from a C, C++, or Java background (as do I) that can come as quite a shock! In terms of VBA's evolution from the various BASICs that have been around since the mid 1960s, the way it works makes perfect sense.

Your best bet is to refactor to a Do While or similar. In that case the stopping conditional is re-evaluated on each iteration.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • *urghhhh* thanks.. Some things are really annoying with VBA. – Andreas Apr 05 '18 at 13:24
  • You have to love the debug capabillities with steping through code. But yes it does have a few... flaws... – Andreas Apr 05 '18 at 13:28
  • 1
    @Andreas: Absolutely! VBA is the only language in which I actively develop in a debugging session. – Bathsheba Apr 05 '18 at 13:29
  • C, C++, Java, C# and others implement `for` loops with separate statements/instructions (notice these semicolons?) that specifically separate the initialization, exit condition, and increment parts. VB6/VBA `For` loop syntax *clearly* doesn't have that separation - that's just the way it is, no need to be condescending about it, and no need for that VBA-bashing talk either. Different languages have different mechanics, and they're clearly specified, and that's part of what makes different languages *different*. – Mathieu Guindon Apr 05 '18 at 14:10
  • @MathieuGuindon: What does VB.NET do? I work in finance, where the average quant analyst codes in C, C++ and Java, and occasionally VBA. This corner of VBA trips such folk up all the time. Another one is the rounding when you "cast" a `double` to an `int`. Most languages truncate the decimal, VBA rounds it. – Bathsheba Apr 05 '18 at 14:11
  • AFAICT, exactly the same behavior as VB6/VBA. – Mathieu Guindon Apr 05 '18 at 14:13
  • Exactly. AFAICT. So it's not known in a click of the finger. I don't know either but my bet is that it's evaluated on each iteration. – Bathsheba Apr 05 '18 at 14:14
  • 1
    [Nope](https://dotnetfiddle.net/Uz6dzE). That would have been a rather massively confusing change, given VB.NET was meant to appease the VB6 crowd migrating to .NET. – Mathieu Guindon Apr 05 '18 at 14:15
  • @MathieuGuindon: That's good to know. I suspect it comes from the early BASICS (e.g. BBC model B, and the remarkably good BASIC that shipped with the Amstrad CPC series). – Bathsheba Apr 05 '18 at 14:16
  • @MathieuGuindon: VBA is a scaled-down version of God. When it came out in 1997 it was truly awesome. It's a pity though that Microsoft has invested minimal time and effort into it since then - barring a kludge to accommodate 64 bit Office products. PHP sucks since they reversed the associativity of the ternary conditional operator! Why? – Bathsheba Apr 05 '18 at 14:23
4

Using Do While instead of For:

lr = 5
i = 1
Do While i < lr
    If i = 3 Then
        lr = 500
        i = 50
    End If
    i = i+1
Loop
Dan
  • 45,079
  • 17
  • 88
  • 157
  • `End While` , I think it should be `Wend` instead, am I wrong? – Foxfire And Burns And Burns Apr 05 '18 at 13:38
  • 1
    @FoxfireAndBurnsAndBurns that's correct. `While...Wend` is also an obsolete construct, deprecated by `Do While...Loop`. – Mathieu Guindon Apr 05 '18 at 13:41
  • @MathieuGuindon thanks, I've edited it. – Dan Apr 05 '18 at 13:43
  • But don't forget to change the preamble too. – Bathsheba Apr 05 '18 at 13:44
  • 2
    This. This is the idiomatic code that answers OP's question without being condescending and without introducing silly work-arounds. – Mathieu Guindon Apr 05 '18 at 13:50
  • @MathieuGuindon Do you have a link to the official documentation? I couldn't track it down through the forest of other VB docs online – Dan Apr 05 '18 at 14:37
  • 2
    @Dan the [language specifications](https://msdn.microsoft.com/en-us/library/dd361851.aspx), the [VB6 language reference](https://msdn.microsoft.com/en-us/library/aa338033(v=vs.60).aspx), and here's the [Office VBA Reference](https://msdn.microsoft.com/vba/office-vba-reference), where you'll find the [VBA For...Next Statement](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/fornext-statement) documentation. I'll probably be contributing to these in the near future, it's all hosted on GitHub =) – Mathieu Guindon Apr 05 '18 at 14:55
  • @MathieuGuindon Thanks! – Dan Apr 05 '18 at 15:03
1

just for fun, you could cheat a little:

    lr = 5
begin:
    For i = 1 To lr
        If i = 3 And Not switched Then
            lr = 500
            switched = True
            GoTo begin
            i = 50
        End If
        Debug.Print i
    Next
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • I like this one, because it restarts from 1 the For Next. Nice one – Foxfire And Burns And Burns Apr 05 '18 at 13:47
  • 2
    Introducing a `GoTo` jump simply can't be the answer. If you wouldn't do it in Java or C#, then why would you even suggest doing it in VBA? Crap code is on the coder, not the language. – Mathieu Guindon Apr 05 '18 at 13:48
  • 1
    @MathieuGuindon. the question was _"Is it possible to update the for loop condition?"_. And my code answers that _very_ question. Furthermore I made a clear premise: "just for fun". Because the timing of my answer was such that other answers already pointed out the _right path_. I think what above can lead to consider removing your down vote – DisplayName Apr 05 '18 at 13:53
  • Fair enough. Had to edit to remove the vote though (rolled it back). – Mathieu Guindon Apr 05 '18 at 13:55
0

You can be kind of tricky and code something for you to work. In this code, you will need a third variable to restart your For Loop (please, note this code won't do a real restart, when i=3 it'll jump to 50, not restarting from 1. You will get 1, 2, 3, 50, 51, ....)

lr = 5
zz = 1

For_Loop_Start:

For i = zz To lr
    If i = 3 Then
        lr = 500
        zz = 50
        GoTo For_Loop_Start
    End If
Next i

Another option would be just a Do Until:

lr = 5
i = 1
Do Until i > lr
    If i = 3 Then
        lr = 500
        i = 49
    End If
    i = i + 1
Loop

Of course, you can also use other options as Do While, Do While not,...etc.

Rules in programming are precomputed, but sometimes you can code stuff that "skip" those rules, and make the code what you want to. Is just imagination.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38