Excel et l'enregistreur de macro

Enregistrer les actions répétitives dans un fichier Excel

Pierre Fauconnier

Si vous répétez régulièrement les mêmes manipulations lors du traitement d'un fichier Excel, ce tutoriel sur l'enregistreur de macro pourrait vous intéresser. Commentez

- Titre : Excel et l'enregistreur de macro

- Auteur : Pierre Fauconnier

- Parution : 9 avril 2015

- Mise à jour : 21 avril 2015

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2015 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Introduction

- I-A. Utilité de l'enregistreur de macro

- I-B. Objectifs de ce tutoriel

- I-C. Version utilisée

- I-D. Préparation de l'application

- II. Prise en main

- II-A. Enregistrer une première macro

- II-B. Exécuter la macro enregistrée

- II-C. Quel est le code qui a été enregistré

- II-D. Enregistrement du classeur

- II-E. Conclusions de cette prise en main

- III. Un exemple concret

- III-A. Introduction

- III-B. Cahier des charges

- III-C. Mise en place du classeur

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

- III-C-1-a. Feuille de saisie

- III-C-1-b. Feuille de la table des données

- III-D. Scénarisation de la séquence

- III-D-1. Étapes du traitement

- III-E. Répétitions avant l'enregistrement

- III-F. Enregistrement

- III-G. Utilisation de la macro

- III-H. Bouton de lancement de la macro

- IV. Limites de l'enregistreur de macro

- IV-A. Structure du classeur figée

- IV-B. Code inutile

- IV-C. Code redondant

- IV-D. Annulation impossible

- V. Déplacements absolus et relatifs

- V-A. Utilisation de cellules nommées

- V-A-1. Nommer la cellule

- V-B. Déplacements relatifs.

- V-C. Nouveau road-book

- V-D. Notes complémentaires sur les déplacements

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

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

- VI. Conclusions

- VII. Remerciements

I - Introduction

I-A - Utilité de 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.

I-B - Objectifs de ce tutoriel

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.

I-C - Version utilisée

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.

I-D - Préparation de l'application

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.

II - Prise en main

II-A - Enregistrer une première 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.

II-B - Exécuter la macro enregistrée

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.

II-C - Quel est le code qui a été enregistré

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.

II-D - Enregistrement du classeur

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.

II-E - Conclusions de cette prise en main

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.

III - Un exemple concret

III-A - Introduction

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.

III-B - Cahier des charges

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.

III-C - Mise en place du classeur

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

III-C-1-a - Feuille de saisie

La feuille de saisie est préparée de manière à recevoir les données saisies. Pour ce tutoriel, vous vous baserez sur l'illustration suivante, dans laquelle la zone de saisie est colorée en vert.

Évitez comme la peste les cellules fusionnées dans ce genre de travail. Si vous voulez centrer sur plusieurs colonnes, utilisez la mise en forme adéquate. La fusion de cellules ne peut être utilisée que pour la mise en forme esthétique, mais doit être évitée à tout prix pour les cellules « de travail ».

III-C-1-b - Feuille de la table des données

Naturellement, la plage de données reprendra les mêmes données, mais en colonnes.

III-D - Scénarisation de la séquence

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

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. Lancer l'enregistrement.

3. Sélectionner la feuille du tableau.

4. Se placer en A2:H2.

5. Par clic droit, insérer et décaler vers le bas.

6. Sélectionner la feuille du formulaire.

7. Se placer sur la cellule B3.

8. Copier la cellule.

9. Sélectionner la feuille Données.

10. Se positionner en A2.

11. Coller la valeur en collage spécial valeur.

12. Sélectionner la feuille Saisie.

13. Sélectionner la cellule B4.

14. 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.

15. Sélectionner la feuille du formulaire.

16. Vider le formulaire

1. Sélectionner les cellules du formulaire ;

2. Vider les cellules du formulaire de saisie.

17. Sélectionner la cellule B3.

18. 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.)

C'est en répétant le scénario sur votre classeur que vous verrez si vous n'avez rien oublié. Par exemple :

- en pensant que la macro va être déclenchée au départ du formulaire, il faut que ce soit cette feuille qui soit sélectionnée avant de démarrer l'enregistrement ;

- si vous loupez les étapes 1 à 6, vous allez arriver avec le nom copié sur la feuille du tableau puis vous allez devoir insérer une nouvelle ligne, et vous « perdrez » donc votre nom copié.

Ce ne sont que les multiples répétitions qui vous permettront de bien reprendre TOUTES les étapes et DANS LE BON ORDRE.

