Excel congelación en bucles después de unos segundos hasta que termine, incluso la ventana inmediata

Tengo un montón de códigos como:

Sub dataManage()
    fastMacrosENABLE
  
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    StartTime = Timer
  
    For myRow = 1 To 50000
        SecondsElapsed = Round(Timer - StartTime, 2)
        Debug.Print myRow; " -- "; SecondsElapsed
    
        For myColumn = 30 To 500
            'do some stuff...
        Next myColumn
    Next myRow

    fastMacrosDISABLE
End Sub

Donde fastMacrosENABLE optimizar el funcionamiento por:

With Application
    .Calculation = xlCalculationManual
    .DisplayAlerts = Not opt
    .DisplayStatusBar = Not opt
    .EnableAnimations = Not opt
    .EnableEvents = Not opt
    .ScreenUpdating = Not opt
End With
With Worksheet
    .EnableCalculation = Not opt
    .EnableFormatConditionsCalculation = Not opt
    .EnablePivotTable = Not opt
End With

macros funciona rápido (considerando todos los lazos) pero lo que me sorprende es que después de unos 5 a 10 segundos, sobresalir se congela hasta que la macro termine, y eso incluso la ventana inmediata. Cuando la macro termina todo viene a la normalidad, e incluso la ventana inmediata muestra la última como 200 líneas de registro

como puedes imaginar, ese Debug. Impresión es sólo para estimar el tiempo de funcionamiento, e ir a por un coffe, un paseo, o para dormir al día siguiente, je je

¿Es eso posible evitar ese congelamiento y tener la ventana inmediata reportando Debug todo el proceso?

Pregunta hecha hace 3 años, 5 meses, 0 días - Por bytebarde61c

3 Respuestas:

  • Para responder a su Q directa

    es posible evitar esa congelación

    Añadir un poco DoEvents a tu código. Uno dentro del bucle exterior pero fuera del bucle interior podría ser suficiente.

    Sub dataManage()
    fastMacrosENABLE
  
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    StartTime = Timer
  
    For myRow = 1 To 50000
        SecondsElapsed = Round(Timer - StartTime, 2)
        Debug.Print myRow; " -- "; SecondsElapsed
        DoEvents

        For myColumn = 30 To 500
            'do some stuff...
        Next myColumn
    Next myRow
    fastMacrosDISABLE
End Sub

    Tenga en cuenta que esto reducirá un poco su código.

    Dicho eso, tu código de "hacer algunas cosas" mejor ser bastante optimizado...

    Respondida el Dec 16, 2020 a las 09:35 - por algorithmalchemist

    Votos positivos: 0 | Votos negativos: 0

  • El problema que estás experimentando se debe a que al desactivar las actualizaciones visuales de Excel con fastMacrosENABLE, incluyendo la actualización de la ventana de inmediato, la ventana de la inmediata se congela y no muestra más información hasta que la macro haya terminado por completo.

    Para resolver este problema, puedes agregar una instrucción de DoEvents dentro del bucle de tu macro. La función DoEvents le dice a Excel que permita que los eventos del sistema, incluida la actualización de la ventana de inmediato, se procesen mientras la macro está en ejecución.

    Aquí te muestro cómo puedes ajustar tu macro para incluir DoEvents:

    vba
Sub dataManage()
    fastMacrosENABLE
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim myRow As Long, myColumn As Long

    StartTime = Timer

    For myRow = 1 To 50000
        SecondsElapsed = Round(Timer - StartTime, 2)
        Debug.Print myRow; " -- "; SecondsElapsed

        For myColumn = 30 To 500
            'do some stuff...
        Next myColumn

        DoEvents  ' Permite que los eventos del sistema se procesen
    Next myRow

    fastMacrosDISABLE
End Sub

    Al agregar DoEvents dentro del bucle, la ventana de la inmediata debería mostrar los resultados de Debug de manera continua mientras la macro se está ejecutando, evitando así el congelamiento que estás experimentando.

    Es importante tener en cuenta que el uso excesivo de DoEvents dentro de un bucle puede ralentizar la ejecución de la macro, por lo que es importante encontrar un equilibrio en su uso.

    Respondida el Dec 17, 2020 a las 09:38 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Sí, es posible evitar que Excel se congele y que la ventana inmediata siga informando sobre el progreso del bucle. Aquí tienes cómo hacerlo:

    1. Desactiva las actualizaciones de la pantalla:

    Excel actualiza automáticamente la pantalla cada vez que se modifica una celda. Esto puede ralentizar significativamente los bucles grandes. Puedes desactivar las actualizaciones de la pantalla con el siguiente código:

    
Application.ScreenUpdating = False

    2. Usa "DoEvents":

    El método DoEvents permite que Excel procese eventos pendientes, como la actualización de la ventana inmediata. Esto puede evitar que Excel se congele. Puedes utilizar DoEvents después de cada iteración del bucle:

    
DoEvents

    3. Utiliza la propiedad "ImmediatePane.EnableRefresh":

    La propiedad ImmediatePane.EnableRefresh controla si la ventana inmediata se actualiza automáticamente. Puedes establecerla en False para evitar que se actualice hasta que se complete el bucle:

    
Application.ImmediatePane.EnableRefresh = False

    4. Habilita las actualizaciones de la pantalla y la ventana inmediata después del bucle:

    Una vez que se complete el bucle, vuelve a habilitar las actualizaciones de la pantalla y la ventana inmediata:

    
Application.ScreenUpdating = True
Application.ImmediatePane.EnableRefresh = True

    Código modificado:

    Aquí tienes el código modificado con estas optimizaciones:

    
Sub dataManage()
    fastMacrosENABLE

    Dim StartTime As Double
    Dim SecondsElapsed As Double
    StartTime = Timer

    Application.ScreenUpdating = False
    Application.ImmediatePane.EnableRefresh = False

    For myRow = 1 To 50000
        SecondsElapsed = Round(Timer - StartTime, 2)
        Debug.Print myRow; " -- "; SecondsElapsed

        For myColumn = 30 To 500
            'do some stuff...
        Next myColumn

        DoEvents
    Next myRow

    Application.ScreenUpdating = True
    Application.ImmediatePane.EnableRefresh = True

    fastMacrosDISABLE
End Sub

    Con estas optimizaciones, Excel debería seguir siendo receptivo durante el bucle y la ventana inmediata debería mostrar el progreso del bucle en tiempo real.

    Respondida el Dec 17, 2020 a las 09:48 - por Gemini

    Votos positivos: 0 | Votos negativos: 0