Mejora el desempeño de tus macros VBA en Excel con estos 9 tips

Una de las cosas a las cuales debemos enfrentarnos seguido (en especial si trabajamos con clientes que no conocemos) son equipos que no están actualizados o bien mal optimizados, y con ello nos encontramos con problemas a la hora de usar un lenguaje de macros como VBA (Visual Basic for Applications), que con problemas de rendimiento puede funcionar tan lento como para ser descartado cuando bien podría no ser necesario.

La idea de esta entrada es enlistar las cosas que se pueden hacer para acelerar el rendimiento de tus macros, y esto se aplica así seas desarrollador, usuario o programador.

Algunos de estos tips han sido juntados de otros blogs confiables, de los mismos ingenieros de Microsoft y otros nombres con autoridad como Jan Karel Pieterse y Charles Williams.

1. Solo mantén activadas las funciones esenciales en VBA

Algo vital para acelerar el código en VBA es desactivar funciones innecesarias como la actualización de pantalla, animaciones, cálculos automáticos y eventos, ya que estos trabajan a la vez que tu macro está activa. Al estar activas al mismo tiempo se genera una sobrecarga que solo ralentiza el macro, en especial si se trata de un macro que modifica muchas celdas a la vez y hace recálculos, puesto que las actualizaciones de pantalla deberán realizarse al mismo tiempo.

Este ejemplo de código te muestra cómo habilitar o deshabilitar estas opciones:

Si no recuerdas como empezar con VBA puedes refrescar tu memoria aquí.

 
 Option Explicit
 Dim lCalcSave As Long
 Dim bScreenUpdate As Boolean
 Sub SwitchOff(bSwitchOff As Boolean)
   Dim ws As Worksheet
     
   With Application
     If bSwitchOff Then 
  
       ' OFF 
       lCalcSave = .Calculation
                bScreenUpdate = .ScreenUpdating
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
       .EnableAnimations = False
       
       '
       ' switch off display pagebreaks for all worksheets
       '
       For Each ws In ActiveWorkbook.Worksheets
         ws.DisplayPageBreaks = False
       Next ws
     Else
  
       ' ON
       If .Calculation <> lCalcSave And lCalcSave <> 0 Then .Calculation = lCalcSave
       .ScreenUpdating = bScreenUpdate
       .EnableAnimations = True
       
     End If
   End With
 End Sub
  
 Sub Main()
   SwitchOff(True) ‘ turn off these features
   MyFunction() ‘ do your processing here
   SwitchOff(False) ‘ turn these features back on
 End Sub 

2. Deshabilitar las animaciones de Office a través de Configuración del Sistema.

Por defecto, Windows tiene activadas sus animaciones, lo cual puede causar problemas de rendimiento en equipos viejos, pero estas pueden ser desactivadas.

Para abrir este menú podemos simplemente presionar las teclas Windows + U. Aquí, y dependiendo de nuestro sistema operativo podremos ajustar las opciones y quitar lo que no haga falta.

Para más detalles en este enlace se explican los pasos para Windows 7, 8 y 10.

3. Deshabilitar animaciones de Office mediante ajustes del registro de Windows

Este método es útil si trabajamos con varios equipos a la vez ya que se ajusta la clave de registro por medio de un ajuste a la configuración de las políticas de grupo. Es importante mencionar que modificar el registro de Windows es delicado y puede causar problemas serios que requieran reinstalar Windows de nuevo, por lo que se recomienda tener cuidado si se decide usar este paso.

Para abrir el registro de Windows habrá que presionar Windows + R y escribir «regedit» en la ventana de Ejecutar.

 En la ventana que se abrirá iremos a HKEY_CURRENT_USER y seguiremos la ruta Software/Microsoft/Office/16.0/Common. Aqui crearemos una nueva KEY llamada Graphics y despues otra llamada DisableAnimations con el valor 0.

Si quieres ver como se realiza esto paso por paso checa el siguiente link.

4. Remover selecciones innecesarias

El método de selección es común en código VBA, aunque este es añadido al macro aun cuando no hace falta. Esta función también puede activar cosas como animaciones y formato condicional en múltiples celdas, lo cual llega a ralentizar bastante el macro, por lo cual removerlo puede ayudar enormemente a optimizar nuestros macros. Este ejemplo muestra el código antes y después de hacer el cambio para remover selecciones innecesarias:

Antes:

Sheets("Order Details").Select
 Columns("AC:AH").Select
 Selection.ClearContents

Después:

Sheets("Order Details").Columns("AC:AH").ClearContents

5. Usar el sentencia With para leer propiedades del objeto

Cuando se trabajan con objetos puedes usar la sentencia With para reducir el número de veces que las propiedades del objeto son leídas. Este ejemplo muestra el antes y el después de hacer el cambio usando la sentencia With:

Antes:

 Range("A1").Value = “Hello”
 Range("A1").Font.Name = “Calibri”
 Range("A1").Font.Bold = True
 Range("A1").HorizontalAlignment = xlCenter 

Después:

 With Range("A1")
   .Value2 = “Hello” 
   .HorizontalAlignment = xlCenter
     With .Font
       .Name = “Calibri”
       .Bold = True
     End With
 End With 

6. Usar rangos y arrays

Leer y escribir a celdas en Excel desde VBA llega a ser pesado puesto que hay sub procesos que ocurren cada vez que hay datos moviéndose entre VBA y Excel. Con esto se entiende que lo mejor sería intentar reducir el número de veces que pasas datos entre VBA y Excel.

Es aquí donde los rangos son útiles, ya que, en lugar de leer y escribir a cada celda de forma individual en ciclos, se puede solamente leer un rango entero en un array desde el inicio. Así el ciclo pasa por el array y luego escribe el array entero de vuelta al final

 Dim vArray As Variant
 Dim iRow As Integer
 Dim iCol As Integer
 Dim dValue As Double
 vArray = Range("A1:C10000").Value2 ‘ read all the values at once from the Excel cells, put into an array 
  
 For iRow = LBound(vArray, 1) To UBound(vArray, 1)
   For iCol = LBound(vArray, 2) To UBound(vArray, 2)
     dValue = vArray (iRow, iCol)
     If dValue > 0 Then 
       dValue=dValue*dValue ‘ Change the values in the array, not the cells 
     vArray(iRow, iCol) = dValue
   End If
 Next iCol
 Next iRow
 Range("A1:C10000").Value2 = vArray ‘ writes all the results back to the range at once 

7. Usa «.Value2» en lugar de «.Text» o «.Value»

Hay varias formas de recuperar valores de una celda, y dependiendo de qué propiedad uses puede marcar diferencia en el desempeño de tu código.

• «.Text» es usado comúnmente para recuperar el valor de una celda. Obtener el formato de una celda es más complejo que solo recuperar un valor, lo cual hace a «.Text» notablemente lento.
• «.Value» es una mejora sobre la anterior, ya que esta en su mayoría se encarga de obtener el valor de una celda sin darle formato, aunque para celdas con formato como fechas o monedas[?] «.Value» va a regresar[?] fechas o monedas de VBA, lo cual puede causar problemas como truncar decimales.
• «.Value2» le da el valor subyacente a las celdas. Dado que no involucra formatos, «.Value2» es más rápido que «.Value» a la hora de procesar números y es mucho más rápido usando una variante array.

Si te interesa conocer una explicación más detallada, aquí tienes el blog de Charles William al respecto.

8. Evita el portapapeles (copiar/pegar)

Cuando usas la Grabadora de Macros para grabar operaciones que usan copiar y pegar, el código va a usar el método de copiar y pegar por defecto. Cuando se trata de código en VBA, es mucho más rápido saltarse el portapapeles y usar operaciones internas en su lugar.

Por defecto, copiar va a copiarlo todo, fórmulas incluídas, así como valores y formatos. Se puede hacer el copiado más rápido si solamente se copian valores o fórmulas sin formato. Este ejemplo nos muestra el código antes y después de hacer el cambio para evitar el portapapeles:

Antes:

Range("A1").Select
 Selection.Copy
 Range("A2").Select
 ActiveSheet.Paste

Después:

‘ Approach 1: copy everything (formulas, values and formatting
 Range("A1").Copy Destination:=Range("A2")
 ‘ Approach 2: copy values only
 Range("A2").Value2 = Range("A1").Value2
 ‘ Approach 3: copy formulas only
 Range("A2").Formula = Range("A1").Formula

9. Usa la opción Explicit para detectar variables sin declarar

La opción Explicit es uno de los módulos directivos disponibles en VBA que indica cómo debe tratarse el código dentro de un módulo. Ajustar la opción Explicit requiere que todas las variables estén declaradas, de lo contrario va a arrojar errores de compilación si una variable sin declarar es usada. Esto ayuda a detectar nombres de variables mal escritas y nos ayuda a mejorar el desempeño con todas las variables escritas siendo definidas a la hora de compilar en vez de ser inferido a la hora de ejecutarlo.

Option Explicit
Sub loQueSea()
' todo el codigo aqui...
End Sub

Esto puede ser activado escribiendo «Option Explicit» hasta arriba de cualquier módulo en tu proyecto o seleccionando la opción «Require Variable Declaration» en Herramientas y luego Opciones en el editor VBA.

Optimiza siempre tus macros de Excel así sea un procedimiento corto o largo para hacer buena costumbre la forma de trabajar limpia. Nadie quiere “parchar” sus códigos después de hechos.

Cualquier duda nos leemos abajo.