VBA: Rédiger des macros plus rapides

Il y a de nombreuses façons d’optimiser le code VBA pour Excel afin d’améliorer la performance et, conséquemment, le temps d’exécution.

On peut regrouper ces trucs en 2 grandes catégories:

Les astuces de programmation qui ne dépendent pas réellement de la façon dont le code est bâti, mais plutôt de la compréhension du fonctionnement du VBA pour Excel;
Les astuces liés à la structure du programme lui-même. Ici, on pense à du code qui est structuré de telle manière que son exécution sera plus ou moins longue.

Astuces de programmation

Mettre à Off tout ce qui peut être mis à Off!

Le premier truc, que bien des gens connaissent, est d’utiliser VBA afin d’éviter d’utiliser des fonctionnalités Excel qui prennent du temps d’exécution, l’exemple classique étant la mise à Off du rafraîchissement de l’écran en cours d’exécution. En effet, qui veut voir les écrans se promener en fou pendant que ça roule! Et ceux qui maîtrisent ce simple truc remarquent que cela accélère de beaucoup l’exécution du code VBA.

Cela étant dit, il y a bien d’autres fonctionnalités qui peuvent être modifiées afin d’accélérer l’exécution du code VBA. Voici donc les principales:

Tourner à Off le rafraîchissement de l’écran: Comme expliqué plus haut, l’interaction avec l’écran gaspille beaucoup de temps et devrait être mis à OFF.
Tourner à Off la mise à jour de la barre de statut: À chaque instant, Excel met à jour la barre de statut (la barre qui est affichée au bas de l’écran). Même si cette interaction avec l’écran est minimale, mettre ce rafraîchissement à OFF va sauver un peu de temps d’exécution.
Gérer les options de calcul: Il existe 3 modes de calcul en Excel: Automatique, où tout est recalculé à chaque fois qu’une cellule est modifiée, Automatique sauf les tables de données, où tout est recalculé à l’exception des tables de données, ce qui peut déjà sauver du temps! et le mode Manuel, où aucun calcul n’est mis à jour tant que l’usager n’en fait pas la demande en appuyant sur F9. Si le code VBA interagit souvent en modifiant des cellules dans le fichier Excel (ce qui est habituellement le but!), un mode de calcul automatique peut consommer beaucoup de ressources pendant l’exécution car Excel recalcule souvent l’ensemble du chiffrier. En général, il est donc souhaitable de mettre l’option de calcul à Manuel et de forcer le recalcul, dans le code, aux instants appropriés en cours d’exécution de la macro VBA. Également, lorsque le code s’exécute, évitez d’avoir de nombreux fichiers Excel ouverts qui ne sont pas liés à votre code VBA, car un recalcul va se faire sur l’ensemble des fichiers qui sont ouverts.
Gérer les événements: Excel interagit avec l’environnement Office et gère la relation avec des compléments (des add-in). Ces fonctionnalités surveillent donc en permanence la venue d’événements qui les impliquent et des messages peuvent même s’afficher qui demandent la participation de l’usager. Par exemple, si votre code VBA ouvre un fichier Excel pour en extraire des données et que, en cours d’exécution, Excel détecte que le fichier a été modifié, si vous fermez ce fichier dans le code VBA, Excel va vous demander si vous voulez sauvegarder les modifications. En mettant la gestion des événements à Off, cette question ne vous sera pas posée et Excel ne surveillera pas la venue de tels événements, économisant ainsi du temps d’exécution;
Tourner à Off le recalcul des sauts de page: Finalement, quand une feuille est modifiée, Excel recalcule les sauts de page. Afin d’éviter que du temps d’exécution soit consacré à cette tâche, il faut donc mettre à Off le traitement des sauts de page. Il est à noter que cette configuration devrait être traitée dans chaque feuille concernée! Si de nombreuses feuilles ne sont que légèrement modifiées, la gestion du recalcul des sauts de page peut donc devenir laborieuse. Pour cette raison, je ne l’utilise pas moi-même. Toutefois, si une seule feuille est grandement modifiée par l’exécution du code VBA, il devient intéressant de l’utiliser.

Comment faire?

Dans le code VBA, il faut donc, au début d’une procédure, conserver le statut de ces différentes configurations pour ensuite les forcer à OFF. Et, à la fin de la procédure, on les remet dans leur état initial.

Donc, votre code VBA devrait ressembler à:

Paramètres d’affichage

La déclaration explicite des variables

VBA est un des rares langages de programmation qui ne requiert pas la déclaration des variables. Donc, vous pouvez programmer à la qui-mieux-mieux sans vous soucier de la définition des variables utilisées. Cette façon de programmer en VBA crée un risque très important, dont voici un excellent exemple. Supposons que vous utilisez la variable dSomme afin de calculer un résultat intermédiaire dans votre procédure. Et cette variable dSomme apparaît sur plusieurs lignes de programmation. Supposons maintenant que, à un endroit, vous vous trompez et que vous passez dSommes (avec un s à la fin) à la place dans un calcul. Comme VBA voit la variable dSommes pour la première fois, il lui assigne la valeur 0, donc votre calcul ne sera pas bon.

