Macros-commandes VBA/Procédures et événements automatiques

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

Plusieurs moyens permettent d'exécuter une procédure Sub. L'une d'entre elles consiste à faire en sorte qu'elle le soit automatiquement. Dans cette leçon, vous découvrirez tout ce qu’il faut savoir pour exploiter ces fonctionnalités particulièrement puissantes. Nous vous expliquerons comment préparer le terrain pour qu'une macro soit déclenchée lorsqu'un événement se produit.

De quels types d’événements est-il question ici ? C'est une bonne question. Un événement est fondamentalement quelque chose qui se produit dans Excel. Voici quelques exemples :

• l'ouverture ou la fermeture d'un classeur ;
• l'activation ou la désactivation d'une fenêtre ;
• l'activation ou la désactivation d'une feuille de calcul
• l'entrée de données dans une cellule, ou la modification d'une cellule ;
• l'enregistrement du classeur ;
• le calcul dans la feuille ;
• un clic sur un objet (un bouton par exemple) ;
• l'appui sur une touche ou sur une combinaison de touches ;
• le double clic dans une cellule ;
• la survenance d'une heure du jour particulière ;
• une erreur.

La plupart des programmeurs ne se soucient pas de la majorité de ces événements. Vous devez cependant savoir qu’ils existent, car, un jour ou l'autre, ils pourront vous être utiles. Dans cette leçon nous utiliserons les événements les plus communément utilisés. Pour faire simple, nous n'en aborderons que deux : les classeurs et les feuilles de calcul.

Au terme de cette leçon vous saurez répondre aux questions suivantes :

• quels sont les événements qui peuvent déclencher une exécution ?
• où faut-il placer le gestionnaire d'événement dans le code VBA ?
• comment exécuter une macro à l'ouverture/fermeture d'un classeur ou quand un classeur/feuille de calcul est activé ?

Caractéristiques des procédures d'évenements

Un événement est toujours associé à un objet ou à une collection d'objets spécifique. Nous avons défini une procédure événementielle de niveau "feuille de calcul", attachée à la première feuille du classeur. Si nous voulions que cette procédure se déclenche quand l'utilisateur sélectionne de nouvelles cellules dans n’importe quelle feuille du classeur, il faudrait créer une procédure événementielle de niveau "classeur", s'appliquant à l’ensemble de ses feuilles.

La feuille de calcul représente ici l’objet associé à la procédure événementielle. Il peut s'agir soit d'un objet existant, créé par l'utilisateur, soit d'un objet créé par programmation.

Sous Excel, il existe cinq types principaux d'objets associés.

