vba代码运行时，MS Excel崩溃 [英] MS Excel crashes when vba code runs

问题描述

当我在excel表上运行VBA代码时，我遇到了一个excel崩溃的问题。
我有一些代码可以将公式添加到一系列单元格中，如下所示：

  Private Sub Worksheet_Change（ByVal Target作为范围）
工作表（testpage）。Range（A1：A8）。Formula == B1 + C1
 End Sub 
  pre> 
 
 
当运行此代码时，我会收到一条消息，说 excel遇到问题，需要关闭，而excel关闭。 p> 
 
 
如果我运行 worksheet_active（）类中的代码，它可以正常工作，不会崩溃
  Private Sub Worksheet_Activate（）
工作表（testpage）。Range（A1：A8）。Formula == B1 + C1
 End Sub 
  
但我真的需要它在 worksheet_change（） class。 
 
 
 
使用 worksheet_change（）类时，有人遇到类似的崩溃，任何人都可以指向正确的方向来修复这个问题？
解决方案
 注意：我现在一直把这个链接引用到这个链接，所以我将这个作为 Worksheet_Change 的一站式文章。每当我得到时间，我会添加新的内容，所以人们可以从中受益。
 
 
 
 
 
 
我一直推荐这个使用 Worksheet_Change  
 
 
 
你不需要表单名称。据了解，代码将在当前工作表 UNLESS 上运行，您正在尝试使用另一个工作表作为参考。 testpage是活动表名称还是不同的表？
 
 
 
 
每当你使用 Worksheet_Change 事件。如果要将数据写入单元格，请始终切换关闭事件。这是必需的，以便代码不会陷入可能的无尽循环
 
 
 
 
每当您关闭事件时，如果发生错误，请使用错误处理，代码将不会在下一次运行。
 
 
 
尝试这个
  Private Sub Worksheet_Change（ByVal Target As Range）
错误GoTo Whoa 
 
 Application.EnableEvents = False 
 
范围（A1：A8）。Formula == B1 + C1
 
 Letscontinue：
 Application.EnableEvents = True 
退出Sub 
哇：
 MsgBox Err.Description 
 Resume Letscontinue 
 End Sub 
  
其他几个当您使用此事件时，您可能想知道的事情。
 
 
 
如果要确保代码在多个单元格更改时不运行，请添加小支票
 
 
  Private Sub Worksheet_Change（ByVal Target As Range）
'~~>对于Excel 2003 
如果Target.Cells.Count> 1然后退出Sub 
 
'
'~~>其他代码
'
 End Sub 
  
  CountLarge 在Excel 2007中引入，因为 Target.Cells.Count 返回一个整数值Excel 2007中出现错误，因为增加了行/列。  Target.Cells.CountLarge 返回一个 Long 值。
  Private Sub Worksheet_Change（ByVal Target As Range）
'~~>对于Excel 2007 
如果Target.Cells.CountLarge> 1然后退出Sub 
'
'~~>其他代码
'
 End Sub 
  
要处理所有单元格被更改使用此代码
  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim aCell As Range 
 
对于每个aCell在Target.Cells 
与aCell 
'~~>做某事
结束
下一个
 End Sub 
  
要检测更改特定单元格，使用相交。例如，如果在Cell  A1 中发生更改，则以下代码将触发
  Private Sub Worksheet_Change（ByVal Target As Range）
如果不相交（目标，范围（A1））是否为
 MsgBox单元格A1已更改
'〜 〜>你的代码
如果
 End Sub 
  
检测特定的变化一组范围，再次使用相交。例如，如果范围 A1：A10 发生更改，则以下代码将触发
 $ code Private Sub Worksheet_Change（ByVal Target As Range）
如果不相交（目标，范围（A1：A10））不是
 MsgBoxA1中的单元格：A10范围是改变了
'~~>你的代码
如果
结束Sub 
  
 
I am having a problem with excel crashing when i run VBA code on an excel sheet.
I have some code to add a formula to a range of cells as follows:
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.

IF i run the code in the worksheet_active() class it works fine and doesn't crash
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() class. 

Has anyone experienced similar crashes when using the worksheet_change() class and can anyone point in the right direction to fix this issue ?
解决方案
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.



I always recommend this when using Worksheet_Change

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?
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.
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 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
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
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


                        
这篇关于vba代码运行时，MS Excel崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！