10 maneras de acelerar sus macros

  1. Software
  2. Oficina de Microsoft
  3. Excel
  4. 10 maneras de acelerar sus macros

Libro Relacionado

Macros de Excel para tontos

Por Michael Alexander

A medida que sus macros de Excel se vuelven cada vez más robustas y complejas, es posible que pierda rendimiento. Cuando se habla de macros, la palabra rendimiento suele ser sinónimo de velocidad. La velocidad es la rapidez con la que sus procedimientos VBA realizan las tareas previstas. A continuación se presentan diez maneras de ayudar a mantener sus macros de Excel funcionando a su nivel óptimo de rendimiento.

Cálculos de la hoja de detención

¿Sabía usted que cada vez que una celda que afecta cualquier fórmula en su hoja de cálculo es cambiada o manipulada, Excel recalcula la hoja de cálculo completa? En hojas de trabajo que tienen una gran cantidad de fórmulas, este comportamiento puede ralentizar drásticamente sus macros.

Puede utilizar la propiedad Cálculo de aplicación para indicar a Excel que cambie al modo de cálculo manual. Cuando un libro de trabajo está en modo de cálculo manual, el libro de trabajo no se recalcula hasta que se lanza explícitamente un cálculo pulsando la tecla F9.

Coloque Excel en el modo de cálculo manual, ejecute su código y luego vuelva al modo de cálculo automático.

Sub Macro1()Application.Calculation = xlCalculationManual 'Coloque su código de macro aquíApplication.Calculation = xlCalculationAutomaticEnd Sub

Ajustar el modo de cálculo de nuevo a xlCalculationAutomatic activará automáticamente un nuevo cálculo de la hoja de cálculo, por lo que no es necesario pulsar la tecla F9 después de la ejecución de la macro.

Desactivar la actualización de la pantalla de hojas

Usted puede notar que cuando sus macros se ejecutan, su pantalla parpadea bastante. Este parpadeo es Excel tratando de redibujar la pantalla para mostrar el estado actual de la hoja de trabajo. Desafortunadamente, cada vez que Excel redibuja la pantalla, ocupa recursos de memoria.

Puede utilizar la propiedad Application.ScreenUpdating para desactivar las actualizaciones de pantalla hasta que la macro haya finalizado. Desactivar la actualización de la pantalla ahorra tiempo y recursos, permitiendo que su macro se ejecute un poco más rápido. Después de que su código de macro haya terminado de ejecutarse, puede volver a activar la actualización de la pantalla.

Sub Macro1()Application.Calculation = xlCalculationManualApplication.ScreenUpdating = False 'Ponga su código de macro aquíApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueEnd Sub.

Después de volver a establecer la propiedad ScreenUpdating en True, Excel lanzará automáticamente un redibujo de la pantalla.

Desactivar las actualizaciones de la barra de estado

La barra de estado de Excel, que aparece en la parte inferior de la ventana de Excel, muestra normalmente el progreso de ciertas acciones en Excel. Si su macro está trabajando con muchos datos, la barra de estado ocupará algunos recursos.

Es importante tener en cuenta que la desactivación de la actualización de la pantalla es independiente de la desactivación de la visualización de la barra de estado. La barra de estado seguirá actualizándose incluso si desactiva la actualización de pantalla. Puede utilizar la propiedad Application.DisplayStatusBar para desactivar temporalmente cualquier actualización de la barra de estado, mejorando aún más el rendimiento de su macro:

Sub Macro1()Cálculo de la aplicación = xlCálculoManualAplicación.ScreenUpdating = FalseApplication.DisplayStatusBar = False 'Coloque su código de macro aquíApplication.Calculation = xlCálculoAutomáticoApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueEnd Subplication

Diciendo a Excel que ignore los eventos

Puede implementar macros como procedimientos de evento, diciéndole a Excel que ejecute cierto código cuando cambie una hoja de trabajo o un libro de trabajo.

A veces, las macros estándar realizan cambios que desencadenan un procedimiento de evento. Por ejemplo, si tiene una macro estándar que manipula varias celdas en la Hoja 1, cada vez que se cambia una celda en esa hoja, su macro tiene que hacer una pausa mientras se ejecuta el evento Worksheet_Change.

Puede añadir otro nivel de aumento de rendimiento utilizando la propiedad EnableEvents para indicar a Excel que ignore los eventos mientras se ejecuta la macro.

Establezca la propiedad EnableEvents en False antes de ejecutar su macro. Después de que su código de macro haya terminado de ejecutarse, puede volver a establecer la propiedad EnableEvents en True.

SubMacro1()Cálculo de la aplicación = xlCálculoManualAplicación.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = False 'Ponga su código de macro aquíAplicación.Cálculo = xlCálculoAplicaciónAutomática.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueEnd Subplication

Ocultar saltos de página

Cada vez que su macro modifica el número de filas, modifica el número de columnas o altera la configuración de la página de una hoja de trabajo, Excel se ve obligado a tomarse su tiempo para recalcular los saltos de página mostrados en la hoja.

Puede evitar este comportamiento simplemente ocultando los saltos de página antes de iniciar su macro.

