Este tutorial discutirá cómo acelerar las macros de VBA y otras mejores prácticas de VBA.
Configuración para acelerar el código VBA
A continuación, encontrará varios consejos para acelerar su código VBA. Los consejos están vagamente organizados por importancia.
La forma más fácil de mejorar la velocidad de su código VBA es deshabilitando ScreenUpdating y deshabilitando los cálculos automáticos. Esta configuración debe desactivarse en todos los procedimientos grandes.
Deshabilitar la actualización de pantalla
De forma predeterminada, Excel mostrará los cambios en los libros de trabajo en tiempo real a medida que se ejecuta el código VBA. Esto provoca una ralentización masiva en la velocidad de procesamiento, ya que Excel interpreta y muestra la mayoría de los cambios para cada línea de código.
Para desactivar la actualización de pantalla:
| 1 | Application.ScreenUpdating = Falso |
Al final de su macro, debe volver a activar la Actualización de pantalla:
| 1 | Application.ScreenUpdating = True |
Mientras su código se está ejecutando, es posible que deba "actualizar" la pantalla. No hay ningún comando de "actualización". En su lugar, deberá volver a activar la Actualización de pantalla y deshabilitarla nuevamente.
Establecer cálculos en manual
Siempre que se cambia el valor de una celda, Excel debe seguir el "árbol de cálculo" para volver a calcular todas las celdas dependientes. Además, cada vez que se cambia una fórmula, Excel deberá actualizar el "árbol de cálculo" además de volver a calcular todas las celdas dependientes. Dependiendo del tamaño de su libro de trabajo, estos nuevos cálculos pueden hacer que sus macros se ejecuten irrazonablemente lentas.
Para configurar los cálculos en manual:
| 1 | Application.Calculation = xlManual |
Para volver a calcular manualmente todo el libro de trabajo:
| 1 | Calcular |
Tenga en cuenta que también puede calcular solo una hoja, rango o celda individual, si es necesario para mejorar la velocidad.
Para restaurar los cálculos automáticos (al final de su procedimiento):
| 1 | Application.Calculation = xlAutomatic |
¡Importante! Esta es una configuración de Excel. Si no restablece los cálculos a automáticos, su libro de trabajo no se volverá a calcular hasta que usted se lo indique.
Verá las mayores mejoras de la configuración anterior, pero hay varias otras configuraciones que pueden marcar la diferencia:
Desactivar eventos
Los eventos son "desencadenantes" que provocan procedimientos de eventos correr. Los ejemplos incluyen: cuando cambia cualquier celda en una hoja de trabajo, cuando se activa una hoja de trabajo, cuando se abre un libro de trabajo, antes de guardar un libro de trabajo, etc.
La desactivación de eventos puede provocar pequeñas mejoras de velocidad cuando se ejecutan macros, pero la mejora de la velocidad puede ser mucho mayor si su libro de trabajo utiliza eventos. Y en algunos casos es necesario deshabilitar eventos para evitar la creación de bucles sin fin.
Para deshabilitar eventos:
| 1 | Application.EnableEvents = False |
Para volver a activar los eventos:
| 1 | Application.EnableEvents = True |
Desactivar saltos de página
Deshabilitar PageBreaks puede ayudar en ciertas situaciones:
- Anteriormente, configuró una propiedad PageSetup para la hoja de trabajo relevante y su procedimiento de VBA modifica las propiedades de muchas filas o columnas
- O Su procedimiento de VBA obliga a Excel a calcular los saltos de página (mostrando la vista previa de impresión o modificando las propiedades de PageSetup).
Para deshabilitar los saltos de página:
| 1 | ActiveSheet.DisplayPageBreaks = Falso |
Para volver a habilitar los saltos de página:
| 1 | ActiveSheet.DisplayPageBreaks = Verdadero |
Mejores prácticas para mejorar la velocidad de VBA
Evite activar y seleccionar
Cuando grabe una macro, verá muchos métodos Activar y Seleccionar:
| 12345678 | Sub Slow_Example ()Hojas ("Hoja2"). SeleccioneRango ("D9"). SeleccionarActiveCell.FormulaR1C1 = "ejemplo"Rango ("D12"). SeleccionarActiveCell.FormulaR1C1 = "demostración"Rango ("D13"). SeleccionarEnd Sub |
La activación y selección de objetos suele ser innecesaria, añaden desorden a su código y consumen mucho tiempo. Debe evitar estos métodos cuando sea posible.
Ejemplo mejorado:
| 1234 | Sub Fast_Example ()Hojas ("Hoja2"). Rango ("D9"). FórmulaR1C1 = "ejemplo"Hojas ("Hoja2"). Rango ("D12"). FórmulaR1C1 = "demo"End Sub |
Evite copiar y pegar
Copiar requiere mucha memoria. Desafortunadamente, no puede decirle a VBA que borre la memoria interna. En cambio, Excel borrará su memoria interna en intervalos (aparentemente) específicos. Entonces, si realiza muchas operaciones de copiar y pegar, corre el riesgo de acaparar demasiada memoria, lo que puede ralentizar drásticamente su código o incluso bloquear Excel.
En lugar de copiar y pegar, considere establecer las propiedades de valor de las celdas.
| 123456789 | Copia secundariaPaste ()'Más lentoRango ("a1: a1000"). Rango de copia ("b1: b1000")'Más rápidoRango ("b1: b1000"). Valor = Rango ("a1: a1000"). ValorEnd Sub |
Utilice los bucles For Each en lugar de los bucles For
Al recorrer objetos, el bucle For Each es más rápido que el bucle For. Ejemplo:
Esto para bucle:
| 123456 | Sub Loop1 ()dim i como RangoPara i = 1 a 100Celdas (i, 1) .Valor = 1Siguiente yoEnd Sub |
| 123456 | Sub Loop2 ()Atenuar celda como rangoPara cada celda del rango ("a1: a100")cell.Value = 1Siguiente celdaEnd Sub |
Declarar variables / Opción de uso explícito
VBA no requiere que declare sus variables, a menos que agregue Option Explicit en la parte superior de su módulo:| 1 | Opción explícita |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Usar con - Finalizar con declaraciones
Si hace referencia a los mismos objetos varias veces (por ejemplo, rangos, hojas de trabajo, libros de trabajo), considere usar la instrucción With. Es más rápido de procesar, puede hacer que su código sea más fácil de leer y simplifica su código.Con ejemplo de declaración:| 12345678 | Sub Faster_Example ()Con hojas ("Hoja2").Rango ("D9"). FormulaR1C1 = "ejemplo".Rango ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Rango ("D12"). Font.Bold = TrueTerminar conEnd Sub |
| 123456 | Sub Slow_Example ()Hojas ("Hoja2"). Rango ("D9"). FórmulaR1C1 = "ejemplo"Hojas ("Hoja2"). Rango ("D12"). FórmulaR1C1 = "demo"Hojas ("Hoja2"). Rango ("D9"). Font.Bold = TrueHojas ("Hoja2"). Rango ("D12"). Font.Bold = TrueEnd Sub |
Consejos avanzados de mejores prácticas
Proteger UserInterfaceOnly
Es una buena práctica proteger sus hojas de trabajo de la edición de celdas desprotegidas para evitar que el usuario final (¡o usted!) Corrompa accidentalmente el libro de trabajo. Sin embargo, esto también protegerá las hojas de trabajo para que no permitan que VBA realice cambios. Por lo tanto, debe desproteger y volver a proteger las hojas de trabajo, lo que consume mucho tiempo cuando se hace en muchas hojas.
| 12345 | Sub UnProtectSheet ()Hojas de cálculo ("hoja1"). Desproteger "contraseña"'Editar Hoja1Hojas de cálculo ("hoja1"). Proteger "contraseña"End Sub |
En su lugar, puede proteger las hojas configurando UserInterfaceOnly: = True. Esto permite que VBA realice cambios en las hojas, al mismo tiempo que las protege del usuario.
| 1 | Hojas de cálculo (“hoja1”). Contraseña de protección: = "contraseña", UserInterFaceOnly: = True |
¡Importante! UserInterFaceOnly se restablece a False cada vez que se abre el libro. Entonces, para usar esta característica increíble, deberá usar los eventos Workbook_Open o Auto_Open para establecer la configuración cada vez que se abra el libro.
Coloque este código en el módulo Thisworkbook:
| 123456 | Private Sub Workbook_Open ()Dim ws como hoja de trabajoPara cada ws en hojas de trabajows.Protect Password: = "contraseña", UserInterFaceOnly: = TrueSiguiente wsEnd Sub |
o este código en cualquier módulo regular:
| 123456 | Private Sub Auto_Open ()Dim ws como hoja de trabajoPara cada ws en hojas de trabajows.Protect Password: = "contraseña", UserInterFaceOnly: = TrueSiguiente wsEnd Sub |
Utilice matrices para editar rangos grandes
Puede llevar mucho tiempo manipular grandes rangos de celdas (Ex. 100,000+). En lugar de recorrer los rangos de celdas, manipulando cada celda, puede cargar las celdas en una matriz, procesar cada elemento de la matriz y luego devolver la matriz a sus celdas originales. La carga de las células en matrices para su manipulación puede ser mucho más rápida.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Atenuar celda como rangoDim t Empezar como dobletStart = TemporizadorPara cada celda del rango ("A1: A100000")cell.Value = cell.Value * 100Siguiente celdaDebug.Print (Timer - tStart) y "segundos"End SubSub LoopArray ()Dim arr como varianteAtenuar elemento como varianteDim t Empezar como dobletStart = Temporizadorarr = Rango ("A1: A100000"). ValorPara cada artículo en arrartículo = artículo * 100Proximo articuloRango ("A1: A100000"). Valor = arrDebug.Print (Timer - tStart) y "segundos"End Sub |
