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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - accelerer vba - 01

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 :

Excel formation - accelerer vba - 02

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 

Excel formation - accelerer vba - 03

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 :

Excel formation - accelerer vba - 04

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

Excel formation - accelerer vba - 05

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 :

Excel formation - accelerer vba - 06

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

Excel formation - accelerer vba - 07

 



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.