cours EXCEL VBA - AgroParisTech

1 Le Tableur EXCEL La Programmation en VBA Juliette Dibie d informatique PLAN I. Introduction EXCEL VBA _____ 1 II. L enregistreur de macro _____ 2 Enregistrer une macro _____ 2 Ex cuter une macro _____ 4 Exercice _____ 4 III. L environnement visual Basic Editor _____ 5 L explorateur de projets _____ 6 Les modules de code _____ 7 Les proc dures _____ 8 Modifier une macro _____ 9 Exercice _____ 10 IV. Les divers moyens pour ex cuter une macro _____ 11 Ex cuter une macro partir d'un raccourci clavier _____ 11 Ex cuter une macro partir d'un bouton d'une barre d'outils _____ 12 Ex cuter une macro partir d'un menu _____ 14 V. Le mod le objet d EXCEL _____ 16 Les objets _____ 16 Les collections _____ 16 L acc s aux objets _____ 17 Exercice _____ 17 Les propri t s des objets _____ 18 Exercice _____ 18 Les m thodes des objets _____ 19 Exercice _____ 19 Les v nements _____ 20 Exercice_____ 22 L explorateur d objets _____ 23 Comment obtenir de l aide : quelques astuces _____ 25 VI.

2 Les fonctions d finies par l utilisateur _____ 26 Cr er une Fonction personnalis e _____ 27 Exercice _____ 28 Utiliser les fonctions int gr es d EXCEL _____ 29 Exercice _____ 30 VII. Le langage VBA _____ 31 Les variables et les constantes _____ 31 La d claration des variables_____ 31 Le type des variables _____ 32 La port e des variables _____ 34 La d claration des constantes _____ 34 L instruction With _____ 36 Exercice _____ 36 Ex cuter une proc dure sans argument _____ 37 Les entr es et sorties standards _____ 38 La fonction MsgBox _____ 38 La fonction InputBox_____ 40 R capitulatif sur les parenth ses et les listes d arguments _____ 41 Exercice_____ 42 Les nonc s conditionnels _____ 43 L instruction If crite sur une seule ligne_____ 43 Le bloc If _____ 43 Les nonc s it ratifs _____ 45 La boucle _____ 45 Exercice _____ 45 La boucle _____ 46 La boucle For _____ 47 L instruction Exit

3 _____ 47 Exercice _____ 47 Les tableaux _____ 48 Exercice _____ 48 Le d bogage _____ 49 Les erreurs de compilation et d ex cution _____ 49 Les erreurs Logiques _____ 49 VIII. Les objets UserForm _____ 53 Cr er un objet UserForm _____ 55 Afficher et fermer un objet UserForm _____ 57 Afficher un objet UserForm _____ 57 Fermer ou masquer un objet UserForm _____ 57 Associer du code un objet UserForm _____ 58 Associer du code un bouton de commande _____ 59 Exercice _____ 59 Initialiser un objet UserForm _____ 60 Acc der aux contr les d un objet UserForm _____ 62 Afficher un objet UserForm partir d un bouton d une feuille de calcul _____ 64 IX. Bibliographie _____ 65 EXCEL VBA AgroParisTech - Juliette Dibie Page 1 I. INTRODUCTION A EXCEL VBA EXCEL VBA ( visual Basic pour Application) est un langage de programmation permettant d utiliser du code visual Basic pour ex cuter les nombreuses fonctionnalit s de l Application EXCEL .

4 Un programme crit en VBA est souvent appel une macro. Les macros permettent notamment d automatiser des t ches r p titives r alis es sous EXCEL . Elles peuvent aussi tre utilis es pour cr er des bo tes de dialogue afin de rendre une application d velopp e sous EXCEL plus conviviale. Une macro peut tre cr e en utilisant l'enregistreur de macros, qui ne n cessite aucune connaissance du langage VBA. Cependant une macro ainsi cr e ne s ex cutera que sur un ensemble de cellules donn es et le code produit ne sera pas toujours tr s efficace. Pour pouvoir cr er des macros propres ses besoins, efficaces et interactives, il faut apprendre programmer en VBA. EXCEL VBA AgroParisTech - Juliette Dibie Page 2 II. L ENREGISTREUR DE MACRO L enregistreur de macro permet d crire du code VBA partir d op rations effectu es manuellement sous EXCEL .

5 ENREGISTRER UNE MACRO Cr er une macro MACRO1 qui crit dans la cellule A1 le texte " AgroParisTech " en caract res gras et dans la cellule G1 la date du jour en caract res italiques. Avant de commencer l enregistrement, il faut se poser plusieurs questions : penser la mani re d effectuer les op rations manuelles ; se demander quand commencer l enregistrement. Dans notre cas, la s lection de la cellule A1 fait partie de l enregistrement puisqu on veut que le texte " AgroParisTech " se trouve dans cette cellule. Si l enregistrement ne commence pas par la s lection de la cellule A1, le texte tap est crit dans la cellule active. se demander quand arr ter l enregistrement. Question : que faire pour que la cellule active apr s l ex cution de la macro soit la cellule A1 ?

6 1) Ouvrir un nouveau classeur et l enregistrer sous 2) Les cellules dans une macro sont identifi es l aide d une lettre (colonne) suivie d un chiffre (ligne), comme par exemple la cellule A1. Afin de faciliter la lecture du code VBA g n r , il est pr f rable de choisir la m me identification des cellules dans le classeur dans lequel est enregistr la macro. Si tel n est pas le cas, activer la commande OUTILS OPTIONS, cliquer sur l'onglet GENERAL et d cocher la case STYLE DE REFERENCE L1C1. 3) Positionner le curseur sur une autre cellule que la cellule A1 de la feuille de calcul FEUIL1. 4) Activer la commande OUTILS MACRO NOUVELLE MACRO. 5) Taper MACRO1 dans la zone NOM DE LA MACRO. EXCEL VBA AgroParisTech - Juliette Dibie Page 3 6) Dans la zone ENREGISTRER LA MACRO DANS, s lectionner l'option CE CLASSEUR ; la macro MACRO1 n est alors disponible que dans le classeur Remarque : La zone ENREGISTRER LA MACRO DANS permet de d terminer l endroit o la macro MACRO1 sera enregistr e.

7 Il existe trois options : Avec l'option CLASSEUR DE MACROS PERSONNELLES, la macro est enregistr e dans un classeur sp cial appel Ce classeur est ouvert automatiquement lors du lancement d EXCEL et ses macros sont donc disponibles dans tous les classeurs ouverts. La commande FENETRE AFFICHER permet d afficher ce classeur et la commande FENETRE MASQUER de le masquer. L option NOUVEAU CLASSEUR permet d enregistrer la macro dans un nouveau classeur. L option CE CLASSEUR permet d enregistrer la macro dans le classeur actif. 7) Cliquer sur le bouton OK. 8) La barre d'outils ARRET DE L'ENREGISTREMENT appara t, ce qui marque le d but de l'enregistrement. Par d faut, l'enregistrement d'une macro s effectue en utilisant des r f rences absolues aux cellules.

8 Remarque : Il est possible d enregistrer une macro en utilisant des r f rences relatives, en cliquant sur le bouton REFERENCE RELATIVE de la barre d'outils ARRET DE L'ENREGISTREMENT. 9) Positionner le curseur sur la cellule A1, taper le texte " AgroParisTech ", valider avec le bouton et s lectionner le style gras (GG). 10) Positionner le curseur sur la cellule G1, taper la formule =AUJOURDHUI(), valider avec le bouton et s lectionner le style italique (II). 11) Arr ter l'enregistrement en cliquant sur le carr bleu de la barre d'outils ARRET DE L'ENREGISTREMENT. NE JAMAIS OUBLIER D ARRETER L ENREGISTREMENT D UNE MACRO. EXCEL VBA AgroParisTech - Juliette Dibie Page 4 EXECUTER UNE MACRO 1) Effacer le contenu des cellules A1 et G1.

9 2) Positionner le curseur sur une autre cellule que la cellule A1 de la feuille de calcul FEUIL1. 3) Activer la commande OUTILS MACRO MACROS. 4) S lectionner la macro MACRO1 et cliquer sur le bouton EXECUTER. POUR SUPPRIMER UNE MACRO, ACTIVER LA COMMANDE OUTILS MACRO MACROS, SELECTIONNER LA MACRO A SUPPRIMER ET CLIQUER SUR LE BOUTON SUPPRIMER. EXERCICE Cr er une macro MACROMOIS qui crit les noms de mois Janvier, F vrier et Mars en colonne partir de la cellule active. Utiliser l enregistreur de macro avec r f rence relative aux cellules. EXCEL VBA AgroParisTech - Juliette Dibie Page 5 III. L ENVIRONNEMENT visual BASIC EDITOR Nous avons vu comment cr er une macro l aide de l enregistreur de macro.

10 Nous allons maintenant examiner le code VBA produit. Pour ce faire, il faut utiliser l diteur de visual Basic, visual BASIC EDITOR (VBE), qui s ex cute dans une fen tre diff rente de celle d EXCEL . Ouvrir VBE en activant la commande AFFICHAGE BARRE D'OUTILS visual BASIC et en cliquant sur l'objet visual BASIC EDITOR. explorateur de projets fen tre propri t s module de code EXCEL VBA AgroParisTech - Juliette Dibie Page 6 L EXPLORATEUR DE PROJETS A chaque classeur EXCEL ouvert est associ un projet VBA. L'explorateur de projets affiche une liste hi rarchis e des diff rents projets VBA associ s aux classeurs EXCEL ouverts. Un projet VBA associ un classeur regroupe les l ments du classeur, comme ses feuilles de calcul ou des bo tes de dialogue, et les proc dures et les fonctions associ es au classeur et stock es dans un ou plusieurs modules de code.