Excel et l'enregistreur de macro

En utilisant Excel, il n'est pas rare de devoir réaliser régulièrement, parfois quotidiennement, des opérations répétitives laborieuses et porteuses de peu, voire de pas du tout, de plus-value.
Qui n'a jamais pesté de devoir de façon récurrente sélectionner les x premières lignes d'une plage pour les transférer dans une autre feuille, en les mettant en italique sur fond rouge avec bordure violette à pois jaunes ?

La machine pourrait parfaitement remplacer l'humain pour ces tâches abrutissantes. Excel vous permet d'enregistrer les opérations récurrentes et répétitives, de manière à vous permettre de les exécuter de façon automatique. Cet outil, c'est l'enregistreur de macro.

Cet enregistreur va mémoriser vos actions dans Excel et va les transcrire en code informatique, exécutable à l'envi.

Si le moins que l'on puisse dire est que cet enregistreur ne remplace pas un bon programmeur, il permet à l'utilisateur peu formé aux langages informatiques, voire pas du tout, de créer des « macros » simples et réutilisables.

Il permet également au programmeur VBA chevronné de « se souvenir » rapidement d'un code dont il ne connait plus bien la syntaxe.

Ce tutoriel a pour objectif de vous initier à l'utilisation de cet enregistreur, au travers d'un petit exemple simple ne demandant pas trop de connaissances en Excel et surtout, ne demandant AUCUNE connaissance en langage de programmation.

La version utilisée pour ce tutoriel est Excel 2013. Les adaptations de l'application sont identiques pour les versions 2007 et suivantes. Pour ce que nous verrons dans ce tutoriel, les illustrations sont similaires ou très approchantes. Les utilisateurs d'une version antérieure à Xl2007 pourront adapter le contenu de ce cours avec assez peu d'efforts.

Les forums sont bien évidemment là pour vous permettre de compléter votre apprentissage.

Enregistrer une macro, c'est réaliser un acte de développement d'application. Certes, vous n'allez pas vous lancer dans la création d'un logiciel grand public, l'enregistreur en tant que tel ne vous en donne pas les moyens. Mais comme vous allez développer, il vous faut des outils de développeur.

Vous trouverez ces outils sur l'onglet [Développeur] du ruban, invisible lors de l'installation d'Excel. Pour le rendre accessible, cliquez droit sur le ruban puis {Personnaliser le ruban}. Dans la fenêtre des options qui s'ouvre, cochez Onglet Développeur dans le panneau de droite.

Le ruban [Développeur] apparaît, normalement à droite de l'onglet [Affichage].

L'emplacement de l'onglet peut différer selon votre personnalisation du ruban. Vous pouvez également afficher l'onglet [DEVELOPPEUR] via le menu Backstage [FICHIER]>Options>Personnalisation du ruban.
Le ruban illustré peut différer chez vous, mais ce n'est pas très important. Dans le cadre de ce tutoriel, nous n'utiliserons que le premier groupe d'actions nommé Code.

Notez qu'il n'est pas nécessaire que le ruban soit actif pour pouvoir démarrer et stopper l'enregistreur (voir plus bas dans le tutoriel). Cependant, je conseille d'afficher le ruban, car il permet notamment de placer un bouton sur la feuille pour activer facilement une macro.

Votre première macro va simplement consister à :

• colorer le fond d'une sélection en rouge ;
• placer un cadre +/- épais autour de la sélection ;
• placer des lignes en pointillé sur les bords intérieurs de la sélection.

Créez un nouveau classeur et sélectionnez une plage sur la feuille active.

Lorsque cette mise en place est effectuée, vous allez démarrer l'enregistreur via l'onglet [DEVELOPPEUR]>Code>Enregistrer une macro.

Avant d'enregistrer une nouvelle macro, vous devrez :

• lui donner un nom ;
• détailler brièvement ce qu'elle fait (facultatif) ;
• lui attribuer un raccourci (facultatif) ;
• spécifier le classeur dans lequel elle sera enregistrée. Dans le cas qui vous occupe, choisissez Ce classeur.

Ne cliquez pas sur OK trop vite. Tout ce que vous ferez dans Excel (enfin, presque tout) sera enregistré dans la macro. Il faut donc faire TOUT ce que vous voulez enregistrer, mais UNIQUEMENT ce que vous voulez enregistrer.
Réfléchissez bien aux données que vous saisissez dans cette boite de dialogue, car il sera malaisé, du moins lors de vos premiers pas, de modifier ces informations lorsque la macro sera enregistrée.

Observez rapidement les zones de cette boite de dialogue :

• Nom de la macro : donnez un nom explicite à votre macro, surtout si vous en créez plusieurs au sein d'un classeur. Respectez les règles suivantes. Le nom :
  • commence toujours par une lettre,
  • ne contient pas d'espaces (utilisez le _ pour matérialiser l'espace),
  • ne contient que des lettres et des chiffres, si possible sans caractères accentués (ils sont admis, mais déconseillés) ;
• Touche de raccourci : à utiliser si vous souhaitez pouvoir lancer la macro via un raccourci. N'utilisez pas un raccourci générique d'Excel !
• Enregistrer la macro dans : vous avez plusieurs choix possibles :
  • Ce classeur : la macro fait partie intégrante du classeur. Si vous transmettez ce classeur à un tiers, il pourra utiliser la macro,
  • Nouveau classeur : la macro sera enregistrée dans un nouveau classeur. Dans les faits, je n'ai jamais utilisé cette option,
  • Classeur de macros personnelles : la macro sera enregistrée dans un classeur spécial qui sera systématiquement ouvert en invisible à chaque démarrage d'Excel. Les macros seront donc disponibles dans tous vos classeurs. Le corollaire est que pour qu'un tiers puisse utiliser la macro, vous devrez lui transmettre votre classeur de macros personnelles ;
• Description : description succincte de ce que la macro est censée réaliser.

Lorsque nous sommes prêts, nous cliquons sur OK et c'est parti. Il suffit alors de :

• colorier en rouge la sélection des cellules ;
• choisir les bordures appropriées.

Ne « bégayez » pas durant ces opérations. L'enregistreur de macro est actif et enregistre donc (presque) toutes les actions réalisées sur votre classeur.

Après avoir réalisé les étapes citées plus haut, vous stoppez la macro, soit par le ruban, soit via la barre d'état.

La macro est enregistrée et l'enregistreur s'arrête.

Ces deux boutons sont des boutons bascule (ON/OFF). Vous pouvez donc démarrer ET stopper la macro via ces boutons. Notez également qu'il n'est pas nécessaire d'activer le ruban pour que ces boutons spécifiques soient disponibles.
Via l'onglet d'affichage, vous avez également accès aux boutons de démarrage et d'arrêt de la macro, toujours sans qu'il soit nécessaire que le ruban Développeur soit activé.

Toutes les actions ne sont pas enregistrées dans la macro. Seules celles qui altèrent le classeur le sont. C'est ainsi que l'action de cliquer sur des onglets du ruban n'est pas enregistrée dans la macro.

Pour exécuter une macro disponible, vous pouvez aller sur l'onglet [DEVELOPPEUR]>Code>Macros.

Dans la fenêtre, il suffit de sélectionner la macro souhaitée, puis de cliquer sur Exécuter.

La nouvelle sélection est alors colorée en rouge et quadrillée selon les choix opérés durant l'enregistrement de la macro.

Vous remarquerez donc que la macro travaille avec la sélection active, puisque nous n'avons pas sélectionné une plage durant l'enregistrement de la macro.

Pour visualiser le code qui a été enregistré, il suffit d'ouvrir la fenêtre des macros comme pour en exécuter une, et de cliquer sur Modifier. L'éditeur de code (VBE pour Visual Basic Editor) s'ouvre et vous pouvez admirer le code que « vous » avez enregistré.

Plus loin dans le tutoriel, nous reviendrons sur le code produit par l'enregistreur.

Pour conserver le code enregistré et donc pouvoir jouer à nouveau la macro, il faut enregistrer le classeur. Le code associé à celui-ci sera enregistré en même temps, puisqu'il fait partie intégrante du classeur.

Jusqu'à la version 2003 comprise, un classeur contenait ou non des macros, et ce n'est qu'à l'ouverture dudit classeur, et pour autant que les sécurités soient mises en place, que l'on savait s'il contenait des macros incorporées ou non.

Depuis la version 2007, Microsoft a différencié les classeurs selon qu'ils contiennent ou non des macros. Les classeurs XLSX ne contiennent jamais de macros. Les classeurs XLSM peuvent contenir des macros. Les icônes sont différentes, ainsi que les infobulles s'affichant dans l'explorateur de fichiers de Windows.

Vous devez choisir un format qui supporte les macros pour que les macros soient enregistrées avec le fichier.

