Introduction à VBA (Visual Basic for Applicationsargonaths.free.fr/VB Excel/td VBA 2004 2005.doc · Web viewLes exercices utilisent les logiciels de la suite Microsoft Office, en

G.Bousquet Université de Bourgogne 2004/2005

Introduction à VBA (Visual Basic for Applications)

Les exercices utilisent les logiciels de la suite Microsoft Office, en particulier Word et Excel. Le logiciel Visual Basic 6 ou Net n'est pas nécessaire.Les premiers exercices sont basés sur le logiciel Excel.

EXERCICE 1. UTILISATION DE L'ENREGISTREUR DE MACRO

Le menu Outils/Macro/Nouvelle Macro lance l'enregistreur de Macros. Il s'agit d'une sorte de magnétophone qui enregistre toutes les opérations que vous effectuez sur la feuille Excel et qui écrit le code VBA correspondant dans une procédure Sub Macro .... End Sub.On relance la procédure enregistrée par Outils/Macro/Macros (Alt+F8)On peut visualiser et modifier ensuite la procédure en faisant Outils/Macro/Editeur Visual Basic ou le raccourci clavier Alt+F11.

1) Créez avec l'enregistreur une macro qui change la couleur de fond (vert par exemple) et les bordures extérieures de la plage de cellules sélectionnées. Rappel : sélectionnez la plage, clic-droit/format de cellule/Motifs.

2) Modifiez dans l'éditeur Visual Basic (Alt+F11) la procédure créée à la question précédente pour que le code de la couleur de fond soit demandé à l'utilisateur par l'intermédiaire d'une boîte de dialogue (Inputbox).

EXERCICE 2. FONCTION PERSONNALISÉE ECRITE EN VISUAL BASIC.

Excel contient de nombreuses fonctions prédéfinies pour différents domaines : finances, statistiques, mathématiques, traitement des chaînes de caractères, etc. On introduit une fonction dans une cellule sous la forme "=nomfonction(paramètres)". Pour avoir une idée des fonctions disponibles vous pouvez utiliser le menu "insertion/fonction" ou le bouton Les fonctions sont triées par catégorie.

1) Utilisez des fonctions prédéfinies pour :a) mettre dans une suite de cellules une série de nombres au hasard entre 1 et 6b) Calculer la moyenne et l'écart-type d'une série de valeurs.

2) certaines fonctions Visual Basic ne sont accessibles que par le biais des fonctions dites personnalisées.a) allez dans l'éditeur Visual Basic (Alt+F11) puis créez un nouveau module (clic droit/insertion/module). Dans ce

module écrivez le code suivant qui crée une fonction permettant de calculer le nombre de jours qui séparent deux dates données :

Function mafonction(date1 As Date, date2 As Date) As Longmafonction = DateDiff("d", date1, date2)End Function

Retournez à la feuille excel pour utiliser la fonction "mafonction". Elle apparaît dans la catégorie des fonctions personnalisées :

Page 1


EXERCICE 3. AJOUT DE CONTRÔLES SUR LA FEUILLE.

1) Ouvrez Excel. Affichez les contrôles "Visual Basic" (Affichage/Barre d'outils/boîte à outils Contrôles). Ajoutez un bouton de commande.

2) Modifiez les propriétés du bouton en cliquant sur ou en faisant clic-droit/propriétésname : cmdHasardcaption : lancer le dé

Remarque : si l'on fait clic-droit/"format de contrôle" sur le bouton on atteindra les propriétés du bouton en tant qu'objet de la feuille Excel et non en tant qu'objet pour Visual Basic. Par exemple les propriétés de positionnement de l'objet par rapport aux cellules de la feuille.

3) Pour écrire le code associé au bouton vous avez le choix entre le double-clic sur le bouton (en mode création) qui ouvre l'éditeur Visual Basic sur la procédure événementielle Click du bouton, ou ouvrir d'abord l'éditeur (Outils/Macros/Visual Basic Editor ou Alt+F11) avant d'écrire la procédure.

Remarque : on passe du mode création (modification de la position et des propriétés des contrôles) au mode normal de Excel (les contrôles sont actifs) par le bouton bascule :

a) Choisissez une cellule vide sous le bouton cmdHasard et nommez-la "dé" :

b) Ecrivez, testez et comprenez le code suivant, Private Sub cmdHasard_Click()Range("dé").value = hasard(1, 6)End Sub

Page 2


Function hasard(a, b)hasard = Int(Rnd * (b - a + 1)) + aEnd Function

Commentaires : Range("dé").value est valeur de la plage de cellules nommée "dé". Ici cette plage est réduite à une seule

cellule mais il est souvent intéressant de donner un nom unique à une plage de cellules contigües (un tableau).

On peut faire référence à une plage de cellules avec la syntaxe Range("A1:B3") ou Range ("D:E"). Les lettres représentent les colonnes, les nombres représentent les lignes.

Si l'on veut faire référence au contenu d'un seule cellule de coordonnées I,J (I=num. de ligne, J=num. de colonne) la syntaxe est Cells(I,J).

pour atteindre la cellule située I lignes plus bas, et J colonnes plus à droite : Range("dé").Offset(I,J) cells(2,1).Offset(I,J) par exemple cells(2,1).Offset(1,3) est équivalent à cells(3,4)

EXERCICE 4. FONCTIONS DE CHAÎNES DE CARACTÈRES.

On considère une colonne dans laquelle est inscrite une liste du type de personne sous la forme "prénom nom". On souhaite écrire une macro qui lise chaque cellule de cette colonne et place séparément dans deux autres colonnes le prénom et le nom de chaque personne.

Voici le canevas de la procédure qui extrait le prénom (ou le nom) de chaque cellule de la liste

Public Sub SéparePrénomNom()

Dim zone As RangeDim cellule As RangeDim Prénom As StringDim Nom As String

Set zone = SelectionFor Each cellule In zone.Cells

Prénom =Left(cellule.Value, 3) Nom=Mid(cellule.Value,5,3)

Cellule.Offset(0,1)=PrénomCellule.Offset(0,2)=Nom

Next cellule

End Sub

1) Que signifie la ligne : Set zone = Selection ?2) Testez la procédure en écrivant et en sélectionnant une colonne avec des contenus de la forme "prénom nom" puis en

lançant la macro SéparePrénomNom . Expliquez le résultat. 3) ajoutez dans la boucle la ligne cellule.offset(0,3)=instr(cellule.value," ") qui permet de repérer l'emplacement du

premier espace dans la cellule qui contient "prénom nom". Testez.4) Utilisez le résultat de la question précédente pour extraire le prénom et le nom de chaque cellule de la plage sélectionnée

et les inscrire dans les deux cellules adjacentes à droite.

Page 3


EXERCICE 5. COPIE DE FORMAT PAR MACRO.

Le but de cet exercice est de lancer un dé virtuel au hasard et d'afficher non pas la valeur du dé sous forme de nombre mais une image de la face du dé qui lui correspond.Pour cela on dessine les 6 faces du dé dans une autre feuille du classeur et à chaque lancement du dé on copie l'une de faces dessinées (en fait son format) sur la première feuille.

1) Dupliquez le classeur Excel de l'exercice 3 (avec la fonction hasard). Sur la feuille 2 du classeur, créez les six tableaux suivants représentant schématiquement les faces d'un dé.

2) Le but du code est de copier le format du dé choisi au hasard et de le coller sur première feuillea) la première chose à faire est de définir une plage (objet Range) pour chacune des six faces. On commence pour cela

par nommer la première plage (plagedé)

b) Les cinq autres plages (objets Range) seront définies par rapport à "plagedé" par des décalages vers la droite ou vers le bas (méthode Offset). les objets Range doivent être créés à l'ouverture du classeur (Workbook). Par contre les objets et variables qui doivent être accessibles depuis n'importe quelle feuille seront impérativement définis dans un nouveau module (clic-droit sur "Modules" puis Insertion/Module).

Placez le code suivant dans Module1 :

Public plage(1 To 6) As RangePublic plagedé As Range

Placez le code suivant dans le module ThisWorkbook et analysez-le :

Private Sub Workbook_Open()For I = 1 To 3 Set plage(I) = Range("plagedé").Offset(0, 4 * I - 4)Next I

For I = 1 To 3 Set plage(3 + I) = Range("plagedé").Offset(4, 4 * I - 4)Next I

RandomizeEnd Sub

Remarque : les deux boucles reviennent à exécuter le code suivant :

Set plage(1) = Range("plagedé").Offset(0, 0)Set plage(2) = Range("plagedé").Offset(0, 4)Set plage(3) = Range("plagedé").Offset(0, 8)

Page 4


Set plage(4) = Range("plagedé").Offset(4, 0)Set plage(5) = Range("plagedé").Offset(4, 4)Set plage(6) = Range("plagedé").Offset(4, 8)

c) Il ne reste "plus qu'à" modifier le code de la procédure cmdHasard_Click pour faire un copier/coller de la face choisie. Pour cela, faites un premier essai en enregistrant une nouvelle macro avec l'enregistreur de macro et analysez le code produit.Remarque : Les paramètres de la méthode Pastespecial proposés dans l'aide VBA ne portent pas les noms corrects.

EXERCICE 6. CONTRÔLE LISTE.

Le contrôle listbox de Visual Basic se comporte un peu différemment dans Excel. Les différents items peuvent être ajoutés à l'ouverture de la feuille par programmation à l'aide de la méthode additem. On peut aussi utiliser la méthode ListFillRange pour extraire les données à partir d'une plage de cellule.Reprenez l'exercice 1 en remplaçant la boîte de dialogue (fonction inputbox) par une liste simple (listbox). L'initialisation de la liste pourra se faire comme à l'exercice précédent :

Private Sub Workbook_Open()Dim I As IntegerFor I = 0 To 56 Step 5 Feuil1.ListBox1.AddItem INext IEnd Sub

EXERCICE 7. AJOUT D'UN NOUVEAU CONTRÔLE (DATE AND TIME PICKER)..

1) Pour introduire un contrôle "Date and Time Picker" permettant de choisir une date dans un calendrier : affichez la boîte d'outils "contrôles" (clic-droit dans une barre d'outils) puis appuyez sur le bouton qui permet d'ajouter de nouveaux contrôles en plus des contrôles initiaux.

2) Insérez un contrôle Date and Time Picker. Ecrivez une macro ou une procédure événementielle qui écrive dans la cellule active la date choisie dans le contrôle. (utilisez de préférence l'événement closeUp).

EXERCICE 8. VBA WORD.

Le but de cet exercice est de créer un programme (macro) qui permette d'afficher à la demande dans le document Word ouvert un petit calendrier, de choisir une date dans ce calendrier et de l'inscrire à la position du curseur.1) Ouvrez un nouveau document Word et enregistrez-le dans votre dossier sous le nom "calendrier".2) Ouvrez l'éditeur Visual Basic (alt+F11). Faites un clic droit sur le dossier qui correspond au document ouvert et

choisissez Insertion/UserForm.

Page 5

testez les différents événements possibles pour l'objet DTpicker1 (click, change …).Eventuellement enregistrez, fermez et rouvrir le classeur avant le test.


3) Dans la boîte à outils faites clic droit/contrôles supplémentaires et choisissez Contrôle calendrier 9.0

Un nouveau contrôle (objet Visual Basic ) apparaît dans la boîte à outils. Sélectionnez-le et déposez-le sur la feuille.

4) Faites un clic droit sur UserForm1 et choisissez "code". Ecrivez (ou collez) le programme suivant :

Private Sub Calendar1_DblClick()Me.HideSelection.TypeText Format(Calendar1.Value, "dddd d mmmm yyyy")End Sub

Private Sub UserForm_Initialize()Calendar1.Value = DateEnd Sub

Dans le code du document (clic sur This Document) écrivez maintenant :

Sub OuvreCalendrier()UserForm1.ShowEnd Sub

Essayez de comprendre la logique de ces trois programmes, sachant que les deux premiers s'exécutent sur des événements liés à la feuille UserForm1 alors que le troisième est une macro qui ne s'exécutera qu'à la demande de l'utilisateur.

5) Créez un bouton "calendrier" sur la barre d'outil du document qui lance la macro OuvreCalendrier et testez.

EXERCICE 9. PROCÉDURES ÉVÉNEMENTIELLES D'UNE FEUILLE..

Il est possible d'intercepter entre autres les deux événements suivants dans une feuille de calcul : Worksheet_SelectionChange : survient quand on change la plage de cellules sélectionnées. Worksheet_Change : survient quand le contenu de la sélection a changé. La plage de cellules concernée par le

changement est passé en paramètre dans la variable Target (but).

On peut utiliser par exemple cet événement pour faire une modification automatique du contenu d'une cellule.

Page 6


Si la modification ne porte que sur le format de la cellule, il faut se demander si une mise en forme conditionnelle ne suffirait pas.

1) Créez une procédure événementielle (worksheet_change) qui mette en majuscule chaque cellule modifiée de la première colonne (Ucase).

2) Même chose, mais seule la première lettre de la cellule modifiée est mise en majuscule. 3) Créez une macro qui positionne en haut de la fenêtre Excel la cellule sélectionnée : cet exemple est donné dans l'aide de

VBA pour Worksheet_SelectionChange.

EXERCICE 10. CRÉATION D'UN FICHIER AU FORMAT TEXTE DEPUIS UNE MACRO.

les instructions les plus simples pour créer un fichier au format texte (suite de caractères ascii) ou au format binaire (suite d'octets quelconques) sont les instructions open et close.Exemple:

Open "C:\mes documents\essai.txt" For output As #1signifie que l'on affecte le canal n°1 à l'écriture dans un nouveau document essai.txt.Si le fichier existe déjà il est écrasé. On peut compléter un fichier existant par

Open "C:\mes documents\essai.txt" For Append As #1(Append=ajouter).1) Créer un nouveau classeur Excel et insérez le code suivant dans un nouveau module :

Option Explicit

Sub créefichier() Dim ligne As String

Open ActiveWorkbook.Path & "/pageweb.htm" For Output As #1

ligne = "<HTML><HEAD><TITLE> page créée par une macro excel</TITLE></HEAD><BODY>"ajouteligne ligneClose #1End Sub'ajoute la chaîne de caractère "ligne" dans la page html

Sub ajouteligne(ligne)Print #1, ligneEnd Sub

2) Analysez le code. Remplacez "Output" par "Append" et testez en relançant plusieurs fois la procédure créefichier et en actualisant la page web créée.

EXERCICE 11. CONVERSION D'UN TABLEAU EXCEL EN TABLEAU HTML.

Le but de cet exercice est de créer automatiquement une page web qui contient un tableau analogue à la plage de cellules sélectionnées dans une feuille Excel. Dans cette version simple on ne tient pas compte des cellules fusionnées ni du format des cellules.

Des questions préliminaires permettent d'apprendre à manipuler la plage de cellules sélectionnées.

1) Faites une copie du classeur de l'exercice précédent sous le nom conversiontableau.xls et créez un bouton personnalisé (dans la barre d'outils ou, mieux, sur la feuille elle-même) renvoyant à la macro test que l'on créera sous la première feuille du classeur.

Page 7

vous pouvez commencer par tester (et comprendre) le petit programme suivant :

Private Sub CommandButton1_Click()Open ActiveWorkbook.Path & "/mon fichier.txt" For Output As #3Print #3, "coucou"Close #3End subEnd Sub


2) L'objet selection renvoie à la plage de cellules sélectionnées. Par exemple selection.cells(1,2) est la cellule située immédiatement à droite du coin supérieur gauche de la sélection (comparez à activecell). Créez dans la première feuille du classeur une macro "test" qui affiche dans une boîte de message (Msgbox) cette cellule.

3) le nombre de lignes de la sélection est selection.Rows.Count et le nombre de colonnes est selection.Columns.Count. Modifiez la procédure test en y insérant une boucle qui affiche successivement dans une boîte de message le contenu des cellules de la première colonne de la sélection.

4) Reprenez la question précédente en faisant afficher successivement toutes les cellules de la sélection.5) La syntaxe html pour un tableau simple de deux lignes et deux colonnes est :

<TABLE border='1' cellpadding='5' ><TR><TD> ligne 1 cellule 1 </TD><TD> ligne 1 cellule 2</TD></TR><TR><TD> ligne 2 cellule 1 </TD><TD> ligne 2 cellule 2 </TD></TR></TABLE>

Remarques : cellpadding indique en pixels l'écart entre le contenu d'une cellule et sa bordure.

Ecrivez une macro "tableau" qui crée une page web contenant ce tableau. Vous utiliserez pour cela les macros créefichier (avec "as Ouput") et ajouteligne.

6) Combinez le code des questions 4 et 5 pour construire une macro qui convertisse la plage des cellules sélectionnées dans la feuille Excel en un tableau html.Si la sélection contient des cellules vous constaterez que le tableau html aura un aspect peu satisfaisant. Pour corrigez cela il suffit de mettre un espace insécable (&nbsp) dans les cellules vides.

EXERCICE 12. ÉVÉNEMENT WORKSHEET_BEFOREDOUBLECLICK.

On peut intercepter le traitement du double clic sur une cellule en incorporant le code suivant derrière une feuille.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Cancel = True...End Sub

L'instruction Cancel = True sert ici à empêcher le traitement normal du double clic. Il suffit alors de remplacer les pointillés par un programme personnalisé.

Dans cet exercice on va utiliser cette technique pour permettre le changement de couleur d'une cellule sur un double clic.1) Utilisez la propriété ColorIndex (voir exercice 1 et 4) pour mettre en vert la cellule sur laquelle on fait le double clic.2) Faites en sorte qu'un double clic sur une cellule verte en enlève la couleur. Utilisez éventuellement l'enregistreur de

macro pour trouver le code de couleur approprié.3) Modifiez le code pour que la procédure événementielle ne traite qu'une plage de cellules determinée. Utilisez pour cela

le paramètre Target.4) Ajoutez une liste déroulante dans laquelle on peut choisir la couleur que prendra la cellule active après le double clic.

EXERCICE 13. PROCÉDURE ONTIME DE L'OBJET APPLICATION..

Il n'y a pas d'objet Timer dans VBA Excel. A la place, on peut utiliser la méthode ontime de l'objet application qui permet de programmer l'exécution d'une procédure à une heure précise. Pour des informations plus complètes sur la gestion du temps par des macros vba Excel voir par exemple http://disciplus.simplex.free.fr/xl/tempo.htm. Vous y verrez en particulier qu'il faut être particulièrement attentif à la méthode d'arrêt du timer. On y apprend aussi d'autres méthodes pour simuler un timer, en utilisant par exemple des fonctions spécifiques de windows (appelées API, "Application Programming Interface").

Page 8

http://disciplus.simplex.free.fr/xl/tempo.htm


1) Lisez l'aide relative à la méthode ontime.2) Créez une procédure "timer" qui ouvre une boîte de message au bout d'un temps prédéfini.3) Créez une procédure qui affiche un compteur de secondes dans une cellule. Pour cela :

a) Créez une procédure "lanceTimer" qui programme le timer (par Application.ontime) pour lancer une procédure "chrono" dans une seconde. Mémoriser dans une variable "heure" l'heure de lancement de "chrono".Indication : heure = Now + TimeSerial(0, 0, nbresecondes)

b) Créez une procédure "arrêteTimer" pour arrêter le timer en utilisant la variable "heure". c) Créez la procédure "chrono" qui affiche un compteur (variable "compteur" initialement à 0 puis incrémentée) dans

une cellule. Relancez le timer dans cette procédure pour le prochain affichage en utilsant la même syntaxe qu'en a).4) Améliorez le fonctionnement du timer de l'exercice 3 en introduisant un bouton marche/arrêt sur la feuille excel.

Indication : on pourra utiliser une variable booléenne pour mémoriser l'état du timer (lancé ou pas) afin de savoir l'action à entreprendre lors du clic sur le bouton.

EXERCICE 14. TEST SUR PLUSIEURS CELLULES..

L'objet de cet exercice est d'effectuer un test sur le contenu d'une plage de cellules et de lancer un avertissement quand une certaine condition est vérifiée.1) Première méthode sans code.

a) Nommez "test" une plage de cellules. b) Dans une cellule nommé "somme" et n'appartenant pas à la plage "test", créez une formule qui calcule la somme des

valeurs de la plage "test". c) Dans une autre cellule, utilisez la fonction "SI" pour afficher un message d'avertissement quand le contenu de la

cellule "somme" dépasse 100.d) Utilisez la mise en forme conditionnelle pour mettre en rouge la cellule contenant le message d'avertissement en cas

de dépassement.2) Deuxième méthode avec code VBA.

a) Utilisez l'événement "Change" de la feuille pour afficher une boîte d'avertissement quand la somme des valeurs de la plage "Test" dépasse 100.

b) Ajoutez une zone de texte d'avertissement sur la feuille. Rendez-la provisoirement invisible. Modifiez le code de la question précédente pour que la zone de texte n'apparaisse qu'en cas de dépassement de la somme des valeurs de la plage "Test".

c) complément à la question a) :i) la cellule qui a occasionné le dépassement change de couleur avant l'apparition de l'avertissement et revient à

sa couleur initiale après la fermeture de la boîteii) la cellule qui a occasionné le dépassement reprend le focus après la boîte de message, avec sa nouvelle valeur

ou avec sa valeur ancienne.

EXERCICE 15. PROCÉDURE ÉVÉNEMENTIELLE REMPLAÇANT UNE COMMANDE WORD.

Il est possible de remplacer n'importe quelle commande de Word ou presque par une procédure personnalisée. Il suffit d'écrire dans un module situé dans "normal" (pour les documents basés sur ce modèle) ou dans le module d'un document donné (Project("mondocument.doc)/Microsoft Word Objets/ThisDocument) un code comme celui-ci :

Sub NomDeLaCommande () traitement personnalisé remplaçant la commande initialeEnd Sub

NomDeLaCommande est un nom réservé par Word pour la commande qui nous intéresse. Par exemple "FichierEnregistrer" pour la commande "Enregistrer". La liste de ces mots réservés s'obtient dans Word par Outils/Macro/Macros en choisissant "Commandes Word" dans la liste "Macros disponibles dans ".En général on cherche simplement à modifier la commande initiale, il faut donc pouvoir la lancer depuis le code VBA.Les commandes qui permettent d'ouvrir une boîte de dialogue sont lancées par des instructions du type Dialogs(paramètredelaboîte).Show. Voir l'aide VBA de "Dialogs" pour plus de détails.Les instructions pour les autres commandes se retrouvent facilement à l'aide de l'enregistreur de macros.

Page 9


1) Faites en sorte que la commande "Enregistrer" fasse apparaître une boîte de message quelconque à la place de l'action d'enregistrement.

2) Modifiez le code précédent pour que l'enregistrement du fichier en cours ait quand même lieu après la fermeture de la boîte de message.

3) Ecrivez le code nécessaire pour qu'à chaque ouverture de la boîte de dialogue "imprimer" la case "page en cours" soit cochée à la place de la case "Tout".

EXERCICE 16. PROCÉDURE AUTOOPEN (WORD).

Tout code écrit dans une procédure Sub autoOpen() placé dans un module du projet "normal" sera exécuté à l'ouverture d'un document Word.1) Ecrivez le code nécessaire pour qu'à chaque ouverture d'un document l'affichage "page" avec zoom "Largeur page" soit

sélectionné.2) Faites en sorte que le chemin complet vers le document ouvert soit affiché dans la barre de titre de la fenêtre Word.3) Comme la question précédente mais pour les classeurs Excel. Il faut placer le code dans la procédure Workbook_Open.

Indications : windows(1) est la fenêtre active, activeWorkbook.fullname est le chemin complet vers le classeur ouvert.Remarque : il faut mettre le code dans chaque classeur. Je ne sais pas éviter cet inconvénient.

EXERCICE 17. CRÉATION D'UNE MACRO DANS LE CLASSEUR DE MACROS PERSONNELLES.

Le but de cet exercice est de placer une feuille et une macro dans le fichier des macros personnelles "perso.xls" puis de rendre cette macro accessible dans n'importe quel classeur en tant que macro complémentaire (add-in). Cet exercice est librement inspiré de la page web http://www.info-3000.com/vbvba/oumacrostockee.php qui contient d'autres informations sur la façon dont Excel gère la mise à disposition des macros stockées dans divers classeurs.1) Le classeur perso.xls, appelé par Excel "classeur des macros personnelles" est stocké à sa création dans le dossier

xlstart. Chaque classeur situé dans ce dossier est lancé au démarrage d'Excel, éventuellement de façon masqué. C'est le cas de perso.xls. Pour créer ce classeur particulier :a) Allez dans Excel b) Faites Outils/Macros/Nouvelle Macro c) Nom de la macro : Laissez Macro1 d) Enregistrez la macro dans : "Classeur de macros personnelles" e) Cliquez dans une cellule quelconque de votre feuille de calcul (pour que la macro ne soit pas vide) f) Outils/Macros/Arrêter l'enregistrement g) Quittez Excel h) Enregistrez Classeur1 ? NON i) "Voulez-vous enregistrer les modifications apportées au classeur de macros personnelles" : OUI

2) Ouvrez à nouveau Excel et l'éditeur Visual Basic et constatez la présence d'un nouveau projet "perso.xls" alors que le classeur "perso.xls" est lui-même masqué.

Ce classeur est le lieu privilégié pour stocker les macros qui seront disponibles pour les autres classeurs.3) Vous pouvez aussi spécifier un autre dossier de démarrage dans Outils/Options/général, dossier dont tous les classeurs

seront également lancé au démarrage d'Excel, en même temps que perso.xls. Testez cette possibilité.

EXERCICE 18. CRÉATION D'UNE MACRO COMPLÉMENTAIRE.

On peut stocker des macros ou des groupes de macros dans des fichiers individuels d'extension "xla" (Excel Add-in). L'intérêt est de pouvoir transmettre des macros autrement qu'en transmettant un classeur (perso.xls ou autre). Inconvénient : si un classeur utilise une fonction située sur une macro complémentaire et que cette macro complémentaire n'est pas transportée en même temps que le classeur, une erreur sera générée.

1) Le petit exercice suivant est tiré de http://www.info-3000.com/vbvba/oumacrostockee.php et aide à faire comprendre la différence entre un fichier xls et un fichier xla :a) Ouvrez un nouveau classeur et dans dans la cellule A1, écrivez "Coucou".b) Faites "Fichier/Enregistrer sous" et choisissez "Macro complémentaire Microsoft Excel (*.XLA) comme type de

fichier. Choisissez "testcomplement" comme nom de fichier.a) Fermez Excel. "Voulez-vous enregistrer les modifications apportées à Classeur1" : NON

Page 10

http://www.info-3000.com/vbvba/oumacrostockee.php

http://www.info-3000.com/vbvba/oumacrostockee.php


b) Effectivement, Excel n'a pas enregistré le classeur courant (celui qui contient "Coucou" dans A1) c) Revenez dans Excel. Retrournez dans "Outils/Macros Complémentaires". Recherchez "testcomplement.xla".

Constatez que maintenant, il est dans la liste. Si vous avez enregistré votre fichier xla ailleurs que dans le dossier par défaut, cliquez sur "Parcourir".

d) Allez dans l'éditeur Visual Basic pour constater la présence d'un projet "testcomplément.xla".Remarque : quand on sélectionne une macro complémentaire dans un classeur elle apparaît dans tous les futurs classeurs ouverts jusqu'à ce qu'on la déselectionne.Retournez sur la feuille Excel, décochez "testcomplément" dans la liste des macros complémentaires puis retournez dans l'éditeur Visual Basic pour constater la disparition du projet "testcomplément".

EXERCICE 19. CRÉATION D'UNE FONCTION PERSONNALISÉE, STOCKÉE DANS UNE MACRO COMPLÉMENTAIRE.

Cet exemple est une libre traduction d'un exemple disponible sur la page http://www.fontstuff.com/vba/vbatut03.htm.

1) Ouvrez un nouveau classeur, insérez un nouveau module dans le projet associé depuis l'éditeur Visual Basic. Dans ce module, créez une nouvelle fonction destinée à calculer approximativement l'âge d'une personne à partir de sa date de naissance et de la date du jour.