Afin d’éviter ces ennuis, il est donc grandement préférable de travailler en mode de déclaration explicite des variables. Quand une variable est déclarée en VBA, on peut ne déclarer que son nom ou son nom et son type. Cette deuxième approche est préférable.

En effet, la déclaration du type évite à la procédure d’assigner un type à chaque fois que la variable est utilisée. Si vous avez une procédure itérative qui utilise 5 variables et qui génère 50 000 itérations, la procédure doit se questionner 250 000 fois sur le type de variable à utiliser!

Afin de vous assurer de bien suivre cette règle, dans un module, avant toute fonction ou procédure, ajouter l’instruction:

Option Explicit

qui va forcer la déclaration explicite des variables.

Astuces de structure du code

Écrire et lire dans les feuilles en bloc

Il arrive que le travail de la procédure en VBA soit de lire et de modifier le contenu de cellules. À chaque fois que le code VBA fait une requête de lecture ou d’écriture dans la feuille, cela demande un certain temps d’exécution. En conséquence, il est judicieux de lire une plage de données complète en une seule opération en la chargeant dans une variable de type tableau. Puis, lorsque les opérations sont effectuées, de reporter les résultats en transcrivant tout le tableau d’un seul coup.

À titre d’exemple, regardons le cas suivant qui lit des données (et non des formules) dans la plage A1:C10000. Dans un premier temps, la version non-optimisée est présentée. Cette version lit les cellules une à une:

Lecture cellule par cellule

Ce code, même s’il va bien fonctionner, n’est pas la façon la plus optimale de programmer. Une façon beaucoup plus rapide consiste à lire et copier toute la plage de cellule en une seule étape vers une variable de type tableau. Ensuite, de modifier les valeurs, le cas échéant, des variables contenues dans le tableau et, finalement, de transcrire le tableau dans la feuille Excel. Donc, en tout: une opération de lecture et une d’écriture, alors que la méthode précédente avait 30 000 opérations de lectures et un grand nombre d’opérations d’écriture selon les valeurs des variables.

Le code serait plutôt alors:

Lire les cellules d’un coup

Le fait de minimiser l’interaction entre le feuille et le code VBA améliore beaucoup la performance. Faites le test et vous verrez que la différence est hallucinante!

Éviter de sélectionner ou d’activer des objets

Le fait de sélectionner ou d’activer des objets requiert du temps d’exécution. Si le nombre d’objets auxquels on doit se référer est grand, il est préférable d’y accéder par référence au lieu de les sélectionner.

Par exemple, si on voulait changer le texte contenu dans toutes les Formes d’une feuille, on peut le faire en les sélectionnant à tour de rôle, puis en en changeant ensuite le texte. Ou on réfère à l’objet afin de changer directement le texte. Dans le premier cas, qui génère un temps d’exécution plus élevé, la portion du code VBA serait la suivante:

Objets traités par sélection

Le code suivant fait le même travail, mais en se référant aux objets, sans les sélectionner, ce qui sauve du temps:

Objets traités par référence

Personnellement, si le nombre d’objets est petit, une sélection explicite ne me dérange pas et je trouve que le code est plus clair, surtout quand il s’agit d’objets tels des feuilles, des range ou des fichiers. Tel que mentionné, lors d’opérations répétitives, la référence, au lieu de la sélection, représente toutefois un gain à ne pas négliger.

Utiliser des fonctions créées en VBA

Si vos procédures font des calculs itératifs, il peut être avantageux de créer des fonctions en VBA afin de faire des bouts de calculs, même si ces fonctions sont déjà définies dans Excel et peuvent être appelées dans la procédure. En effet, sortir du code VBA afin d’appeler une fonction Excel prend du temps d’exécution. Toutefois, si la fonction est complexe à programmer, peut-être vaut-il mieux prendre celle d’Excel, qui fonctionne bien! Le jugement du programmeur est important à ce niveau!

En conclusion

En conclusion, la première décision est de faire ou non du code VBA. En effet, est-ce bien requis? On voit bien des cas où des procédures VBA ont été mises en place, alors que de simples fonctions et fonctionnalités d’Excel auraient été suffisantes pour les besoins. Car en effet, des procédures VBA vont éventuellement avoir besoin de maintenance et d’évolution et requièrent donc que l’expertise soit disponible à cette fin. Donc, si on peut éviter du code VBA, on l’évite.

Cela étant dit, tant qu’à faire du code VBA, il faut s’assurer que cela soit fait d’une manière efficace. Conséquemment, les trucs et astuces montrés ici s’avéreront utiles.

NOTRE OFFRE DE FORMATIONS

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com