Les 6 bonnes pratiques du bon développeur VBA pour Excel

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 peut 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é.

Pauvre développeur 🙁 On ne peut pas lui en vouloir, car ce n’est pas forcément évident d’apprendre tout seul, et devenir un vrai développeur VBA demande du temps et des efforts.

Vous pensez appartenir à la 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 :

Excel Srtucture classeur suivi conso elec
  • 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
Cet article devrait aussi vous intéresser :  Maîtrisez la classe Collection en VBA

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.

Développement VBA Excel fonctions publiques et privées

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 possible

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.

VBA Excel user form

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.

Cet article devrait aussi vous intéresser :  Rechercher dans un tableau avec la fonction EQUIV d’EXCEL

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.

Développement VBA Excel code spécifique Windows
Exemple de code VBA Excel utilisant une classe spécifique à Windows

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.

C’est pourquoi j’ai décidé de partager mon expérience de développeur professionnel, notamment en VBA Excel dans une formation originale qui allie technique, pragmatisme et pédagogie.

Si vous ne connaissez pas du tout le VBA, elle vous permettra de vous former en appliquant directement les bonnes pratiques, ce qui vous fera gagner un temps considérable.

Si vous faites déjà un peu de VBA dans la catégorie « bidouilleur », cette formation vous permettra de changer de catégorie 😉

Maintenant dites-moi en commentaire :

Que vous inspirent ces bonnes pratiques ?
Aimeriez-vous les découvrir plus en détail ?

4.2 5 votes
Notez cet article
Si vous avez aimé cet article, un like et un partage me feraient très plaisir ;)
  •   
  •   
  •  
  •   
  •   
S’abonner
Notifier de
guest

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

5 Commentaires
plus récent
plus ancien Le plus populaire
Commentaires en ligne
Voir tous les commentaires
Didier
Didier
12/12/2020 16:48

Salut!

Je veux apprendre.

Merci

Françoise
Françoise
12/12/2020 16:18

bonjour,
ça m’intéresse+++ !
est-ce que cette formation pourrait être prise en compte par Pôle Emploi, càd avec un devis et avec un n° d’agrément ?

Françoise
Françoise
13/12/2020 20:37
Répondre à  Cyril

merci !
vous pourriez m’envoyer un devis, avec votre n° d’agrément, je leur présenterai et vous dirai si c’est suffisant
merci d’avance !
F

Raouf
Raouf
11/12/2020 08:50

C’est parfait 👍,
Je vais désormais appliquer cette technique en car j’en ai absolument besoin dans l’apprentissage de VBA.
Merci beaucoup 😊