每次单元格通过公式更改其值时，如何运行 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_Calculatecode> 不提供 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，在这种情况下，代码 Target.Dependents.Address 将返回 $A$1 对 B1 或 C1 的任何更改.

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屋！