Les procédures événementielles

Morefunc (fr)
Présentation Téléchargement
Installation
Démo
Dates et heures
Information
Mathématiques
Statistiques
Texte
Tri
Autres
Formules
Nom classeur et feuille
Nombre valeurs uniques
Elimination de doublons
Dates et heures
Combinaisons
Formules matricielles
Matricielles avancées
Programmation
XLA pas à pas -1
XLA pas à pas -2
XLA pas à pas -3
XLA pas à pas -4
XLA pas à pas -5
Evénements
Accélération VBA
Fonctions complémentaires
Noms masqués
Paramètres personnalisés
XLL et Api-C
Api Windows
Utiliser Api Windows
Userform non modal
copier comme .emf
Imprimantes installées
Changer logo
Dimension texte
Changer résolution
Polices installées
Sélection couleur
Créer raccourci
Interruption programme
Téléchargements
FunCustomize
ParamReg
ListeProcs
Etiquettes
TriFeuilles
Epidemio
Xl-Version
Bric à Brac
English pages
Vous êtes ici
Accueil » Programmation

Les procédures événementielles
I. Qu'est-ce qu'une procédure événementielle ?
II. Caractéristiques des procédures événementielles
1. Objets associés aux événements
2. Modules objet
3. Création d'un procédure événementielle
4. Paramètres des procédures événementielles
. Paramètres transmettant des informations à la procédure
. Paramètre d'annulation de l'événement (Cancel)
5. Désactivation des procédures événementielles (EnableEvents)
III. Evénements dynamiques
1. Affectation dynamique d'événements à des objets
2. Le mot-clé WithEvents
. Les variables WithEvents
. Affectation d'objets aux variables WithEvents
3. Les événements de graphiques incorporés
4. Les événements de niveau Application
5. Evénements d'objets extérieurs à Excel
IV. Evénements dynamiques et modules de classe
1. Exemple : événements de l'application et modules de classe
2. Evénements affectés à des collections d'objets
V. Quelques procédures événementielles courantes
1. Evénements de feuilles de calcul
. L'événement Change
. L'événement Calculate
. Worksheet_Activate et Worksheet_Deactivate
2. Evénements du classeur
. Workbook_Open et Workbook_BeforeClose
. Gérer les annulations de fermeture du classeur
3. Evénements de l'application
I. Qu'est-ce qu'une procédure événementielle ?

Une procédure événementielle est une procédure qui se déclenche automatiquement en réponse à une action de l'utilisateur ou du logiciel, appelée "événement".
Cet événement peut être par exemple la sélection d'une cellule, la saisie d'une valeur, l'ouverture d'un classeur, l'activation d'une feuille de calcul ou encore la sélection d'une commande de menu. Les procédures événementielles permettent d'intercepter ces actions afin de contrôler les manipulations de l'utilisateur, d'enrichir les commandes intégrées d'Excel en leur "greffant" ses propres macros, ou encore de personnaliser de manière dynamique l'environnement de l'application.
Cette page présente essentiellement les procédures événementielles spécifiques à Excel, généralement peu documentées. Les procédures événementielles que l'on retrouve dans toutes les applications Office (essentiellement celles des UserForms et de leurs contrôles) dépasseraient le cadre de cet article.
II. Caractéristiques des procédures événementielles

1. Objets associés aux événements

Un événement est toujours associé à un objet ou à une collection d'objets spécifique. Dans le précédent exemple, 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.
A 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.
2. Modules objet

A l'exception de l'objet Application, les autres objets mentionnés ci-dessus disposent de leurs propres modules, dits "modules objet". Ces modules sont accessibles par l'explorateur de projet de l'éditeur VBA. Ils sont créés automatiquement par Excel dans le cas des modules de classeur, de feuille de calcul et de feuille graphique. Les modules de UserForms et les modules de classe ne peuvent être créés qu'à partir de l'éditeur VBA, via le menu Insertion.
L'illustration suivante montre les différents types de modules qu'un classeur peut contenir:

Tous les modules présents dans ce classeur sont des modules objet (c'est à dire associés à des objets particuliers, ou permettant de définir un nouvel objet dans le cas du module Class1), à l'exception du module standard Module1, qui n'est associé à aucun objet.
Le module ThisWorkbook contient, par définition, les procédures événementielles associées au classeur.
Selon l'objet concerné par l'événement auquel on veut associer une procédure, on utilisera le module approprié:
Type de module Evénements associés à...
Module de feuille feuille de calcul particulière et objets placés sur cette feuille
Module graphique feuille graphique particulière et objets placés sur cette feuille
Classeur (ThisWorkbook) classeur, collection de toutes les feuilles et fenêtres du classeur
UserForm boîte de dialogue (UserForm) et contrôles inclus
Module de classe application, collection de toutes les feuilles, fenêtres et classeurs
Comme on le voit dans ce tableau, un module objet permet de gérer non seulement les événements de l'objet associé au module lui-même (feuille, classeur ou UserForm), mais aussi les objets qu'il contient, par exemple l'ensemble des feuilles contenues par un classeur, ou encore les contrôles (boutons, cases à cocher...) placés dans une feuille de calcul.
3. Création d'une procédure événementielle

La liste des événements que l'on peut intercepter par des procédures VBA est prédéterminée. Les feuilles de calcul disposent par exemple de 7 événements et les classeurs de 19 événements prédéfinis. Il n'est pas possible de créer des procédures pour des événements qui ne sont pas prédéterminés par l'application.
Evénements de feuilles de calcul, de classeur ou de UserForm
Supposons que l'on veuille créer une procédure qui se déclenche automatiquement dès que l'utilisateur modifie manuellement la valeur inscrite dans la cellule A1 de la feuille "Feuil1". La procédure est la même que celle indiquée en introduction :
Sous VBE, double-cliquer sur l'icône 'Feuil1' du classeur dans l'explorateur de projets, afin d'ouvrir sa feuille de code.
Comme tout module de code, celui-ci comporte deux listes déroulantes.

La liste de gauche permet de sélectionner la section du module que l'on veut atteindre:la section Général est destinée aux déclarations de variables globales et aux fonctions ou procédures non-événementielles. Les autres sections concernent les procédures événementielles associées aux objets. Ici, comme la feuille ne contient pas de contrôles, un seul objet apparaît dans cette liste, l'objet Worksheet représentant la feuille de calcul elle-même.
Après sélection de Worksheet dans cette liste déroulante, les événements concernant la feuille de calcul apparaissent dans la fenêtre de droite.

Le cadre de la procédure événementielle par défaut de l'objet Worksheet, SelectionChange, est automatiquement placé dans le module. Il suffit de sélectionner l'événement qui nous intéresse dans la liste, ici Change, pour créer le cadre de la procédure événementielle que l'on veut manipuler:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
End Sub
Le cadre de la procédure Worksheet_SelectionChange, s'il n'est pas prévu de l'utiliser, peut être effacé.
Le nom de la procédure identifie à la fois la nature de l'événement ("Change") et l'objet concerné par cet événement ("Worksheet", placé avant le trait de soulignement). On peut modifier les noms des arguments, mais pas leur type ni leur ordre d'apparition.
La méthode est la même pour définir des procédures événementielles concernant le classeur entier ou une boîte de dialogue. Pour le classeur, il faut activer le module objet ThisWorkbook et sélectionner "Workbook" dans la liste déroulante des objets disponibles. Pour une boîte de dialogue, activer le UserForm, afficher son module par F7 et sélectionner l'objet "UserForm".
Evénements liés à des contrôles
Pour définir des procédures événementielles liées à des contrôles, il faut passer par le module de l'objet contenant le contrôle, c'est à dire de la feuille de calcul ou du UserForm.
Voici par exemple comment créer un bouton sur une feuille de calcul et définir une macro qui se déclenchera quand l'utilisateur clique sur ce bouton:
Créer le nouveau bouton par l'intermédiaire de la barre d'outils "Commandes" (de préférence à "Formulaires", réservée pour les contrôles hérités d'Excel 5/95).
Double-cliquer sur ce bouton. Automatiquement, le module objet de la feuille de calcul est affiché, et le cadre de la procédure événementielle par défaut du contrôle (événement "Click") est créé:
Private Sub CommandButton1_Click()
End Sub
Il ne reste plus qu'à insérer dans cette procédure le code VBA devant s'exécuter après un clic sur le contrôle.
De la même manière que les procédures événementielles de la feuille de calcul, celles associées aux contrôles permettent par leur intitulé d'identifier l'objet (CommandButton1) et l'événement concerné (Click).
L'objet est manipulable directement par son nom. Excel attribue automatiquement un nom par défaut à tous les objets créés, par exemple ici CommandButton1, mais il est parfaitement possible de le modifier par la suite en changeant le contenu de sa propriété Name. Pour modifier les propriétés d'un contrôle placé sur une feuille de calcul, il faut d'abord activer le mode "Création" (icône de la barre d'outils "Commandes"), sélectionner ensuite le contrôle à la souris, et afficher sa fenêtre de propriétés (icône ). Pour utiliser à nouveau le contrôle, il est nécessaire de désactiver le mode "création".
La création du bouton de commande a généré un objet VBA "CommandButton1" de type CommandButton, que la procédure événementielle peut elle-même manipuler dans son propre code.
Voici par exemple comment faire basculer le texte de CommandButton1 de "Arrêter" à "Reprendre" et inversement à chaque clic de l'utilisateur:
Private Sub CommandButton1_Click()
With CommandButton1
.Caption = IIf(.Caption = "Arrêter", "Reprendre", "Arrêter")
End With
End Sub
Le nouveau contrôle s'ajoute à la liste des objets disponibles du module objet.
Pour modifier cette procédure événementielle ou en définir d'autres, il suffit d'activer ce module, de sélectionner l'objet par cette liste déroulante et la procédure choisie dans la liste déroulante de droite. Le procédé est le même pour les contrôles de UserForms.
4. Paramètres des procédures événementielles

La plupart des procédures événementielles intègrent des paramètres. Ces paramètres peuvent être de deux types:
Paramètres transmettant des informations à la procédure
L'exemple typique est celui de l'argument Target intégré à la procédure Worksheet_Change des feuilles de calcul. Cet argument récupère la référence de la cellule ou de la plage dont la modification du contenu a déclenché l'événement. Il peut être utilisé par la procédure comme toute autre variable de type Range :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox "Plage modifiée : " & Target.Address
End Sub
Paramètre d'annulation de l'événement (Cancel)
Ce paramètre est intégré à un certain nombre de procédures telles que les procédures de niveau classeur (module ThisWorkbook) Workbook_BeforeClose ou Workbook_BeforePrint. Il permet à la procédure d'annuler éventuellement l'action associée à l'événement. Il concerne donc exclusivement les procédures dont l'exécution se déclenche avant l'action demandée par l'utilisateur (procédures "Before...").
Quand la procédure est lancée, le paramètre Cancel est égal à False. Pour annuler le déclenchement de l'action associée à l'événement, il faut mettre ce paramètre à True avant la fin de la procédure.
L'exemple suivant utilise la procédure de niveau classeur (module ThisWorkbook) Workbook_SheetBeforeRightClick pour afficher la liste de choix à la place du menu contextuel lorsque l'utilisateur fait un clic droit sur une cellule.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Excel.Range, Cancel As Boolean)
Application.CommandBars("Cell") _
.FindControl(msoControlButton, 1966).Execute
Cancel = True
End Sub
Si la valeur True n'était pas affectée à Cancel, le menu contextuel associé au clic droit sur une cellule serait automatiquement affiché après la liste de choix.
5. Désactivation des procédures événementielles (EnableEvents)

