vba 对于开始到结束循环，结束可变改变中循环

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

提问by Chrismas007

Starting with test data:

从测试数据开始：

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:

我希望循环在第 2 行结束，因为我更改了变量LastRow。然而结果是：

The MSDN Reference FOR VB.NEThas this to say:

该参考MSDN FOR VB.NET有这样一段话：

When a For...Nextloop starts, Visual Basic evaluates start, end, and step. Visual Basic evaluates these values only at this time and then assigns startto counter. Before the statement block runs, Visual Basic compares counterto end. If counteris already larger than the endvalue (or smaller if stepis negative), the For loopends and control passes to the statement that follows the Nextstatement. Otherwise, the statement block runs.

Each time Visual Basic encounters the Nextstatement, it increments counterby stepand returns to the Forstatement. Again it compares counterto end, and again it either runs the block or exits the loop, depending on the result. This process continues until counterpasses endor an Exit Forstatement is encountered. [Emphasis mine]

当一个For...Next循环开始时，Visual Basic计算start，end和step。Visual Basic 仅在此时计算这些值，然后分配start给counter. 在语句块运行之前，Visual Basic 将counter与end. 如果counter已经大于该end值（或者如果step为负则更小），For loop结束和控制传递到该语句后面的Next语句。否则，语句块运行。

每次Visual Basic中遇到的Next声明，它增加counter通过step并返回到For语句。再次它比较counter来end，并再次它要么运行块或退出该循环，根据结果。这个过程一直持续到counter通过end或Exit For遇到一个语句。[强调我的]

Given that it checks the counteragainst the endevery iteration, shouldn't changing the variable change the end?

鉴于它counter针对end每次迭代检查，不应该更改变量更改end?

采纳答案by RubberDuck

I'd like to note a few things here. First, you're referencing the VB.Netdocumentation. So, I wouldn't rely on that when talking about VBA. In fact, the VBA Documentation for the For...Nextstatementdoesn'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.

我想在这里说明几点。首先，您参考的是VB.Net文档。所以，在谈论VBA时，我不会依赖它。事实上，该语句的VBA 文档For...Next根本没有提到这种行为。所以，我挖得更深一些，VBA 确实与 VB.Net 在这里的行为相同。以下来自[MS-VBAL]：VBA 语言规范。

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

表达式<start-value>、<end-value>和<step-increment>被评估一次，按顺序，并在以下任何计算之前。

This means that LastRowin 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.)

这意味着LastRow在您的示例中仅在首次进入循环时计算。之后更改它对循环运行的次数没有影响。（并不是说我建议一开始就尝试这样做。）

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...Nextstatement.

对于那些感兴趣的人，这里是For...Next语句的整个运行时语义。

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.

运行时语义。

• 表达式<start-value>, <end-value>, 和<step-increment>被评估一次，按顺序，并在以下任何计算之前。如果价值<start-value>， <end-value>以及<step-increment>是不是让，强制转换为Double，错误13（类型不匹配）立即提出。否则，使用原始的非强制值继续执行以下算法。

• <for-statement>根据以下算法执行收益：

  1. 如果 的数据值为<step-increment>0 或正数，且 的值<bound-variable-expression>大于 的值<end-value>，则<forstatement>立即完成执行；否则，前进到步骤 2。

  2. 如果 的数据值为<step-increment>负数，且 的值<bound-variable-expression>小于 的值<end-value>，则<for-statement>立即完成执行；否则，前进到步骤 3。

  3. 将<statement-block>被执行。如果 a<nested-for-statement>存在，则执行它。最后，将 的值与 的值<bound-variable-expression>相加， <step-increment>并赋值给 <bound-variable-expression>。然后在步骤 1 重复执行。

• 如果一个<goto-statement>外部定义的<for-statement>使<statement>内<statement-block>被执行的，表述<start-value>，<end-value>以及<step-increment>不被评估。如果 的执行<statement-block>完成并在<statement-block>没有评估的情况下 到达 的末尾<start-value>，<end-value>并且<step-increment>在封闭过程的执行期间，将生成错误（编号 92，“For 循环未初始化”）。即使包含<bound-variable-expression>显式初始化的赋值表达式，也会发生这种情况 。否则，如果表达式<start-value>、<end-value>、 和<step-increment>已经被求值，算法会根据为 a 的执行定义的规则在步骤 3 继续<for-statement>。

• 当<for-statement>完成执行时， 的值<bound-variable-expression>保持在它在循环完成时保持的值。

回答by Chrismas007

Given that the Correct MSDN Referencementions:

鉴于正确的 MSDN 参考提到：

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

表达式<start-value>, <end-value>, 和<step-increment>被评估一次，按顺序，并在以下任何计算之前。

It appears that Visual Basic CANremember the value of the endduring the loop even if the variable containing the endamount changes.

看来，Visual Basic中的CAN记得的价值end，即使包含变量的循环过程中end量的变化。

回答by Jason Faulkner

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

基于上面@Chrismas007 的回答，如果您想在For结构范围内短路循环，您只需将递增变量设置为终止值：

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