Cours / Formation Excel > Introduction aux macros d'Excel

Savoir

1. Expliquer la nature des différentes couches logicielles dans l'ordinateur ;
2. Expliquer le rôle et la nature des langages d'ordinateurs ;

Savoir faire

1. Utiliser l’enregistreur de macros d'Excel ;
2. Ajouter des commentaires dans une macro d'Excel ;
3. Exécuter une macro d'Excel
4. Réaliser une exécution pas à pas d’une macro Excel ;
5. Affecter une macro à un bouton de commande ;

Les ensembles d’instructions que l’ordinateur peut exécuter sont appelés des programmes.

Il existe plusieurs niveaux de programmes dans l’ordinateur.

Le niveau du BIOS

Le « BIOS » est un composant électronique de très petite dimension et qui contient, dans ses circuits des ensembles d’instructions qui peuvent être exécutés par le microprocesseur. Ces instructions concernent les capacités de base de la machine :

• écrire un caractère à l’écran ;
• lire ce que l’utilisateur frappe au clavier ;
• lire un octet sur la localisation X de la piste Y sur un disque ;
• ...

Le « BIOS » se charge de tout le travail de base de gestion du matériel. Quand on met l’ordinateur sous tension, c’est lui qui est actif.

Le niveau du système d’exploitation

Les fondements

Les capacités du « BIOS » sont très limitées.

Lors de la mise sous tension de la machine, il est chargé d’appeler en mémoire un programme plus évolué qui a pour mission d’organiser tout le travail de l’ordinateur : c’est le système d’exploitation.

Ce dernier est chargé d’organiser la mémoire de l’ordinateur. Il est également chargé d’organiser la façon d’utiliser les pistes et les secteurs des disques.

Le système d’exploitation communique avec le « BIOS » qu'il charge de la plupart des fonctions liées à la gestion du matériel.

Linux, MacOS ou Windows sont des systèmes d’exploitation.

Le niveau de l’interface graphique

Le système d’exploitation dispose donc des capacités d’utiliser au mieux les possibilités du matériel.

Cependant, l’utilisateur moyen souhaitera pouvoir piloter le plus facilement possible son système d’exploitation.

Comment faire pour lui demander d’effacer un fichier qui porte tel nom ? Comment lui faire renommer tel fichier avec tel nouveau nom ? Comment obtenir la liste des fichiers dans tel répertoire ?

Dans le système d’exploitation DOS, on disposait de commandes à frapper au clavier pour chacune de ces actions. La méthode est efficace, mais elle demande de retenir des noms de commandes plutôt rébarbatifs : del *.txt, ren fichierA.doc fichier2.doc, dir c:abc?.*, …

Le niveau du logiciel d’application

Les différents logiciels d’applications (Excel, Word,...) qui fonctionnent sous Windows constituent la couche logicielle la plus élevée pour la plupart des utilisateurs.

• Lorsque l’on travaille avec un tableur comme Excel, on communique avec ce programme.
• Celui-ci communique à son tour avec l'interface graphique qui, elle-même, est en relation avec les couches profondes du système d’exploitation.
• Ces dernières entrent en contact avec le « BIOS » lorsqu’elles doivent utiliser le matériel.

Questions

Visualisation des couches logicielles

Une couche supplémentaire

Nous allons voir ici que nous pouvons encore ajouter une couche logicielle supplémentaire, au-dessus du logiciel d'application. Celle-ci servira donc à piloter la couche sous-jacente constituée par Word, Excel et autres.

Vérifie que le logiciel Excel est démarré et qu'une feuille de calcul vierge est disponible.

Tu vas exécuter quelques opérations simples qui vont être « enregistrées » par Excel. Les commandes enregistrées pourront être « rejouées » plus tard. Nous aborderons ainsi la notion de macro-instruction.

Créer une macro pour changer la feuille active

Dans le menu « Outils », sélectionne la commande « Macro » et l’option « Nouvelle macro » dans le sous-menu.

Une boîte de dialogue apparaît et invite à donner un nom à la macro. Observe la boîte de dialogue quelques instants sans rien y modifier et accepte les paramètres par défaut en cliquant sur le bouton OK.

