sábado, 24 de enero de 2015

Como optimizar la ejecución de una macro de excel y no morir en el intento

Las macros de excel son una buena alternativa cuando se requiere automatizar procesos que involucran cálculos y tratamiento de datos de forma repetitiva, gracias a que su programación resulta relativamente fácil y que es fácil contar con un equipo con el Excel instalado.

En los entornos productivos, con el paso del tiempo las macros que vamos escribiendo suelen crecer en tamaño y complejidad por cuenta de la dinámica de los procesos y del negocio, lo cual suele llevar a que el código se vuelva en lento y poco óptimo. 

Para evitar lo anterior, como en cualquier desarrollo de software, es importante adoptar buenas prácticas entre las cuales se pueden destacar los siguientes principios del desarrollo de software:

  • DRY (Don't repeat yourself - No te repitas): que básicamente consiste en que se debe eliminar al máximo la duplicidad de procesos, o en este caso de instrucciones, para lo cual es fundamental hacer uso de funciones, clases, módulos y demás elementos que proveen los lenguajes de programación (para el caso de este artículo vba de excel) y sus paradigmas.
  • KISS (Keep It Simple, Stupid - Mantenlo simple, estúpido): el cual establece que un sistema funciona mejor si se elimina al máximo la complejidad en su diseño.
  • YAGNI (You ain't gonna need it - No vas a necesitarlo): el cual consiste en que una funcionalidad que no va a ser utilizada no debe ser implementada.

Pero más allá de la metodología que se adopte para los procesos de desarrollo, también es importante desde la misma creación del código fuente contar con buenas practicas para que al momento de ejecutar nuestros programas el tiempo que estos se tarden ene ejecutarseb sea el mejor.

A continuación comparto algunas de las practicas que suelo realizar al escribir el código de las macros de excel, las cuales naturalmente no son todas las posibles, pero que en mi caso me han sido de gran utilidad para optimizar el rendimiento de las mismas.

Deshabilitar algunas funcionalidades propias de excel

Una de las cosas que más ralentizan la ejecución de una macro son el hecho de que todo el proceso que estas hacen se muestra en pantalla, por lo cual suelo deshabilitar esta característica mientras se ejecuta el código de la macro así:

Application.screenupdating=False

Con esto cada vez que se ejecute el código, no se verá en pantalla todo el proceso sino que la macro se ejcutará de una manera "más silenciosa". Una vez se termine la rutina o proceso que estamos codificando solo basta con cambiar de nuevo el valor a true:

Application.screenupdating=True

También es bueno deshabilitar otras características como los cálculos y eventos automáticos:

Application.calculation=xlCalculationManual
Application.EnableEvents=False

Con base a lo anterior así sería el ejemplo de una macro con las modificaciones mencionadas:

Private Sub Worksheet_Activate()
    ' Declaro e inicializco variables
    Dim NumeroPagos As Long
    NumeroPagos = 0
    
    Application.ScreenUpdating = False ' Deshabilito actualización en pantalla
    Application.Calculation = xlCalculationManual ' Deshabilito los cáculos automáticos
    Application.EnableEvents = False ' Deshabilito los eventos automáticos
    ActiveSheet.PivotTables("Resumen Pagos").PivotCache.Refresh 'Actualizo la tabla dinámica
    NumeroPagos = Range("A" & Rows.Count).End(xlUp).Row ' Obtengo la cantidad de registros en la hoja
    Application.DisplayStatusBar = True ' Activo la barra de estado
    Application.StatusBar = "Hay " & NumeroPagos & " registros." ' Cambio el contenido de la barra de estado
    Application.ScreenUpdating = False ' Habilito de nuevo la actualización en pantalla
    Application.Calculation = xlCalculationAutomatic ' Habilito de nuevo los cálculos automáticos
    Application.EnableEvents = False ' Habilito de nuevo los eventos automáticos
End Sub


Centralizar a la medida de lo posible el código fuente

Siendo consecuentes con los principios mencionados al comienzo de la entrada, una buena practica es centralizar el código fuente que contiene funciones y rutinas utilizando módulos, y hacer uso de estas a través de los diferentes eventos disponibles en cada hoja.

Utilizar la instrucción with para trabajar con los objetos

Si se necesita trabajar con un objeto como una hoja, un rango, o una gráfica por ejemplo; es más óptimo acceder a éste y manipularlo con la instrucción with ya que solo debo hacer referencia al objeto una vez:

' De la forma tradicional debo hacer referencia al objeto en cada línea
ActiveWorkbook.Sheets(1).Range("A1:A3").Font.Size = 12
ActiveWorkbook.Sheets(1).Range("A1:A3").Font.Name = "Calibri"
ActiveWorkbook.Sheets(1).Range("A1:A3").Font.Bold = True
    
' Usando With solo lo debo hacer una vez, lo cual optimiza el tiempo de ejecución
With ActiveWorkbook.Sheets(1).Range("A1:A3").Font
        .Size = 12
        .Name = "Calibri"
        .Bold = True
End With

Usar las formulas directamente desde visual basic

Haciendo uso del método WorksheetFunction  del objeto Application se pueden ejecutar las mismas formulas que se tienen disponibles en la hoja de cálculo, teniendo en cuenta que al usarlas desde excel éstas están con su nombre en inglés. Por ejemplo:

' Escribo la fecha y hora actuales usando la función AHORA()
Range("A").Value = Application.WorksheetFunction _
.now()

Conclusiones


Los puntos descritos en esta entrada no son los definitivos, existen muchos más "trucos" y buenas practicas que contribuyen a optimizar el tiempo de ejecución. En términos generales las buenas practicas que se aplican en otros lenguajes de programación se pueden aplicar con éxito a la hora de escribir macros.

El tiempo de ejecución de una macro es un factor importante para mejorar la experiencia del usuario final u optimizar el proceso, y más aún si estamos en un entorno corporativo donde el tiempo es valioso; por lo cual, desde la misma etapa de diseño de una solución se debe apuntar a hacer uso de las mejores practicas de programación.