VBA

Initiation VBA pour Excel

Philippe BernardIngnierie Economique et Financire

Universit Paris-Dauphine

Septembre 2008

Chapitre 1

Rfrences

F. Riva Applications nancires sous Excel en Visual Basic, Economica2me dition(ouvrage qui en 200 pages russit la performance de prsenter de ma-nire trs complte le langage VBA, puis de faire des applications la gestion de portefeuille, lvaluation des options, lassurance deportefeuille et le cience des marchs - les notes ci-dessous suiventsouvent trs troitement la prsentation de VBA de cet ouvrage)

pour dautres ouvrages ou rfrences consultez les pages ressources ennance et en conomie du master Ingnierie Economique et Financire

http ://www.dauphine.fr/dfrea/master_ace/plan.html

parmi les ouvrages trs complets consacrs VBA sous Excel, B. Jelen& T. Syrstad VBA and macros for Microsoft Excel (ou J. WalkenbachVBA pour Excel 2003 )

On peut se rfrer louvrage de C. Albright VBA for modelers, Thom-son, 2nd dition

3

4 CHAPITRE 1. RFRENCES

Chapitre 2

VBA un langage objet

Depuis Excel 97, un diteur Visual Basic rside sous Excel et permetdcrires des programmes complexes. En fait, cette proprit est vraie pourtoutes les applications (Word, Excel, Power Point, Access) de Microsoft Of-ce. VBA, Visual Basic for Applications, le langage utilis peut tre vucomme lapplication dun langage, Visual Basic, un ensemble dapplica-tions, une collection dobjets, quil manie. Il ne faut pas confondre a priorile langage Visual Basic, qui est un langage complexe opramment indpen-damment, de VBA en gnral, de VBA pour Excel en particulier.

2.1 Le VBE

Pour activer lditeur de Visual Basic depuis Excel, dans la barre de menuExcel :

A chage! Barre doutils! VisualBasicet on clique ensuite sur licne Visual Basic Editor (VBE). La fentre apparatalors par dfaut dispose comme dans la gure 2.2.On peut faire apparatre la fentre des codes en cliquant bouton droit

sur This Workbook, situ dans la fentre Explorateur de projets (en haut gauche), puis en cliquant en choisissant dans les menus Insertion puis Module.Dans la fentre de code (en haut droite), on peut crire le code mais aussides commentaires. Ceux-ci sont le texte qui apparat droite dun guillemetsimple

0.

