TD macro-commande Excel 2007 - TD VBA Excel 2007 - Exercice Corrigé Macros et VBA Excel 2007 Exemple Pratique VBA Excel

Objectifs : Réaliser une macro-commande Visual Basic

- Enregistrer une Procédure VBA

- Utiliser la fonction If pour contrôler le déroulement d’une macro

- Réaliser un message d’alerte
 

CAS DEPENS 

                

La société DEPENS S.A. souhaite réaliser un tableau pluriannuel (4 ans) de ses dépenses. Ce tableau est mis à jour chaque année. Lorsque les dépenses d’une nouvelle année sont connus, l’utilisateur du tableau doit :

  

• effacer le contenu de la colonne des dépenses de la première année

• Copier le contenu des trois dernières colonnes à partir de la première

• Saisir les dépenses  de la nouvelle année

Cette procédure est complexe pour un néophyte aussi, vous vous proposez de développer une macro-commande qui exécutera les deux premières opérations automatiquement.

ACTIVITE	MODES OPERATOIRES
1)       Charger le classeur DEPENS.XLS. Et sauvegardez le sous le nom DEPENS1.XLS
2) Démarrer l’enregistreur de macro	Onglet Développeur  – Enregistrer Macro
3) Il faut alors définir le nom de la macro et lui affecter au besoin une touche de raccourci clavier.	Après validation, l’enregistreur de macro est en attente TOUT ce que vous faites sera mémorisé et deviendra du code VBA
4) Enregistrement de la macro-commande	Plutôt que de saisir des lignes de codes V.B.A. vous même (avec les risques d’erreur de syntaxe possible), vous allez enregistrer une macro-commande, c’est à dire que vous allez effectuer toutes les manipulations « manuellement » et EXCEL va enregistrer ces manipulations et les traduire en code macro V.B.A. : - Cliquer sur l’onglet DEPENSES - Sélectionner la zone B4:B10 - Appuyez sur la touche <Suppr> (Les données de 2007 sont effacées) - Sélectionner la zone C4:E10 - Activer le menu Edition - Copier - Sélectionner la cellule B4 - Activer le menu Edition - collage spécial et cocher la case Valeurs (Les données de 2008  à 2010 ont été copiées à partir de la cellule B4 - La colonne 2010 étant en double, Sélectionner la zone E4:E10, puis appuyer sur la touche<Suppr>  - La macro est terminée : cliquer sur le bouton Arrêter l’enregistrement
	Saisissez les données de 2011 : En E4 : tapez 2011 puis tapez les chiffres suivants à partir de E6 : 150200 90000 140000 55000 10000
5) Visualisez le contenu de la macro	Onglet Développeur – Macros  Sélectionner la macro Inserercolonne puis Cliquer sur "Modifier"

La procédure doit être la suivante :

  

Sub Inserercolonne()	Le nom de la macro devient nom d’une procédure VBA les () vides indiquent qu’il n’y a pas d’arguments
Sheets("DEPENSES").Select	Sélectionner feuille “DEPENSES”
Range("B4:B10").Select	Sélectionner plage “B4:B10”
Selection.ClearContents	EffacerContenu de la Sélection
Range("C4:E10").Select	Sélectionner la Plage “C4:C10”
Selection.Copy	Copier Sélection
Range("B4").Select	Sélectionner Plage “B4”
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _         IconFileName:=False	CollageSpécial dans la FeuilleActive
Range("E4:E10").Select	Sélectionner Plage „ E4:E10“
Selection.ClearContents	EffacerContenu de la Sélection
End Sub

                                                                            

Commentaires :

  

1) Toutes les manipulations ont été traduites en code V.B.A.

  

2) Afin de documenter la macro, on peut placer un commentaire au bout de chaque ligne de code grâce au caractère ‘

 

3) Excel 2000 permet de créer deux types de macros :

  

• les procédures (encadrées par les mots Proc... et Fin Proc qui correspondent à un enchaînement de commandes
• Les fonctions (encadrées par les mots Fonction... et Fin Fonction) : qui correspondent à la possibilité de créer des fonctions personnalisées directement utilisables comme les formules dans les feuilles de calcul.

 

4) Le langage Visual Basic Application est un langage orienté objet :

Les objets manipulés sont définis par leurs propriétés. Et on leur applique des Méthodes.

Dans l’exemple de la macro précédente, on peut noter les objets suivants :

- Sheets(« Nom de la feuille »)	équivaut à une feuille (de calcul,...)
- Range(« Référence de la plage de cellule »)	Équivaut à une zone de cellules (exemple « A1:B10 »
- Selection	correspond à la zone sélectionnée en cours (une cellule, une plage...)
- ActiveSheet	correspond à la feuille active (celle dont l’onglet est en surbrillance)

  

Toujours dans l’exemple, les méthodes suivantes ont été appliquées à différents objets :

          

- Select	Sélectionner
- ClearContents	Effacer (le contenu)
- Copy	Copier
- PasteSpecial	Collage Spécial

Ces méthodes peuvent être traduites par un verbe ou un substantif

D’une manière générale, pour affecter une méthode à un objet on utilise la syntaxe suivante :

Objet.Méthode 

  

Par exemple Selection.copy Signifie que l’on souhaite copier (méthode)  la sélection (objet) en cours

Remarque : Les mots clés comportent toujours une première lettre en majuscule affichée automatiquement par Excel. Si la première lettre n’est pas convertie en majuscule, c’est que la commande est inconnue

Certaines méthodes comportent des variables intrinsèques, par exemple pour la méthode EffectuerCollage spécial comporte la variable intrinsèque Format: 

CONCLUSION : POUR EVITER TOUTE ERREURS DE SYNTAXE, UTILISER LA COMMANDE D’ENREGISTREMENT DE MACRO ET PENSEZ A DOCUMENTER VOTRE CODE POUR EN ASSURER LA MAINTENANCE FUTURE.

  

6)Exécution de la macro : Pour lancer la macro Inserercolonne(), vous pouvez Activer l'onglet Développeur  – Macros -  Exécuter, ou mieux, cliquer sur une image ou une zone de texte

