De même qu’il y a le bon chasseur et le mauvais chasseur, il y a le bon développeur et le mauvais développeur VBA 🙂
Le premier est le bidouilleur du dimanche qui essaie tant bien que mal d’automatiser quelques traitements en VBA, mais qui ne connaît pas les bonnes pratiques. Alors il crée des classeurs avec des interfaces visuelles que lui seul peut comprendre, et il est tout content lorsque ses macros mettent moins de 10 secondes à s’exécuter…;-)
S’il vient à modifier un peu les feuilles de son classeur, plus rien ne marche et il est obligé de réviser tout son code. Ça lui prend des heures, et encore, il n’est pas improbable que certains problèmes lui aient échappé.
Mais franchement on ne peut pas lui jeter la pierre, car ce n’est pas évident d’apprendre tout seul, et devenir un vrai développeur VBA demande du temps et des efforts.
Vous pensez appartenir à cette première catégorie ?
Gasp ! Pas de panique.
Dans cet article, je vais partager avec vous 6 bonnes pratiques clés pour rejoindre la catégorie des bons développeurs VBA 🙂
Bien structurer ses classeurs Excel
Depuis plusieurs années, j’ai une structure de base type pour mes classeurs Excel, avec quelques variantes pour m’adapter aux besoins spécifiques. Cette structure me permet à la fois de :
- Rendre les classeurs simples et compréhensibles pour l’utilisateur final
- Faciliter la structuration de mon code VBA
Elle consiste à créer les feuilles suivantes :
- Une feuille Accueil qui décrit le classeur et la façon de l’utiliser
- Une feuille contenant tous les paramètres, qu’il s’agisse de tableaux servant à alimenter des listes déroulantes, d’abaques ou de paramètres individuels.
- Une feuille contenant les données saisies ou importées
- Une ou plusieurs feuilles pour l’analyse des données (via des tableaux croisés, des graphiques générés éventuellement par macros)
Vous pouvez par exemple retrouver une structure proche dans le classeur Excel de suivi de consommation électrique décrit dans cet article :
- On retrouve la feuille Accueil
- Les paramètres sont dans la feuille Tarifs qui contient tous les tarifs selon les options de base ou heure pleine/heure creuse, ainsi que les différentes taxes.
- Les données sont dans les feuilles Relevés base et Relevés HP-HC, dans lesquelles on peut saisir manuellement ses relevés de compteurs.
- L’analyse est faite dans les mêmes feuilles que les relevés au moyen de graphiques
Cette structure est souple et s’adapte à beaucoup de situations.
Si le classeur contient du code VBA, selon sa complexité, j’adopte la même structure pour le code, c’est-à-dire qu’à chaque feuille correspond un module de code.
Rendre accessible uniquement ce qui doit l’être
Bien définir quelles sont les parties de code directement exécutables par l’utilisateur, et celles qui ne le sont pas.
Dans un projet assez conséquent, il vous faudra diviser votre code VBA en fonctions (macros) pour le structurer correctement.
Mais si vous ne faites rien, par défaut toute fonction est visible dans la fenêtre des macros, et peut donc être exécutée directement par l’utilisateur. Or ce n’est généralement pas ce qu’on souhaite.
Dans l’exemple de code ci-dessus, la fonction marquée Public est reliée à un bouton et peut être appelée de l’extérieur, tandis que la fonction marquée Private n’est pas visible de l’extérieure, mais peut être appelée par la fonction publique. Cela fait partir d’un principe de programmation qu’on appelle « l’encapsulation ».
L’idée est donc de rendre privé (c’est-à-dire inaccessible de l’extérieur) toutes les fonctions qui ne doivent pas être accessibles de l’extérieur via la fenêtre des macros ou des boutons.
Créer des interfaces visuelles les plus simples possibles
Ceci à 2 avantages :
- L’application est plus simple à comprendre par l’utilisateur
- Moins il y a de boutons à cliquer, moins il y a de problèmes potentiels liés notamment à des enchaînements d’actions non prévus
Attention, simplicité n’est pas forcément synonyme de facilité. Créer des choses simples à utiliser demande souvent beaucoup de réflexion.
Pour un classeur contenant des macros VBA, l’ajout de boutons dans les feuilles n’est pas toujours suffisant. Il faut quelquefois créer des boîtes de dialogue.
L’exemple ci-dessus montre une interface visuelle que j’ai créée pour la comparaison de tableaux de données à l’intérieur d’une feuille, selon la technique décrite dans cet article.
Utiliser le moins possibles d’adresses en dur dans le code VBA
Je veux parler d’adresse de cellules du style A3, B6…. Car plus votre code comporte de telles références, moins il s’adapte aux changements. En effet, si vous êtes amené à ajouter des lignes ou des colonnes dans la feuille, les références dans le code devront certainement être modifiées pour que le code continue de fonctionner. C’est compliqué à maintenir et source de nombreux problèmes de fiabilité.
Il existe tout un ensemble de techniques en VBA pour n’utiliser quasiment aucune référence en dur. Votre code s’adapte ainsi sans problème aux déplacements de cellules et autres ajouts de lignes et colonnes.
Travailler sur des données chargées en mémoire et non sur les cellules
Cette pratique concerne les performances.
En effet, pour optimiser drastiquement les performances de votre code VBA, vous devez faire le moins possibles de traitements directement sur les cellules, et au contraire le plus possible sur des valeurs chargées en mémoire.
Le gain est absolument considérable !
Il peut être facilement d’un facteur 100 selon le volume de données à traiter, c’est-à-dire que la macro s’exécutera en 1/10 de seconde au lieu de 10 secondes par exemple.
Mais pour faire cela, il faut connaître finement les techniques de chargement et de stockage de données en mémoire, par exemple à l’aide de collections d’objets ou de dictionnaire.
Eviter les classes spécifiques à Windows
Pour simplifier, une classe est un morceau de code fourni par un tiers (Microsoft par exemple), que vous pouvez utiliser dans votre propre code VBA au lieu de réinventer l’eau chaude.
En n’utilisant que les classes fournies par Excel, et pas celles fournies par Windows, vous assurez la compatibilité de vos classeurs avec macOS !
Ce point est loin d’être négligeable si vous distribuez votre classeur à d’autres personnes.
Mais le problème est qu’il est quelquefois plus facile de faire certaines choses avec des classes spécifiques à Windows qu’avec celles d’Excel. Notamment manipuler les fichiers et dossiers, ou encore stocker des données dans un dictionnaire, qui sont pourtant des tâches très courantes.
Mais en apprenant à bien connaître et utiliser les classes d’Excel, vous pourrez tout-à-fait vous débrouiller sans les classes de Windows et ainsi garder la compatibilité avec macOS.
Et si vous développez en VBA directement sur macOS, vous n’avez de toutes façons pas le choix !
En synthèse
Vous voyez que développer sous Excel dans les règles de l’art ne s’improvise pas, et que les sources de problèmes potentiels sont nombreuses si vous ne connaissez pas les bonnes pratiques ni les techniques pour les mettre en œuvre.
Maintenant dites-moi en commentaire :
Que vous inspirent ces bonnes pratiques ?
Aimeriez-vous apprendre le VBA ?