Bureautique

Accueil > Bureautique > Cours Excel > Macros Excel

Macros Excel

Tu dois devenir capable de

Savoir

Expliquer la notion d'objet de la programmation à partir d'un objet de la vie courante
distinguer les propriétés et les méthodes des objets
distinguer les paramètres et les arguments d'une méthode

Savoir faire

reconnaître les objets, leurs propriétés et leurs méthodes dans une macro enregistrée par Excel
reconnaître les méthodes simples et les méthodes avec paramètres en distinguant les arguments de la méthode
modifier une macro enregistrée par Excel
rédiger des macros complexes par agrégation de macros simples

Notion d'objet en programmation: première approche

Notion d'objet dans la vie courante

Pour expliquer la nature d'un « objet » dans le cadre de la programmation des ordinateurs, il est habituel d'établir une comparaison avec un objet utilitaire de la vie courante.

Une automobile est un bon exemple d'objet. Elle dispose de :

un certain nombre de propriétés: couleur de la carrosserie, nombre de places, capacité du réservoir, vitesse actuelle, nombre effectif de passagers, nombre de litres de carburant dans le réservoir, ...
un certain nombre de méthodes, d'actions possibles : démarrer, freiner, tourner, débrayer ,changer de vitesse,...

Notion d'Objet en programmation: du déjà-vu

Nous avons déjà eu l'occasion de rencontrer des objets dans le cadre de la leçon précédente.

Retrouvons-les, par exemple, dans la procédure qui permet d'écrire une série continue de nombres entre 1 et 10 (voir à la leçon précédente):

Ce petit fragment de code nous fait déjà découvrir quelques objets, caractérisés ici par l'une de leurs propriétés ou l'une de leurs méthodes :

Objet	Propriété	Méthode
ActiveCell	FormulaR1C1
Range('A2')		Select
Selection		Autofill

Détaillons ce qui se produit dans cette procédure dans le cadre du modèle Objet

ligne	Explication
1	la propriété FormulaR1C1 de l'objet ActiveCell reçoit la valeur '1'.
2	on actionne la méthode Select de l'objet Range('A2').
3	la propriété FormulaR1C1 de l'objet ActiveCell reçoit la valeur '2' (la cellule active a été changée à l'étape 2).
4	on actionne la méthode Select de l'objet Range('A1:A2')
5	on actionne la méthode AutoFill de l'objet Selection (le détail de l'opération sera étudié plus loin)

Dans un autre exercice de la leçon précédente, on trouvait la ligne

Sheets('Feuil2').Select

Peux-tu la commenter dans le cadre du modèle Objet, comme dans le tableau ci-dessus? Quel est l'objet? Y a-t-il une méthode? Y a-t-il une propriété? Lesquelles?

Dans le cadre de la programmation par objets en Excel, on indique

d'abord le nom de l'objet sur lequel on va travailler
un point, qui sert de séparateur
ensuite le nom de la propriété que l'on veut utiliser ou de la méthode que l'on veut mettre en action

Quand tu as bien compris la notion d'objet de la programmation, passe à l'étape suivante.

Les collections d'objets

A de nombreuses reprises, dans le logiciel Excel, les objets manipulés viennent en groupes. On parle alors de Collections.

Une collection s'utilise telle quelle ou après extraction de ses composants.

Si un jeu de cartes est un objet, on peut l'utliser dans son entier en utilisant les méthodes qui lui sont propres

mélanger, distribuer

ou évaluer l'un de ses composants et ses propriétés, activer l'une de ses méthodes

valeur et couleur de la 12ème carte du jeu
mettre en jeu

Utilisation de la collection dans son entier

Nous avons déjà rencontré la collection Sheets qui, comme son nom l'indique en anglais, représente l'ensemble des feuilles (de calcul) présent dans le classeur.

La collection Sheets est elle-même un objet qui dispose donc de propriétés et de méthodes.

Sheets.PrintOut

par exemple, permet d'imprimer toutes les feuilles (de calcul) du classeur.

Extraction d'un élément de la collection

Dans les exemples de la leçon précédente, nous avons sélectionné un élément précis de la collection Sheets

Sheets('Feuil2').Select

De la collection Sheets, nous extrayons le deuxième objet qui porte le nom 'Feuil2'. Cet objet de la collection possède une méthode Select qui est mise en action.

Un objet peut être extrait d'une collection en fournissant son nom dans la collection. La syntaxe est du type suivant:

JeuDeCarte('Roi de pique').Jouer

On peut aussi extraire un élément d'une collection en indiquant son numéro d'ordre dans la collection

JeuDeCarte(8).Distribuer

Distribuer la 8ème carte à un joueur

Sheets(1).Select

Sélectionner la première feuille (de calculs) de la série.

A retenir!

Les collections d'objets:

sont des objets disposant donc de propriétés et de méthodes
se reconnaissent au 's' pluriel de leur nom (Sheets, WorkBooks, Files,...)
peuvent être utilisées en tant qu'objets
contiennent un groupe ordonné de (sous-)objets
peuvent exposer leurs composants; ceux-ci sont extraits en indiquant leur nom ou leur numéro d'ordre dans la collection.

Quand tu as bien en tête les caractéristiques des collections d'objets, passe à l'étape suivante.

Des objets formés d'objets

Un objet formé d'objet: l'automobile

L'exemple de l'objet automobile peut nous permettre d'introduire l'idée qu'un objet peut, lui-même, être composé de sous-objets.

Comment faire pour obtenir de la musique en roulant? Allumer l'auto-radio!!

La commande sera probablement:

Automobile.Radio.Allumer

Plus fort la musique!!


              Automobile.Radio.NiveauSonore = 10

L'automobile contient donc un objet Radio qui dispose de ses propres méthodes et propriétés.

Un objet formé d'objets dans Excel

Dans les exemples de la leçon précédente, nous avons rencontré des instructions du type


              Selection.Font.Italic = True

Il y est donc question de l'objet Selection qui possède une propriété Font.
Cette propriété Font est elle-même un objet qui possède (entre-autres), la propriété Italic. Dans ce cas-ci, cette propriété reçoit la valeur True (vrai).

Quand tu es capable de reconnaître la syntaxe d'un objet formé d'objets, passe à l'étape suivante.

Les méthodes et leurs paramètres

Les méthodes simples

On peut qualifier de 'simples', certaines méthodes appartenant à des objets.

Dans une automobile, on aurait, par exemple:

Automobile.DémarrerMoteur Automobile.OuvrirCapot

Dans le tableur Excel, on aurait, par exemple:

Sheets('Feuil2').Select Selection.Copy Range('A1').Justify

Dans chacun de ces cas, il n'est pas nécessaire de donner plus d'informations pour que l'action correspondant à la méthode puisse s'effectuer.

Les méthodes avec paramètres: cas de l'automobile

Certaines méthodes ne peuvent être mises en oeuvre sans informations complémentaires:

Prendre du carburant

Automobile.PrendreCarburant

Cette méthode demande au moins deux informations complémentaires:
- le type de carburant (Diesel, Super, LPG, ...)
- le nombre de litres de carburant

Pour être complet, il faudrait donc utiliser la méthode avec une syntaxe du type:

Automobile.PrendreCarburant TypeCarburant:='Diesel', NombreLitres:=20

Tourner

Automobile.Tourner

Cette méthode demande une information complémentaire: la direction

Pour être complet, il faudrait donc utiliser la méthode avec une syntaxe du type

Automobile.Tourner
                Direction:='Gauche'

On pourrait imaginer des cas où le nombre d'informations complémentaires serait plus grand.

Ici, chaque information complémentaire nécessaire est désignée par un nom: nous trouvons, par exemple, TypeCarburant, NombreLitres, Direction, ...