Si vous choisissez un format n'acceptant pas les macros pour enregistrer un fichier qui en contient, Excel supprimera simplement les macros. Vous avez bien lu « supprimera », ce qui signifie que vous les perdrez définitivement. Au vu des conséquences, Excel vous affichera un message explicite avant l'enregistrement.

Vous venez de découvrir l'enregistreur de macro d'Excel. Vous savez maintenant créer vos propres macros simples à l'aide de cet outil. Vous savez également où enregistrer vos macros, et vous avez découvert l'envers du décor en allant voir du côté du VBE pour découvrir le code créé par l'enregistreur.

Vous savez également enregistrer votre classeur contenant des macros de manière à pouvoir les réutiliser par la suite.

Après cette prise en main rapide de l'enregistreur, vous allez maintenant comprendre comment Excel sélectionne les cellules et les feuilles durant l'enregistrement de la macro. Vous pourrez alors créer des macros réellement utiles, dont la complexité augmentera de quelques crans par rapport à la macro de coloriage.

Vous devez saisir les informations de personnes participant à des formations. Il vous faut donc enregistrer les données personnelles des participants, ainsi que la formation à laquelle ils s'inscrivent et la date de la session.

Vous disposez pour cela d'un classeur contenant une feuille de saisie de données et d'une feuille dans laquelle stocker vos données. Pour faciliter la saisie, la zone de saisie est mise en forme. Après la saisie, vous souhaitez transférer les données du « formulaire » vers la plage de données. Les cellules à transférer sont plus ou moins nombreuses, la saisie de données est fréquente et la personne qui effectue le travail voudrait automatiser le transfert des données de la feuille formulaire vers la plage de données.

La bonne nouvelle est que l'enregistreur de macro va vous aider à automatiser ce travail. Vous allez également vous apercevoir qu'une bonne maîtrise des outils Excel facilitera la création de vos macros et simplifiera celles-ci, parfois de manière substantielle.

Je sais qu'Excel n'est pas l'outil le plus approprié pour réaliser ces opérations, et qu'il serait professionnellement plus judicieux d'utiliser des outils tels qu'Access pour réaliser la saisie et le stockage de données. Mais dans la mesure où tout le monde ne dispose pas d'Access, Excel peut être une solution intéressante.

L'enregistreur de macro retenant tout ce qui est réalisé durant l'enregistrement, il convient que le classeur soit préparé et que la séquence à enregistrer soit bien répétée, de façon à n'enregistrer que ce qui doit l'être.

III-C-1. Création des feuilles▲

Lors de l'utilisation de l'enregistreur de macro, il est impératif de bien scénariser ce que vous allez devoir réaliser durant l'enregistrement. Pour rappel, l'enregistreur enregistre tout ce vous faites. Si vous vous trompez durant l'enregistrement, vous devrez soit aller trifouiller dans le code pour nettoyer les actions inutiles, soit enregistrer à nouveau la macro.

Il importe donc de rédiger un « road-book » de ce que vous allez effectuer, et de penser à tous les cas de figure.

Par exemple, si la cellule B3 du formulaire est active au moment de démarrer l'enregistreur, Excel n'enregistrera pas qu'il faut la sélectionner. Dès lors, si vous démarrez l'exécution de la macro alors qu'une autre cellule est active, vous transférerez de mauvaises données dans la table.

Une bonne habitude est donc de se mettre « hors contexte » avant de démarrer l'enregistreur. Dès lors, on notera dans le road-book d'activer une autre cellule de la feuille, puis on sélectionnera éventuellement une autre feuille du classeur, dans le cas où la macro pourrait démarrer avec une mauvaise feuille active, de manière à ce que l'enregistreur mémorise la sélection de la bonne feuille et de la bonne cellule.

Afin de faciliter l'apprentissage de l'utilisation de l'enregistreur et de faciliter la démarche, nous insérerons les nouvelles données en début de tableau. Dans la mesure où normalement, l'ordre des données dans la table ne peut influencer le traitement de celles-ci, ce n'est pas un problème en soi.

III-D-1. Étapes du traitement▲

Après avoir rédigé votre road-book, vous allez répéter votre macro plusieurs fois, de manière à être certain que vous n'avez rien oublié et que vous êtes bien rôdé. Je le répète encore une fois pour que cela rentre bien, l'enregistreur mémorisera tous vos faits et gestes dans Excel (enfin, presque tous). Il convient dès lors d'être certain de la pièce à jouer, sous peine de devoir recommencer l'enregistrement ou d'aller patauger dans le code pour le « nettoyer ».

