Les 6 bonnes habitudes qui facilitent le développement en VBA sur Excel

Depuis le début de la formation sur le développement en VBA, nous avons eu l’occasion de découvrir de nombreuses méthodes et concepts. Aujourd’hui, je vous propose de revenir sur les six bonnes habitudes à avoir pour nous faciliter le développement d’applications en VBA. Bien entendu, il ne s’agit absolument pas de préceptes indispensables pour pouvoir coder des macros, mais plus d’astuces qui permettront de gagner du temps et éviter des prises de têtes inutiles.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Bonne habitude n 1 : Utiliser l’Option Explicit

Nous avons déjà eu l’occasion de découvrir la notion de « Option Explicit », qui est une petite commande à insérer sur la première ligne d’un module de code et qui permet de forcer la déclaration des variables dans le code :

De cette manière, à chaque fois que nous utiliserons une variable sans l’avoir au préalable déclarée, Excel nous retournera alors une interruption :

Cela permettra ainsi d’être alerté dès qu’une nouvelle variable va être utilisée.

De cette manière, le code en sera plus cadré et donc plus lisible :

En définissant un type pour chaque variable, nous éviterons également certaines erreurs dans le développement du code.

Par exemple, si nous utilisons le code suivant :

Sub test()
    monMessage = "coucou"
    MsgBox Message
End Sub

Ici, nous n’aurons aucune erreur, VBA va afficher une notification, mais celle-ci sera vide !

En effet, nous affecté une valeur à une variable nommée « monMessage », puis nous avons tenté d’afficher ce message, mais avec une erreur dans le nom de la variable (Message au lieu de monMessage).

Bien entendu, ici nous allons rapidement nous rendre compte du problème en regardant la notification, mais dans un code bien plus conséquent, qui pourrait tenir facilement sur plusieurs centaines ou milliers de lignes, identifier une telle erreur sera bien plus complexe…

En revanche, si nous ajoutons la ligne Option Explicit en tout début de module, Excel nous affichera une erreur :

Pour ne pas avoir à saisir cette ligne manuellement sur chaque module, nous pouvons tout simplement activer l’option « Déclaration des variables obligatoire » depuis le menu Outils > Options :

De cette manière, nous nous assurons de ne pas oublier de saisir la ligne sur le début de chaque module.

Bien entendu, au besoin nous pourrons simplement la supprimer…

2. Bonne habitude n 2 : Structurer le code (indentation et paragraphes)

Pour faciliter la compréhension et la relecture du code, il est important de bien l’aérer en définissant des blocs.

Pour créer ces blocs, nous pouvons l’indenter, c’est-à-dire insérer des tabulations au début de chaque ligne pour en faire ressortir la continuité logique.

Voici un exemple de code non indenté :

Sub test()
Dim monMessage As String
Dim i As Integer
monMessage = "coucou"
For i = 1 To 10
If i = 7 Then
MsgBox monMessage
End If
Next
End Sub

Et voici le même code une fois celui-ci indenté :

Sub test()
    Dim monMessage As String
    Dim i As Integer
    monMessage = "coucou"
    For i = 1 To 10
        If i = 7 Then
            MsgBox monMessage
        End If
    Next
End Sub

Comme vous pouvez le voir ici, il est bien plus simple de comprendre le fonctionnement du code, notamment en ce qui concerne la boucle For… Next et le test If… Then…

Pour créer une tabulation, il suffit d’appuyer sur la touche Tabulation du clavier (ou alors d’insérer manuellement des espaces en début de ligne).

Pour supprimer une tabulation, nous appuyons sur les deux touches : [Majuscule] + [Tabulation], et nous pouvons également appuyer sur la touche [Retour Arrière] ([Backspace]).

Pour gérer l’indentation à l’aide de la souris, il suffit d’utiliser les boutons correspondants dans la barre d’outils :

Si les boutons ne sont pas présents, rendez-vous dans le menu Affichage > Barres d’outils > Edition :

En plus d’utiliser l’indentation, il peut être intéressant de de créer des blocs de lignes de codes, qui vont permettre de regrouper les instructions par blocs entre elles au sein de procédures, sous la forme de paragraphes :

Sub test()
    Dim monMessage As String
    Dim i As Integer
    
    monMessage = "coucou"
    
    For i = 1 To 10
        If i = 7 Then
            MsgBox monMessage
        End If
    Next
End Sub

Ici la procédure est décomposée en trois paragraphes :

• Nous commençons par déclarer les variables de la procédure,
• Puis nous affectons une valeur à la variable monMessage
• Et pour finir nous retrouvons la boucle For… Next avec le test If... Then…

3. Bonne habitude n 3 : Commenter le code VBA

Comme nous venons de le voir, il est important de bien organiser le code pour que celui-ci soit clair, aéré et en définitif plus visible.

Maintenant, pour que le code soit encore facilement compréhensible, nous allons pouvoir commenter ce code.

Nous ne reviendrons pas dans le détail sur la notion de commentaires, étant donné qu’un chapitre complet lui est déjà dédié.

Pour faire rapide, un commentaire est une portion de ligne de code qui va être ignoré par le programme lors de son exécution.

