vba - Worksheet_Change 事件中的目标范围不正确

Question

我是一名 Excel-VBA 新手，我正在尝试编写一小段代码，该代码是由某人更改工作表中单元格的值触发的。如果已更改单元格的值小于零，则应将其设置为零。代码如下所示:

Private Sub Worksheet_Change(ByVal Target As Range)
'Debug.Print Target.Address
 If Target.Column = 6 Then
  For Each Cell In Target.SpecialCells(xlCellTypeConstants, 3)
     If Cell.Value < 0 Then
        Cell.Value = 0
     End If
  Next
 End If
End Sub

现在发生的情况是，当我更改第 6 列中任何单元格的值时，工作表中包含小于零的数字的每个单元格也会更改为零。

我认为为 Worksheet_Change 事件处理程序创建的“目标”对象将仅包含更改的单元格，因此我的代码只会更改已修改的单元格的值并首先触发了该事件。

我尝试使用Debug.Print来输出对象的地址来帮助自己。它打印出工作表中值小于零的每个单元格的地址，因此我假设处理程序运行了几次。

我实际上找到了问题本身的解决方法，但我的问题是:我如何使用 Worksheet_Change 事件失败以及将来我可以做什么以避免出现此类问题？

Answer 1

编辑 1:使用评论中建议的错误修复更新了代码

编辑 2:使用错误处理更新了代码以处理数组公式

回答你的问题

[1] how did I fail in using the Worksheet_Change event and [2] what can I do in the future to not have such problems?

1. 从技术上讲，你没有
2. 对于此类问题没有任何帮助，尽管以下规则总体上有帮助

您对Target对象的理解是正确的。您的代码失败，因为 SpecialCells 不喜欢对单个单元格进行操作。给它一个，它就会将其扩展到整个工作表!给它任何其他东西，它就可以正常工作。

Debug.Print 显示所有单元格的原因是，每次代码更改单元格时，都会触发另一个更改事件。幸运的是，第二个找到了零，因此它不会触发另一个 1。以下一般规则应该有助于避免很多问题，但不是这个特定的问题:

• 始终使用 Application.EnableEvents 将更改工作簿任何部分的事件处理程序中的任何代码括起来。

要修复代码使其正常工作，只需删除 SpecialCells 方法调用即可。由于您使用的是 Cell.Value 而不是强烈推荐的 Cell.Value2(请参阅 here )，VBA 隐式类型会将文本格式的数字转换为实际数字。因此，该代码既适用于数字值，也适用于文本值。

代码:

Private Sub Worksheet_Change(ByVal Target As Range)
  'Debug.Print Target.Address;
  Application.EnableEvents = False
    For Each Cell In Target '.SpecialCells(xlCellTypeConstants, 3)
      If Cell.Column = 6 And Cell.Value < 0 Then
        On Error GoTo Error:
        Cell.Value = 0
        On Error GoTo 0
      End If
    Next
GoTo ExitSub:
Error:
  If Err.Number = 1004 Then ' 1004 -> "You cannot change part of an array."
    'Application.Undo ' Uncomment to disallow array entering a negative value formula into/across column 6
    MsgBox "A cell could not be zeroed as" & vbCr & "it is part of an array formula.", vbExclamation, "Microsoft Office Excel"
    On Error GoTo 0
  Else
    On Error GoTo 0
    Resume
  End If
ExitSub:
  Application.EnableEvents = True
End Sub

_注释:

_{- 更新 1:代码现在可以正确处理多单元格更改。}

_{- 更新 2:代码现在捕获错误 1004 来处理输入数组公式。它可以允许输入数组公式，从而在第 6 列中产生负值，也可以完全停止输入。}