EXCEL possède de multiples fonctions qui permettent de répondre à de nombreux besoins, certes, mais dans certains cas, il n'existe aucune fonction intégrée pour répondre aux besoins particuliers de l'utilisateur. Il faut alors programmer ses propres fonctions…

« Un programme informatique est une séquence d'instructions qui spécifie étape par étape les opérations à effectuer pour obtenir un résultat. Pour écrire un programme informatique, on utilise un langage de programmation ».

Pour programmer EXCEL nous allons donc utiliser un langage de programmation : le VBA, acronyme anglais de « Visual Basic for Applications ».

• Visual : car c'est une « programmation visuelle », grâce à l'éditeur intelligent qui reconnaît les mots clés du langage et permet le débogage.

• BASIC : « Beginner's All Purpose Symbolic Instructions Code » que l'on traduit par « code d'instructions symboliques à tout faire pour débutant ». VBA est un langage de programmation qui se veut simple à apprendre et à utiliser, à la portée de tous.

• Applications : car ce langage de programmation sera commun à la suite bureautique de Microsoft : EXCEL, ACCESS, WORD, POWERPOINT, OUTLOOK, même si chacune de ces applications a ses particularités. Nous allons étudier le VBA utilisé pour programmer EXCEL, mais nous verrons aussi comment, depuis EXCEL, nous pouvons intervenir dans OUTLOOK ou ACCESS, grâce au VBA.

Il faudrait des centaines de pages de documentation pour explorer le VBA dans son intégralité, ici nous allons juste découvrir les bases de la programmation en VBA : comprendre la logique de construction d'un programme, générer des algorithmes, utiliser les principales instructions du langage.
À l'appui d'exemples, d'abord simples puis qui se complexifient graduellement, nous allons aborder de nombreuses notions de la programmation en VBA.
Jusqu'à devenir de véritables champions, capables de rédiger un algorithme de tri rapide. Exercice plus délicat qu'il n'y paraît, car chaque instruction doit être optimisée pour gagner en rapidité de traitement.

Armé de ce bagage, vous pourrez alors approfondir vos connaissances en lisant des ouvrages plus techniques, en farfouillant sur les sites internet, en échangeant sur des forums de programmation.

N'oubliez pas non plus l'aide disponible dans EXCEL, très fournie, pour laquelle un chapitre est consacré afin de vous apprendre à l'exploiter pleinement.

Nous faisons référence dans ce document au VBA version 7.0 pour EXCEL 2010.
Mais la grande majorité du code est compatible avec la version antérieure.

Pour programmer en VBA, il suffit de connaître les notions de base d'EXCEL, savoir ce qu'est un classeur, une feuille, une cellule.

Par exemple, savoir que « A1 » fait référence à la cellule de la première ligne, première colonne, de la feuille active.

Car la programmation en VBA utilise ces mêmes notions, et pour mettre la valeur 15 dans la cellule « A1 » de la feuille active, l'instruction est : Range("A1").Value = 15

Dans EXCEL, pour mettre manuellement la valeur 15 dans la cellule « A1 » d'une autre feuille, par exemple « Feuil2 », vous devez d'abord sélectionner cette feuille, puis sélectionner la cellule « A1 » et enfin saisir la valeur 15.
En VBA vous pouvez atteindre une cellule sans l'avoir sélectionnée préalablement, simplement en indiquant son « adresse ». L'instruction est : Sheets("Feuil2").Range("A1").Value = 15

La syntaxe respecte la logique suivante : Workbooks - Sheets - Range - Value = 15

Si l'objet en amont n'est pas renseigné, Workbooks (classeur) ou Sheets (feuille), c'est l'objet actif qui est pris par défaut.
C'est pourquoi, pour simplifier le code, les objets en amont ne sont indiqués qu'en cas de besoin.

En VBA, on dit que Range est un objet, qui possède des propriétés, dont la propriété Value (valeur).

Un objet ne peut pas être modifié. Seules ses propriétés peuvent l'être, et encore, pas toutes, car certaines sont en lecture seule.

Un objet peut avoir une propriété par défaut. Par exemple la propriété Value pour l'objet Range. Dans ce cas il n'est pas nécessaire de saisir cette propriété. Et Range("A1") = 15 équivaut à Range("A1").Value = 15

À la place de Range vous pouvez utiliser Cells(Ligne, Colonne).
Cells désigne toujours une seule cellule, alors que Range représente une plage pouvant contenir une ou plusieurs cellules. Nous étudierons plus loin comment utiliser Range pour une sélection multicellulaire.

Attention, les coordonnées d'une cellule sont exprimées au format (y, x). La première cellule d'une feuille est la cellule située en ligne 1, colonne 1.

Comme pour l'objet Range, la propriété Value est la propriété par défaut de Cells, nous écrirons donc indifféremment Cells(1,1) = 15 ou Cells(1,1).Value = 15

Vous en savez désormais assez pour programmer.

Pour notre premier programme, imaginons une cour de récréation où deux enfants discutent bonbons. Le premier demande au second : « Combien as-tu de bonbons dans ta poche ? » Pour le savoir, l'enfant plonge sa main dans sa poche, et compte les bonbons : 1, 2, 3… jusqu'à ce qu'il n'en trouve plus. Et donne la solution : « J'en ai 9 ».

Si l'enfant peut réaliser une telle prouesse, c'est parce qu'il suit un processus logique.

Pour commencer, l'enfant réserve dans l'espace mémoire de son cerveau une variable bonbon, elle sera numérique, et non pas alphabétique, car elle sert à compter des nombres. Pour le moment, cette variable vaut 0.
Vient ensuite un traitement en boucle : tant que la poche n'est pas vide, la variable numérique bonbon est incrémentée de une unité.
Quand la condition pour sortir de la boucle est atteinte, ici c'est quand il n'y a plus de bonbon trouvé, alors le traitement se termine et la réponse peut être donnée.

Transposons cet exemple à EXCEL : ouvrez un nouveau classeur. 

Dans les cellules « A1 » à « A9 », mettre un « x ».
La feuille EXCEL représente la poche de l'enfant, et les « x » représentent ses bonbons.
Cliquez sur le menu « Développeur » puis « Visual Basic », ou utilisez le raccourci clavier [Alt] [F11].
Si le menu « Développeur » n'est pas affiché dans votre ruban, il faut l'installer via le menu « Fichier », « Options », « Personnaliser le Ruban », cochez dans la partie de droite : « Développeur ».

L'éditeur qui s'affiche est composé de deux parties. Si ce n'est pas le cas, faire « Affichage », « Explorateur de projets » ou [Ctrl][R] . Vous trouverez dans la partie gauche le détail du « Projet », et dans la partie droite, l'éditeur, pour l'instant vierge, où sera saisi notre code de programmation.

Dans la partie gauche, faire un clic droit pour ouvrir le menu contextuel et choisir « Insertion », « Module ».
Le module créé peut être renommé en appelant la fenêtre des propriétés [F4] après avoir été sélectionné.

Notre code sera donc contenu dans un « module » du « projet » du classeur actif. Nous verrons plus tard l'utilité de programmer dans un formulaire, un module, une feuille, ou un classeur.

Faire un clic gauche dans la partie droite, vierge pour l'instant, pour l'activer. Puis choisir dans le menu « Insertion », « Procédure ».
Un formulaire s'ouvre et nous demande le nom de notre procédure, c'est-à-dire le nom de notre programme.
Notre programme va s'appeler : « CompterMesBonbons ». Donc saisissez « CompterMesBonbons » et validez sur « OK » sans changer les options proposées, type « Sub » et portée « Public ». Nous étudierons plus tard ces options.

L'éditeur a généré automatiquement le texte suivant :

Public Sub CompterMesBonbons()

End Sub

Le texte en bleu signale que le mot est réservé au langage de programmation. Ainsi, nous pourrons avoir une variable nommée Bonbon, mais impossible d'avoir une variable nommée Public ou End, car ces mots sont réservés à la programmation.
End Sub indique la fin de notre programme.

Justement, programmons :

Il nous faut en premier lieu déclarer les variables que nous allons utiliser, tout comme l'enfant dans notre exemple s'est réservé dans son espace mémoire une variable numérique Bonbon.

C'est le mot Dim qui permet cela. Le mot As indique le type de la variable. Il existe plusieurs types de variables. Ici nous choisirons un type Long. Le choix d'un type de variable est facultatif à notre niveau, mais prenons la bonne habitude de le faire. Nous étudierons ultérieurement les différents types de variables existants.

Il nous faut une deuxième variable Ligne pour passer d'une ligne à l'autre dans la feuille du classeur, pour savoir s'il reste des « x » qui représentent les bonbons. Ainsi nous allons lire la ligne 1, puis la ligne 2, puis la ligne 3 et ainsi de suite, toujours bien sûr dans la colonne 1.

Ce qui donne :

Public Sub CompterMesBonbons()

Dim Bonbon As Long
Dim Ligne As Long

End Sub

Vous avez remarqué qu'en saisissant As une liste de choix s'est ouverte vous permettant de piocher Long. Les mots en bleu réservés au langage de programmation peuvent être saisis indifféremment en minuscules ou en majuscules car après validation de la ligne de code, la syntaxe correcte est automatiquement appliquée par l'éditeur.

Quand une variable est déclarée par Dim elle est initialisée à zéro.

Parfait pour notre compteur Bonbon, mais nous avons besoin que Ligne soit à 1, car la ligne 0 n'existe pas et essayer de la lire engendrerait une erreur. Cela est fait par le code : Ligne = 1.

Un commentaire peut être inséré avec le guillemet simple « ' ». Le texte s'affiche alors en vert.

Public Sub CompterMesBonbons()

Dim Bonbon As Long
Dim Ligne As Long

Ligne = 1 ' Initialise Ligne à 1

End Sub

Maintenant générons notre boucle « Tant que la condition est remplie… Faire ».

VBA étant en anglais, il faudra écrire While pour « Tant que ». N'oubliez pas que les coordonnées d'une cellule sont au format (y,x).

Notre boucle débute par While suivi de sa condition, et pour indiquer la fin de la boucle Wend est utilisé.

La syntaxe officielle est écrite ainsi :

While Condition
    [Traitement]
Wend 

Dans cette syntaxe, [Traitement] représente une ou plusieurs lignes de code.

Pour notre exemple nous allons incrémenter les variables Bonbon et Ligne, avec les instructions « La variable Bonbon égale Bonbon plus un » et « La variable Ligne égale Ligne plus un ».

Il existe d'autres manières pour faire un traitement en boucle, nous les étudierons plus tard.

Revenons à notre programme : il nous reste encore à afficher, dans une fenêtre d'information, le contenu de la variable Bonbon à la fin du traitement en boucle, avec l'instruction MsgBox. Ne tenez pas compte pour le moment de l'info-bulle qui s'affiche quand vous saisissez cette instruction.

Le code de programmation est :

Public Sub CompterMesBonbons()

Dim Bonbon As Long
Dim Ligne As Long

Ligne = 1 ' Initialise Ligne à 1

While Cells(Ligne, 1) <> "" ' Boucle Tant que cellule(y,x) n'est pas vide.
    Bonbon = Bonbon + 1     ' Incrémente le nombre de bonbons.
    Ligne = Ligne + 1       ' Passe à la ligne suivante.
Wend

MsgBox Bonbon ' Affiche le contenu de la variable Bonbon

End Sub

Notre programme prend forme. Nous pouvons le tester.

Depuis le classeur, cliquez sur « Macro » dans le menu « Développeur », sélectionnez votre programme puis cliquez sur Exécuter.

Cette fenêtre s'affiche. Vous venez de réaliser votre premier programme en VBA… Félicitations. Cliquez sur le bouton « OK ».

Pour suivre l'exécution du programme pas à pas, revenez dans l'éditeur. Placez-vous n'importe où dans le code et faites [F8] ou utilisez le menu « Débogage », « Pas à pas détaillé ».

La ligne en cours d'exécution est mise en surbrillance jaune. Faites [F8] pour passer à la suivante.

Vous suivez en direct le traitement de la boucle « While… Wend ».

En plaçant la souris sur une variable, sa valeur s'affiche en info-bulle.

Remplacez maintenant l'instruction MsgBox Bonbon par :

MsgBox "J'ai dans ma poche " & Bonbon & " bonbons"