Function age(datenaissance As Date) age = Int((Date - datenaissance) / 365.25)End Function

2) Retournez sur la feuille Excel et utilisez la nouvelle fonction créée :

3) On peut aussi insérer la fonction à partir du menu Insertion/Fonction/Personnalisée.

Ajoutez une aide pour cette fonction : a) Outils/Macro/Macros puis tapez le nom de la fonction (seules les procédures apparaissent)b) Choisissez Options et écrivez l'aide pour la fonction.

4) Enregistrez le classeur en tant que macro complémentaire : Enregistrer sous/ Type de fichier "macro complémentaire". Vous pouvez modifier le dossier de stockage par défaut.

Page 11

http://www.fontstuff.com/vba/vbatut03.htm


5) Testez en fermant le classeur, en ouvrant un nouveau classeur, en ajoutant la macro complémentaire créée à la question précédente et en insérant la fonction "age" dans une cellule.

6) Ajoutez un commentaire de description pour le fichier de macro complémentaire lui-même (en plus de celui de la fonction "age").

7) Affinez la fonction "age" à l'aide de la page web http://www.fontstuff.com/vba/vbatut03.htm.

EXERCICE 20. INPUTBOX POUR SÉLECTIONNER UNE PLAGE DE CELLULES..

La fonction InputBox de l'objet Application dans VBA Excel permet de sélectionner une plage de cellules. Il suffit de donner le paramètre adéquat. 1) Testez d'abord la syntaxe classique de la fonction Inputbox qui demande à l'utilisateur une valeur par l'intermédiaire

d'une boîte de dialogue

Sub essai()Dim A As SingleDim B As SingleA = InputBox("donnez-moi un nombre")B = InputBox("donnez un autre nombre")MsgBox "le produit de " & A & " par " & B & " vaut " & A * BEnd Sub.

2) La fonction Inputbox dans Excel permet aussi de demander à l'utilisateur de choisir une plage de cellules. Créez une macro qui demande une plage de cellules puis qui demande un coefficient et multiplie enfin la valeur de chacune des cellules par le coefficient.Indications : a) Cherchez une aide sur la méthode inputbox dans le fichier d'aide vbaxl9.chm (en téléchargement). Type:=8 permet

de préciser qu'on attend une plage de cellules et non un nombre ou une chaîne de caractères. Repérez l'exemple donné dans l'aide. (Set myRange = Application.InputBox(prompt := "Sample", type := 8)

b) Le programme d'essai ci-dessous place dans chaque cellule de la zone sélectionnée le mot "coucou". Il utilise une boucle For Each … Next pour inspecter chaque cellule :

Set plagecellules = Application.InputBox(prompt:="choisissez une plage avec la souris", Type:=8)

For Each macellule In plagecellules.Cells macellule = "coucou"Next macellule

EXERCICE 21. EXERCICE 1.ANALYSE DE CODE.

Le programme suivant, trouvé sur internet, propose une méthode pour éliminer les doublons sur les lignes d'une feuille Excel.

Sub tridoublon()Worksheets("Feuil1").Range("A1").Sort _key1:=Worksheets("Feuil1").Range("A2"), _Order1:=xlAscending, Header:=xlGuess

Set MaCell = Worksheets("Feuil1").Range("A1")

Do While Not IsEmpty(MaCell)Set MaCellSuite = MaCell.Offset(1, 0)If MaCellSuite.Value = MaCell.Value Then

MaCell.EntireRow.DeleteEnd IfSet MaCell = MaCellSuite

Loop

Page 12

http://www.fontstuff.com/vba/vbatut03.htm


End Sub

1) Que fait exactement l'instruction "Worksheets("Feuil1").Range("A1").Sort ….. Header:=xlGuess" ?2) Analysez la boucle Do … Loop.3) Modifiez le code pour que l'on puisse choisir les lignes sur lesquelles opèrent la procédure.

EXERCICE 22. REPÉRAGE DES DOUBLONS DANS UNE COLONNE.

Le but de cet exercice est de prévenir l'utilisateur de l'entrée d'un doublon dans une même colonne. Si on entre dans une cellule de la première colonne une valeur identique à une autre valeur de cette colonne alors un message d'avertissement apparaît et les cellules identiques apparaissent en surbrillance.Vous pourrez suivre les indications suivantes.1) Déterminez l'événement de feuille sur lequel sera basée la macro.2) Une structure de boucle sera chargée d'inspecter les autres cellules de la première colonne. Vous inspecterez par

exemple les 100 premières lignes de la colonne, sans vous occupez des cellules vides, contrairement à l'exercice précédent.

3) Utilisez les méthodes union et select pour mettre en surbrillance des cellules non contiguës.

EXERCICE 23. ANALYSE DE CODE : REPÉRAGE DES DOUBLONS DANS UNE PLAGE QUELCONQUE.

La procédure suivante est un prolongement des deux exercices précédents. Elle repère les doublons dans une plage de cellules quelconque et pas seulement dans une colonne unique comme dans ces exercices.Testez et analysez le code suivant en répondant aux questions suivantes :1) Cherchez dans l'aide l'instruction Redim. Pourquoi est-elle nécessaire ici ?2) Que fait la ligne : maplage.Interior.ColorIndex = xlNone ?3) Expliquez le fonctionnement de la boucle For Each … Next.4) Analysez le fonctionnement et le rôle de la double boucle suivante en répondant aux questions suivantes :

a) Quel est le rôle de chacune des deux boucles ?b) Pourquoi vide-t-on la variable tableaucellules(I) quand on trouve un doublon avec tableaucellules(I) ?c) Expliquez le rôle de la variable booléenne flgTrouvé.

