在for循环中使用if语句 - Excel VBA

问题描述

我在使用excel vba for循环中的if语句时遇到了麻烦。调试器的输出不是我所期望的。我可以发布我想要完成的完整代码，但是我想我已经把它缩小到了我不了解的地步。这是我的代码：

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Dim i As Integer 
 
对于i = 9到60步骤3 
 
如果单元格（i，DR），值< Cells（i，EB）。Value Then 
 Debug.Print i& -ifloopstart
 Cells（i，DR）。Value = 999999 
 Debug.Print i& -ifloopend
结束如果
 
下一个我
 
结束小组
  

调试器的输出是：

  9-ifloopstart 
 33-ifloopstart 
 51-ifloopstart 
 51-ifloopend 
 33-ifloopend 
 9-ifloopend 
  

但是，我预计：

pre $ 9-ifloopstart
9-ifloopend
33-ifloopstart
33-ifloopend
51-ifloopstart
51 -ifloopend


有人可以解释这是如何工作的？它似乎循环回到if语句的开始，而不是完成if语句。我如何修改代码来获得我期望的输出？我一直在挣扎几个小时，这似乎很简单:(。

解决方案

每次工作表更新与 Cells（i，DR）。Value = 999999 ， Worksheet_Change 被再次调用。 b

想想这样，每次上面的代码被调用时，都会修改一个单元格，它会再次触发工作表的更改方法。

所以你实际上嵌套了这个函数的三个调用：
$ b

• 调用一次，用i = 9
  
  
  再次调用>
  
  • ，再次调用i = 33
    
    
    
    
    
    
    • 完成i = 51呼叫
    
    
  
  
  • 完成i = 33呼叫
  
  
  • 完成i = 9调用
  
  
  
  
  

  然后，VBA从每一个中退回到第一次您的方法已运行。

  
  
  

  编辑：as Application.EnableEvents = False

  禁用这个功能。
  

  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:

  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 :( .

  解决方案

  Each time the worksheet is updated with Cells(i, "DR").Value = 999999, Worksheet_Change gets called again.

  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
  • finishes i = 9 call

  VBA then goes backwards from each of these to get back to the first time your method was run.

  Edit: as Tim says you can disable this with Application.EnableEvents=False

  这篇关于在for循环中使用if语句 - Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！