O Excel congela ao executar a macro

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

Meu excel continua congelando depois de executar o script. Alguém pode me ajudar nisso?

    

Answer 1

Ao usar manipuladores de eventos e fazer alterações que poderiam acionar o manipulador de eventos novamente, é recomendável desabilitar o monitoramento de eventos declarando:

Application.EnableEvents = False

Lembre-se de reativar os eventos antes que o procedimento seja encerrado. Caso contrário, eles permanecerão desativados até você reiniciar o Excel.

Seu código com eventos desativados e reativados:

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

    

Answer 2

O problema é simples. Você verifica algum intervalo para uma mudança. Se for alterado, você executará alguma ação, que altera as células no intervalo verificado. Isso leva a um evento de mudança desse intervalo e ... chama o método. E de novo e de novo ...

Para evitar esse problema, você deve criar um sinalizador global ou estático e ignorar todas as chamadas secundárias:

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

Além disso, o Target pode ser um intervalo multicelular, não uma única célula. Você pode verificar a interseção para cada linha no mínimo - talvez mais de uma célula seja alterada para o valor "Em andamento" ...

Adicionalmente-2: Adicione Application.DoEvents declaração (s) ao código - ele permite quebrar a execução do código por Ctrl-C se a macro 'freeses'.