- Afficher l'onglet « Insertion »  - Cliquer sur « Formes »  - Cliquer sur l’outil  « Zone de texte» - Dessiner une zone de texte en A1 et saisissez le texte « Nouveau » (mettre en forme la zone de texte selon son goût) - Sélectionner la zone de texte (pas le texte mais bien la zone) - Cliquer droit pour activer le menu contextuel et sélectionner « Affecter une macro … » - Sélectionner la procédure Insèrecolonne Vous devez obtenir ceci : - Sauvegarder le classeur - Cliquer sur ce bouton pour essayer et saisir des données quelconques pour l’année 2012.

  

AMELIORATION DE LA MACRO-COMMANDE

Une fois que l’on clique sur le bouton nouveau des données sont effacées de manière irréversible. Aussi on souhaite afficher un message d’alerte afin d’informer l’utilisateur de cet effacement et de lui demander de confirmer ou d’annuler l’opération.

La structure algorithmique de la macro-commande serait la suivante :

Sélectionner feuille DEPENSES

Alerte = Afficher message d’alerte de type 2          

 (la variable alerte aura pour valeur VRAI, si l’utilisateur souhaite continuer  et FAUX s’il annule l’opération)

SI Alerte = VRAI

                            ALORS : Effacement et copie de cellules effectués

                            SINON : On ne fait rien

FIN.SI

Fin de la macro-commande

Activité :

  

1) Créer la procédure Insérecolonne2 depuis la fenêtre « Visual Basic Editor » et non par le menu nouvelle macro :

Pour ouvrir Visual Basic Editor, cliquez sur l'onglet Développeur – Visual Basic
  

2) Saisir la ligne suivante dans la feuille Module1 immédiatement après l’instruction End Sub :

Sub Inserercolonne2()

Puis recopier le corps de la procédure précédente en dessous de la déclaration de procédure Inserercolonne2()

3) Ajouter des lignes de code afin d’obtenir le code de la page suivante (les lignes en gras italique correspondent aux lignes ajoutées :
 

 

REMARQUE : Après avoir tapé la fonction MsgBox, cliquez droit pour faire apparaître le menu contextuel et sélectionnez le menu Infos Paramètres. Vous verrez alors apparaître les arguments de la fonction (ses paramètres)

4) Créer une zone de texte dans la feuille, saisir le texte « Nouveau2 » et affectez la macro InsererColonne2() à la zone de texte

 

 

5) Sauvegarder la feuille de calcul

6) Tester la macro en cliquant successivement sur les boutons Oui et Non

 

Sub Inserercolonne2()
Alerte = MsgBox("Attention, les données de la deuxième colonne vont être perdus, voulez vous continuez ?", vbYesNo, "Suppression de données")	'Affiche un message avec deux boutons : OK et Annuler Le résultat est stocké dans la variable Alerte
If Alerte = vbYes Then	'Si bouton Oui est enfoncé Alors  on éxécute les instructions qui suivent
Sheets("DEPENSES").Select	Sélectionner feuille “DEPENSES”
Range("B4:B10").Select	Sélectionner plage “B4:B10”
Selection.ClearContents	EffacerContenu de la Sélection
Range("C4:E10").Select	Sélectionner la Plage “C4:C10”
Selection.Copy	Copier Sélection
Range("B4").Select	Sélectionner Plage “B4”
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False	CollageSpécial dans la FeuilleActive
Range("E4:E10").Select	Sélectionner Plage „ E4:E10“
Selection.ClearContents	EffacerContenu de la Sélection
Else	Sinon (l’utilisateur a cliqué sur le bouton non)
End If	Le test s’achève
End Sub

                               

Remarques : 

1) les cellules en Gras - Italique correspondent aux nouvelle lignes de codes ajoutées. 

2)  la fonction Message permet d’afficher une boîte de dialogue personnalisée comportant :

- un message (« attention ! Les données…)

- un ou plusieurs boutons définis par une  constante intrinsèque contenant le code du bouton Dans l’exemple la variable VbYesNo affiche une boîte de dialogue avec deux boutons :  Oui et Non.

Si l’utilisateur clique sur Oui, la fonction Message renvoie la valeur 6 (constante intrinsèque nommée VbYes) . Si le bouton Non est enfoncé, la valeur retournée est  7 ( constante intrinsèque nommée VbNo). D’autres valeurs sont disponibles depuis l’aide de Visual Basic (Faites une recherche sur le mot clé MsgBox)

La valeur retournée par la fonction message est stockée dans une variable définie par l’utilisateur (dans l’exemple la variable est nommée  Alerte)

3) La valeur de la variable Alerte est utilisée dans la fonction Si pour continuer ou terminer le déroulement de la macro.

            

---------------------------------------------------------------------------------------------------

<<< CHAPITRE V : Les Objets Excel et Développement VBA

         

             >>>  Exercices Corrigés VBA EXCEL