Ir al contenido principal

14 formas de acelerar y optimizar tus macros excel

Esta información es muy útil para quienes manejen el tema de programación de macros excel. ¿Tus macros van lentas? ¿Problemas a la hora de ejecutarlas? ¿Cuáles son las técnicas recomendadas?
Cuando de programación de macros excel se trata, el tema de la eficiencia y la velocidad es clave. Hay 2 leyes fundamentales que hay que recordar:

.

codigo
a. Cuanto menos código tiene una macro mejor…¿por qué?
Ayuda a que la macro se ejecute mucho más rápido
Simplifica la tarea a la hora de modificar/ampliar/reparar la macro

.
rapido
b. Cuanto más rápido se ejecuta una macro mejor!…¿por qué?
Mejora la experiencia del usuario
No mantiene la PC ocupada tanto tiempo


Respecto de usar menos código dependerá de las habilidades del programador excel en cuestión. Hemos visto infinidad de casos donde 30 o 40 líneas de código VBA se pueden resumir en 5 o 6 líneas (algo similar pasa con las fórmulas excel). Siempre hay macros o fórmulas que hacen la tarea de forma más directa y sin dar tantas vueltas!
Otra recomendación clave es invertir mucho tiempo inicial en planificar y analizar la lógica del trabajo. Esto nos va a ahorrar muchos problemas y dolores de cabeza posteriores!
Hay algunas instrucciones puntuales que siempre conviene usar y que van a acelerar y optimizar nuestras macros en todos los casos. Vamos a ver repasar algunas técnicas puntuales que podemos usar al comienzo, durante y al final de nuestras macros.
.
AL COMIENZO DE LAS MACROS


1. 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



2. 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



3. Apagar los eventos automáticos

Lo hacemos con la instrucción: Application.EnableEvents=False
Evita que se disparen macros de evento si las hubiere



4. 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
.

AL FINAL DE LAS MACROS


5. 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


6. 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 



7. 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”)



8. 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!

9. 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″))



10. Forzar la declaración de variables

En el editor VBA, menú Herramientas > Opciones > pestaña Editor > marcar “Requerir declaración de variables”
Luego usar la variable correcta: si es fecha usar Date, si es texto usar String, si es valor usar Long…
Evitar el uso de la variable Variant ya que insume más recursos…
Usar nombres de variables que nos digan algo (por ej. “UltimaFila” o “FilaZ” en lugar de “f” o “uf”)

11. Escribir las macros en módulos y no en hojas

Las hojas pueden ser borradas o copiadas y esto generaría problemas inesperados

12. Separar el proceso en varias macros (divide y conquistarás)
Si tu macro hace muchas cosas conviene separarla en muchas macros pequeñas y luego unirlas
Es más fácil para controlar, auditar, etc…
Además te permite luego poder rehusar alguna parte del proceso en otras macros

Macro muy laaarga…

Sub MegaMacro()
‘Codigo limpia datos
’Codigo carga datos
’Código arregla datos
’Código arma reporte
End Sub() 


Mejor dividir en diferentes macros para cada proceso

Sub LimpiaDatos()
‘Codigo…
End Sub Sub

CargaDatos()

‘Codigo…

End Sub

Sub ArreglaDatos()

‘Codigo…

End Sub

Sub ArmaReporte()

‘Codigo…

End Sub



Finalmente podemos unir todos los procesos
Sub ProcesoCompleto()
Call LimpiaDatos
Call CargaDatos
Call ArreglaDatos
Call ArmaReporte
End Sub() 

13. Ser cuidadoso con la instrucción ON ERROR RESUME NEXT


Esta instrucción hace que la macro siga avanzando aunque encuentre un error
En algunos casos esto hará que se ignoren errores que no deberían ser ignorados
Podrías tener errores (bugs) y no enterarte! 

14. Comentar bien las macros

¿Qué pasaría si tuvieras que volver a revisar/arreglar/ampliar tu código 8 meses después?
Añadir comentarios te ayudará a describir y recordar la lógica y te ahorrará mucho tiempo!
Labels:

Comentarios

Publicar un comentario

Entradas más populares de este blog

Consolidar varias hojas en una sola con Power Query

Te dejo un video para consolidar varias hojas en una sola con Power Query  
Publicar un comentario
Leer mas »

Controlar base de datos de proveedores.

Has tu pedido por el correo electronico   solucionesmsexcelvba@gmail.com ¡Yo Te lo diseño de acuerdo a tus necesidades!
Publicar un comentario
Leer mas »

Funcion (NOMPROPIO,MAYUSC,MINUSC)

Existen algunas funciones en Excel que te ayudarán a aplicar formato de mayúsculas ó minúsculas a textos de manera fácil y rápida. Revisaremos estas funciones con un ejemplo sencillo. En la celda A1 colocaré el valor “excel total”: Primera letra en mayúscula Ahora haré uso de la función NOMPROPIO para colocar la primera letra de cada palabra en mayúscula. La fórmula queda de la siguiente manera: =NOMPROPIO(A1) El resultado al aplicar esta fórmula en la celda B1 es el siguiente: Convertir a mayúsculas Para transformar todas las letras en mayúsculas de la celda B1 y utilizaré la función MAYUSC aplicando la siguiente fórmula: =MAYUSC(B1) Con esta fórmula, la celda C1 tendrá el siguiente resultado: Convertir a minúsculas Finalmente haré uso de la función MINUSC para convertir de nuevo todas las letras en minúsculas. La fórmula a utilizar será la siguiente: =MINUSC(C1) Este es el resultado final:
Publicar un comentario
Leer mas »

