Cuando creamos una macro para automatizar una tarea, nuestro objetivo principal es conseguir hacer tareas repetitivas de una forma mas eficiente. Con estos consejos sobre buenas prácticas en VBA, además lograremos que se ejecuten de forma mas rápida y las haremos mas robustas ante posibles errores.
Indice de buenas prácticas
- Moverte por diferentes hojas
- Usa siempre el identificador de hoja para trabajar con rangos
- Mejor Cells que Range
- Detén la visualización
- No calcules cada cambio
- No te posiciones en cada celda
- Usa las funciones del propio Excel
- Deshazte de los objetos en cuanto dejes de necesitarlos
- No abuses de los If
- Cada variables, de su tipo
- Usa las constantes propias de VBA
- Termina los ciclos For… Next en cuanto puedas
Moverte por diferentes hojas
Haz referencia a las hojas simepre por el nombre asignado a ellas en el proyecto VBA.
Por defecto, las hojas se llaman Hoja1, Hoja2,… pero puedes personalizar ese nombre en las propiedades, modificándola como ves en la imagen anterior. Ha referencia a ellas por ese nombre.
Si haces referencia a la hoja por su nombre con Sheets(«Mi_Hoja_1»), el el caso de que el usuario decida cambiar el nombre a dicha hoja, se generará un error ya que tu código no la encontrará.
También puedes referirte a la hoja usando el índice Sheets(1), pero tampoco puedes estar seguro de que éste se mantenga en el tiempo ya que se pueden añadir o eliminar hojas y dicho índice podría variar.
En resumen, mejor Hoja1.Range(«A1»).Value=0 que Sheets(«Mi_Hoja_1»).Range(«A1»).Value=0 o Sheets(1).Value=0 Tu código será mucho mas robusto.
Usa siempre el identificador de hoja para trabajar con rangos
Nunca hagas referencias a rangos asumiendo en que hoja te encuentras: Range(«A1»).Value=0. Te puedes llevar la sorpresa de que la hoja activa no es la que esperas y puedes generar un desastre. Identifica perfectamente a que hoja te refieres. Hoja2.Range(«A1»).Value=0.
Mejor Cells que Range
Es mucho mas rápido hacer
Hoja1.Cells(1,1).Value=0
que
Hoja1.Range(«A1»).Value=0
y ya no digamos que
Hoja1.Range(«A1»).Select
ActiveCell.Value=0
Detén la visualización
Una de las cosas que mas tiempo consume es la actualización de la visualización. Si, ya se que es muy hipnotizante ver como se van haciendo las cosas que queremos por si solas en la pantalla mientras piensas la de tiempo de teclear que te estás ahorrando, pero si al principio de tu código detienes la actualización de visualización mediante Application.ScreenUpdating = False y la activas de nuevo justo antes de finalizar con Application.ScreenUpdating = True, el tiempo de ejecución se reducirá de forma drástica.
Haz la prueba, con un código muy sencillo como el siguiente:
Sub Probando1
For x=1 to 500.000
Hoja1.Cells(x,1).Value=x
Next x
End Sub
Y compara el tiempo con respecto a este:
Sub Probando2
Application.ScreenUpdating = False
For x=1 to 500.000
Hoja1.Cells(x,1).Value=x
Next x
Application.ScreenUpdating = True
End Sub
No calcules cada cambio
Salvo que sea estrictamente necesario, pon el modo de calculo en manual al comienzo del código
Application.Calculation = xlCalculationManual
y activa el modo automático al finalizar
Application.Calculation = xlCalculationAutomatic
Si a lo largo de la ejecución hubiese un punto donde necesitas que la hoja se recalcule para trabajar con dichos datos recalculados, fuerza esta actualización con
Application.Calculate
No te posiciones en cada celda
Posicionarse en cada celda lleva tiempo. Códigos del tipo
Hoja1.Range(“A1”).Select
For x=1 to 100
ActiveCell.Value=x
Activecell.Offset(1,0).Select
Next x
Llevan muchísimo tiempo más que:
For x=1 to 100
Hoja1.cells(x,1).Value=x
Next x
Usa las funciones del propio Excel
Si tienes que hacer cálculo y existe una función propia de Excel, utilízala en vez de calcularlo con una rutina. Haz, por ejemplo:
MiMaximo = Application.WorksheetFunction.Max(Hoja3.Range(«A1:A10»))
en vez de:
MiMaximo = 0
For x = 1 To 10
If Hoja3.Cells(x, 1).Value > MiMaximo Then MiMaximo = Hoja3.Cells(x, 1).Value
Next x
Deshazte de los objetos en cuanto dejes de necesitarlos
Aunque tu PC o portátil tenga mucha memoria, Excel es capaz de manejar solo 4 GB en su versión 32 bits. Y si instalas la versión de 64 bits, podrás manejar mas memoria pero probablemente tengas problemas de compatibilidad con tu código. Es frecuente en libros pesados con muchas macros encontrarte con el mensaje de que «no hay memoria suficiente».
Cada vez que defines una variable, esta ocupa espacio en memoria, por ello es muy recomendable liberar dicha memoria en cuanto no la necesites.
Si haces un Set, por ejemplo, Set MiRango = Hoja1.UsedRange, destruye el objeto al terminar de manipularlo con Set MiRango = Nothing.
No abuses de los If
Las expresiones If … Then … Else son lentas. Intenta simplificarlas o usar alternativas. Por ejemplo:
Mejor
Select Case x
Case 1
Hoja1.cells(1,1).Value=0
Case 2
Hoja1.cells(1,1).Value=4
Case 3
Hoja1.cells(1,1).Value=10
Case Else
Hoja1.cells(1,1).Value=50
End Select
Que
If x= 1 then
Hoja1.cells(1,1).Value=0
End if
If x= 2 then
Hoja1.cells(1,1).Value=4
End if
If x= 3 then
Hoja1.cells(1,1).Value=10
End if
If x<>1 and x<>2 and x<>3 then
Hoja1.cells(1,1).Value=50
End if
Cada variable, de su tipo
Además de ser una buena práctica establecer la opción de declarar variables de forma obligatoria para asegurarnos de que por un error al escribir no nos encontramos con un problema en la ejecución del código, l mayoría de las veces, difícil de localizar, es también recomendable evitar declaraciones de variables del tipo que no son. Si vamos a manejar datos en una variable que son de tipo Integer, no tenemos porque declarar una de tipo Long y reservar el doble de espacio en memoria. (4 bytes en vez de 2 bytes) y menos, una Variant, que es cierto que «facilita» las cosas, porque sirve para todo, pero también ocupa 16 bytes (8 veces mas de lo que necesitaríamos!!!).
Si quieres refrescar tus ideas sobre el espacio usado en memoria por cada tipo de variable, puedes ir al resumen de tipos de datos de Microsoft.
Usa las constantes propias de VBA
VBA tiene toda un serie de constantes predefinidas. Su uso no es obligatorio, pero si recomendable por dos motivos:
- si usas la constante predefinida, te aseguras que futuros cambios en las versiones de VBA no te vayan a afectar, por un cambio en el valor que representa
- normalmente, la legibilidad del código es mas sencilla
Por ejemplo, si esperas una respuesta de un MSgBox vbYesNo, es más sencillo de interpretar un
If Respuesta=vbYes…
que un
If Respuesta=6…
Si tienes curiosidad, puedes consultar el listado de constantes de VBA.
Termina los ciclos For… Next en cuanto puedas
Si estás haciendo algo dentro de un ciclo For… Next pero que no tiene por que llegar hasta el último valor de la variable usada y, además, no hay forma de hacerlo con un Do Until… Loop o Do While… Loop (cosa bastante difícil), inserta una condición de salida de dicho ciclo en cuanto sea posible para no ejecutar instrucciones innecesarias. Bien haz que la variables del bucle iguale o supere el valor máximo del ciclo o, mas fácilmente, inserta una instrucción Exit For.
Conclusión
Si aplicas estas buenas prácticas en VBA tus programas, verás que se ejecutan mucho mas rápido y que reduces problemas de robustez. Son consejos sencillos de implementar, pero verás que la recompensa es grande.
Y si tienes alguna otra sugerencia sobre buenas prácticas, por favor, compártela con nosotros en los comentarios.
Tal vez te interese también:
