Si vous manipulez des données sur Excel, vous avez régulièrement à effectuer des opérations récurrentes, comme sélectionner certaines cellules, les déplacer à tel endroit, en calculer la somme, etc. Ou vous pouvez vouloir créer une feuille de données invitant l'utilisateur à saisir des requêtes dans une boîte de dialogue et affichant automatiquement la réponse voulue.

>> Téléchargez ces modèles Excel professionnels à utiliser dès maintenant.

Excel ne permet pas par défaut d'exécuter des actions aussi ciblées sur vos besoins spécifiques, mais propose deux outils différents et complémentaires pour développer des fonctions dédiées : l'enregistreur de macros et l'éditeur VBA (Visual Basic for Applications). Vous enregistrez et/ou créez une fois pour toutes une macro que vous pouvez ensuite réutiliser à votre gré.

Comment créer une Macro ?

Les deux solutions proposées par Excel adoptent des approches totalement différentes : l'enregistreur procède par sauvegarde d'actions manuelles de l'utilisateur, tandis que l'environnement VBA fait appel à du codage dans un langage dédié en vue de développer de véritables outils distincts. L'une et l'autre approches exigent une réflexion préalable quant au scénario à adopter. C'est d'autant plus vrai dans l'enregistreur, qui comme son nom le laisse supposer, va enregistrer strictement une séquence que vous exécutez à la main. Le principe est ensuite de conserver cet enregistrement en vue de l'appliquer automatiquement à d'autres données dès lors que vous avez besoin de les traiter à l'identique. Les macros codées en langage VBA peuvent, elles, être modifiées et peaufinées, notamment grâce à la fonction de test en temps réel intégrée à l'éditeur VBA. La syntaxe du langage VBA est particulièrement riche et permet de développer des outils personnalisés très puissants.

Créer une macro grâce à l'enregistreur

Dans l'exemple proposé ci-dessous, la secrétaire d'un centre de loisirs veut harmoniser la présentation des tableaux répertoriant les enfants inscrits. Elle dispose d'un classeur Excel avec un onglet pour chaque année de naissance. Les différents onglets adoptent une présentation incohérente et la secrétaire souhaite y remédier. Initialement, les tableaux sont présentés sous cette forme : 

15802729557948246

Elle souhaite obtenir le résultat suivant, et ce pour toutes les classes d'âge des enfants :

15802729924215589

Elle a donc le choix entre ouvrir chaque onglet d'une classe d'âge pour y apporter à chaque fois les différentes modifications ou, avantageusement, choisir d'effectuer une seule fois ces modifications tout en les enregistrant dans une macro. Il lui suffira ensuite d'appliquer cette macro sur tous les autres tableaux qui prendront en une seconde et automatiquement la mise en forme voulue.

Pour commencer, ouvrez un nouveau classeur et organisez des données telles que présentées dans le premier tableau.

Réfléchissez ensuite à la séquence logique qu'il convient d'adopter pour aboutir au résultat voulu. Cette étape de planification est indispensable, faute de quoi vous risquez de devoir réenregistrer plusieurs fois la macro, car vous ne pouvez pas revenir en arrière en cas d'erreur.

Le scénario ici serait le suivant :

  • Inversion des colonnes Prénom et Nom.
  • Suppression de la colonne vide.
  • Application d'un même format de date à la colonne C.
  • Affichage du quadrillage.
  • Application de couleurs spécifiques au fond et à la police de l'en-tête.

Commencez par vérifier que l'onglet Développeur est disponible dans le Ruban d'Excel. Si ce n'est pas le cas, il convient de l'activer au préalable en sélectionnant le menu Fichier / Options / Personnaliser le ruban. Dans la boîte de dialogue qui s'affiche, cochez la case Développeur :

1580273075694654

Cliquez sur le bouton Enregistrer une macro pour démarrer l'enregistrement :

15802731179887013

Une boîte de dialogue s'affiche alors, demandant d'attribuer un nom à la macro. Il est également possible de lui affecter une combinaison de touches via laquelle vous pourrez ré-exécuter la macro par la suite. Vous pouvez au besoin en donner une description dans la zone dédiée :

1580273150163513

Cliquez sur OK pour lancer l'enregistrement.

Le nom du bouton dans la barre d'outils change et devient Arrêter l'enregistrement.

Il est l'heure de procéder à la séquence d'opérations nécessaires pour effectuer le traitement du tableau et aboutir au résultat voulu.

1) Inversion des colonnes A et B : sélectionnez la colonne B et appuyez sur la touche Maj. Le curseur prend la forme de 4 flèches de direction. Maintenez enfoncée la touche Maj tout en déplaçant la colonne B vers la gauche. Elle est alors permutée avec la colonne A.

Première étape réalisée avec succès.

2) Suppression de la colonne C : il suffit de la sélectionner, de cliquer sur le bouton droit et de sélectionner l'option Supprimer dans le menu contextuel.

Deuxième étape réalisée avec succès.

3) Harmonisation du format des dates de naissance : sélectionnez la colonne, puis appelez la boîte de dialogue des propriétés des cellules au moyen de la combinaison de touches Ctrl+Maj+1. Sélectionnez l'option Date et choisissez le format de date souhaité :

