故意在其自身之上运行Worksheet_Change [英] Running Worksheet_Change on top of itself intentionally

问题描述

_{通过有意向下滚动到 tldr 和 问题 .}

我有几行和几列带有值；例如A10:G15.在每一行中，紧靠任何单元格右侧的单元格的值取决于该单元格，直到涉及的列的范围为止.这样，任何单元格右边的一个单元格的值始终在数值上大于该单元格或空白(如果原始单元格为空白).

I have several rows and columns with values; e.g. A10:G15. In each row, the value of the cell immediately to the right of any cell is dependent on that cell up to the extents of the columns involved. In this manner, the value of a cell immediately to the right of any cell is always numerically larger than the cell or blank if the original cell is blank.

要保持这种依赖性，如果要清除A:F中某个单元格中的值，则要清除右侧的任何值；如果我向任何其他单元格中输入新值，则要向右侧的其余单元格中逐渐添加一个随机数A:F中的单元格.

To maintain this dependency, I want to clear any values to the right if I clear the value from a cell within A:F or progressively add a random number to the remaining cells to the right if I input a new value into any cell within A:F.

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

Sample data. The 7 in the top-left is 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

tldr

   ▪如果我清除D12，也应该清除E12:G12.
   ▪如果我在C14中键入一个新值，则D14:G14应该每个都收到一个新值，该值是
               随机，但大于先前的值.
   ▪我可能想清除或粘贴列中的多个值，并期望
    常规依次处理.
   我有几个不连续的区域(请参见下面的代码示例
中的Union'ed range)，并且希望使用

    ▪ If I clear D12, E12:G12 should also be cleared.
    ▪ If I type a new value into C14 then D14:G14 should each receive a new value which is
      random but larger than the previous value.
    ▪ I might want to clear or paste in several values in a column and would expect the
      routine to deal with each in turn.
    ▪ I have several of these non-contiguous regions (see Union'ed range in code sample
      below) and would prefer a DRY coding style.

代码

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会触发一个事件，该事件以新的Target调用自身.

This event driven Worksheet_Change deals with each cell that has changed but only modifies the cell directly to the right, not the remaining cells in that row. The job of maintaining the remaining cells is achieved by leaving event triggers active so that when that single cell to the right is modified, the Worksheet_Change triggers an event that calls itself with a new Target.

问题

Question

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

The above routine seems to run fine and I have yet to destabilize my project environment despite my best/worst efforts. So what's wrong with intentionally running a Worksheet_Change on top of itself if the reiteration cycles can be controlled to a finite result?

推荐答案

我认为递归触发变更事件的错误在于，这种方式Excel仅能维持很小的调用堆栈.在80时，它杀死了我的Excel实例.当我将递归外包时，我至少接到了1200个电话，当然在一定程度上增加了冗余:

I would argue that what is wrong with recursively triggering the change event is that this way Excel can only sustain a pretty tiny call stack. At 80 calls it killed my Excel instance. When I outsourced the recursion I at least got to a little over 1200 calls, of course adding redundancy to some extent:

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

这篇关于故意在其自身之上运行Worksheet_Change的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！