Pour insérer un commentaire, nous utilisons simplement une apostrophe au début de celui-ci :

Le commentaire va alors être identifié avec la couleur de police verte

Sub test()
    ' Déclaration des variables
    Dim monMessage As String
    Dim i As Integer
    
    ' Affectation de la valeur
    monMessage = "coucou"
    
    ' Mise en place d'une boucle qui va se répéter 10 fois
    For i = 1 To 10
        ' Est que la valeur de i est égale a 7 ?
        If i = 7 Then
            ' Si oui, nous pouvons afficher le message
            MsgBox monMessage
        End If
    Next
End Sub

4. Bonne habitude n 4 : Structurer le code VBA

VBA offre la possibilité d’utiliser plusieurs modules de codes au sein d’un même projet.

Nous pouvons donc utiliser cette possibilité pour organiser le code en séparant le code en plusieurs parties (une par module), ce qui permettra ainsi de s’y retrouver plus facilement.

Nous pouvons alors imaginer séparer le code en fonction de plusieurs logiques :

• Soit insérer une macro par module,
• Soit regrouper les macros par usage dans chaque module (interactions sur les feuilles de calculs, réalisation de traitements, gestion de fichiers, …)
• Soit dissocier les fonctions et les macros dans des modules différents
• …

Libre à chacun de trouver l’organisation qui puisse permettre de s’y retrouver au mieux.

Attention toutefois à la portée attribuée aux variables pour que celles-ci soient utilisables au travers de plusieurs modules.

5. Bonne habitude n 5 : Structurer le classeur Excel

La plupart du temps les macros VBA permettent de manipuler des données issues de feuilles de calculs.

Il est donc important de bien structurer le classeur pour que la récupération des données se fasse rapidement, et le plus naturellement possible.

Cela passe tout d’abord par l’attribution de noms suffisamment explicites aux diverses feuilles de calculs, mais également aux plages de cellules normées, ou encore aux tableaux de données et autres tableaux croisés dynamiques.

Autant que cela est possible, il peut également être intéressant de bien séparer les différents types d’informations dans des feuilles spécifiques :

• Regrouper les paramètres du classeur dans une feuille unique (nom de société, mois et année étudiées, …)
• Les bases de données peuvent également être saisies dans d’autres feuilles dédiées,
• Enfin, nous pouvons synthétiser els données sous la forme de tableau de bord dans une autre feuille

6. Bonne habitude n 6 : Bannir les adressages fixes

Il n’y a rien de pire après avoir passé de nombreuses heures à mettre en place un fichier Excel parfait que de découvrir que les macros jusqu’alors pleinement fonctionnelles sont maintenant devenues inopérantes après avoir simplement ajouté quelques lignes par-ci et renommé quelques onglets de feuilles de calculs par là.

Pourtant, ce genre de problème pourrait être évité simplement en utilisant des noms relatifs.

Cela consiste à utiliser des références à des cellules ou des feuilles de calculs, comme s’il s’agissait de simples variables.

En effet, contrairement aux fonctions d’Excel qui vont pouvoir s’adapter automatiquement aux décalages de cellules, ou au renommage des feuilles de calculs, le code VBA est totalement indépendant, sans moyen de communication directs !

Nous allons ainsi pouvoir utiliser des plages de cellules nommées en tapant un nom correspondant dans la « zone des noms » (après avoir sélectionné les cellules) :

Pour utiliser cette plage, il suffit simplement de saisir son nom entre crochets :

    ' Passer en revues toutes les cellules de la plage
    For Each c In [mesCellules]
        MsgBox c
    Next

Nous pouvons tout aussi bien utiliser des « noms de feuilles VBA ».

En effet, si nous appelons une feuille de calcul en VBA, nous utilisons l’instruction Sheets :

    ' Afficher la première cellule de la feuille
    MsgBox Sheets("excelformation.fr").[a1]

Par contre, si nous renommons la feuille, cela nous renverra une erreur :

Pour éviter qu’un changement de nom de feuille ne génère ce type d’erreur, nous allons pouvoir utiliser le nom de feuille VBA.

Nous retrouvons celui-ci dans le navigateur de projet (« Feuil1 ») :

Il s’agit alors de simplement l’utiliser, sans instruction (et donc sans guillemets) :

    ' Afficher la première cellule de la feuille
    MsgBox Feuil1.[a1]

Bien entendu, il est possible de modifier ce nom à partir de la fenêtre propriété de la feuille (après avoir double cliqué sur la feuille concernée depuis l’explorateur de projet) :

L’intérêt de cette méthode c’est que le seul moyen de modifier cette propriété, c’est de lancer VBE.

Bien entendu, cela ne permettra pas de s’affranchir de toutes anomalie venant empiéter sur le bon déroulement du code, il suffit en effet qu’un utilisateur renomme une plage de cellules, un tableau de données ou encore un tableau croisé dynamique pour entacher la bonne exécution des macros.

Cela dit, ce genre de manipulation sera bien plus rare que le simple fait d’ajouter ou supprimer des lignes dans une feuille de calcul, ou encore de renommer cette dernière.