Cómo optimizar la velocidad de nuestras macros de Excel (1 de 5)

Comenzamos con este artículo una serie de cinco que tendrán como hilo conductor un tema muy demandado por usuarios intermedios y avanzados de Excel. Se trata de la optimización de la velocidad de ejecución de nuestras macros (código VBA).

 

Cuando un usuario ya ha aprendido a grabar sus propias macros, se enfrenta a un siguiente paso: personalizar el código de las macros grabadas. Esta personalización irá encaminada en un principio a acomodar los procesos que ejecuta el código a las necesidades del propio usuario, añadiendo líneas de código que ejecuten acciones que normalmente no es capaz de grabar la grabadora de macros de Excel.

Pero también hay otra vertiente relativa a la personalización de macros: la optimización de la velocidad. Es decir, intentar que el código se ejecute de forma más rápida. Esto no tendrá mucha importancia cuando nuestras macros son muy sencillas y cortas, pero si en ellas se reflejan multitud de procesos que requieren muchos recursos de nuestro ordenador (activar hojas distintas, copiar y pegar mútiples veces, abrir y cerrar otros libros, realizar cálculos complejos, aplicar muchos formatos, etc...), será necesario aplicar algo de ingenio y conocimiento de ciertas técnicas para que el código se ejecute de forma fluida y rápida.

Comenzamos en esta primera entrega de una serie de varios artículos, con cinco trucos o técnicas que harán nuestro código más rápido:

1) Evitar que todas las acciones de la macro se vayan representando en la pantalla

Si dejamos que cada acción que ejecuta una macro se refleje en la pantalla, nuestro equipo tendrá que aplicar muchos recursos y tiempo al "refresco" o actualización de la pantalla. Para evitar esto utilizaremos la propiedad ScreenUpdating aplicada al objeto Application (Excel) en una línea de código al principio de nuestro código:

Por ejemplo:

Sub MiMacro ()

   Application.ScreenUpdating = False

   ....mi código

End sub ()

Con ello, establecemos la capacidad de Excel de refresco de panatalla como "False" (No). Además, hay que decir que no es necesario volver a cambiar en la última línea de la macro el valor a "True" (Sí), debido a que automáticamente lo hace Visual Basic cuando la macro (o suscesión de macros que se llaman entre sí) termina o cuando se produce un error.

2) Evitar que Excel recalcule todas las celdas de nuestro libro cuando no es necesario

En muchas macros (la mayoría), se ejecutan acciones que provocan que Excel haga un recálculo de todas las fórmulas que pudiera haber en el archivo (siempre que Excel esté configurado con "Cálculo automático"). Entre otras, esas acciones que provocan un recálculos son:

• Introducir o actualizar un dato (ya sea desde teclado o desde código VBA) en cualquier celda
• Eliminar o insertar filas
• Filtrar datos
• Hacer doble clic entre los títulos de una hoja para autoajustar el ancho o alto de columnas o filas, respectivamente
• Agregar, editar o eliminar un nombre de rango definido
• Cambiar el nombre de una hoja
• Cambiar la posición de una hoja (moverla)
• Ocultar o mostrar filas (no se produce recálculo si ocultamos o mostramos columnas)

También hay que decir que Excel realiza un recálculo inteligente. Es decir, solo recalcula las celdas con fórmulas que están afectadas por un cambio.

Para conseguir que nuestras macros no provoquen recálculos sin necesidad de que los haya, debemos insertar al principio de nuestro código la línea: Application.Calculation = xlCalculationManual

En estas condiciones, hasta que no volvamos a escribir una línea que reestablezca la situación original (Application.Calculation = xlCalculationAutomatic), Excel no recalcula nada. Por ejemplo, quedaría así:

Sub MiMacro ()

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual

   ....mi código

   Application.Calculation = xlCalculationAutomatic
End sub ()

Es posible que entre la línea de código que establece el recálculo a manual y la línea de código que lo devuelve a automático necesitemos que Excel calcule una celda o rango de celdas determinado. En este caso, podemos forzar un recálculo particular de ese rango, evitando que el resto del libro se actualice. Para lograr esto tenemos dos métodos:

Range.Calculate

Este método calcula el rango al que hacemos referencia y también calcula todas las celdas con fórmulas que dependan de dicho rango (aunque no pertenezcan a él).

Range.CalculateRowMajorOrder

Este método calcula el rango al que hacemos referencia, pero no calcula ninguna celda más (por lo tanto, es más rápido).

Es importante resaltar que es MUY CONVENIENTE escribir la última línea de código mencionada anteiormente (Application.Calculation = xlCalculationAutomatic), puesto que si no lo hiciéramos, nuestro Excel permanecerá sin recálculo automático aunque cerremos el archivo y abramos una nueva sesión con otro.

3) Evitar que se ejecute el código VBA que hemos escrito en eventos de hoja o de libro

Es posible que tengamos código dentro de algunas hojas de nuestro libro o incluso en el propio libro de Excel (ThisWorkbook). Este código responderá a ciertos eventos (según hayamos elegido al escribirlo).

Por ejemplo, si en una hoja tenemos un determinado código que se ejecuta con el evento Worksheet_Change (cuando ocurre un cambio en alguna celda), quiere decir que el código entrará en funcionamiento cuando modifiquemos alguna celda de esa hoja... pero claro, cuando la modifiquemos introduciendo un dato "manualmente" Y TAMBIÉN si una macro genera dicha modificación.

Por lo tanto, se nos presenta la necesidad de evitar que el código asociado a eventos se ejecute cuando no es necesario. Para ello, podemos utilizar la propiedad EnableEvents del objeto Application. El código podría quedar así:

Sub MiMacro ()

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False

   ....mi código

   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
End sub ()

Vemos cómo la propiedad EnableEvents tiene dos valores posibles (False y True) debiendo utilizar un línea de código al final de la macro para reestablecer el valor a "True", puesto que si no se hiciera, los eventos dejarían de funcionar hasta que Excel se reiniciara.

4) Evitar que Excel muestre los saltos de página después de algunas acciones

Excel se ve obligado a mostrar los saltos de página después de las siguientes acciones:

• Al mostrar una vista previa de impresión
• Al hacer mostrar el cuadro de diálogo "Configuración de página"
• Al modificar mediante VBA cualquier configuración de página
• Al configurar la opción "Mostrar saltos de página" desde Archivo/Opciones/Avanzadas

Para evitar que se muestren estas líneas discontinuas que representan a los saltos o cambios de página en una hipotética impresión, podemos hacerlo manualmente, pero también mediante una macro insertando la línea de código:  ActiveSheet.DisplayPageBreaks = False   al final de la macro (para que "borre" los saltos de página. Un ejemplo sería este:

Sub MiMacro ()

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False

   ....mi código

   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DisplayPageBreaks = False
End sub ()

Después de que esta macro se ejecute ActiveSheet.DisplayPageBreaks = False deja de estar vigente y automáticamente, Excel mostrará los saltos de página la próxima vez que ocurra uno de los hechos que provocan la visualización de estas líneas. Por lo tanto, para evitar verlos, nuevamente, deberemos desactivarlos manualmente o bien con una nueva macro.

5) Evitar que Excel muestre la barra de estado

Tanto la anterior medida (ActiveSheet.DisplayPageBreaks = False) como la que vamos a comentar ahora, no son de las que más ahorro de tiempo generan, pero como dice el refrán: "Toda piedra hace pared".

Se trata de ocultar la barra de estado de Excel si no va a ser necesaria. Simplemente se hace introduciendo esta línea de código: Application.DisplayStatusBar = False


Quedaría así:

Sub MiMacro ()

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False 

   Application.DisplayStatusBar = False

   ....mi código

   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DisplayPageBreaks = False
End sub ()

La barra de estado permanecerá oculta para la sesión de Excel que tenemos abierta (para cualquier libro que haya abierto), pero volverá a visualizarse al reiniciar una sesión de Excel. Esto quiere decir que si deseamos que siempre esté oculta cuando un determinado libro esté abierto, debemos escribir el código en el evento "Open" (estableciendo el valor como "False".

José Manuel Pomares Medrano