Comme dans d’autres langages, il existe de nombreuses façons de coder en VBA. Des bonnes et des moins bonnes…
Si vous êtes développeur VBA débutant, vérifiez si vous mettez déjà en œuvre ou non les 6 bonnes pratiques décrites dans cet article.
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)
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 identifier les parties du code directement exécutables par l’utilisateur, et celles qui restent privées.
Dans un projet assez conséquent, il vous faudra diviser votre code VBA en fonctions (macros) pour le structurer correctement.
Mais attention, par défaut, toutes les fonctions sont visibles dans la fenêtre des macros et peuvent donc être exécutées directement par l’utilisateur. Or ce n’est généralement pas ce qu’on souhaite. Il faut au donc rendre privées toutes les fonctions qu’on ne souhaite pas rendre visibles à l’utilisateur.
Dans l’exemple de code ci-dessous, la fonction marquée Public est reliée à un bouton et peut être appelée de l’extérieur. En revanche, la fonction marquée Private peut être appelée par la fonction publique, mais n’est pas visible de l’extérieur. Cela fait partir d’un principe de programmation qu’on appelle « l’encapsulation ».
L’idée est donc de rendre privées toutes les fonctions qui ne doivent pas apparaître dans la fenêtre des macros, ni être directement exécutables par des boutons.
Créer des interfaces visuelles les plus simples possibles
Une interface simple a 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 directement 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 facilement atteindre 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. Croyez-moi, pour l’utilisateur cela fait une grosse différence !
L’exemple de code ci-dessous montre comment charger en mémoire le contenu d’un tableau contenant des pays et leurs capitales. Les données sont par défaut récupérées sous forme d’un tableau à 2 dimensions (type Array du VBA). Mais pour les manipuler plus facilement, je les transforme en une collection (classe Collection du VBA) d’objets de type CPays (classe toute simple non représentée ici) :
Pour en savoir plus sur la façon de créer des listes et dictionnaires en VBA avec la classe Collection, je vous conseille cet article.
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.
Par exemple, pour implémenter des listes ou dictionnaires, utilisez la classe Collection plutôt que la classe Scripting.Dictionary, qui est spécifique à Windows. De même, pour manipuler les fichiers et dossiers, utilisez les méthodes standard du VBA.
Conclusion
Je n’ai fait ici que présenter brièvement quelques bonnes pratiques pour développeur VBA, sans trop entrer dans les détails du code, car il faudrait pour cela un livre entier. Mais dites-moi en commentaire si le sujet vous intéresse et si vous aimeriez avoir plus d’articles, voire une formation complète sur le développement VBA pour Excel.
Devenez rapidement opérationnel sur Excel avec Excel Efficace
