为什么MS Excel在Worksheet_Change Sub过程中崩溃并关闭? [英] Why MS Excel crashes and closes during Worksheet_Change Sub procedure?

问题描述

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

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:

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

运行此代码时，我收到一条消息，提示" excel遇到问题，需要关闭"，然后excel关闭.

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

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

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

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

但是我真的需要它在Worksheet_Change()过程中工作.

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的一站式帖子.每当我有时间的时候，我都会为此添加新的内容，以便人们可以从中受益.

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

1. 您不需要工作表名称.可以理解，该代码将在当前工作表除非要上运行，您正在尝试使用另一工作表作为参考. "testpage"是Activesheet名称还是其他工作表?

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

无论何时使用Worksheet_Change事件.如果要将数据写入单元格，请始终切换Off事件.这是必需的，以便使代码不会陷入可能的无限循环

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

每当您关闭事件时，请使用错误处理，否则如果您遇到错误，则该代码将不会在下次运行.

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

尝试

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

CountLarge是Excel 2007以后的版本中引入的，因为Target.Cells.Count返回的Integer值由于行/列增加而在Excel 2007中出错. Target.Cells.CountLarge返回Long值.

The CountLarge was introduced in Excel 2007 onward because Target.Cells.Count returns an Integer value which errors out in Excel 2007 becuase of increased rows/columns. Target.Cells.CountLarge returns a Long value.

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

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

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

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

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

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

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

这篇关于为什么MS Excel在Worksheet_Change Sub过程中崩溃并关闭?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！