Cours Excel 4

EXCELChapitre 4 : Le langage Visual Basic Application

Claude Duvallet

Universit du HavreUFR des Sciences et Techniques

25 rue Philippe LebonBP 540

76058 Le Havre CedexCourriel : [email protected]

Claude Duvallet 1/35 EXCEL

IntroductionLes macro-commandes

Structures de base en VBALes objets et les instructions

Gnralits sur VBA (1/2)

VBA = Visual Basic pour Applications.

VBA est le langage de programmation des applications deMicrosoft Office.

VBA permet dautomatiser les tches, de crer des applicationscompltes, de scuriser vos saisies et vos documents, de crerde nouveaux menus et de nouvelles fonctions.VBA utilise le mme langage que Microsoft Visual Basic.

VB est un ensemble complet qui permet de dvelopper desapplications indpendantes et librement distribuablesune application VBA est compltement lie au logiciel sous lequelelle a t cre (une application VBA cre sous Excel ne pourrapas se lancer sur un poste si Excel nest pas install).




Gnralits sur VBA (2/2)

Avant Excel utilisait son propre langage de programmation et uneapplication tait appele "macro". Ce terme est rest, mais unemacro Excel ralise en VBA nest rien dautre quune procduretelle quelles sont ralises sous VB.

VBA manipule des objets Excel tels que les objets Workbook(classeur), Worskheet (Feuille de calcul), Range(plage decellule), etc.

VBA, langage puissant, souple et facile utiliser permet deraliser trs rapidement des applications.




Lenregistreur de macro-commandesLditeur de macro-commandescriture de code VBALe vocabulaire

Lenregistreur de macro-commandes (1/3)Excel, comme Word ou PowerPoint possde un outil :lenregistreur de macros.Il cre une macro et transforme en langage VBA toutes lescommandes effectues par lutilisateur dans lapplication hte.Il permet dautomatiser sans aucune connaissance de laprogrammation certaines de vos tches et galement de sefamiliariser avec le langage VBA.Pour lancer lenregistreur de macro, il vous suffit dans le menuOutils de slectionner loption Macro puis Nouvelle macro.





Lenregistreur de macro-commandes (2/3)

Nom de la macro : Il est possible de renommer la macro. Le nom de lamacro doit commencer par une lettre et ne doit pas contenirdespaces. Utilisez le caractre de soulignement pour sparer lesmots.

Touche de raccourci : Il est possible de crer un raccourci clavier pourlancer la macro en saisissant une lettre (minuscule ou majuscule).

Enregistrer la macro dans : Ce classeur : la macro ne pourraensuite sexcuter que si le classeur dans lequel la macro a tenregistre est ouvert.Le "classeur de macros personnelles" : un nouveau classeurest cr et enregistr dans le dossier "xlstart" de faon que vosmacros soient disponibles chaque fois que vous utilisezExcel.

Description : Vous pouvez donner une description votre macro.





Lenregistreur de macro-commandes (3/3)

Vous pouvez ensuite dmarrer et arrter lenregistrement de lamacro au moyen des boutons de la barre doutils "Macro".

Toutes les actions effectues pendant la priodedenregistrement seront inscrites dans la macro en langage VBAmme les erreurs ou les annulations.

Vous pouvez ensuite diter le code de la macro pour lexaminerou encore le modifier.





Lditeur de macro-commandes (1/3)

Lditeur de macro, ou VBE (Visual Basic Editor) estlenvironnement de programmation de VBA.

Il se lance par le menu Outils puis loption Macro et ensuiteloption Visual Basic Editor ou par le raccourci clavier Alt+F11.






1 Fentre VBAProject Elle prsente les diffrents projets ouvertset permet de naviguer facilement entre vos diffrentes feuilles decodes VBA.

2 Fentre Code Cest lendroit ou vous allez saisir votre code VBA.3 Fentre Proprits Proprits de lobjet slectionn.4 Fentre Excution Elle permet de tester une partie du code. Elle