Le signe « & » permet de concaténer le texte placé entre deux doubles guillemets et une variable, ou du texte.

Relancez le programme et vous obtenez maintenant :

EXCEL dimensionne la fenêtre automatiquement.

Maintenant, nous allons personnaliser cette fenêtre, car cet exercice permet d'aborder des notions importantes de VBA.

Lorsque vous saisissez MsgBox une info-bulle s'affiche.

Les arguments supportés par l'instruction sont séparés par des virgules. Quand ils sont entre crochets, cela signifie qu'ils sont facultatifs. Vous pouvez sauter un argument facultatif et passer au suivant, mais il faut mettre la virgule demandée. Par exemple : MsgBox "Mon Texte", , "Mon Titre" pour ne renseigner que les arguments Prompt (message) et Title (titre) de la fonction.

Si l'ensemble des arguments est entre parenthèses, c'est que la fonction retourne une valeur. C'est le cas de la fonction MsgBox qui retourne une constante, qui correspond au numéro du bouton cliqué par l'utilisateur.

Appuyer sur [F1] pour ouvrir l'aide, très complète, sur cette fonction. Un exemple est disponible.

Ce qu'il faut retenir :

• Prompt : est le texte de la fenêtre. Cet argument est obligatoire. Utilisez deux doubles guillemets pour afficher du texte, comme cela : "Mon Texte". Par contre, les variables, numériques ou alphabétiques, ne doivent pas être entre deux doubles guillemets, sinon c'est le nom de la variable qui est affiché, et non pas sa valeur.

• Buttons : est le style de bouton. Par défaut c'est le style vbOkOnly, c'est-à-dire celui qui affiche uniquement le bouton « OK ». Les styles de boutons peuvent être classés en trois catégories :

  • la catégorie qui détermine les boutons à afficher :

    vbOKOnly	Affiche le bouton OK uniquement.
    vbOKCancel	Affiche les boutons OK et Annuler.
    vbAbortRetryIgnore	Affiche les boutons Abandonner, Réessayer et Ignorer.
    vbYesNoCancel	Affiche les boutons Oui, Non et Annuler.
    vbYesNo	Affiche les boutons Oui et Non.
    vbRetryCancel	Affiche les boutons Réessayer et Annuler.

  • La catégorie qui détermine l'icône à afficher :

    vbCritical	Affiche l'icône message critique.
    vbQuestion	Affiche l'icône question.
    vbExclamation	Affiche l'icône message d'avertissement.
    vbInformation	Affiche l'icône message d'information.

  • La catégorie qui détermine le bouton sélectionné par défaut :

    vbDefaultButton1	Le premier bouton est le bouton par défaut.
    vbDefaultButton2	Le deuxième bouton est le bouton par défaut.
    vbDefaultButton3	Le troisième bouton est le bouton par défaut.
    vbDefaultButton4	Le quatrième bouton est le bouton par défaut.

• Title : est le titre de la fenêtre. « Microsoft Excel » s'affichera si cet argument est omis.

Vous pouvez personnaliser une fenêtre en cumulant une des valeurs de chacune de ces catégories de style de bouton.

Par exemple : Buttons = vbYesNo + vbQuestion + vbDefaultButton2 affiche une fenêtre avec les deux boutons « Oui » et « Non », avec une icône symbole d'une question, en sélectionnant le bouton « Non » par défaut.

Sélectionnez MsgBox "Avez-vous compris?", vbYesNo + vbQuestion + vbDefaultButton2

MsgBox "Avez-vous compris?", _
        vbYesNo + vbQuestion + vbDefaultButton2

Ce qui peut donner aussi :

Sélectionnez MsgBox "J'ai dans ma poche " & Bonbon & " bonbons." & Chr(10) & Chr(13) _ & "Sauf erreur ou omission." _ & Chr(10) & Chr(13) _ & Chr(10) & Chr(13) _ & "Et si je ne me suis pas trompé.", _ vbOKOnly + vbExclamation, _ "Fin du Traitement" ' Affiche le contenu de la variable bonbon

Note : Chr(10) & Chr(13) permet de faire un saut de ligne.

Étudions maintenant comment récupérer le bouton cliqué par l'utilisateur, dans une fenêtre qui permet de faire un choix entre plusieurs boutons.

Nous avons vu que si les arguments sont entre parenthèses la fonction MsgBox retourne un chiffre, de type Long, qui correspond au numéro du bouton cliqué par l'utilisateur.

Il faut donc prévoir une variable, nommée par exemple Reponse, qui contiendra ce chiffre (Notez que les puristes évitent l'usage des accents dans leurs codes, même si le VBA le permet, pour faciliter la portabilité du code dans d'autres languages de programmation).

Et un traitement conditionnel de la forme : si la condition est remplie alors je fais cela, sinon je fais cela.

En VBA la syntaxe est la suivante :

If Condition Then
    [Traitement]
[Else
    [Traitement]]
End If 

Modifiez le code de notre programme ainsi et lancez le traitement :

Public Sub CompterMesBonbons()

Dim Bonbon As Long  ' Variable numérique pour compter les bonbons.
Dim Ligne As Long   ' Variable numérique pour balayer les lignes de la feuille.

Ligne = 1 ' Initialise Ligne à 1

While Cells(Ligne, 1) <> "" ' Tant que cellule(y,x) n'est pas vide...
    Bonbon = Bonbon + 1     ' Incrémente le nombre de bonbons,
    Ligne = Ligne + 1       ' Passe à la ligne suivante,
Wend                        ' Boucle.

Dim Reponse As Long  ' Variable numérique pour le choix de l'utilisateur.

Reponse = MsgBox("J'ai dans ma poche " & Bonbon & " bonbons." _
            & Chr(10) & Chr(13) & Chr(10) & Chr(13) _
            & "Est-ce que je ne me suis trompé ?", _
            vbYesNo + vbQuestion + vbDefaultButton2, _
            "Fin du Traitement")

If Reponse = vbYes Then ' Teste si l'utilisateur a choisi le bouton Yes ?
    MsgBox "Mince."     ' Si oui, alors affiche : Mince.
Else                    ' Sinon
    MsgBox "Super."     ' Affiche : Super.
End If                  ' Fin du test conditionnel.
    
End Sub

Deux remarques sur la syntaxe de « If… Then » :

