L’enregistreur de macro : notre meilleur ami (si nous savons l’apprivoiser…)

La vocation première de l’enregistreur de macro EXCEL est la création d’automates exécutant une succession d’actions. On pourra par la suite reproduire ces actions autant de fois que désirées.

C’est, de plus, un outil essentiel d’apprentissage de la programmation VBA : grâce à lui, nous pouvons, explorer les objets complexes exposés par EXCEL.

Cependant, il est nécessaire d’en bien comprendre les possibilités ainsi que les limites pour l’utiliser de façon optimale.

Assurons-nous de la présence de l’onglet “Développeur”

Pour accéder à l’enregistreur de macro, il est nécessaire que l’onglet « Développeur » soit présent dans le ruban EXCEL:

L'onglet développeur

Si tel n’est pas le cas, activons cette fonctionnalité dans : Fichier/Options/Personnaliser le ruban et sélectionnons la case « Développeur » :

Déclenchons l’enregistreur de macro

Pour déclencher l’enregistrement d’une macro, cliquons sur le bouton « Enregistrer une macro » dans l’onglet « Développeur » ou (Alt+v ;R1):

Enregistrer une macro

Par défaut, EXCEL propose le nom « Macro1 » (1 pour notre première macro du classeur, 2 pour la seconde macro, etc.) :

nommage d'une macro

Afin de les repérer plus facilement par la suite, il est vivement conseillé de donner un nom le plus explicite possible aux macros.

Pour le nommage des macros, EXCEL impose les règles suivantes :

  • Son nom doit commencer par une lettre (majuscule ou minuscule) ou un trait de soulignement (_).
  • Son nom ne doit comporter ni espace ni caractères spéciaux (& * / …).
  • Son nom doit être unique dans le classeur.
  • Son nom ne doit pas entrer en conflit avec un mot clé réservé (comme « Print »…).

De manière facultative, nous pouvons affecter une ‘Touche de raccourci’ à la macro. Mais, il existe d’autres moyens pour déclencher à volonté l’exécution d’une macro, par exemple :

  • Au moyen d’un clic sur un bouton (ou une image) présent sur une feuille du classeur.
  • En ajoutant une entrée référant la macro dans le ruban EXCEL.

La macro sera enregistrée dans un module VBA qui suivant le choix peut se trouver :

Nous pouvons compléter la zone « Description » : le texte que nous y insérerons se retrouvera en commentaire dans la macro générée.

 

Dès le moment où nous cliquons sur le bouton « OK » de cette boite de dialogue, toutes les actions réalisées seront enregistrées dans la macro :

  • Sélection de cellules ou plages de cellules,
  • Ecriture de caractères,
  • Insertion de formule,
  • Insertion d’objet,
  • Déclenchement d’une fonction EXCEL,
  • Etc.

 

Stoppons l’enregistreur de macro

Pour stopper l’enregistrement d’une macro, cliquons sur le bouton « Arrêter l’enregistrement » dans l’onglet « Développeur » ou (Alt+v ;R1):

Arret de l'enregistreur

 

Exerçons-nous 

A titre d’exercice, déclenchons l’enregistrement d’une nouvelle macro, nommons la ‘MaPremiereMacro’ et donnons lui la description ‘Ceci est ma première macro’. Cliquons sur OK et effectuons les actions suivantes :

Dans la cellule A1, entrons la formule permettant d’obtenir la date et l’heure actuelle : « =MAINTENANT() »
Copions la cellule dans le presse-papier (Ctrl+C)
Déplaçons la sélection sur la cellule D1
Collons le contenu du presse-papier (Ctrl+V)
Redimensionnons la cellule D1 pour visualiser la date et heure courante par un double-clic sur le quadrillage de la colonne D :

Exercice Enregistreur

Stoppons l’enregistrement de la macro

 

Pour réviser le code VBA d’une macro enregistrée, cliquons sur le bouton « Visual Basic » dans l’onglet « Développeur » ou (Alt+F11):

Ouvrir l'éditeur Visual Basic

L’éditeur de code VBA (VBE) s’ouvre dans une nouvelle fenêtre.

En sélectionnant « Modules/Module1 » nous visualisons le code VBA généré par notre exercice :

Code VBA Genere

Pour une formation à la programmation VBA, nous nous reporterons utilement au cours : https://www.excel-pratique.com/fr/vba.

Le code VBA de la macro générée devrait se présenter sensiblement comme ci-dessous :

Sub MaPremiereMacro()
'
' MaPremiereMacro Macro
' Ceci est ma première macro
'