15802736113161736

Troisième étape réalisée avec succès. 

4) Quadrillage du tableau : sélectionnez le tableau et appliquez l'option Toutes les bordures depuis l'icône appropriée du Ruban :

15802736619515812

Quatrième étape réalisée avec succès. 

5) Formatage des cellules d'en-tête : sélectionnez les 4 cellules d'en-tête, centrez le contenu, changez la couleur de fond et la couleur de la police à l'aide des icônes appropriées du Ruban : 

15802736930452886

La séquence d'opérations manuelles étant à présent terminée, revenez dans l'onglet Développeur et cliquez sur le bouton Arrêter l'enregistrement.

Si vous avez affecté la macro à une combinaison de touches lors du lancement de l'enregistreur, il suffit d'appliquer cette combinaison sur chacun des tableaux à mettre en forme pour que l'ensemble des actions précédemment enregistrées se déroulent automatiquement à l'identique.

Créer une macro avec VBA

Dans l'exemple ci-dessous, la secrétaire du centre de loisirs, toujours elle, veut pouvoir interroger le fichier des enfants inscrits et vérifier si leur cotisation est payée ou non. Pour ce faire, elle souhaite développer une macro demandant à saisir les nom et prénom d'un enfant afin d'obtenir en réponse l'information sur le paiement de la cotisation.

Prenez votre fichier de l'exemple précédent, Inscriptions.xlsx. Dans l'onglet Développeur du Ruban, sélectionnez l'option Visual Basic. L'éditeur VBA s'ouvre alors.

Cliquez sur le nom de la feuille active dans la fenêtre de gauche pour faire apparaître le curseur de saisie dans la fenêtre de droite. Vous pouvez alors commencer à saisir votre code.

15802737843850799

Une macro s'ouvre toujours par le terme « Sub » et se termine par « End Sub ».

Saisissez donc Sub Contrôle_Cotisation, où Contrôle_Cotisation est le nom que vous attribuez à la macro, puis appuyez sur la touche Entrée. Automatiquement, une paire de parenthèses et le repère de fin de macro End Sub s'affichent. Le script de votre macro est à écrire entre les repères Sub et End Sub.

À titre d'exemple, voici le script répondant à la problématique de la secrétaire :

Sub Contrôle_Cotisation()
If ActiveSheet.Name <> "Inscriptions" Then Exit Sub
Dim dlig&, lig&: dlig = Cells(Rows.Count, 1).End(xlUp).Row
Dim NP$, Nom$, Prénom$, msg$, lng As Byte, p As Byte
Do
NP = InputBox("Saisir les nom et prénom, séparés par une espace :", "Enfant")
lng = Len(NP): p = InStr(NP, " ")
Loop Until lng = 0 Or (lng > 6 And p > 0)
If lng = 0 Then Exit Sub
Nom = Left$(NP, p - 1): Prénom = Right$(NP, lng - p)
p = 0
For lig = 2 To dlig
If LCase$(Cells(lig, 1)) = LCase$(Nom) Then
If LCase$(Cells(lig, 2)) = LCase$(Prénom) Then
p = 1: Exit For
End If
End If
Next lig
Cells(lig, 1).Select: Application.ScreenUpdating = False
msg = "La réponse concernant l'enfant " & WorksheetFunction.Proper(NP) & ", est " & _
IIf(p = 1, LCase$(Cells(lig, 4)), "Enfant non trouvé") & "."
MsgBox msg, 64, "Résultat de la recherche"
End Sub

Le propos n'est pas ici d'expliquer la syntaxe de VBA, car les possibilités sont multiples. Le script ci-dessus en illustre quelques-unes : 

  • déclaration de variables à l'aide de la commande Dim, 
  • exécution en boucle de la macro avec la commande Loop, 
  • invitation à saisir des données avec la commande InputBox, 
  • affichage d'informations avec la commande MsgBox, etc.

Une fois le script saisi, vous pouvez le tester et le déboguer pas-à-pas à l'aide des boutons de la barre d'outils ou de la touche F8 :

15802739286256814
Une fois la macro terminée, vous pouvez l'associer à un bouton pour l'exécuter : dans l'onglet Développeur, sélectionnez l'option Insérer et choisissez l'icône de bouton. 

15802740280784137

Tracez la forme du bouton. Dans la boîte de dialogue qui s'affiche alors, sélectionnez la macro, puis renommez votre bouton, par exemple Contrôle :

1580274156028332

Lorsque vous exécutez cette macro, une première boîte de dialogue s'affiche, vous invitant à saisir le nom d'un enfant. Une deuxième boîte de dialogue affiche alors le résultat de la requête :

15802739689542391

Remarque : pour conserver la macro dans le fichier Excel, il convient de l'enregistrer au format XLSM.

Il y a toujours une bonne raison d'utiliser Excel. Tableaux, factures, reçus, plannings : découvrez ces modèles Excel professionnels à utiliser dès maintenant.

Bottom-CTA : Modèles et exemples Excel

 Slide-in-CTA : Modèles et exemples gratuits Excel

Publication originale le 2 mars 2020, mise à jour le 05 octobre 2020

Sujet(s):

Excel