¿Cómo saber si tengo instalado una versión de Office de 32 o 64 bits?

Microsoft recomienda el uso de Office de 32 bits para evitar problemas de compatibilidad, incluso nos da la posibilidad de usar esta versión en  sistemas operativos de 32 o 64 bits mientras que   una versión de Office de 64 bits solo puede ser usado en  sistemas operativos de 64 bits. La versión de office de 64 bits está orientada a archivos con altos volúmenes de información con tamaños y/o peso superior a 2GB, podemos inferir que esta versión esta orientada a usuarios avanzados y usos corporativos. A continuación te explico paso a paso como saber la versión de office (32 o 64 bits) que tenemos instalado para Office 2010 y 2013. OFFICE 2010 Abrimos Excel 2010 . Clic en el menú ARCHIVO . Clic en AYUDA En la parte derecha podemos ver la versión de Excel. OFFICE 2013 Abrimos Excel 2013. Clic en el menú ARCHIVO . Clic en CUENTA. Clic en ACERCA DE EXCEL. Se apertura una ventana desde donde podemos ver la versión.
Publicar un comentario
Leer mas »

Versiones de Excel

Historia de las versiones de Excel Es importante conocer la versión de Excel que estás utilizando porque si tienes una versión actual y quieres compartir la información con otra persona pero tu archivo hace uso de funcionalidades exclusivas de las últimas versiones de Excel, entonces la otra persona podría tener dificultades para trabajar con el archivo. O peor aún, si has guardado el archivo en el formato de las versiones 2007 o 2010 entonces una persona con una versión de Excel 2003 ( o anterior ) no podrá siquiera abrir el archivo. para tener una mejor idea de las versiones de Excel existentes en el mercado mencionaré todas las versiones del software y su año de lanzamiento. 1987 – Excel 2.0 1990 – Excel 3.0 1992 – Excel 4.0 1993 – Excel 5.0 1995 – Excel 95 (versión 7.0) 1997 – Excel 97 – incluido en Office 97 1999 – Excel 2000 (versión 9.0) 2001 – Excel 2002 (versión 10) 2003 – Excel 2003 (versión 11) ...
Publicar un comentario
Leer mas »

Consultar estatus de CFDI en el SAT desde VBA Excel

Si eres contador, auditor o trabajas día a día con CFDI´s, entonces esta herramienta es para ti. Si necesitas validar el estatus de un CFDI en el SAT basta con ingresar en la página del SAT https://verificacfdi.facturaelectronica.sat.gob.mx/ capturas los datos desde el portal del SAT como en la siguiente imagén... y listoooo...   Perooooooo..... Imagínate validar estatus de miles y miles de CFDI´s en el SAT es relativamente tedioso copiar y pegar uno por uno, luego capturar el CAPTCHA es aburrido no crees?, es por ello que tengo la mejor herramienta que te va ayudar a validar de forma masiva tus CFDI´s en el servidor del SAT, la herramienta esta hecha 100% en VBA Excel y ademas es muy fácil de utilizar. Si estas interesado, envíame un whatsapp al +52 967 151 5365 o también me puedes seguir en mi página de Facebook de igual manera me puedes dejar un mensaje.
Publicar un comentario
Leer mas »

Convertir números a letras

Te muestro como crear tu propia función para convertir números a letras , esto puede serte de mucha utilidad si usas Excel para imprimir  reportes en los que debas ingresar un numero en letras. Primero ingresa a VBA presionando las teclas Alt+F11, e inserta un Modulo (Si ya tienes un modulo no necesitas insertar uno nuevo)   Ahora selecciona tu modulo ( Module1 ) Y en la ventana derecha ingresa este texto: Function NumLetras(Valor As Currency, Optional MonedaSingular As String = "", Optional MonedaPlural As String = "") As String Dim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte, lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As Byte, lcBloque As String, lnNumeroBloques As Byte, lnBloqueCero Dim laUnidades As Variant, laDecenas As Variant, laCentenas As Variant, I As Variant 'Si esta como Option Explicit Dim ValorEntero As Long     Valor = Round(Valor, 2)     lyCantidad = Int...
Publicar un comentario
Leer mas »

Validar Datos desde VBA con FOR

Publicar un comentario
Leer mas »

Sumar en Excel según el color de la celda

Desafortunadamente Excel no tiene una formula o una función que realice esta tarea, por lo que tendremos que construir una función personalizada. Para sumar por el color de relleno de una celda será necesario evaluar la propiedad Interior.Color y todas las celdas que tengan el mismo valor deberán ser sumadas. El código de nuestra función será el siguiente: Function Sumarcolor(Celdacolor As Range, Rangosuma As Range) As Double     Dim celda As Range     For Each celda In Rangosuma         If celda.Interior.ColorIndex = Celdacolor.Cells(1, 1).Interior.ColorIndex Then Sumarcolor = Sumarcolor + celda     Next celda     Set celda = Nothing End Function Cada vez que encontramos una coincidencia en el valor de la propiedad Interior.Color la variable resultado hace la suma del valor de la celda y así obtenemos la suma total. Observa el comportamiento de esta función: ...
3 comentarios
Leer mas »