每当单元格的值被公式更改时，如何运行VBA代码? [英] How can I run VBA code each time a cell gets its value changed by a formula?

问题描述

每次单元格通过公式更改其值时如何运行VBA函数?

How can I run a VBA function each time a cell gets its value changed by a formula?

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

I've managed to run code when a cell gets its value changed by the user, but it doesn't work when the value is changed due to a formula referencing another cell.

推荐答案

如果我在单元格A1中有一个公式(例如= B1 * C1)，并且由于每个单元格的更新而导致每次A1更改时我都想运行一些VBA代码B1或C1，则可以使用以下内容:

If I have a formula in cell A1 (e.g. = B1 * C1) and I want to run some VBA code each time A1 changes due to updates to either cell B1 or C1 then I can use the following:

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不提供Target对象)

As far as I know the problem with Worksheet_Calculate is that it fires for all cells containing formulae on the spreadsheet and you cannot determine which cell has been re-calculated (i.e. Worksheet_Calculate does not provide a Target object)

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

To get around this, if you have a bunch of formulas in column A and you want to identify which one has updated and add a comment to that specific cell then I think the following code will achieve that:

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.

To explain, for a formula to update, one of the input cells into that formula must change e.g. if formula in A1 is =B1 * C1 then either B1 or C1 must change to update A1.

我们可以使用Worksheet_Change事件来检测s/sheet上的单元格更改，然后使用Excel的审核功能来跟踪依赖项，例如单元格A1依赖于B1和C1，在这种情况下，对于B1或C1的任何更改，代码Target.Dependents.Address都将返回$A$1.

We can use the Worksheet_Change event to detect a cell change on the s/sheet and then use Excel's auditing functionality to trace the dependents e.g. cell A1 is dependent on both B1 and C1 and, in this instance, the code Target.Dependents.Address would return $A$1 for any change to B1 or C1.

鉴于此，我们现在要做的就是检查从属地址是否在A列中(使用Intersect).如果在A列中，则可以将注释添加到适当的单元格中.

Given this, all we now need to do is to check if the dependent address is in column A (using Intersect). If it is in Column A we can then add comments to the appropriate cell.

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

Note that this only works for adding comments once only into a cell. If you want to continue to overwrite comments in the same cell you would need to modify the code to check for the existance of comments first and then delete as required.

这篇关于每当单元格的值被公式更改时，如何运行VBA代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！