• Feuille de calcul
• Feuille graphique
• Classeur
• Application (instance d'Excel)
• Boîte de dialogue (UserForm)

Les quatre premiers types d'objets sont spécifiques à Excel, alors que les UserForms peuvent s'utiliser dans toutes les autres applications (Access, Word...) intégrant VBA.

À ces types d'objets, il faut ajouter ceux qui sont créés et définis par programmation par l'intermédiaire de modules de classe.

Pourquoi écrire une procédure d'événement ?

Laissez nous vous convaincre de l'utilité de ces procédures.

Voici un exemple : supposons que vous ayez un classeur dans lequel vous entrez des valeurs dans la colonne A. Votre chef, un gars très compulsif, vous dit qu’il a besoin de savoir quand chaque nombre a été saisi. Le fait d'entrer des données est un événement, plus précisément un événement appelé Worksheet_Change. Vous pouvez écrire une macro qui réagisse à cet événement. Cette macro sera déclenchée chaque fois que la feuille de calcul est modifiée. Si cette modification a été faite dans la colonne A, elle écrira la date et l’heure dans la colonne B, juste à droite de la cellule qui a été éditée.

À titre de curiosité, voyons à quoi une telle macro pourrait bien ressembler :

Private Sub Worksheet_Change (ByVal Target As Range)
    If Target.Column = 1 Then
      Target.Offset(0, 1) = Now
    End if
End Sub

Les macro qui répondent à des événement sont très attentives à l'endroit où elles sont enregistrées. Par exemple, cette macro Worksheet_Change doit être placée dans le module de code associé à cette feuille de calcul. Mettez-la ailleurs, et elle ne fonctionnera pas. Nous allons y revenir un peu plus loin, dans la section "Où placer le code VBA ?".

Attention ! Ce n’est pas parce que votre classeur contient des procédures qui répondent à des événements que celles-ci sont obligatoirement exécutées. Comme vous le savez, il est possible d'ouvrir un classeur dont les macros sont désactivées. Dans ce cas, aucune fonctionnera, événements ou pas. Vous ne devez pas oublier cela lorsque vous créez des classeurs qui sont basés sur des procédures de gestion d’événements.

Comment écrire une procédure d'événement ?

En langage VBA, une procédure de gestion d'événement, ou procédure d'événement, est une procédure qui s'exécute en réponse à un événement. Dont acte. Elle est toujours de type Sub (et non Function). Une fois que vous en aurez assimilé le principe, l'écriture d'une procédure d'événement ne posera pas de problème particulier. La programmation se réduit en effet à ces quelques étapes que nous détaillerons d'ici peu :

1. Identifiez l'événement qui doit déclencher la procédure.
2. Appuyez sur Alt+F11 pour activer l'éditeur VBE.
3. Dans la fenêtre Projet de l'éditeur VBE, double-cliquez sur l’objet approprié listé sous l'intitulé Microsoft Excel Objects. Pour un événement lié au classeur, l’objet est ThisWorkbook. Pour un événement lié à la feuille de calcul, l’objet est un objet de type WorkSheet (comme Feuil1).
4. Dans la fenêtre Code de l'objet, écrivez la procédure d'événement qui devra être exécutée lorsque ledit événement se produira. Cette procédure aura un nom spécial qui l'identifie en tant que procédure d'événement.

Ces étapes vous paraîtrons plus claires en progressant dans cette leçon.

.

Il est très important de comprendre où le code de votre gestionnaire d'événement doit être placé. Il doit résider dans la fenêtre Code d'un module Objet. La procédure ne fonctionnerait pas si elle était placée dans un module VBA standard. Et aucun message d'erreur ne viendrait attirer votre attention là-dessus.

.

Image 1o

.

Cette image montre la fenêtre VBE avec un projet affiché dans la fenêtre Projet. remarquez que le projet est constitué de plusieurs objets

• Un objet pour chacune des feuilles du classeur (en l’occurrence les trois objets Feuil1).
• Un objet nommé ThisWorkbook.
• Un module VBA inséré manuellement avec la commande Insertion/Module

Double-cliquez sur n’importe lequel de ces objets affiche le code associé à l'élément (s'il existe).