III-E - Répétitions avant l'enregistrement

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 ».

III-F - Enregistrement

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.

III-G - Utilisation de la macro

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.

III-H - Bouton de lancement de la macro

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.

IV - Limites de l'enregistreur de macro

IV-A - Structure du classeur figée

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.

IV-B - Code inutile

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.

IV-C - Code redondant

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.

IV-D - Annulation impossible

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.

V - Déplacements absolus et relatifs

Malgré ces limites, Excel offre un peu plus de souplesse dans l'enregistrement du code. Vous avez vu plus haut que les adresses sont inscrites en dur dans le code. Par exemple, si je décale mon formulaire d'une ligne vers le bas, je vais devoir adapter toutes les lignes qui concernent la sélection des cellules de mon formulaire. De même, si je décale la zone de données de la feuille de données, la macro ne s'exécutera plus comme souhaité.

Dans ce qui va suivre, je vous propose d'abord d'utiliser quelques astuces pour contourner ce problème tout en ne modifiant pas le code « à la main ». Cela ajoutera un peu de souplesse à la macro.

V-A - Utilisation de cellules nommées

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

Pour nommer une cellule, il suffit de la sélectionner puis, dans la zone des noms (à gauche de la barre de formules), vous saisissez le nom souhaité et vous validez par ENTER (c'est souligné, OK ?).

Vous allez pouvoir sélectionner cette cellule plutôt que sélectionner B3. En effet, Excel offre depuis toujours (?) la possibilité d'atteindre une cellule, soit par son adresse, soit par son nom. La façon la plus simple de procéder consiste à saisir l'adresse (ou le nom) de la cellule souhaitée dans la zone des noms. Excel activera directement la cellule ou la plage renseignée. Vous pouvez également dérouler la liste des noms et sélectionner le nom de la plage que vous souhaitez atteindre. On pourra donc enregistrer l'activation de la cellule nommée DebutFormulaire plutôt que l'activation de la cellule B3. Si l'on insère des lignes ou des colonnes en haut et à gauche du formulaire, le déplacement vers DebutFormulaire activera bien la bonne cellule.

Vous pourrez procéder de la même manière pour nommer la première cellule d'entête de la zone de données, par exemple DebutDonnees. De cette manière, si la plage de données était déplacée, la macro pourrait continuer à fonctionner normalement.

Et tant qu'à faire, vous pouvez également nommer toutes les cellules de données du formulaire, de manière à pouvoir les sélectionner d'un seul coup pour les vider. Nommez cette plage Formulaire, par exemple.

V-B - Déplacements relatifs.

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.

V-C - Nouveau road-book

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 - Notes complémentaires sur les déplacements

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

Au sein d'une même macro, vous pouvez alterner entre déplacement relatif et déplacement absolu. Avant de sélectionner une cellule/plage non nommée, basculez le mode en fonction de votre besoin.

V-D-2 - Ne sortez pas des limites

Vous pouvez générer une erreur d'exécution lorsqu'un déplacement relatif vous fait sortir des limites de la feuille.

Vous êtes en B10 et vous enregistrez un code qui vous fait remonter en relatif en A1 (vous remontez donc de 9 lignes et vous vous décalez d'une colonne à gauche). Si vous exécutez ce code alors que vous êtes en A5 par exemple, Excel va vouloir remonter de 9 lignes et se déplacer d'une colonne à gauche et va donc être hors limites, avec un beau plantage à la clé.

VI - Conclusions

Après avoir lu et mis en pratique ce tutoriel, il vous reste à pratiquer et encore pratiquer pour créer des automatisations grâce à l'enregistreur de macro. Gardez toutefois les points suivants en tête lorsque vous déciderez d'automatiser une tâche dans Excel grâce à l'enregistreur :

- voyez petit. L'enregistreur n'est pas là pour créer une application robuste et professionnelle ;

- rédigez et testez votre road-book ;

- utilisez les astuces Excel telles que les cellules/plages nommées. Elles simplifieront vos actions et vos macros. L'astuce d'une plage nommée permet parfois d'éviter de sélectionner une feuille (dont le nom est écrit en dur dans la macro) qui ne serait pas reconnue après renommage ;

- réfléchissez bien avant de modifier la structure des feuilles et plages de données concernées par les macros écrites à l'aide de l'enregistreur et partez du principe que les structures créées ne peuvent pas être modifiées (en ce compris le nom des feuilles sélectionnées).

VII - Remerciements

Merci à :

- Olivier Lebeau pour sa relecture technique ;

- Claude Leloup pour la relecture orthographique.