Else est facultatif (d'où la présence des crochets ouverts « [ » et fermés « ] » dans la syntaxe) vous pouvez faire un test conditionnel de la forme simplifiée :

If Condition Then
    [Traitement]
End If 

Et si le traitement est sur la même ligne que la condition, End If n'est plus utile.

If Condition Then [Traitement]

Nous aurions pu écrire le code suivant, qui donne le même résultat :

If Reponse = vbYes Then MsgBox "Mince."     ' Si oui, alors affiche : Mince.
If Reponse <> vbYes Then MsgBox "Super."    ' Si différent de oui, alors affiche : Super.

De nos jours, où les ordinateurs sont ultra rapides, avec une mémoire énorme, le choix entre une syntaxe ou une autre relève plus de l'habitude de travailler du programmeur, que d'une recherche de gains en temps de traitement par le microprocesseur.

Même remarque concernant les traitements en boucle.

Nous avons utilisé la syntaxe « Tant que la condition est remplie… Faire » :

While Condition
    [Traitement]
Wend

Mais nous aurions pu utiliser la syntaxe « Faire… Tant que la condition est remplie » :

Do
    [Traitement]
    [Exit Do]
    [Traitement]
Loop [{While | Until} Condition]

Ce qui aurait donné le code suivant :

Do                                                    ' Faire...
    If Cells(Ligne, 1) = "x" Then Bonbon = Bonbon + 1 ' Si x alors incrémente nombre bonbons,
    Ligne = Ligne + 1                                 ' Passe à la ligne suivante,
Loop While Cells(Ligne, 1) <> ""                      ' Boucle Tant Que ligne n'est pas vide.

Contrairement à notre première syntaxe avec  While… Wend, où la condition est en amont, et donc s'il n'y a aucun bonbon la boucle n'est pas traitée, Do… Loop While (ou Do… Loop Until, Until étant l'inverse de While, peut être traduit par « Jusqu'à ce que ») exécute le traitement et teste ensuite la condition en fin de boucle. Donc dans le cas où il n'y a aucun bonbon dans la poche, le traitement se déroule quand même. Il faut donc tester si la cellule analysée vaut bien « x » pour incrémenter le nombre de bonbons.

Ou alors faire une boucle avec la syntaxe « Faire tant que la condition est remplie… » :

Do [{While | Until} Condition]
    [Traitement]
    [Exit Do]
    [Traitement]
Loop

Do While Cells(Ligne, 1) = "x"  ' Faire Tant Que cellule vaut x...
    Bonbon = Bonbon + 1 '       ' Incrémente nombre bonbons,
    Ligne = Ligne + 1           ' Passe à la ligne suivante,
Loop                            ' Boucle.

Vous avez remarqué que dans certaines syntaxes, vous pouvez sortir de la boucle prématurément en utilisant l'instruction Exit Do, généralement déclenchée par un traitement conditionnel de la forme If… Then.

L'instruction Exit Do peut être utilisée même si une condition de sortie est renseignée.

Enfin, certains préfèrent utiliser la syntaxe « Pour Variable = valeur de début… incrémenter la variable jusqu'à ce qu'elle vaille la valeur de fin » :

For VariableCompteur = ValeurDeDébut To ValeurDeFin [Step ValeurDuPas]
    [Traitement]
    [Exit For]
    [Traitement]
Next [VariableCompteur]

For Ligne = 1 To 9999                       ' Boucle sur les lignes.
    If Cells(Ligne, 1) <> "x" Then Exit For ' Sort de la boucle si
                                            ' la cellule ne vaut pas x.
    Bonbon = Bonbon + 1                     ' incrémente nombre bonbons
Next Ligne                                  ' Passe à la ligne suivante.

Si elle n'est pas renseignée, comme ici, ValeurDuPas vaut 1. Mais cette valeur peut être différente, voire négative. Nous utiliserons cette caractéristique bien pratique dans d'autres exemples.

En résumé, il existe plusieurs façons de faire une boucle, libre à vous d'adopter la syntaxe qui vous convient le mieux. Mais dans une boucle, il faut toujours prévoir la sortie, sans quoi vous risquez, soit de tourner en rond sans fin, soit de sortir au mauvais moment (pensez à tester vos boucles avec [F8]). Cependant, pour une meilleur lisibilité du code, évitez si possible l'usage de Exit Do et Exit For, car cela génère plusieurs sources de sorties et complique le débogage.

Revenons à notre programme, et inspirons-nous de la fonction MsgBox qui accepte des arguments, certains obligatoires, d'autres facultatifs, et retourne une valeur. Imaginons que notre procédure puisse en faire autant. Cela lui ferait gagner en souplesse et en puissance… Car notre code pourrait alors être utilisé aussi bien quand la colonne utilisée pour symboliser les bonbons est la colonne 1 que lorsque c'est la colonne 2. Ce qui correspondrait à pouvoir compter les bonbons de la poche droite et ceux de la poche gauche avec le même code. Il suffirait de passer en argument le numéro de la colonne et d'utiliser cet argument. Et la procédure retournerait le nombre de bonbons comptés.

Au travail…

Insérez un nouveau module.

Une procédure qui retourne une valeur n'est pas du type Sub (sous-programme en français) mais du type Function (fonction en français).

Notre fonction, que l'on va appeler « CompterPoche » aura un argument, MaColonne, de format numérique, qui représentera la colonne de référence, et retournera une valeur numérique qui représentera le nombre de bonbons comptés.

Le code s'inspire de celui déjà étudié, avec en différence, la variable MaColonne qui est utilisée comme coordonnée « x » de la cellule(y,x) à analyser à la place d'une valeur figée à 1 :

Function CompterPoche(MaColonne As Long) As Long

Dim Bonbon As Long  ' Variable numérique pour compter les bonbons.
Dim Ligne As Long   ' Variable numérique pour balayer les lignes de la feuille.

Ligne = 1 ' Initialise Ligne à 1

While Cells(Ligne, MaColonne) <> ""  ' Boucle Tant que cellule(y,x) n'est pas vide.
    Bonbon = Bonbon + 1              ' Incrémente le nombre de bonbons.
    Ligne = Ligne + 1                ' Passe à la ligne suivante.
Wend

CompterPoche = Bonbon ' La fonction retourne le nombre de bonbons comptés

End Function

Cette fonction ne peut pas être testée en l'état, avec la touche [F8] de l'éditeur, car elle a besoin que la valeur de son argument MaColonne soit renseignée. Nous ajoutons donc une procédure, dans le même module, de type « sous-programme », nommée « CompterLesBonbons » qui appelle notre fonction avec en argument la colonne désirée. La valeur retournée par la fonction est stockée dans la variable numérique Bonbon.

Sub CompterLesBonbons()

Dim Bonbon As Long ' Variable numérique pour les retours.

Bonbon = CompterPoche(1) ' Traitement sur la colonne 1.
MsgBox "J'ai " & Bonbon & " bonbons dans la poche 1"

Bonbon = CompterPoche(2) ' Traitement sur la colonne 2.
MsgBox "J'ai " & Bonbon & " bonbons dans la poche 2"

End Sub

Cette fois vous pouvez tester le code, avec la touche [F8] de l'éditeur, pour obtenir ceci :

Vous avez remarqué que la variable Bonbon est déclarée dans la fonction « CompterPoche » et aussi dans le sous-programme « CompterLesBonbons » : il devrait donc se produire une erreur, car la variable est déclarée deux fois. Or, ce n'est pas le cas.

En effet, une variable déclarée dans une procédure a une utilisation, on dit une portée, limitée à la procédure, ce qui a deux conséquences :

• la variable ne peut être utilisée que dans la procédure où elle est déclarée. Elle est donc unique pour la procédure et même si une variable de même nom est déclarée dans une autre procédure, cette autre variable est physiquement différente. Ça tombe plutôt bien, car ça évite de se casser la tête pour trouver des noms différents à toutes les variables que nous allons utiliser dans un programme ;

• la variable est effacée de la mémoire à la fin de la procédure, son contenu est donc perdu… sauf si elle est déclarée avec Static au lieu de Dim. Dans ce cas la valeur de la variable est conservée, mais la variable ne peut pas être utilisée dans une autre procédure.

Pour qu'une variable soit commune à toutes les procédures d'un module, elle doit être déclarée en en-tête du module, avant les procédures, avec Dim, ou Private.

Pour qu'une variable soit commune à toutes les procédures de tous les modules, elle doit être déclarée en en-tête d'un des modules, avant les procédures, avec Public.

Les procédures aussi ont une portée :

• une procédure déclarée avec le mot clé Private ne peut être appelée que depuis une procédure au sein du même module ;

• une procédure déclarée avec le mot clé Public peut être appelée depuis toutes les procédures de tous les modules. Les procédures sont Public par défaut.

Dernière remarque au sujet de nos variables : elles ne sont pas identifiées par un code de couleur par l'éditeur, au contraire des mots clés du langage qui sont affichés en bleu. Donc il suffit d'une faute de frappe pour que la variable Bombon soit utilisée à la place de la variable Bonbon dans une partie du traitement. Car bien que Bombon ne soit pas déclarée, l'éditeur accepte cette variable, en lui attribuant par défaut le type Variant et en l'initialisant à la valeur nulle. De quoi mettre un beau bazar dans un programme et des migraines en perspective pour trouver l'origine du bogue.

Pour se prémunir de ce danger, nous allons prendre l'habitude d'obliger la déclaration des variables, en écrivant Option Explicit en en-tête du module, avant les autres déclarations de variables et de procédures.

Ainsi, en cas d'erreur de frappe, l'éditeur bloque le traitement quand l'on veut exécuter la procédure :

Vous pouvez forcer cette déclaration depuis l'éditeur : menu Outils, puis Options, cochez « Déclaration des variables obligatoire ».

De toute façon, en y regardant de plus près, nous n'avons pas besoin de la variable Bonbon dans notre programme !

En effet, dans le sous-programme « CompterLesBonbons » cette variable est utilisée pour stoker le retour de l'appel à la fonction « CompterPoche » et sert ensuite à former le message à afficher avec MsgBox.

Mais le message peut être construit directement avec l'appel à la fonction « CompterPoche » :

Sub CompterLesBonbons()

MsgBox "J'ai " & CompterPoche(1) & " bonbons dans la poche 1"
MsgBox "J'ai " & CompterPoche(2) & " bonbons dans la poche 2"

End Sub

De même dans la fonction « CompterPoche », la variable Bonbon sert de compteur provisoire dont on peut se passer en incrémentant directement « CompterPoche », sachant que sa valeur de retour est initialisée à zéro à chaque appel. La fonction se transforme alors en une variable :

Function CompterPoche(MaColonne As Long) As Long

Dim Ligne As Long ' Variable numérique pour balayer les lignes de la feuille.
Ligne = 1         ' Initialise Ligne à 1.

While Cells(Ligne, MaColonne) <> "" ' Tant que cellule(y,x) n'est pas vide.
    CompterPoche = CompterPoche + 1 ' Incrémente le nombre de bonbons.
    Ligne = Ligne + 1               ' Passe à la ligne suivante.
Wend                                ' Boucle

End Function

Le code de notre programme est maintenant plus compact. Mais est-il plus lisible pour autant ? Le programmeur ne doit pas oublier que son programme aura peut-être besoin d'être relu et compris par une autre personne.

Nous sommes fiers de notre programme, mais imaginons que maintenant, nous devions aussi compter le nombre de scoubidous et de bonbons cachés dans le cartable de notre enfant.

Sur la feuille EXCEL, dans les cellules « C1 » à « C9 », mettez aléatoirement un « x » pour symboliser les bonbons et un « s » pour symboliser les scoubidous. Évidemment, la colonne 3 symbolise le cartable.

La fonction « CompterPoche » peut servir de base : car si actuellement, elle considère qu'une cellule non vide représente un bonbon et qu'elle ne permet donc pas de distinguer les bonbons des scoubidous, il suffit d'ajouter un argument alphabétique, nommé MonChoix, pour savoir si l'on doit compter des bonbons « x » ou des scoubidous « s » ainsi qu'une condition qui compare la valeur de la cellule avec cet argument.

Enfin, je veux une nouvelle procédure, indépendante de la procédure « CompterLesBonbons », pour compter les éléments du cartable.

Le code de la fonction devient :

Function CompterPoche(MaColonne As Long, MonChoix As String) As Long

Dim Ligne As Long    ' Variable numérique pour balayer les lignes de la feuille.

Ligne = 1 ' Initialise Ligne à 1

While Cells(Ligne, MaColonne) <> ""             ' Tant Que cellule(y,x) n'est pas vide...
    If Cells(Ligne, MaColonne) = MonChoix Then  ' Si la cellule est ce qu'il faut compter,
        CompterPoche = CompterPoche + 1         ' Incrémente le nombre d'items,
    End If                                      ' Fin de la condition.
    Ligne = Ligne + 1                           ' Passe à la ligne suivante.
Wend                                            ' Boucle.

End Function

Et je peux écrire ma nouvelle procédure « CompterLeCartable » :

Sub CompterLeCartable()

MsgBox "J'ai " & CompterPoche(3, "x") & " bonbons et " _
        & CompterPoche(3, "s") & " scoubidous dans mon cartable."

End Sub

Mais il faut aussi modifier le sous-programme « CompterLesBonbons » que nous avons écrit, en ajoutant le nouvel argument requis pour l'appel de la fonction « CompterPoche » :

Sub CompterLesBonbons()

MsgBox "J'ai " & CompterPoche(1, "x") & " bonbons dans la poche 1"
MsgBox "J'ai " & CompterPoche(2, "x") & " bonbons dans la poche 2"

End Sub

Et c'est cela le problème. Je dois modifier une procédure qui marche bien, pour ajouter un argument qui à première vue, ne sert pas à grand-chose. Il faut donc trouver une autre solution pour que l'appel à « CompterPoche » reste compatible…

C'est possible, en déclarant facultatif le nouvel argument MonChoix, comme le fait l'instruction MsgBox que nous avons étudiée, avec ses arguments Buttons, Title…

La procédure gère alors deux situations :

• soit MonChoix n'est pas renseigné et est une chaîne vide, dans ce cas « CompterPoche » est incrémenté ;

• soit MonChoix est renseigné par « x » ou « s » et dans ce cas « CompterPoche » est incrémenté uniquement si la cellule contient la valeur de l'argument MonChoix.

Dit autrement, si MonChoix est vide ou si la cellule analysée vaut MonChoix, alors « CompterPoche » est incrémentée.

Nous ne l'avions pas encore vu, mais une « Condition » peut être constituée de plusieurs expressions. Généralement on combine les opérateurs logiques OR (ou bien) et AND (et aussi) pour générer une condition multicritères.

Le code modifié permet à la procédure « CompterLesBonbons » de rester compatible avec l'ancien appel de « CompterPoche », car le nouvel argument est facultatif :

Function CompterPoche(MaColonne As Long, Optional MonChoix As String) As Long

Dim Ligne As Long
Ligne = 1

While Cells(Ligne, MaColonne) <> ""
    
    If MonChoix = "" Or Cells(Ligne, MaColonne) = MonChoix Then
        CompterPoche = CompterPoche + 1
    End If
    
    Ligne = Ligne + 1
Wend

End Function

Super, notre nouvelle procédure marche à merveille. Mais elle a un inconvénient. Pour compter les éléments du cartable, il faut appeler deux fois la fonction. Une fois pour compter les bonbons, une autre fois pour compter les scoubidous. Si nous pouvions ne l'appeler qu'une seule fois… le temps de traitement serait divisé par deux. Ça peut valoir la peine d'étudier une autre solution…

Souvenez-vous de notre fonction d'origine :

Function CompterPoche(MaColonne As Long) As Long

Dim Ligne As Long ' Variable numérique pour balayer les lignes de la feuille.
Ligne = 1         ' Initialise Ligne à 1.

While Cells(Ligne, MaColonne) <> "" ' Tant que cellule(y,x) n'est pas vide.
    CompterPoche = CompterPoche + 1 ' Incrémente le nombre de bonbons.
    Ligne = Ligne + 1               ' Passe à la ligne suivante.
Wend                                ' Boucle

End Function

Nous allons ajouter à cette fonction deux arguments facultatifs de type numérique : NbBonbon qui sera incrémenté quand la cellule analysée vaut « x », et NbScoubidou qui sera incrémenté quand la cellule analysée vaut « s ».

Nous avions déjà utilisé plusieurs fois la notion d'argument, facultatif ou non, mais jusqu'à présent l'argument était passé en dur : il valait 1, 2, 3, « x » ou « s ». Ici nous allons passer en argument une variable, préalablement déclarée dans la procédure « CompterLeCartable ». En VBA, cette variable est passée par référence (sauf si le mot clé ByVal est explicitement déclaré) et peut donc être modifiée.

Concrètement, cela signifie que la procédure appelée peut modifier la valeur de la variable passée en argument. Et la procédure appelante peut utiliser la nouvelle valeur de la variable ainsi modifiée.

Vous allez mieux comprendre en exécutant la procédure « CompterLeCartable » :

Sub CompterLeCartable()

Dim MesBonbon As Long    ' Variable numérique qui contiendra le nombre de bonbons
Dim MesScoubidou As Long ' Variable numérique qui contiendra le nombre de scoubidous

Call CompterPoche(3, MesBonbon, MesScoubidou)  ' Alimente MesBonbon et MesScoubidou

MsgBox "J'ai " & MesBonbon & " bonbons et " _
        & MesScoubidou & " scoubidous dans mon cartable."

End Sub

Notez que la fonction « CompterPoche » est conçue pour retourner une variable, qui n'est pas utilisée ici. C'est pourquoi il faut faire précéder l'appel à la fonction du mot clé Call pour prévenir VBA que nous faisons délibérément un appel simple, sans stocker la valeur retournée par la fonction.

La fonction « CompterPoche » a été modifiée ainsi :

Function CompterPoche(MaColonne As Long, _
                      Optional NbBonbon As Long, _
                      Optional NbScoubidou As Long) As Long

Dim Ligne As Long     ' Variable numérique pour balayer les lignes de la feuille.
Ligne = 1             ' Initialise Ligne à 1

While Cells(Ligne, MaColonne) <> ""  ' Tant que cellule(y,x) n'est pas vide.
    
    CompterPoche = CompterPoche + 1 ' Incrémente le nombre d'éléments trouvés.
    If Cells(Ligne, MaColonne) = "x" Then NbBonbon = NbBonbon + 1       ' Les bonbons.
    If Cells(Ligne, MaColonne) = "s" Then NbScoubidou = NbScoubidou + 1 ' Les scoubidous.
    Ligne = Ligne + 1 ' Passe à la ligne suivante.
    
Wend

End Function

Le plus déroutant pour un débutant est que les noms des variables sont différents, alors qu'ils représentent les mêmes portions de mémoire pour l'ordinateur. Par exemple, MesBonbon est la variable utilisée comme premier argument dans l'appel de la fonction « CompterPoche », alors que cette dernière va utiliser NbBonbon dans ses traitements, et donc finalement, modifier la valeur de MesBonbon !

Rassurez-vous, vous parviendrez rapidement à jongler avec ces subtilités.

Les arguments d'une fonction peuvent aussi être nommés. La syntaxe des arguments nommés est :

NomArgument  := Valeur

Dans ce cas, l'ordre de passage des arguments est libre.

L'appel à la fonction « CompterPoche » peut alors prendre cette forme :

Call CompterPoche(3, NbScoubidou:=MesScoubidou, NbBonbon:=MesBonbon)

Cette forme d'appel est cependant rarement utilisée, car elle nécessite plus de temps de rédaction, mais la lecture est beaucoup plus claire. La généralisation de cette pratique simplifierait pourtant la maintenance des programmes.

Une nouvelle façon d'aborder le problème…

Il faut déclarer en en-tête du module les variables MesBonbon et MesScoubidou, pour qu'elles puissent être utilisées par toutes les procédures du module.

Et alimenter ces variables dans la fonction « CompterPoche » qui du fait n'a plus besoin d'argument facultatif.

Il faut cependant prendre soin de mettre les variables à zéro avant d'appeler la fonction, au cas où elles auraient déjà été utilisées (par d'autres fonctions ou par cette fonction), ce qui fausserait les résultats.

