堆栈空间不足的'28'.Worksheet_Change和Application.ScreenUpdating [英] '28' Out of Stack Space. Worksheet_Change and Application.ScreenUpdating

问题描述

感谢您提供的清晰度.

在具有许多模块和工作表的Excel工作簿中，SHEET2的VBA代码底部有以下子例程:

In an Excel Workbook with many modules and worksheets, at the bottom of the VBA code for SHEET2, there is this Subroutine:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TargetCells As Range

Set TargetCells = Range("B1000:B1029")

If Not Application.Intersect(TargetCells, Range(Target.Address)) Is Nothing Then
   Call SpecificSubRoutine
End If

End Sub

我对这段代码的理解是，它会监视整个工作表中是否有任何更改.如果更改了任何内容，则它将在工作表上的任何位置运行If语句.如果对工作表所做的任何更改超出指定的 TargetCells 范围，If语句都会失败，但是此Sub仍会尝试在工作表上每次更改任何内容时都验证If语句

My understanding of this code is that it watches the entire sheet for ANY changes. If ANYTHING is changed, anywhere on the sheet, it runs the If statement. The If statement fails in the event that any of the changes made to the sheet take place outside of the specified TargetCells range, but this Sub still tries to validate the If statement EVERY time ANYTHING is changed on the sheet.

现在，您也许可以猜到我的问题是堆栈溢出.(运行时错误"28":堆栈空间不足)

Now, you might be able to guess that my problem is some stack overflow. (Run-time error '28': Out of Stack Space)

每当运行 Worksheet_Change 子程序时，如果对工作表所做的更改是在 TargetCells 范围内进行的，它将调用 SpecificSubRoutine 来填充单元格，每次 SpecificSubRoutine 填充ANY单元格时，都会触发 Worksheet_Change 子.( SpecificSubRoutine 还会调用不同的模块，这些模块当然会填充单元格，当然会触发 Worksheet_Change 子级)

Whenever the Worksheet_Change Sub runs, if the changes to the sheet were made inside of the TargetCells range, it calls SpecificSubRoutine which populates cells, which triggers the Worksheet_Change Sub for every time SpecificSubRoutine populates ANY cell. (SpecificSubRoutine also calls different modules, which of course populate cells, which of course trigger the Worksheet_Change Sub)

不太好.

此外，整个应用程序中的大多数子例程都包装在 Application.ScreenUpdating = False / Application.ScreenUpdating = True 中，我错误地认为这会限制 Application.ScreenUpdating = True 运行后立即调用一次 Worksheet_Change .

Also, most of the subroutines throughout the application are wrapped in Application.ScreenUpdating = False / Application.ScreenUpdating = True, which I mistakenly thought would limit the number of times Worksheet_Change is called to once, immediately after Application.ScreenUpdating = True runs.

要点: SpecificSubRoutine 或它所调用的任何子例程都不会填充 TargetCells 范围内的单元格.我不是那么昏暗...

NOTE OF IMPORTANCE: Neither SpecificSubRoutine nor any of the Subroutines called by it populate cells in the TargetCells range. I'm not quite that dim...

这是我的问题:

1. 是否有办法缩小触发 Worksheet_Change 子对象的范围，以便仅在 TargetCells 范围内进行更改才能触发子对象?(而不是工作表中任何位置的更改)
2. 有没有办法做我错误地认为 Application.ScreenUpdating 会做的事情?(一次完成对工作表的更改，而不是几乎每个步骤都触发一次更改)
3. 出于好奇，还有一种方法可以让 Worksheet_Change 监视2个特定范围(而不是整个工作表?)知道如何做到这一点将是至高无上的，并且可能会解决所有问题.这张纸上的问题.

1. Is there a way to narrow the scope of what triggers the Worksheet_Change Sub, so that only changes in the TargetCells range triggers it? (instead of changes anywhere in the sheet)
2. Is there a way to do what I mistakenly thought that Application.ScreenUpdating would do? (make changes to the sheet all in one bulk update, as opposed to triggering a change with nearly every step)
3. Also, as an extra curiosity, is there a way to have Worksheet_Change watch 2 specific ranges (instead of the whole sheet?) Knowing how to do this would be paramount, and would likely solve all of the problems on this sheet.

我的直觉是将 End 添加到 SpecificSubRoutine 的最后部分，或添加到它所调用的任何/所有子例程的末尾，但是只是不确定是否会绕过 Worksheet_Change 多次循环，因为 Application.ScreenUpdating 不会像我想的那样批量更新.

My intuition is to add an End to the last part of SpecificSubRoutine, or to the end of any/all of the Subroutines called by it, but I'm just not sure this will circumvent the looping through Worksheet_Change multiple times, since Application.ScreenUpdating doesn't bulk update like I thought.

想法?

推荐答案

第1部分:否-事件处理程序会响应工作表上的所有更改:对更改的响应方式中的任何过滤都必须在处理程序本身中进行.

Part 1: No - the event handler responds to all changes on the sheet: any filtering in how you respond to that change must occur in the handler itself.

第2部分:@simoco回答

Part 2: answered by @simoco

第3部分(并结合了simoco的建议):

Part 3 (and incorporating simoco's suggestion):

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents=False

    If Not Application.Intersect(Me.Range("B1000:B1029"), Target) Is Nothing Then
       Call SpecificSubRoutine
    End If

    If Not Application.Intersect(Me.Range("D1000:D1029"), Target) Is Nothing Then
       Call SomeOtherSpecificSubRoutine
    End If

    Application.EnableEvents=True

End Sub

这篇关于堆栈空间不足的'28'.Worksheet_Change和Application.ScreenUpdating的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！