Lorsque vous êtes fin prêt, vous créez une nouvelle macro comme expliqué plus haut dans ce tutoriel, et vous vous lancez, en respectant scrupuleusement votre road-book. Normalement, suite à vos répétitions, il est nickel et reflète bien l'ensemble des actions que vous devez mener.

Si tout s'est bien passé, que vous n'avez pas bégayé durant l'enregistrement, vous disposez maintenant d'une macro qui transfère les données du formulaire vers une « table de données », puis qui nettoie le formulaire et se positionne pour une nouvelle saisie.
Vous remarquerez que les saisies repoussent les lignes existantes vers le bas, puisque nous insérons toujours une ligne en première ligne de la plage de données. Dans la mesure où l'ordre de saisie n'a normalement que peu d'importance, ce n'est pas bien dramatique.

Ce serait un peu plus « fun » si vous disposiez d'un bouton de lancement sur la feuille de formulaire, de manière à pouvoir cliquer dessus pour lancer la macro. L'onglet [Développeur] expose plusieurs contrôles à placer sur une feuille, dont un bouton de commande.

Sélectionnez Bouton (Contrôle de formulaire) et placez-le sur la feuille du formulaire. Ne vous tracassez pas trop de sa taille ni de sa position, vous modifierez cela par la suite. Dès le bouton placé, une fenêtre s'ouvre pour vous permettre d'affecter une macro au clic du bouton. Sélectionnez votre macro et validez.

Par clic droit sur le bouton, modifiez son texte, déplacez-le et donnez-lui les dimensions souhaitées.

Lorsque tout est prêt, cliquez sur une cellule de la feuille. En survolant le bouton avec la souris, vous verrez une main indiquant que vous pouvez cliquer. Il suffit de remplir le formulaire, puis de cliquer sur le bouton pour que la macro s'exécute.

Après avoir réalisé cette macro déjà plus volumineuse - elle fait un « vrai » travail réutilisable -, il est nécessaire de bien baliser ce que fait l'enregistreur de macro.

Un simple regard sur le code vous permettra de comprendre une des limites de l'enregistreur :

Les noms de feuille sont inscrits « en dur » dans la macro. Normalement, cela ne doit jamais être ainsi dans un code informatique professionnel. Cette technique s'appelle le hard coding et est normalement à proscrire. En effet, la simple modification du nom d'une des feuilles rendra la macro inutilisable. Toutefois, dans le cadre de ce tutoriel, nous devrons nous en contenter.

Le hard coding continue avec les adresses des cellules en dur. Si vous insérez des lignes avant la ligne 3 de la feuille du formulaire, ou si vous insérez des colonnes avant la dernière colonne utilisée de la feuille des données, votre macro fonctionnera, mais ne récupérera plus les bonnes données et/ou ne les copiera pas dans les bonnes colonnes. En effet, un déplacement de cellules en Excel n'adapte pas le code VBA.

En clair, cela veut dire que toute modification structurelle de votre classeur vous obligera à modifier, voire réécrire entièrement la macro.

L'enregistreur enregistre tout ce que vous réalisez, il copie donc les actions de sélection tant pour les feuilles que pour les cellules. Une programmation professionnelle réaliserait les mêmes actions en beaucoup moins de lignes.

Si vous souhaitez créer une macro qui réalise x fois la même opération, le code doit être écrit x fois dans la macro. Si vous voulez l'exécuter par la suite x-y fois ou x+y fois, vous devrez adapter la macro.

En effet, l'enregistreur de macro ne permet pas de manipuler les variables. Si l'on veut qu'une partie de la macro, voire tout le code, s'exécute un certain nombre de fois, il faudra adapter le code « à la main », ce qui dépasse le cadre de ce tutoriel.

Une macro exécutée NE PEUT PAS ÊTRE ANNULÉE !!

Si vous exécutez une macro, vous ne pourrez pas en annuler les effets avec la touche d'annulation (Ctrl+Z) et la liste des actions annulables sera remise à zéro. Ne perdez jamais cela de vue.

Excel ne saurait évidemment pas mémoriser ce que vous réalisez avec la macro. Il ne saurait donc pas le détricoter. Et puisque votre macro aura placé votre classeur dans un état qu'il n'a pas mémorisé, il ne saurait pas plus vous proposer d'annuler des actions réalisées avant l'exécution de la macro.

Ce point est trop souvent oublié par les programmeurs, c'est pourquoi j'insiste ici sur cet état de fait.

