Les meilleures pratiques du développement en VBA

Quel type de données est-ce que je vais manipuler/produire ? D’où viennent-elles ? Où devraient-elles êtres idéalement stockées ? SQL Server, Access, Excel, autre ?
Par ailleurs, VBA a aussi ses limites. Peut-être vaut-il mieux envisager un autre environnement ou langage objet (VB .Net / VSTO, C#…) ?
Ne pas hésiter à contacter quelqu’un du service informatique, un DBA s’il y en a un…
Dans trop d’entreprises, on a laissé des applications se développer pour devenir des monstres incontrôlables, critiques pour le fonctionnement de l’entreprise alors qu’elles reposent sur des données Excel, insuffisamment sécurisées, dont la cohérence, l’intégrité et la mise à jour laissent à désirer. Pour remédier à ces situations, j’ai développé un plan en 10 étapes, mais autant partir sur de bonnes bases.
Difficulté technique :
FiabilitéRapidité / MémoireRéutilisabilité / Intégration

Dans la mesure du possible, il faut faire en sorte que le code puisse fonctionner sur un autre ordinateur, voire avec une autre application Office.
Les chemins d’accès aux fichiers peuvent être sauvegardées dans des variables globales, ou dans la base de registre par exemple. On peut aussi permettre leur modification à travers l’interface utilisateur de l’application VBA.
Par ailleurs, plutôt que de mettre en paramètre d’une fonction des éléments spécifiques à Excel, comme « (cell as range) », il faut se poser la question de l’utilisation d’une chaîne de caractères. Ainsi la fonction pourrait fonctionner avec Word, Photoshop ou d’autres logiciels.
Difficulté technique :
FiabilitéRéutilisabilité / Intégration

La plupart du temps, les commentaires que je vois dans les programmes VBA sont complètement inutiles. Un code bien écrit, avec des variables et des procédures / fonctions correctement nommées, suffit largement. A deux exceptions près :
• Il est intéressant pour chaque grand groupe de lignes de code (un module par exemple), de donner le nom du programmeur, et l’objectif principal du programme.
• si vous êtes peu sûr(e) de votre code, si vous avez eu du mal à trouver la solution à un problème, il peut être intéressant de le documenter.
Par ailleurs, il m’arrive encore trop souvent de rencontrer du code non indenté, toutes les lignes démarrant à la même colonne. Or c’est indispensable pour la lisibilité ! La touche TAB est requise.
Enfin, si vous voulez pouvoir déboguer vos procédures facilement, et pouvoir juste les lire, coupez les longues lignes en plusieurs morceaux en mettant à la fin un espace puis un caractère souligné _.
Difficulté technique :
Lisibilité / DébogageRéutilisabilité / Intégration

Certains scripts VBA sont très légers, et faciles à vérifier. Par contre d’autres sont beaucoup plus complexes, et VBA n’est pas conçu pour réaliser des tests unitaires (Il y a des outils tierces qui permettent de le faire comme Rubberduck).
Ceci dit, on peut simuler des tests unitaires en créant une fonction spéciale UnitTests qui va envoyer des paramètres aux fonctions que l’on veut tester, pour vérifier que le résultat correspond à ce que l’on souhaite.
Difficulté technique :
Fiabilité

Là encore, VBA n’est pas très pratique pour la gestion des versions, car le code est intégré à des fichiers Excel. On peut le faire manuellement en exportant le module sur lequel on travaille, bien sûr. Mais certains outils comme VbaDeveloper permettent d’exporter le code, de telle sorte qu’on puisse l’intégrer à un outil comme Git pour gérer les modifications du code.
Difficulté technique :
FiabilitéLisibilité / Débogage

Réfléchir aux questions posées par la référence à des composants COM externes. La plupart du temps, Microsoft recommande la liaison anticipée («early binding»), c’est-à-dire l’ajout d’une référence à une bibliothèques comme celle de MS Project, MS Outlook ou Adobe Acrobat par exemple (Article de Microsoft).
Cependant, pour éviter des problèmes de compatibilité entre versions, Il est souvent intéressant d’intégrer des librairies lors de l’exécution («late binding». Cependant, cela a un inconvénient parfois, car toutes les méthodes de l’objet ne sont pas accessibles.
A voir et à tester selon vos besoins.
Difficulté technique :
FiabilitéRéutilisabilité / Intégration

Un travers assez fréquent chez les autodidactes en VBA consiste à écrire du code qui fonctionne, le plus rapidement possible. Or parfois il faut prendre un peu de la réflexion pour gagner du temps par la suite : une fonction pourra être réutilisée si on la définit avec les bons paramètres, si on teste ces paramètres dès le début pour éviter des erreurs dues au type de variable, etc.
Difficulté technique :
Réutilisabilité / Intégration

Ne laissez pas la place aux erreurs d’orthographe ou à la confusion des variables. Option Explicit, au début de votre module, évitera de nombreuses sources d’erreurs.
Si les variables ont le même type de données ou si elles sont liées entre elles, alors elles devraient être sur la même ligne. Cela simplifiera les modifications ultérieures en améliorant la lisibilité et en limitant les erreurs d’orthographe:
Par ailleurs, le mieux est de typer les variables pour éviter d’avoir des soucis par la suite. Si vous savez que c’est une chaîne de caractères, autant l’indiquer dès le début à VBA : Dim maChaine as String !
Difficulté technique :
FiabilitéLisibilité

Nommer les variables selon une convention constante et descriptive permettra de simplifier la lecture et le débogage du code, surtout après quelques temps ou si le code est lu par quelqu’un d’autre… Vous pouvez vous inspirer de la notation hongroise. Le but est de préfixer les variables avec la portée (g = globale, m = f = form, l = local), le type de données (dbl = double, int = integer, wks = worksheet…). Les contrôles utilisateurs des forms devraient également être préfixés (chk = checkbox, cmd = command button…). Enfin le nom de la variable doit être le plus précis et le plus descriptif possible. Pour simplifier la lecture, on pourra adopter le Camel Case ( 1er mot en minuscules, une initiale en majuscule à chaque mot suivant)
Difficulté technique :
FiabilitéLisibilité

A l’exception des variables globales qui sont toujours déclarées au début d’un module, les variables locales devraient être déclarées près de l’endroit où elles sont utilisées la première fois. Cela sera plus simple pour remanier le code.
D’ailleurs, le mieux est de les initialiser juste après leur déclaration. Ceci est recommandé pour s’assurer que toute variable a une valeur connue, et ainsi réduire le risque de plantages inattendus. Choisissez une valeur qui a du sens, compte tenu de ce à quoi elle va servir.
Evitez de réutiliser cette variable pour autre chose plus tard dans le code car ce sera plus difficile à comprendre pour une autre personne.
Difficulté technique :
FiabilitéLisibilité / Débogage

Le type Variant permet de contenir n’importe quel type de donnée. Le problème est qu’il a besoin de 128 bits pour stocker la valeur, plus que n’importe quel autre type de données.
Les seules raisons d’utiliser un type Variant sont :
• quand vous ne connaissez vraiment pas le type de données
• quand vous écrivez une fonction qui doit pouvoir traiter différents types de données
• quand vous voulez passer un tableau de n’importe quel type de données comme paramètre d’une fonction ou d’une procédure.
Si vous ne spécifiez pas le type de données (Dim chaine), la variable sera considérée comme Variant.
Difficulté technique :
Rapidité / Mémoire

Les constantes VBA sont plus lisibles et leur utilisation est en général plus efficace en termes de gestion de la mémoire.
Les énumérations sont bien moins gourmandes en ressources, et donc plus rapides que les chaînes de caractères. Elles évitent également d’affecter une valeur incorrecte à une variable. De plus, leur utilisation améliore la lisibilité du code. Vous pouvez définir les énumérations que vous souhaitez, pour les jours, les mois ou un tas d’autres choses.
Difficulté technique :
FiabilitéRapidité / Mémoire Lisibilité / Débogage

Plutôt que de déclarer séparément des variables indépendantes qui sont en fait des propriétés d’un même objet (par exemple, le nom, le prénom, l’adresse d’une personne, etc.), il vaut mieux déclarer un module de classe pour gérer les propriétés de cet objet. On pourra modifier les propriétés de l’objet avec la même structure que les objets Excel, par exemple Personne.Nom. On pourra également facilement créer une nouvelle instance de l’objet.
Difficulté technique :
FiabilitéRapidité / MémoireLisibilité / Débogage

Chaque variable objet à laquelle on va affecter une valeur avec le mot clé SET, devrait être vidée quand on n’en a plus besoin avec SET monObjet = Nothing.
Difficulté technique :
FiabilitéRapidité / Mémoire

Le seul nom d’une procédure/fonction doit permettre de comprendre ce qu’elle fait. Pour moi, la meilleure solution consiste, en utilisant le Camel Case, d’utiliser un verbe et un nom, au minimum. Quelques exemples : calculerROI(), enregistrerFichier(), creerTableauSynthese(), envoyerMail()…
Difficulté technique :
Lisibilité / Débogage

Les procédures et fonctions très longues sont plus difficiles à lire et à déboguer. De plus, c’est souvent le signe qu’elles sont difficilement réutilisables, alors qu’elles pourraient être découpées en blocs qui, eux, pourront être appelés par d’autres procédures. Si vous ne pouvez pas voir le début et la fin d’une procédure sans utiliser votre souris, c’est qu’elle est trop longue… Cela demande un peu de réflexion supplémentaire, mais le jeu en vaut la chandelle.
Difficulté technique :
Réutilisabilité / IntégrationLisibilité / Débogage

Selon vos besoins, vous devrez choisir entre ByVal et ByRef. Prendre l’option par défaut (ByRef) n’est pas toujours la meilleure solution.
ByRef (par défaut): passe une référence de la variable (un pointeur) à la procédure/fonction. Ainsi, la valeur de la variable pourra être changée dans cette dernière. A noter : un type de donné défini par l’utilisateur (UDT) devra être passé par référence.
ByVal permet de passer une copie de la variable, de telle sorte que le changement de valeur de cette variable n’affectera que la copie locale. On utilise cet argument pour simplement récupérer la valeur mais ne pas la modifier dans la procédure appelante.
Difficulté technique :
Fiabilité

Ceci est d’autant plus important que la fonction est publique, c’est-à-dire qu’elle peut être appelée par d’autres modules voire d’autres fichiers. Il est essentiel de vérifier la validité des arguments avant de les utiliser. De nombreuses erreurs sont liées à des arguments transmis qui sont en fait null, ou contiennent une chaîne de caractères alors qu’ils sont censés contenir un nombre. Plutôt que de prendre le risque de planter l’application, mieux vaut vérifier le type de données, par exemple avec TypeOf, voire la longueur d’une chaîne (len) ou la valeur d’un nombre (>0…)
Difficulté technique :
Fiabilité

Ces fonctions retournent Vrai ou Faux selon le succès ou l’échec. Elles peuvent avoir leur propre traitement d’erreur comme dans le cas suivant :

Public Function creerRepertoire(ByVal sCheminComplet As String) As Boolean
     On Error Goto TraitementErreur
     VBA.MkDir(sCheminComplet)
     creerRepertoire = True
     Exit Function
TraitementErreur:
     creerRepertoire = False
End Function

Par défaut, toutes les procédures d’un module peuvent être accédées par l’utilisateur s’il a le menu Développeur visible. Dans l’exemple suivant, les 3 procédures, la principale et les 2 « sousProgramme » peuvent être lancés indépendamment, ce qui risque de provoquer des erreurs voire d’endommager le classeur, selon leur contenu :

Il peut être tout à fait justifié de sortir d’une boucle quand on a exécuté ce que l’on voulait, pour gagner du temps. Par contre cela demande de la rigueur dans l’analyse pour ne pas sortir d’une boucle alors qu’on devrait y rester… Cela va de soi, même figurez-vous que je trouve régulièrement des cas où l’utilisateur-programmeur a mal calculé son coup !
Difficulté technique :
Fiabilité

Réminiscence de ce bon vieux basic, où l’on pouvait spécifier le numéro de ligne où il fallait aller dans tel ou tel cas, et où on écrivait les programmes de manière séquentielle, l’instruction GoTo est encore utile, notamment pour le traitement d’erreur, puisqu’avec On Error Goto 0, on revient à un processus de gestion d’erreur standard, géré par VBA. Avec On Error Goto TraitementErreur, on indique à quel endroit de la procédure il faut se rendre en cas d’erreur. Et là encore, c’est un bénéfice inestimable en VBA, même si on devrait beaucoup plus utiliser les instructions Try…Catch…Finally qui est présente dans la plupart des langages « modernes ».
Difficulté technique :
Fiabilité

Réminiscence de ce bon vieux basic, où l’on pouvait spécifier le numéro de ligne où il fallait aller dans tel ou tel cas, et où on écrivait les programmes de manière séquentielle, l’instruction GoTo est encore utile, notamment pour le traitement d’erreur, puisqu’avec On Error Goto 0, on revient à un processus de gestion d’erreur standard, géré par VBA. Avec On Error Goto TraitementErreur, on indique à quel endroit de la procédure il faut se rendre en cas d’erreur. Et là encore, c’est un bénéfice inestimable en VBA, même si on devrait beaucoup plus utiliser les instructions Try…Catch…Finally qui est présente dans la plupart des langages « modernes ».
Difficulté technique :
Fiabilité

Les propriétés ActiveWorkbook, ActiveSheet et ActiveCell sont bien pratiques, mais très dangereux : ils laissent la place au hasard des manipulations d’Excel par l’utilisateur. Imaginez qu’un utilisateur lance votre macro tout en manipulant d’autres fichiers. Quel va être le résultat ? Impossible à prédire, sauf si vous avez spécifié une variable objet avec par exemple :

Vous voulez vraiment gagner du temps ? Mettez le contenu de votre feuille, ou la plage de cellules qui vous intéresse, dans un Array, et effectuez les manipulations, recherches, tris, modifications, etc. dans l’Array. Puis, quand vous aurez fini, libre à vous de rebasculer les données de l’array dans la feuille de calcul. Gain de temps : jusqu’à 99% !
Dans tous les cas, évitez les boucles quand :
• vous recopiez ou coupez/collez des données entre plages de cellules
• quand vous triez des données dans des plages
• quand vous effectuez une recherche dans des plages

Une fois que vous êtes sûr de votre code, vous pouvez désactiver les propriétés suivantes de « Application »:
• ScreenUpdating : le rafraichissement automatique de l’écran
• Calculation: le mode de calcul, à positionner sur xlCalculationManual
• EnableEvents: le traitement des événements Excel (en fonction de vos besoins)
• DisplayStatusBar: la barre de statut, si vous ne vous en servez pas
• ActiveSheet.DisplayPageBreaks (ou wksFeuille.DisplayBreaks): l’affichage des sauts de page
Vous pouvez déclarer une procédure qui désactivera ou réactivera ces variables selon vos besoins.
Difficulté technique :
Rapidité / Mémoire

Le copier/coller d’une plage de cellules vers une autre, ou d’une cellule vers une autre, demande beaucoup de mémoire, et peut donc prendre beaucoup de temps, voire faire planter Excel. Surtout sur des ordinateurs dont la mémoire et le processeur sont limités. Bref, plutôt que d’écrire :

Quand vous manipulez des objets, que ce soit des objets Excel ou non, plutôt que de modifier individuellement les propriétés de l’objets, utilisez la syntaxe With/End With pour rendre votre code plus simple à écrire et plus lisible :

    With wks
        .Visible = xlSheetVisible
        .AutoFilterMode = False
    End With

Réinventer la roue peut être très sympathique… si on a le temps. C’est rarement le cas. Alors plutôt que d’écrire votre propre procédure de calcul ou de tri, allez chercher dans les fonctions disponibles de Application.WorksheetFunction pour voir s’il n’y a pas un équivalent. En plus, il y a de fortes chances que l’exécution soit plus rapide que votre procédure…
Difficulté technique :
Lisibilité / Débogage

Cela paraît évident, pourtant nombreuses sont les applications où le développeur n’a rien mis en place de spécial, et donc un message d’erreur abscons se présente parfois à l’utilisateur. Rien de pire pour la réputation du programmeur, et bonjour la difficulté pour résoudre le problème. Tout cela s’anticipe, se teste, se valide.
Avec des instructions comme On Error Goto TraitementErreur ou l’ensemble d’instructions Try…Catch…Finally (voir ci-après), les outils ne manquent pas pour détecter et réagir de manière adéquate quand une erreur surgit.

Petite remarque supplémentaire : certains abusent du « On Error Resume Next » parce que c’est pratique. Alors c’est vrai que parfois, quand on a certaines erreurs, on peut passer outre car elles n’ont pas un impact majeur. Mais 1. il faut revenir à un fonctionnement normal le plus rapidement possible (« On Error Goto 0 ») et 2. il ne faut surtout pas en prendre l’habitude. Rien de vaut un traitement d’erreur précis et spécifique.
Difficulté technique :
Fiabilité

En cliquant sur le menu Débogage/Compiler, vous allez permettre à VBA de déceler les erreurs de syntaxe et de compilation, et gagner un temps certain sur la mise au point. Toutes les procédures, même celles qui ne sont pas appelées, seront compilées, et c’est un gage de fiabilité.
Difficulté technique :
Fiabilité

Cet ensemble d’instructions, présente dans de nombreux langages, est une façon organisée et très lisible de gérer du code qui peut conduire à une erreur. Mais elle crée une surcharge supplémentaire, et peut être plus lente que le traitement d’erreur traditionnel. Donc elle doit être réservée au traitement d’événements inattendus ou imprévus.
Difficulté technique :
Fiabilité

Pour faciliter le débogage, y compris quand l’application sera déployée sur plusieurs postes utilisateurs, il est important de sauvegarder toutes les erreurs dans un fichier texte.
Difficulté technique :
Lisibilité / DébogageFiabilité