CODEWITHSUNDEEP
vbaloopsexcel
Chrismas007
Chrismas007

Reputation: 6105

For start To end Loop with end Variable Changing Mid-Loop

Starting with test data:

Start

And running the code:

Sub TestLoop()

Dim LastRow As Long, CurRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For CurRow = 1 To LastRow
    Range("B" & CurRow).Value = "Done"
    LastRow = 2
Next CurRow

End Sub

I would expect the Loop to end at Row 2 because I changed the variable LastRow. However the result is:

Done

The MSDN Reference FOR VB.NET has this to say:

When a For...Next loop starts, Visual Basic evaluates start, end, and step. Visual Basic evaluates these values only at this time and then assigns start to counter. Before the statement block runs, Visual Basic compares counter to end. If counter is already larger than the end value (or smaller if step is negative), the For loop ends and control passes to the statement that follows the Next statement. Otherwise, the statement block runs.

Each time Visual Basic encounters the Next statement, it increments counter by step and returns to the For statement. Again it compares counter to end, and again it either runs the block or exits the loop, depending on the result. This process continues until counter passes end or an Exit For statement is encountered. [Emphasis mine]

Given that it checks the counter against the end every iteration, shouldn't changing the variable change the end?

Upvotes: 3

Views: 8036

Answers (3)

RubberDuck
RubberDuck

Reputation: 12728

I'd like to note a few things here. First, you're referencing the VB.Net documentation. So, I wouldn't rely on that when talking about VBA. In fact, the VBA Documentation for the For...Next statement doesn't mention this behavior at all. So, I dug a little deeper and VBA does indeed behave the same as VB.Net here. The following is from the [MS-VBAL]: VBA Language Specification.

The expressions <start-value>, <end-value>, and <step-increment> are evaluated once, in order, and prior to any of the following computations.

This means that LastRow in your example is only calculated when first entering the loop. Changing it afterward has no effect on the number of times the loop will run. (Not that I advise trying to do that to begin with.)

This also means that you can not change the size of the steps the loop takes mid-execution as well and this snippet shows the "odd" behavior of both of these at once.

Sub TestLoop()

    Dim LastRow As Long, CurRow As Long, StepsToTake As Integer
    LastRow = 100
    StepsToTake = 2

    For CurRow = 1 To LastRow Step StepsToTake
        Range("B" & CurRow).Value = "Done"
        LastRow = 2
        StepsToTake = 1
    Next CurRow

End Sub

For those interested, here are the entire runtime semantics of the For...Next statement.

Runtime Semantics.

  • The expressions <start-value>, <end-value>, and <step-increment> are evaluated once, in order, and prior to any of the following computations. If the value of <start-value>, <end-value>, and <step-increment> are not Let-coercible to Double, error 13 (Type mismatch) is raised immediately. Otherwise, proceed with the following algorithm using the original, uncoerced values.

  • Execution of the <for-statement> proceeds according to the following algorithm:

    1. If the data value of <step-increment> is zero or a positive number, and the value of <bound-variable-expression> is greater than the value of <end-value>, then execution of the <forstatement> immediately completes; otherwise, advance to Step 2.

    2. If the data value of <step-increment> is a negative number, and the value of <bound-variable-expression> is less than the value of <end-value>, execution of the <for-statement> immediately completes; otherwise, advance to Step 3.

    3. The <statement-block> is executed. If a <nested-for-statement> is present, it is then executed. Finally, the value of <bound-variable-expression> is added to the value of <step-increment> and Let-assigned back to <bound-variable-expression>. Execution then repeats at step 1.

  • If a <goto-statement> defined outside the <for-statement> causes a <statement> within <statement-block> to be executed, the expressions <start-value>, <end-value>, and <step-increment> are not evaluated. If execution of the <statement-block> completes and reaches the end of the <statement-block> without having evaluated <start-value>, <end-value> and <step-increment> during this execution of the enclosing procedure, an error is generated (number 92, "For loop not initialized"). This occurs even if contains an assignment expression that initializes <bound-variable-expression> explicitly. Otherwise, if the expressions <start-value>, <end-value>, and <step-increment> have already been evaluated, the algorithm continues at Step 3 according to the rules defined for execution of a <for-statement>.

  • When the <for-statement> has finished executing, the value of <bound-variable-expression> remains at the value it held as of the loop completion.

Upvotes: 4

Jason Faulkner
Jason Faulkner

Reputation: 6558

Building on @Chrismas007's answer above, if you want to short-circuit the loop within the confines of the For structure, you can just set the incremented variable to the terminating value:

For CurRow = 1 To LastRow
    Range("B" & CurRow).Value = "Done"

    ' This will end the loop.
    ' When Next is hit, CurRow will be LastRow + 1.
    CurRow = LastRow

    ' Alternately, you can exit the for loop immediately.
    ' Usually this would be inside an If statement.
    Exit For
Next CurRow

Upvotes: 1

Chrismas007
Chrismas007

Reputation: 6105

Given that the Correct MSDN Reference mentions:

The expressions <start-value>, <end-value>, and <step-increment> are evaluated once, in order, and prior to any of the following computations.

It appears that Visual Basic CAN remember the value of the end during the loop even if the variable containing the end amount changes.

Upvotes: 4

Related Questions