10 maneras de acelerar sus macros

Como las macros de Excel se vuelven cada vez más robusta y compleja, es posible que pierden rendimiento. Cuando se habla de macros, la palabra actuación

suele ser sinónimo de velocidad. La velocidad es la rapidez de sus procedimientos de VBA realizan sus tareas previstas. Los siguientes son diez maneras de ayudar a mantener los macros de Excel se ejecuta en su nivel óptimo rendimiento.

Cálculos hoja vacilantes

¿Sabía usted que cada vez que una célula que afecta a cualquier fórmula en la hoja de cálculo se cambia o manipulado, Excel vuelve a calcular toda la hoja de cálculo? En las hojas de trabajo que tienen una gran cantidad de fórmulas, este comportamiento puede ralentizar drásticamente sus macros.

Puede utilizar la propiedad Application.Calculation para decirle Excel para cambiar al modo de cálculo manual. Cuando un libro está en el modo de cálculo manual, el libro no va a volver a calcular hasta que se activa de forma explícita un cálculo pulsando la tecla F9.

Lugar de Excel en el modo de cálculo manual, ejecute el código, y luego volver al modo de cálculo automático.

Sub Macro1 () Application.Calculation = xlCalculationManual'Place su código de macro hereApplication.Calculation = xlCalculationAutomaticEnd Sub

Ajuste el modo de cálculo de nuevo a xlCalculationAutomatic se activará automáticamente un nuevo cálculo de la hoja de trabajo, lo que no hay necesidad de presionar la tecla F9 después de sus macro se ejecuta.

Hoja de desactivación de pantalla de actualización

Usted puede notar que cuando sus macros se ejecutan, la pantalla hace una buena cantidad de parpadeo. Este parpadeo se Excel tratando de volver a dibujar la pantalla para mostrar el estado actual de la hoja de trabajo. Por desgracia, cada vez que Excel vuelve a dibujar la pantalla, ocupa recursos de memoria.

Puede utilizar la propiedad Application.ScreenUpdating desactivar las actualizaciones de pantalla hasta que el macro ha completado. Desactivación de la actualización de pantalla ahorra tiempo y recursos, lo que permite su macro para ejecutar un poco más rápido. Después de que su código de macro termine de ejecutarse, puede activar la pantalla actualizar de nuevo.

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

Después de establecer la propiedad ScreenUpdating volver a True, Excel activará automáticamente un redibujado de la pantalla.

Desactivación de actualizaciones barra de estado

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

Es importante señalar que apagar la actualización de pantalla es independiente de apagar la pantalla la barra de estado. La barra de estado se seguirá actualizando incluso si se desactiva la actualización de pantalla. Puede utilizar la propiedad Application.DisplayStatusBar para desactivar temporalmente las actualizaciones barra de estado, lo que mejora aún más el rendimiento de la macro:

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = False'Place su código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueEnd Sub

Decirle a Excel para ignorar eventos

Puede implementar macros como procedimientos de evento, contando Excel para ejecutar cierto código cuando una hoja de cálculo o libro cambios.

A veces, las macros estándar hacen cambios que activarán un procedimiento de evento. Por ejemplo, si usted tiene una macro estándar que manipula varias celdas en Hoja1, cada vez que una célula en esa hoja se cambia, la macro tiene que hacer una pausa mientras el evento Worksheet_Change ejecuta.

Puede añadir otro nivel de rendimiento de impulsar mediante la propiedad EnableEvents para decirle Excel ignorar eventos mientras se ejecuta la macro.

Establezca la propiedad EnableEvents False antes de ejecutar la macro. Una vez finalizada su código de macro se ejecuta, puede establecer la propiedad EnableEvents volver a True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = False'Place su código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueEnd Sub

Esconder los 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 página de una hoja de cálculo de Excel se ve obligado a tomar tiempo volver a calcular los saltos de página que aparecen en la hoja.

Usted puede evitar este comportamiento simplemente ocultar los saltos de página antes de iniciar la macro.

Establezca la propiedad DisplayPageBreaks hoja en False para ocultar los saltos de página. Si desea seguir mostrando los saltos de página después de sus carreras macro, establezca la propiedad DisplayPageBreaks hoja posterior a True.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseActivesheet.DisplayPageBreaks = False'Place su código de macro hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueActivesheet.DisplayPageBreaks = TrueEnd Sub

