Excel 20xx Etude des macros

Copyright © 2021 All Rights Reserved

Joël Lambert

Pour Bruxelles Formation Rue de Stalle, 67 1180 Bruxelles 02/371.73.50

MICROSOFT EXCEL Création de macros : enregistrement, diffusion, modification, références de cellules – Niveau avancé

1

JOËL LAMBERT

INITIATION À LA CRÉATION DE MACROS AVEC EXCEL

CRÉATION D'UNE MACRO PAR ENREGISTREMENT 2

MANIPULATIONS DIVERSES 4

EXÉCUTION DE LA MACRO 4 MODIFICATION DU RACCOURCI 6 CODE VBA DE LA MACRO 6

MODIFICATION D'UNE MACRO 7

EXÉCUTION DE MACROS À L’OUVERTURE DU CLASSEUR 8

DIFFUSION DES MACROS 9

RÉFÉRENCES DES CELLULES DANS LES MACROS + APPLICATIONS 13

2

JOËL LAMBERT

CRÉATION D'UNE MACRO PAR ENREGISTREMENT

Créez un classeur ‘Macros.xlsm’. Dans ce classeur, enregistrez une macro permettant d'afficher le texte "1) Qu’est-ce qu’une macro ? Où se trouvent-elles ?" dans la cellule D4 d’une feuille nommée ‘Planning’

Enregistrer le

classeur au format prenant en charge les macros :

Utiliser le ruban ‘Développeur’ pour créer les macros (affiché via les options d’Excel) :

Excel 2010 – Excel 2019

3

JOËL LAMBERT

Donner un nom à la macro Attribuer un raccourci clavier Choisir l’endroit où sera enregistrée la macro (Ce classeur) .. OK

Se placer en D4 .. Encoder le texte "1) Qu’est-ce qu’une macro ? Où se trouvent-elles ?" ..

Enter.

Bouton d’arrêt de l’enregistrement du ruban ‘Développeur’ :

La liste des macros créées est disponible via le bouton ‘Macros’ :

Raccourci clavier pour l'exécution

de la macro

4

JOËL LAMBERT

MANIPULATIONS DIVERSES

EXÉCUTION DE LA MACRO

1) Via le raccourci clavier (ctrl + p)

2) Ruban ‘Développeur’ .. Macros .. Choix de la macro .. Exécuter.

3) Ajout d’un bouton dans la barre d’accès rapide

5

JOËL LAMBERT

4) Insertion d'un bouton « formulaires » Via le ruban ‘Développeur’ :

Cliquer – tirer sur la feuille de façon à créer le bouton. Une fenêtre d’affectation de macro s’ouvre .. Choisir la macro :

5) Insertion d’un bouton « ActiveX »

Via le ruban développeur, mode création, propriétés : NB : C’est la propriété ‘caption’ qui affiche le texte du bouton

Click droit sur le bouton .. visualiser le code :

NB : Afin de pouvoir utiliser le bouton, il faut quitter le mode création (via le bouton ad hoc du ruban développeur

1) Qu'est-ce qu'une macro? Où se trouvent-elles?Planning

6

JOËL LAMBERT

6) Excel 2010-2019 : création d’un ruban personnalisé

Via ‘Fichier .. Options .. Personnaliser le ruban’ : Nouvel onglet (MesMacros) .. Nouveau groupe (Macros du jour) .. Y placer les macros

MODIFICATION DU RACCOURCI

CODE VBA DE LA

MACRO

7

JOËL LAMBERT

MODIFICATION D'UNE MACRO

A toute macro créée par enregistrement est associé un code (un langage de programmation). En Excel, et pour les autres logiciels de la suite Office, le code des macros est "Visual Basic for Application" (VBA). On peut modifier une macro en travaillant directement dans l'éditeur VBA sur le code qui lui est associé.

Recopiez la macro « Planning » et nommez-la « Planning_déf ». Modifiez cette macro de manière à faire apparaître en D6 le texte "2) L’enregistreur de macros". En D8 le texte "3) Exécution d’une macro : raccourci, barre d’outils ‘Accès rapide’, …" Associez cette nouvelle macro au bouton ‘Planning’

