50044903 Excel2007 Macro Commandes

-1-

Initiation aux macro-commandes Excel 2007

Initiation aux macro-commandes Excel 2007.docx / Denis Belot / jeudi 26 novembre 2009 - 16:40

-2-

Avertissement Ce document accompagne le cours qui a t conu spcialement pour les stagiaires des cours de Denis Belot. Le cours a t ralis en rponse aux diverses questions poses par les stagiaires. Le support de cours est conu dans le but de permettre de suivre la formation sans devoir prendre des notes. Le support de cours permet galement de refaire les exercices raliss durant la formation. Ce document ne constitue pas une rfrence utilisable sans le cours. Le cours est rgulirement adapt pour tenir compte des demandes et des volutions techniques, cest pourquoi le contenu peut tre diffrent chaque session de formation. Ce cours a t adapt pour tre ralis dans une dure rduite tout en prsentant un nombre important de fonctions. Les exemples fournis sont des exemples buts pdagogiques et ne constituent

pas des modles pour des cas rels en entreprises.Si des anomalies ou des incohrences demeurent dans ce document, elles peuvent tre le rsultat dune faute de frappe, dune volution des techniques ou dune imprcision involontaire. Dans tous les cas nous vous remercions de bien vouloir nous signaler les ventuelles erreurs. Windows, Internet Explorer, Outlook Express, Word, Excel, Publisher, FrontPage, Office sont des marques dposes de Microsoft Corporation. Toutes les autres marques cites ont t dposes par leur diteur respectif. La loi du 11 mars 1957 nautorise aux alinas 2 et 3 de larticle 41, dune part, que les copies ou reproductions strictement rserves lusage priv du copiste et non destines une utilisation collective , et, dautre part, que les analyses et les courtes citations dans un but dexemple et dillustration. Toute reprsentation ou reproduction intgrale ou partielle, faite sans le consentement de lauteur ou de ses ayants droit ou ayant cause, est illicite (Alina 1er article 40). Toute reproduction ou reprsentation par quelque procd que ce soit, constituerait donc une contrefaon sanctionne par larticle 425 et suivant du Code Pnal. Usage dInternet : Nous avertissons nos stagiaires que, durant les cours les accs au rseau Internet sont enregistrs dans un historique conformment la lgislation. Les rponses ne vous sont donnes qu' titre indicatif. Sans un diagnostic prcis sur un matriel il peut tre difficile de cerner lorigine des problmes. Aussi nous ne pouvons en aucuns cas tre tenus pour responsable de problmes ou pannes survenant sur votre propre matriel.


-3-

Table des matiresA. B. C. D. E. F. G. H. I. J. K. L. M. N. O. P. Q. R. Introduction ............................................................................................................................................................... 4 Rappels indispensables .............................................................................................................................................. 5 Concepts ................................................................................................................................................................... 8 Cration dune macro avec lenregistreur.................................................................................................................... 9 Ajouter un Bouton dexcution ................................................................................................................................ 12 Dsactivation de la scurit ...................................................................................................................................... 14 Exercice de base ...................................................................................................................................................... 15 Exercice : convertir une formule en valeur ................................................................................................................ 17 Rfrence Relative et Absolue ................................................................................................................................. 18 Exercice : quadrillage .............................................................................................................................................. 20 Type de classeur pour macros .................................................................................................................................. 22 Crer des Macros combinant plusieurs macros .......................................................................................................... 23 Exercice combinant plusieurs Macros....................................................................................................................... 24 Dfinitions gnrales ............................................................................................................................................... 26 Exercice de conversion de fichier ............................................................................................................................. 27 Remplir un fichier de mail ....................................................................................................................................... 30 Scuriser vos macros ............................................................................................................................................... 31 Classeur des macros personnelles ............................................................................................................................. 34


-4-

A. IntroductionCe cours sadresse des utilisateurs souhaitant connatre les macro-commandes et les fonctions avances du logiciel Excel. Ce cours a t adapt pour tre ralis dans une dure rduite tout en prsentant un nombre important de fonctions, aussi il sadresse des utilisateurs ayant dj une bonne pratique des fonctions courantes de linformatique et dExcel. Il est prconis davoir dj suivi un cours de perfectionnement Excel. Ce cours a t conu en rponse aux questions poses par les stagiaires. Ce cours est rgulirement adapt pour tenir compte des demandes et des volutions techniques. Il est important de prciser que ce cours ne traite pas exhaustivement de toutes les fonctions du logiciel Excel, mais prsente les fonctions, qui semblent les plus utiles dans le contexte dune dcouverte des macro-commandes.


