vba 为什么 MS Excel 在 Worksheet_Change Sub 过程中崩溃并关闭？

Why MS Excel crashes and closes during Worksheet_Change Sub procedure?

提问by derek

I am having a problem with Excel crashing, when I run VBA code on an excel sheet.
I'm trying to add the following formula on worksheet change:

当我在 Excel 工作表上运行 VBA 代码时，我遇到了 Excel 崩溃的问题。
我正在尝试在工作表更改中添加以下公式：

Private Sub Worksheet_Change(ByVal Target As Range)
   Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1"
End Sub

When this code is run i get a message saying "excel has encountered a problem and needs to close" and excel closes.

运行此代码时，我收到一条消息，提示“ excel 遇到问题，需要关闭”并且 excel 关闭。

If I run the code in the Worksheet_Activate()procedure, it works fine and doesn't crash

如果我在Worksheet_Activate()程序中运行代码，它工作正常并且不会崩溃

Private Sub Worksheet_Activate()
   Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1"
End Sub

But I really need it to work in the Worksheet_Change()procedure.

但我真的需要它在Worksheet_Change()程序中工作。

Has anyone experienced similar crashes when using the Worksheet_Change()event and can anyone point in the right direction to fix this issue ?

有没有人在使用Worksheet_Change()事件时遇到过类似的崩溃，任何人都可以指出正确的方向来解决这个问题？

回答by Siddharth Rout

Note: I have been referring people to this link quite often now so I will make this a one stop post for Worksheet_Change. Every now and then, when I get the time, I will add new content to this so people can benefit for it.

注意：我现在经常向人们推荐此链接，因此我会将其设为Worksheet_Change. 时不时地，当我有时间时，我会为此添加新内容，以便人们可以从中受益。

---

---

I always recommend this when using Worksheet_Change

使用时我总是推荐这个 Worksheet_Change

1. You do not need the sheet name. It is understood that the code is to be run on current sheet UNLESSyou are trying to use another sheet as a reference. Is "testpage" the Activesheet name or is it a different sheet?

2. Whenever you are working with Worksheet_Changeevent. Always switch Offevents if you are writing data to the cell. This is required so that the code doesn't go into a possible endless loop

3. Whenever you are switching off events, use error handling else if you get an error, the code will not run the next time.

1. 您不需要工作表名称。据了解，代码将在当前工作表上运行，除非您尝试使用另一张工作表作为参考。“testpage”是 Activesheet 名称还是不同的工作表？

2. 每当您处理Worksheet_Change事件时。Off如果您将数据写入单元格，请始终切换事件。这是必需的，以便代码不会进入可能的无限循环

3. 每当您关闭事件时，请使用错误处理，否则如果出现错误，代码下次将不会运行。

Try this

尝试这个

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    Range("A1:A8").Formula = "=B1+C1"

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Few other things that you may want to know when working with this event.

在处理此事件时，您可能想知道的其他几件事。

If you want to ensure that the code doesn't run when more than one cell is changed then add a small check

如果要确保在更改多个单元格时代码不会运行，请添加一个小检查

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2003
    If Target.Cells.Count > 1 Then Exit Sub

    '
    '~~> Rest of code
    '
End Sub

The CountLargewas introduced in Excel 2007 onward because Target.Cells.Countreturns an Integervalue which errors out in Excel 2007 becuase of increased rows/columns. Target.Cells.CountLargereturns a Longvalue.

将CountLarge在Excel 2007年推出以后，因为Target.Cells.Count返回一个Integer值误差出在Excel 2007中增加监守行/列。Target.Cells.CountLarge返回一个Long值。

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2007
    If Target.Cells.CountLarge > 1 Then Exit Sub
    '
    '~~> Rest of code
    '
End Sub

To work with all the cells that were changed use this code

要处理所有更改的单元格，请使用此代码

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

    For Each aCell In Target.Cells
        With aCell
            '~~> Do Something
        End With
    Next
End Sub

To detect change in a particular cell, use Intersect. For example, if a change happens in Cell A1, then the below code will fire

要检测特定单元格的变化，请使用Intersect。例如，如果 Cell 发生更改A1，则将触发以下代码

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        MsgBox "Cell A1 was changed"
        '~~> Your code here
    End If
End Sub

To detect change in a particular set of range, use Intersectagain. For example, if a change happens in range A1:A10, then the below code will fire

要检测一组特定范围的变化，请Intersect再次使用。例如，如果 range 发生更改A1:A10，则将触发以下代码

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        MsgBox "Cell in A1:A10 range was changed"
        '~~> Your code here
    End If
End Sub

回答by stenci

Excel was crashing, not the VBA function.
The events were not disabled and the call stack was filled by an infinite loop of OnChange events.
A little advice that helps finding this type of errors: set a breakpoint on the first line of the event, then execute it step by step pressing F8.

Excel 崩溃了，而不是 VBA 函数。
事件未被禁用，调用堆栈由 OnChange 事件的无限循环填充。
一个有助于发现此类错误的小建议：在事件的第一行设置一个断点，然后按 F8 逐步执行它。

回答by Mario Palumbo

Also this solution is good:

这个解决方案也很好：

Option Explicit
Private Busy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Busy Then
        Busy = True
        Range("A1:A8").Formula = "=B1+C1"
        Busy = False
    End If
End Sub