L'optimalisation de la vitesse des macros VBA est d'une importance capitale pour le bon fonctionnement de vos programmes. Plus une macro est rapide, plus le travail est efficace. De plus, une macro (trop) lente peut être contre-productive ou induire l'utilisateur en erreur s'il pense que la macro est "bloquée".
Optimiser les macros Excel en testant leur vitesse
Pour pouvoir optimiser une macro / un script VBA, il est donc nécessaire de pouvoir mesurer sa vitesse. C'est assez simple et quelques lignes de code suffisent. En fait, il suffit de stocker l'heure exacte de début et l'heure exacte de la fin de la macro dans une variable et les comparer à la fin. Cela vous donnera le temps exact que la macro a pris pour tourner.
Voici un exemple pratique de code VBA qui permet de voir la vitesse de la macro.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub TesterLaVitesseDeMacro() 'par Excel-Malin.com ( https://excel-malin.com ) On Error GoTo Erreur Dim MacroDebut As Date 'stocker le moment de début MacroDebut = Now '-------------------------- ' VOTRE CODE VBA '-------------------------- 'comparer le début & la fin et afficher le résultat MsgBox "Durée d'exécution: " & Format(Now - MacroDebut, "hh:mm:ss") Exit Sub Erreur: MsgBox "Une erreur est survenue..." End Sub |
Vitesse des macros VBA: vitesse partielle pour les différentes parties du code VBA
Connaître la durée totale de l'exécution d'une macro n'est que le début. Il est évidemment possible de mesurer la vitesse de différentes parties du code de la macro et trouver ainsi la partie qui, éventuellement, ralenti toute la procédure. Une fois la partie du code la plus lente identifiée, vous pourrez vous concentrer à son optimisation.
Car selon la Loi de Pareto, 80% du temps d'exécution de la macro est causé par 20% du code… Si vous identifiez le "goulot d'étranglement", la correction peut augmenter exponentiellement la vitesse de votre macro VBA.
Code VBA pour déterminer la vitesse des blocs du code
Le code VBA qui suit va vous indiquer la durée nécessaire à l'exécution de différentes parties d'une macro.
Pour exemple, le code VBA qui suit est séparé en trois parties dont la durée d'exécution est calculée séparément. Bien sûr, vous pouvez séparer le code en autant de partie que vous voulez.
Il suffit de stocker le moment de la fin de chaque étape (comme: MacroEtape8 = Now ) dans une variable de type Date et à la fin, calculer autant de fois la durée de chaque étape (= le moment de la fin d'une étape – le moment de la fin de l'étape précédente; par ex. MacroEtape8_duree = Format(MacroEtape8 - MacroEtape7, "hh:mm:ss").
Vous pouvez choisir le format de la durée à afficher selon votre convenance. J'ai opté pour le format hh:mm:ss…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Sub DureeExecutionMacroParEtape() 'par Excel-Malin.com ( https://excel-malin.com ) On Error GoTo Erreur Dim MacroDebut As Date Dim MacroEtape1, MacroEtape2, MacroEtape3 As Date Dim MacroFin As Date Dim MacroEtape1_duree, MacroEtape2_duree, MacroEtape3_duree, MacroTotal_duree 'stocker le moment de début MacroDebut = Now '--------------------------------------------- 'première partie de votre code VBA '... '--------------------------------------------- MacroEtape1 = Now '--------------------------------------------- '2ème partie de votre code VBA '... '--------------------------------------------- MacroEtape2 = Now '--------------------------------------------- '3ème partie de votre code VBA '... MacroEtape3 = Now '--------------------------------------------- 'la fin de l'exécution MacroFin = Now 'calcul & mise en forme des durées des étapes MacroEtape1_duree = Format(MacroEtape1 - MacroDebut, "hh:mm:ss") MacroEtape2_duree = Format(MacroEtape2 - MacroEtape1, "hh:mm:ss") MacroEtape3_duree = Format(MacroEtape3 - MacroEtape2, "hh:mm:ss") MacroTotal_duree = Format(MacroFin - MacroDebut, "hh:mm:ss") 'affichage des résultats MsgBox _ "Durée partie 1: " & MacroEtape1_duree & Chr(10) & _ "Durée partie 2: " & MacroEtape2_duree & Chr(10) & _ "Durée partie 3: " & MacroEtape3_duree & Chr(10) & Chr(10) & Chr(13) & _ "Durée totale: " & MacroTotal_duree Exit Sub Erreur: MsgBox "Une erreur est survenue..." End Sub |
Petite remarque pratique: le message final va s'afficher en plusieurs lignes. Voici deux tutos pratiques qui pourraient bien vous servir: Continuer le code VBA sur une nouvelle ligne et VBA: retour à la ligne, formater un MsgBox
Vitesse des macros VBA: quelle précision?
Avec le code VBA indiqué plus haut, vous pouvez voir la durée d'exécution de vos macros avec la précision d'une seconde. Ce sera suffisant pour la plupart des macros – disons celles dont le temps d'exécution dépasse 10 secondes.
Si votre macro est plus courte ou si vous avez besoin de l'optimiser au maximum, je vous conseille de mesurer la durée avec la précision en millisecondes. Comment faire? Vous trouverez les informations nécessaires dans le tutoriel Comment obtenir le temps en millisecondes en VBA?
Conclusion
J'espère que cette petite astuce vous permettra de rendre vos macros encore plus rapides et encore plus efficaces!
Si vous voulez aller encore plus en profondeur, vous pouvez par exemple exporter les informations vers un fichier texte (créer un log) plutôt que de les afficher dans une fenêtre pop-up (MsgBox). Cela vous permettrait de 1) stocker les résultats de manière durable & 2) ne pas interrompre l'exécution de la macro par l'utilisation du MsgBox (même s'il est possible de créer des MsgBox qui se ferment automatiquement)… Si vous ne savez pas comment créer un fichier journal ou comment exporter un texte vers un fichier, vous pouvez utiliser le code prêt à l'emploi qui se trouve dans VBA: Sauvegarder un texte dans un fichier.
Pour aller plus loin en VBA
Et pour terminer, voici quelques articles qui pourraient vous intéresser et vous servir dans votre travail avec VBA…
- Liste de toutes les fonctions VBA
- Les Événements disponibles en VBA
- RECHERCHEV en VBA
- VBA: gestion des dates et des heures
- Calculer une somme en VBA
- Ouvrir un fichier de tout type par VBA