peut savrer trs utile pour voir comment sexcutent certaineslignes de code.





criture de code VBA (1/3)

Le code VBA scrit dans les modules lintrieur de procduresou de fonctions.

Dans VBE, il faut commencer par crer un nouveau module parle menu Insertion et loption Module. Renommer le module laide de la fentre proprits, la recherche de vos procduressera plus rapide.

Une procdure est une suite dinstructions effectuant des actions.Elle commence par Sub + NomDeLaProcdure et se terminepar End Sub. Le nom des procdures doit commencer par unelettre et ne doit pas contenir despaces. Utilisez le caractre desoulignement pour sparer les mots.

Pour dclarer une procdure, taper Sub et son nom puis taperEntre. VBE ajoute automatiquement les parenthses et la ligneEnd Sub.





criture de code VBA (2/3)Une fonction est une procdure qui renvoie une valeur. Elle sedclare de la mme faon quune procdure.

En gnral, on crit une instruction par ligne.

Il est possible dajouter des lignes de commentaire entre leslignes dinstruction ou au bout de celles-ci. Les commentairessont prcds dune apostrophe et prennent une couleurdiffrente.

Il ny a pas de limite de caractres pour chaque lignedinstruction. Il est toutefois possible dcrire une instruction surplusieurs lignes afin daugmenter la visibilit du code. Pour cela, ilfaut ajouter le caractre de soulignement avant le passage laligne.

Loption Info express automatique permet dafficher lesinformations relatives la fonction que vous venez de taper.





criture de code VBA (3/3)

Il est galement possible dobtenir de laide tout moment par lacombinaison de touches Crtl+j.

La vrification automatique de la syntaxe vous alerte si il y a uneerreur dans lcriture du code et la ligne de code change decouleur.

Chaque procdure Sub ou Function peut tre appele denimporte quelle autre procdure du projet. Pour restreindre laporte dune procdure au module, dclarez-la Private.

lintrieur de vos procdures, crivez vos instructions enminuscules, VBE se chargera de transformer votre code par desmajuscules.





Les objetsVBA manipule les objets de lapplication hte. Chaque objet possdedes proprits et des mthodes.

Chaque objet reprsente un lment de lapplication : unclasseur, une feuille de calcul, une cellule, un bouton, etc.Par exemple, Excel reprsente lobjet Application, Workbooklobjet classeur, Worksheet lobjet feuille de calcul, etc.Tous les objets de mme type forment une collection comme, parexemple, toutes les feuilles de calcul dun classeur. Chaquelment est alors identifi par son nom ou par un index.Par exemple, pour faire rfrence la Feuil2, on va utiliserWorksheets(2) ou Worksheets("Feuil2")Chaque objet peut avoir ses propres objets. Un classeur possdedes feuilles qui possdent des cellules. Pour faire rfrence unecellule, on peut donc utiliser :Application.Workbooks(1).Worksheets("Feuil2").Range("A1")





Les proprits

Une proprit correspond une particularit de lobjet.

La valeur dune cellule, sa couleur, sa taille, etc. sont desproprits de lobjet Range.

Les objets sont spars de leurs proprits par un point. Oncrira ainsi Cellule.Proprit=valeur.

Mettre la valeur 10 dans la cellule A1Range("A1").Value = 10

Une proprit peut galement faire rfrence un tat de lobjet.Par exemple, si on veut masquer la feuille de calcul "Feuil2", oncrira :

Worksheets("Feuil2").Visible = False





Les mthodesOn peut considrer quune mthode est une opration queralise un objet.Les mthodes peuvent tre considres comme des verbes telsque ouvrir, fermer, slectionner, enregistrer, imprimer, effacer, etc.Les objets sont spars de leurs mthodes par un point. Parexemple, pour slectionner la feuille de calcul nomm "Feuil2", oncrira : Worksheets("Feuil2").Select

Lorsque lon fait appel plusieurs proprits ou mthodes dunmme objet, on fera appel au bloc dinstruction With ObjetInstructions End With. Cette instruction rend le code souventplus facile lire et plus rapide a excuter.

Mettre la valeur 10 dans la cellule A1, la police en graset en italique et copier la cellule.With Worksheets("Feuil2").Range("A1")

.Value = 10

.Font.Bold = True

.Font.Italic = True

.CopyEnd With




Les vnementsLes messagesLes variables

Les vnements (1/3)

Pour quune macro se dclenche, il faut quun vnement (un clicsur un bouton, louverture dun classeur, etc.) se produise. Sansvnements, rien ne peut se produire.

Il existe deux types dvnements : les vnements lis auxobjets et les vnements lis aux objets.Les principaux objets pouvant dclencher une macro sont :

Un classeurUne feuille de travailUne boite de dialogue

Chacun de ces objets possde leur propre module. Pour yaccder, il faut lancer lditeur de macro.





Les vnements (2/3)

Pour crer une procdure vnementielle lie un classeur,slectionner le classeur ThisWorkbook puis cliquez sur licne 3(ou plus simplement double-clic sur ThisWorkbook).

Vous accdez ainsi au module li lobjet. SlectionnezWorkbook dans la liste 1 puis sur lvnement dsir dans laliste 2.





Les vnements (3/3)

La cration dune procdure vnementielle lie une feuille decalcul se fait de la mme faon.

Une macro peut galement tre dclenche une heure donne(OnTime) ou lorsque lutilisateur appuie sur une touche (OnKey).

Le dclenchement dune macro nomme "Test" 15 Heures sefait par la ligne dinstruction suivante : Application.OnTimeTimeValue("15:00:00"), "Test".

Le dclenchement dune macro nomme "Test" lorsquelutilisateur appuie sur la touche "F1" se fait par la lignedinstruction suivante : Application.OnKey "F1", "Test".





Les messages (1/4)

Lors dune procdure, les messages servent communiqueravec lutilisateur.

Il existe des messages qui donnent de linformation et dautresqui en demandent.

Les MsgBox peuvent simplement donner une information. Laprocdure est alors stoppe tant que lutilisateur na pas cliqusur le bouton.

Le texte peut-tre affich sur plusieurs lignes en utilisant le coderetour chariot chr(13) ou le code retour ligne chr(10).

Vous pouvez ajouter une icne concernant le type de message afficher.





Les messages (2/4)

Les MsgBox peuvent galement demander une information lutilisateur. Dans ce cas, la bote de message comprendplusieurs boutons.

La syntaxe est MsgBox ("Message", attribut bouton ).

Vous pouvez galement y ajouter les icnes et personnaliser letitre de la fentre en utilisant la syntaxe : Msgbox ("Message",attribut bouton + attribut icne, "titre de lafentre").





Les messages (3/4)

Les InputBox sont des botes de dialogue dans lesquelleslutilisateur est invit entrer des donnes. La syntaxe est :InputBox ("Message").

Comme pour les MsgBox, vous pouvez changer le titre de lafentre.





Les messages (4/4)

Vous pouvez galement entrer une valeur par dfaut dans lazone de saisie. La syntaxe devient : InputBox ("Message","Titre de la fentre", "Valeur par dfaut").

La valeur saisie peut tre rcupre dans une variable. Silutilisateur clique sur le bouton "Annuler", la variable renvoie unechane de longueur nulle ("").

Vous pouvez galement crire un message dans la barre dtatde lapplication. La syntaxe est : Application.StatusBar ="Message".

la fin de la procdure, pensez supprimer le message de labarre dtat par la ligne dinstruction : Application.StatusBar= False.





Les variables (1/3)

Lors dune procdure, les variables servent stocker toutessortes de donnes (des valeurs numriques, du texte, desvaleurs logiques, des dates, ...).

Elles peuvent galement faire rfrence un objet.

Suivant les donnes que la variable recevra, on lui affectera untype diffrent.

Pour rendre obligatoire la dclaration de variables, placezlinstruction Option Explicit sur la premire ligne du moduleou cochez loption Dclaration des variables obligatoires dansle menu Outils sous-menu Options de lditeur de macros.

La dclaration explicite dune variable se fait par le mot Dim(abrviation de Dimension).





Les variables (2/3)

Le nombre maximum de caractres du nom de la variable est de255. Il ne doit pas commencer par un chiffre et ne doit pascontenir despaces. La syntaxe est Dim NomDeLaVariable asType.

La porte dune variable est diffrente suivant lendroit et la faondont elle est dclare.

Une variable dclare lintrieur dune procdure est dite"Locale". Elle peut-tre dclarer par les mots Dim, Static ouPrivate.

Ds que la procdure est termine, la variable nest plus chargeen mmoire sauf si elle est dclare par le mot Static.

Une variable locale est gnralement place juste aprs ladclaration de la procdure.





Les variables (3/3)

Une variable peut tre "locale au module" si celle-ci est dclareavant la premire procdure dun module. Toutes les procduresdu module peuvent alors lui faire appel. Elle est dclare par lesmots Dim ou Private.

Une variable peut galement tre accessible tous les modulesdun projet. On dit alors quelle est publique. Elle est dclare parle mot Public. Elle ne peut pas tre dclare dans un module deFeuille ou dans un module de UserForm.

Une variable peut garder toujours la mme valeur lors delexcution dun programme. Dans ce cas, elle est dclare parles mots Const ou Public Const.




Les objetsLes instructions conditionnellesLes bouclesLes fonctions de texte

Les classeurs et les feuilles de calcul

Les classeurs sont dsigns par le mot Workbook. Ils peuventtre ouverts, ferms, enregistrs, activs, masqus, supprims,...par une instruction VB.

Les feuilles de calcul sont dsignes par le mot Worksheet.Comme les Workbook, ces objets possdent de nombreusesproprits et mthodes.





Les cellules

Une plage de cellules est dsigne par lobjet Range. Pour fairerfrence la plage de cellule "A1 :B10", on utiliseraRange("A1:B10").Lobjet Range permet galement de faire rfrence plusieursplages de cellules non contigus.Pour faire rfrence une seule cellule, on utilisera lobjetRange("Rfrence de la cellule) ou Cells(Numro deligne, Numro de colonne).VB vous permet galement de changer le format des cellules(polices, couleur, encadrement ...). partir dune cellule de rfrence, vous pouvez faire appel auxautres cellules par linstruction Offset. La syntaxe estRange(Cellule de rfrence).Offset(Nombre delignes, Nombre de colonne).Les arguments (Nombre de lignes, Nombre de colonnes) delinstruction Offset sont facultatifs et leur valeur par dfaut est 0.





Les instructions conditionnelles

Elles peuvent dterminer la valeur que prennent les variables,arrter une procdure, appeler une procdure, quitter une boucle,atteindre une tiquette.

Linstruction la plus courante dans VB est la condition Ifcondition Then instruction_vrai.

Si la valeur vraie possde plusieurs lignes dinstructions, lasyntaxe devient If condition Then instructions_vraiEnd If.

Une autre possibilit de syntaxe est la suivante : If conditionThen instructions_si_vrai Else instructions_si_fauxEnd If.

Dans le cas de conditions multiples, il est prfrable dutiliser lebloc dinstruction Select Case expression Case valeurinstructions Case Else instructions End Select.





Les boucles (1/2)

Les boucles le plus souvent utiliss sont les boucles For ... Next.

Elles permettent de rpter un nombre de fois dfini un blocdinstructions.

Elles utilisent une variable qui est incrmente ou dcrmente chaque rptition.

La variable peut tre incrmente dune valeur diffrente de 1 parle mot Step.

La variable peut galement tre dcrmente, le mot Step estalors obligatoire.





Les boucles (2/2)

lintrieur dun bloc dinstruction For Next, linstruction ExitFor, peut quitter la boucle avant que la variable nest atteint sadernire valeur.

Pour rpter un bloc dinstructions pour chaque objet dunecollection ou pour chaque lment dun tableau, on utilisera lebloc dinstruction For Each Objet In Collection Next.

On peut galement utiliser linstruction Exit For pour sortir dunbloc dinstruction For Each ... Next.





Les boucles conditionnellesLes boucles While condition instructions Wend excutentun bloc dinstruction tant que la condition est vraie.

Les boucles Do Loop sont mieux structures que les bouclesWhile Wend. On peut tout moment sortir dune boucle DoLoop par linstruction Exit Do.

La boucle Do While condition instructions Loop excute un blocdinstruction tout pendant que la condition est vraie.

Dans la boucle Do instructions Loop While condition, lebloc dinstruction est excut une fois avant que la condition soitteste.

Pour sortir dune boucle, on utilise linstruction Exit Do.

Les boucles Do Until sont identiques aux boucles Do While,seulement le bloc dinstruction est rpt tout pendant que lacondition nest pas vraie.





Les fonctions de texte (1/3)

VBA possde des fonctions permettant dextraire une chane decaractres dun texte.

La fonction Len renvoie le nombre de caractres dun texte.

La fonction Left renvoie un nombre de caractres en partant dela gauche. La syntaxe est Left(Texte, Nombre de caractres).

La fonction Right renvoie un nombre de caractres en partant dela droite. La syntaxe est Right(Texte, Nombre de caractres).

La fonction Mid renvoie un nombre de caractres en partant duncaractre dfini. La syntaxe est Mid(Texte, Dpart, Nombre decaractres). Si le Nombre de caractres nest pas indiqu, lafonction renvoie tous les caractres partir de la position dpart.

La fonction LTrim supprime les espaces se trouvant avant lachane de caractres.





Les fonctions de texte (2/3)

La fonction RTrim supprime les espaces se trouvant aprs lachane de caractres.

La fonction Trim supprime les espaces se trouvant avant et aprsla chane de caractres.

La fonction Ucase convertie le texte en majuscules.

La fonction Lcase convertie le texte en minuscules.

La fonction Application.Proper convertie le texte en nom propre.

La fonction Replace permet de remplacer une chane decaractres par une autre. Cette fonction possde des argumentsfacultatifs permettant de dterminer la position du premierremplacement et le nombre de remplacement effectuer. Lasyntaxe est Replace(Texte, Chane remplacer, chane deremplacement, Dpart, Nombre de remplacement).





Les fonctions de texte (3/3)La fonction Val renvoie la valeur numrique dune chane decaractres. Si la chane de caractres est compose de chiffreset de lettres, la valeur sarrte au premier caractre nonnumrique.

La fonction IsNumeric permet de tester si une chane decaractres est numrique et renvoie une valeur de type Boolean.

La fonction IsDate permet de tester si une chane de caractresest une date. Elle renvoie une valeur de type Boolean.

Certaines fonctions permettent de convertir des donnes en duntype dfini. Par exemple, CDate va convertir des donnes endate.

Le format des dates et heures est dfini par la fonction Format.La syntaxe est Format(MaDate, Format). La fonction Formatpermet galement de formater les nombres.





Remerciements

Cette prsentation est appele tre enrichie et amliore.Cette prsentation a t ralise au moyen de divers supports dontcertains trouvs sur Internet et notamment ceux qui suivent :http://www.lecompagnon.info/excel/index.htmlhttp://perso.wanadoo.fr/jml85/Pages/cours_VBA.htm


Excel et le langage VBAIntroductionLes macro-commandesL'enregistreur de macro-commandesL'diteur de macro-commandescriture de code VBALe vocabulaire

Structures de base en VBALes vnementsLes messagesLes variables

Les objets et les instructionsLes objetsLes instructions conditionnellesLes bouclesLes fonctions de texte

Documents

Cours Excel 4