Macros-commandes VBA/Procédures et événements automatiques
Introduction
modifierPlusieurs 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
modifierUn é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 ?
modifierLaissez 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 ?
modifierEn 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 :
- Identifiez l'événement qui doit déclencher la procédure.
- Appuyez sur Alt+F11 pour activer l'éditeur VBE.
- 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).
- 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.
.
.
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
modifier| É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
modifierL'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.
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
modifierVoyons 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
modifierL'é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
modifier.
| 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
modifierIl 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
modifierExcel 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
modifierUn é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 :
- Sélectionnez la plage A1:C12
- Ouvrez l'onglet Données, puis cliquez sur le bouton Validation des données dans le groupe Outils des données.
- 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
.
.
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 :
- Entrez -1 dans n’importe quelle cellule en dehors de la plage de validation.
- Appuyez sur Ctrl+C afin de placer le nombre négatif dans le Presse papiers.
- Sélectionnez une cellule dans la plage de validation.
- 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
modifierBibliographie
modifier- John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST
Liens internes
modifierLiens externes
modifier