Immédiatement, l’enregistreur de macros s’active ; une petite barre d’outils ne comprenant que deux boutons apparaît à l’écran. La mention Enregistrement est indiquée dans la barre d’état, en bas de la fenêtre. Le bouton permettra d’arrêter l’enregistrement ; Le bouton permettra de basculer le type de référence : relative ou absolue.

L’objectif des commandes en cours d’enregistrement est tout simplement de permettre de changer de feuille active. Pour cela, clique sur l’onglet de la feuille nommée « Feuil2 » .

Arrête ensuite l’enregistrement de la macro en cliquant sur le bouton .

Super, cet enregistreur... mais moi je ne vois pas ce qui s'est passé. D'ailleurs, s'est-il même passé quelque chose? J'ai un doute.

Ce doute est bien compréhensible: tout cela n'est pas très spectaculaire. Mais l'enregistrement a bien eu lieu si tu as fait ce qui était demandé. Veux-tu que nous allions voir où se trouve la commande enregistrée?

Cool.

Dans le menu Outils, sélectionne la commande Macro et chosisi l’option Macros ou utilise le raccourci clavier alt+F8

Une fenêtre semblable à la suivante apparaît :

Sélectionne la macro que tu viens d’enregistrer (en principe, elle devrait s’appeler Macro1) et clique sur le bouton Modifier

La fenêtre de l’éditeur de code Visual Basic apparaît.

La fenêtre de l'éditeur de code se présente, par défaut, en deux parties:

Bon d'accord, mais tout ça ne me dit toujours pas ce que signfie tout le charabia qui constitue ce fameux "code".

Fort exact. Je te propose de résoudre cette question à l'étape suivante.

Il nous suffit maintenant de considérer le code.

Le texte de la macro commence par le mot réservé « Sub » suivi du nom de la macro et d’une paire de parenthèses.

Il se termine par l’expression « End Sub ».

Le mot Sub est l’abréviation de Subroutine (« sous-routine », en français) : en informatique, une « routine » est une portion de code nécessaire à la réalisation d’une tâche.

Une routine peut être subdivisée en « sous-routines » qui réalisent chacune une partie de la tâche à effectuer.

Le texte écrit en couleur verte et précédé d'apostrophes n'est que du commentaire qui sera éventuellement utile aux personnes qui liront ce code. Pour l'ordinateur, il ne sert à rien.

Ce sont les apostrophes qui signalent au logiciel qu'il ne doit pas chercher à interpréter ce qui y est indiqué.

A n'importe quel endroit du texte de la macro, ajoute une ligne de commentaire contenant ton prénom, ton nom et ton numéro de téléphone.

Il n'est pas obligatoire que ce commentaire figure parmi les autres lignes de commentaires. Il suffit qu'il soit précédé d'une apostrophe.

La couleur verte est appliqué automatiquement quand la frappe de la ligne est terminée et que le curseur de texte est déplacé vers une autre ligne.

La lecture de la seule ligne de "code" est relativement parlante dès que l'on connaît un peu d'anglais.

• Il est donc question de "Sheets"; traduction "Feuilles". "Feuilles de calcul", bien sûr.
• "Feuil2" est précisément le nom de la feuille de calcul que nous voulons sélectionner.
• L'action précisée "Select" est, on ne peut plus claire.

S'il fallait traduire cela en français, on dirait:

Sélectionner la Feuil2 parmi les feuilles de calcul

Une telle instruction élémentaire est l'unité de base de ce que l'on peut demander de faire au logiciel. A son sujet, on pourrait donc parler d'une micro-instruction.

Ouais, ouais, c'est vrai que c'est presque limpide quand on le voit comme ça. Mais ce serait quand même plus simple si c'était écrit en français plutôt que dans ce langage imbuvable.

Bien sûr, c'est plus simple en français pour nous! Mais ce texte est conçu pour être lu par un ordinateur qui est, comme chacun le sait, incapable de comprendre le langage naturel.
Je propose de détailler ce point à l'étape suivante.

Le langage machine

Les seules informations que puisse stocker un ordinateur sont des séries de 0 et de 1. Les données qu’il peut traiter doivent donc être codées sous cette forme.

De même, les instructions destinées aux ordinateurs sont formées d’ensembles de 0 et de 1, compréhensibles par le processeur. Un programme d’ordinateur se présente donc comme sur l’illustration ci-dessous :

00110110 11010101 10100011 00111001 1100110001 10101100 11000011
00100101 01011010 01110110 11011010 1101101110 10101101 01101101

