问当某个值在参考范围内更改时，VBA将更新公式
EN

Stack Overflow用户

提问于 2017-01-26 07:31:28

回答 1查看 1.7K关注 0票数 1

我有一个Excel公式，其中给出了上星期五的日期“=-WEEKDAY() A1 (今天)-1”

只有当另一个工作表B2:D 469中的引用范围内的值发生变化时，我才想更新单元格A1。

下面是我正在使用的代码，但问题是只有在手动更改范围时，代码才能工作。但是，当源透视表刷新时，范围中的值将被更新。我希望在刷新pivot表时更新代码，"B2:D469“范围内的值将发生更改。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Worksheets.("Source").Range("B2:D469")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Worksheets.("Dashboard").Range ("A1").EnableCalculation = True

End If
End Sub

vba

excel

回答 1

Stack Overflow用户

发布于 2017-01-26 13:48:05

Range.Dependents

Range有一个属性Dependents，它是一个包含此范围影响的所有单元格的范围，即使这些单元格已被移除几个步骤。例如，如果C4为"= B4“，而B4为"=A4"，则Range("A4").Dependents将同时包含B4和C4。

因此，在您的示例中，如果Target影响到您所关心的单元格，则它包含在范围Target.Dependents中。你可以用这个来完成你的目标。

如何使用它

使用以下代码作为ThisWorkbook.的代码我已经评论过了，但如果你有问题，可以在评论中随意提问。

Option Explicit

Private RangeToMonitor As Range
Private RangeToChange As Range

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    '---If this is the first change since the workbook has been opened, define the ranges---
    If RangeToMonitor Is Nothing Then
        Set RangeToMonitor = Worksheets("Source").Range("B2:D469")
        Set RangeToChange = Worksheets("Dashboard").Range("A1")
    End If
    '---------------------------------------------------------------------------------------

    'First, check to see if Target is in the RangeToMonitor
    If Not Application.Intersect(Target, RangeToMonitor) Is Nothing Then
        'If so, set your date. Rather than using a formula in the cell - which could go haywire if someone messes with it - just set it straight from VBA
        RangeToChange.Value = Date - Weekday(Date) - 1
    'Second, check to see if a change to Target *triggers* any recalculation of cells in RangeToMonitor.
    'You can do this by looking at Dependents, which is all the cells affected by a change to Target, even several steps removed
    ElseIf hasDependents(Target) Then
        '(The above and below criteria cannot be done in one if condition because VBA boolean operators do not short circuit)
        If Not (Application.Intersect(Target.Dependents, RangeToMonitor) Is Nothing) Then
            RangeToChange.Value = Date - Weekday(Date) - 1
        End If
    End If
End Sub


'The reason for this function is that trying to use Target.Dependents when Target has no Dependents causes an error
'I use this function to verify that Target DOES have Dependents before attempting to find out if any of them affects RangeToMonitor
Private Function hasDependents(rng As Range)
On Error GoTo ErrHandler
    Dim test As Long
    test = rng.DirectDependents
    hasDependents = True
    Exit Function

ErrHandler:
    If Err.Number = 1004 Then
        '"No Cells Were Found"
        'This error signifies that Target has no Dependents, so we can safely ignore the change and exit the event.
        hasDependents = False
    Else
        Err.Raise Err.Number, , Err.Description
    End If
End Function