Notre magasin

Rue Albert 1er, 7

B-6810 Pin - Chiny

(/fax: 0032 (0)61/32.00.15

Magasin YBET informatique

Le cours Excel YBET

FORMATIONS LOGICIELS FORMATIONS TECHNIQUES DIVERS
Microsoft PowerPoint PC et périphériques Le langage PHP, MySQL
Tutorial Access Configurations de Windows Installations techniques
Formation Internet Le matériel réseau  
YBET informatique CONTACT  

16. Macros Excel (suite et exercices)

1. Introduction - 2. Macro pour tous les classeurs, classeur spécifique - 3. Macros complémentaires - 4. Créer ses macros - 5. Exemples de macros "Fichier" - 6. Exemples de "mise en page" - 7. Calculs - 8. Exercice final

Avec le chapitre précédant, nous avons étudier une vue d'ensemble de cette programmation. Cette partie va expliquer d'autres spécificités, mais surtout donner des exemples d'utilisation. Au final, nous utiliserons une macro pour automatiser certaines parties de notre facture Excel.

2. Macro pour tous les classeurs, classeur spécifique.

Lorsque vous créez une macro Excel, la première fenêtre pose la question d'enregistres dans? (3).

 Les différentes possibilités sont:

  1. Classeur de macros personnelles: (dans votre classeur personnel perso.xls) Ce choix vous permet d'utiliser la macro créée à partir de n'importe quel classeur.
  2. Nouveau classeur dans un nouveau classeur qu'Excel crée automatiquement. La macro ne sera utilisable que dans ce classeur
  3. Ce classeur: valable uniquement dans le classeur en cours
  4. Description, un commentaire personnel

Remarque: Vous disposez de 3 méthodes pour qu'une macro soit accessible dans tous les classeurs:

  1. l'écrire dans un module du classeur perso.xls.
  2. l'écrire dans un module d'un classeur ouvert automatiquement avec Excel ( le classeur doit être enregistré dans le dossier C:\program Files\Microsoft Office\Office\XLStart ou C:\Windows\Application Data\Microsoft\Excel\xlstart selon votre version de Windows.
  3. Dans l'environnement VB, vous pouvez enregistrer le classeur en tant que macro complémentaire avec le type Macro complémentaire.

3. Macro complémentaires.

Le tableur intègre quelques fonctions complémentaires optionnelles. La liste se trouve en macros complémentaires dans le menu Outils. Leur installation est optionnelle. Elles sont insérées comme commandes additionnelles dans le menu Outils.

Par exemple, le solver est une fonction étendue de la valeur cible permettant d'insérer des conditions dans les cellules à modifier pour obtenir le bon résultat.

4. Créer ses macros

Comme exemple d'utilisation et comme exercice, nous allons créer différents types de macros Excel. Nous terminerons par une utilisation pour notre facture (l'exercice de toute cette formation qui sert de référence).

Avant de commencer à programmer des macros, songer que l'ordinateur est bête. Il exécute très vite et sans erreurs des tâches simples, à condition de lui signaler tout ce qu'il doit faire, pas à pas.

De plus, lorsque vous enregistrez une macro par cette méthode, une simple erreur peut vous faire recommencer tout l'enregistrement de votre macro. Mieux vaut donc faire la procédure lentement.

5. Exemples de macro "fichier"

Cette partie va reprendre des exemples de macros concernant l'ouverture, enregistrement de classeurs Excel, impression, ...

5.1. Ouverture d'un fichier

Cette macro va nous permettre d'ouvrir un fichier spécifique à l'aide d'un bouton dans une barre des tâches. Elle doit être créée avec l'option "Classeur de macro personnel".

Commencer par créer une nouvelle macro. Utilisez la commande Ouvrir du menu Fichier et sélectionnez "Autre classeur" dans le volet Office (version XP et 2003)

Lorsque la fenêtre de choix Windows apparaît, sélectionnez le dossier où se trouve votre document. Cette étape est obligatoire, sinon, la macro Excel cherchera le document dans le dossier en cours.

Une fois le dossier sélectionné, sélectionnez le document dans la liste et arrêtez l'enregistrement de votre macro.

Exercice: créez un bouton dans la barre d'outils standard exécutant cette commande.

L'enregistrement de cette suite de commande est exactement identique à une opération courante d'ouverture de fichier. Une seule petite distinction est la sélection obligatoire du dossier avant de sélectionner le fichier.

5.2. Impression du classeur sur une autre imprimante.

Nous allons créer une macro qui imprime le classeur courant sur une autre imprimante (une imprimante réseau par exemple) et remet l'imprimante par défaut. De nouveau, nous devons donner comme option "Classeur de macro personnel".

Une fois l'enregistrement commencé, sélectionnez Imprimer dans le menu Fichier, sélectionnez l'imprimante sur la qu'elle vous désirez imprimer et faites OK. L'impression démarre (y compris en mode enregistrement). Resélectionnez la commande Imprimer du menu Fichier, sélectionnez votre imprimante par défaut et cliquez sur le bouton "Fermer", pas OK qui démarrerais l'impression sur votre imprimante une seconde fois. Arrêter l'enregistrement de la macro.

Exercice: utilisez le bouton imprimante créé ci-dessus.

6. Exemples de macros "Mises en page".

Ce type de macros va nous permettre de faire des mises en formes (police, alignement, couleur de fond, ...) sur des cellules sélectionnées. Une seule difficultés pour ce type de macros, l'enregistrement ne permet pas de sélectionner une zone de cellules. Vous devez donc sélectionnez une zone de cellules avant de démarrer l'enregistrement. De même, vous devrez sélectionner les cellules avant d'exécuter la macro de commande.

Commençons par créer une nouvelle macro avec une plage de cellule sélectionné. Modifiez la mise en forme des cellules à l'aide de la commande cellule du menu Format. Une fois terminé, arrêter l'enregistrement.

Exercice: affecter un raccourci clavier à cette macro, par exemple <CTRL> + >Shift> + I. Pour cette opération, utilisez la commande macros du menu outils, sélectionnez la macro et cliquez sur le bouton Options.

7. Calculs

Cette parties des exemples d'utilisation des macros en Excel va sans doutes être la plus difficile. D'une part, elle va utiliser les notions relatives et absolues, d'autre part, elle nécessite de connaître quelques raccourcis claviers.

  • CTRL + END: positionne le curseur souris le plus en bas à gauche du tableau
  • CTRL + Home: positionne le curseur en A1 (le plus haut à gauche)
  • Shift + CTRL + END: sélectionne les cellules à droites et en bas de la cellule courante
  • Shift + CTRL + HOME: sélectionne les cellules à gauches et en haut jusqu'à A1
  • Shift (maj) + END suivi de la flèche vers la gauche: positionne le curseur vers la cellule utilisée la plus à gauche de la cellule active
  • Shift (maj) + END (Fin) suivi de la flèche vers la droite: positionne le curseur vers la cellule utilisée la plus à droite de la cellule active.
  • Shift (maj) + END (Fin) suivi de la flèche vers le haut: positionne le curseur vers la cellule utilisée la plus à proche en direction du haut de la cellule active.
  • Shift (maj) + END (Fin) suivi de la flèche vers le bas: positionne le curseur vers la cellule utilisée la plus à proche en direction du bas de la cellule active.

Par contre: Si vous maintenez les 3 touches ensembles, Excel sélectionne l'ensemble des cellules:

. à droite, flèche droites (y compris les cellules vides si vous êtes à la dernière colonne de données)

. à gauche, flèche gauche, jusqu'à la colonne A

. en haut, flèche haute jusqu'à la ligne 1

. en bas, flèche bas (jusqu'à la ligne 65635 si vous être à la dernière ligne de données).

Le mieux est d'essayer ces raccourcis claviers au préalable.

7.1. Mettre automatiquement une valeur dans une cellule donnée (par exemple A3).

Dans ce cas, comme la référence de cellule est connue, nous devons travailler en mode absolu (bouton grisé). Durant l'enregistrement, sélectionnez la cellule A3 (même si le curseur est déjà positionné) et tapez la valeur souhaitée.

7.2. Copier le contenu de la cellule active dans une autre cellule.

Comme premier exercice, nous allons copier le contenu de la cellule active dans la cellule 2 lignes plus bas. Dans ce cas, comme la référence de cellule est inconnue (où plutôt relative par rapport à la cellule en cours), nous devons sélectionner le mode relatif durant l'enregistrement de notre macro. Si vous souhaitez copier toute une plage de cellule, vous devez le faire avant de débuter l'enregistrement.

Dans la cellule en cours, utilisez la commande "copier" (soit à partir du menu Edition, soit à l'aide du raccourci clavier CTRL + C). Descendez de 2 cellules et utilisez la fonction copier.

Par contre, si nous souhaitons copier le contenu de la cellule en cours vers la cellule A3 par exemple, vous devez utiliser le mode absolu.

7.3. Insérer une colonne avec une formule

Si nous désirons insérer une colonne entre la troisième et la quatrième par exemple, il suffit (en mode absolu) de sélectionner la colonne 4 et d'insérer une colonne (menu contextuel).

Par contre, voyons l'exemple ci-dessous.

A partir de ce tableau, nous souhaitons rajouter à droite de la dernière colonne le total. Nous ne savons pas combien de lignes seront présentes et pour compliquer, ce tableau peut comporter des colonnes avant notre colonne quantité et des colonnes à droite.. Voici comment créer la macro.

Commencez l'enregistrement en mode relatif. Pour positionner le curseur le plus en bas à droite, utilisez le raccourci CTRL + END. Premier problème, les colonnes sont toujours insérées à gauche sous Excel. Déplaçons le curseur d'une case à gauche (d'où le mode relatif) et par le menu insertion, sélectionnons colonne. Tapons maintenant notre formule en relatif: =ref-cell-quantité*ref-cell-prix (utilisez les flèches, c'est plus facile). Il ne nous reste plus qu'à recopier notre formule vers le haut. Déplacez le curseur vers la cellule contenant la formule. CTRL + shift + flèche haute va sélectionner les cellules vers le haut. Il ne reste plus qu'à utiliser la commande remplissage en haut du menu Edition.

Dernier problème. Comme notre première ligne contient du texte (les en-têtes de colonnes), cette cellule contient un message d'erreur. CTRL + flèche haut positionne le curseur sur cette cellule. Il ne reste plus qu'à effacer le contenu.

7.4. Copier le contenu d'une cellule contenant une formule.

 Cette fonctionnalité est un peu plus compliquée. Cette fonctionnalité va nous permettre de sauvegarder les données de notre facture sur une nouvelle feuille par exemple. Si vous copier et coller une formule, comme Excel adapte les formules, le résultat est modifié lors du déplacement.

La solution passe par un copier suivi d'un collage spécial "valeur".

8. Exercice final

Nous allons utiliser les notions ci-dessus pour faire une superbe macro adaptée à notre facture. Cette suite de commande doit permettre à l'aide d'un bouton associé au classeur facture de:

  1. Copier les données de facturation sur une autre feuille en colonne (à créer au préalable). Ceci permet de conserver un résumé des factures effectuées (éventuellement ceci peut servir pour le listing TVA).
  2. Repasser sur la feuille du contenu de la facture, effacer les données à l'intérieur de la facture, remettre "Code client" au lien du code modifié. Remettre la date avec la fonction aujourd'hui()
  3. Incrémenter le numéro de facture.

Cette dernière partie nécessite en mode absolu: de copier le numéro de facture dans une cellule vide. Dans la cellule adjacente, additionner cette valeur avec 1. Copier le contenu en collage spécial vers la cellule reprenant le numéro de facture et effacer le contenu des calculs intermédiaires.

Créer des macros Access, même utilisation mais programmation totalement différente Commandes relationnelles MySQL, gérer des tables en relationnelles en langage MySQL avec PHP

< 16. Macro Excel (Théorie)

Suite de la formation >18. Trucs et astuces Excel

Plan de la formation en ligne

1. Cours Excel - 2. Fonction Excel - 3. Mélanges de fonctions entre-elles - 4. Mise en forme des cellules - 5. Impressions sous EXCEL - 6. Graphique sous Excel - 7. Fonction Somme.si() - 8. Feuilles Excel - 9. Tri et Filtre Excel - 10 . Relatif - absolu - 11. Les tables Excel - 12. Fonction financière - 13. Sous-totaux - 14. Filtre élaboré   - 15. Fonctions de recherche - 16. Macro Excel - 17. Macros Excel (suite et exercice) - 18. Petites trucs complémentaires

Votre revendeur de confiance en province de Luxembourg:

Ce site est maintenu en partie par la publicité: merci de désactiver votre bloqueur de publicités (adblock et autres).