Establezca la propiedad DisplayPageBreaks sheet en False para ocultar los saltos de página. Si desea continuar mostrando saltos de página después de la ejecución de la macro, vuelva a establecer la propiedad DisplayPageBreaks en True.

Sub Macro1()Cálculo de la aplicación = xlCálculoManualAplicación.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseActivesheet.DisplayPageBreaks = False 'Coloque su código de macro aquíApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueActivesheet.DisplayPageBreaks = TrueEnd Sub

Suspensión de actualizaciones de la tabla pivotante

Si su macro manipula tablas pivotantes que contienen grandes fuentes de datos, es posible que experimente un rendimiento deficiente cuando haga cosas como añadir o mover dinámicamente campos pivotantes.

Puede mejorar el rendimiento de su macro suspendiendo el recálculo de la tabla pivotante hasta que se hayan realizado todos los cambios en el campo pivotante. Simplemente establezca la propiedad PivotTable.ManualUpdate en True para diferir el recálculo, ejecute su código de macro y, a continuación, vuelva a establecer la propiedad PivotTable.ManualUpdate en False para activar el recálculo.

Sub Macro1()ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True 'Ponga su código de macro aquíActiveSheet.PivotTables("PivotTable1").ManualUpdate=FalseEnd Sub

Alejarse de copiar y pegar

Es importante recordar que aunque Macro Recorder ahorra tiempo escribiendo código VBA para usted, no siempre escribe el código más eficiente. Un buen ejemplo es cómo Macro Recorder captura cualquier acción de copiar y pegar que realice durante la grabación.

Puede dar a sus macros un ligero impulso eliminando al intermediario y realizando una copia directa de una celda a otra de destino. Este código alternativo usa el argumento Destino para pasar por alto el portapapeles y copiar el contenido de la celda A1 directamente a la celda B1.

Rango ("A1") Destino de la copia:=Rango ("B1")

Si necesita copiar sólo valores (sin formato ni fórmulas), puede mejorar aún más el rendimiento evitando el método Copiar todos juntos. Simplemente establezca el valor de la celda de destino al mismo valor que se encuentra en la celda fuente. Este método es aproximadamente 25 veces más rápido que el método Copy:

Rango ("B1") Valor = Rango ("A1") Valor

Si necesita copiar sólo fórmulas de una celda a otra (no valores o formato), puede fijar la fórmula de la celda de destino en la misma fórmula contenida en la celda fuente:

Rango ("B1").Fórmula = Rango ("A1").Fórmula

Uso de la sentencia With

Al grabar macros, a menudo manipulará el mismo objeto más de una vez. Puede ahorrar tiempo y mejorar el rendimiento utilizando la sentencia With para realizar varias acciones sobre un objeto dado de una sola vez.

La sentencia Con utilizada en el siguiente ejemplo le dice a Excel que aplique todos los cambios de formato a la vez:

Con Range("A1").Font.Bold = True.Italic = True.Underline = xlUnderlineStyleSingle End With

Adquirir el hábito de reagrupar acciones en Con sentencias no sólo hará que sus macros funcionen más rápido, sino que también facilitará la lectura de su código de macros.

Evitar el método Select

Macro Recorder es aficionado a utilizar el método Select para seleccionar objetos explícitamente antes de realizar acciones sobre ellos. Generalmente no hay necesidad de seleccionar objetos antes de trabajar con ellos. De hecho, puede mejorar drásticamente el rendimiento de las macros al no utilizar el método Select.

Después de grabar sus macros, haga un hábito de alterar el código generado para eliminar los métodos de selección. En este caso, el código optimizado tendría el siguiente aspecto:

Hojas ("Hoja1").Rango ("A1").FórmulaR1C1 = "1000" Hojas ("Hoja2").Rango ("A1").FórmulaR1C1 = "1000" Hojas("Hoja3").Rango("A1").FórmulaR1C1 = "1000")

Tenga en cuenta que no se está seleccionando nada. El código utiliza simplemente la jerarquía de objetos para aplicar las acciones necesarias.

Limitar los viajes a la hoja de trabajo

Otra manera de acelerar sus macros es limitar la cantidad de veces que usted hace referencia a los datos de la hoja de trabajo en su código. Siempre es menos eficiente tomar datos de la hoja de trabajo que de la memoria. Es decir, sus macros se ejecutarán mucho más rápido si no tienen que interactuar repetidamente con la hoja de trabajo.

Por ejemplo, el siguiente código simple obliga a VBA a regresar continuamente a Hojas (“Hoja 1”), Rango (“A1”) para obtener el número necesario para la comparación que se está realizando en la sentencia If:

Para ReportMonth = 1 a 12 Si Rango ("A1") Valor = ReportMonth Then MsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Un método mucho más eficiente es guardar el valor en Hojas (“Hoja1”), Rango (“A1”) en una variable llamada MiMes. De esta manera, el código hace referencia a la variable MyMonth en lugar de a la hoja de trabajo:

ValueFor ReportMonth = 1 a 12 Si MyMonth = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Considere la posibilidad de aprovechar las variables para trabajar con datos en memoria en lugar de hacer referencia directa a las hojas de trabajo.

Leave a Reply

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *