10 formas de acelerar sus macros

Evite la referencia excesiva

Al llamar a un método o propiedad de un objeto, debe pasar por la interfaz IDispatch del componente OLE. Las llamadas a estos componentes OLE toman tiempo, por lo que reducir la cantidad de referencias a componentes OLE puede mejorar la velocidad del código de macro.

Para la invocación de propiedades o métodos de objetos, generalmente se utiliza el método de representación de  Object.Method  , es decir, el "." El símbolo se utiliza para invocar propiedades y métodos.

Por lo tanto, el número de llamadas a métodos o propiedades se puede juzgar según el número de símbolos ".". Cuanto menos "." símbolo, más rápido se ejecuta el código.

Por ejemplo, la siguiente declaración incluye 3 símbolos ".".

ThisWorkbook.Sheet1.Range("A1").Value = 100

La siguiente declaración tiene sólo un símbolo ".".

Activewindow.Top = 100

Aquí te dejamos algunos trucos para reducir el número de símbolos "." para correr más rápido.

Primero, cuando necesite hacer referencia al mismo objeto repetidamente, puede configurar el objeto en una variable para reducir la cantidad de llamadas. Por ejemplo, el siguiente código requiere dos llamadas por línea.

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

Debido a que es necesario hacer referencia repetidamente al  objeto Sheets ("Hoja1")  , se puede establecer primero en una variable  sht  , de modo que cada código solo deba llamarse una vez.

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

En segundo lugar, si no desea declarar una variable temporal sht, también puede utilizar la  instrucción With  mencionada anteriormente. Como se muestra en el siguiente ejemplo:

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

En tercer lugar,  cuando hay muchos bucles, intente mantener las propiedades y métodos fuera del bucle.  Al reutilizar un valor de propiedad del mismo objeto en un bucle, primero puede asignar el valor de propiedad a una variable específica fuera del bucle y luego usar la variable en el bucle, lo que puede lograr una velocidad más rápida. Como se muestra en el siguiente ejemplo:

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

Cada bucle en este ejemplo obtiene la propiedad Valor de la celda Celdas (1,2). Si asigna la propiedad Valor de Celdas (1.2) a una variable antes de que comience el ciclo, obtendrá una ejecución más rápida. Como se muestra en el siguiente ejemplo:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

El código anterior llama  a ThisWorkbook.Sheets("Sheet1")  cada vez que se repite. Puede hacer esto más rápido usando la  instrucción With  para mover la llamada a  ThisWorkbook.Sheets("Hoja1")  fuera del bucle. Como se muestra en el siguiente ejemplo:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

Evite el uso de tipos de variantes

Los principiantes generalmente prefieren usar variables de tipo Variante, que tienen la ventaja de ser menos complicadas porque se puede usar cualquier tipo de datos sin el problema de desbordamiento de la memoria si los datos son demasiado grandes para los tipos de datos Integer o Long. Sin embargo, los datos de tipo Varienmt requieren más espacio de memoria adicional que los otros tipos especificados (2 bytes para datos de tipo entero, 4 bytes para datos largos y 16 bytes para datos de variante), VBA requiere más tiempo para procesar datos de tipo variante que otros tipos especificados. de datos. Como muestra el siguiente ejemplo.

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

En el código anterior, las líneas 8 a 13 realizan 1 millón de operaciones de suma, resta, multiplicación y división de variables variantes, y las líneas 17 a 22 realizan 1 millón de operaciones de suma, resta, multiplicación y división de variables enteras. En mi computadora, la operación de la variable Variante tomó aproximadamente  0,09375  segundos, mientras que la operación de la variable Entero tomó aproximadamente  0,03125  segundos. Los resultados pueden variar de una computadora a otra, pero  las variables variantes son significativamente más lentas que las variables enteras .

Por este motivo,  se recomienda evitar el uso de variables variantes cuando pueda utilizar explícitamente el tipo de datos especificado .