[PDF] UFR 06 M1 Gestion -? Informatique 1 / 13 - Fiche de TD VBA

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique1/13UniversitéParis1-PanthéonSorbonneFichedeTDVBAL'objectifdecetteficheest defaire unrappel(voireu neprésentationra pide)dula ngagedeprogrammationVBAetdesonusagesurdesdocumentsExceletAccess.Pourrappel,VBA(VisualBasicforApplications)estàlafoisunlangagedeprogrammationetunenvironnementd'exécutionattachésauxdocumentsMSOffice( Word,Excel ,Access...).Il nousp ermetd'enrichir lesdocumentsOfficea vecuncomportementditactif,telsquedesvérificationsoul'automatisationd'untraitement.Lesdocumentsdeviennentainsiplusdynamiques,pourdevenirpresquedesvéritablesapplications.DémarragePourcommencer,ilfautd'abordcomprendreenquoiVBApeutnousêtreutile.VBAsepositionnedanslasuitelogiquedes"macros».Lesmacros,quisontprisesenchargeparlesdocumentsauxformats.xlsm,.accdbouencore.docm,représententunesuited'opérationsréaliséesdemanièreséquentielle.Lorsqu'onexécuteunemacro,celle-cireproduitunensembled'actionsenregistréespréalablement.Or,l'enregistreurdemacronepeutenregistrerquedessuitesd'actions(qu'iltraduitd'ailleursenVBA).Dèsqu'ilfautfaireuntraitementunpeupluscomplexe(desvérificationssurunensemblededonnées,parexemple),ilfautpasserauVBA,carlesmacrosnepourrontpasenregistrertouslestraitements.PourfairedeVBA,ilfautavoiraccèsàsonenvironnementdeprogrammation,nomméVBE,quiestintégréauxapplicationsMSOffice.DansAccess,c'estfacile,leboutond'accèsàl'environnementVBEestdéjàdisponiblesurl'onglet"Outilsdebasededonnées»,illustréci-dessous.SurExcel,ilfautd'abordrendrevisiblel'onglet"Développeur»surleruban,pourqu'onytrouveleboutond'accèsàl'environnement("VisualBasic»).

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique2/13UniversitéParis1-PanthéonSorbonneActivitéOuvrirExcel,allersu rles"Options»à"PersonnaliserleRuban»et cocherlacase"Développeur»,commel'illustrelafigureci-contre:Unefoisvisiblel'onglet"Développeur»,ouvrirl'environnementVBEencliquantsurlebouton"VisualBasic».L'environnementci-dessousdevientalorsvisible.Maintenant,nousallonscréernotrepremier"code»VBA.Nousallonsprogrammerunpetitmessagedebienvenuequis'afficheralorsqu'onouvrelefichierExcel.Pourcefaire,ilnousfaudradoncprogrammerl'événement"Open»denotreclasseur.Dansl'environnementVBE,faireundoubleclicksurlemodu le"ThisWorkbook».Danslafenêtreques'ouvre,sélectionner"Workbook»danslepremiermenudéroulan t,et"Open»sur lesecond(normalement,Excelferacelapourvou s).Danscedeuxièmemenu,voustrouvereztouslesévènementsauxquelspeutrépondrel'objetWorkbook.Unefoissélectionnél'événement"Open»,l'environnementvousrempliradéjàledébutdevotrecode,avecladéclar ation delaprocédure(PrivateSubWorkbook_Open()...EndSub).Ilnousrestequ'àlaremplir.JPourcepremierexemple,nousallonsutiliserunefonctionofferteparVBAappeléeMsgBox.Celle-cipermetd'afficherdesfenêtresdedialoguesdedifférentstypes(simples,avecdeuxboutonsOK etCancel, etc.),enfonctiondesparamètresqu'onluidonne.Onl'utiliseradanssaversionPrivateSubWorkbook_Open()MsgBox"Bienvenueetbontravail!"EndSub

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique3/13UniversitéParis1-PanthéonSorbonnelaplussimple,avecjusteunmessageàafficher.Nousallonsdonctaperlaligneci-contredansnotreprocédureSubcommel'illustrelafigureci-dessous:Ilnousrestequ'àenregistrernotredocument(attentionauformat:ildoitêtreauformat.xlsm),refermerl'environnementVBAetleclasseur,etlerouvrirpourvoirsinotreévénementvabienmarcher(s'ilvabiensedéclencheràl'ouvertureduclasseur).Lorsdel'ouverturedudocument,Excelnousdemand erasionveut "activerlecontenu»(figureci-dessous),c'est-à-dire,autoriserlesmacrosetlecontenuVBAdudocumen t(àfaire uniquemen taveclesdocumentsdontonconnaîtla provenance ). Unefoisautorisélecontenu,nousdevronsvoirl'exécutiondenotreévénement"Open».ExercicesVBAsurExcel1) Améliorerl'exerciceprécédent,enajoutantaumessageaffichélenomdudocumentouvert.Pourréalisercetexercice,nousallonsmanipulerl'objetThisWorkbook,quireprésenteleclasseurouvert,etplusprécisément,sapropriété"Name»:ThisWorkbook.Name.Utiliserlesastucesdeconcaténationetdenouvelleligne(voirsection"Bonàsavoir»enfindudocument)pourajouterlenomdudocumentaumessage.Utiliserlebouton"exécuter»pourtestervotrecode.2) Améliorerencorepluslecodeprécédent,enajoutantaumessagelenomdefeuilleactive.Lorsqu'onouvreunclasseur,lafeuilleactiveestaccessibleàtraversl'objetActiveSheet,appartenantàThisWorkbook.Nousallons,ànouveau,utiliserlapropriétéName,cettefoispourl'ActiveSheet.Testervotrecodeàl'aidedubouton"exécuter».

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique4/13UniversitéParis1-PanthéonSorbonne3) Construireunefonctionquiretourneladatededemain.Afindeconstruirenotrepremièrefonction,nousallonsd'abordajouterunnouveaumoduleànotreprojetVBA(menu"insertion»ouclickdroitsurVBAProjectcommel'illustrelafigureci-dessous).Al'intérieurdecemodu le,nousallonsinsérernotrenouveaucode.Puisquecelui-cidoit retournerune valeur(ladate),n ousdevonscréeralorsuneFunctionqu'onappellera"DateDemain».Afind'obtenirladatededemain,ilfautd'abordtrouvercelled'aujourd'hui.Pourcela,nousallonsfaireappelàlafonction"Date»de VBA,dont lavaleurnous garderonsdansunevariablenommée"auj»detype"Date»(commenousavonsvuencours).Ladatededemaindevientdonc"auj+1».Sachantquelavaleurqueretourneraunefonctioncorrespondàlavaleurdelavariabledemêmenomquecettefonction,ilnousrestedoncàindiquer:"DateDemain=auj+1»(voirfigureci-dessous).Maintenantquenousavonsnotrefonction,ilsuffitdel'utiliserdansnotreclasseur.Ajouterdonclaformule"=DateDemain()»dansunecelluleduclasseurpourutiliservotrefonction.Attention:n'oubliezpasdeformatervotrecelluleentantquedate,sinonlacelluleprésenteraladatesoussaforme"numérique»(c'est-à-dire,lenombredejoursécoulésdepuis1/1/1970).

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique5/13UniversitéParis1-PanthéonSorbonne4) Construireunefonctionquivérifiesiuneannée(passéeenparamètre)estbissextileounon.Pourfairecetexercice,nousallonsd'abordremplirunecolonnedenotreclasseuravecdesannées:2000,2001,2002...2015.Puis,nousallonsconstruirenotrefonction,qu'onnommera"EstBissextile».L'idéeiciestderecevo irenparamètreunnumé rocorrespondantà uneannée(anneeAsInteger)etdonc derépondresiouiounoncetteannéeestbissextile(retour"AsBoolean»).Enprincipe,onditqu'uneannéeestbissextilesielleestdivisiblepar4.End'autrestermes,silerestedeladivisiondel'annéepar4estzéro,l'annéeestbissextile.Sinon,ellenel'estpas.Nousavonsdoncdeuxétapesàréaliser:d'abordrécupérerlerestedeladivision,puisvérifiersicelui-ciestégaleàzéroounon.Nouspouvonsréaliserlapremièreétapegrâceàl'opérateur"Mod»:l'opération"anneeMod4»nousdonneracommerésultatlerestedeladivision,qu'onpourrastockerdansunevariable.Pourtestersileresteestégalà0,ilnousfautréaliseruneinstructionconditionnelle"If...then...else»,quenousavonsvuencours.Enfin,commepourl'exerciceprécédente,lerésultatdelafonction(Truesil'annéeestbissextile, Falsesinon)doitêtrega rdédansuneva riabledumême nomdelafonction (EstBissextiledansnotrecas).Maintenantquenotrefoncti onestprête,o nval'utiliserpourvérifierlesvaleursdesannéessurnotreclasseur.ßAvantd'ajusterleformatdelacelluleAprèsl'avoirajustéàDimresteAsIntegerreste=anneeMod4

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique6/13UniversitéParis1-PanthéonSorbonne5) Améliorerl'exerciceprécéd entpourprendreenconsidératio nlesexceptions:le sannéesdivisiblespar100etpaspar400.L'algorithmequenousavonsutilisédansl'exerciceprécédentcomporteunefaille:lesannéesdivisiblespar4etpar100nesontpastousbissextiles.Parexemple,l'année1900oul'année2100:touteslesdeuxsontdivisiblespar4(1900÷4=475)etpar100(1900÷100=19),maisellesnesontpasbissextiles.Pourqu'uneannéedivisiblepar4etpar100soitbissextile,elledoitêtreaussidivisiblepar400.Nousdevonsdoncajouterdestestssupplémentaires,commel'illustrelediagrammed'activitésci-contre.Avousdejouer!J6) Créerunefonctioncapabledecomptercombiend'annéesbissextilesexistentdansunensembledecellulescontentantdesannées.L'objectifdecetexerciceestdepouvoirparcourirunensembledecellulescontentantdesannéesetdecomptercombien,parmicesannées,sontbissextiles(àl'aidedenotrefonctionEstBissextile).Pourlefaire,nousauronsbesoindeconnaîtrel'ensembledecellulesàparcourir.IlnousfautdoncunobjetRangeenparamètre,lequelvanousindiquerquellescellulesnousallonsregarder.Ensuite,ilvafalloirparcourirchacunedecescellulesàl'aided'uneboucle.DansVBA,lesbouclesdetype"ForEach...Next»,vuesencours,permettentdeparcourirunàunlesélémentsappartenantàunensemble(unecollection,parexemple).Onvadoncpouvoirl'utiliserpourregarderuneparunedenoscellules.ßuncompteur,pourcomptercombiendefoisonatrouvéuneannéebissextileForEachcelInunRangeNext

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique7/13UniversitéParis1-PanthéonSorbonnePourchacunedescellules,onvadevoirvérifier,àl'aided'un"If...Then»,silavaleurdelacelluleestBissextileounon,à l'aide delafonctionqu enousav onsfait précédemment.Sic'est bienlecas,onincrémentenotrecompteur(somme=somme+1).Unefoisterminéelaboucle,lecompteurcontiendracombiendefoisonatrouvéuneannéebissextile.Nousn'avonsqu'àretournerlavaleurenregistréesurlecompteur(SommeBissextile=somme).Puis,ilnousrestequetesternotrenouvellefonctionsurunerangéedecellulesdenotretableur.

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique8/13UniversitéParis1-PanthéonSorbonneExercicesVBAsurAccessNousallonsmaintenantexpérimenterl'usagedeVBAsurunebasededonnéesAccess.Lapremièreétapeseradetéléch arger,à partirdenotreEPI( https://cours.univ-paris1.fr/course/view.php?id=547),ledocument"GestionCommandes.accdb».Nousallonsutilisercettebasepournousexercices.1) Ouvrirlabasededonnées"GestionCommandes.accdb»etcréerunnouveauformulairepourlatable"articles».Lesformulaires,dansAccess,représententunmoyenfaciledecommuniqueraveclesutilisateurs.Chaqueformulaireestreprésentéparu nobjetForm,da nslequelnouspo uvonsajouterplusieur scontrôles(boutons,champsdetexte, casesàcocher,etc.), représentés pardesobjetsControl.On peutcré ern'importequelformulairedans Access.Cependant,celui-cipeut aussicréerunf ormulaireautomatiquementàpartird'unetable .Nousallonsutiliser parcette option,ensélecti onnantlatable"articles»etencliquantsurlebouton"formulaire»,situésurleruban"créer».UnefoisleformulairecrééparAccess,ilnousrestequ'àl'enregistreraveclenom"FormArticles».J2) Ajouter,auformulairerécemmentcréé,unboutonpermettantd'augmenterleprixdel'articlede10%.Pourajouterunnouveauboutonànotreformulaire,nousallonsdevoirmodifierlastructuredecelui-ci.Pourcela,ilnousfautd'abordchangersonmodeaffichageversle"modepage»ou"modecréation».Cesdeuxmodespermettentlamodificationd'unformul aire.Onvapouvoirensuiteajouterunbout on,àl' aidedela barre"contrôles»surleruban"outildeprésentationduformulaire».Pourlefaire,ilsuffitdechoisir"Bouton»surlabarre"contrôles»etdepositionnerlenouveauboutonsurleformulaire(parexemple,àcôteduchamp"prix»).Dèsqu'oncliquesurleformulaire,Accessnousproposerad'indiqueruncomportementpardefaultaunouveaubouton.Cecipermetd'ajouterfacilementdesboutonspourpa rcourirunet able,i mprimerunétatouencoree xécuter unemacro.Danslec as

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique9/13UniversitéParis1-PanthéonSorbonneprésent,nousallonscréer unnouveaucomportementquin'existepasencore.Nouspouvons donccliquersur"annuler».Pourl'instant ,notreboutonnefaitrien.Nous allonsdonclu iajouteruncomportemen t,à traver sl'événement"surclic»("Click»).Pourcela,ilfautallerdansle"modedecréation»etnousallonsafficherlespropriétésdubouton,encliquantsurlebouton"feuilledepropr iétés».Lapa letteave ctouteslespropriétésapparaît,ilnousrestedoncàchoisirl'onglet"événements»etcliquersurévénement"surclic».Accessnousguideraalor svers l'environnementVBE(enchoisissant"générateurcode»),da nslequelnous allonspouvoirprogrammer lecomportementdenotreboutonlorsqu'onluicliquedessus.Atravescebouton,nousvoulonsmodifierlavaleurduprix.Ilfautnousdoncd'abordêtrecapablederécupérerlavaleuractue lleduprixdel'arti cleaf fiché.Lescollections"Forms»et "Controls»nous permettrontd'accéderàcettevaleur:Nouspouvonsdoncgardercettevaleurdansunevariableetaugmenterlavaleurdecelle-cien10%.Puisilsuffitdemettreàjourlavaleurduchamp"prix».DimprixAsCurrencyprix=Forms("FormArticles").Controls("prix").Value

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique10/13UniversitéParis1-PanthéonSorbonne3) Ajouterunnouveauboutonauformulaireprécédentpermettantd'augmentertouslesprixdesproduitsdesproduitsde10%.Pourcetexercice,nousallonsrépéterleprocessusquenousavonsfaitdansl'exerciceprécédentpourajouterunnouveaubouton.Cequichangeiciestlecontenudel'événement:maintenant,ilnes'agitplusd'augmenterleprixd'unseularticle,maisceluidetouslesarticles.Ilnousfalloirdoncparcourirtouslesregistrespouraugmenterleprixdechacundesarticles.CecisefaitgrâceàunobjetRecordsetreprésentantnotretable"articles».Grâceàuneboucle"DoWhile»nousallonspouvoirparcourir,àpartirdupremierregistre,l'e nsemblederegistresdisponibles,tantqueledernierregistrenesoitpasatteint.Pourchaqueregi stre,nousallon srécupérerlavaleurdeso nprixetl'augmenterde10%,puismettreàjourlavaleurduprixsurlatable.OnmodifieleprixOnmetàjourlatableOnmetàjourleformulaireNomduboutonEvénementOnrécu pèrelavaleurduchamp"prix»d ansleformulaire"FormArticles»DimtableAsRecordsetSettable=CurrentDb().OpenRecordset("articles")table.MoveFirstDoWhileNOTtable.EOFtable.MoveNextLoop

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique11/13UniversitéParis1-PanthéonSorbonneBonàsavoirExplorateurd'objetsLorsqu'onveuttrouverunobjetetsavoirsespropriétésetopérations,onpeututiliserl'ex plora teurd'objet s.Celui-cipermetderechercherun objetouc lasseparsonnometindiquelespropriétésetopérationsdisponiblespourunobjetsélectionné.ConcaténationetnouvelleligneTouteslescommandesVBAdoivententrerdansuneseuleligne,cequin'estpastrèspratiquelorsqu'onaunelignedecodetroplongue.Afindepouvoir"casser»lacommandeenplusieurslignes,onvautiliserlecaractère"_»commeici:Inversement,sionsouhaiteregrouper(concaténer)plus ieurschaînesdecaractèresdansuneseule(parexemple,dansunmessagepourunMsgBox),onvapouvoirutiliserlecaractère"&»,commeici:Enfin,sionsouhaiteajouteràunechaînedecaractèreunenouvellelignepourqu'ellesoitaffichée(dansuneMsgBox,parexemple),onpeututiliserlafonction"Chr(10)»:PrivateSubWorkbook_Open()MsgBox"Bienvenueetbontravail!"_,vbInformationEndSubPrivateSubWorkbook_Open()MsgBox"Bienvenueet"&"bontravail!"EndSubPrivateSubWorkbook_Open()MsgBox&Chr(10)_&"bontravail!"_,vbInformationEndSub

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique12/13UniversitéParis1-PanthéonSorbonneDéclarationd'unevariableAfindedéclarerunevariabledansuncodeVBA,ilsuffitd'utiliserlemotclé"Dim».Onpeutluiassocier,àl'aidedumotclé"As»,untypededonnée(String,Date,Integer...)ouuneclasse(Range,RecordSet...).Danscederniercas,notrevariablegarde,enréalité,unobjet.Unefois,lavariabledéclarée,onpeutluiattribuerunevaleuràl'aidedel'opérateur"=».Attention,parcontre,s'ils'agitd'unobjet,l'attributionsefaitàl'aidedumotclé"Set».Obtenirlecontenud'unecelluleOnpeut accéderàn'i mportequellecelluleda nsun classeuràl'aiderdescollec tions.Lacollection Worksheetspermetderetrouverlesfeuillesdecalculparleurnom(ouposition,àcompteràpartirde1),alorsquel'objetActiveSheetnousoffrelafeuilleactive.Apar tird'unobjetWorksheet,on peutréc upérern'importequellecellule(ouplagedecellules),grâceàlacollectionCells,ouencorel'opérationrange().Unecellule(ouplagedecellule)vadoncêtrereprésentéeparunobjetRange,pourlequelonpeutsollicitersavaleuraveclapropriétéValue,ouencoremodifiersespropriétésgraphiques.Obtenirlecontenud'unchampdecontrôleLorsqu'onconstruitunformulaire,ilesttrèsutiledepouvoiraccéderaucontenuafficherdansuncontrôle(unchampdetexte,parexemple).Pourcela,deuxcollectionsvontêtreparticulièrementutiles.D'abord,lacollection"Forms»permetdeaccéderàchaqueformulairedisponibledansl'applicationparsonnom.Chaqueobjet"Form»possède,àsontour,unecollection,nommée"Controls»contenanttoussescontrôles(objets"Control»).Atraverscelle-ci,onpourradoncrécupérerlavaleurduco ntrôle (propriété"Value»)oulamodifier.Ilestimportantdesoulignerque,sileformulaireestattachéàunetabledansunebasededonnées,toutemodificationsurlecontenudeseschampsserarépercutéesurlecontenudelatable.Parcourirunetable,récupérersesregistresPourparcourir unetabledansunebasededonnées,ilfautd'a bordouvrirla tableenquestion.Celle-ciappa rtientàun ebasededonnéesprécise(objet"Database»).S'ils'agitdelabasededonnéescourante,ilsuffitdedemanderàl'objet"CurrentDB()».Danslecascontraire,ilfautd'abordouvrirlefichiercontenantlabasededonnées.DimiAsIntegerDimcelluleAsRangei=1Setcellule=Worksheets("Feuil1").Cells(1,1)DimfeuilleAsWorksheetSetfeuille=Worksheets("Feuil1")DimcelluleAsRangeSetcellule=feuille.Cells("A1")Setcellule=ActiveSheet.Cells(1,1)DimcelluleAsRangeSetcellule=ActiveSheet.range("A1:B1")MsgBoxcellule.Valuecellule.Font.Bold=Truecellule.Font.Size=14DimprixAsCurrencyDimcPrixAsControlSetcprix=Forms("FormArticles").Controls("prix")prix=cprix.ValueDimdbAsDatabaseSetdb=DBEngine.OpenDatabase("c:\docs\db.accdb")

