Acelerar y optimizar las macros

I. ACELERAR Y OPTIMIZAR LAS MACROS

1.1. AL COMIENZO DE LAS MACROS

a. Apagar el parpadeo de pantalla

Lo hacemos con la instrucción: Application.screenupdating=False

Evita los movimientos de pantalla que se producen al seleccionar celdas, hojas y libros

b. Apagar los cálculos automáticos

Lo hacemos con la instrucción: Application.calculation=xlCalculationManual

Evita que se recalcule todo cada vez que se pegan o modifican datos

c. Apagar los eventos automáticos

Lo hacemos con la instrucción: Application.EnableEvents=False

Evita que se disparen macros de evento si las hubiere

d. Apagar visualización de saltos de página

Lo hacemos con la instrucción: ActiveSheet.DisplayPageBreaks = False

Sirve para evitar algunos problemas de compatibilidad entre macros Excel 2003 vs. 2007/2010

En resumen, siempre debemos comenzar las macros así:

Application.screenupdating=False

Application.calculation=xlCalculationManual

Application.EnableEvents=False

ActiveSheet.DisplayPageBreaks = False

.

1.2. AL FINAL DE LAS MACROS

a. Borrar contenido de portapapeles

Lo hacemos con la instrucción: Application.CutCopyMode = False

Permite limpiar el portapapeles en caso de haber copiado datos

Además debemos volver a su estado original las instrucciones con las que comenzamos la macro.

En resumen, siempre debemos finalizar las macros así:

Application.screenupdating=True

Application.calculation=xlCalculationAutomatic

Application.EnableEvents=True

ActiveSheet.DisplayPageBreaks = True

Application.CutCopyMode = False

.

OTRAS TECNICAS UTILES

a. Usar la instrucción WITH

Se usa para evitar tener que referenciar un mismo objeto muchas veces

Ejecución leeenta…

Sheets(1).Range(“A1:Z1″).Font.Italic = True

Sheets(1).Range(“A1:Z1″).Font.Interior.Color = vbRed

Sheets(1).Range(“A1:Z1″).MergeCells = True

Ejecución rápida!

With Sheets(1).Range(“A1:Z1″)

.Font.Italic = True

.Font.Interior.Color = vbRed

.MergeCells = True

End With

b. Evitar la instrucción SELECT

Se genera sobre todo en las macros grabadas

La mayoría de las veces no es necesario seleccionar para cumplir el objetivo

Ejecución leeenta…

Range(“E1″).Select

Selection.Copy

Range(“D10″).Select

ActiveSheet.Paste

Ejecución rápida!

Range(“E1″).Copy Range(“D10”)

c. Evitar loops FOR EACH

Tener que ir celda por celda consume mucho tiempo

Se puede resolver el problema de forma más directa!

Ejecución leeenta…

For Each cell In Range(“A1:A10000″)

If cell = Empty Then cell = 0

Next cell

• Los loops siempre son leeentos

* En este caso recorre 10.000 celdas!

Ejecución rápida!

Existen diversas formas de evitar los loops. La solución dependerá del caso concreto en cuestión. Generalmente se usan algunas de estas técnicas: agrupar, ir a especial, filtros, filtros avanzados. La idea es poder realizar la acción sobre todos los elementos al mismo tiempo, en lugar de tener que ir uno a uno!

d. Usar las funciones nativas de Excel

No quieras reinventar la rueda. Quizás ya exista una función Excel que lo haga!

Las macros siempre ejecutan más rápido las funciones nativas de Excel

Ejecución leeenta…

mProducto = 1

For i = 1 to 100

mProducto = mProducto * Cells(3,i)

Next

Ejecución rápida!

mProducto = Application.WorkSheetFunction.Product(Range(“C1:C100″))

...