-5-

B. Rappels indispensables(Dj vu en perfectionnement Excel) Pour lexemple ouvrir 0-exercices de manipulations 1 - Slection de cellules : a ) Tout slectionner Slectionner toute la feuille (intgralement) Une seule fois Ctrl + a slectionne lespace actuel. (Ou deux fois Ctrl + a si lon est dans un plage dj remplie. Ou

b ) Slectionner la zone en cours Toutes les colonnes et lignes dans lesquelles au mois un lments est garni Ctrl + * (pav numrique). Remarque : Il ne faut pas de lignes (ou colonnes) totalement vides. 2 - Slectionner les cellules vides dun tableau Slectionner un ensemble de cellules, Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides Remarque : si tout le tableau est slectionn, la slection sarrte lespace des cellules dj utilises. 3 - Remplissage dune srie de cellules slectionnes Slectionner une srie de cellules (voir avant), la premire reste blanche et les suivantes sont en inverse vido (noires ou bleues). Taper la valeur souhaite dans la cellule blanche, puis valider avec CTRL+Entre. Toutes les cellules seront remplies avec la mme valeur. Remarque : ceci fonctionne avec des cellules contigus ou indpendantes. 4 - Recherche la dernire ligne dun tableau Appuyer sur CTRL+ touche Fin (ou touche flche vers le bas 1). En fait la cellule slectionne est la dernire utilise, celle qui est le plus en bas droite de toutes celles qui ont t utilises. Cette technique permet ventuellement de retrouver des cellules qui ont t utilises et qui sont devenues inutiles. (Remarque : on peut aussi revenir la premire cellule avec Ctrl + Flche Dbut) 5 - Remplissage jusqu la dernire ligne utilise Double clic sur la croix noire dans le coin en bas droite

1

Selon les types de clavier


-6-

Reproduit le contenu jusqu la dernire ligne utilise, si la colonne adjacente est remplie. 6 - Antcdents & dpendants Onglet Formules, reprer les dpendants (ou les antcdents)

7 - Collage spcial Valeur


-7-

Lorsque lon supprime des lments sur lesquels porte un calcul, on obtient des erreurs. Exemple si lon supprime les colonnes Hors taxe et Tva

On peut faire un collage spcial, valeur, pour rcuprer les valeurs avant de supprimer les colonnes.


-8-

C. ConceptsLes macro-commandes appeles communment Macros, sont des programmes raliss dans le langage de programmation Visual Basic pour Application (ou VBA). Il existe un langage plus gnral appel Visual Basic. Les macros peuvent tre assez simples raliser laide de lenregistreur de macros. Lenregistreur de macro se comporte un peu comme un magntophone qui enregistre votre voix. Sauf que lenregistreur de macros nenregistre pas votre voix mais vos actions. Lenregistreur va enregistrer vos actions et les transformer en un vrai programme rutilisable de nombreuses fois. Exemple de macro-commandes utiliser : Les boutons des macro-commandes apparaissent dans la barre doutils accs rapide


-9-

D. Cration dune macro avec lenregistreur1 - Rglage Pralable Ce rglage doit tre fait une premire fois avant de crer des macro-commandes. Dans les options dExcel, cocher la case Afficher longlet Dveloppeur

2 - Cration dune macro-commande avec lenregistreur Onglet Dveloppeur, Enregistrer une macro

Donnez un nom votre Macro Le nom ne peut pas comporter de Blancs Il est utile denregistrer la macro dans Classeur de macros personnelles si lon souhaite que la macro soit utilisable avec nimporte quel classeur. Sinon, elle ne sera utilisable que dans le classeur pour lequel elle a t cre. (Remarque : on peut aussi affecter une touche de raccourci pour permettre lexcution de la macro) Validez Un bouton apparat (pour arrter lenregistrement)


-10-

Effectuez les actions souhaites, Cliquez sur le bouton darrt pour arrter lenregistrement

Votre Macro est cre

Exercice :Cration dune Macro dessai Crer une Macro pour mettre une cellule en rouge et en gras. Remarque : cette macro ne prsente pas dintrt particulier, mais elle est destine comprendre le mcanisme denregistrement. Remarque : pour changer la touche dappel de la Macro


-11-

3 - Excution Pour excuter une Macro : Onglet Dveloppeur, Macro

Slectionner la macro correspondante, cliquez sur le Bouton Excuter On peut aussi utiliser la touche de raccourci, si celle-ci a t indique lors de lenregistrement. 4 - Voir le programme Visual Basic Pour voir1 le programme, lors de laccs la macro, il faut choisir loption Modifier (au lieu dexcuter).

1

Ultrieurement on pourra modifier aussi le programme


-12-

E. Ajouter un Bouton dexcution1 - Dans la barre daccs rapide Choisir Autres Commandes via la barre daccs rapide

Slectionner la catgorie Macros, puis choisissez votre commande :

Vous pouvez galement modifier lallure du bouton :


-13-

2 - Crer un bouton de formulaire VBA Choisir Onglet Insrer :

Tracez votre bouton et affecter la macro correspondante :

Modifier le texte de votre bouton..


-14-

F. Dsactivation de la scuritLes techniques de macro-commandes permettant de crer des virus (Macro virus), les logiciels exercent une certaine vigilance lgard de toutes les macros commandes. Ces techniques de protections sont bases sur des signatures lectroniques, qui permettent de valider lorigine dune macro et ainsi sassurer de son innocuit. Une macro signe (numriquement) est suppose ne pas tre un virus. Les techniques de signature numrique prsentant une certaine complexit, dans un premier temps on dsactivera (partiellement) le systme de scurit. Bien entendu durant la dsactivation du systme de scurit on veillera nutiliser que les macros que lon aura dvelopp soi-mme.

Onglet Dveloppeur, Scurit des macros


-15-

G. Exercice de baseExercice : mise en forme et centrage1 - Ralisation Ouvrir le document Exercice centrage Crez une macro centrage pour centrer horizontalement et verticalement le texte dune cellule.


-16-

2 - Lecture du programme

Correction (ventuelles) : En fait Excel indique toute les proprits du format, mais en ralit, on peut ne conserver que celles qui sont rellement modifies (les alignements).

Remarque : ces suppressions peuvent sembler inutiles. En fait, cest une premire approche simple pour comprendre que lon peut modifier le code source de la macro-commandes.


-17-

H. Exercice : convertir une formule en valeurOuvrir Collage spcial , Dbut de lenregistrement, Nom de macro CollageSpcial Slectionner la zone de rsultat,

Clic droit, Copier, Clic droit, Collage Spcial, Valeur

Modifier la macro pour la simplifier :


-18-

I. Rfrence Relative et AbsolueAvec les rfrences relatives, dans les formules, Excel se positionne relativement la position dune cellule. Par exemple, une formule ralise avec une cellule qui se trouve sur sa gauche, se ralisera toujours avec une cellule positionne sur la gauche. Avec les rfrences absolues, dans les formules, Excel se positionne de manire fixe par rapport la position dune cellule. Par exemple, une formule ralise avec une cellule qui se trouve en B1, se ralisera toujours avec la cellule positionne en B1. Ajout du mot Montant devant chaque montant

1 - Sans rfrences relatives

Examiner le code de la macro :


-19-

2 - Avec rfrences relatives Supprimer la macro, puis la recrer en cochant le bouton Rfrences relatives

SANS rfrences relatives

AVEC rfrences relatives

Examiner le code de la macro :


-20-

J. Exercice : quadrillageOuvrir le document Exercice quadrillage 1- Crer la macro pour enlever le quadrillage Crez une macro SansQuadrillage pour faire disparatre le quadrillage dExcel. Voir Option Excel, Options avances, Afficher les options pour cette feuille

Vous noterez quil est possible denlever le quadrillage, mais que la commande ne permet pas de le remettre. 2- Crer la macro pour mettre le quadrillage Crez une macro AvecQuadrillage pour faire apparatre le quadrillage dExcel.


-21-

Voici le code source des deux macro-commandes

Dans chaque macro, il ny a quune ligne dopration (Sub et End Sub dtermine le dbut et la fin de la macro). ActiveWindow indique la fentre active, c'est--dire la fentre sur laquelle on est en train de travailler. DisplayGridlines peut se traduire par Afficher le quadrillage . ActiveWindow. DisplayGridlines est en fait la caractristique du quadrillage qui peut prendre la valeur True1 ou False2 3- En remplacement des 2 macros prcdentes, crer la macro pour mettre ou enlever le quadrillage. Crez une macro Quadrillage pour faire apparatre ou disparatre le quadrillage dExcel. Dans ce cas, on nutilisera pas lenregistreur mais lon devra crire intgralement la macrocommande.

Dans notre cas on commence par rcuprer la valeur du quadrillage, puis on inverse cette valeur et ensuite on force la proprit du quadrillage avec la valeur que lon vient dinverser.

