Hay teoría general de optimización, pero ¿hay tretas específicas para VBA?
Conceptos generales de optimización
Cuando pensamos en optimizar código deberíamos analizar varios aspectos:
- Escalabilidad: ¿Sirve para grandes volúmenes?
- Desempeño: Puede ser fácil de escribir en VBA, pero ¿acaso tarda una eternidad?
- Estabilidad: ¿Tu código puede fallar con alguna condición?
- Propenso a errores: Si le haces cambio al código, ¿es muy propenso a errores que arrojan resultados equivocados?
- Documentación y mantenimiento: ¿Tu código está documentado para que alguien más lo pueda entender y modificar, o para que tu mismo puedas recordar lo que hiciste cuando lo programaste meses o años atrás?
Cuando vas a optimizar, normalmente pensamos en algoritmos. Para evaluarlos es preciso pensar en:
- ¿Entiendo lo que hace y cómo lo hace?
- ¿Lo he probado y funciona con todos los casos extremos y con los casos comunes?
- ¿Funciona mejor que otro algoritmo? ¿Por qué? ¿Es más rápido? ¿Es más fiable? ¿Es más estable?
- ¿El algoritmo se ajusta a la necesidad presente?
- ¿Funciona para todos los escenarios?
- ¿Es fácil de usar y modificar?
Optimizar en VBA
Muchos dicen que Visual Basic no es un lenguaje muy profesional, por la velocidad de procesamiento. Sin embargo en Excel VBA hay varios trucos para hacer que el tiempo que tarda en procesar se reduzca:
- Apaga todo lo que no necesitas. tendrás que ver si alguna de estas características es necesaria o no. Si no la necesitas, apágala.
Para apagar (al inicio del programa)
screenUpdateStatus = Application.ScreenUpdating
statusBarStatus = Application.DisplayStatusBar
calcStatus = Application.Calculation
eventsStatus = Application.EnableEvents
displayPageBreakStatus = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Para encender (al final del programa)
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState
- Usa vbNullString en lugar de ""
- Reduce los números de línea, separando con dos puntos (:). A veces hace el código ilegible.
- Evita copiar y pegar innecesariamente.
- Usa las funciones de Excel en lugar de instrucciones de Visual Basic cuando puedas (mejora la velocidad, pero te expones a problemas futuros de compatibilidad y a los bugs de los programadores de Microsoft, que son bastantes)
- Utiliza WITH
- Destruye objetos en memoria que no utilices.
- Trata de usar constantes
- Limpia el portapapeles al pegar
- Usa FOR EACH en lugar de INDEXED FOR
- Evita usar variables del tipo Variant
- Minimiza la escritura y lectura de celdas, especialmente si vas a leer muchas celdas.
- Evita seleccionar y activar objetos
- A la hora de hacer ciclos "loops" tienes varias posibilidades:
Busquedas: Usas las funcionesSEARCH, FIND, VLOOKUP, MATCH
Copiar: Copia rangos y no celdas. Puedes también asignar un valor de un rango a otro rango para evitar el paso por el portapapeles.
Ordenar: Usa Range.sort o métodos incorporados en Excel.
Ciclos anidados: Evítalos tanto como sea posible.
- Adopta ideas: Miras la manera de hacer código de otros en internet para ver como acelerar la ejecución.
- Subdivide tu código: En lugar de tener un enorme programa principal, subdivídelo en pequeñas porciones. Si tienes oportunidad de crear código genérico reutilizable, hazlo. Es más fácil revisar pequeños trozos de programa que un enorme bloque.
- Muestra el avance al usuario: El usuario puede ser impaciente y mostrarle cuanto falta para terminar, o el estado de avance, ayuda a reducir su ansiedad.
- Achica: Si tenes un programa principal pequeño, eso es mejor. Para ello es mejor diseñar el programa primero, en lugar de empezar a programar lo que se te viene a la mente de manera improvisada. El edificio se diseña, en lugar de apilar ladrillos alocadamente.
- Simplifica: Que la interface de usuario sea simple. Menos clicks para llegar donde se quiere llegar, menos botones, menos de todo. Minimalista.
Por supuesto, en el camino tendrás la disyuntiva entre usar funciones de Excel y las funciones de Visual Basic, pues Excel está plagado de bugs. Al usar funciones de Excel puedes ganar velocidad pero te expones a los bugs de los programadores de Microsoft que ya hacen que grandes empresas migren sus macros de VBA. Ya en el pasado me he referido a algunos bugs horribles en posts anteriores.
Por ejemplo, notarás que la orden de ScreenUpdating no sirve en Excel 2013, tampoco el comando DoEvents, y otros, de modo que la optimización que planeabas no va a salir tan buena como creías.
En lo personal yo prefiero meter todas las funciones de Excel en un módulo aparte, de modo que mi programa principal carezca de funciones de Excel. En caso de bugs, siempre puedo encontar un solo punto de falla en ese módulo y puedo reemplazar el código que no funciona con código lento en comandos de Visual Basic, que funciona como debe. Por eso mi código no es el más rápido del universo, pero funciona.
Prefiero calidad, bug free, en lugar de velocidad, pues con bugs conocidos de Excel, pensar en velocidad se vuelve despropósito.
Cuando aparece un bug de Microsoft, normalmente tardan mucho en arreglarlo, así que el código lento en mi módulo de Excel llega para quedarse.
Aventurarse a programar en VBA podría ser un sueño, de no ser porque los programadores de Microsoft no atendieron los bugs.
Para elaborar prototipos es maravilloso, pero si se trata de tener algo duradero, vas a tener que acostumbrarte al dicho que dice "Open Windows and let the bugs in".
Lo curioso es que habiendo cosas que funcionaban bien, de pronto aparece un bug donde todo funcionaba bien antes, que para mí es señal de que en materia de desarrollo, en Microsoft deben estar teniendo serios problemas.
Y por eso no me inspira para nada cambiar la versión de Excel. La sabiduría popular de internet dice "nunca actualices algo que funciona" y es mejor bug conocido que bugs por conocer. Ya me arrepentí de pasar de Office 2010 a 2013. Y es que había funciones de 2010 que dejaron de funcionar y que funcionan en 2013, pero ahora en 2013 hay cosas de 2010 que funcionaban y en 2013 no funcionan (como el ScreenUpdate). Si te cambias de nuevo, imagino que verás otras maravillas como las que se ven en el paso de 2010 a 2013.