Ce sont des instructions, ça? Je n'y comprends rien moi!

Tout à fait normal. Il s'agit de la langue "maternelle" des ordinateurs. Nous ne travaillons pas comme des ordinateurs. Chacun trouvera donc cela difficile à comprendre. Un ordinateur, par contre, s’y retrouve très bien.

Les premiers ordinateurs se programmaient exclusivement dans ce langage : le langage binaire encore appelé langage machine.

Pour des raisons de facilité, on utilise généralement une variante du langage binaire dans laquelle les instructions sont codées en hexadécimal (notation en base 16). A titre informatif, tu trouveras ci-contre, un convertisseur hexadécimal/décimal. Essaie de convertir tous les nombres décimaux, entre 8 et 17, en hexadécimal. Les nombres hexadécimaux sont souvent présentés avec 4 chiffres, en informatique. Le convertisseur fonctionne dans les deux sens.

Décimal Hexadécimal

L’assembleur et les langages évolués

On a ensuite trouvé plus facile de composer les programmes dans des langages plus simples à comprendre pour l’être humain.

Exemples : un programme qui écrit « Salut tout le monde » à l’écran

Le langage Visual Basic

Parmi les langages évolués, le langage BASIC ("Beginners All-purpose Symbolic Instruction Code") est extrêmement populaire: relativement simple à étudier, il est relativement puissant. En quelques minutes, chacun est capable d'écrire un programme en BASIC.

Parmi les variantes de ce langage, une version développée par la société Microsoft est appelée "Visual Basic". L'adjectif Visual provenant du fait que l'interface graphique intervient pour beaucoup dans la construction du code. Visual Basic permet d'écrire des logiciels fort utiles dans de nombreux domaines.

Ce langage a ensuite été adapté dans le but de piloter les logiciels d'applications comme Word ou Excel. Les instructions codées par l'enregistreur de macros sont rédigées dans une variante de Visual Basic uniquement destinée à piloter les logiciels Microsoft - Office. On parle alors de Visual Basic pour Applications (acronyme VBA).

C'est bien de VBA dont il est question dans cette leçon.

Bon, tout cela est très instructif, mais ça nous éloigne du travail effectué précédemment. Que devient notre "macro" enregistrée, là-dedans?

Et bien, je propose que nous y revenions et que nous fassions un peu travailler l'ordinateur, maintenant.

Nous allons "exécuter" la macro qui permet de changer de feuille de calcul.

• Retourne à la première feuille du classeur (Feuil1).
• Dans le menu Outils, sélectionne la commande Macro. La boîte de dialogue des macros apparaît.

On peut réaliser une exécution complète ou une exécution pas à pas.

• Sélectionne le nom de la macro dans la liste et clique sur le bouton Exécuter. La macro est exécutée en une fois. C'est maintenant la feuille Feuil2 qui est active.

Il est souvent instructif d'exécuter une macro pas-à-pas, principalement quand elle est constitué de plusieurs instructions. Voyons comment on procède.

• Sélectionne le nom de la macro dans la liste et clique sur le bouton Pas à pas détaillé. La fenêtre de l’éditeur de code VBA apparaît.

Dans cette fenêtre, on distingue :

• une flèche jaune dans la marge, à hauteur du titre de la macro active,
• le curseur sur la ligne de titre,
• le surlignement en jaune de cette ligne.

Fais apparaître la barre d’outils débogage par la commande Affichage > Barres d’outils > Débogage.

Pour obtenir l’exécution pas à pas de la macro, clique sur le bouton d’exécution Pas à pas détaillé de la barre d’outils.

Le surlignement et la flèche passent sur chacune des lignes de la macro au fur et à mesure de leur exécution.

Cool, les macros. Mais que c'est lourd!! On a plus vite fait de le faire soi-même que de laisser le travail à une macro.

Exact: dans l'état actuel des choses, les macros ne sont pas très utiles. Voyons comment on peut simplifier les choses.

Une nouvelle macro, un peu plus évoluée

Nous allons commencer par composer une nouvelle macro, un peu plus évoluée que la première.

A l’aide de l’enregistreur, établis une macro capable de:

• écrire l'intitulé : « Les résultats des ventes du mois » dans la cellule C3 de la feuille de calcul active.
• en gras, en italiques et en couleur rouge.