Dim flgtrouvé As BooleanDim maplage, cellule As RangeDim couleur As ByteDim nbrecellules, I As LongSet maplage = Application.InputBox(Left:=1, Top:=1, prompt:="choisissez la plage de cellules où chercher les doublons", Type:=8, Title:="doublons")nbrecellules = maplage.Cells.CountReDim tableaucellules(1 To nbrecellules)ReDim tableauadresses(1 To nbrecellules)couleur = 20

maplage.Interior.ColorIndex = xlNone

For Each cellule In maplage I = I + 1 tableaucellules(I) = cellule.Value tableauadresses(I) = cellule.AddressNext cellule

For I = 1 To nbrecellules - 1 If tableaucellules(I) = "" Then GoTo nexti valeur = tableaucellules(I) For J = I + 1 To nbrecellules

Page 13


If tableaucellules(J) = "" Then GoTo nextj If tableaucellules(J) = valeur Then Range(tableauadresses(I)).Interior.ColorIndex = couleur Range(tableauadresses(J)).Interior.ColorIndex = couleur tableaucellules(J) = "" flgtrouvé = True End Ifnextj: Next J If flgtrouvé Then couleur = couleur + 2 flgtrouvé = False End Ifnexti: Next IEnd Sub

EXERCICE 24. MACRO POUR TESTER SI UNE CELLULE EST À L'INTÉRIEUR D'UNE PLAGE NOMMÉE.

1) Créez une procédure coordonnées(plage) qui calcule la coordonnée de la colonne la plus à gauche de l'objet range "plage", celle de la colonne la plus à droite, celle de la ligne la plus haute et enfin celle de la plus ligne la plus basse.

2) Utilisez la procédure précédente pour créer une fonction inclus(cellule,plage) qui renvoie "vrai" quand l'objet range "cellule" est inclus dans l'objet range "plage"

3) Utilisez la méthode intersect (voir aide) pour donner une autre solution à la question précédente. Indication : http://www.erlandsendata.no/english/vba/ws/cellinrange.htm

EXERCICE 25. ÉVÉNEMENT WORKSHEET_SELECTIONCHANGE, BOUTON DE COMMANDE. (RÉVISIONS)

Le but de cet exercice est de calculer dans un tableau la somme des valeurs des seules cellules coloriées. Dans la première méthode on utilise l'événement Worksheet_SelectionChange pour inspecter toutes les cellules de la plage nommée "tableau" et pour ajouter à la variable "somme", initialement à zéro, la valeur des cellules coloriées. Le résultat est placée dans la cellule nommée "résultat".Dans la deuxième méthode on utilise un bouton de commande pour remettre à jour la formule (=somme(…)) placée dans la cellule nommée "résultat" en fonction des nouvelles cellules coloriées.

1) Première méthode :a) Nommez "tableau" une plage de cellules quelconques et "résultat" une cellule non contenue dans cette plage. Placez

en dehors de ces deux plages deux cellules qui serviront de modèles pour le pinceau de copie de format qui permettra de colorier ou de décolorier rapidement les cellules. Placez des nombres et coloriez certaines cellules comme ci-dessous. Enregistrez le classeur.

b) Allez dans l'éditeur Visual Basic et créez une procédure Worksheet_SelectionChange dans laquelle vous placerez le code suivant :

If Application.Intersect(Target, Range("tableau")) Is Nothing Then Exit SubExpliquez le rôle de l'instruction ci-dessus.

Page 14

http://www.erlandsendata.no/english/vba/ws/cellinrange.htm


c) Faites une boucle For Each … d'inspection de toutes les cellules de la plage "tableau". Pour chaque cellule coloriée ajoutez à la variable "somme" la valeur de la cellule. Affichez dans la cellule "résultat" la variable "somme".

2) Deuxième méthode :a) Ajoutez sur la feuille un bouton de commande cmdMiseàjour comme ci-dessous :

b) Ajoutez la procédure cmdMiseàjour_Click qui modifie la formule de la cellule nommée "résultat" en fonction des cellules coloriées.

Indications :Pour placer depuis Visual Basic une formule dans une cellule on utilise la propriété Formula. Exemple : Range("résultat").Formula="=$A$1+$B$2".Le problème est que Visual Basic ne reconnaît alors que la syntaxe anglaise pour les fonctions :Range("résultat").Formula="=sum(A1,B2)" au lieu de Range("résultat").Formula="=somme(A1;B2)" comme on pourrait s'y attendre.

EXERCICE 26. CRÉER UN DOCUMENT WORD ET Y INSCRIRE DES DONNÉES D'UNE FEUILLE DE CALCUL.

Pour ouvrir Word depuis VBA Excel il faut commencer par aller dans Visual Basic/Outils/Références et cocher l'item " "Microsoft Word 9.0 Object Library" (ou 10.0 si on dispose de la version d'Office XP). On aura ensuite accès depuis VBA Excel à tous les objets de VBA Word.

1) Faites une référence à Word dans Visual Basic comme indiqué ci-dessus. Créez ensuite dans votre feuille Excel une macro "créeDoc" qui ouvre Word, en reproduisant la syntaxe suivante :

Dim progword As Word.ApplicationSet progword = CreateObject("Word.Application")progword.Documents.Addprogword.Visible = True 'facultatif

2) Dans Word, créez à l'aide de l'enregistreur une macro qui écrive quelques mots et passe à la ligne. Editez cette macro pour apprendre la syntaxe de VBA Word qui permet d'écrire à l'endroit du curseur. Insérez alors dans votre procédure VBA Excel "créeDoc" les instructions qui permettent d'écrire quelques mots dans le document créé. Indication : progword.Selection.Typetext.

3) Modifiez la procédure "créeDoc" pour qu'elle écrive dans le nouveau document Word la liste des valeurs des cellules de la plage sélectionnée sur la feuille Excel.

