Enregistrer une macro dans Excel - Articles TechTV

Ce sont mes notes d'émission de mon apparition sur l'épisode 33 de l'appel à l'aide sur TechTV Canada.

Enregistreur de macros VBA

Chaque copie d'Excel vendue depuis 1995 comprend l'incroyablement puissant Visual Basic pour Applications (VBA) caché derrière les cellules. Si vous avez Excel, vous avez VBA.

VBA vous permet d'automatiser tout ce que vous pouvez faire dans Excel, ainsi que de faire plus de choses qui ne sont généralement pas possibles dans Excel.

Heureusement pour nous, Microsoft a inclus un enregistreur de macros avec Excel. Cela vous permet d'écrire du code VBA sans être un programmeur. Malheureusement, l'enregistreur de macros est défectueux. Il ne produira pas de code qui fonctionnera à chaque fois. L'enregistreur de macros vous rapprochera, mais dans de nombreux cas, vous devrez corriger un peu le code pour qu'il fonctionne de manière fiable. Dans d'autres cas, vous devez savoir quand utiliser l'enregistrement relatif pour que la macro fonctionne.

Tracy Syrstad et moi avons écrit VBA et Macros Microsoft Excel 2016 afin de vous aider à comprendre les limites du code enregistré et de vous apprendre à corriger le code enregistré en quelque chose qui fonctionnera à chaque fois.

Lors de l'émission, j'ai montré le processus d'enregistrement d'une macro qui fonctionnera parfaitement si vous savez utiliser le bouton Relative Reference. Quelqu'un m'avait donné une feuille de calcul Excel avec des centaines de noms et d'adresses. Ils voulaient des étiquettes postales. Si vous avez déjà utilisé la fonctionnalité de fusion et publipostage de Microsoft Word, vous savez que vous avez besoin de chaque champ dans une nouvelle colonne de la feuille de calcul. Au lieu de cela, cette feuille de calcul particulière contenait les données dans la colonne A.

Le processus de correction d'une seule étiquette est assez fastidieux.

Commencez par le pointeur de cellule sur le nom de la colonne A.
Appuyez sur la flèche vers le bas pour accéder à l'adresse
Ctrl + x pour couper
Flèche haut, flèche droite pour passer à la colonne B à côté du nom
Ctrl + v pour coller
Flèche vers le bas deux fois, flèche gauche une fois pour se déplacer vers la ville
Ctrl + x pour couper
Flèche haut deux fois, flèche droite trice
Ctrl + v pour coller
Flèche gauche deux fois, flèche bas une fois pour passer à la ligne désormais vide.
Maintenez la touche Maj enfoncée tout en appuyant deux fois sur la flèche vers le bas
Alt + edr pour supprimer les lignes vides
Flèche haut et flèche bas pour sélectionner à nouveau uniquement le nom.

Voici l'animation montrant ces étapes:

Configurer Excel pour autoriser les macros

Bien que chaque copie d'Excel contienne VBA, par défaut, Microsoft désactive la possibilité d'exécuter des macros. Vous devez effectuer un ajustement ponctuel pour permettre aux macros de s'exécuter. Ouvrez Excel. Dans le menu, sélectionnez Outils> Macro> Sécurité. Si le niveau de sécurité de votre macro est actuellement défini sur élevé, modifiez-le sur Moyen.

Préparation de l'enregistrement de la macro

Pensez aux étapes nécessaires pour accomplir la tâche. Vous voulez vous assurer que vous commencez par le pointeur de cellule sur un nom et que vous finissez par lui sur le nom suivant. Cela vous permettra d'exécuter plusieurs fois la macro à plusieurs reprises. Lorsque les étapes sont prêtes, allumez l'enregistreur de macros. Dans le menu, sélectionnez Outils> Macro> Enregistrer une nouvelle macro.

Si vous exécutez cette macro pendant plusieurs jours, vous devez donner à la macro un nom utile. S'il s'agit d'une macro ponctuelle pour automatiser une tâche ponctuelle, laisser le nom de la macro comme Macro1 est probablement très bien. Le champ important ici est le champ de la touche de raccourci. Si vous créez une macro pour une utilisation unique, assignez la macro à une touche de raccourci telle que Ctrl + a - il est assez facile d'appuyer sur Ctrl + a à plusieurs reprises car les touches sont proches les unes des autres. Si vous envisagez de créer une macro que vous utiliserez tous les jours, vous souhaitez attribuer la macro à une touche qui n'est pas déjà une combinaison de touches de raccourci importante. Ctrl + j ou Ctrl + k sont de bons choix.

Les macros à usage unique doivent être stockées dans ThisWorkbook. Si vous devez utiliser la macro à plusieurs reprises sur de nombreux classeurs différents, vous pouvez stocker la macro dans le classeur de macros personnelles.

Vous êtes maintenant présenté avec la plus petite barre d'outils dans tout Excel; la barre d'outils Arrêter l'enregistrement. Il n'a que deux icônes et ressemble à ceci:

Si cette barre d'outils vous gêne, n'appuyez pas sur le X pour la fermer. Au lieu de cela, saisissez la barre bleue et faites glisser la barre d'outils vers un nouvel emplacement. L'action de déplacer la barre d'outils n'est pas enregistrée dans la macro. Si vous fermez accidentellement la barre d'outils, récupérez-la avec Affichage> Barres d'outils> Arrêter l'enregistrement. Cette action, cependant, sera enregistrée.

Le premier bouton de la barre d'outils est le bouton "Arrêter l'enregistrement". Ceci est explicite. Lorsque vous avez terminé l'enregistrement de la macro, appuyez sur la barre d'outils Arrêter l'enregistrement.

Le bouton le plus important (et rarement compris) est le bouton «Référence relative».

Dans notre exemple actuel, vous devez appuyer sur le bouton Référence relative avant de commencer. Si vous enregistrez une macro avec les références relatives activées, Excel enregistrera des étapes comme celle-ci:

Déplacer une cellule vers le bas
Couper la cellule actuelle
Déplacer une cellule vers le haut
Déplacer une cellule vers la droite
Pâte
etc.

Si vous enregistrez à la place la macro sans références relatives, la macro enregistrera ces étapes:

Déplacer vers la cellule A4
Couper la cellule A4
Déplacer vers la cellule A3
Déplacer vers la cellule B3
Coller dans la cellule B3
etc.

Ce serait horriblement faux - nous avons besoin de la macro pour travailler sur des cellules qui sont 1 et 2 cellules à partir du point de départ de la macro. Lorsque vous exécutez la macro encore et encore, le point de départ sera la ligne 4, la ligne 5, la ligne 6, etc. L'enregistrement de la macro sans références relatives activées aurait la macro toujours exécutée sur la ligne 3 comme point de départ.

Suivez ces étapes:

Choisissez le bouton Référence relative dans la barre d'outils Arrêter l'enregistrement
Le pointeur de cellule doit déjà être sur un nom de la colonne A.
Appuyez sur la flèche vers le bas pour accéder à l'adresse
Ctrl + x pour couper
Flèche haut, flèche droite pour passer à la colonne B à côté du nom
Ctrl + v pour coller
Flèche vers le bas deux fois, flèche gauche une fois pour se déplacer vers la ville
Ctrl + x pour couper
Flèche haut deux fois, flèche droite trice
Ctrl + v pour coller
Flèche gauche deux fois, flèche bas une fois pour passer à la ligne désormais vide.
Maintenez la touche Maj enfoncée tout en appuyant deux fois sur la flèche vers le bas
Alt + edr pour supprimer les lignes vides
Flèche vers le haut et flèche vers le bas pour sélectionner le nom suivant dans la liste.
Appuyez sur la barre d'outils Arrêter l'enregistrement
Enregistrez le classeur
Testez la macro en appuyant sur la touche de raccourci attribuée ci-dessus. Il devrait parfaitement fixer le nom suivant dans la liste

Une fois que vous voyez que la macro fonctionne comme vous le souhaitez, vous pouvez maintenir Ctrl + a pour corriger rapidement quelques noms par seconde. La liste complète de 500 noms peut être fixée en une minute ou deux.

Astuce bonus - Pas sur le salon

Vous pouvez facilement envelopper la macro dans une simple boucle afin qu'elle répare les 500 noms sans que vous ayez à appuyer sur Ctrl + plusieurs centaines de fois.

Appuyez sur la touche Alt + F11 pour ouvrir l'éditeur de macro.

Si vous ne voyez pas le volet Projet - VBAProject, appuyez sur Ctrl + r.

Cliquez avec le bouton droit sur Module1 et choisissez Afficher le code. Vous verrez du code qui ressemble à ceci:

Maintenant, vous n'avez pas à comprendre ce que fait ce code, mais vous savez que nous voulons tout exécuter dans cette macro une fois pour chaque nom que nous avons. Si vous savez qu'il y a 462 noms, vous pouvez ajouter 2 lignes afin d'exécuter le code 462 fois. En haut de la macro, insérez une nouvelle ligne avec les mots " For i = 1 to 462". Au bas de la macro, insérez une ligne qui dit " Next i". Cela indique à VBA d'exécuter le code 462 fois.

Conclusion

Après cette simple macro, j'ai montré un exemple sur le spectacle d'une macro très complexe. Cette macro a créé des tableaux croisés dynamiques et des graphiques pour 46 départements d'une entreprise. Ce rapport prenait 40 heures par mois. La macro VBA s'exécute maintenant et crée les 46 rapports en moins de 2 minutes.

Le livre VBA and Macros Microsoft Excel 2016 vous guidera dans la courbe d'apprentissage afin que vous puissiez passer de l'enregistrement de macros simples comme celle-ci à l'exécution de rapports très complexes qui peuvent vous faire gagner des centaines d'heures par an. Bien sûr, si vous ne voulez pas apprendre VBA, engagez un consultant Excel pour concevoir un rapport pour vous.