如何结束 VBA 中的无限“更改”循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28029283/
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
How to end infinite "change" loop in VBA
提问by Pawel
I have a problem with visual basic. I want to make a macro/function that will multiply a number I enter by 3 and give a result in the same cell. I tried something like this:
我的visual basic有问题。我想制作一个宏/函数，它将我输入的数字乘以 3 并在同一个单元格中给出结果。我试过这样的事情：
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Q" Then
Target.Value = Target.Value * 3
End If
End Sub
but it doesn't work - I'm getting results like "xE25" because it keeps multiplying.
I'd like it to stop after first iteration or work only when I press "enter" not with every change in the cell.
It's quite easy to put a result in different cell, but it's not my point.
-----Edit:
I edited "If" line to :If (Target.Column = 5 Or Target.Column = 11 Or Target.Column = 17 Or Target.Column = 23) And (Target.Row >= 19 And Target.Row <= 24) And Target.Value <> "" Then
so it would work on all cells that I need. After that, the best solution is the way given by @Chrismas007, because it doesn't prompt an error when trying to delete data in few cells at once.
但它不起作用 - 我得到像“xE25”这样的结果，因为它一直在增加。
我希望它在第一次迭代后停止，或者仅在我按“输入”而不是在单元格中的每次更改时才工作。
将结果放在不同的单元格中很容易，但这不是我的意思。
-----编辑：
我将“如果”行编辑为：If (Target.Column = 5 Or Target.Column = 11 Or Target.Column = 17 Or Target.Column = 23) And (Target.Row >= 19 And Target.Row <= 24) And Target.Value <> "" Then
这样它就可以在我需要的所有单元格上工作。之后，最好的解决方案是@Chrismas007给出的方式，因为它在尝试一次删除几个单元格中的数据时不会提示错误。
采纳答案by Chrismas007
With error handling to ensure .EnableEventsgoes back to True:
通过错误处理以确保.EnableEvents返回到True：
Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanExit
If Target.Address = "$Q" Then
Application.EnableEvents = False
Target.Value = Target.Value * 3
End If
CleanExit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
回答by Chrismas007
When a Worksheet_Changeevent macro changes a value, you need to set Application.EnableEventsto falseor risk triggering another event and having the macro run on top of itself.
当Worksheet_Change事件宏更改值时，您需要设置Application.EnableEvents为false或冒触发另一个事件并使宏在其自身之上运行的风险。
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Q" Then
Application.EnableEvents = False
Target.Value = Target.Value * 3
Application.EnableEvents = True
End If
End Sub
While I would usually involve some error control in order that the .EnableEventswas always reset to true, the above should get you started.
虽然我通常会涉及一些错误控制，以便.EnableEvents始终将 重置为true，但上述内容应该可以帮助您入门。
回答by Rory
You need to disable events to prevent recursion:
您需要禁用事件以防止递归：
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Q" Then
application.enableevents = false
Target.Value = Target.Value * 3
application.enableevents = true
End If
End Sub
Just as an alternative, you can also use your own variable rather than tinkering with EnableEvents, though it will mean your code responds twice even though it doesn't actually do anything the second time round:
作为替代方案，您也可以使用自己的变量而不是修改 EnableEvents，尽管这意味着您的代码即使第二次实际上没有做任何事情也会响应两次：
Dim bSkipEvents as Boolean
Sub Worksheet_Change(ByVal Target As Range)
If bSkipEvents then exit sub
If Target.Address = "$Q" Then
bSkipEvents = True
Target.Value = Target.Value * 3
bSkipEvents = False
End If
End Sub
This is also the sort of approach you need with userforms and most events for activex controls on worksheets.
这也是您需要使用用户表单和工作表上 activex 控件的大多数事件的那种方法。
