10 modi per velocizzare le tue macro

Evitare riferimenti eccessivi

Quando si chiama un metodo o una proprietà di un oggetto, è necessario che passi attraverso l'interfaccia IDispatch del componente OLE. Le chiamate a questi componenti OLE richiedono tempo, pertanto la riduzione del numero di riferimenti ai componenti OLE può migliorare la velocità del codice macro.

Per l'invocazione di proprietà o metodi dell'oggetto viene generalmente utilizzato il metodo di rappresentazione di  Object.Method  , ovvero il "." Il simbolo viene utilizzato per richiamare proprietà e metodi.

Pertanto, il numero di chiamate a metodi o proprietà può essere giudicato in base al numero di simboli ".". Meno "." simbolo, più velocemente viene eseguito il codice.

Ad esempio, la seguente istruzione include 3 simboli ".".

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

La seguente istruzione ha un solo simbolo ".".

Activewindow.Top = 100

Ecco alcuni trucchi per ridurre il numero di simboli "." per correre più veloce.

Innanzitutto, quando è necessario fare riferimento ripetutamente allo stesso oggetto, è possibile impostare l'oggetto su una variabile per ridurre il numero di chiamate. Ad esempio, il codice seguente richiede due chiamate per riga.

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

Poiché  è necessario fare riferimento ripetutamente all'oggetto Sheets("Sheet1")  , è possibile impostarlo prima su una variabile  sht  , in modo che ciascun codice debba essere chiamato solo una volta.

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

In secondo luogo, se non vuoi dichiarare una variabile temporanea sht, puoi anche utilizzare l'  istruzione With  menzionata in precedenza. Come mostrato nel seguente esempio:

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

In terzo luogo,  quando sono presenti molti cicli, provare a mantenere le proprietà e i metodi all'esterno del ciclo.  Quando si riutilizza il valore di una proprietà dello stesso oggetto in un ciclo, è possibile prima assegnare il valore della proprietà a una variabile specificata all'esterno del ciclo, quindi utilizzare la variabile nel ciclo, ottenendo così una velocità maggiore. Come mostrato nel seguente esempio:

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

Ogni ciclo in questo esempio ottiene la proprietà Value della cella Cells(1,2). Se assegni la proprietà Value di Cells(1.2) a una variabile prima dell'inizio del ciclo, otterrai un'esecuzione più veloce. Come mostrato nel seguente esempio:

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

Il codice precedente chiama  ThisWorkbook.Sheets("Sheet1")  ogni volta che esegue un ciclo. Puoi farlo più velocemente utilizzando l'  istruzione With  per spostare la chiamata a  ThisWorkbook.Sheets("Sheet1")  all'esterno del ciclo. Come mostrato nel seguente esempio:

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

Evitare l'uso di tipi di variante

I principianti di solito preferiscono utilizzare le variabili di tipo Variant, che ha il vantaggio di essere meno complicato perché qualsiasi tipo di dati può essere utilizzato senza il problema di overflow della memoria se i dati sono troppo grandi per i tipi di dati Integer o Long. Tuttavia, i dati di tipo Varienmt richiedono più spazio di memoria aggiuntivo rispetto agli altri tipi specificati (2 byte per i dati Integer, 4 byte per i dati Long e 16 byte per i dati Variant), VBA richiede più tempo per elaborare i dati di tipo Variant rispetto ad altri tipi specificati di dati. Come mostra l'esempio seguente.

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

Nel codice precedente, le righe da 8 a 13 eseguono 1 milione di operazioni di addizione, sottrazione, moltiplicazione e divisione di variabili Variant e le righe da 17 a 22 eseguono 1 milione di operazioni di addizione, sottrazione, moltiplicazione e divisione di variabili Integer. Sul mio computer, l'operazione della variabile Variant ha richiesto circa  0,09375  secondi, mentre l'operazione della variabile Integer ha richiesto circa  0,03125  secondi. I risultati possono variare da computer a computer, ma  le variabili Variant sono significativamente più lente delle variabili Integer .

Per questo motivo,  si consiglia di evitare di utilizzare le variabili Variant quando è possibile utilizzare esplicitamente il tipo di dati specificato .