Option Explicit

Dim MesBonbon As Long    ' Variable numérique qui contiendra le nombre de bonbons
Dim MesScoubidou As Long ' Variable numérique qui contiendra le nombre de scoubidous

Sub CompterLeCartable()

Call CompterPoche(3)  ' Alimente MesBonbon et MesScoubidou

MsgBox "J'ai " & MesBonbon & " bonbons et " _
        & MesScoubidou & " scoubidous dans mon cartable."

End Sub

Function CompterPoche(MaColonne As Long) As Long

Dim Ligne As Long               ' Variable numérique pour balayer les lignes de la feuille.
Ligne = 1                       ' Initialise Ligne à 1
MesBonbon = 0: MesScoubidou = 0 ' Variables mises à zéro par sécurité.

While Cells(Ligne, MaColonne) <> ""  ' Tant Que cellule(y,x) n'est pas vide.

    CompterPoche = CompterPoche + 1 ' Incrémente le nombre d'éléments trouvés.
    If Cells(Ligne, MaColonne) = "x" Then MesBonbon = MesBonbon + 1         ' Les bonbons.
    If Cells(Ligne, MaColonne) = "s" Then MesScoubidou = MesScoubidou + 1   ' Les scoubidous.
    Ligne = Ligne + 1 ' Passe à la ligne suivante.

Wend
 
End Function

Notez l'usage du symbole « : » (deux points) qui permet de coder plusieurs instructions sur une même ligne.

Cette dernière méthode est peut-être la plus simple. Comme toujours en programmation, il y a plusieurs manières de faire. C'est à chacun de s'approprier la méthode qui lui convient le mieux, tout en restant en adéquation avec les nécessités de l'application à développer.

Avant de continuer je vous propose un peu de théorie sur les variables, car ces notions nous seront nécessaires par la suite.

L'aide d'EXCEL donne un tableau complet, ici je ne reprends que les types couramment utilisés :

Rappel des évidences :

• vous devez toujours adapter le type des variables utilisées à vos besoins ;
• plus vous minimisez les ressources utilisées, plus vos traitements tourneront rapidement ;
• un dépassement de capacité provoque un plantage de l'application.

Cas particulier : une chaîne de caractères peut avoir une longueur fixe, par exemple 20 caractères : Dim Nom As String * 20

Type défini par l'utilisateur :

Ce type est utilisé pour la déclaration de variables contenant plusieurs types d'information, qui seront ainsi regroupés.
Par exemple une couleur est composée des éléments rouge, vert, bleu.

Nous pouvons, dans un premier temps, définir un type « Couleur » contenant ces trois éléments.

Type Couleur          ' Déclaration d'un type de données personnel
    Rouge As Long     ' Définition du 1er élément
    Vert As Long      ' Définition du 2e élément
    Bleu As Long      ' Définition du 3e élément
End Type              ' Fin de la déclaration

Puis, dans un second temps, créer une variable « MaCouleur » de type « Couleur ».

Dim MaCouleur As Couleur ' Création d'une variable MaCouleur de type "Couleur"

L'appel des éléments de la variable « Couleur » se fait en mettant un point après la variable :

Sub Test()
Dim MaCouleur As Couleur

MaCouleur.Rouge = 0
MaCouleur.Vert = 255
MaCouleur.Bleu = 0

End Sub

Un tableau est une variable comportant plusieurs éléments. On accède à un élément en utilisant un indice.

Le premier élément de la variable est l'indice 0. Dim MonTableau(i) dimensionne un tableau des indices 0 à i soit i+1 éléments. Sauf si l'on utilise l'instruction Option Base 1, dans ce cas le premier élément est l'indice 1. Mais fuyez cette instruction qui déroge aux habitudes.

Pour prévenir toute confusion, l'usage est de spécifier explicitement la plage d'indice pour la variable, en utilisant la syntaxe (IndiceMini To IndiceMaxi).

Les règles de portée des tableaux sont les mêmes que pour les variables.

Par exemple, ce tableau peut stocker le nom de 20 personnes :

Dim MonTableau(0 To 19) As String ' Déclare un tableau de 20 éléments
MonTableau(0) = "Toto" 'Initialise le 1er élément
MonTableau(1) = "Titi" 'Initialise le 2e élément

Un tableau peut avoir jusqu'à 60 dimensions. Chaque dimension a une plage d'indices qui lui est propre. L'exemple suivant permet de stocker la valeur des 100 cellules d'un carré de 10 sur 10, dans un tableau à deux dimensions :

Sub MémoriseUnCarré()

Dim MaCellule(1 To 10, 1 To 10) As Variant ' Tableau de 10 éléments à deux dimensions.
Dim y As Integer, x As Integer
 
For y = 1 To 10     ' Boucle sur les 10 premières lignes.
    For x = 1 To 10 ' Boucle sur les 10 premières colonnes.
        MaCellule(y, x) = Cells(y, x) ' Mémorise le contenu de la cellule
    Next x          ' Colonne suivante.
Next y              ' Ligne suivante

End Sub

Un tableau de données peut avoir des indices négatifs : Dim MonTableau(-4 To 7) As Integer

Le premier élément du tableau vaut 3 et son indice est -4. L'indice 6 représente le 11^e élément du tableau, sa valeur est 5.

Le mot clé Lbound(NomDuTableau), donne le numéro de l'indice le plus petit du tableau.
Le mot clé Ubound(NomDuTableau), donne le numéro de l'indice le plus grand du tableau.

Un tableau peut être dynamique, c'est-à-dire que sa dimension n'est pas définie lors de la déclaration de la variable, mais au cours du traitement, suivant les besoins nécessaires. Cela permet l'optimisation de la mémoire :

• Le mot clé ReDim Preserve NomDuTableau(i To n), indique qu'il faut redimensionner le tableau de i à n, en conservant les données déjà existantes.

  Attention : pour les tableaux à plusieurs dimensions, seule la dernière dimension peut être redimensionnée.

• Le mot clé ReDim NomDuTableau(i To n), indique qu'il faut redimensionner le tableau de i à n, sans conserver les données existantes. Les données sont alors toutes mises à zéro.

Un tableau de type variant peut être alimenté rapidement : MonTableau = Array("Toto", "Titi", ...)
Un tableau à plusieurs dimensions peut aussi être dynamique.
Les règles de portée des tableaux dynamiques sont les mêmes que pour les variables.

Par exemple pour mémoriser la valeur des cellules de la colonne A, en sachant qu'une cellule vide indique la fin des cellules à mémoriser, il faut utiliser un tableau dynamique, car nous ne savons pas à l'écriture du code combien il y aura d'éléments à mémoriser.

Sub MémoriseColonneA()

Dim MaCellule() As Variant ' Déclare un tableau dynamique à une dimension.
Dim y As Long              ' Variable qui indique la ligne à analyser.
Dim i As Long              ' Variable qui dimensionne le tableau.
 
y = 1
While Cells(y, 1) <> ""          ' Boucle sur les lignes de la colonne A.
    ReDim Preserve MaCellule(i)  ' Redimensionne le tableau MaCellule sans l'effacer.
    MaCellule(i) = Cells(y, 1)   ' Mémorise la valeur de la cellule.
    i = i + 1                    ' Incrémente l'indice du tableau.
    y = y + 1                    ' Passe à la ligne suivante.
Wend

' Boucle sur les éléments du tableau pour afficher
' l'indice et la valeur de l'élément.
For i = LBound(MaCellule) To UBound(MaCellule)
    MsgBox "Indice : " & i & Chr(10) & Chr(13) & "Valeur : " & MaCellule(i)
Next i

End Sub

Pour être presque complet sur le sujet des variables, il faut parler des constantes et des énumérations.

Une constante représente une variable, numérique ou de type chaîne, qui ne peut pas être modifiée. Sa valeur reste donc constante.
Le mot clé Const permet de déclarer une constante et de définir sa valeur fixe.
Les règles de portée des constantes sont les mêmes que pour les autres variables.
Généralement, une constante est déclarée Public dans l'en-tête de module pour pouvoir être utilisée par toutes les procédures de l'application.

Exemple de déclaration :

Public Const MaVersion = "Version 1.0" ' Constante de type chaîne
Public Const MaCouleur = 15 ' Constante de type numérique

L'énumération permet d'attribuer des noms à des valeurs numériques, de la même manière que les constantes, à la différence que ces noms sont regroupés dans un ensemble. Les énumérations sont peu utilisées, pourtant elles peuvent rendre de précieux services dans l'utilisation de nos procédures, à la manière de l'instruction MsgBox qui affiche une liste déroulante des arguments disponibles lorsque l'on commence la saisie :

L'énumération est généralement déclarée Public dans l'en-tête de module pour pouvoir être utilisée par toutes les procédures de l'application.
Le mot clé Enum permet de déclarer une énumération, et ses éléments.
Le mot clé End Enum doit être utilisé pour indiquer la fin de l'énumération.
Les valeurs des constantes sont toujours de type Long, donc un entier positif ou négatif. Elles peuvent être omises, mais dans ce cas le premier membre aura pour valeur 0. Les autres membres auront la valeur du membre précédent plus 1.

Dans l'exemple qui suit, nous déclarons de type Enum une variable nommée MesCouleurs et affectons une valeur à chacun de ses éléments :

Public Enum MesCouleurs
    Bleu = 8210719
    Blanc = 16777215
    Rouge = 255
    Vert = 5287936
    Jaune = 65535
End Enum

Puis nous créons une procédure « CouleurCellule » qui change la couleur de fond de la cellule active.

L'argument ChoixCouleur est une variable qui indique la couleur à utiliser. Nous déclarons cette variable de type MesCouleurs :

Sub CouleurCellule(ChoixCouleur As MesCouleurs)
    ActiveCell.Interior.Color = ChoixCouleur
End Sub

Ainsi, lorsque l'on appelle cette fonction, une liste déroulante des éléments disponibles s'ouvre pour permettre au programmeur de piocher la couleur de son choix :

Vous remarquerez que la liste déroulante classe automatiquement par ordre alphabétique croissant les éléments disponibles.

La technique est très facile à mettre en place, et elle sécurise les développements, tout en restant souple. Car il reste possible de faire un appel à la fonction en passant en argument une valeur différente des éléments proposés, ou de passer en argument une variable.
De plus, il suffit de modifier la valeur de la constante (une seule fois) pour que la modification se répercute à l'ensemble du code.

Exemples d'appel avec une valeur déterminée ou avec une variable :