Dans certaines situations, il est nécessaire d'empêcher VBA d'exécuter automatiquement les procédures événementielles. Cette possibilité est offerte par la propriété EnableEvents de l'objet Application. L'affectation de la valeur False à cette propriété désactive le déclenchement automatique des procédures événementielles.
Cette propriété permet par exemple d'éviter un piège fréquent posé par la procédure Sheet_Change ou Worksheet_Change, lorsque celle-ci modifie elle-même, par code, le contenu d'une cellule. Par exemple, la procédure Worksheet_Change suivante inscrit automatiquement, dès que l'on saisit une valeur dans une cellule du classeur, le type de cette valeur (String, Double,...) dans la cellule adjacente de droite:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
Target.Offset(0, 1) = TypeName(Target)
End Sub
Telle quelle, cette procédure ne marchera pas. Si l'on saisit "Zaza" dans la cellule A1 d'une feuille, elle inscrira "String" dans la cellule B1, C1, etc. jusqu'au bout de la ligne (IV1) et s'arrêtera ensuite sur un message d'erreur.
Comme les procédures événementielles sont déclenchées de manière automatique non seulement par les actions effectuées par les utilisateurs, mais aussi par les actions déclenchées par macro, l'instruction Target.Offset(0,1) = TypeName(Target) provoque en effet un appel récursif de la procédure Workbook_Sheet_Change, qui s'appelle elle-même jusqu'à ce qu'une erreur soit rencontrée ou alors que la mémoire réservée à la pile d'appel soit épuisée.
Pour éviter cet appel récursif, il est nécessaire de désactiver temporairement le déclenchement automatique des procédures événementielles avant l'instruction susceptible de provoquer un appel récursif, et de le réactiver ensuite:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1) = TypeName(Target.Value)
Application.EnableEvents = True
End Sub
Autre exemple typique, comment empêcher la procédure Workbook_Open d'un classeur ouvert par macro de se déclencher automatiquement après son ouverture:
Application.EnableEvents = False
Workbooks.Open "C:\Temp\Zaza.xls"
Application.EnableEvents = True
La désactivation provisoire des procédures événementielles par EnableEvents, dans la mesure où celles-ci peuvent être déclenchées automatiquement par des actions opérées par code, peut également permettre d'optimiser la vitesse d'exécution des macros (voir Accélération du code VBA)
III. Evénements dynamiques

1. Le mot-clé WithEvents

Dans tous les exemples précédents, nous sommes partis d'objets déjà existants, créés sous Excel, auxquelles nous avons affecté des procédures événementielles saisies dans des modules objet. Il s'agit de procédures définies au moment de la création. Tant qu'elles sont présentes dans le module objet (et que les procédures événementielles ne sont pas désactivées par l'intermédiaire de la propriété EnableEvents), elles s'exécuteront systématiquement dès que l'événement associé se produit. Elles sont immédiatement opérationnelles dès l'ouverture du classeur qui les contient.
Mais VBA donne également la possibilité d'affecter de manière dynamique, au moment de l'exécution, des procédures événementielles, soit à des objets existants, soit également à des objets créés par macro ou par l'utilisateur. Ces procédures peuvent être annulées et réactivées individuellement de manière dynamique. Elles permettent aussi d'affecter plusieurs procédures au même événement associé à un objet, de les enchaîner ou de les permuter. Contrairement aux procédures "statiques" précédentes, elles ne sont pas automatiquement opérationnelles à l'ouverture du classeur mais nécessitent un code minimal pour être activées.
Ces procédures sont également nécessaires pour intercepter les événements d'objets qui ne disposent pas de leur propre module de code:il s'agit des graphiques incorporés à des feuilles de calcul, de l'objet Application et des objets extérieurs à Excel programmables par OLE Automation
2. Affectation d'événements à des objets

Les variables WithEvents
Pour utiliser de manière dynamique un objet intégrant des événements, il est nécessaire de le manipuler par l'intermédiaire d'une variable. Le mot-clé WithEvents permet de déclarer des variables destinées à référencer des objets disposant d'événements.
Par exemple, la ligne suivante déclare une variable "Wksht" de type Worksheet destinée à répondre à des événements:
Dim WithEvents Wksht As Worksheet
Le mot-clé WithEvents doit être placé entre l'instruction de déclaration (Dim, Public ou Private, selon la portée que l'on veut attribuer à la variable) et le nom de la variable. Ce mot-clé implique un certain nombre de restrictions:
- Les variables définies de cette manière ne peuvent être déclarées qu'au niveau module. Il n'est pas possible de les déclarer comme variables locales à une procédure ou une fonction.
- Seuls les types d'objets pour lesquels des procédures événementielles sont prévues peuvent être déclarées avec le mot-clé WithEvents. Il peut s'agir par exemple de variables de type Worksheet, Workbook, UserForm, CommandButton (bouton de UserForm) ou Chart, mais pas de type Range ou AutoFilter.
- Les variables déclarées WithEvents ne peuvent pas être des tableaux.
- Enfin, ces variables ne peuvent être déclarées qu'au sein de modules objets, que ce soit un module de feuille de calcul, de feuille graphique, de UserForm, le module ThisWorkbook ou un module de classe. Elles ne peuvent pas être déclarées dans des modules standard.
Les variables définies avec WithEvents disposent automatiquement des procédures événementielles correspondant à leur type d'objet. Comme pour les objets existants, elles sont intégrées à la liste déroulante de gauche du module objet, et leurs événements peuvent être sélectionnés dans la liste déroulante de droite.
Voici l'exemple de trois variables de type Workbook, Worksheet et UserForm déclarées avec WithEvents dans un module de feuille de calcul:

Il est possible de définir tout type de variable WithEvents dans n'importe quel module objet, et non pas seulement dans les modules de classe.
Affectation d'objets aux variables WithEvents
Les variables déclarées WithEvents sont à l'origine non initialisées, et ne référencent aucun objet particulier. Pour que leurs procédures événementielles soient activées, il faut d'abord leur affecter un objet. Cette affectation peut se faire n'importe où dans le code, ou alors dans la procédure Workbook_Open si l'on veut que la procédure événementielle soit active dès l'ouverture du classeur.
A partir du moment où la variable est initialisée et qu'elle référence un objet particulier, la ou les procédures événementielles attachées à cette variable se "connectent" à l'objet concerné et deviennent opérationnelles. Pour annuler cette connexion, il suffit de détruire la référence à l'objet en affectant Nothing à la variable.
L'intérêt des variables WithEvents est relativement limité pour les objets dont on peut programmer directement les procédures événementielles. En revanche, ces variables sont nécessaires pour intercepter les événements des graphiques incorporés et de l'application Excel, qui ne disposent d'aucun module objet.
3. Les événements de graphiques incorporés

Contrairement aux feuilles graphiques autonomes, les graphiques incorporés aux feuilles de calcul ne disposent d'aucun module objet. En revanche, on peut leur associer les mêmes procédures événementielles, à condition de passer par des variables WithEvents.
Dans l'exemple suivant, la variable "Graph" sert à intercepter l'événement Calculate d'un graphique incorporé dans une feuille de calcul. A chaque changement d'une ou plusieurs valeurs dans la plage de données, la procédure Graph_Calculate ajuste automatiquement les extrémités de l'axe des ordonnées en fonction des valeurs minimale et maximale représentées sur le graphique (avec une marge de 5% par rapport à l'intervalle entre ces deux valeurs).
Dim WithEvents Graph As Chart

Private Sub Graph_Calculate()

Dim Min As Double, Max As Double
Dim Valeurs(), I As Integer
Dim Marge As Double

Application.ScreenUpdating = False
' Recherche des ordonnées minimale et maximale
With Graph.SeriesCollection
ReDim Valeurs(1 To .Count)
For I = 1 To .Count
Valeurs(I) = .Item(I).Values
Next I
End With
Min = Application.Min(Valeurs)
Max = Application.Max(Valeurs)
ReDim Valeurs(0)
' Redimensionnement de l'axe des ordonnées
Marge = (Max - Min) * 0.05
Min = IIf(Min >= 0 And Min - Marge < 0, 0, Min - Marge) Max = Max + Marge With Graph.Axes(xlValue) If .MinimumScale <> Min Then .MinimumScale = Min
If .MaximumScale <> Max Then .MaximumScale = Max
End With

End Sub
Ce code peut être placé dans n'importe quel objet du classeur, mais il est préférable de le mettre dans le module de la feuille de calcul contenant le graphique. Pour que la procédure Graph_Calculate soit opérationnelle, il faut encore connecter la variable Graph au graphique concerné. Cette affectation peut se faire dans la procédure Workbook_Open du classeur pour que la procédure soit activable dès son ouverture.
Par exemple, pour connecter la variable au premier graphique de Feuil1:
Private Sub Workbook_Open()
Set Feuil1.Graph = Feuil1.ChartObjects(1).Chart
End Sub
Pour désactiver notre procédure événementielle, il suffit de "déconnecter" la variable Graph de l'objet graphique:
Set Feuil1.Graph = Nothing
Si nous avons défini par ailleurs une autre variable "Graph2" de type Chart disposant de sa propre procédure Graph2_Calculate, il est possible d'affecter l'objet graphique à cette deuxième variable:
Set Feuil1.Graph = Feuil1.ChartObjects(1).Chart
Set Feuil1.Graph2 = Feuil1.ChartObjects(1).Chart
De cette manière, le graphique disposera de deux procédures événementielles Calculate, celle de la variable Graph et celle de la variable Graph2. Quand des données sources du graphique changeront de valeur, ces deux procédures se déclencheront successivement. En affectant Nothing à l'une des variables ou aux deux, on peut désactiver chacune de ces procédures événementielles.
Cette méthode permettant d'enchaîner plusieurs procédures événementielles n'est pas spécifique aux objets Chart, mais peut s'appliquer à tout objet pouvant être connecté à des variables WithEvents.
4. Les événements de niveau Application

