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.
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é.
Qu'est-ce qu'une macro Excel ?
Une macro (ou macrocommande) Excel est un programme simple à complexe, selon le degré d'élaboration de son code. Elle automatise une séquence d'actions ou de calculs récurrents, et exécute des fonctions avancées que ne propose pas l'interface standard d'Excel. Elle peut être réalisée à l'aide de l'enregistreur de macros Excel ou dans l'éditeur VBA (Visual Basic for Applications) d'Excel.
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 :
Elle souhaite obtenir le résultat suivant, et ce pour toutes les classes d'âge des enfants :
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 :
Cliquez sur le bouton Enregistrer une macro pour démarrer l'enregistrement :
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 :
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é :
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 :
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 :
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
Qu'est-ce que VBA sur Excel ?
Visual Basic for Applications (VBA) est le langage de programmation créé par Microsoft et intégré dans toutes les applications de la suite Office (Excel, Word, Access, etc.). VBA est principalement utilisé dans la création de macros afin d'automatiser et accélérer des tâches récurrentes.
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.
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 :
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.
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 :
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 :
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 de graphiques Excel pour formater vos données et mettre en évidence vos résultats.
Comment faire une macro sur Excel ?
Mis à jour :
Publié :
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.
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é.
Qu'est-ce qu'une macro Excel ?
Une macro (ou macrocommande) Excel est un programme simple à complexe, selon le degré d'élaboration de son code. Elle automatise une séquence d'actions ou de calculs récurrents, et exécute des fonctions avancées que ne propose pas l'interface standard d'Excel. Elle peut être réalisée à l'aide de l'enregistreur de macros Excel ou dans l'éditeur VBA (Visual Basic for Applications) d'Excel.
Comment créer vos graphique sur Excel en toute simplicité ?
Téléchargez ces modèles de graphiques Excel et découvrez comment utiliser des graphiques qui feront parler vos données.
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 :
Elle souhaite obtenir le résultat suivant, et ce pour toutes les classes d'âge des enfants :
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 :
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 :
Cliquez sur le bouton Enregistrer une macro pour démarrer l'enregistrement :
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 :
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é :
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 :
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 :
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
Qu'est-ce que VBA sur Excel ?
Visual Basic for Applications (VBA) est le langage de programmation créé par Microsoft et intégré dans toutes les applications de la suite Office (Excel, Word, Access, etc.). VBA est principalement utilisé dans la création de macros afin d'automatiser et accélérer des tâches récurrentes.
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.
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 :
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 :
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.
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 :
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 :
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 de graphiques Excel pour formater vos données et mettre en évidence vos résultats.
Partager cet article sur les réseaux sociaux
Articles recommandés
Comment calculer un écart type sur Excel ? (+ exemple)
Fichier CSV : définition, création et import dans Excel
Comment faire une liste déroulante sur Excel ?
Comment créer un heatmap sur Excel
TCD Excel : comment faire un tableau croisé dynamique ?
Comment utiliser la fonction NB.SI sur Excel correctement ?
Comment supprimer les doublons sur Excel ?
Comment faire un graphique sparkline sur Excel ?
Comment utiliser la formule INDEX + EQUIV sur Excel ? (+ exemple)
Comment filtrer sur Excel ? (Filtre automatique + fonction FILTRE)