为什么 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

1. 您不需要需要工作表名称.在工作表代码模块中，非限定范围引用引用该工作表.也就是说，使用 Me 限定符更清晰.如果您尝试使用其他工作表，请使用该工作表限定范围引用.

1. You do not need the sheet name. In a Sheet Code Module, an unqualified Range reference refers to that sheet. That said, it is clearer to use the Me qualifier. If you are trying to use another sheet, then qualify the range reference with that sheet.

每当您使用 Worksheet_Change 事件时，如果要将数据写入任何单元格，请始终切换 Off 事件.这是必需的，以便代码不会重新触发 Change 事件，并进入可能的无限循环

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

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

Whenever you are switching off events, use error handling to turn it back on, 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
    
    Me.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 返回一个 Long 值，该值可能会在 Excel 2007 中出错，因为细胞总数增加.

The CountLarge was introduced in Excel 2007 onward because Target.Cells.Count returns an Long value which can error out in Excel 2007 becuase of increased total cells count.

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.例如，如果 Cell 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, Me.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, Me.Range("A1:A10")) Is Nothing Then
        MsgBox "one or more Cells in A1:A10 range was changed"
        '~~> Your code here
    End If
End Sub

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