Via l’éditeur VBA, recopiez la macro ‘Planning’ en une macro ‘Planning_def’. Il suffit de faire un copier-coller de la procédure ‘Planning’ (de Sub Planning ( ) à End Sub). Il faut alors modifier le code en ajoutant les lignes qui seront affichées sur la feuille dans les différentes cellules. Lorsque c’est fait, il suffit de fermer l’éditeur VBA. La nouvelle macro est utilisable :

Association de la nouvelle macro au bouton. Clic droit sur le bouton ‘Planning’ .. Affecter une macro

8

JOËL LAMBERT

EXÉCUTION DE MACROS À L’OUVERTURE DU CLASSEUR

Créez une macro ‘EffacerTout’ qui efface tout le contenu de la feuille ‘Planning’. Cette macro sera exécutée à l’ouverture du classeur

Enregistrer la macro ‘EffacerTout’ qui reprend les étapes suivantes :

- Sélectionner la feuille - Delete - Se placer en A1

Recopier les instructions dans la procédure associée à l’ouverture du classeur. Double-clic sur ‘ThisWorkbook’ de l’explorateur de projet :

Testez cette macro : ça fonctionne mais la macro efface le contenu de la feuille active Modifiez la macro de façon à n’effacer que le contenu de la feuille ‘Planning’

Enregistrer une macro qui ne contient comme instruction que le positionnement sur la feuille ‘Planning’. Recopier alors l’instruction de cette macro en ligne 1 de la procédure associée à l’ouverture du classeur :

9

JOËL LAMBERT

DIFFUSION DES MACROS

1) Une macro est disponible lorsqu’elle se trouve dans le classeur ou dans tout classeur ouvert.

Dans notre cas, il suffit alors d’ouvrir « Macros.xlsm » pour que ses macros soient utilisables dans un autre classeur du pc.

2) Le classeur de macros personnelles :

Par exemple, enregistrez, dans le classeur de macros personnelles, une macro ‘Texte’

permettant d’afficher les mots « Macros personnelles » en B2 sur Feuil2

La macro créée se trouve dans un classeur « Personal.xlsb »

Lors de la fermeture d’Excel, un message nous propose d’enregistrer les modifications apportées à « Personal.xlsb » :

10

JOËL LAMBERT

Lors de l’ouverture d’Excel, la macro que nous venons de créer est disponible quel que soit le classeur du poste :

NB : Lors de l’ouverture d’Excel, le classeur de Macros personnelles est automatiquement ouvert en mode masqué. C’est grâce à ceci que les macros de ce classeur sont disponibles car les macros utilisables dans un classeur sont celles du classeur et de tous les classeurs ouverts. Pour modifier les macros personnelles, il faut afficher ce classeur masqué via ‘Ruban Affichage .. Afficher’ :

3) L’utilisation des fichiers *.xlam : Vous voulez que l'on puisse utiliser vos macros sur plusieurs ordinateurs. A l'aide du menu Fichier, enregistrez votre classeur au format Macro complémentaire (*.xlam).

11

JOËL LAMBERT

Ce fichier est utilisable sur les autres PC et permet l’installation des macros complémentaires (le fichier *.xlam). Ceci se fait via les options d’Excel :

Les macros d’un fichier *.xlam installées ne sont pas disponibles directement via « Outils .. macros » :

Ici, ne sont reprises

que les macros de tous les classeurs ouverts.

Nom du fichier *.xlam

12

JOËL LAMBERT

Elles sont pourtant disponibles via le ruban développeur .. Visual Basic :

NB : Les modifications des fichiers *.xlam ne sont pas sauvegardées, sauf si nous le demandons via l’éditeur VBA : « Fichier .. Enregistrer macrosSolution.xlam »

Les macros du fichier xlam peuvent être utilisées en les affectant selon leurs noms à

divers éléments de l’interface (bouton, barre d’outils Accès rapide, …) ou via les raccourcis clavier. Voici par exemple l’exécution de la macro ‘Planning’ via un bouton formulaire :