Cette macro porte le nom « CréerTitre ».

Quand cette macro est enregistrée, retrouve-la dans l'éditeur VBA. Il n'est pas nécessaire que tu comprennes parfaitement le texte de la macro. Tente cependant d'y retrouver des éléments connus.

Teste-la ensuite en exécution directe et en mode pas-à-pas détaillé.

Affectation de la macro à un bouton de commande

Nous allons diposer un bouton de commande sur la feuille de calcul. L'action de ce bouton va provoquer l'exécution de la macro.

Placer un nouveau bouton

• Reviens à la première feuille de calcul du classeur actif.
• Fais apparaître la barre d'outils Boîte à outils Contrôles (menu Affichage > Barres d'outils)
• Sélectionne l'outil Bouton de commande
• Par un cliquer/glisser, dessine un bouton de commande dans la partie supérieure gauche de la feuille de calcul.

• Clique sur le bouton Désactiver le mode création de la barre d'outils Contrôles.

Tu disposes d'un bouton parfaitement fonctionnel... mais qui ne fait rien.

Lier une macro au bouton

• Réactive le mode création en cliquant sur le bouton .
• Sélectionne le bouton par un clic de souris.
• Clique sur le bouton Visualiser le code de la barre d'outils.

L'éditeur de code VBA apparaît.

• Complète le texte de la sous-routine CommandButton1_Click() qui a été préparée comme indiqué ci-dessus.
• Retourne à la feuille de calcul où se trouve le bouton de commande.
• Désactive le mode création en cliquant sur le bouton .
• Vérifie que la cellule C3 est bien vide.
• Clique sur le bouton. C'est magique!

Tu es employé(e) dans l’administration d’une petite chaîne de magasins appelée « Le P’tit Maga » implantée dans plusieurs villes de Belgique.

Chacune des filiales a fait parvenir les résultats de ses ventes pour les quatre trimestres de l’année précédente. Ces résultats sont disponibles dans la feuille de calcul ptitmaga.xls que tu peux télécharger en cliquant sur le nom du fichier (clic droit/Enregistrer la cible sous).

Ce classeur doit être totalement réorganisé selon les indications suivantes:

• La première page du classeur présentera une carte de Belgique sur laquelle on précisera les localisations des filiales. Cette carte peut être téléchargée en cliquant sur le lien suivant (clic droit/Enregistrer la cible sous).
• Chacun des tableaux de résultats figure sur une feuille séparée dans le classeur.
• Chaque feuille porte le nom de la ville correspondante.

Afin de rendre la consultation des résultats plus simple, tu proposes d’ajouter des boutons de commande sur la carte géographique de Belgique. Chaque bouton représentera une implantation de la société.

Avec la carte géographique , on doit pouvoir passer à la page d’une ville en cliquant sur un bouton situé sur la carte.

Sur chacune des pages, un bouton de commande permet de revenir à la page de la carte de géographie.

Il est possible de voir l'enregistrement de la macro s'opérer en direct.

Préparation du terrain

• Si nécessaire, ouvre un classeur qui contient une macro enregistrée.
• Ouvre l'éditeur de macros et visualise le texte d'une macro existante.
• Réduis toutes les fenêtres sauf la fenêtre d'Excel et la fenêtre de l'éditeur de macros.
• Clique droit dans la barre des tâches, en bas de l'écran et sélectionne la commande Mosaïque verticale.

Tu dois disposer maintenant à l'écran des deux fenêtres placées côte à côte : la fenêtre de la feuille de calcul d'Excel et la fenêtre de l'éditeur de macros, comme illustré ci-dessous.

Nous allons pouvoir examiner le contenu de la fenêtre de code pendant l'enregistrement d'une macro.

Enregistrement de la nouvelle macro

• Si nécessaire, efface le contenu de la feuille de calcul présentée dans la fenêtre Excel.

Nous allons enregistrer une macro qui écrira tous les nombres compris entre 1 et 10 dans les 10 premières cellules de la première colonne.

Pour cela, il suffit d'écrire les deux premiers nombres (1 et 2) dans les deux premières cellules (A1 et A2). On sélectionne ces deux cellules. Ensuite, en utilisant la poignée de recopie, on complète la série sur les dix premières cellules.

