Comment lancer automatiquement une macro à l’ouverture d’un fichier Excel ?
Dans ce tutoriel, je vais vous montrer comment automatiser le lancement d’une macro commande VBA lorsque nous allons ouvrir un classeur Excel.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation.
Comme nous venons de le voir dans l’introduction de ce tutoriel, je vais vous montrer comment automatiser le lancement d’une macro commande à l’ouverture d’un fichier Excel.
Cela nous permettra par exemple d'effectuer un certain nombre de contrôles, et éventuellement de diriger le comportement que le classeur doit avoir en fonction du résultat obtenu par ce contrôle.
Nous pourrons par exemple imaginer contrôler le dépassement des dates d’échéance pour le règlement des factures, ou encore vérifier qu’il n’y a pas de retard dans les dates des visites médicales des employés d’une entreprise, etc.
Et c’est justement ce dernier exemple que nous allons vouloir mettre en place dans ce tutoriel.
2. Contrôler le dépassement des dates de visites médicales des employés
Pour cela, comme vous pouvez le voir sur la capture affichée ci-dessous, nous disposons d’une base de données reprenant les informations du personnel d’une entreprise, avec les noms, les prénoms, les dates de naissance et la date de la dernière visite médicale :
Pour savoir si la personne est à jour, il suffit de comparer les valeurs de la colonne « Prochaine visite » avec la date du jour.
Nous allons donc ajouter une nouvelle colonne à la suite de ce tableau, que nous allons appeler « Vérification » :
À l’intérieur de celle-ci, nous allons insérer une simple formule qui va vérifier si la date est dépassée ou non :
=SI(C9<AUJOURDHUI();"Dépassée";"OK")
Nous utilisons donc la fonction SI() qui permet d’effectuer un test, puis de retourner un résultat différent en fonction du résultat de ce dernier.
Si la date est inférieure à la date du jour, que nous récupérons avec la fonction AUJOURDHUI(), alors nous affichons le message « Dépassée », et dans le cas contraire nous affichons simplement « OK ».
Si vous souhaitez en savoir plus sur ces deux fonctions, suivez simplement les liens suivants :
3. Être alerté en cas de dépassement des dates d'échéance
Ensuite, pour être alerté automatiquement lorsqu’il y a des dépassements, nous allons créer une macro commande VBA et nous allons vouloir lancer celle-ci automatiquement au démarrage.
Pour cela, nous allons nous rendre dans VBE (Visual Basic Editor), qui est l’outil de développement des macros commande, en appuyant sur les touches [Alt]+[F11].
Une fois à l’intérieur du projet, nous allons pouvoir créer un nouveau module, pour y saisir la macro VBA en nous rendant dans le menu « Insertion » > « Module ».
Une fois que le module est inséré, nous allons pouvoir créer une nouvelle macro en utilisant le mot-clé Sub suivi du nom de la macro, que nous appellerons « verifierVisites » :
Une fois que nous appuyons sur la touche [Entrée], Excel insère automatiquement une ligne « End Sub » qui marque la fin de la macro.
Maintenant, tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement lorsque nous lancerons la macro.
Ensuite, pour déterminer s’il existe effectivement des dates dont l’échéance est dépassée, nous allons vouloir dénombrer le nombre de fois que le mot « Dépassée » est affiché dans la colonne « Vérification ».
Pour cela, nous allons revenir sur la feuille de calcul afin de transformer notre base de données en a un tableau Excel.
Pour cela, nous appuyons sur les touches [Ctrl]+[L].
Puis, sur la boîte de dialogue affichée à l’écran, nous pouvons vérifier qu’effectivement Excel a bien détecté les coordonnées du tableau, et étant donné que celui-ci possède des titres sur chaque colonne, nous pouvons cocher l’option « Mon tableau possède des en-têtes » :
Ensuite, nous validons en appuyant sur « OK ».
Pour pouvoir manipuler facilement ce tableau, nous allons lui affecter un nom en nous rendant dans le menu « Création de tableaux », qui n’apparaît que lorsqu’une des cellules du tableau est sélectionnée, puis tout à gauche, nous allons donner un nom : « suiviVisites » :
De cette manière, nous pourrons appeler très simplement ce tableau depuis VBA en saisissant ce nom entre crochets.
De retour dans la macro, nous allons commencer par créer une variable que nous appelons « nombreDepassements », que nous typons en tant que Integer, c’est-à-dire un nombre entier.
dim nombreDepassements as Integer
Puis, nous y stockons le nombre de dépassements que nous obtenons avec la fonction Application.WorksheetFunction.CountIf qui est l’équivalent de la fonction Excel NB.SI() :
nombreDepassements = Application.WorksheetFunction.CountIf([suiviVisites[Vérification]], "Dépassée")
Il ne reste plus qu’à regarder la valeur de cette variable, afin d’afficher un message si celle-ci est supérieure à zéro :
If nombreDepassements > 0 Then
MsgBox "Attention, " & nombreDepassements & " dates de visites sont dépassées"
End If
La macro est maintenant terminée, si nous souhaitons la tester, il suffit d’appuyer sur la touche [F5] pour constater qu’effectivement Excel nous affiche l’information :
4. La macro évènementielle : Workbook_Open
Revenons-en maintenant à l’objet de cette vidéo, qui est de voir comment faire en sorte de lancer automatiquement cette macro à l’ouverture du classeur.
Pour cela, nous allons faire appel à une notion que nous avons découverte dans un tutoriel précédent : les macros événementielles.
Il s’agit en effet d’un type de macro commande qui va s’exécuter automatiquement lorsqu’une action en particulier va se produire sur une feuille de calcul ou alors sur le classeur en lui-même.
Ici, l’événement qui va nous intéresser est l’événement qui va être appelé lorsque nous allons ouvrir le classeur.
Pour cela nous revenons dans notre projet VBA et nous double cliquons sur la feuille « ThisWorkbook » :
Ensuite pour sélectionner l’événement qui va s’ouvrir à l’ouverture du classeur, nous allons sélectionner dans le premier menu déroulant que nous retrouvons en haut au-dessus de la feuille de code « Workbook » :
De cette manière dans le second menu déroulant, nous allons retrouver tous les événements disponibles sur le classeur :
Ici l’événement qui nous intéresse et l’événement « Open », qui comme vous pouvez le constater a été automatiquement ajouté sur la feuille de code par Excel :
Mais si nous avions voulu lancer un autre événement, comme nous le verrons plus tard, nous pourrions le choisir dans la liste du second menu déroulant.
Ensuite, il ne reste plus qu’à demander à Excel, à l’intérieur de cette macro événementielle, d’appeler la macro « verifierVisites » que nous avons créée juste avant :
Pour finir, nous enregistrons le classeur, et il nous suffira de le relancer pour constater qu’effectivement Excel affichera le message avec le dénombrement des visites dépassées.
5. Afficher le message lorsque nous activons la feuille
Comme je vous le disais juste avant, les événements disponibles sont très nombreux.
Nous pouvons par exemple vouloir utiliser un événement qui va s’exécuter automatiquement lorsque nous allons activer la feuille de calcul.
Pour cela nous allons tout d’abord commencer par créer une autre feuille de calcul et nous rendre à l’intérieur de celle-ci.
Ensuite, nous revenons dans VBE en appuyant à nouveau sur [Alt]+[F11], puis cette fois-ci, nous allons double cliquer sur la feuille de calcul principal.
Dans le premier menu déroulant, nous allons sélectionner « Worksheet » :
Et dans le second menu déroulant, nous allons cliquer sur « Activate », c’est-à-dire que nous allons paramétrer l’événement qui va se lancer à chaque fois que nous sélectionnons la feuille de calcul ;
Ensuite, à l’intérieur de cet événement, nous allons tout simplement appeler à nouveau la fonction « verifierVisites » :
Maintenant, nous revenons sur notre classeur, et lorsque nous allons cliquer, et à chaque fois que nous allons revenir sur la feuille de calcul principale, nous pourrons constater qu’effectivement Excel va effectuer le test et afficher le message correspondant :