Fichier *.xlam installé

13

JOËL LAMBERT

RÉFÉRENCES DES CELLULES DANS LES MACROS + APPLICATIONS

A partir du classeur « Macros_Applications.xls », créez les macros suivantes :

Sur la feuille ‘Tvac’, créez une macro TVAC qui permet de calculer le prix tva comprise en C4 selon le prix qui se trouve en B4. Appliquez un format monétaire à la cellule dans laquelle s’effectue le calcul

Sub TVAC( ) Range("C4").Select ActiveCell.FormulaR1C1 = "=RC[-1]+21%*RC[-1]" Selection.NumberFormat = "#,##0.00 $" End Sub

Copiez la macro TVAC et donnez-lui le nom TVAC2 de façon à calculer le prix tva comprise quel que soit l’endroit du classeur où se trouve le prix hors tva. Ajoutez un bouton sur la feuille ‘Tvac2’ de façon à exécuter cette macro.

Sub TVAC2( ) ActiveCell.FormulaR1C1 = "=RC[-1]+21%*RC[-1]" Selection.NumberFormat = "#,##0.00 $" End Sub

NB : Ici, le taux est directement utilisé dans la formule. Si le taux se trouve dans une cellule fixe (par exemple, C1), une référence absolue doit être utilisée lors du calcul.

En VBA, le code devient le

suivant : ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-1]*R1C3"

Créez une macro TVAC3 qui permet de calculer le prix tva comprise d’une cellule sélectionnée. La cellule sélectionnée au départ de l’enregistrement de la macro est la cellule contenant le prix hors tva. Le résultat du calcul sera placé à droite de la cellule de départ.

Le problème ici est de se positionner dans la cellule à côté pour faire le calcul. Ceci est solutionné par l’utilisation des références relatives lors de l’enregistrement de la macro.

Le code VBA est celui-ci : Sub TVAC3()

‘la propriété ‘Offset(xligne,xcolonne)’ qui permet un décalage de xligne et de xcolonne ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-1]*21%" ActiveCell.Offset(1, 0).Range("A1").Select End Sub

14

JOËL LAMBERT

Créez TVAC4 (en références absolues) de façon à calculer le prix tva comprise et remplacer le prix hors tva. Ici, le calcul se fera directement sur la cellule active et le taux à utiliser se trouve dans la cellule « B3 »

Selon le code vu précédemment, nous allons utiliser les propriété ‘ActiveCell’ et ‘Range’ en combinaison avec les propriétés ‘FormulaR1C1’ et ‘Value’ ActiveCell.FormulaR1C1 = ActiveCell.Value + Range(“B3”).Value + ActiveCell.Value

Créez TVAC5 à partir de TVAC4 qui permet de calculer le prix tva comprise et remplacer le prix hors tva. La modification prévoit de pouvoir déplacer le taux utilisé dans la formule de calcul

Dans ce cas, nous pouvons utiliser une zone nommée pour la cellule qui contient le taux

La syntaxe devient : Range("NomCellule").Value

Créez un bouton ‘Insertion’ sur la feuille ‘Insertion’. Associez à ce bouton une macro permettant d’insérer une nouvelle ligne entre le dernier article et la ligne des totaux. La cellule active après exécution de la macro sera la cellule dans laquelle il faut encoder le nom du nouvel article

Sub Insertion() Range("Totaux").Select Selection.Insert Shift:=xlDown ActiveCell.Select End Sub

Créez un bouton ‘Insertion2’ sur la feuille insertion. Ce bouton permettra d’insérer une ligne entre le dernier article et la ligne des totaux. Celui-ci permettra aussi de recopier le calcul du total jusqu’à la cellule qui précède le total général

Sub Insertion2() Range("Totaux").Select Selection.Insert Shift:=xlDown Range("ColonneTotaux").Select Selection.FillDown Range("Total").Select ActiveCell.FormulaR1C1 = "=SUM(R6C5:R[-1]C)" End Sub