La suspensión de actualizaciones de la tabla pivote

Si sus macro manipula pivotan tablas que contienen las fuentes de datos de gran tamaño, puede experimentar un rendimiento deficiente al hacer cosas como agregar dinámicamente o mover campos de pivote.

Usted puede mejorar el rendimiento de su macro suspendiendo el nuevo cálculo de la tabla dinámica hasta que se hayan realizado todos los cambios en el campo de pivote. Basta con establecer la propiedad PivotTable.ManualUpdate True aplazar recálculo, ejecute el código de macro y, a continuación, establezca la propiedad PivotTable.ManualUpdate volver a False para desencadenar el recálculo.

Sub Macro1 () ActiveSheet.PivotTables ("PivotTable1"). ManualUpdate = True'Place sus hereActiveSheet.PivotTables código de macro ("PivotTable1"). ManualUpdate = FalseEnd Sub

Alejado de copiar y pegar

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

Usted puede dar a sus macros un ligero impulso al cortar el intermediario y la realización de una copia directa de una celda a una celda de destino. Este código alternativo utiliza el argumento de destino para evitar el portapapeles y copiar el contenido de la celda A1 directamente a la celda B1.

. Range ("A1") Copia Destino: = Range ("B1")

Si necesita copiar sólo los valores (no el formato o fórmulas), puede mejorar el rendimiento aún más, evitando el método Copiar todos juntos. Basta con establecer el valor de la celda de destino en el mismo valor que se encuentra en la celda de origen. Este método es de unos aproximadamente 25 veces más rápido que con el método de copia:

Range ("B1"). Value = Range ("A1") Valor.

Si necesita copiar sólo las fórmulas de una célula a otra (no valores o formato), se puede establecer la fórmula de la celda de destino a la misma fórmula contenida en la celda de origen:

Range ("B1"). Formula = Range ("A1"). Fórmula

Utilización de la sentencia Con

Al grabar macros, a menudo manipular el mismo objeto más de una vez. Usted puede ahorrar tiempo y mejorar el rendimiento mediante la instrucción Con para realizar diversas acciones en un objeto determinado en una sola toma.

La declaración Con utilizado en el ejemplo siguiente le dice a Excel para aplicar todos los cambios de formato de una sola vez:

Con Range ("A1"). Font.BOLD = True.Italic = True.Underline = xlUnderlineStyleSingleEnd Con

Entrar en el hábito de la fragmentación en acciones Con declaraciones no sólo mantendrá sus macros correr más rápido, pero también hacen que sea más fácil de leer el código de macro.

Evitar el método Select

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

Después de grabar macros, lo convierten en un hábito para alterar el código generado para eliminar los métodos Select. En este caso, el código optimizado vería como el siguiente:

Sheets ("Sheet1"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Hoja2"). Range ("A1"). FormulaR1C1 = "1000" Hojas ("Sheet3"). Range ("A1" ) .FormulaR1C1 = "1000"

Tenga en cuenta que la nada se está seleccionando. El código simplemente utiliza 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 se hace referencia a los datos de hoja de cálculo en el código. Siempre es menos eficiente para tomar los datos de la hoja de cálculo que de memoria. Es decir, las macros se ejecutarán mucho más rápidamente si no tienen que interactuar en varias ocasiones con la hoja de trabajo.

Por ejemplo, el siguiente código simple obliga VBA para volver continuamente a Sheets ("Sheet1") Range ("A1") para conseguir el número necesario para la comparación se realiza en la instrucción If.:

Para ReportMonth = 1 Para 12Si Range ("A1"). Value = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Un método mucho más eficiente es para guardar el valor en Sheets ("Sheet1"). Range ("A1") a una variable llamada MyMonth. De esta manera, el código hace referencia a la variable MyMonth lugar de la hoja de trabajo:

Dim MyMonth como IntegerMyMonth = Range ("A1"). Valuefor ReportMonth = 1 Para 12Si MyMonth = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Considere la posibilidad de aprovechamiento de las variables para trabajar con datos en la memoria en lugar de hacer referencia directamente a las hojas de cálculo.