excel - 有意在自身之上运行 Worksheet_Change

Question

_{Skip my rambling narrative by scrolling down to tldr and Question.}

我有几个带有值的行和列；例如答10:G15。在每行中，紧邻任何单元格右侧的单元格的值取决于该单元格，直至涉及的列的范围。通过这种方式，如果原始单元格为空白，则紧邻任何单元格右侧的单元格的值在数值上始终大于该单元格或空白单元格。

为了保持这种依赖性，如果我清除 A:F 中的单元格中的值，我想清除右侧的任何值，或者如果我将新值输入到任何单元格中，则逐步向右侧的剩余单元格添加一个随机数。 A:F 内的单元格。

样本数据。左上角的 7 是 A10。

    A    B     C     D     E     F     G
    7    12    15    19    23    27    28
    4     6    10    14    17    18    22
    8    10    14    18    23    26    31
    8    13    15    18    22    25    30
    8    13    16    18    19    21    24
    0     3     4     9    10    12    16

'similar data in A19:G22 and A26:G30

<强> tldr

    ▪ 如果我清除 D12，E12:G12 也应该被清除。
▪ 如果我在 C14 中输入新值，则 D14:G14 应该各自收到一个新值，该值是
      随机的，但比之前的值大。
▪ 我可能想要清除或粘贴列中的多个值，并希望
      例程依次处理每个值。
▪ 我有几个这样的非连续区域(请参阅下面代码示例中的联合范围
     )，并且更喜欢 DRY coding风格。

<强> Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    'Debug.Print Target.Address(0, 0)
    If Not Intersect(Target, Range("A10:F15, A19:F22, A26:F30")) Is Nothing Then
        Dim t As Range
        For Each t In Intersect(Target, Range("A10:F15, A19:F22, A26:F30"))
            If IsEmpty(t) Then
                t.Offset(0, 1).ClearContents
            ElseIf Not IsNumeric(t) Then
                t.ClearContents
            Else
                If t.Column > 1 Then
                    If t <= t.Offset(0, -1) Or IsEmpty(t.Offset(0, -1)) Then
                        t.ClearContents
                    Else
                        t.Offset(0, 1) = t + Application.RandBetween(1, 5)
                    End If
                Else
                    t.Offset(0, 1) = t + Application.RandBetween(1, 5)
                End If
            End If
        Next t
    End If

End Sub

<强> Code explanation

此事件驱动的 Worksheet_Change 处理已更改的每个单元格，但仅修改直接右侧的单元格，而不修改该行中的其余单元格。维护剩余单元格的工作是通过使事件触发器保持事件状态来实现的，这样当右侧的单个单元格被修改时，Worksheet_Change 就会触发一个事件，该事件用新的目标调用自身。

<强> Question

上述例程似乎运行良好，尽管我尽了最大/最差的努力，但我尚未破坏我的项目环境的稳定。那么，如果可以将重复周期控制为有限结果，那么故意在其自身之上运行 Worksheet_Change 有什么问题呢？

Answer 1

我认为递归触发更改事件的错误在于，Excel 只能维持一个非常小的调用堆栈。在 80 次调用时，它杀死了我的 Excel 实例。当我外包递归时，我至少进行了超过 1200 次调用，当然在某种程度上增加了冗余:

Option Explicit
Const RANGE_STR As String = "A10:F15, A19:F22, A26:F30"

Private Sub Worksheet_Change(ByVal target As Range)
    Application.EnableEvents = False
        Dim t As Range
        If Not Intersect(target, Range(RANGE_STR)) Is Nothing Then
            For Each t In Intersect(target, Range(RANGE_STR))
                makeChange t
            Next t
        End If
    Application.EnableEvents = True
End Sub

Sub makeChange(ByVal t As Range)
    If Not Intersect(t, Range(RANGE_STR)) Is Nothing Then
        If IsEmpty(t) Then
            t.Offset(0, 1).ClearContents
            makeChange t.Offset(0, 1)
        ElseIf Not IsNumeric(t) Then
            t.ClearContents
            makeChange t
        Else
            If t.Column > 1 Then
                If t <= t.Offset(0, -1) Or IsEmpty(t.Offset(0, -1)) Then
                    t.ClearContents
                    makeChange t
                Else
                    t.Offset(0, 1) = t + Application.RandBetween(1, 5)
                    makeChange t.Offset(0, 1)
                End If
            Else
                t.Offset(0, 1) = t + Application.RandBetween(1, 5)
                makeChange t.Offset(0, 1)
            End If
        End If
    End If
End Sub