Почему MS Excel вылетает и закрывается во время Sub-процедуры Worksheet_Change?

У меня проблема с аварийным завершением работы Excel, когда я запускаю код VBA на листе Excel.
Я пытаюсь добавить следующую формулу при изменении листа:

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

Когда этот код запускается, я получаю сообщение " Excel столкнулся с проблемой и должен закрыться " и Excel закрывается.

Если я запускаю код в процедуре Worksheet_Activate(), он работает нормально и не вылетает

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

Но мне действительно нужно, чтобы он работал в процедуре Worksheet_Change().

Кто-нибудь сталкивался с подобными сбоями при использовании события Worksheet_Change() и может ли кто-нибудь указать правильное направление, чтобы исправить эту проблему?

Ответ 1

Примечание. Я часто ссылался на людей по этой ссылке, поэтому сделаю это одностановочным сообщением для Worksheet_Change. Время от времени, когда я получаю время, я добавлю новый контент, чтобы люди могли извлечь из этого выгоду.

Я всегда рекомендую это при использовании Worksheet_Change

Вам не нужно имя листа. Понятно, что код должен быть запущен на текущем листе UNLESS, который вы пытаетесь использовать в качестве ссылки другой лист. Является ли "testpage" названием Activesheet или это другой лист?
Всякий раз, когда вы работаете с событием Worksheet_Change. Всегда переключайте события Off, если вы записываете данные в ячейку. Это необходимо, чтобы код не попадал в возможный бесконечный цикл
Всякий раз, когда вы выключаете события, используйте ошибку, иначе, если вы получите ошибку, код не будет запускаться в следующий раз.

Попробуйте это

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

Несколько других вещей, которые вы можете знать при работе с этим событием.

Если вы хотите убедиться, что код не запускается при изменении нескольких ячеек, добавьте небольшую проверку

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.

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

Чтобы работать со всеми ячейками, которые были изменены, используйте этот код

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 произойдут изменения, тогда код ниже будет срабатывать

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, тогда в приведенном ниже коде будет срабатывать

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

Ответ 2

Ошибка Excel, а не функция VBA.
События не были отключены, а стек вызовов был заполнен бесконечным циклом событий OnChange.
Небольшой совет, помогающий найти этот тип ошибок: установить точку останова в первой строке события, затем выполнить ее шаг за шагом, нажав F8.

Ответ 3

Также это решение хорошо:

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