Excel зависает при запуске макроса

Question

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim i As Integer

    Set KeyCells = Range("H4:H100")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
        i = Range(Target.Address).Row
        If Cells(i, "L") = "" Then
            Cells(i, "H") = "In Progress"
        End If
    End If
End Sub

Мой Excel продолжает зависать после запуска скрипта. Кто-нибудь может помочь мне в этом?

Answer 1

Проблема проста. Вы проверяете некоторый диапазон для изменения. Если это изменилось, вы выполняете какое-то действие, которое изменяет ячейки в проверяемом диапазоне. Это приводит к событию изменения этого диапазона и ... вызывает метод. И снова и снова ...

Чтобы избежать этой проблемы, вы должны создать глобальный или статический флаг и игнорировать все вторичные вызовы:

Dim IsWorking As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If IsWorking Then Exit Sub         ' Exit if it is a 'slave' call
IsWorking = True                   ' Set 'main call is processed' flag
' perform actions you need
IsWorking = False                  ' Drop the flag, main call is finished
Exit Sub

Дополнительно: Target может быть многоклеточным диапазоном, а не одной ячейкой. Вы можете проверить пересечение для каждой строки в нем, по крайней мере - может быть, более чем одна ячейка должна быть изменена на значение "В процессе" ...

Дополнительно-2: Добавить Application.DoEvents для кода - он позволяет прервать выполнение кода с помощью Ctrl-C, если макрос «замирает».

Answer 2

При использовании обработчиков событий и внесении изменений, которые могут снова вызвать обработчик событий, рекомендуется отключить мониторинг событий, объявив:

Заявка.EnableEvents = False

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

Ваш код с отключенными и повторно активированными событиями:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim i As Integer

    Set KeyCells = Range("H4:H100")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
        i = Range(Target.Address).Row
        Application.EnableEvents = False
        If Cells(i, "L") = "" Then
            Cells(i, "H") = "In Progress"
        End If
        Application.EnableEvents = True
    End If
End Sub