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 macros "mise en page" - 7. Calculs - 8. Exercice final
Avec le chapitre
précédant, nous avons étudier une vue d'ensemble des macros Excel.
Cette partie va expliquer d'autres spécificités des macros, mais surtout donner
des exemples de macros. Au final, nous utiliserons une macro pour automatiser
certaines parties de notre facture Excel.
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:
- 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.
- Nouveau classeur dans un nouveau classeur qu'Excel crée
automatiquement. La macro ne sera utilisable que dans ce classeur
- Ce classeur, macro valable uniquement dans le classeur en cours
- Description, un commentaire personnel
Remarque: Vous disposez de 3 méthodes pour qu'une macro soit accessible dans
tous les classeurs:
- l'écrire dans un module du classeur perso.xls.
- 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.
- Dans l'environnement VB, vous pouvez enregistrer le classeur en tant que
macro complémentaire avec le type Macro complémentaire.
Excel intègre quelques fonctions complémentaires optionnelles. La liste se
trouve en macros complémentaires dans le menu Outils. Leur installation est
optionnelle. Ces macros 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 des macros et comme exercice, nous allons créer
différents types de macros Excel. Nous terminerons par une macro pour notre
facture (l'exercice de toute cette formation Excel).
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 le plus en bas à gauche du tableau
-
CTRL + Home: positionne le curseur en A1
-
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 de macros ci-dessus pour faire une superbe
macro adaptée à notre facture. Cette macro doit permettre à l'aide d'un bouton
associé au classeur facture de:
-
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).
-
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()
-
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.
|