excel - 每次单元格的值被公式更改时，如何运行 VBA 代码？

Question

每次单元格的值被公式更改时，如何运行 VBA 函数？

当单元格的值被用户更改时，我设法运行代码，但当由于引用另一个单元格的公式而更改值时，它不起作用。

Answer 1

如果我在单元格 A1 中有一个公式(例如 = B1 * C1)，并且我想在每次 A1 由于单元格 B1 或 C1 更新而发生更改时运行一些 VBA 代码，那么我可以使用以下命令:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("A1")

    If Not Intersect(target, Range("A1")) Is Nothing Then
    //Run my VBA code
    End If
End Sub

<小时/>

更新

据我所知，Worksheet_Calculate 的问题是它会触发电子表格上包含公式的所有单元格，并且您无法确定哪个单元格已被重新计算(即 Worksheet_Calculate) code> 未提供 Target 对象)

为了解决这个问题，如果 A 列中有一堆公式，并且您想要确定哪个公式已更新并向该特定单元格添加注释，那么我认为以下代码将实现这一点:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)

    If Not Intersect(updatedCell, Range("A:A")) Is Nothing Then
       updatedCell.AddComment ("My Comments")
    End If

End Sub

解释一下，要更新公式，该公式的输入单元格之一必须更改，例如如果 A1 中的公式为 =B1 * C1，则必须更改 B1 或 C1 才能更新 A1。

我们可以使用 Worksheet_Change 事件来检测工作表上的单元格更改，然后使用 Excel 的审核功能来跟踪依赖项，例如单元格 A1 依赖于 B1 和 C1，在本例中，代码 Target.Dependents.Address 将返回 $A $1 用于对 B1 或 C1 进行任何更改。

鉴于此，我们现在需要做的就是检查依赖地址是否在 A 列中(使用 Intersect)。如果它在 A 列中，我们可以将注释添加到相应的单元格中。

请注意，这仅适用于在单元格中添加一次注释。如果您想继续覆盖同一单元格中的注释，则需要修改代码以先检查注释是否存在，然后根据需要删除。