Comment accélérer vos macros VBA sur Excel (jusque 25x plus rapide) ?

Dans ce tutoriel, nous allons découvrir les principales techniques d’optimisations qui vous nous permettre de booster les macro-commandes VBA afin de pouvoir les exécuter beaucoup plus rapidement. Nous y verrons ainsi qu’en utilisant ces quelques techniques une même macro pourra demander 25 fois de temps pour être exécutée.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour découvrir les différentes techniques d’optimisation du code VBA de ce tutoriel, nous allons partir d’un exemple de macro très simple.

Celle-ci se contente en effet d’utiliser les résultats contenus dans deux cellules pour déterminer par multiplication le résultat à saisir dans une troisième cellule.

Cette macro est très simple en l’état, mais pour que les résultats rendus soient suffisamment significatifs, nous les répèterons 100 fois !

Ensuite, pour estimer l’impact de chaque technique, nous repartirons à chaque fois d’une même base pour n’y effectuer qu’une unique modification.

Voici la macro commande de base :

Sub calcul()
    
    timerdebut = Timer
 
    For i = 1 To 100
    
        [b7].Select
        valeurA = Selection.Value
        
        [b8].Select
        valeurB = Selection.Value
        
        [b9].Select
        Selection = valeurA * valeurB
    
    Next
    
    MsgBox "Durée : " & (Timer - timerdebut)  & " sec."
    
End Sub

Nous lançons ensuite la macro pour découvrir le temps nécessaire à Excel pour réaliser ces 100 calculs :

Ici, il faut 4,77 secondes, mais évidemment ce résultat est fortement dépendant des caractéristiques du poste de travail sur lequel la macro est exécutée.

2. Stopper les calculs

La première méthode d’optimisation que nous allons découvrir consiste simplement à stopper les calculs automatiques.

De cette manière, nous n’effectuerons pas 100 fois le calcul demandé, mais une fois seulement en toute fin de macro en remettant les calculs automatiques.

Pour passer les calculs en mode manuel à partir de VBA, nous utilisons la propriété Application.Calculation à laquelle nous donnons la valeur xlCalculationManual :

Application.Calculation = xlCalculationManual

Une fois les calculs terminés, nous repassons en mode automatique en lui attribuant la valeur xlCalculationAutomatic :

Application.Calculation = xlCalculationAutomatic

Ici, les résultats sont spectaculaires :

Avec 1,83 seconde, nous avons divisé le temps d’exécution par 2,6 !

En revanche, lorsque nous désactivons les calculs, nous devons nous assurer que nous n’aurons pas besoin d’utiliser des données provenant d’une cellule qui ne serait alors plus à jour, sous peine d’utiliser des informations erronées.

3. Désactiver le rafraîchissement de l’écran

Lors de chaque modification effectuée sur la feuille, Excel doit faire apparaître celle-ci, ce qui demandera évidemment des ressources.

Pour désactiver le rafraîchissement, nous utilisons la propriété Application.ScreenUpdating.

Lorsque celle-ci est égale à False, le rafraîchissement est désactivé, nous lui attribuons ensuite la valeur True pour le réactiver :

    Application.ScreenUpdating = False
…    
    Application.ScreenUpdating = True 

Si le résultat est bien moins spectaculaire ici, celui-ci en reste toutefois non négligeable avec près de 30% du temps d’exécution économisé.

4. Inutile de sélectionner les cellules

La sélection des cellules est évidemment consommatrice de ressources.

D’autant plus que celle-ci est à de très rares exceptions prêtes totalement inutile.

Nous pouvons en effet effectuer le calcul en une seule ligne, sans même avoir à sélectionner les cellules :

    For i = 1 To 100
    
        [b9] = [b7] * [b8]
    
    Next

Une fois encore, les temps d’exécution sont fortement réduits :

Plus d’une seconde d’économisée.

5. Déclarer les variables

Lorsque nous souhaitons optimiser au maximum l’utilisation de la mémoire, il est important de bien déclarer les variables utilisées.

Nous avons déjà eu l’occasion de découvrir dans un tutoriel précédent que le type d’une variable permet de spécifier les informations que celle-ci sera capable de gérer.

Ici, le but sera de déterminer la valeur maximale pouvant être affectée aux variables pour réduire au maximum l’utilisation de la mémoire.

En effet, lorsque nous ne déclarons pas explicitement le type de la variable, VBA se contentera de lui donner le type Variant.

Il s’agit d’un type très pratique car nous pouvons l’utiliser pour manipuler tous types de variables, mais la contrepartie logique de cette polyvalence c’est qu’il s’agit du type de variable consommant le plus de ressource en termes de mémoire.

Pour commencer, nous pouvons déclarer les variables suivant les types utilisés :

Dim timerDebut As Double, i As Long, valeurA As Double,  valeurB As Double

Ici, même si le gain est bien présent, il n’est pas extraordinaire.

La raison vient de fait que nous manipulons des variables de types Double, qui sont des nombres en virgule flottante, gros consommateurs de ressources.

La marge de manœuvre est donc très réduite. En revanche, si nous avions à faire à un programme plus complexe avec des dizaines ou des centaines de variables, choisir le bon type pourra avoir un impact bien plus significatif.

J’en profite pour rappeler qu’il est toujours intéressant de mettre en place l’Option Explicit en haut de chaque module pour s’assurer que toutes les variables sont systématiquement déclarées de manière explicite.

6. Utiliser toutes ces techniques

Voilà, maintenant que nous avons découvert les principales techniques d’optimisation du code, l’idée pour cela sera alors de mettre en place chacune d’elles, et non pas seulement une.

Les résultats seront alors bien plus spectaculaires :

La macro s’exécute en effet 25 fois plus rapidement !