Call CouleurCellule(0)
Call CouleurCellule(MaCouleur)

Nous utilisons l'éditeur VBA pour écrire notre code. grâce à son moteur intelligent, il reconnaît les mots clés du langage et les affiche en bleu. Les commentaires sont affichés en vert.
Vous pouvez changer ces paramètres de l'éditeur dans le menu « Outils », puis « Options ».

L'éditeur permet aussi de suivre le déroulement d'un traitement, explorer la valeur des variables. C'est le débogage.
Nous avons déjà vu que la touche [F5] permet d'exécuter une procédure, et que la touche [F8] permet de suivre le traitement pas à pas. L'éditeur offre d'autres possibilités, dont voici les principales :

[Ctrl][F8] : exécute jusqu'au curseur. La ligne est en surbrillance jaune.

[Ctrl][F9] : placer un point d'arrêt. La ligne est en surbrillance rouge. Le traitement s'arrêtera à cette ligne.

Placer la souris sur une variable : affiche dans une info-bulle la valeur de la variable.

Vous pouvez aussi sélectionner la variable et faire [Majuscule][F9]

Placer la souris sur une instruction : affiche dans une info-bulle la valeur de l'instruction.

Vous pouvez aussi sélectionner l'instruction et faire [Majuscule][F9]

Ajouter un espion sur une valeur sélectionnée : clic droit, puis « ajouter un espion ».

La valeur de l'expression reste affichée en permanence.

C'est valable aussi pour un tableau, en ne sélectionnant que le tableau et non pas l'ensemble tableau plus indice :

Le tableau n'a pas de valeur, il faut cliquer sur le + pour voir les différents éléments :

C'est valable aussi pour certains objets, ce qui permet de connaître les différentes propriétés de l'objet :

Pensez aussi à la fenêtre d'exécution qui s'ouvre avec [Ctrl][G] :

Elle permet d'interroger la valeur d'une variable, ou d'une expression, précédée d'un point d'interrogation. Par exemple pour connaître la valeur de la variable y ou la couleur de fond de la cellule « A1 » :

Ou elle permet d'afficher la valeur d'une variable avec l'instruction Debug.Print Variable placée dans le code. Dans notre procédure « MémoriseColonneA » nous affichons la valeur des cellules dans un message, nous pouvons aussi l'afficher dans la fenêtre d'exécution :

For i = LBound(MaCellule) To UBound(MaCellule)
    Debug.Print "Indice : " & i & " Valeur : " & MaCellule(i)
Next i

Plus fort encore, la fenêtre d'exécution permet de lancer une procédure, une fonction ou une instruction :

Lorsqu'une partie du code a un intérêt particulier, vous pouvez y placer un signet, et vous déplacer rapidement d'un signet à l'autre :

Icônes pour placer un signet, aller au suivant, retourner au précédent, effacer les signets.

Pour une saisie rapide de votre code, abusez de [Ctrl][J], qui ouvre la liste des mots clés, des variables, des propriétés, des méthodes et procédures, et sélectionne le mot désiré à mesure de votre saisie.

Enfin, avant de tester un traitement, testez déjà sa syntaxe par le menu « Débogage », « Compiler VBAProjet ».

Pour mettre en pratique une grande partie de ce que nous venons d'étudier sur la programmation en VBA, je vous propose un exercice qui va permettre de se faire la main sur les algorithmes.

Sur une feuille EXCEL mettre dans les cellules « A1 » à « A5 » les valeurs suivantes : 5, 6, 2, 1, 3.

Nous allons créer une première procédure pour récupérer ces données, et une seconde pour les trier par ordre croissant ou décroissant. Bien sûr, cette procédure devra marcher quel que soit le nombre de données à trier.

Enfin nous allons restituer ces données triées en « B1 » à « B5 ».

Récupérer les données de la feuille pour les charger dans un tableau, et inversement, afficher en colonne B les données d'un tableau, nous savons faire. Par contre il nous faut un algorithme pour trier les données.

Avant d'aller voler sur internet un algorithme scientifique comme QuickSort, essayons de créer notre propre algorithme, histoire de faire travailler nos méninges…