Si l'on nomme une plage (mono ou multicellulaire) et qu'on la déplace, Excel adapte évidemment l'adresse de la plage. Vous pourrez utiliser cette astuce en nommant la première cellule du formulaire et en vous arrangeant pour qu'Excel mémorise que vous la sélectionnez par son nom plutôt que par son adresse.

V-A-1. Nommer la cellule▲

Excel permet d'enregistrer les déplacements relatifs. Dans la macro d'enregistrement des données du formulaire, nous étions en B3 pour copier le nom, puis nous avons voulu nous déplacer en B4 pour copier le prénom.

La ligne de code enregistrée est bien un déplacement en B4, ce qui fait que où que nous soyons dans la feuille avant l'exécution de la ligne, nous serons en B4 juste après. Mais nous pouvons également nous déplacer une ligne en dessous de la cellule DebutFormulaire. Dès lors, même si nous déplaçons le formulaire, nous nous déplacerons toujours vers les bonnes cellules.

De même, pour prévenir le déplacement de la zone de données dans laquelle vous stockerez les informations, vous pouvez nommer la première cellule d'entête de la zone de données, par exemple DebutDonnees. Vous pourrez alors l'atteindre en la sélectionnant dans la zone des noms, puis vous déplacer d'une cellule vers le bas avant d'insérer une nouvelle ligne.

Pour effectuer un déplacement relatif plutôt qu'absolu, il faut basculer l'enregistreur en mode relatif. Vous verrez comment dans les paragraphes suivants.

Notre road-book doit être modifié en conséquence. En rouge, les lignes ajoutées ou modifiées.

1. Avant de lancer l'enregistrement
   1. Remplissage de la fiche ;
   2. Sélection d'une cellule hors zone de saisie pour obliger Excel à se placer sur la bonne cellule en début de macro (dans ce cas précis, il n'est pas nécessaire de sélectionner une autre feuille).
2. PASSER EN MODE RELATIF.
3. Lancer l'enregistrement.
4. Sélectionner la cellule DebutDonnees (via la liste déroulante de la zone des noms).
5. Se déplacer d'une cellule vers le bas.
6. Sélectionner toutes les cellules de la ligne de données.
7. Par clic droit, insérer et décaler vers le bas.
8. Sélectionner la première cellule de la ligne (nous sommes en relatif, nous sélectionnons donc la cellule active (B2 si nous n'avons pas déplacé le formulaire).
9. Se placer sur la cellule DebutFormulaire (via la zone des noms).
10. Copier la cellule.
11. Sélectionner la feuille Données.
12. Coller la valeur en collage spécial valeur.
13. Se déplacer d'une cellule vers la droite.
14. Sélectionner la feuille Saisie.
15. Sélectionner la cellule B4.
16. Répéter les opérations 4 à 9 pour chaque cellule du formulaire, en sélectionnant chaque fois la bonne cellule du formulaire et la bonne cellule de la feuille de données (comme nous sommes en déplacement relatif, Excel mémorise le déplacement d'une cellule à l'autre et non l'adresse absolue de la cellule.
17. Sélectionner la plage nommée Formulaire.
18. Vider la plage avec DELETE.
19. Sélectionner la cellule B3.
20. Stopper la macro ! (Ne JAMAIS oublier cette dernière ligne. Mettez-la en dernière position dès la création de votre road-book.)
21. Repassez en mode déplacement absolu grâce au bouton de type de sélection.

Réalisez une ou deux fois ces opérations avant d'enregistrer une nouvelle macro. Si vous souhaitez que le bouton de commande active cette nouvelle macro, vous le faites par clic droit sur le bouton, puis Affecter une macro et vous choisissez votre nouvelle macro.

Notez que le code est modifié pour tenir compte des déplacements relatifs. Notez également que les déplacements ne sont pas relatifs lorsque vous sélectionnez des zones nommées. Pour bien comprendre la différence entre les déplacements absolus et relatifs, comparez les lignes surlignées avec celles du point V.B.

Vous pouvez alors tester que tout est opérationnel en déplaçant la zone du formulaire et/ou la zone de données.

Déplacez bien chaque bloc EN ENTIER, car les déplacements à l'intérieur des blocs ne permettent pas d'insérer des lignes et/ou des colonnes à l'intérieur des zones de formulaire ou de données.

V-D-1. Déplacements absolus et relatifs au sein d'une même macro▲

V-D-2. Ne sortez pas des limites▲