10 façons daccélérer vos macros

Évitez les références excessives

Lors de l'appel d'une méthode ou d'une propriété d'un objet, celle-ci doit passer par l'interface IDispatch du composant OLE. Les appels à ces composants OLE prennent du temps, donc réduire le nombre de références aux composants OLE peut améliorer la vitesse du code de macro.

Pour l'invocation de propriétés ou de méthodes d'objet, la méthode de représentation de  Object.Method  est généralement utilisée, c'est-à-dire le "." Le symbole est utilisé pour appeler des propriétés et des méthodes.

Par conséquent, le nombre d'appels de méthode ou de propriété peut être jugé en fonction du nombre de symboles ".". Moins il y a de "." symbole, plus le code s'exécute rapidement.

Par exemple, l'instruction suivante comprend 3 symboles ".".

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

L'instruction suivante n'a qu'un seul symbole ".".

Activewindow.Top = 100

Voici quelques astuces pour réduire le nombre de symboles "." pour courir plus vite.

Premièrement, lorsque vous devez faire référence au même objet à plusieurs reprises, vous pouvez définir l'objet sur une variable pour réduire le nombre d'appels. Par exemple, le code suivant nécessite deux appels par ligne.

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

Étant donné que l'  objet Sheets("Sheet1")  doit être référencé à plusieurs reprises, il peut d'abord être défini sur une variable  sht  , de sorte que chaque code ne doit être appelé qu'une seule fois.

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

Deuxièmement, si vous ne souhaitez pas déclarer de variable temporaire sht, vous pouvez également utiliser l'  instruction With  mentionnée précédemment. Comme le montre l'exemple suivant :

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

Troisièmement,  lorsqu'il y a beaucoup de boucles, essayez de conserver les propriétés et les méthodes en dehors de la boucle.  Lorsque vous réutilisez une valeur de propriété du même objet dans une boucle, vous pouvez d'abord attribuer la valeur de propriété à une variable spécifiée en dehors de la boucle, puis utiliser la variable dans la boucle, ce qui peut atteindre une vitesse plus rapide. Comme le montre l'exemple suivant :

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

Chaque boucle de cet exemple obtient la propriété Value de la cellule Cells(1,2). Si vous attribuez la propriété Value de Cells(1.2) à une variable avant le début de la boucle, vous obtiendrez une exécution plus rapide. Comme le montre l'exemple suivant :

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

Le code ci-dessus appelle  ThisWorkbook.Sheets("Sheet1")  à chaque boucle. Vous pouvez le faire plus rapidement en utilisant l'  instruction With  pour déplacer l'appel vers  ThisWorkbook.Sheets("Sheet1")  en dehors de la boucle. Comme le montre l'exemple suivant :

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

Évitez d'utiliser des types de variantes

Les débutants préfèrent généralement utiliser des variables de type Variant, ce qui a l'avantage d'être moins compliqué car n'importe quel type de données peut être utilisé sans problème de débordement de mémoire si les données sont trop volumineuses pour les types de données Integer ou Long. Cependant, les données de type Varienmt nécessitent plus d'espace mémoire supplémentaire que les autres types spécifiés (2 octets pour les données entières, 4 octets pour les données longues et 16 octets pour les données Variant). VBA nécessite plus de temps pour traiter les données de type Variant que les autres types spécifiés. de données. Comme le montre l’exemple suivant.

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

Dans le code ci-dessus, les lignes 8 à 13 effectuent 1 million d'opérations d'addition, de soustraction, de multiplication et de division de variables Variantes, et les lignes 17 à 22 effectuent 1 million d'opérations d'addition, de soustraction, de multiplication et de division de variables entières. Sur mon ordinateur, l'opération de la variable Variant a pris environ  0,09375  seconde, tandis que l'opération de la variable Integer a pris environ  0,03125  seconde. Les résultats peuvent varier d'un ordinateur à l'autre, mais  les variables Variant sont nettement plus lentes que les variables Integer .

Pour cette raison,  il est recommandé d'éviter d'utiliser des variables Variant lorsque vous pouvez utiliser explicitement le type de données spécifié .