1 2

Vrai Faux


-22-

K. Type de classeur pour macrosLes macros peuvent tre stockes dans plusieurs emplacements :

1 - Classeur de macro personnelle Les macros seront toujours accessibles (Voir en le chapitre concernant la gestion de ce classeur particulier) 2 - Ce classeur Les macros ne seront accessibles, que depuis CE classeur 3 - Nouveau Classeur Les macros ne seront accessibles, que dans un nouveau classeur Dans un premier temps on nutilisera que les macros dans Ce classeur . Ultrieurement on pourra mettre ces macros dans le classeur de macro personnelles. Remarque : lorsque lon utilise des macros personnelles, aprs avoir cr une macro stocke dans le classeur PERSO, pensez afficher le classeur si vous souhaitez modifier ou supprimer la macro du classeur PERSO. Onglet Affichage, Afficher


-23-

L. Crer des Macros combinant plusieurs macrosCette technique permet de crer plusieurs macros, les tester individuellement puis crer une macro qui appelle chacune des macros cres prcdemment. Exemple :

Macro TraitementGlobal

Macro SuppressionInitiale

Macro Ajout

Macro Calcul

Dans cet exemple on a cr la macro de SuppressionInitiale, puis la macro Ajout, puis la macro Calcul. Chaque macro a t teste individuellement. Ultrieurement on a cr la macro TraitementGlobal qui enchaine les trois macros de base.


-24-

M.Exercice combinant plusieurs Macros(Ce cas correspond un cas rel pos par un stagiaire) Un logiciel de gestion de livres produit automatiquement un document Excel. Ce document Excel doit tre envoy rgulirement un fournisseur de services. Cependant le fournisseur impose des colonnes avec certains amnagements. Ouvrir le classeur LIVRES1008

1 - Macro de suppression pralable (Suppression) Supprimer les 10 premires lignes Supprimer toutes les colonnes sauf les colonnes : Code, Prix Ventes TTC, Stock Dispo, Code Barre. 2 - Macro de changement de format (CodeBarre) Pour la colonne Code Barre, changer le format en indiquant la catgorie Nombre avec zro dcimale. 3 - Macro de changement de la colonne Prix (ChangPrix) Application dun coefficient de 0,95 la colonne prix de vente. En fait on cr une nouvelle colonne dans laquelle on utilise le prix dorigine multipli par 0,95, puis on fait un copier coller pour remplacer la colonne dorigine par les valeurs. Pour viter de remplir toute la colonne il est prfrable de mettre un titre dans la premire cellule, puis slectionner la colonne, Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides Puis, dans x2 indiquer la formule : =D2*0,95 et valider avec Ctrl+Entre. Il faut ensuite faire un collage spcial avec loption valeur. Il faut ensuite supprimer la colonne qui a servi au calcul. (Remarque : diverses solutions sont possibles pour traiter ce genre de situation)


-25-

4 - Macro dajout des colonnes a, et 11 (AjoutA11) Ajouter une colonne contenant uniquement des valeurs a pour la premire colonne Ajouter une colonne contenant uniquement des valeurs 11 pour la deuxime colonne Pour viter de remplir toute la colonne il est prfrable de mettre un titre dans la premire cellule, puis slectionner la colonne, Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides Puis, dans x2 indiquer a et valider avec Ctrl+Entre. Mme chose avec 11. 5 - Supprimer les lignes inutiles la fin (SuppLignesFin) Au dbut de lexercice, nous avons supprim des lignes en dbut de tableau. Ces technique a fait que pour les colonnes ajoutes des lignes supplmentaire sont remplies en fin de tableau (Colonnes Prix de vente, a et 11). Pour supprimer ces lignes se mettre sur la colonne A (par exemple), Slectionner toute la colonne. Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides Puis sur la slection, clic droit, Supprimer, Ligne entire

6 - Crer une macro qui excute les 4 macros prcdentes (Traitement) Afficher lditeur VisualBasic puis taper le code suivant :


-26-

N. Dfinitions gnrales1 - Proprits La proprit est une caractristique dun objet. Par exemple le quadrillage est une proprit de la fentre. 2 - Valeur dune proprit La valeur dune proprit correspond ltat de cette proprit linstant. Par exemple si laffichage du quadrillage est en cours on dira que la valeur de la proprit Affichage du Quadrillage est Vraie En anglais DisplayGridlines aura la valeur True . Comme il sagit de la fentre active (celle sur laquelle nous travaillons), nous dirons ActiveWindow . Donc en final: ActiveWindow.DisplayGridlines = True La valeur dune proprit est transmise largument avec le symbole