Le plus simple est de faire une boucle sur les données. Donc ici de N à N+4. Comparer si N+1 est inférieure à N (dans le cas d'un tri par ordre croissant, ou supérieure pour un ordre décroissant). Dans ce cas, il faut intervertir les données N+1 et N. Et continuer avec la comparaison entre N et N+2… Jusqu'à N+4.

En fin de boucle l'on repart de N+1 à N+4.
Et ainsi de suite.

Ce qui donne la représentation suivante :

Et ainsi de suite…

Nous avons deux boucles dans notre algorithme, l'une imbriquée dans l'autre :

For n = 0 To 4
    For i = n + 1 to 4
        ' Comparaison de n et i, avec échange des données si besoin selon l'ordre désiré.
        ' L'échange des données nécessite trois traitements comme démontré ci-joint.
    Next i
Next n

La procédure de chargement des données, d'appel de la fonction de tri, et d'affichage du résultat :

'-------------------------------------------------------------------------------
Sub ChargeLesDonnées()
'-------------------------------------------------------------------------------
Dim MonTableau() As Variant ' Déclare un tableau Variant dynamique.
Dim y As Long               ' Indique la ligne à analyser.
Dim i As Long               ' Indice du tableau.

y = 1
While Cells(y, 1) <> ""           ' Boucle sur les lignes de la colonne A.
    ReDim Preserve MonTableau(i)  ' Redimensionne le tableau d'après l'indice.
    MonTableau(i) = Cells(y, 1)   ' Mémorise la valeur de la cellule.
    i = i + 1                     ' Incrémente l'indice du tableau.
    y = y + 1                     ' Passe à la ligne suivante.
Wend

Call TriDonnées(MonTableau(), True)   ' Appelle la fonction de tri du tableau.

For y = LBound(MonTableau) To UBound(MonTableau) ' Boucle sur les éléments.
    Cells(y + 1, 2) = MonTableau(y)              ' Affiche l'élément en B.
Next y

Application.ScreenUpdating = True

End Sub

La procédure avec notre algorithme de tri :

'-------------------------------------------------------------------------------
Function TriDonnées(ByRef TabDonnées() As Variant, _
                    Optional OrdreCroissant As Boolean = True)
'-------------------------------------------------------------------------------
' Procédure pour trier par ordre croissant ou décroissant les données passées
' en argument dans un tableau à une dimension. Le tableau est actualisé.
' Le tableau étant de type variant les chaînes et les numériques sont gérés.
'-------------------------------------------------------------------------------
Dim n As Long
Dim i As Long
Dim Tps As Variant

For n = LBound(TabDonnées) To UBound(TabDonnées) ' Boucle sur les données.

    For i = n + 1 To UBound(TabDonnées) ' Boucle depuis la donnée suivante.
        
        ' Gestion de l'ordre croissant ou décroissant.
        If (OrdreCroissant = True And TabDonnées(i) < TabDonnées(n)) _
        Or (OrdreCroissant = False And TabDonnées(i) > TabDonnées(n)) Then
            Tps = TabDonnées(n)             ' Mémorise la donnée à échanger
            TabDonnées(n) = TabDonnées(i)   ' Échange la donnée...
            TabDonnées(i) = Tps             ' Avec la donnée mémorisée
        End If
    
    Next i
   
Next n

End Function

Notre procédure donne satisfaction, pourtant elle comporte un défaut : elle fait de nombreux échanges, et chaque échange nécessite trois traitements. De plus la gestion de l'ordre de tri croissant ou décroissant nécessite une condition complexe. Le tout demande des ressources importantes, donc du temps. Il est possible que la fonction soit trop longue à exécuter sur des tableaux plus volumineux. Nous devons trouver un algorithme plus rapide.

Nous gardons notre boucle sur les données. Ici de N à N+4. Nous lisons toutes les données de N+1 à N+4, et mémorisons l'indice de la valeur la plus petite, ici c'est N+3. La lecture terminée, nous échangeons N avec N+3. Nous continuions notre boucle en lisant les données de N+1 à N+4, puis de N+2 à N+4. Ainsi de suite.

Les données sont classées par ordre croissant. Si l'ordre décroissant est désiré, il faut échanger les données.

Ce qui donne la représentation suivante :

'-------------------------------------------------------------------------------
Sub TriDonnéesBis(ByRef TabDonnées() As Variant, _
                  Optional OrdreCroissant As Boolean = True)
 '-------------------------------------------------------------------------------
Dim i As Long
Dim n As Long
Dim RefPos As Long
Dim Tps As Variant
 
For n = LBound(TabDonnées) To UBound(TabDonnées) ' Boucle sur tous les éléments.
    RefPos = n ' Indice de référence = élément actuel
    For i = n + 1 To UBound(TabDonnées) ' Boucle sur les éléments suivants.
        ' Si un élément est plus petit que l'élément de référence alors il devient
        ' l'élément de référence (nous mémorisons son indice).
        If TabDonnées(i) < TabDonnées(RefPos) Then RefPos = i
    Next i ' RefPos contient l'indice du plus petit élément.
    If RefPos <> n Then ' Si un élément plus petit que l'élément actuel est trouvé.
        Tps = TabDonnées(n) ' alors fait l'échange.
        TabDonnées(n) = TabDonnées(RefPos)
        TabDonnées(RefPos) = Tps
    End If
Next n
  
If OrdreCroissant = False Then ' Si Ordre décroissant alors échange les données.
    For n = LBound(TabDonnées) To UBound(TabDonnées)
        Tps = TabDonnées(UBound(TabDonnées) - n)
        TabDonnées(UBound(TabDonnées) - n) = TabDonnées(n)
        TabDonnées(n) = Tps
        If UBound(TabDonnées) - n < n Then Exit For
    Next n
End If

End Sub

Vous trouverez sur internet l'algorithme QuickSort, qui comme son nom l'indique, permet de trier rapidement les données. Wikipédia et d'autres sites donnent des informations plus précises sur la logique mise en œuvre.

Il existe plusieurs versions de cet algorithme, attention, toutes ne se valent pas. J'ai repris celle qui est la plus rapide. Vous pouvez remplacer la déclaration Variant par Long pour optimiser l'algorithme pour le traitement des entiers.

Sources : http://rosettacode.org/wiki/Sorting_algorithms/Quicksort

'-------------------------------------------------------------------------------
Private Sub QuickSort(ByRef TabDonnées() As Variant, ByVal Gauche As Long, _
                      ByVal Droite As Long)
'-------------------------------------------------------------------------------
Dim i As Long, j As Long, Temp As Variant, Pivot As Variant
 
i = Gauche
j = Droite
Pivot = TabDonnées((Gauche + Droite) / 2)
 
Do
    While Pivot > TabDonnées(i): i = i + 1: Wend
    While TabDonnées(j) > Pivot: j = j - 1: Wend
 
    If j + 1 > i Then ' If i <= j Then
        Temp = TabDonnées(i)
        TabDonnées(i) = TabDonnées(j)
        TabDonnées(j) = Temp
        j = j - 1: i = i + 1
    End If
    
Loop Until i > j ' ou Loop While i < j
 
If Gauche < j Then Call QuickSort(TabDonnées(), Gauche, j)
If i < Droite Then Call QuickSort(TabDonnées(), i, Droite)
End Sub

Il ne reste plus qu'à comparer nos trois algorithmes, sur un tableau volumineux.

Pour cela j'utilise la fonction EXCEL « =ALEA.ENTRE.BORNES(0;10000) » que je recopie sur 10 000 lignes et que je colle en valeurs.

Pour chronométrer la durée des traitements, dans la procédure « ChargeLesDonnées » je déclare une variable T de type Double. Avant l'appel d'une fonction de tri, je mémorise l'heure : T = Timer 

Après le traitement, j'affiche la durée écoulée : Timer - T 

Résultat de la course entre ces trois traitements :

• TriDonnées(MonTableau(), True) dure 14,3 secondes. 

• TriDonnéesBis(MonTableau(), True) dure 2,24 secondes. Effectivement, cela valait la peine de rechercher un algorithme moins gourmand en ressources.

• QuickSort(MonTableau(), LBound(MonTableau), UBound(MonTableau)) : 0,02 seconde. Il n'y a pas photo. Cet algorithme est 100 fois plus rapide que notre meilleur algorithme.

Il faudrait au moins, pour sauver l'honneur, trouver un algorithme qui permet de passer sous la barre symbolique de la seconde.

Pour gagner en temps de traitement, il faut moins de tests conditionnels. Ce sont eux qui sont gourmands en ressources.

Armé d'une feuille et d'un crayon, après deux jours de réflexion, j'ai un nouvel algorithme en tête…

Je pars sur un principe de deux tableaux. Les éléments du tableau source vont être lus par une boucle. L'élément analysé sera classé immédiatement dans le second tableau. Pour trouver son emplacement dans ce tableau classé par ordre croissant, il est trop long de lire un à un les éléments déjà classés. Une recherche par dichotomie est bien plus rapide. Une fois connu l'emplacement où insérer l'élément, il faut déplacer vers la fin du tableau les éléments déjà classés afin de libérer la place, puis y copier l'élément analysé.

Par exemple, pour classer l'élément de valeur 55 dans un tableau déjà classé : 20, 30, 30, 40, 50, 60, 80, 100, les étapes à suivre sont les suivantes :

L'algorithme de recherche dichotomique (non optimisé, pour une meilleure compréhension), est décrit dans la fonction « TableauRecherchePosition » à la page suivante.

Notez que lorsque l'on doit insérer un nouvel élément dans la partie basse du tableau des données déjà classées, plutôt que de décaler les données vers la fin du tableau, ce qui déplace de nombreuses données, il est plus judicieux de fonctionner à l'envers, en déplaçant les données vers l'autre côté, quitte à utiliser des indices négatifs, ce que le VBA permet.

'-------------------------------------------------------------------------------
Private Sub TableauDécalerElément(ByRef TabDonnées() As Variant, _
        ByRef IndiceElément As Long, ByRef IndiceMini As Long, ByRef IndiceMaxi As Long)
'-------------------------------------------------------------------------------
Dim i As Long

If IndiceElément > (IndiceMaxi + IndiceMini) / 2 Then
    For i = IndiceMaxi To IndiceElément Step -1 ' Décale vers le haut.
        TabDonnées(i + 1) = TabDonnées(i)
    Next i
    IndiceMaxi = IndiceMaxi + 1 ' Nouvel indice maxi du tableau.
Else
    For i = IndiceMini To IndiceElément         ' Décale vers le bas.
        TabDonnées(i - 1) = TabDonnées(i)
    Next i
    IndiceMini = IndiceMini - 1 ' Nouvel indice mini du tableau.
    IndiceElément = IndiceElément - 1 ' Décale l'emplacement du nouvel élément.
End If

End Sub

Le code complet de la fonction de tri :

'-------------------------------------------------------------------------------
Sub TriRapide(ByRef TabDonnées() As Variant)
'-------------------------------------------------------------------------------
Dim i As Long, n As Long, TabClassés() As Variant
ReDim TabClassés(-UBound(TabDonnées) - 1 To UBound(TabDonnées) + 1)
Dim TabDébut As Long, TabFin As Long

' Si moins de deux données à trier alors quitte.
If Abs(UBound(TabDonnées) - LBound(TabDonnées)) < 1 Then Exit Sub

' Classe les 2 premiers éléments par ordre croissant.
n = LBound(TabDonnées)
If TabDonnées(n + 1) > TabDonnées(n) Then i = 1
TabClassés(n) = TabDonnées(n + 1 - i)
TabClassés(n + 1) = TabDonnées(n + i)

TabDébut = LBound(TabDonnées): TabFin = LBound(TabDonnées) + 2 ' Limites tableau classé

' Boucle sur les autres éléments à classer.
For n = 2 + LBound(TabDonnées) To UBound(TabDonnées)
    ' Recherche la position dans la liste des éléments classés.
    i = TableauRecherchePosition(TabClassés(), TabDébut, TabFin, TabDonnées(n))
    ' Décale les éléments déjà classés pour faire une place.
    Call TableauDécalerElément(TabClassés(), i, TabDébut, TabFin)
    ' Insère l'élément dans la liste des éléments classés.
    TabClassés(i) = TabDonnées(n)
Next n

' Retourne le tableau classé :
n = LBound(TabDonnées)
For i = TabDébut To TabFin - 1
    TabDonnées(n) = TabClassés(i):
    n = n + 1
Next i
End Sub

'-------------------------------------------------------------------------------
Function TableauRecherchePosition(ByRef TabDonnées() As Variant, ByVal Début As Long, _
                             ByVal Fin As Long, ByVal ValRecherchée As Variant) As Long
'-------------------------------------------------------------------------------
Dim Milieu As Long

' Si nouvelle extrémité inférieure ou supérieure
If ValRecherchée <= TabDonnées(Début) Then TableauRecherchePosition = Début: Exit Function
If ValRecherchée >= TabDonnées(Fin - 1) Then TableauRecherchePosition = Fin: Exit Function

Do
    Milieu = (Début + Fin) / 2 ' Calcule le milieu du tableau borné par Début et Fin.
    ' Si l'élément à classer est compris entre Milieu et Milieu+1
    If ValRecherchée >= TabDonnées(Milieu) And ValRecherchée <= TabDonnées(Milieu + 1) Then
        TableauRecherchePosition = Milieu + 1 ' Retourne la position où insérer l'élément.
        Exit Do                               ' Sort de la boucle
    End If
    If ValRecherchée > TabDonnées(Milieu) Then  ' Compare l'élément avec le milieu
        Début = Milieu + 1                      ' Nouvelle borne de début.
    Else
        Fin = Milieu - 1                        ' Nouvelle borne de fin.
    End If
Loop
End Function

Testons la procédure : 0,37 seconde. C'est 20 fois plus que QuickSort, mais 50 fois moins que notre première procédure.

Grâce à ces exercices, vous manipulez désormais les tableaux de données avec aisance, ainsi que la recherche par dichotomie, une technique qui permet de réduire considérablement le temps d'exécution de certains traitements. Voici ici une version plus perfectionnée que la procédure présentée à la page précédente, qui gère le cas où la valeur cherchée n'est pas trouvée (retourne l'indice où devrait être incluse la valeur et renseigne Trouvé = Faux), ainsi que le cas où plusieurs éléments ont la valeur cherchée (retourne l'indice de la première ou de la dernière valeur et renseigne Trouvé = Vrai) :

'-------------------------------------------------------------------------------
Public Function RechercheDichotomique(TabDonnées() As Variant, ByVal ValRecherchée As Variant, _
                                      Optional PremierSiEgalité As Boolean = True, _
                                      Optional ByRef Trouvé As Boolean) As Long
'-------------------------------------------------------------------------------
' Retourne suivant que ValRecherchée est trouvée ou non :
' - Si trouvée : l'indice de la première valeur (si PremierSiEgalité=Vrai) et renseigne Trouvé=Vrai.
'                l'indice de la dernière valeur (si PremierSiEgalité=Faux) et renseigne Trouvé=Vrai.
' - Si non trouvée : l'indice où devrait être incluse la valeur et renseigne Trouvé=Faux.
'-------------------------------------------------------------------------------
Dim Début As Long, Fin As Long, i As Long, Anc As Long

Trouvé = False
Début = LBound(TabDonnées): Fin = UBound(TabDonnées): Anc = Début

If ValRecherchée > TabDonnées(Fin) Then RechercheDichotomique = Fin + 1: Exit Function
If ValRecherchée < TabDonnées(Début) Then RechercheDichotomique = Début: Exit Function
    
' Méthode 1: En cas d'égalité dans les valeurs trouvées alors c'est l'indice le plus bas :
If PremierSiEgalité = True Then
    Do
        i = (Début + Fin) / 2
        If ValRecherchée > TabDonnées(i) Then Début = i + 1: Anc = Début Else Fin = i - 1
        
    Loop While Début <= Fin
    
    If ValRecherchée = TabDonnées(Anc) Then Trouvé = True
    RechercheDichotomique = Anc
    Exit Function
End If

' Méthode 2: En cas d'égalité dans les valeurs trouvées alors retourne l'indice le plus haut :
Do
    i = (Début + Fin) / 2
    If ValRecherchée >= TabDonnées(i) Then Début = i + 1: Anc = i Else Fin = i - 1
    
Loop While Début <= Fin

If ValRecherchée = TabDonnées(Anc) Then Trouvé = True Else Anc = Anc + 1
RechercheDichotomique = Anc

End Function

Certaines des procédures que nous avons étudiées sont génériques, c'est-à-dire qu'elles peuvent être réutilisées telles quelles. C'est par exemple le cas des procédures « TriRapide » ou « RechercheDichotomique ». Elles pourront nous être utiles dans d'autres projets, alors pour les retrouver facilement nous allons les regrouper dans une « boîte à outils », qui nous suivra dans toutes nos applications et sera complétée au fur et à mesure de nos progrès en programmation.

À partir de l'éditeur VBA, choisir dans le menu « Insertion », « Module ».

Copiez en en-tête du module, les deux instructions suivantes pour respectivement, forcer la déclaration des variables, indiquer à EXCEL que pour ses comparaisons de texte, la lettre « A » équivaut à la lettre « a », sans cette instruction EXCEL va classer les majuscules avant les minuscules et « B » sera classé avant « a » :

Option Explicit
Option Compare Text

Puis copiez les procédures « TriRapide », « RecherchePosition », « QuickSort », ainsi que les autres procédures que vous souhaitez avoir dans votre boîte à outils.
Renommer le module créé (touche [F4], après avoir sélectionné le module) en « VBO ». Votre boîte à outils est créée.

Par usage (c'est facultatif) nous ferons précéder le nom des procédures du nom du module « VBO ».

Ainsi l'appel à la fonction « TriRapide » devient : Call VBO.TriRapide(MonTableau())

Lorsque vous tapez « VBO. » la liste des procédures publiques du module s'ouvre…

Le fait de préciser le nom du module contenant la procédure permet aussi d'utiliser des procédures qui portent le même nom, mais situées dans des modules différents.

Notre application ne comporte désormais plus qu'une seule procédure spécifique au classeur EXCEL actif, et qui ne pourra probablement pas être réutilisée telle quelle dans une autre application, alors que les procédures généralistes que nous pouvons réemployer sont codées dans un module à part.

'-------------------------------------------------------------------------------
Sub ChargeLesDonnées()
'-------------------------------------------------------------------------------
Dim MonTableau() As Variant  ' Déclare un tableau Variant dynamique.
Dim y As Long                ' Indique la ligne à analyser.
Dim i As Long                ' Indice du tableau.
Dim T As Double              ' Pour calculer la durée du traitement.

y = 1
While Cells(y, 1) <> ""                 ' Boucle sur les lignes de la colonne A.
    ReDim Preserve MonTableau(i)        ' Redimensionne le tableau d'après l'indice.
    MonTableau(i) = Cells(y, 1)         ' Mémorise la valeur de la cellule.
    i = i + 1                           ' Incrémente l'indice du tableau.
    y = y + 1                           ' Passe à la ligne suivante.
Wend

T = Timer ' Mémorise l'heure avant le lancement du traitement.

Call VBO.TriRapideBis(MonTableau(), 0)     ' Trie les données (ordre croissant).

Durée = Timer - T                       ' Calcule la durée du traitement.

Application.ScreenUpdating = False                ' Bloque la mise à jour écran.
For y = LBound(MonTableau) To UBound(MonTableau)  ' Boucle sur les éléments.
    Cells(y + 1, 2) = MonTableau(y)               ' Affiche l'élément en B
Next y
Application.ScreenUpdating = True                 ' Mise à jour de l'écran.
MsgBox "Durée =  " & Durée                        ' Affiche la durée du traitement.

End Sub

Jusqu'à présent, la procédure était exécutée depuis l'éditeur VBA. Nous allons créer un « bouton » sur la feuille de calcul d'EXCEL pour lancer cette procédure.

Dans le menu « Développeur », choisissez « Insérer » et cliquez sur le « bouton » : Lorsque la souris passe sur la feuille, le curseur prend la forme d'un plus. Cliquez sur la feuille et maintenez le clic gauche enfoncé pour définir la taille du bouton, puis relâchez le clic. Dans la liste déroulante qui s'ouvre, piochez la procédure à associer au bouton. Un clic droit permet de modifier le libellé du bouton.

Vous venez de créer un événement. À chaque clic sur le bouton, la procédure est lancée.

Il existe d'autres types d'événements…

Nous avons codé nos procédures dans plusieurs modules. Et pour les exécuter nous devons soit les lancer manuellement depuis l'éditeur VBA, soit les lier à un bouton. Une procédure peut aussi être codée dans une feuille du classeur, ou, dans le classeur. Dans ce cas, la procédure codée sera lancée sur un événement lié à la feuille, ou au classeur.

Par exemple, sur la feuille 4 du classeur, pour rendre la cellule « B3 » inaccessible, j'utilise l'événement déclenché « Sur changement de cellule sélectionnée » et je code une procédure qui contrôle si la cellule active (passée en argument Target) est « B3 ». Dans ce cas je sélectionne la cellule de la ligne suivante :

Cells(y+1,x).Activate avec y = Target.Row et x = Target.column

Faire un clic droit sur l'onglet de la feuille 4, et choisir dans la liste déroulante « Visualiser le code ».
L'éditeur VBA s'ouvre. Piochez dans la liste de gauche « Worksheet » et dans la liste de droite « SelectionChange ». Une procédure vierge s'ouvre :

Écrire le code suivant, qui sera exécuté chaque fois que la cellule active de la feuille changera :

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
' Événement déclenché à chaque changement de cellule sélectionnée.
' L'argument Target représente la cellule active.

If Target.Row = 3 And Target.Column = 2 Then ' Si cellule sélectionnée = B3
    Cells(Target.Row + 1, Target.Column).Activate 'Active la cellule suivante.
End If

End Sub

Notez que dans le code, nous pouvons aussi faire appel à une procédure d'un module (sauf si elle a été déclarée Private). C'est une autre façon d'appeler nos procédures.

L'événement est lié à la feuille 4, la feuille 3 n'est donc pas concernée par cette procédure et sa cellule « B3 » peut être sélectionnée.
Pour que cette procédure concerne toutes les feuilles du classeur, il faut que l'événement soit déclaré au niveau du classeur et non plus de la feuille.
Dans la partie droite de l'éditeur VBA, cliquez sur « ThisWorkbook ». Piochez dans la liste de gauche de l'éditeur « Workbook » et dans la liste de droite « SheetSelectionChange ». Une procédure vierge s'ouvre :

Écrire le code suivant, qui sera exécuté chaque fois que la cellule active changera dans le classeur :

Private Sub WorkBook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Array)
' Événement déclenché à chaque changement de cellule sélectionnée.
' L'argument Sh représente la feuille active.
' L'argument Target représente la cellule active.

If Target.Row = 3 And Target.Column = 2 Then ' Si cellule sélectionnée = B3
    Cells(Target.Row + 1, Target.Column).Activate 'Active la cellule suivante.
End If

End Sub

On retrouve ici le principe de la portée des procédures Private et Public.

• Les événements d'une feuille ne concernent que la feuille en question.

• Les événements du classeur, concernent toutes les feuilles du classeur.

Certains événements sont spécifiques au classeur et n'existent pas pour les feuilles.

Autre façon de lancer une procédure, lui associer une touche du clavier avec :

Application.OnKey "Touche", "Procédure"

• Touche : est le code la touche concernée ;

• Procédure : est le nom de la procédure à exécuter. Ou vide pour annuler le lien entre la touche et la procédure. 

Utilisez cette technique avec modération, car si le code de la procédure plante entre l'attribution du raccourci et sa libération, le lien associé à la touche du clavier reste actif.

Par exemple, pour lancer la procédure « ChargeLesDonnées » par la touche « F5 » lorsque l'utilisateur est sur la feuille 4 : il faut activer ce lien quand l'utilisateur active la feuille 4 (événement « Activate » de la feuille) , et désactiver le lien quand l'utilisateur quitte la feuille 4 (événement « Deactivate » de la feuille).

Ce qui donne le code suivant :

Private Sub WorkSheet_Activate()
' Événement déclenché chaque fois que l'on active la feuille

Application.OnKey "{F5}", "ChargeLesDonnées" ' Activer le lien de la touche "F5"

End Sub

Private Sub WorkSheet_Deactivate()
' Événement déclenché chaque fois que l'on quitte feuille

Application.OnKey "{F5}", "" ' Désactiver le lien avec la touche "F5"

End Sub

Une procédure peut aussi être exécutée à un moment précis, par exemple à 17 heures :

Application.OnTime TimeValue("17:00:00"), "ChargeLesDonnées"

Extrait de la documentation EXCEL des principaux événements liés à une feuille :

Extrait de la documentation EXCEL des principaux événements liés au classeur :

Comme vous pouvez le constater, les événements permettent de couvrir une large gamme de besoins.

Par exemple, nous pouvons archiver sur la feuille 5 les modifications faites dans la feuille 4, en indiquant les coordonnées de la cellule modifiée, son ancienne valeur, la date de modification, l'utilisateur concerné, la nouvelle valeur. Pour éviter les débordements, nous n'archivons que les 20 000 dernières modifications.

Nous allons utiliser dans un premier temps l'événement « Worksheet_SelectionChange » pour mémoriser dans une variable publique la valeur de la cellule sélectionnée, c'est-à-dire avant sa modification, puis dans un second temps, l'événement « Worksheet_Change » activé lors de la modification de la valeur de la cellule.

Ce qui donne le code suivant sur la feuille 4 :

Dim AncienneValeur As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Evénement déclenché à chaque changement de cellule sélectionnée.
' L'argument Target représente la cellule active.

AncienneValeur = Target.Value ' Mémorise la valeur de la cellule avant modification

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' Evénement déclenché quand la valeur de la cellule change.
' L'argument Target représente la cellule active.

Sheets("Feuil5").Rows(2).Insert ' Insertion d'une nouvelle ligne en ligne 2
Sheets("Feuil5").Cells(2, 1) = Target.Address ' Coordonnées de la cellule
Sheets("Feuil5").Cells(2, 2) = AncienneValeur ' Ancienne valeur
Sheets("Feuil5").Cells(2, 3) = Now            ' jour et heure de modification
Sheets("Feuil5").Cells(2, 4) = Application.UserName ' Utilisateur
Sheets("Feuil5").Cells(2, 5) = Target.Value         ' Nouvelle valeur

Sheets("Feuil5").Rows(20000).Delete   ' Suppression de la ligne 20 000

End Sub

Grâce à l'événement « BeforeClose » déclenché avant de fermer le classeur, nous allons envoyer un message via OUTLOOK à l'administrateur lorsque l'utilisateur quitte l'application, en lui indiquant les modifications faites. Nous voulons supprimer ce message du dossier « boîte d'envoi » de l'utilisateur pour ne pas l'encombrer de données inutiles…

Mais avant nous allons ajouter une nouvelle procédure dans notre boîte à outils.
La fonction « EnvoiMailOutLook » comporte plusieurs arguments :

• StrSendTo : est l'adresse de messagerie du destinataire. Nous prendrons celle de l'administrateur, préalablement renseignée en cellule « A1 » de la feuille 5.

• StrSubject : est l'objet du message. Ici nous indiquerons le nombre de modifications faites, et l'utilisateur concerné.

• StrBodyText : est le corps du message. C'est le détail de ces modifications, cellule concernée, valeur avant et après intervention.

• StrAttachment (facultatif) : est l'éventuel fichier à mettre en pièce jointe. Pour l'instant nous n'utiliserons pas cet argument.

• SupprimerBoiteEnvoi (facultatif) : indique s'il faut supprimer le message envoyé de la boîte des messages envoyés. Par défaut cet argument est à « Faux ».

Nous allons aussi gérer les éventuelles erreurs qui pourraient survenir dans le traitement.
Avant d'étudier le code de la procédure, nous allons donc faire une brève parenthèse pour évoquer la gestion des erreurs…

Par défaut en VBA, lorsqu'une erreur se produit dans un traitement, un message s'affiche, parfois explicite, souvent très avare d'explication, et surtout, le traitement plante, laissant l'utilisateur dans une impasse.

Bien sûr, comme tout bon langage de programmation, VBA permet d'intercepter une erreur de traitement, et de proposer une alternative au plantage fatidique. Cependant, vous constaterez que la gestion des erreurs est souvent négligée par les développeurs, car elle est très chronophage : il faut en effet penser à tous les cas de figure qui peuvent faire planter un traitement, parfois banal, et écrire un code en conséquence, qui peut se révéler plus long que le code principal.

VBA met quatre mots clés à disposition des développeurs pour gérer les erreurs :

• On Error Resume Next : indique qu'en cas d'erreur, le traitement doit continuer comme si de rien n'était et passer à la ligne suivante. Généralement ça ne règle pas le problème, mais c'est très rapide à mettre en œuvre et ça peut rendre service ;

• On Error Goto NomEtiquette : indique que le traitement doit se brancher à l'étiquette indiquée. En principe l'étiquette, déclarée par « NomEtiquette: » contient un code qui permet de gérer réellement l'erreur. Le nom de l'étiquette est suivi du signe deux-points.

  Il est possible de sortir de l'étiquette et de retourner à la ligne qui a généré l'erreur par le mot clé Resume ou de se connecter à une autre étiquette par le mot clé Resume NomEtiquette ;

• On Error Goto 0 : indique que la gestion personnalisée des erreurs est terminée. VBA reprend la main et en cas d'erreur, le traitement plante ;

• Err : cet objet contient les informations sur l'erreur (numéro de l'erreur, description, source…)