'
1    ActiveCell.FormulaR1C1 = "=NOW()"
2    Range("A1").Select
3    Selection.Copy
4    Range("D1").Select
5    ActiveSheet.Paste
6    Columns("D:D").EntireColumn.AutoFit
End Sub
  • La description de la macro a été reproduite dans l’entête
  • Ligne 1 : Le code se réfère à « ActiveCell» pour entrer la formule.
  • Ligne 1 : La formule « MAINTENANT() » a été traduite en sa forme anglaise.
    (Un outil de traduction français/anglais des formules EXCEL est disponible ici : https://www.excel-pratique.com/fr/index_des_fonctions/traduction_formule).
  • Ligne 3 : La copie de la cellule est réalisée au moyen de la propriété « Selection ».
  • Ligne 6 : Le redimensionnement de la colonne « D » est réalisé au moyen de la fonction « Autofit ».

Améliorons le code VBA de la macro générée

Dans le code VBA généré, nous observons que :

  • Ligne 1 : dans notre exercice, nous n’avions pas explicitement sélectionné la cellule A1.
    L’enregistreur, utilisant le référencement implicite, a enregistré la propriété ‘ActiveCell’. Si nous voulons que la macro sélectionne spécifiquement la cellule “A1”, nous modifierons le code de la ligne en affectant une variable objet ‘oRange’ de la façon suivante :
    • Set oRange = ActiveSheet.Range(“A1”)
    • oRange.FormulaR1C1 = “=NOW()”
  • Ligne 2 et 3 : l’enregistreur met en œuvre la méthode « Select » puis la propriété « Selection » pour copier la cellule. Cette façon n’est pas optimale ni sur le plan de la fiabilité ni sur celui des performances. Nous préférerons faire appel aux méthodes équivalentes des objets « Range ». Ainsi nous remplacerons la ligne 3 avec :
    • oRange.copy

Même sur une séquence relativement simple, nous pouvons apporter des améliorations au code généré par l’enregistreur de macro. Par exemple, l’expérience montre que, lors de l’enregistrement de macro, nous réalisons des actions inutiles, nous tâtonnons et il nous arrive de ne pas stopper à temps l’enregistreur. Tout ceci a pour effet de générer du code VBA inutile (voire néfaste). Nous avons donc tout intérêt après utilisation de l’enregistreur de macro, à réviser le code généré pour en éliminer le code superflu et améliorer sa fiabilité et sa performance.

     

    Connaissons les limites de l’enregistreur de macro

    Indéniablement utile dans la plupart des situations, l’enregistreur de macro ne nous sera pas d’un grand secours lorsque nous utilisons les « Compléments COM ». En effet dans ce cas, il se révèlera incapable de générer le code VBA attendu.

    De plus, les macros générés par l’enregistreur de macro comportent les limitations suivantes :

    • L’enregistreur de macro ne permet pas le passage d’arguments à la macro générée. Si cela est nécessaire, il faudra modifier la macro générée. Voir l’article Microsoft Transmission efficace d’arguments.
    • L’enregistreur ne permet pas de créer complètement des fonctions personnalisées telles que décrites dans l’article Microsoft Créer des fonctions personnalisées.
    • L’enregistreur ne permet pas de créer complètement un code VBA se déclenchant lorsqu’un certain évènement se produit. Par exemple, lorsqu’une certaine cellule est modifiée dans une feuille particulière.
    • L’enregitreur de macro ne peut pas créer des boucles de type :
      • For Next
      • Do While
      • Do Until
      • For each Next
    • L’enregistreur de macro ne peut pas conditionner le déroulement de la macro par les instructions :
      • If-Then-Else
      • Select Case
    • L’enregistreur de macro ne génère aucune déclarations explicite de variables. Voir l’article VBA: L’option explicit

      Résumons les BONNES PRATIQUES à mettre en oeuvre

       

      • Utilisons l’enregistreur de macro aussi souvent que nécessaire. C’est un moyen rapide pour explorer les objets EXCEL, leur collections, méthodes, propriétés.
        Un autre moyen, encore plus rapide : le pur copier/coller de code trouvé sur le NET par exemple. Mais, dans ce cas, il est indispensable de comprendre et de s’approprier ce code. « Science sans conscience n’est que ruine de l’âme » nous enseigne Rabelais. Et n’omettons pas de citer l’auteur du code plagié. 
      • N’enregistrons pas des séquences trop longues d’actions et circonscrivons l’enregistrement à un seul sujet.
      • Révisons systématiquement le code généré.

       

      Allons vers d’autres articles “Bonnes Pratiques VBA”.