Accélérer l'exécution de ses macros VBA sur Excel

Nous commençons ce billet en douceur avec la notion des bonnes pratiques de développement. Ces dernières feront surtout gagner du temps au niveau de la conception et de la réalisation du programme, tout en réduisant les risques d’erreur.

Déclarer les types des variables

Donner un type à une variable, c’est aussi lui donner une certaine taille en mémoire. Le but est de donner le type adéquate à la donnée. De cette manière sa taille sera fixe, ni trop grande, ni trop petite.

Private Sub Exemple_Typage_Variables()
    Dim uneVariable ' Variable non typée
    Debug.Print VarType(uneVariable) ' => 0 : Empty
    uneVariable = "Toto"
    Debug.Print VarType(uneVariable) ' => 8 : String
    
    Dim iUnEntier As Integer ' Variable typée
    iUnEntier = 100
    Debug.Print VarType(iUnEntier)  ' => 2 : Integer
    ' iUnEntier = uneVariable ' => Erreur d'exécution : incompatibilité de types
End Sub

Utiliser des variables

Ne lésinez pas sur les variables, en particulier lorsque les mêmes données reviennent régulièrement. Par exemple, une feuille de calcul utilisée à plusieurs endroits dans le code pourra être chargée une fois.

Option Explicit

Public Sub Exemple_Utilisation_Variables()
    Dim shPage1 As Worksheet
    Set shPage1 = Sheets("Feuil1")
    
    Dim dRecettes As Double, dDepenses As Double
    dRecettes = 12542.45
    dDepenses = 8572.1
    
    shPage1.Range("A1") = "Recettes"
    shPage1.Range("B1") = "Dépenses"
    shPage1.Range("C1") = "Bénéfices"
    
    shPage1.Range("A2") = dRecettes
    shPage1.Range("B2") = dDepenses
    shPage1.Range("C2") = dRecettes - dDepenses
End Sub

Utiliser des blocs With

Dans le même genre, le bloc With permet d’appliquer de multiples opérations à la suite à un élément en y accédant en une fois.

Public Sub Exemple_Utilisation_Blocs_With()
    With Worksheets("Feuil1")
        With .Range("A1:C1").Font
            .Bold = True
            .Size = 12
        End With
        
        With .Range("A1:C2").Borders
            .LineStyle = xlContinuous
            .ColorIndex = 23
            .Weight = xlThin
        End With
    End With
End Sub

Structurer son code

Portée des variables

Si une variable est utilisée à plusieurs endroits, il peut être intéressant de lui donner une portée plus large si ce n’est pas déjà fait afin de la déclarer qu’une seule fois (constantes, …).

Utilisation de procédures et fonctions

Découper son code permet de le rendre plus modulable, donc plus réutilisable, donc plus compréhensible aussi. Chaque procédure ou fonction doit avoir son propre rôle.

Complexité algorithmique

Il existe un domaine de l’informatique pour évaluer la complexité algorithmique. Parfois, la lenteur vient de l’algorithme ! Par exemple, il convient d’éviter d’imbriquer plus de deux boucles ensemble.

Public Sub Exemple_Complexite_Algorithmique()
    Dim i As Integer, j As Integer, k As Integer
    For i = 0 To 10000
        For j = 0 To 10000
            For k = 0 To 10000
                ' à éviter !
            Next k
        Next j
    Next i
End Sub

Au cours de cette première section, nous avons vu qu’en utilisant des variables typées, en intégrant des blocs With et en organisant notre code, nous pouvions gagner du temps à l’exécution, mais surtout à l’écriture de nos programmes.

Dans cette seconde section, nous allons nous intéresser aux paramètres de l’application qui nous permettront de gagner du temps dans certains cas.

Les options de calcul des formules

Vous connaissez peut-être les options de calcul des formules dans l’onglet "Formules".

En mode automatique, les formules sont recalculées à chaque changement dans les dépendances.

Imaginons une formule faisant la somme d’une colonne. Si au cours du programme, des données sont ajoutées, modifiées ou supprimées dans cette colonne, la formule sera recalculée à chaque fois. Parfois, il est donc judicieux de passer en mode manuel le temps du programme afin de gagner en performance. Cela se fait avec la propriété Calculation de l’objet Application.

Public Sub Exemple_CalculationMode()
    Dim iCalculation As Integer
    iCalculation = Application.Calculation ' Sauvegarde du paramètre initial

    Application.Calculation = xlCalculationManual ' Mode manuel
    
    ' Traitement ...
    
    Application.Calculation = iCalculation ' Rétablissement du paramètre initial