UniversitéParis1-PanthéonSorbonne-UFR06M1Gestion-Informatique13/13UniversitéParis1-PanthéonSorbonneUnefoise npossessionde labonn ebasededonnes,ilfautouvrirlatablesouhaitée.Cettemanoeuvresefai tàl'aidedel 'opération"OpenRecordset»proposéeparlesobjet sDatabase.Latable,commeunere quêteSQL,vaêtre représentéeparunobjet"Recordset»,représentantunensembled'enregistrements.Unefoisenpossessiondel'objetRecordsetcorrespondantàlatable,ilestpossibledelaparcouriràl'aidedesopérations"MoveFirst»et"MoveNext».Chaqueregistresecomportantcommeunecollection,onpeutaccéderàchaquechamp(attribut)parsonnom. Lafindu Registreset,quantàelle ,est indiquéeparlapropriété "EOF»(s iEOF=Tr ueona atteint ledernierregistre).Iles tégalementp ossibledemodifierchaquereg istreaufuretàmesurequ'onlepar court,àl'aided esopérat ions"Edit»et "Update».Enfin,ilestposs ibledes avoircombien deregistresunRecordsetpossèdeàl'aidede l'opérat ion"RecordCount»et mêmederechercherunregistreenparti culierdan sleRecordSetàl' aidedel'opération"FindFirst».DimtableAsRecordsetSettable=CurrentDb().OpenRecordset("articles")DimtableAsRecordsetSettable=db.OpenRecordset("Select*Fromarticles")table.MoveFirstDoWhileNOTtable.EOFMsgboxtable("noma")table.MoveNextLoopIftable.RecordCount>0Thentable.MoveFirstDoWhileNOTtable.EOFtable.Edittable("prix")=10table.Updatetable.MoveNextLoopEndIftable.FindFirst"codeart=W123"IfNOTtable.NoMathThentable.Edittable("noma")="clous"table.UpdateEndIf