¿Por qué MS Excel se bloquea y se cierra durante el procedimiento Worksheet_Change Sub?

Tengo un problema con el locking de Excel cuando ejecuto el código VBA en una hoja de Excel.
Estoy tratando de agregar la siguiente fórmula en el cambio de la hoja de trabajo:

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

Cuando se ejecuta este código, recibo un mensaje que dice ” Excel ha encontrado un problema y debe cerrarse ” y Excel se cierra.

enter image description here

Si ejecuto el código en el procedimiento Worksheet_Activate() , funciona bien y no falla

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

Pero realmente necesito que funcione en el procedimiento Worksheet_Change() .

¿Alguien ha experimentado lockings similares al usar el evento Worksheet_Change() y puede alguien apuntar en la dirección correcta para solucionar este problema?

Nota : He estado enviando personas a este enlace con bastante frecuencia ahora, por lo que haré de esto una publicación única para el Worksheet_Change de Worksheet_Change de Worksheet_Change . De vez en cuando, cuando tenga tiempo, agregaré nuevo contenido para que la gente se beneficie.


Siempre lo recomiendo cuando Worksheet_Change

  1. No necesitas el nombre de la hoja. Se entiende que el código se ejecutará en la hoja actual A MENOS que esté tratando de usar otra hoja como referencia. ¿Es “página de prueba” el nombre de la hoja de trabajo o es una hoja diferente?

  2. Siempre que trabaje con el evento Worksheet_Change . Siempre apague los eventos si está escribiendo datos en la celda. Esto es necesario para que el código no entre en un posible bucle sin fin

  3. Siempre que desconecte los eventos, use el manejo de errores. De lo contrario, si obtiene un error, el código no se ejecutará la próxima vez.

Prueba esto

 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 

Algunas otras cosas que tal vez desee saber al trabajar con este evento.

Si desea asegurarse de que el código no se ejecute cuando se cambia más de una celda, agregue un pequeño cheque

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

El CountLarge se introdujo en Excel 2007 en adelante porque Target.Cells.Count devuelve un valor Integer que se descarta en Excel 2007 debido a un aumento de filas / columnas. Target.Cells.CountLarge devuelve un valor 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 

Para trabajar con todas las celdas que se cambiaron, use este código

 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 

Para detectar cambios en una celda en particular, use Intersect . Por ejemplo, si ocurre un cambio en la celda A1 , entonces el siguiente código disparará

 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 

Para detectar cambios en un conjunto particular de rango, use Intersect nuevamente. Por ejemplo, si ocurre un cambio en el rango A1:A10 , entonces el siguiente código disparará

 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 

Excel estaba fallando, no la función VBA.
Los eventos no fueron deshabilitados y la stack de llamadas fue completada por un ciclo infinito de eventos OnChange.
Un pequeño consejo que ayuda a encontrar este tipo de errores: establezca un punto de interrupción en la primera línea del evento, luego ejecútelo paso a paso presionando F8.

También esta solución es buena:

 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