在 for 循环中使用 if 语句 - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18410612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an if statement within a for loop- Excel VBA
提问by DanW
I'm having trouble using an if statement inside a for loop in excel vba. The output of the debugger is not what I expect. I can post the full code of what I am trying to accomplish, but I think I have narrowed it down to what I don't understand. Here is my code:
我在 Excel vba 的 for 循环中使用 if 语句时遇到问题。调试器的输出不是我所期望的。我可以发布我想要完成的完整代码，但我想我已经把它缩小到我不明白的地方。这是我的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 9 To 60 Step 3
If Cells(i, "DR").Value < Cells(i, "EB").Value Then
Debug.Print i & "-ifloopstart"
Cells(i, "DR").Value = 999999
Debug.Print i & "-ifloopend"
End If
Next i
End Sub
The output of the debugger is:
调试器的输出是：
9-ifloopstart
33-ifloopstart
51-ifloopstart
51-ifloopend
33-ifloopend
9-ifloopend
However, I expected:
但是，我预计：
9-ifloopstart
9-ifloopend
33-ifloopstart
33-ifloopend
51-ifloopstart
51-ifloopend
Can someone explain how this works? It seems to be looping back to the beginning of the if statement instead of finishing the if statement. How can I modify the code to get the output I expect? I've been struggling with this for hours and it seems so simple :( .
有人可以解释这是如何工作的吗？它似乎循环回到 if 语句的开头，而不是完成 if 语句。如何修改代码以获得我期望的输出？我已经为此苦苦挣扎了几个小时，这似乎很简单:(。
回答by enderland
Each time the worksheet is updated with Cells(i, "DR").Value = 999999, Worksheet_Changegets called again.
每次使用 更新工作表时Cells(i, "DR").Value = 999999，Worksheet_Change都会再次调用。
Think of it this way. Each time that above code gets called, you modify a cell, which triggers the worksheet change method again.
这么想吧。每次调用上述代码时，您都会修改一个单元格，这将再次触发工作表更改方法。
So you are effectively nesting three calls of this function:
所以你有效地嵌套了这个函数的三个调用：
- called once, with i = 9
- called again, with i = 33
- called again, with i = 51
- finishes i = 51 call
- finishes i = 33 call
- called again, with i = 33
- finishes i = 9 call
- 调用一次，i = 9
- 再次调用，i = 33
- 再次调用，i = 51
- 完成 i = 51 通话
- 完成 i = 33 通话
- 再次调用，i = 33
- 完成 i = 9 通话
VBA then goes backwards from each of these to get back to the first time your method was run.
VBA 然后从这些中的每一个向后返回到您的方法第一次运行时。
Edit: as Tim saysyou can disable this with Application.EnableEvents=False
编辑：正如蒂姆所说，你可以禁用它Application.EnableEvents=False