Tout comme les procédures événementielles de graphiques incorporés, celles de l'objet Application ne peuvent être programmées qu'à l'aide de variables WithEvents, l'application Excel ne disposant pas de son propre module objet.
Ces procédures permettent d'intercepter des événements de niveau Application, c'est à dire se déclenchant indépendamment de toute feuille ou classeur ouvert. Elles ont toutes leurs équivalents de niveau feuille de calcul et classeur. Pour intercepter des saisies de l'utilisateur dans des cellules, on dispose ainsi de trois niveaux de procédures:
- Worksheet_Change (module objet d'une feuille) : intercepte les saisies faites dans une feuille de calcul particulière.
- Workbook_SheetChange (module objet ThisWorkbook) : intercepte les saisies faites dans n'importe quelle feuille du classeur.
- App_SheetChange (liée à la variable WithEvents "App", de type Application):intercepte les saisies faites dans n'importe quelle feuille de tout classeur ouvert.
Pour illustrer la différence entre ces trois niveaux, supposons que l'on veuille mettre en place une procédure événementielle qui mette automatiquement en majuscule toute saisie de texte dans une cellule. La procédure à utiliser et son module objet ne sont pas les mêmes selon la portée que l'on veut attribuer à cette procédure (feuille de calcul particulière, classeur entier ou toute feuille de calcul ouverte dans l'application).
- Feuille de calcul particulière
Dans le module objet de la feuille concernée :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or VarType(Target) <> vbString _
Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub
Toute feuille du classeur
Dans le module ThisWorkbook du classeur :
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Target.Count > 1 Or VarType(Target) <> vbString _
Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub
Toutes les feuilles de tous les classeurs ouverts
Dans le module ThisWorkbook ou tout autre module objet :
Dim WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Target.Count > 1 Or VarType(Target) <> vbString _
Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub
Et dans le module ThisWorkbook, pour connecter la variable App à l'application:
Private Sub Workbook_Open()
Set App = Application
End Sub
Si le classeur est en fait une macro complémentaire XLA (plus utile et mieux appropriée qu'un classeur XLS à la gestion des événements de niveau Application), il est préférable d'employer la procédure Workbook_AddinInstall plutôt que Workbook_Open pour affecter l'application à notre variable:
Private Sub Workbook_AddinInstall()
Set App = Application
End Sub
Les procédures événementielles de niveau feuille, classeur et application sont additives, c'est à dire qu'elles ne s'écrasent pas mutuellement. Ainsi, on peut tout à fait utiliser simultanément une procédure du type Sheet_Change pour une feuille donnée, le classeur qui la contient et l'ensemble de l'application. Lorsque la feuille de calcul sera activée, VBA exécutera successivement la procédure de niveau feuille, puis celle de niveau classeur et enfin celle de niveau Application.
Par l'intermédiaire des variables WithEvents, on peut même construire une multiplicité de procédures événementielles du même type pour une même famille d'objets. Toutes les procédures coexistent indépendamment les unes des autres et s'enchaînent à l'exécution. Une macro complémentaire peut donc utiliser sa propre procédure WorkbookOpen de niveau Application sans se préoccuper si d'autres macros extérieures exploitent le même événement. Chacune de ces procédures s'exécutera tour à tour sans interférer avec les autres.
5. Evénements d'objets extérieurs à Excel

Les variables WithEvents permettent non seulement de gérer des procédures événementielles d'objets spécifiques à Excel, mais également d'autres d'objets extérieurs à l'application. Il doit s'agir d'objets ActiveX supportant l'utilisation du mot-clé WithEvents.
Par exemple, il est possible de définir des procédures événementielles d'une fenêtre Word dans une macro Excel, simplement en créant une variable WithEvents de type Word.Application et en lui affectant une instance déjà ouverte ou nouvelle de Word.
Le code ci-dessous ouvre une nouvelle fenêtre Word, l'active et "espionne" ensuite tous les documents ouverts sous Word en recopiant leur nom dans une feuille de calcul masquée. Une fois que Word est fermé (événement Quit de Word), cette feuille est rendue visible. L'ouverture des documents sous Word est interceptée par la procédure événementielle DocumentOpen.
' *** Dans le module ThisWorkbook ***

Public WithEvents WRD As Word.Application

Private Sub WRD_DocumentOpen(ByVal Doc As Word.Document)
' Ajout du nom du document ouvert dans la feuille masquée
I = I + 1
Wksht.Cells(I, 1) = Doc.FullName
End Sub

Private Sub WRD_Quit()
' Destruction de la variable WRD et affichage du résultat
Set WRD = Nothing
Wksht.Visible = True
Wksht.Activate
End Sub

' *** Dans un module standard ***

Public Wksht As Worksheet
Public I As Integer

Sub ActiverWord()
With ThisWorkbook
' Connexion de la variable WRD du module ThisWorkbook
' avec une nouvelle instance de Word,
' Affichage et activation de la fenêtre Word
Set .WRD = New Word.Application
.WRD.Visible = True
.WRD.Activate
End With
Set Wksht = Worksheets.Add
Wksht.Visible = False
I = 0
End Sub
De la même manière, il est possible de piloter les événements de toute autre application supportant la déclaration WithEvents à partir d'une macro Excel.
IV. Evénements dynamiques et modules de classe

La documentation existante sur les procédures événementielles dynamiques, en particulier celles de l'application, présente très souvent celles-ci comme une caractéristique réservée aux modules de classe. En réalité, du moment que tous les modules objet (par exemple un module de feuille de calcul ou le module ThisWorkbook) acceptent la déclaration de variables WithEvents, tous peuvent servir à créer des événements dynamiques, quel que soit le type d'objet rattaché à ces événements.
Toutefois, les modules de classe présentent des avantages indéniables. Au lieu de définir des procédures événementielles de l'application à l'intérieur du module ThisWorkbook, par exemple, on peut regrouper ces procédures dans un module de classe que l'on nommerait "Application". De cette manière, les procédures événementielles sont clairement réparties dans des modules objet distincts selon les objets auxquels elles se rattachent (module ThisWorkbook pour le classeur, modules de feuille pour les feuilles de calcul et modules de classe particuliers pour l'application ou d'autres objets éventuels).
En plus d'un code mieux structuré, les modules de classe permettent surtout, indirectement, d'attacher des procédures événementielles dynamiques à des tableaux ou collections d'objets, ce qui n'est normalement pas possible compte tenu du fait que le mot-clé WithEvents ne peut pas servir à définir des variables de type tableau.
1. Exemple : événements de l'application et modules de classe

Les modules de classe représentent également un espace de stockage bien adapté aux procédures événementielles de niveau Application. Ils permettent de regrouper toutes ces procédures dans un module unique distinct des procédures liées au classeur. De cette manière, la gestion des événements de niveau classeur peut être réservée au module ThisWorkbook, les événements de niveau Application étant réservés au module de classe. Le code est ainsi mieux structuré et plus lisible.
La technique pour créer des procédures événementielle dans un module de classe est la même que pour tout autre module objet, à quelques différences près:
- La variable "App" du module de classe doit être déclarée Public pour que l'on puisse lui affecter l'objet Application à partir d'un autre module.
- Un module de classe servant en principe à définir une nouvelle classe d'objet, et non l'objet lui-même, il est nécessaire de l'instancier en créant un objet fondé sur cette classe. Pour cela, il faut déclarer dans un autre module une variable ayant cette classe comme type, et affecter à cette variable un nouvel objet (une nouvelle instance de la classe). Il faudra donc prévoir, d'une part, dans le module de classe, une variable "App" destinée à connecter les procédures événementielles à l'objet Application:
Public WithEvents App As Application
Et dans un autre module :
La déclaration de la variable destinée à instancier la classe :
Dim MonApplication As Nom_du_module_de_classe
L'instanciation de la classe à l'intérieur de cette variable :
Set MonApplication = New Nom_du_module_de_classe
Enfin, l'affectation de l'application Excel en cours à la "propriété" App de notre instance de classe:
Set MonApplication.App = Application
Si l'on reprend l'exemple du paragraphe III.4 en utilisant un module de classe nommé "Evts_Application" à la place de ThisWorkbook, le code à utiliser dans le module de classe est similaire au précédent, à la différence près que la variable WithEvents est déclarée ici Public:
Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Target.Count > 1 Or VarType(Target) <> vbString _
Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub
Dans le module ThisWorkbook (par exemple), il faut alors instancier la classe à l'aide d'une variable (ici "XL") et connecter sa propriété App (définie dans le module de classe par la variable App) à l'application Excel en cours:
Dim XL As Evts_Application

Private Sub Workbook_Open()
Set XL = New Evts_Application
Set XL.App = Application
End Sub
On pourrait également instancier directement la variable XL en intégrant le mot-clé New à sa déclaration:
Dim XL As New Evts_Application
Private Sub Workbook_Open()
Set XL.App = Application
End Sub
Avec ce procédé, il est possible de désactiver les procédures événementielles soit en "détruisant" le contenu de la variable App (Set XL.App = Nothing) soit en détruisant celui de la variable XL (Set XL = Nothing).
2. Evénements affectés à des collections d'objets

Le mot-clé WithEvents a comme principale limitation de ne pas supporter les variables de type tableau. Si l'on saisit par exemple une déclaration comme "Dim WithEvents Graph(5) As Chart", l'indice (5) est automatiquement supprimé à la validation de la ligne. L'instruction ReDim appliquée à une variable WithEvents ne déclenche pas d'erreur de compilation ou d'exécution, mais en revanche les procédures événementielles de cette variable ne seront pas déclenchées.
Cette limitation peut poser des problèmes si l'on veut affecter des événements à un grand nombre d'objets, comme par exemple tous les graphiques incorporés dans le classeur. Pour affecter par exemple la procédure Graph_Calculate définie en III.3 à chacun de ces graphiques, il faudrait déclarer autant de variables Graph qu'il y a de graphiques dans le classeur, chacune avec sa propre procédure Graph_Calculate et leur affecter ensuite chacun de ces objets Chart séparément:
Dim WithEvents Graph1 As Chart
Dim WithEvents Graph2 As Chart
Dim WithEvents GraphN As Chart

Private Sub Graph_Calc(Graph As Chart)
' Traitement à effectuer sur Graph
End Sub

Private Sub Graph1_Calculate()
Graph_Calc Graph1
End Sub

Private Sub Graph2_Calculate()
Graph_Calc Graph2
End Sub

Private Sub GraphN_Calculate()
Graph_Calc GraphN
End Sub

Private Sub Workbook_Open()
Set Graph1 = Feuil1.ChartObjects(1).Chart
Set Graph2 = Feuil2.ChartObjects(1).Chart
' ...
Set Graph1 = FeuilN.ChartObjects(X).Chart
End Sub
Cette méthode n'est évidemment pas viable et ne peut pas s'adapter automatiquement à un nombre variable de graphiques incorporés.
En passant par l'intermédiaire d'un module de classe, on peut simplifier cela en créant indirectement un tableau ou une collection de variables WithEvents par l'intermédiaire d'un tableau d'instances de la classe.
Le module de classe (appelé par exemple Classe_Graph) contiendra la déclaration d'une seule variable WithEvents de type Chart et le code de sa ou de ses procédures événementielles:
Public WithEvents Graph As Chart

Private Sub Graph_Calculate()
' Traitement à effectuer sur le graphique...
End Sub
Ensuite, il suffira de déclarer par exemple dans ThisWorkbook une variable tableau de type Classe_Graph, de créer dans ce tableau autant d'instances de Classe_Graph qu'il y a de graphiques, puis d'affecter à la variable Graph de chacune de ces instances chaque graphique du classeur à l'aide d'une boucle.
Ce qui pourrait donner par exemple le code suivant :
Dim Obj_Graph() As Classe_Graph

Private Sub Workbook_Open()

Dim Wksht As Worksheet
Dim I As Integer, J As Integer, K As Integer

' Parcours des feuilles de calcul du classeur
For Each Wksht In Me.Worksheets
J = Wksht.ChartObjects.Count
ReDim Preserve Obj_Graph(I + J)
' Parcours des graphiques de la feuille de calcul
For K = 1 To J
' Affectation d'une nouvelle instance de Classe_Graph
Set Obj_Graph(K + I) = New Classe_Graph
' Affectation du graphique à la variable Graph de l'instance
Set Obj_Graph(K + I).Graph = Wksht.ChartObjects(K).Chart
Next K
I = I + J
Next Wksht

End Sub
Ce genre de code est beaucoup plus simple à gérer si l'on utilise une variable de type Collection à la place d'une variable tableau, comme le montre l' exemple suivant:
Dim Obj_Graph As Collection

Private Sub Workbook_Open()

Dim Wksht As Worksheet
Dim ChObj As ChartObject

Set Obj_Graph = New Collection
For Each Wksht In Me.Worksheets
For Each ChObj In Wksht.ChartObjects
' Ajout d'une nouvelle instance de Classe_Graph à la collection
Obj_Graph.Add New Classe_Graph
' Affectation du graphique à la variable Graph de l'instance
Set Obj_Graph(Obj_Graph.Count).Graph = ChObj.Chart
Next ChObj
Next Wksht

End Sub
Dans les deux cas (tableau ou collection), autant d'instances de Classe_Graph qu'il y a de graphiques incorporés dans le classeur seront automatiquement créées, donc autant d'instances de la variable Graph. Les événements attachés à cette variable (Graph_Activate, Graph_Calculate...) seront déclenchés dès que l'utilisateur effectuera des manipulations sur n'importe lequel des graphiques incorporés dans le classeur.
Avec cette méthode, il est possible d'élargir les procédures événementielles à d'autres objets en redimensionnant le tableau (ou en ajoutant un élément à la collection), en affectant au nouvel élément une nouvelle instance de la classe puis en affectant à la variable Graph de cette instance une référence vers l'objet concerné. De même, pour annuler les procédures événementielles pour un objet particulier, il suffit d'affecter Nothing à l'élément du tableau correspondant (ou à sa variable Graph), ou encore de détruire l'élément de la collection par 'Obj_Graph.Remove Num_Index'.
V. Quelques procédures événementielles courantes

Avec les feuilles de calcul, les UserForms et leurs contrôles respectifs, les feuilles graphiques, les classeurs, l'objet Application, Excel dispose d'un nombre important de procédures événementielles prédéfinies. Commenter toutes ces procédures dépasserait de très loin le cadre de cette page.
Les procédures événementielles commentées ci-dessous sont des procédures fréquemment employées et dont l'utilisation réclame quelques précautions particulières.
1. Evénements de feuilles de calcul

Ces événements peuvent être gérés au niveau d'une feuille de calcul particulière, du classeur ou de l'application.
Procédure Evénement associé
Activate Activation de la feuille
BeforeDoubleClick Double-clic sur la feuille par l'utilisateur
BeforeRightClick Clic droit sur la feuille
Calculate Calcul d'une formule ou recalcul automatique de la feuille
Change Entrée de valeur(s) dans une plage
Deactivate Désactivation de la feuille
SelectionChange Changement de la plage sélectionnée
L'événement Change
Cet événement se produit dès qu'une ou plusieurs valeurs sont modifiées ou saisies dans une feuille de calcul. Dans les modules de feuilles, l'en-tête est:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
La variable Target transmet à la procédure la cellule ou la plage dont le contenu a été modifié. Il est important de noter qu'il peut s'agir d'une cellule unique, d'une plage de plusieurs cellules, ou encore d'une union de plages discontinues.
Si l'utilisateur change le contenu de la cellule A1, le paramètre Target contiendra la référence d'une cellule unique. S'il modifie en bloc la plage A1:A10 par Ctrl-Entrée ou par recopie incrémentée, ce paramètre référencera la plage A1:A10. S'il sélectionne la plage A1:A10 et la plage B1:B10 avant d'en modifier la valeur par Ctrl-Entrée, le paramètre Target contiendra une union de plages.
Pour éviter toute erreur à l'exécution, la procédure Worksheet_Change doit donc prendre en compte le fait que Target peut contenir plusieurs cellules, et modifier celles-ci par l'intermédiaire d'une boucle.
Voici par exemple comment compléter l'exemple mentionné plus haut (mise automatique en majuscules) en tenant compte de cette possibilité et en optimisant le code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Cell As Range
Dim ModeCalcul As Long

' Target = cellule unique :
If Target.Count = 1 Then
If VarType(Target) = vbString And Not Target.HasFormula Then
Application.EnableEvents = False
Target = UCase$(Target)
Application.EnableEvents = True
End If
Exit Sub
End If
' Target = plage de cellules :
' Désactivation du recalcul automatique,
' des procédures événementielles (pour éviter les appels
' récursifs) et de la mise à jour écran
ModeCalcul = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
' Désactivation de la touche Echap
On Error GoTo Fin
Application.EnableCancelKey = xlErrorHandler
' Mise en majuscule de chaque cellule de Target
For Each Cell In Target
If VarType(Cell) = vbString And Not Cell.HasFormula _
Then Cell = UCase$(Cell)
Next Cell

Fin:
' Rétablissement des paramètres de l'application
Application.Calculation = ModeCalcul
Application.EnableEvents = True

End Sub
Il est recommandé d'intégrer un gestionnaire d'erreur et de désactiver la touche Echap pour pouvoir rétablir les paramètres de l'application en cas d'interruption prématurée de la procédure. Si l'exécution était par exemple interrompue avant le rétablissement de la propriété EnableEvents sur True, cette procédure événementielle ainsi que toutes les autres seraient ensuite désactivées.
Comme le paramètre Target peut contenir une référence à plusieurs cellules, il est également nécessaire de passer par la fonction Intersect pour tester la modification d'une cellule particulière.
Par exemple, le code suivant empêche l'utilisateur d'effacer le contenu de la cellule A1:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Not IsEmpty(Range("A1")) Then Exit Sub
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub
La première ligne sort de la procédure si la cellule concernée (A1) ne fait pas partie de la plage modifiée. Si elle en fait partie et si son contenu a été effacé, l'action de l'utilisateur est annulée afin de replacer dans la cellule son contenu précédent.
L'événement Calculate
Cet événement se produit après tout recalcul de la feuille.
L'événement Calculate complète l'événement Change en ce sens qu'il permet de détecter des changements de valeur par calcul dans des cellules contenant des formules.
Contrairement à Change, la procédure Calculate ne dispose pas de paramètre Target. Il n'est donc pas possible de détecter quelles sont les cellules dont le contenu a été modifié à l'issue du recalcul. pour cela il est nécessaire de passer par une ou plusieurs variables stockant le contenu antérieur de la ou des cellules dont on veut intercepter le changement de valeur.
Dans le code suivant, un message est affiché à chaque fois que la cellule A1 de la feuille Feuil1 change de valeur, que ce soit par saisie manuelle (Worksheet_Change) ou calcul de formule (Worksheet_Calculate). La variable ValPrec sert à vérifier si cette valeur a changé en stockant la valeur précédente de la cellule et en la comparant à sa nouvelle valeur.
' Dans le module objet de Feuil1 :

Public ValPrec

Private Sub Worksheet_Calculate()
Vérif
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Vérif
End Sub

Private Sub Vérif()
If VarType(Range("A1")) = VarType(ValPrec) Then _
If ValPrec = Range("A1") Then Exit Sub
MsgBox "Cellule A1 passe de " & CStr(ValPrec) & _
" vers " & CStr(Range("A1"))
ValPrec = Range("A1")
End Sub

' Dans le module de code ThisWorkbook :

Private Sub Workbook_Open()
Feuil1.ValPrec = Feuil1.Range("A1")
End Sub
Worksheet_Activate et Worksheet_Deactivate
Ces procédures se déclenchent respectivement quand une feuille de calcul est activée ou désactivée par l'utilisateur (ou par une macro). Elles permettent entre autres de personnaliser l'affichage ou d'afficher une barre d'outils pour une feuille de calcul particulière lors de son activation, et de rétablir l'affichage standard ensuite.
Par exemple, pour que la fenêtre du classeur se mette en mode "plein écran" lorsque l'utilisateur active la feuille de calcul:
Private Sub Worksheet_Activate()
Application.DisplayFullScreen = True
End Sub

Private Sub Worksheet_Deactivate()
Application.DisplayFullScreen = False
End Sub
Au niveau du classeur, les procédures équivalentes sont WindowActivate et WindowDeactivate.
2. Evénements du classeur

Les événements associés au classeur sont d'une part relatifs à toutes les feuilles du classeur (événements similaires à ceux des feuilles de calcul, comme par exemple Worksheet_Calculate, comportant un paramètre Sh supplémentaire permettant d'identifier la feuille concernée), et d'autre part des événements spécifiques à l'objet classeur dans son ensemble:
Procédure Evénement associé
AddinInstall Installation du classeur (macro XLA)
AddinUninstall Désinstallation du classeur (macro XLA)
BeforeClose Demande de fermeture du classeur
BeforePrint Demande d'impression
BeforeSave Demande d'enregistrement du classeur
Deactivate Désactivation du classeur
NewSheet Insertion d'une nouvelle feuille
Open Ouverture du classeur
WindowActivate Activation du classeur ou d'une de ses fenêtres
WindowDeactivate Désactivation du classeur ou d'une de ses fenêtres
WindowResize Redimensionnement de la fenêtre du classeur
Workbook_Open et Workbook_BeforeClose
Ce sont les procédures les plus fréquemment utilisées. Elles se déclenchent respectivement quand le classeur est ouvert (par l'utilisateur ou par macro) ou en instance de fermeture.
Typiquement, ces procédures peuvent être employées pour afficher une barre d'outils spécifique au classeur au moment de son ouverture, et détruire cette barre à la fermeture.
Voici par exemple comment procéder avec une barre d'outils spécifique "MaBarreOutils". Celle-ci doit être au préalable attachée au classeur (menu Affichage -> Barres d'outils -> Personnaliser, bouton "Attacher").
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("MaBarreOutils").Delete
End Sub

Private Sub Workbook_Open()
Application.CommandBars("MaBarreOutils").Visible = True
End Sub
La barre d'outils étant attachée au classeur, on peut la détruire dans la procédure Workbook_BeforeClose. Elle sera automatiquement recréée à la réouverture du classeur.
Gérer les annulations de fermeture du classeur
Le code précédent présente un certain défaut. Si l'utilisateur a opéré des modifications dans le classeur et veut le fermer, Excel va effectuer les actions suivantes:
- Déclenchement de la procédure Workbook_BeforeClose => Suppression de la barre d'outils
- Demande de confirmation : "Voulez-vous enregistrer les modifications ?"
Si l'utilisateur répond par "Annuler" à cette demande de confirmation, le classeur reste ouvert, mais la barre d'outils a été supprimée.
Pour éviter ce problème, il est nécessaire que la procédure Workbook_BeforeClose prenne elle-même en charge cette demande de confirmation, afin de laisser la barre d'outils affichée si l'utilisateur annule la fermeture du classeur, ce qui peut donner lieu au code suivant:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Rep As Long

If Not Me.Saved Then
Rep = MsgBox("Voulez-vous enregistrer " & _
"les modifications apportées à '" & Me.Name & "'?", _
vbQuestion + vbYesNoCancel)
Select Case Rep
Case vbYes
Me.Save
Case vbCancel
Cancel = True
Exit Sub
Case vbNo
Me.Saved = True
End Select
End If
Application.CommandBars("MaBarreOutils").Delete

End Sub
Si l'utilisateur annule la fermeture en cliquant sur Annuler, le paramètre Cancel est mis sur True, ce qui désactive la fermeture du classeur (voir annulation d'événement) et 'Exit Sub' sort de la procédure. Le classeur est donc toujours ouvert, mais la barre d'outils n'a pas été supprimée. Cette technique peut être utilisée dans toutes les situations où la procédure Workbook_BeforeClose doit nettoyer l'environnement de la fenêtre Excel avant la fermeture du classeur.
Les macros complémentaires XLA ne posent pas ce genre de problème, car elles ne demandent pas de confirmation à la fermeture. En principe, les procédures AddinInstall et AddinUninstall sont nettement mieux adaptées à ce type d'application que Workbook_Open et Workbook_BeforeClose.
3. Evénements de l'application

Les événements de niveau Application reprennent tous ceux liés aux feuilles de calcul (SheetActivate, SheetCalculate...) et aux classeurs (WorkbookOpen etc.). Le seul événement nouveau est NewWorkbook, qui se déclenche à la création d'un nouveau classeur.
Pour finir, voici un exemple très simple de procédure Workbook_Open de niveau Application. Cette procédure de macro XLA change le répertoire courant par celui du dernier classeur ouvert. Par exemple, si on ouvre le classeur "C:\Temp\Test.xls" en le sélectionnant directement dans la liste des derniers classeurs ouverts du menu Fichier, le répertoire courant (celui de la boîte de dialogue "Ouvrir") devient "C:\Temp\". Ce n'est pas le comportement par défaut sous Excel 97.
Dim WithEvents XL As Application

Private Sub Workbook_AddinInstall()
Set XL = Application
End Sub

Private Sub XL_WorkbookOpen(ByVal Wb As Excel.Workbook)
ChDir Wb.Path
End Sub
14068 lectures