End Sub

Voici un récapitulatif des valeurs possibles pour Calculation :

L’option de mise à jour de l’écran

Par défaut, les opérations du programme se déroulent sous nos yeux. Si le programme insère des centaines voire des milliers de lignes, nous les verrons être écrites au fur et à mesure.

Pour désactiver la mise à jour de l’affichage le temps du programme, il est possible de jouer sur le paramètre ScreenUpdating de l’objet Application.

Public Sub Exemple_ScreenUpdating()
    Application.ScreenUpdating = False ' Désactivé
    
    Dim shPage2 As Worksheet
    Dim i As Integer
    
    Set shPage2 = Sheets("Feuil2")
    For i = 1 To 10000
        shPage2.Cells(i, 1) = i
    Next i
    
    Application.ScreenUpdating = True ' Activé
End Sub

En reprenant notre exemple, le résultat sera que les lignes apparaîtront en un coup à l’écran.

L’option des alertes

Selon les instructions, il arrive que l’exécution de macros entraîne l’ouverture de boîtes d’alerte. Ce qui est gênant, c’est que celles-ci bloquent la continuation du programme dans l’attente d’une réponse humaine.

Si vous êtes sûr qu’il n’y a rien de dangereux dans votre programme, vous pouvez désactiver celles-ci avec la propriété DisplayAlerts de l’objet Application.

Public Sub Exemple_DisplayAlerts()
    Application.DisplayAlerts = False ' Désactivé
    
    ' Traitement ...
    
    ' Application.DisplayAlerts = True ' Remis automatiquement à True à la fin
End Sub

Au cours de cette section, nous avons vu que nous pouvions désactiver le calcul automatique des formules, la mise à jour de l’affichage et l’affichage des alertes pendant l’exécution de nos macros, en fonction de nos besoins afin de réduire de façon importante le temps d’exécution.

Dans cette dernière section, nous allons voir qu’il est préférable de travailler sur les plus grands ensembles de données possibles tant en lecture qu’en écriture.

Lire ou écrire une ligne d’un tableau d’un coup est plus rapide que de lire ou écrire celle-ci cellule par cellule. De même, lire ou écrire un tableau par bloc de X lignes est plus rapide que de lire ou écrire celui-ci ligne par ligne. De même, lire ou écrire un tableau d’un coup est plus rapide que lire ou écrire celui-ci par bloc de X lignes.

En VBA, les variables de type Array nous aideront à effectuer cela.

Lecture des données d’un coup

Imaginons que nous ayons un tableau de 10000 lignes contenant le numéro de la ligne.

Nous pouvons lire l’intégralité de celui-ci d’un coup comme ceci :

Public Sub Exemple_Lecture_Donnees()
    Dim shPage4 As Worksheet
    Set shPage4 = Sheets("Feuil4")
    
    Dim loTableau1 As ListObject
    Set loTableau1 = shPage4.ListObjects("Tableau1")
    
    Dim vDonnees As Variant
    vDonnees = loTableau1.DataBodyRange.Value
    Debug.Print (VarType(vDonnees)) '=> 8204: Tableau (8192) de variant (12)
End Sub

Écriture des données d’un coup

Voici un exemple VBA écrivant l’intégralité d’un tableau d’un coup :

Public Sub Exemple_Ecriture_Donnees()
    Application.ScreenUpdating = False
    
    ' Construction du tableau 2D
    Dim vDonnees(9999, 2) As Variant
    Dim i As Long
    For i = LBound(vDonnees, 1) To UBound(vDonnees, 1)
        vDonnees(i, 0) = i + 1
        vDonnees(i, 1) = 9999 - i
    Next i
    
    Dim shPage5 As Worksheet
    Set shPage5 = Sheets("Feuil5")
    Dim loTableau2 As ListObject
    Set loTableau2 = shPage5.ListObjects("Tableau2")
    ' Suppression contenu tableau
    If Not loTableau2.DataBodyRange Is Nothing Then
        loTableau2.DataBodyRange.Rows.Delete
    End If
    
    ' Écriture du tableau d'un coup
    loTableau2.ListRows.Add.Range.Resize(UBound(vDonnees, 1), UBound(vDonnees, 2)).Value = vDonnees
    
    Application.ScreenUpdating = True
End Sub

Pendant cette section, nous avons vu qu’il est plus intéressant en terme de performance de travailler sur de grands ensembles de données en lecture et écriture lorsque c’est possible.