Fastouche, c'est la manoeuvre classique pour obtenir une série continue, quoi?

Classique de chez classique. C'est vrai.

La manoeuvre en cours est illustrée ci-contre à droite.

• Sélectionne la cellule A1.
• Démarre ensuite l'enregistreur de macros. Celle-ci s'appellera UnaDix.

Durant la suite de la procédure, tiens la fenêtre de l'éditeur de macros à l'oeil.

• Entre la valeur 1 dans la cellule A1 puis frappe Enter.
• Entre la valeur 2 dans la cellule A2.
• Sélectionne les cellules A1 et A2.
• A l'aide de la poignée de recopie, complète la série jusqu'à la valeur 10.
• Arrête l'enregistrement.

Dans la fenêtre de l'éditeur de macros, plusieurs lignes -que tu as vues s'écrire- constituent l'ensemble des instructions nécesaires à la réalisation de la tâche.

Exécution de la macro

• Dans la fenêtre d'Excel, efface la série de dix valeurs des cellules A1:A10.
• Replace le curseur dans la cellule A1.
• Dans la fenêtre de l'éditeur de macros, fait apparaître la barre d'outils Débogage si elle n'est pas visible.
• Positionne le curseur dans le texte de la macro UnaDix que tu viens de créer.
• Exécute la macro pas à pas en cliquant plusieurs fois sur le bouton .

La série de nombres de 1 à 10 se remet en place.

• Efface à nouveau la série de nombres
• Exécute la macro en une fois en cliquant sur la bouton .

C'est magique, dites!

Bah, c'est informatique. C'est un peu la même chose.
Veux-tu que nous tentions une nouvelle expérience?

• Sélectionne la cellule B3
• Exécute la macro en cliquant sur le bouton .

He laaaaaaa, qu'est-ce qui se passe maintenant??? Excel est devenu fou? C'est quoi cette histoire?

Toutes mes félicitations: tu viens d'assister à ton premier bug en direct.

Examine quelques instants le texte de la macro et tente de trouver l'origine du problème qui se pose. Exécute éventuellement la macro pas à pas si tu n'entrevois pas la raison du comportement particulier d'Excel.

Le problème mis en évidence à l'étape précédente est que l'enregistrement s'est fait en références absolues.
Lors de l'enregistrement, tu as frappé la valeur 1 dans la cellule A1. Ensuite, tu as frappé Enter. Ce qui a déplacé la sélection vers la cellule A2. Cela se marque, dans le texte de la macro, par l'instruction:

Range("A2").Select

En réalité, nous voulions sélectioner la cellule située sous la première de la série et pas forcément la cellule A2.

Nous allons résoudre le problème en ré-enregistrant la macro en Références relatives.

Enregistrement de la macro

• Si nécessaire, efface le contenu de la feuille de calcul présentée dans la fenêtre Excel.
• Sélectionne la cellule A1.
• Démarre ensuite l'enregistreur de macros. Celle-ci s'appellera UnaDixBis.
• Dès le démarrage de l'enregistrement, clique sur le bouton qui permet de passer en mode Références relatives.
• Entre la valeur 1 dans la cellule A1 puis frappe Enter.

Remarque que le texte de la macro UnaDixBis est différent du texte de la macro UnaDix.

• Entre la valeur 2 dans la cellule A2.
• Sélectionne les cellules A1 et A2.
• A l'aide de la poignée de recopie, complète la série jusqu'à la valeur 10.
• Arrête l'enregistrement.

Tu peux maintenant tester la macro à partir de n'importe quelle cellule de la feuille de calcul.

La "sous-routine" obtenue précédemment est constituée d'un certain nombre de "micro-instructions" (tel que le concept a été défini précédemment).

La version "Références absolues" présente un ensemble de 6 micro-instructions successives pour réaliser la tâche demandée.

Un tel ensemble de micro-instructions peut être exécuté en une seule fois, sans point d'arrêt entre chacune des instructions composantes. Tout se passe comme si cet ensemble ne constituait plus qu'une seule et unique instruction appelée UnaDix (par exemple).

Cette instruction unique UnaDix peut d'ailleurs être affectée à un bouton.

• Affecte la sous-routine UnaDix à un bouton de commande.

Une telle instruction composée, formée de plusieurs micro-instructions est appelée macro-instruction ou encore, plus simplement, une macro.