La procédure d'événement doit être écrite dans la fenêtre Code de l'élément ThisWorkbook (si l'événement concerne le classeur) ou dans l'un des objets Feuil (si l'événement concerne une feuille de calcul ou de graphique). Dans le cas de l'image précédente, il s'agit du classeur, et c’est d'ailleurs la seule procédure qui y soit définie. À nouveau, notez les deux listes déroulantes affichées en haut de la fenêtre Code. Ce sont vos allies.

.

Lors de l'écriture d'une procédure d'événement l'éditeur VBE vous vient en aide en affichant la liste de tous les événements disponibles pour l’objet sélectionné.

Quand vous sélectionnez un événement dans la liste de droite, l'éditeur VBE crée automatiquement une procédure d'événement à votre place. C'est tout à fait pratique, puisque vous savez immédiatement quels sont les arguments éventuels à fournir.

Il n'est cependant pas obligatoire d’utiliser les deux listes déroulantes disponibles en haut de la fenêtre Code. Mais c’est une aide précieuse, car la syntaxe du nom d'une procédure d'événement est d'une importance critique. De plus, l'instruction Sub de certaines procédures d'événement exige un ou plusieurs arguments. Et rien d’autre ne viendra vous rappeler de quoi il s'agit ! Par exemple, Ici, Sh est là si vous avez sélectionné SheetActivate dans la liste des événements d'un objet Workbook, l'éditeur VBE écrira cette instruction Sub :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Ici, Sh est l'argument passé à la procédure. C'est une variable qui représente la feuille dans le classeur ainsi activé. Les exemples de ce chapitres aiderons à éclaircir ce point.

Les événements de classeur

Les événements de classeur (objet Workbook)

Événement	Lorsqu’il est déclenché :
Workbook_Activate	Le classeur est activé.
Workbook_BeforeClose	Le classeur est fermé.
Workbook_BeforePrint	Le classeur est imprimé.
Workbook_BeforeSave	Le classeur est enregistré.
Workbook_Deactivate	Le classeur est désactivé.
Workbook_NewSheet	Une nouvelle feuille de calcul est insérée dans le classeur.
Workbook_Open	Le classeur est ouvert.
Workbook_SheetActivate	Une feuille de calcul du classeur est activée.
Workbook_SheetBeforeRightClick	Un clic du bouton droit se produit dans une cellule de la feuille de calcul.
Workbook_SheetBeforeDoubleClick	Un double clic se produit dans une cellule de la feuille de calcul.
Workbook_SheetCalculate	Une feuille de calcul du classeur est recalculée.
Workbook_SheetChange	Une cellule du classeur est modifiée.
Workbook_SheetDeactivate	Une feuille de calcul du classeur est désactivée.
Workbook_SheetFollowHyperlink	Un clic se produit sur un lien hypertexte du classeur.
Workbook_SheetSelectionChange	La sélection est modifiée.
Workbook_WindowActivate	La fenêtre du classeur est activée.
Workbook_WindowDeactivate	La fenêtre du classeur est désactivée.
Workbook_WindowResize	La fenêtre du classeur est redimensionnée.

Workbook_Open

L'un des événements les plus communément utilisés est l'événement de classeur Open. Imaginons un classeur que vous utilisez quotidiennement. Dans l'exemple qui va suivre, la procédure Workbook_Open est exécutée chaque fois que le classeur est ouvert. Elle vérifie le jour de la semaine : si c’est vendredi, le code affiche un message de rappel.

Private Sub Workbook_Open()
    Dim Msg As String
    If WeekDay (Now) = 6 then
     Msg = "Nous sommes Vendredi. "
     Msg = Msg & "Pensez à sauvegarder votre travail."
     MsgBox Msg
    End If
End Sub

La fonction WorkBook_Open est exécutée automatiquement chaque fois que le classeur est ouvert. Elle utilise la fonction WeekDay de VBA pour déterminer le jour de la semaine. Si c’est vendredi (jour 6 chez les Anglo-saxons), un message rappelle à l'utilisateur qu’il doit effectuer sa sauvegarde hebdomadaire. Les autres jours, rien ne se produit.

Image 2o

Voyons un autre exemple d'utilisation de la procédure Workbook_Open. Elle se sert des fonctions GetSetting et SaveSetting pour mémoriser le nombre de fois où le classeur a été ouvert. La fonction SaveSetting enregistre une valeur dans le Registre de Windows, tandis que GetSetting retrouve cette valeur (voyez le système d'aide pour plus d'informations à ce sujet). Le code qui suit retrouve ce décompte en consultant le Registre, l'incrémente, puis le sauvegarde à nouveau. L'information, placée dans la variable Cnt, est également affichée à des fins de contrôle.

Private Sub Workbook_Open()
    Dim Cnt As Long
    Cnt = GetSetting("MyApp", "Settings", "Open", 0)
    Cnt = Cnt + 1
    SaveSetting "MyApp", "Settings", "Open", Cnt
    MsgBox "Ce classeur a été ouvert " & Cnt & " fois."
End Sub

Workbook_BeforeClose

Voyons maintenant la procédure d'événement BeforeClose. Exécutée juste avant que le classeur se ferme, elle est localisée dans la fenêtre code de l’objet ThisWorkbook. Par exemple :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As string
    Dim Réponse As integer
    Dim FName As String
    Msg = "Désirez-vous sauvegarder ce fichier ?"
    Réponse = MsgBox(Msg vbYesNo)
    If Réponse = vbYes Then
     FName = "F\SAUVEGARDES\" & ThisWorkbook.Name
     ThisWorkbook.SaveCopyAs NomFichier
    End If
End Sub

Cette routine affiche une boîte de message demandant à l'utilisateur s'il désire effectuer une copie de sauvegarde du classeur. S'il clique sur le bouton Oui, le code utilise la méthode SaveCopyAs pour enregistrer le fichier sur le lecteur F (le lecteur te le chemin devraient bien sûr être adaptés à votre propre configuration).

Les programmeurs utilisent souvent la procédure Workbook_BeforeClose pour faire le ménage. Par exemple, après avoir modifié une option du classeur (comme masquer la barre d'état, par exemple), il est approprié de la rétablir au moment de quitter le classeur. Ce ménage électronique est typiquement une tâche à confier à la procédure Workbook_BeforeClose.

Cet événement présente tout de même un inconvénient. Si vous refermez Excel et qu'un fichier ouvert a été modifié depuis la dernière sauvegarde, l’application vous demandera comme d'habitude si vous voulez enregistrer les changements opérés. le fait de cliquer sur le bouton Annuler clôt le processus de fermeture d'Excel. Mais la procédure Workbook_BeforeClose aura tout de même été exécutée.

Workbook_BeforeSave

L'événement BeforeSave est déclenché avant l'enregistreur d'un classeur. Il se produit lorsque vous utilisez la commande Fichier/Enregistrer ou Fichier/Enregistrer sous.

Placée dans la fenêtre Code d'un objet ThisWorkbook, la procédure suivante démontre la fonctionnement de cet événement. La routine met à jour la valeur de la cellule A1 de Feuil1 chaque fois que le classeur est enregistré. En d'autres termes, la cellule A1 sert de compteur indiquant le nombre de fois que le fichier a été sauvegardé.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Sheets("Feuil1").Range("A1").Value =_
     Sheets("Feuil1").Range("A1").Value + 1
End Sub

Notez que la procédure Workbook_BeforeSave a deux arguments : SaveAsUI et Cancel. Pour comprendre leur fonctionnement, examinez la macro suivante, qui est exécutée avant l'enregistreur du classeur. Elle essaie d'empêcher l'utilisateur de sauvegarder le classeur sous un autre nom. Si celui-ci choisit la commande Fichier/Enregistrer sous, l'argument SaveAsUI est True (vrai).

Lorsque le code est exécuté, il vérifie la valeur de SaveAsUI. Si sa valeur renvoie True, la procédure affiche un message et met Cancel également sur True, ce qui annule l'opérateur de sauvegarde.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    If SaveAsUI Then
      MsgBox "vous ne pouvez pas enregistrer de copie de ce classeur !")
      Cancel = True
    End If
End Sub

En fait, cette procédure n'empêche pas réellement quelqu’un d'enregistrer le classeur sous un nom différent. Il suffit d'ouvrir le classeur avec ses macros désactivées, et le tour est joué. En effet, dans ce cas, toutes les procédures de gestion d’événements sont elles aussi désactivées. Ce qui est parfaitement logique, puisque ce sont aussi des macros...

Les événements de feuille

.

Les événements de feuille (objet Worksheet)

Evénement	Lorsqu’il est déclenché :
Worksheet_Activate	La feuille est activée.
Worksheet_SheetActivate	Une feuille de calcul du classeur est activée.
Worksheet_BeforeRightClick	Un clic du bouton droit se produit dans la feuille de calcul.
Worksheet_BeforeDoubleClick	Un double clic se produit dans la feuille de calcul.
Worksheet_Deactivate	Une feuille de calcul est désactivée.
Worksheet_Calculate	La feuille de calcul est recalculée.
Worksheet_Change	Une cellule de la feuille de calcul est modifiée.
Worksheet_SelectionChange	La sélection est modifiée.

Worksheet_BeforeDoubleClick

Il est possible de définir une procédure VBA qui soit exécutée lorsque l'utilisateur double-clique dans une cellule. Dans l'exemple suivant, stocké dans la fenêtre Code d'un objet Feuil, double-cliquer dans une cellule met son contenu en gras s'il est en caractères maigres, et inversement.

La procédure WorkSheet_BeforeDoubleClick a deux arguments : Target et Cancel. Target est la cellule (un objet Range) qui est double-cliquée. Si Cancel est sur True, l'action par défaut du double clic ne se produit pas.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Target.Font.Bold = Not Target.Font.Bold
    Cancel = True
End Sub

Notez que Cancel est défini avec la valeur True. Cela empêche l'action par défaut (activer le mode édition de cellule d'Excel) de se produire.

Worksheet_Activate

Excel peut détecter si une feuille est activée ou désactivée et exécuter une macro lorsque l'un ou l'autre de ces événements se produit. Ces procédures d'événement doivent être placées dans la fenêtre Code de l’objet Feuil. Pour accéder rapidement à la fenêtre de code d'une feuille, cliquez droit sur l'onglet de celle-ci et choisissez la commande Visualiser le code. L'exemple qui suit montre une procédure simple qui est exécutée chaque fois qu'un feuille donnée est activée. Elle ouvre une boîte de message qui affiche le nom de la feuille active :

Private Sub Worksheet_Activate()
    MsgBox "Vous venez d'activer la feuille "& ActiveSheet.Name
En Sub

Voici un autre exemple qui rend la cellule A1 courante chaque fois qu'une feuille est activée :

Private Sub Worksheet_Activate()
    Range("A1").Activate
End Sub

Ces exemples sont très élémentaires, mais une procédure d'événement peut être beaucoup plus complexe. La procédure qui suit - stockée dans la fenêtre Code de l’objet Feuil1 - utilise l'événement Deactivate pour empêcher l'utilisateur d'activer toute autre feuille du classeur. Lorsque Feuil1 est désactivée (c'est-à-dire qu'une autre feuille est activée), un message est affiché puis Feuil1 est de nouveau activé :

Private Sub Worksheet_Activate()
    MsgBox "Vous devez rester dans Feuil1."
    Sheets("Feuil1").Activate
End Sub

Pour autant je ne vous conseille pas d’utiliser ce genre de procédure pour essayer de court-circuiter Excel. Cela pourrait être très frustrant et source de confusion pour l'utilisateur d'une part, et d’autre part facile à contourner en désactivant les macros. Il vaut mieux profiter de ces possibilités pour aider vos utilisateurs à se servir correctement de votre application.

Worksheet_Change

Un événement Change se produit chaque fois qu'un cellule de la feuille de calcul est modifiée. Dans l'exemple qui suit, la procédure Worksheet_Change empêche effectivement un utilisateur d'entrer une valeur non numérique dans la cellule A1. Ce listing est stocké dans la fenêtre Code de l’objet Feuil.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
      If Not IsNumeric(Target) Then
         MsgBox "Entrez un nombre dans la cellule A1."
         Range("A1").ClearContents
         Range("A1").Activate
      End If
    End If
End Sub

L'unique argument de la procédure WorkSheet_Change (Target) représente la plage qui a été modifiée. La première instruction vérifie si l'adresse de la cellule est bien $A$1. Si oui, le code utilise la fonction IsNumeric pour déterminer si elle contient une valeur numérique. Si ce n’est pas le cas, un message apparaît et la valeur de la cellule est effacée. La cellule A1 est ensuite réactivée, ce qui est commode lorsque le pointeur de la cellule s'est déplacé après la saisie. Si une cellule autre que A1 est modifiée, il ne se passe rien.

Pourquoi ne pas utiliser la commande Validation ?

La commande Donnée/Outils de données/Validation des données vous est peut-être familière. C'est une fonction très commode qui permet de s'assurer facilement que les données entrées dans une plage sont bien du type requis. Elle ne met toutefois pas à l'abri de toutes les fausses manœuvres. Pour vous en convaincre, ouvrez une feuille de calcul vierge puis effectuez les étapes suivantes :

1. Sélectionnez la plage A1:C12
2. Ouvrez l'onglet Données, puis cliquez sur le bouton Validation des données dans le groupe Outils des données.
3. Configurez les critères de validation de manière à n'accepter que des nombres entiers entre 1 et 12, comme le montre l'image qui suit

.

Image 3o

.

Entrez maintenant des valeurs dans la plage A1:C12. La validation des données fonctionne comme prévu. Mais, pour voir ce qui ne va pas, essayez ceci :

1. Entrez -1 dans n’importe quelle cellule en dehors de la plage de validation.
2. Appuyez sur Ctrl+C afin de placer le nombre négatif dans le Presse papiers.
3. Sélectionnez une cellule dans la plage de validation.
4. Appuyez sur Ctrl+V.

Vous constatez que le collage est autorisé. Examinez la situation de plus près et vous remarquerez que la cellule dans laquelle vous avez collé la valeur négative n'a plus de critère de validation. Le collage l'a effacé !

Le collage efface les données de validation, car, pour Excel, la validation est la même chose qu'une mise en forme de cellule (ou, pour être plus précis, qu'une mise en forme conditionnelle). Elle est considérée au même titre que la police, la couleur au autre attribut. Quand vous collez une cellule, vous remplacez la mise en forme de la cellule de destination par celle de la cellule source. Il en va malheureusement de même pour les critères de validation.

Annexes

Bibliographie

• John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST

Liens internes

• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• FAQ developpez VBA
• Une introduction au Visual Basic