Nous appellerons PARAMETRES de la méthode, ces informations complémentaires à fournir aux méthodes.

Quand nous savons quelles informations complémentaires doivent être fournies, il nous reste à préciser la valeur que prendront ces informations.

Nous appellerons ARGUMENT, la valeur effective de chacune de ces informations complémentaires à fournir aux méthodes.

Dans nos exemples, 'Diesel' est l'argument du paramètre 'TypeCarburant', 20 est l'argument du paramètre 'NombreLitres',...

- En gros, on peut dire que le paramètre, c'est la bouteille et l'argument, c'est le vin qui est dedans? La bouteille s'appelle toujours 'bouteille', mais on peut y mettre de la piquette ou un Grand Cru...

- En gros, on pourrait dire cela. Bien que la différence entre ces deux notions ne soit pas toujours définie de cette manière. C'est pourtant la nuance, fort utile, que nous mettrons dans le cadre de ce cours.

Les méthodes avec paramètres dans Excel

Dans une étape précédente de cette leçon, nous avons déjà rencontré l'utilisation de paramètres avec des méthodes d'objets d'Excel. Dans la procédure qui remplit automatiquement les cellules de la zone A1:A10 avec les valeurs successives comprises entre 1 et 10, nous trouvons l'instruction

Selection.Autofill Destination:=Range('A1:A10'), Type:=xlFillDefault

On voit donc que la méthode Autofill demande deux paramètres:

Destination: quelles sont les cellules qui doivent être remplies
Type: une indication sur la façon de les remplir

Les deux paramètres reçoivent chacun leur argument:

Destination reçoit Range('A1:A10')
Type reçoit xlFillDefault

Destination:=Range('A1:A10')
Type:=xlFillDefault

- Et donc, à première vue, le symbole ':=' signifierait 'reçoit'.

- A première vue, oui. A seconde vue aussi, d'ailleurs. C'est tout à fait cela.
Note cependant que lorsqu'une propriété reçoit une valeur, on utilise alors tout simplement le signe '='.

Quand tu distingues bien l'affectation d'une valeur à une propriété et l'attribution d'un argument à un paramètre, passe à l'étape suivante.

Les méthodes et leurs paramètres: questions

Quand tu as répondu aux questions et bien compris les réponses, passe à l'étape suivante.

Les blocs 'With'

Dans les exemples de macros écrites dans la leçon précédente, on trouve des blocs du type suivant:


                With Selection.Font

          .Name = 'Arial'

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ColorIndex = xlAutomatic

        End With

Il s'agit d'une méthode simplifiée pour assigner une valeur aux propriétés d'un objet.

Il faut ici modifier plusieurs propriétés de l'objet Font.

On aurait aussi pu écrire


                Selection.Font.Name
                    = 'Arial'

Selection.Font.Size = 10

Selection.Font.Strikethrough = False

Selection.Font.Superscript = False

Selection.Font.Subscript = False

Selection.Font.OutlineFont = False

Selection.Font.Shadow = False

Selection.Font.Underline = xlUnderlineStyleNone

Selection.Font.ColorIndex = xlAutomatic

Ce qui est, chacun en conviendra, nettement plus lourd et pas forcément plus facile à comprendre.

Un bloc With se termine toujours par une balise End With.

Quand tu as compris l'intérêt des blocs With, passe à l'étape suivante.

Etablissement d'une macro complexe: stratégie

Diviser (la tâche) pour rêgner

Nous allons enfin utiliser toutes les notions apprises durant la leçon. Notre but sera double:

rationnaliser les listes d'instructions composées par l'enregistreur.
écrire une macro qui réalise un travail complexe

Tu es employé(e) au service commercial d'une petite société qui distribue des crèmes glacées.

Trois magasins envoient, chaque semaine, les résultats de leurs ventes sous le forme d'un fichier 'texte'. On y trouve les résultats des ventes, exprimés en litres de crèmes glacées vendues:

Fraise	Chocolat	Framboise	Vanille
10	15	12	13

Ne commence pas immédiatement à travailler sur le problème! Lis d'abord toutes les explications sur le but final et sur les différentes techniques à mettre en oeuvre!!

Le but final du travail sera de produire un graphique des ventes en fonction des parfums.

La tâche est évidemment trop complexe pour être réalisée en une seule étape. Nous allons utiliser la stratégie 'Diviser pour régner' afin de le réaliser sans grande difficulté.

- 'Divide ut imperes'... Mais c'est machiavélique!

- Quelle culture, cher élève! Quelle culture!

Récupérer les trois fichiers 'txt' dans Excel.
Glisser les 3 feuilles de calcul dans le même classeur.
Extraire les données de chacune des trois feuilles vers une feuille de calcul unique.
Supprimer les feuilles de calcul temporaires.
Réaliser le graphique

Si tu as compris ta mission et que tu l'acceptes, passe à l'étape suivante (ce message ne s'auto-détruira pas dans les 5 secondes).

Récupérer les trois fichiers 'txt' dans Excel

L'importation des fichiers 'txt' ne pose pas de problème particulier.

Nous allons enregistrer une macro qui permet de récupérer le premier fichier 'cl1.txt'. Ensuite, en nous inspirant des instructions élaborées par l'enregistreur, il est facile d'élaborer la macro qui récupère les deux autres feuilles.

Tu peux te procurer les trois fichiers correspondant aux ventes de la semaine passée ICI, ICI et ICI (clic droit / Enregistrer la cible sous). Enregistre les trois fichiers dans ton répertoire personnel.

D'abord, je fais moi-même...

Réalise l'opération d'importation du fichier cl1.txt dans le logiciel Excel.

Dans le menu Fichier d'Excel, sélectionne la commande Ouvrir
Déplace-toi jusqu'à ton répertoire personnel, si nécessaire
Dans la boîte de dialogue, sélectionne les Fichiers texte (*.prn; *.txt; *.csv)

Sélectionne le fichier cl1.txt

Un fichier txt n'étant pas à la norme d'Excel, une boîte de dialogue Assistant d'importation apparaît.

Accepte les options proposées en cliquant sur le bouton Suivant pour chacune des trois étapes.
Clique finalement sur le bouton Terminer.

Tu disposes maintenant d'un classeur contenant une seule feuille de calculs intitulée cl1.

ensuite, j'apprends au logiciel à le faire!

Quand tu as bien compris la méthode, enregistre la procédure d'ouverture du fichier cl1.txt sous le nom ImporterTxt.

Quand la macro est enregistrée, rends-toi à l'éditeur VBA afin de découvrir le texte de l'instruction qui permet cette importation.

Dans le texte, tu découvres que le nom du fichier cl1.txt intervient.

Quelle modification faudrait-il apporter au texte de cette macro pour qu'elle importe le fichier cl2.txt ou cl3.txt ?

Comment pourrait-on faire pour que la macro ouvre les trois fichiers l'un après l'autre ?

Quand ta macro ouvre correctement les trois fichiers, passe à l'étape suivante.

Glisser les 3 feuilles de calcul dans le même classeur

Pour pouvoir traiter les chiffres et établir le graphique, il faut que toutes les informations figurent dans le même classeur. Or, nous avons, pour l'instant, trois classeurs différents.

Nous allons faire glisser les trois feuilles de calcul cl1, cl2 et cl3 vers notre classeur principal (celui qui contient la macro).

Réalise d'abord l'opération sans enregistrer. Quand tu auras compris la méthode, enregistre cette opération dans une macro intitulée GlisserTxt.

D'abord, je fais moi-même...

Dispose les classeurs en mosaïque dans la fenêtre Excel Fenêtre > Réorganiser > Mosaïque
Active le classeur contenant le fichier cl1.txt.
Clique sur l'onglet cl1 de la feuille importée.
Par un cliquer/glisser, déplace cet onglet avant la feuille nommée Feuil1 de la feuille de calcul principale.

Le classeur contenant la feuille cl1 se ferme. L'importation de cl1 est terminée.

ensuite, j'apprends au logiciel à le faire!

Active la feuille de calcul principale (et non les classeurs cl1, cl2 ou cl3).
Démarre l'enregistreur de macros.
Active le classeur contenant le fichier cl1.txt.
Clique sur l'onglet cl1 de la première feuille importée.
Par un cliquer/glisser, déplace cet onglet avant la feuille nommée Feuil1 de la feuille de calcul principale.
Arrête l'enregistrement sans traiter les feuilles cl2 et cl3!

Retournons immdiatement voir le texte de la macro composée dans l'éditeur VBA.

Que faut-il encore faire pour que la macro permette d'incorporer les deux autres feuilles importées? Inspire-toi du texte de la première macro et adapte-le en conséquence.

Quand la macro incorpore correctement les trois classeurs importés, passe à l'étape suivante.

Extraire les données de chacune des trois feuilles vers une feuille de calcul unique

Nous allons déplacer toutes les informations disposées dans les trois feuilles cl1, cl2 et cl3 vers la feuille Feuil1 de notre classeur.

Comme pour les étapes précédentes, entraîne-toi d'abord à faire l'opération manuellement avant d'enregistrer la macro.

Nous allons d'abord créer une macro pour récupérer les titres de la page. L'opération manuelle est très simple.

D'abord, je fais moi-même...

Sélectionne les titres sur la première page

Copie les quatre cellules dans le presse-papiers
Colle ces titres dans la feuille Feuil1

...ensuite, j'apprends au logiciel à le faire!

Recommence la manoeuvre en t'enregistrant. La macro contenant cette opération s'appellera RecupTitres.

Une fois la macro constituée, vérifie son texte dans l'éditeur VBA.

Il faut recommencer la même opération pour les chiffres disposés dans les trois feuilles cl1, cl2 et cl3.

Mais il est clair que si nous savons le faire pour la feuille cl1, il n'est pas difficile de reporter le processus pour cl2 et cl3.

Enregistre la macro qui envoie les informations numériques de cl1 vers la deuxième ligne de la feuille Feuil1. Cette macro s'appelle RecupCl.

Modifie le texte de cette macro pour qu'elle récupère ensuite les informations numériques de cl2 vers la troisième ligne de Feuil1 et les informations numériques de cl3 vers la quatrième ligne de Feuil1.

Le résultat final est présenté ci-dessous.

Quand ta macro est capable d'incorporer correctement dans Feuil1 les valeurs provenant des autres feuilles, passe à l'étape suivante.

Terminer le travail

Il ne reste plus aucune difficulté. Les deux dernières étapes

Supprimer les feuilles de calcul temporaires cl1, cl2 et cl3
Réaliser le graphique

seront enregistrées dans les macros

Nettoyer
TracerGraph

Pour supprimer les feuilles temporaires, clique droit sur l'onglet correspondant et sélectionne la commande Supprimer.

La réalisation du graphique est triviale.

Quand tes deux macros sont réalisées, passe à l'étape suivante.

Le grand moment

Nous avons maintenant réalisé tout le travail (en principe) assez facilement.

Il n'y a plus qu'à assembler tous les morceaux en une seule macro.

Dans l'éditeur de macros, procède à la rédaction manuelle de ta macro

Exécute-la ensuite en mode pas-à-pas approfondis.

Quand tout est au point, exécute-la entièrement d'une traite et savoure ta puissance.

Quand ta super-macro fonctionne, sache que tu as fini !!

Droits d'auteur : Yves Mairesse. Ce cours est sous contrat Creative Commons