3 - Mthode Une mthode correspond une action excuter. La copie dun lment est une mthode. Copy est une mthode. 4 - Argument Les arguments sont des informations destines excuter une mthode selon des rgles particulires. Par exemple Paste est un argument de la mthode Copy . Les arguments sont comme des consignes que lon indique avant lexcution de la mthode. 5 - Valeur dun argument La valeur dun argument correspond au dtail de la consigne. Par exemple xlPasteValues est la valeur de largument Paste pour la mthode Copy . La valeur dun argument est transmise largument avec le symbole

Sil y a plusieurs arguments, on les spare par des virgules. 6 - Evnement Une intervention de lutilisateur est un vnement. Un clic est un vnement


-27-

O. Exercice de conversion de fichierUtiliser le document existant Nov2006.txt Crer un classeur nouveau Conversion 1 - Crer une macro ImportFichier Dbuter lenregistrement Bouton ouvrir, Choisissez Nov2006.txt , Indiquez 4 dans la zone Commencer limportation la ligne , Dplacer les fentres si besoin, Glisser la feuille Nov2006 dans votre classeur Conversion , Supprimer la ligne 2, Se placer en A1 Arrter lenregistrement Modifier le code de votre macro pour le simplifier 2 - Gnraliser la macro Ajouter une instruction pour gnraliser la recherche du fichier importer


-28-

3 - Crer une macro RemplirEtiquettes Dbuter lenregistrement Slectionner A1, Ctrl+* (pav numrique), Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides OK, Tapez =, Appuyer sur Flche vers le Haut, Puis Ctrl+Entre, Slectionner A1 Ctrl+* (pav numrique), Clic Droit, Copier Clic droit, Collage spcial, Valeur Touche Echap, Slectionner A1 Arrter lenregistrement


-29-

4 - Crer une macro AjouterDates Dbuter lenregistrement Slectionner A1 Clic droit, Insertion, Colonnes entire, Taper Dates dans A1, valider avec Ctrl+Entr Appuyer sur Ctrl+* (pav numrique) Onglet Accueil, Rechercher, Slectionner les cellules, Cellules vides Tapez Nov-2006 puis Ctrl+Entre, Slectionner A1 Arrter lenregistrement

5 - Modifier la macro pour introduire une date


-30-

P. Remplir un fichier de mailSub RempEmail() ' ' RempEmail Macro ' Macro enregistre le 02/12/2006 par Denis ' ' Touche de raccourci du clavier: Ctrl+Maj+R ' Dim Email As String Do Until Email = "fin" Email = InputBox("Indiquez "[email protected]") votre adresse Email", "Adresse Email",

ActiveCell.FormulaR1C1 = Email ActiveCell.Offset(1, 0).Range("A1").Select Loop End Sub


-31-

Q. Scuriser vos macros1 - Explication pralable Lorsque vous ouvrez un classeur qui contient des macros, le message suivant apparat :

Ce message est destin vous protger contre dventuelles macros qui pourraient tre des macros virus. Si vous souhaitez excuter vos macros vous rpondrez activer les macros , mais vous ntes toujours pas certains que ce classeurs contient bien vos propres macros et ne contient pas dautres macros introduites votre insu. Pour viter ce type de soucis, vous avez la possibilit de crer un certificat numrique qui authentifiera vos macros. Excel reconnatra les macros que vous avez prcdemment valides. Remarque : ce certificat ne sera utilisable que pour vos macros et sur votre ordinateur.


-32-

2 - Crer une signature numrique personnelle Dans Tous les programmes, Microsoft Office, Outils Microsoft Office, Chercher Certificats numrique pour les projets VBA

Indiquez votre nom et valider. Votre certificat est cr. 3 - Ajouter une signature numrique un projet Excel Dans lditeur de macros, Menu Outils, Signature lectronique, Choisir Valider votre nom, puis OK.


-33-

4 - Excuter un projet qui dispose dune signature lectronique Ouvrir votre classeur


-34-

R. Classeur des macros personnelles1 - Trouver lemplacement Xlstart Le dossier contient les classeurs qui doivent tre ouverts au dmarrage. Dans les options Excel

2 - Classeur PERSONAL.XLSB Vrifier la prsence du classeur Si besoin crer un classeur PERSONAL.XLSB dans le dossier Xlstart 3 - Affichage du classeur Pour modifier les macros on doit afficher le classeur


Documents

50044903 Excel2007 Macro Commandes