Cinco trucos para optimizar la ejecución de macros en VBA/Excel

Estándar

A continuación menciono cinco grandes trucos para optimizar el código generado por el grabador de macros, así mismo pueden ser aplicados cuando se programa o se personaliza una macro de forma manual. Con estos trucos se consigue una disminución en el tiempo de ejecución de la misma.

Personalmente me desespera el ver una macro en ejecución que tarda demasiado tiempo… seleccionando celda por celda para llevar acabo un proceso o cosas por el estilo. Tuve la oportunidad de ver la ejecución de una macro que tardaba 2 días para generar reportes de métricas mensuales. Mi jefe presumía del talento de la chica que genero tal macro para obtener dichos reportes… No dude de los conocimientos de la chica en estadística, pero en cuento a su estilo de programación… dejaba mucho que desear, después de todo ella no era programadora!

Si bien el contar con un equipo con grandes prestaciones acelera la ejecución del código lo que realmente impacta en el desempeño de la ejecución del mismo es el utilizar buenas técnicas de programación, algoritmos eficientes y reducir al mínimo la cantidad de lineas de código.

Estos son los trucos:

  • Evitar la selección

En Excel es necesario seleccionar primero las cosas antes de actuar sobre ellas. Por ejemplo, se selecciona la fila uno y se le aplica un formato en negrita. Sin embargo, en VBA raramente se tiene que seleccionar algo para hacer esto.

Código generado por el grabador:

Rows("1:1").Select
Selection.Font.Bold = True

Código optimizado:

Rows("1:1").Font.Bold = True

Al optimizar el código se obtienen dos ventajas. Disminución de líneas de código, ejecución más rápida del mismo.

  • Comenzar el rango desde abajo para encontrar la última fila

Cuando se tengan que procesar datos fila por fila de hoja y no se sepa de antemano el numero de filas a procesar, no es muy recomendable utilizar lo siguiente en el código:


While ActiveCell <> ""
'procesa datos...
Wend

Suponiendo que la celda activa (ActiveCell) es A1, el código anterior va procesando celda por celda hasta encontrar una en blanco pero que tal si hay celdas en blanco entre datos? habría filas de datos que no se procesen. Lo mejor es empezar desde abajo:


fila = Range("A65536").End(xlUp).Row
for i = 1 to fila
'procesa datos...
next i

Con el método anterior realmente sabremos con seguridad el numero de filas a procesar, lo mejor es utilizar una columna en la que estemos 100% seguros que nunca habrá celdas en blanco. El código anterior esta pensado para las 65536 filas de Excel 2003 , pero Excel 2007 dispone de mas de un millón. Una alternativa que funcione en ambas versiones podría ser:


fila = Cells(Rows.Count, 1).End(xlUp).Row
for i = 1 to fila
'procesa datos...
next i

  • Utilizar variables

El grabador de macro no crea ni emplea variables, las cuales son muy sencillas de utilizar.


ultima_fila = Range("A65536").End(xlUp).Row
Range("A" & ultima_fila+1) = "Total: "
Range("B" & ultima_fila+1).Formula = "=SUM(B2:B" & ultima_fila & " )"

El código anterior averigua la ultima fila, pone al final de la columna A la palabra “Total: ” y al final de la columna B la suma de los datos de misma.

  • Copiar y pegar en una sola linea

El código grabado, primero selecciona un rango, lo copia, se selecciona el rango destino y pega. Ejemplo:


Range("A1:A5").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste

El código anterior copia el rango de A1:A5 y lo pega en la celda C1. La versión optimizada es la siguiente:


Range("A1:A5").copy Destination:=Range("C1")

Todo el trabajo de copiado se realiza en una sola linea de código. Este método no es funcional cuando lo que se copia son formulas y lo que se quiere pegar son valores. La palabra “Destination:=” es opcional.

  • Utilizar With – End With

Si se van a realizar varias acciones sobre un un rango de celdas u algún otro objeto, esto es lo que obtenemos del grabador:


Range("A1:A5").Select
Selection.Font.Bold = True 'negrita
Selection.Font.Size = 12 'tamaño de fuente
Selection.Font.ColorIndex = 5 'asigna color
Selection.Font.UnderLine = xlUnderlineStyleDoubleAccounting

El código anterior aplica formato en negrita, un tamaño de letra 12, color y doble subrayado al rango A1:A5. Empleando With – End With el codigo optimizado es:


With Range("A1:A5").Font
.Bold = True 'negrita
.Size = 12 'tamaño de fuente
.ColorIndex = 5 'asigna color
.UnderLine = xlUnderlineStyleDoubleAccounting
End With

Estos 5 trucos han sido tomados del libro: Excel, Macros y VBA de Editorial Anaya

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s