Pour illustrer ces propos, je provoque volontairement une erreur dans le code suivant en appelant la cellule (0,1) qui n'existe pas. Grâce à On Error Resume Next le traitement se poursuit naturellement :

Sub TestErreur()
Dim i As Integer, MaValeur As Variant

On Error Resume Next ' Ignore les erreurs
For i = 0 To 9
    MaValeur = Cells(i, 1)
Next i
End Sub

On Error Goto Err_Test permet en cas d'erreur de brancher le traitement à une étiquette pour afficher un message personnalisé, et Resume Next permet ensuite de poursuivre à la ligne suivante.

Sub TestErreur()
Dim i As Integer, MaValeur As Variant

On Error GoTo Err_Test ' Gestion des erreurs
For i = 0 To 9
    MaValeur = Cells(i, 1)
Next i

Err_Test: ' Étiquette de branchement en cas d'erreur
          ' ou suite du traitement ordinaire.
If Err.Number <> 0 Then   ' S'il y a une erreur (l'Objet Err contient les informations)...
    MsgBox "Erreur quand i vaut " & i   ' Mon message d'erreur,
    Resume Next                         ' Reprend à la ligne suivante,
End If                                  ' Fin du traitement de l'erreur.

End Sub

EXCEL ne parle pas OUTLOOK. Le VBA d'EXCEL va devoir apprendre les fonctions propres à OUTLOOK, les structures des objets, les énumérations et autres constantes. Cela se fait en chargeant le dictionnaire OUTLOOK, depuis l'éditeur VBA, dans le menu « Outils », « Références », en cochant « Microsoft Outlook 14.0 Object Library ».

Nous pouvons maintenant créer dans EXCEL un objet, olApp, qui représente l'application OUTLOOK :

Dim olApp As Outlook.Application
Set olApp = GetObject("", "Outlook.Application")

Puis nous créons un objet, objMail, qui représente la structure d'un message dans l'application OUTLOOK :

Dim objMail As Outlook.MailItem
Set objMail = olApp.CreateItem(olMailItem)

Nous pouvons maintenant renseigner les propriétés de cet objet vierge, à savoir le destinataire principal (objMail.To), la copie (objMail.CC), la copie cachée (objMail.BCC), le sujet (objMail.Subject), le corps (objMail.Body), le style (objMail.BodyFormat), voire ajouter une pièce jointe (objMail.Attachments.Add).

Dans notre procédure « EnvoiMailOutLook » (voir page suivante) l'argument StrAttachment, qui renseigne la pièce jointe à attacher au message, est facultatif.

Alors nous pouvons attacher la pièce jointe StrAttachment : objMail.Attachments.Add StrAttachment.

Tout est fin prêt pour envoyer le message : objMail.Send.

Pour supprimer ce message de la boîte d'envoi où il s'est copié automatiquement, nous devons procéder en deux étapes.

D'abord il faut créer un objet, MyNameSpace, pour accéder à l'espace de travail de OUTLOOK :

Dim MyNameSpace As Outlook.Namespace
Set MyNameSpace = olApp.GetNamespace("MAPI")

Ensuite, parmi cet espace qui regroupe toutes les boîtes de l'application, il faut créer un objet, MaBoite, issu de cet espace, MyNameSpace, et qui représente la boîte d'envoi (olFolderSentMail) :

Dim MaBoite As Object
Set MaBoite = MyNameSpace.GetDefaultFolder(olFolderSentMail)

Ainsi, nous accédons à une propriété de l'élément i de la boîte d'envoi qui contient MaBoite.Items.Count éléments par la syntaxe suivante :

MaBoite.Items(i).Membre

Membre peut être une propriété du message, comme son sujet (Subject), ou une méthode, comme supprimer (Delete). Nous reviendrons sur ces notions dans les pages suivantes. Ici nous souhaitons supprimer le message que nous venons d'envoyer et que nous identifions parmi tous les autres messages, grâce au sujet qui correspond à celui du message envoyé.

Les éléments de la boîte d'envoi peuvent être de nature autre qu'un message, être par exemple des rendez-vous. Ainsi l'élément n'a pas forcément la propriété Subject et sa lecture déclenche une erreur. C'est là que On Error Resume Next nous rend service, en poursuivant le traitement sans faire planter la procédure.

Le code de la procédure se trouve sur la page suivante.

Vous avez remarqué, qu'avant du supprimer le message, nous attendons une seconde pour qu'il y soit :

T = Timer: While T+1 > Timer: Wend

Nous avons une gestion des erreurs dans la première partie du traitement jusqu'à l'envoi du message, qui branche le code à l'étiquette Err_EnvoiMailOutLook en cas d'erreur :

On Error GoTo Err_EnvoiMailOutLook

Et indique que le message n'a pas été envoyé, puis sort de l'application en fermant correctement les objets ouverts, en se branchant directement à l'étiquette Exit_EnvoiMailOutLook :

Resume Exit_EnvoiMailOutLook

En cas d'erreur, donc, la ligne EnvoiMailOutLook = True n'est pas lue. Et la fonction retourne False par défaut.

En règle générale, il est souvent très utile d'utiliser une fonction de type Boolean pour retourner la valeur True si tout s'est bien passé.
Par défaut la valeur retournée est False, il n'est donc pas nécessaire d'initialiser cette valeur dans le code de la fonction.

Cette valeur retournée peut être utilisée par la procédure appelante pour influencer la suite du traitement.

If VBO.EnvoiMailOutLook(Admin, Sujet, Message, , True) = False Then
    [Traitement]
End if

L'initialisation des variables avec Dim n'est pas faite obligatoirement en début du module. Elle peut se faire à n'importe quel moment du code, comme dans notre fonction, où Dim MyNameSpace et Dim MaBoite ne sont initialisés qu'en cas de nécessité.

Notre fonction pour envoyer un message dans OUTLOOK depuis EXCEL :

'-------------------------------------------------------------------------------
Function EnvoiMailOutLook(ByVal StrSendTo As Variant, ByVal StrSubject As String, _
                          ByVal StrBodyText As String, Optional ByVal StrAttachment, _
                          Optional SupprimerBoiteEnvoi As Boolean = False) As Boolean
'-------------------------------------------------------------------------------
Dim i As Long, T As Double
    
On Error GoTo Err_EnvoiMailOutLook ' Étiquette de branchement en cas d'erreur.

Dim olApp As Outlook.Application
Set olApp = GetObject("", "Outlook.Application") ' Déclaration objet Outlook.

Dim objMail As Outlook.MailItem
Set objMail = olApp.CreateItem(olMailItem)       ' Déclaration objet Message.

objMail.To = StrSendTo        ' Destinataire (.CC copie et .BCC copie cachée)
objMail.Subject = StrSubject  ' Sujet
objMail.Body = StrBodyText    ' Texte
objMail.BodyFormat = olFormatRichText  ' Format du message RTF (texte enrichi)

' Ajout d'une pièce jointe si passée en argument, après contrôle de son chemin.
If IsMissing(StrAttachment) = False Then
    If Dir(StrAttachment) <> "" Then objMail.Attachments.Add StrAttachment
End If

objMail.Send              ' Envoie le message
EnvoiMailOutLook = True   ' Retourne True si tout s'est bien passé

If SupprimerBoiteEnvoi = True Then ' S'il faut le supprimer de la boite d'envoi.
    
    T = Timer: While T + 1 > Timer: Wend ' Attente 1 seconde l'envoi du message.
    On Error Resume Next                 ' Ignore les éventuelles erreurs.
    
    Dim MyNameSpace As Outlook.Namespace
    Set MyNameSpace = olApp.GetNamespace("MAPI")
    
    Dim MaBoite As Object
    Set MaBoite = MyNameSpace.GetDefaultFolder(olFolderSentMail)
    
    For i = 1 To MaBoite.Items.Count ' Boucle sur les messages de la boite d'envoi
        If MaBoite.Items(i).Subject = StrSubject Then ' Si c'est ce message
            MaBoite.Items(i).Delete                   ' alors le supprimer.
            Exit For                                  ' Sort de la boucle.
        End If
    Next i

End If

Err_EnvoiMailOutLook:       ' Étiquette branchée en cas d'erreur
If Err.Number <> 0 Then     ' Si erreur, alors Description de l'erreur.
   MsgBox "Message non envoyé suite erreur : " & Err.Description, vbCritical
   Resume Exit_EnvoiMailOutLook
End If

Exit_EnvoiMailOutLook:      ' Étiquette de fin de traitement.
    Set olApp = Nothing     ' Suppression des objets en mémoire.
    Set objMail = Nothing   ' Suppression des objets en mémoire.
    Set MaBoite = Nothing   ' Suppression des objets en mémoire.

End Function

Cette procédure sera déclenchée par l'événement « BeforeClose », lorsque l'utilisateur quitte l'application.

Dans la partie droite de l'éditeur VBA, cliquez sur « ThisWorkbook ». Piochez dans la liste de gauche de l'éditeur « Workbook » et dans la liste de droite « BeforeClose ».

Copiez le code suivant :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim y As Long, NbModif As Integer
Dim Message As String, Sujet As String, S As String, Admin As String

y = 2 ' Boucle sur les modifications faites
While Sheets("feuil5").Cells(y, 1) <> ""
    
    ' Si modification faite aujourd'hui ET par l'utilisateur actuel.
    If DateDiff("d", Sheets("feuil5").Cells(y, 3), Now) = 0 _
    And Sheets("feuil5").Cells(y, 4) = Application.UserName Then
       
        NbModif = NbModif + 1 ' Compte le nombre de modifications
        
        ' Génère le corps du message à envoyer.
        Message = Message & "Cellule " & Sheets("feuil5").Cells(y, 1) _
        & " modifiée de " & Sheets("feuil5").Cells(y, 2) _
        & " en " & Sheets("feuil5").Cells(y, 5) _
        & Chr(10)
    End If
    y = y + 1 ' Passe à la ligne suivante.

Wend

If NbModif = 0 Then Exit Sub ' Si aucune modification alors quitte
If NbModif > 1 Then S = "s"  ' Si plusieurs modifications ajoute d'un "s".

' Création du sujet du message.
Sujet = NbModif & " modification" & S & " effectuée" & S _
        & " par l'utilisateur " & Application.UserName & "" _
        & " le " & Date

Admin = Sheets("feuil5").Cells(1, 1) ' Adresse de l'administrateur

' Envoie un message à l'administrateur et indique si tout se passe bien
If VBO.EnvoiMailOutLook(Admin, Sujet, Message, , True) = True Then
    MsgBox "Votre administrateur a été informé de vos mises à jour"
End If

End Sub

VBA permet de développer des procédures complètes et autonomes, comme ici notre fonction pour envoyer un message dans OUTLOOK. Avec une telle fonction, le développeur n'a plus à se soucier de l'aspect technique ni de la gestion des erreurs, il lui suffit de renseigner les bonnes données dans les bons arguments et le tour est joué.
De plus, la valeur retournée par la fonction permet d'agir sur la suite du traitement.

Idem pour la fonction que je vous propose ici, pour ouvrir une boîte de dialogue afin de sélectionner un fichier, sélectionner un répertoire, ou enregistrer un fichier. Suivant le besoin…

Pour ouvrir une boîte de dialogue « Fichier/Ouvrir », VBA dispose de l'instruction GetOpenFilename, mais nous allons préférer l'objet FileDialog qui offre plus de possibilités, car il permet de gérer trois types de situations : sélectionner un fichier, sélectionner un répertoire, ou enregistrer un fichier.

La boîte de dialogue peut limiter les fichiers affichés d'après leur extension, via un filtre. La fonction prévoit de prérenseigner ce filtre pour les fichiers de type EXCEL, ACCESS, mais laisse aussi la possibilité au développeur de renseigner un filtre particulier, ou de cumuler des filtres.
Le titre de la boîte de dialogue et le bouton peuvent être personnalisés.
Le répertoire par défaut peut être déterminé.
La fonction retourne l'adresse (chemin et nom) de la sélection, cette information étant utilisée ultérieurement, ou une chaîne vide dans les autres cas.
La fonction utilise l'instruction Select Case qui permet des traitements conditionnels très puissants et peut remplacer avantageusement un ensemble d'instructions If … Then.

La syntaxe est la suivante :

Select Case ExpressionATester[Case Expression [Traitements]] ...[Case Else[AutresTraitements]]
End Select

Ne pas confondre avec UCase(Texte) qui met en majuscules le texte passé en argument.

Voici la fonction :

Function BoiteDialogue(FormatBoiteDialogue As MsoFileDialogType, ByVal StrTitre As String,
                       ByVal StrBouton As String, ByVal StrRépertoire As String, 
                       OuvrirFichier As String, ParamArray ListeFiltre() As Variant) As String

Étudions ses arguments.

• FormatBoiteDialogue : est l'un des formats possibles de la boîte de dialogue. Ces formats sont contenus dans l'énumération MsoFileDialogType, intégrée dans VBA, que nous utilisons.

  • Utilisez msoFileDialogFilePicker ou msoFileDialogOpen pour sélectionner un fichier.
  • Utilisez msoFileDialogFolderPicker pour sélectionner un répertoire.
  • Utilisez msoFileDialogSaveAs pour ouvrir une boîte de dialogue « Enregistrer sous ».
• StrTitre : est le titre de la boîte de dialogue.
• StrBouton : est le libellé du bouton de la boîte de dialogue.
• StrRépertoire : permet d'indiquer le répertoire où s'ouvrira la boîte de dialogue. Par exemple « C:\ » Mais, si la valeur USER est passée en argument, le répertoire de l'utilisateur est choisi (Application.DefaultFilePath). Si c'est la valeur ICI qui est passée en argument, c'est le répertoire où est l'application qui est repris (ThisWorkbook.Path). Si l'argument est vide, c'est le dernier répertoire utilisé qui est repris.
• OuvrirFichier : est le nom du fichier attendu à être sélectionné. Si cet argument est renseigné et que l'utilisateur sélectionne un autre fichier, un message informera l'utilisateur de son erreur et lui demandera s'il veut recommencer sa sélection. En cas de refus, la fonction retournera une chaîne vide.

• ListeFiltre : est le type de fichiers que l'on veut sélectionner. Cet argument sert de filtre à la boîte de dialogue. Il fonctionne par couple : libellé à afficher (virgule) extensions. Les différentes extensions sont séparées par un point-virgule. Par exemple : "Fichiers Exécutables,*.exe;*.mde;*.accde"

  Pour plus de souplesse, nous alimentons automatiquement ce filtre pour les types courants, Excel, Access, et tous les fichiers. Le développeur passe alors simplement en argument "EXCEL", "ACCESS", "TOUT" en minuscules ou en majuscules.

  Les filtres prédéfinis et, ou, personnels peuvent se cumuler : par exemple : "EXCEL", "ACCESS", "Fichiers Exécutables,*.exe;*.mde;*.accde", "Tout"

  Cet argument est déclaré ParamArray. Ce qui permet de passer un nombre de valeurs non déterminé, qui seront mémorisées dans un tableau de données.

  L'on consultera ListeFiltre(i) pour connaître la valeur de l'élément i.

  Un argument déclaré ParamArray doit toujours être le dernier de la procédure, et est toujours de type Variant.

  Quelques exemples d'appels avant de voir le code de cette fonction :