Une organisation classique de la fentre de code du VBE sera de spciertout dabord les options (par exemple ici loption dexpliciter la nature detoutes les variables utilises, la spcication du type de fonctions (procdureou fonction proprement dite), la dclaration des variables laide de la va-riable dim. Ci dessous on construit une fonction simpliste calculant le carr

5

6 CHAPITRE 2. VBA UN LANGAGE OBJET

Fig. 2.1 La fentre du VBA Basic Editor (VBE)

Fig. 2.2 La fentre du Visual Basic Editor (VBE)

2.1. LE VBE 7

Fig. 2.3 Ltat du VBE pour la fonction simple dlvation au carr

dun nombre :

Option ExplicitFunction fnSquare(x As Double) As Double

Dim y As Double

y=x*x

fnSquare=y

End Function

Dim y As Double est simplement la dclaration que la variable est detype Double, cest--dire un rel. De mme largument de la fonction fnS-quare, x, est galement un rel comme le souligne la dclaration x As Double.Le rsultat de la fonction est lui aussi dclar tant un rel par la syntaxeFunction fonction As Double. La dclaration de la nature des variables, desarguments des fonctions ou du rsultat de celles-ci nest nullement obligatoire(en labsence de loption explicit) mais est gnralement conseill.


Fig. 2.4 Laspect du VBE pour une procdure de tirage alatoire.

Un autre exemple de programme est celui de la procdure reprsente surla gure 2.4 :Loption explicit est encore une fois spcie. La premire ligne, aprs le

trait horizon (automatiquement introduit par VBA) spcie la nature de lafonction (une procdure de type Sub) et son nom (procTitrageAlatoire). Laligne suivante est en vert car elle constitue un commentaire (tant introduitepar un guillemet). Puis viennent la dclaration des variables : la premire variable, appele nom, est une chane de caractres (et doncson type est string) ;

la seconde variable, appele i, est un entier naturel.Puis on fait pointer lordinateur vers une feuille appele tirage que lon

dsigne comme tant la feuille active, cest--dire celle que lordinateur doitutiliser par dfaut. A la variable nom est aecte ensuite la chane de carac-tres tirages (la nature de chane de caractres tant atteste par les guille-ments). On procde ensuite aux 10 tirages alatoires (par lusage de la boucleFor ... Next, en utilisant la fonction alatoire Rnd) et en stockant chaque r-sultat dans la cellule de la ligne i et de la 1re colonne (Cells(i,1)). Puis la plage (Range) ainsi dnie, on aecte le nom contenu par la variable nompar lcriture :

Range(\A1 : A10"):Name = nom

2.2. LES OBJETS DE VISUAL BASIC 9

En utilisant la fonction Excel (WorksheetFunction) de la moyenne (Average),on calcule la valeur moyenne des tirages alatoires stockes dans la plage nom,valeur que lon aecte la cellule de la ligne 12, colonne 1 :

Range(\A12"):V alue = WorksheetFunction:Average(Range(nom))

Pour enclencher la procdure, il su t alors de cliquer sur le premier bouton(triangle) du sous-menu :

tandis que le second bouton (double traits) est le bouton darrt, le carr lebouton de rinitialisation.

2.2 Les objets de Visual Basic

VBA est un langage orient objet. Son code ne part donc pas commedans les langages procduraux des actions (par exemple limpression) pourpointer vers les objets (par exemple une quation). Au contraire, ce langagednit dabord des objets pour ensuite soit pointer vers certaines de leursproprits, soit leurs appliquer des actions. Ainsi lon dsigne laction deconduire une voiture, on ncrira pas conduire(voiture), comme par exempledans un langage comme le basic, mais on crira voiture.conduire o voiture estlobjet auquel sapplique laction de conduire. De mme si lon dsire obtenirla proprit de la voiture quest sa couleur on crira en VBA voiture.couleur.Objets, proprits, actions sont les principaux lments que lon manie

dans ce langage.

2.2.1 Les objets et les collections

Le premier objet est Excel lui-mme (appel Application) puis viennentdirents objets souvent organiss en collection et notamment :

Objet Nom Collectionchier workbook workbooksfeuille de travail worksheet worksheetsfeuille graphique chart chartsplage (de cellules) rangecellule cell cellsfonctions Excel worksheetFunctionmacro complmentaire addIn addIns


Remarque 1 Visual Basic ne fait pas de dirence entre minuscule et ma-juscule. Les noms prcdents peuvent donc tre crits de multiples maniresselon que lon recourt ou non aux majuscules.

Une collection se dnit comme un ensemble dobjets possdant les mmescaractristiques : la collection des chiers (workbooks), la collection des feuillesde travail (worksheets), la collection des graphiques (charts), etc. La rgle(comportant des exceptions) est que les collections se distinguent de leurslments par le s par lequel elles se terminent. Llment dune collectionpeut tre notamment identie soit par un nombre ou par un nom. Ainsi lesfeuilles de travail sont la fois numrotes et ont un nom. Si lon veut pointervers la 10e feuille du classeur dont le nom est stat, on peut indiremmenttaper :

worksheets(10) ou worksheets(stat)

les guillemets tant ncessaires pour que stat soit trait comme une chanede caractres et non comme une variable.

2.2.2 La hirarchie des objets

Les objets sont organiss hirarchiquement : au sommet rside Excel elle-mme, lobjet Application ; puis vient notamment la collection des classeurs (workbooks) ; les feuilles de travail des classeurs (worksheets), la collection des gra-phiques (charts) ;

....Les plages de cellules (range) constituent lexception selon laquelle

chaque objet correspond une collection. Par contre la cellule (cell) cor-respond la collection des cellules (cells). Le point . permet de spcier lahirarchie des objets. Ainsi si lon veut faire pointer lordinateur vers la plageA1 :B10de la feuille appele travaildu classeur nomm essai, on peutcrire :

Application.Workbooks(essai).Worksheets(travail).Range(A1 :B10)

Par dfaut, lordinateur a une hirarchie dnie par dfaut. Elle correspondau dernier chier utilis sous Excel, la dernire feuille active, etc. Aussicest cette hirarchie qui sera utilise si on utilise une spcication incom-plte, par exemple si on ne spcie dans notre exemple que la plage en crivantseulement Range(A1 :B10). Si le dernier chier activ, la dernire feuilleutilise sont bien respectivement essaiet travail, cela naura donc pas de

2.3. LE MODLE OBJET DEXCEL 11

consquence. Par contre si cela nest pas le cas, le programmeur aura des sur-prises ! Il est donc important dtre prudent dans les racourcis dcriture. Onpourra toujours faire lconomie du premier terme et donc crire seulement :

Workbooks(essai).Worksheets(travail).Range(A1 :B10)

Mais pour les autres niveaux, tout dpend encore une fois des objets actifssous Excel. Ainsi si chier essaiest le chier par dfaut (peut-tre parcequil a t antrieurement activ par une commande :Workbooks(essai).Activate)alors la chane se rduira :

Worksheets(travail).Range(A1 :B10)

Remarque 2 Le point est galement utilis non seulement pour passer dunobjet vers un de ses subordonns mais aussi pour pointer vers une propritou vers une action. Ainsi, si lon veut colorier la plage A1 :B10 en gris,on crira :

Range(A1 :B10).Interior.Color=vbGrey

o Interior est lintrieur des cellules de la plage (un objet donc), Color estvidemment une proprit de lintrieur, vbGrey est la constante dsignantsous Visual Basic le gris (ou plus exactement une manire de dnir un grissous VBA). Si lon veut aussi supprimer la feuille travail, on utilisera aussila syntaxe suivante :

Worksheets(travail).Delete

Dans ce dernier exemple, comme dans le prcdent, on na pas explicit toutela hirarchie des objets en supposant que les objets suprieurs taient pardfaut correctement dnis - ce qui assure par exemple dans notre dernierexemple que notre criture simplie est quivalente

Application.Workbooks(essai).Worksheets(travail).Delete

2.3 Le modle objet dExcel

A chaque application dO ce (et O ce lui-mme) correspond un mo-dle objet, hirarchie de collections dobjets. Comme ce modle objet direpar exemple entre Excel et Word, matriser VBA pour Excel nassure pasde pouvoir sans di cult programmer VBA sous Word, le langage communsappliquant des objets parfois dirents. Pour avoir un aperu du modleobjet de VBA pour Excel, il su t dans lditeur de VBA de solliciter laide


Fig. 2.5 Le modle objet dExcel


Fig. 2.6 Le modle objet dExcel (suite)


Fig. 2.7 Le modle objet dExcel (n)


Fig. 2.8 Lexplorateur dobjets

en y tapant Objets Microsoft Excel. On y obtient laperu reprsent sur lesgures 2.5, 2.6 et 2.7.

On retrouve sur ces graphiques certains objets dj voqus ct denombreux autres. Le modle objet dExcel est en eet riche et complexe.Connatre tous ces objets est naturellement impossible. Dautant que duneversion lautre, le modle senrichit progressivement. Heureusement, sousle VBE, on a accs un outil fort utile : lexplorateur dobjets que lon peutobtenir soit via le menu Edition, soit en tapant F2. Lexplorateur dobjetspermet davoir accs lensemble des libraries simultanment ou den ciblerpour limiter la masse des objets explorer. Les principales librairies sontcelles dExcel et de VBA.

Pour avoir de laide sur une fonction (par exemple Abs dans la gureci-dessous), il su t alors de pointer sur la fonction choisie, puis cliquer surlicne de laide (le point dintrrogation jaune)

Une autre source dinformation trs utile est celle de lInfo Express Au-tomatique. Pour lobjet, sous le VBE, il faut aller dans le menu Outils !Options et cocher Info Express Automatique (cf gure 2.10).


Fig. 2.9 Laide sous lexplorateur dobjets

2.4. PROPRITS ET MTHODES 17

Fig. 2.10 Le menu des options dinformation du VBE

2.4 Proprits et mthodes

Les proprits dun objet font rfrences ses direntes caractristiques.Par exemple pour une cellule : sa couleur, sa hauteur, sa largeur, sa police,etc.On peut : modier les proprits dun objet en utilisant une syntaxe de la forme

Expression.Proprit=Valeur

Par exemple si lon veut que la valeur de la cellule A1 soit 150, onutilisera linstruction suivante

Cells(1,1).Value=150

ou de manire quivalente :

Range(A1).Value=150

interroger un objet pour rcuprer sa proprit, par exemple sa valeur ;ainsi on pourra stocker la valeur de la valeur de la cellule A1 dans unevariable Contenu_A1 en tapant linstruction suivante

Contenu_A1=Range(A1).Value


Les mthodes dun objet sont les actions que lon peut entreprendre surun objet, par exemple le copier, le couper, le dplacer, le slectionner, etc.De manire gnrale linstruction est alors de la forme :

Expression.Mthode

Ainsi, si lon veut copier le contenu la cellule A1, on crira par exemple :

Range(A1).Copy

Ou si lon veut la transfrer son contenu la cellule A2 on pourra crire :

Range(A1).Cut Destination :=Range(A2)

Si lon veut eacer le contenu dune cellule ou dune place on peut utiliserlinstruction ClearContents :

Range(A1).ClearContents

On peut aussi ajouter des lments la collection des feuilles (Worksheets)ou celle des classeurs ouverts (Workbooks), les fermer, les sauver laidedes mthodes Add, Close, Save par des instructions du type :

Worksheets:Add

Workbooks:Add

Workbooks:Close

Workbooks:Save

On peut aussi bouger la position dune feuille lintrieur du classeur, no-tamment aprs une autre feuille, soit en indiquant les indices des feuilles, soitleur noms par des instructions du type :

Worksheets(1).Move After :=Worksheets(3)

Worksheets(feuille1).Move After :=Worksheets(feuille3)

Chapitre 3

Premiers pas

3.1 Lenregistreur de macros

Excel comprend un enregistreur de macros susceptible denregistrer laplupart des actions que lon excute sous Excel et donc de gnrer des pro-grammes. Pour dclencher lenregistreur de macros, on excute la squencesuivante dans le menu Excel :

Outils! Macro! Nouvelle Macro

La fentre de lenregistreur (gure 3.1) permet de choisir le nom de la macro,le chier dans lequel elle va rsider (le chier courant, un autre classeur ouun classeur cach appel classeur des macros personnelles). Enn, la fentrepermet de dcrire la procdure enregistre.Cette action cre si ncessaire un module o une procdure de type Sub est

enregistre. Dans linterface Excel, vient se placer un bouton denregistrementde la macro sur lequel on doit cliquer pour arrter le processus.Lenregistreur de macros permet donc aux novices de VBA de disposer

de procdures quil peuvent rutiliser plus tard, dans le chier voire dans unautre chier. En eet, il est possible denregister la macro dans le classeurcach dit de macros personnelles (cf gure 3.1). Ce chier est disponible tout instant ds lors quExcel est ouvert et ses procdures peuvent donc tresollicites partir de nimporte quel autre chier.A ces multiples avantages de lenregistreur correspondent des inconv-

nients notoires souvent mis en avant par les programmeurs. Le code gnrpar cet outil est en eet souvent excessif par la taille. En eet, si par exemple,on change une proprit du format des cellules (par exemple la couleur de labordure), lenregisteur dtaille lensemble des proprits de celles-ci y compriscelles qui nont pas t aectes.

19

20 CHAPITRE 3. PREMIERS PAS

Fig. 3.1 La fentre de lenregistreur de macros

Fig. 3.2 Le bouton darrt de lenregistrement de la macro

3.1. LENREGISTREUR DE MACROS 21

Ainsi le code gnr pour une modication de la police de caractres pourpour quelle soit du type Times New Roman, sa taille 12 points, en gras, eten rouge :

With selection.Font.Name=Roman.Size=12.Strikethrough=False.SubScript=False.Outlinefront=False.Sahdow=False.Underline=xlNone.ColorIndex=xlAutomatic

End WithWith Selection.Font.Name=Times New Roman.Size=12.Strikethrough=False.SubScript=False.Outlinefront=False.Shadow=False.Underline=xlNone.ColorIndex=xlAutomatic

End WithSelection.Font.Bold=TrueSelection.Font.ColorIndex=3

Ceci rpliqu sur des dizaines dactions entrane naturellement une ex-plosion du nombre de lignes de code. Aussi est-il souvent conseill si lonveut utiliser lenregistreur de nettoyer le code des lignes inutiles avant delenregistrer dnitivement. Ainsi dans notre exemple, le code se simpliedrastiquement :

With selection.Font.Name=Times New Roman.Size=12.Bold=True.ColorIndex=3

End With

Remarque 3 Dans le code ci-dessus, pour viter de rcrire 4 fois selec-tion.Font, on utilise la construction with. Avec celle-ci, on spcie dabordlobjet ou la proprit que lon veut manipuler rptitivement (ici selection.font),


puis aux lignes suivantes on prcise les proprits ou actions (aprsloprateur. ). La n de la construction est signale par End With . Cette constructionest trs souvent utile notamment dans la mise en forme des feuilles Excel.

Cependant mme pour les dveloppeurs, lenregistreur de macros est utile,notamment comme systme daide. Certains objets (comme par exemple lesgraphiques) sont en eet trs complexes par leurs options. Plutt que sas-treindre connatre tous ces lments, il est souvent plus facile de mettre enplace manuellement un objet en dclenchant lenregisteur, puis de retrouverlensemble des proprits de lobjet recherch (le graphique en loccurence),de copier le code obtenue en en faisant une correction.

3.2 Types de variables et procdures

VBA pour Excel utilise non seulement les ressources dExcel (en maniantles cellules notamment) mais peut galement, comme la plupart des logiciels,manier des variables qui lui sont propres pour eectuer des oprations surcelles-ci.

3.2.1 Les principaux types de variables

Outre les types usuels (chanes de caractre, entiers naturels, rels, etc.),VBA utilise aussi un type de variables extrmement souple : les variables detype variant. Naturellement la contrepartie dune plus grande souplesse de lavariable est lespace quelle occupe dans la mmoire de lordinateur. Dclarerune variable permet de dnir les actions et oprations quelle est susceptiblede subir : impossible par exemple deectuer des oprations algbriques (ad-dition, multiplication, etc.) sur des variables dclares comme des chanes decaractres. Dclarer une variable permet aussi dviter les doublons.Les principaux types de variables utilises sont donc les suivants : String - chanes de caractres ; Integer - entiers relatifs compris entre 32 768 et 32 767 ; une variablede type Integer occupe 2 octets de mmoire ;

Long - entiers relatifs compris entre 2 147 483 648 et 2 147 483 647 ;en contrepartie, elle occupe 4 octets de mmoire ;

Boolean - variables de type booleenne, prenant comme valeurs soitTrue, soit False ;

Double - variables relles virgule ottante double prcision dont lesvaleurs peuvent aller de 1; 79769313486231E308 et4; 94065645841247E324 pour les nombres ngatifs et entre 4; 94065645841247E 324 et

3.2. TYPES DE VARIABLES ET PROCDURES 23

1; 79769313486231E308 pour les positifs ; en mmoire une variable detype Double occupe 8 octets de mmoire ;

Variant - Variant est un type de donnes spcial pouvant contenir desdonnes de toutes sortes ; sa contrepartie est dtre la variable la pluscoteuse en mmoire.

Sous VBA, la dclaration du type des variables nest pas obligatoire : enlabsence de toute dclaration, la variable est rpute en eet de type variant(et dans ce cas tout se passe comme si lordinateur dtectait si la variable estnumrique, boolenne, ou autres). Evidemment ceci est coteux en mmoire.Nanmoins, lexplosion de la puissance des ordinateurs a considrablementrduit cet inconvnient. Aujourdhui lintrt de dclarer une variable rsideessentiellement dans le fait quelle permet dviter des erreurs.Pour dclarer les variables, la commande la plus communment utilise

est Dim (pour dimension). Pour que la variable x soit dclare comme unentier relatif, on crira ainsi :

Dim x As Integer

Si lon a plusieurs variables du mme type dclarer, par exemple x et y, onpeut naturellement soit les lister les unes derrires les autres (avec un sautde ligne) :

Dim x As Integer

Dim y As Integer

Evidemment la tentation est alors de racourcir la dclaration. Par exempleen tapant :

Dim x, y As Integer

Mais dans cette criture, seule y sera dclare comme un Integer, x sera consi-dre comme non dclare et donc assimile une variable de type variant.La seule dclaration en ligne correcte suppose lutilisation de la dclarationAs pour chacune des variables :

Dim x As Integer, y As Integer

3.2.2 Les procdures

Les procdures principales utilises sous Excel sont de deux types : les procdures de type Sub les procdures de type Function.


Chaque procdure est construite de la mme manire avec une dclarationdu type, du nom (et des arguments possibles), et une n marque par laprsence des dclarations End Sub ou End Function. Ainsi la procdure Subaura la structure suivante :

Sub nom(argument 1 As type de variable, ...)

< declaration des variables avec Dim >

< lignes d0instruction >

End Sub

La dclaration darguments est facultative. Ainsi dans la procdure ci-dessous,aucune variable nest dclare. La procdure se contente de prendre la valeur(suppose numrique) des cellules A1 et A2, de les multiplier et de reporterle rsultat dans la cellule B1 :

Sub procMult()Dim x As Double, y As Double, z As Doublex=Range(A1).Valuey=Range(A2).Valuez=x*yRange(B1).Value=zEnd Sub

On remarque dans cette procdure simple, la dclaration squentielle desvariables x, y, z, puis laectation x et y de la valeur des deux cellulesA1 et A2. z est bien obtenue par le produit (symbole *) des deux variables.Enn, on aecte la cellule B1 la valeur de z. Naturellement, cette program-mation nest ni trs rapide, ni trs conome en mmoire. On aurait pu trsdirectement crire :

Sub procMult()Range(B1).Value=Range(A1).Value*Range(B1).ValueEnd Sub

Les procdures de type Function sont trs similaires aux procdures Subdans leurs constructions. Elles nen dirent que par le fait quelles sontsupposs retourns directement un rsultat (comme une fonction mathma-tique). En consquence, le rsultat de la fonction elle-mme peut avoir un

3.2. TYPES DE VARIABLES ET PROCDURES 25

type et la dernire ligne est laectation au nom de la fonction dun rsultat :

Function nom(argument 1 As type de variable, ...) As type de variable

< declaration des variables avec Dim >

< lignes d0instruction >

nom = < resultat >

End Functiion

Ainsi la fonction fnMult combinant deux rels x et y pour en faire le produitscrira par exemple :

Function fnMult(x As Double, y As Double) As DoubleDim z As Doublez=x*y

fnMult=zEnd Function

La fonction fnMult utilise une variable intermdiaire (de type Double)pour enregistrer le rsultat de la multiplication des deux variables. Le rsultatest dclar de type Double comme le montre la premire ligne. Evidemment,comme pour la procdure plus haut, il est possible de faire lconomie desvariables intermdiaires en crivant directement :

Function fnMult(x As Double, y As Double) As Double

fnMult=x*yEnd Function

3.2.3 Les variables objet

A ct des variables prcdentes, les objets du modle Excel (notammentles classeurs, les feuilles de travail et les graphiques, les plages de cellules)peuvent tre dclars comme des variables. La dclaration se fait aussi avecDim. Ainsi, si lon crit :

Dim ws As Worksheet

La variable ws sera interprt comme tant un objet worksheet. Si lonveut que ws renvoie une feuille particulire (par exemple une feuille dont lenom est calcul) alors on pourra raliser cette aectation par la commandesuivante :


Set ws=Worksheets(calcul)

La mme procdure (avec Dim puis Set) est galement utilisable pourles plages (range), les graphiques (chart), les classeurs (workbook). Ceci estnotamment trs utile lorsque lon manie des feuilles appartenant des chiersdirents en allgeant considrablement lcriture.

3.2.4 Les constantes et les constantes pr-dnies

Au cours de lexcution dun programme, la valeur dune variable peuttre rednie. Si lon veut maintenir constante la valeur dun paramtre, il estalors souhaitable de la dnir comme une constante. Elle se dclare laidede la dclaration Const. Ainsi par exemple si le taux sans risque du marchest suppos avoir une valeur constante gale 2%, on pourra au dbut duprogramme crire par exemple :

Cst riskFree=0.02

Dans lexcution du programme il sera alors impossible dexcuter unenouvelle aectation de valeur la variable riskFree.Il existe aussi des constantes pr-dnies. Selon quelles sont des constantes

dExcel, de VBA ou dO ce, elles comportent le prxe xl, vb ou mso. Ainsi,sous VBA, les couleurs sont dnies par les constantes vbRed, vbGreen, vb-Grey, etc. . Sous Excel, les directions des dplacements verticaux vers le hautou vers le bas sont dnies par les constantes xlUp et xlDown, tandis que lesdirections horizontales vers la gauche et vers la droite sont dnies par lesconstantes xlToLeft et xlToRight.

3.3 Outils dinteraction

Dans lexcution dun programme, il est parfois ncessaire que lutilisateurxe la valeur de certains paramtres. Pour raliser cette interaction entrelordinateur et lutilisateur, Visual Basic sous Excel met la dispositionplusieurs outils dinteraction dont les deux plus simples sont : la MsgBox ; lInputBox.

3.3.1 La MsgBox

MsgBox est une boite de dialogue qui permet en rponse une questiondapporter direntes rponses codes (oui, non, etc.). Les arguments sousVBA de la commande MsgBox sont notamment :

3.3. OUTILS DINTERACTION 27

le message lui-mme (seul argument obligatoire) ; les boutons a cher ; le titre de la bote de dialogue ;

La disposition pour ces trois lments est :

MsgBox(message,[boutons],[titre],[autresvariables])

o les variables entre crochets sont optionnelles. Le message est une variablede type string (chane de caractres) qui peut simplement tre une questionentre guillemets.

Les boutons sont codis et peuvent notamment prendre les valeurs sui-vantes :1

vbYesOnly (la bote de dialogue comprend un seul bouton) ; vbYesNo (la bote de dialogue comprend deux boutons oui et non) ; vbYesNoCancel (la bote de dialogue comprend trois boutons oui, nonet annulation).

Le titre est une variable de type string prciser.

Les direntes apparences de la bote de dialogue selon les arguments deMsgBox sont :

MsgBox(message)

MsgBox(message,slection)

1Se rfrer laide de VBA pour avoir plus dinformation sur MsgBox.


MsgBox(message,vbYesNo,slection)

MsgBox(message ,vbYesNoCancel,slection)

Lorsque les boutons comportent un choix, i.e. par exemple pour vbYesNoou vbYesNoCancel, il est ncessaire de stocker la rponse dans un objet (soitune variable de type string, soit de type variant). La valeur stocke sera alorssoit vbYes, soit vbNo, soit vbCancel et on peut alors utiliser cette valeur pourconstruire une boucle de contrle conditionnellement cette valeur dans leprogramme.

3.3.2 LInputBox

MsgBox permet seulement des rponses codes binaires (oui/non) ou ter-naires (oui/non/annulation). Parfois il est ncessaire de renvoyer une rponsequi soit un nombre ou un mot. Pour donner ces rponses, on peut utiliserlautre bote de dialogue quest linputBox (voir laide de VBA).Un exemple utilisant lInputBox est celui reprsent sur la gure ci-

dessous :

3.4. FONCTIONS EXCEL DANS VBA 29

Linput comporte un titre (nom de la feuille ), un contenu (Entrezle nom de la feuille et que vous voulez activer ), une zone o lon peuttaper la rponse souhaite, enn deux boutons pour terminer lutilisationde linputBox (avec les deux boutons OK, annuler). Mme si dautres ar-guments peuvent tre dnis, ceux cits peuvent su re pour la plupart desapplications. Pour obtenir linputBox de la feuille, on a tap :

q=InputBox( Entrez le nom de la feuille que vous voulezactiver , nom de la feuille )

q est la variable dans laquelle on va stocker la rponse. Elle peut tre soitune variable de type variant, soit selon la nature de la rponse une variablede type string (pour les chanes de caractre), de type integer, double pourles nombres, etc.

3.4 Fonctions Excel dans VBA

VBA comporte des fonctions nancires et mathmatiques, etc dont onpeut trouver la liste dans lexplorateur dobjets, lintrieur de la librairieVBA.Nanmoins comme le montre la gure 3.3 pour les fonctions mathma-

tiques, le nombre de fonctions peut tre trs modeste. Ceci est videm-ment la consquence du fait que lon a dj notre disposition lensembledes fonctions mathmatiques, statistiques, nancires, etc. dExcel. Pourfaire appel ces fonctions sous VBA, il convient soit de taper Applica-tion.WorksheetFunction ou plus simplement WorksheetFunction en appli-quant ceci soit un nombre, soit une variable, soit une plage. Ainsi pourcalculer la racine carre de 25, on pourra si lon utilise la fonction Racine(Sqrt en anglais) dExcel soit taper directement :

WorksheetFunction.Sqrt(25)

soit si 25 est par exemple la valeur de la cellule B4 :

WorksheetFunction.Sqrt(Range(B4))

ou encore :

WorksheetFunction.Sqrt(Cells(4,2))

soit si 25 est la valeur aecte une variable de type Double appele x :

WorksheetFunction.Sqrt(x)


Fig. 3.3 La collection des fonctions mathmatiques de VBA dans lexplo-rateur dobjets

3.5 Quelques astuces

3.5.1 Mise en forme du projet VBA

Les programmes raliss en VBA, dans le cadre de projets, ne sont pasncessairement courts. La gure 3.4 donne ainsi un aperu sur les fentresdu VBE dun projet visant rcuprer les informations relatives aux mutualfunds amricains sous Yahoo ! Finance.Comme on peut le voir dans la fentre de lexplorateur de projet (en haut

gauche VBA project), le classeur mutualFundsPerf.xls comporte 7 feuillesde travail dont les noms sont requete, intro, etc.. Le rpertoire des modulescomporte lui-mme 8 rpertoires : contenu, fnProcDivers, etc. . Lorsque leprojet devient su samment important il est alors conseill tout dabord demultiplier les commentaires au sein de chaque procdure, comme le montrela gure 3.5, pour rappeler la version du programme et rendre aussi lisibleque possible les direntes tapes du programme.Ensuite il est souvent souhaitable dexpliciter les direntes parties de

chaque programme, voire de sparer le programme en plusieurs procdures.Enn, si le projet comporte de multiples procdures, il est conseill de les

3.5. QUELQUES ASTUCES 31

Fig. 3.4 Un exemple de projet comportant plusieurs modules et plusieursprocdures.


Fig. 3.5 Un exemple de commentaires au sein dun programme.

lister dans un module (appel par exemple contenu) en y prcisant lobjetde chaque procdure et sa place dans la hirarchie du programme, comme lemontre la gure 3.6.

3.5.2 Lexcution du programme

La vitesse dexcution nest pas un des points forts de VBA pour Excelcompar dautres logiciels que lon peut utilis en nance (par exempleMatlab, Gauss). Encore une fois, lintrt de ce logiciel vient de la facilit deson langage et surtout de lutilisation quasi-universelle du tableur Excel.Pour grer la lenteur dexcution de VBA, au surplus, il est possible daug-

menter substantiellement celle-ci en empchant lincessant rafraichissementde lcran dExcel par la commande :

Application.ScreenUpdating=False

laquelle correspondra la n du programme la commande inverse :

Application.ScreenUpdating=True

Comme ce gel de limage peut enlever lutilisateur des repres au coursde lexcution, il convient parfois de susbstituer limage des messages ap-paraissant sur la barre de statut dExcel en utilisant des syntaxes du genre :


Fig. 3.6 Un exemple de module dtaillant les fonctions et procdures dunprojet


Fig. 3.7 Une fentre derreur

Application.StatusBar=dbut de la premire partiedu programme

Il est ncessaire la n de lexcution de rendre la main lordinateursur la barre dtat en crivant la n du programme :

Application.StatusBar=False

Enn, pour grer la possible absence de la barre dtat lcran, il peuttre utile de la faire apparatre en codant au dbut du programme :

Application.DisplayStatusBar=True

3.5.3 Debuggage

Sauf miracle, la rdaction de chaque programme comportera des erreursqui provoqueront des erreurs lors des premires excutions, et la chagedagaantes fentres comme celle de la gure 3.7. Comme on peut le voirsur cet exemple, elle ore la possibilit dun debugage. Lactivation de cetteoption conduira lordinateur faire pointer une che jaune sur la ligne com-portant lerreur (gure 3.8). Si lon pointe sur les lments de cette ligne,la bulle information donnera alors soit la valeur des lments, soit lerreur lorigine du bug. Ainsi dans lexemple, la variable y est un vecteur colonnecomportant 5 composantes alors que la boucle For...Next itre sur 6 compo-santes. Aussi pour i = 6, il est impossible daecter sin(22 i=56) au vecteury. Do le message : , qui tait gale-ment le message de la fentre derreur initiale (mais sans avoir linformationquelle sappliquait la variable y.


Fig. 3.8 Un exemple dinformation lors de la procdure de debuggage

Pour acclrer la dtection des bugs, il est cependant conseill avant toutlancement du programme de procder un debugage. Le menu de debugage(gure 3.9) ore la possibilit entre plusieurs options.Le debugage pas pas dtaill permet ditrer le programme ligne par

ligne. En utilisant le racourci de la touche F8, on passe ainsi de ligne en ligne,en vriant que le code sexcute correctement ou non. Si le programme esttrop long, ou sil comporte des boucles longues, il convient alors dintroduiredes points darrt qui permettront lexcution normale jusquau point darrt.Ainsi sur la gure 3.10, en cliquant sur le bord de la fentre de code au niveaudu dbut de la seconde boucle, on a introduit un point darrt (en rouge).Si lon dclenche normalement le programme il sexcutera (sauf prsencederreurs) jusqu ce point puis sarrtera on pourra alors soit le relancernormalement, soit en cliquant la touche F8 passer en debugage au pas parpas dtaill.


Fig. 3.9 Le menu de debugage du VBE

Fig. 3.10 Un exemple de point darrt

Documents

VBA