4) Complétez "créeDoc" pour que le document soit sauvegardé sous un nom de votre choix et que Word soit automatiquement fermé.Indication : progword.ActiveDocument.SaveAs("chemin DOS vers le document de votre choix").

EXERCICE 27. SUPPRESSION DES LIGNES VIDES D'UNE SÉLECTION.

Le but de cet exercice est de créer une macro qui supprime toutes les lignes vides parmi les lignes sélectionnées.

1) Créez une fonction "testevide(plage as Range)" qui ne renvoie la valeur booléenne True que si toutes les cellules de l'objet Range "plage" ne contient que des cellules vides. Indications : Utilisez une boucle For Each pour inspecter les cellules de "plage". Utilisez la fonction IsEmpty pour tester si la cellule est vide. Dès qu'une cellule n'est pas vide sortez de la boucle avec l'instruction Exit For.

2) Nommez "supprimelignes" la macro qui supprimera les lignes vides de la sélection.

Page 15


a) Les premières lignes de la macro seront :Dim plage As RangeDim plageligne As RangeSet plage = SelectionIf plage.EntireRow.Address <> plage.Address Then Exit Subpremièreligne = plage.rowdernièreligne = premièreligne + plage.Rows.Count – 1

Expliquez le rôle exact de chacune des lignes précédentes, en vous aidant au besoin de l'aide de VBA.b) Complétez la macro pour qu'elles suppriment les lignes vides.

Indications :Vous ferez une boucle en inspectant les lignes de "dernièreligne" à "premièreligne". Il est en effet nécessaire de commencer par la dernière ligne pour que la suppression des éventuelles lignes vides ne viennent pas perturber la boucle. Utilisez la fonction "testevide" de la question précédente et la méthode delete.

EXERCICE 28. FORMULAIRE D'ACCÈS À DES DONNÉES D'UNE FEUILLE EXCEL

Le but de cet exercice est de montrer comment créer un formulaire (Userform) à l'intérieur d'un classeur Excel pour manipuler les données de ce classeur sans que l'utilisateur ait directement accès aus feuilles du classeur.1) Récupérez le classeur inclus dans le fichier compressé "communes.zip" situé sur le serveur. Ce classeur contient la liste

des communes françaises ainsi que leur code postal. Enregistrez-le dans votre dossier sous le nom "Cherche commune.xls".

2) Allez dans l'éditeur VBA et insérez un formulaire (Userform) dans lequel vous placerez les contrôles "cmdAfficheFeuille", "txtCommune", "lstCommunes" comme ci-dessous :

La liste simple "lstCommunes" a deux colonnes de largeurs respectives 140 pt et 425 pt.La suite de l'exercice mettra en place le comportement suivant : quand on rentre le nom d'une commune dans la zone de texte, celui-ci est cherché dans la feuille Excel des communes et, s'il est trouvé, il s'affiche avec son code postal dans la liste "lstCommunes" avec les 10 communes précédentes et les 10 communes suivantes.

3) On souhaite que les feuilles soient invisibles à l'ouverture du classeur et que seul le formulaire s'affiche.Placez pour cela dans la procédure événementielle adéquate le code suivant :

Application.Visible = FalseUserForm1.Show

Page 16


4) Ecrivez le code qui cache le formulaire et qui fasse réapparaître les feuilles par un clic sur le bouton "cmdAfficheFeuille".

5) La fermeture du formulaire ne ferme pas l'application Excel, même si elle demeure invisible. Il faut donc intercepter l'événement "clic sur la croix du coin supérieur droit du formulaire" pour fermer Excel. Le nom de cet événement est QueryClose. Ecrivez la procédure événementielle correspondante.

6) Nommez "communes" la plage correspondant à la liste des communes (sur une seule colonne). Le code suivant permet de chercher dans une plage donnée la première cellule contenant le texte indiqué. Si la cellule n'est pas trouvé une erreur est générée car la ligne "Set Cellule=…" ne peut pas être exécutée, d'où la ligne "On Error Resume Next ..".

lstCommunes.ClearOn Error Resume Next

Set cellule = plagecommunes.Find(What:=commune, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Testez ce code pour l'événement txtCommune_Change en utilisant un Msgbox montrant la cellule trouvée.

7) Pour afficher les communes et leur code postal dans la liste la méthode Additem ne suffit pas puisqu'il faut aussi renseigner la deuxième colonne de la liste. On utilisera donc la méthode "Column" qui permet de remplir n'importte qu'elle colonne de la liste dans une ligne déjà créée par Additem.

If Not cellule Is Nothing Then For I = 0 To 20 lstCommunes.AddItem cellule.Offset(10 - I, 0).Value lstCommunes.Column(1, I) = cellule.Offset(10 - I, 2).Value Next I End If

lstCommunes.ListIndex = 10

8) Pour terminer, on peut lancer Excel par l'intermédiaire d'un script vbs (ou batch), ce qui évitera de faire apparaître les feuilles un court moment avant que le formulaire s'affiche, comme c'était le cas avec la méthode précédente.

Expliquez le rôle des lignes "I=0 …" jusqu'à "dossier=…" puis testez ce script.

cheminScript=wscript.scriptfullname

I=0 do while instr(I+1,cheminScript,"\")>0 I=instr(I+1,cheminScript,"\")loopdossier=left(cheminScript,I)

set oXL=CreateObject("Excel.Application")oXL.Workbooks.Open (dossier & "cherche commune.xls")

9) Modifiez le code de la procédure événementielle txtCommune_Change afin qu'apparaisse dans la liste la première commune dont le nom commence par celui indiqué par la zone de texte. La méthode Find n'est pas adaptée, regardez plutôt l'aide concernant l'opérateur "Like" ou la remarque à la fin de l'aide sur la méthode "Find".

essai

Page 17