DEBUTER AVEC VISUAL BASIC POUR APPLICATIONS (VBA5)

Auteur: Christian Herb Date: Dcembre 1999 MAJ: Juin 2001 Le prsent document est un support de cours. L'objectif de la formation est d'aider les stagiaires prendre en main ce langage. Puisque l'utilisateur a la possibilit d'enregistrer ses actions, on se limitera ici l'tude du code permettant le contrle et l'intervention dans le droulement des programmes. Retour l'accueil Pourquoi le terme de macro ? Historique du langage Quelques mots sur la Programmation Oriente Objet (POO) Comment situer VBA parmi les autres langages ? Comment choisir un langage de programmation ? Prise en main de l'diteur VBA5 Premires macros Enregistrement et sauvegarde des programmes Intervention de l'auteur dans le code des programmes Les variables Porte et dure des variables Rfrences des cellules Structurer les projets Les boucles Tant Que (Do ... Loop et While...Wend) La boucle "pour ... suivant" (For ...Next) La boucle "pour chaque ... suivant" (For Each ...Next) Les conditions Si ... Alors ...Sinon (If ... Then ... Else) La structure Select Case L'instruction GoTo Les botes de dialogue Les oprateurs logiques La gestion des erreurs

Un peu au del de l'iniation:

Les botes de dialogue personnalises ou Userform Communiquer avec le port srie (RS232) Programmation vnementielle

Pourquoi le terme de macro ?

Macro sous entends macro commandes c'est dire un ensemble de commandes destines raliser une ou plusieurs actions sans intervention de l'utilisateur. Les macros sont aux logiciels ce que les scripts ou "batchs" sont aux systmes d'exploitation. Trs souvent, ds qu'un logiciel comporte un nombre important de commandes ou de fonctions distinctes, il est accompagn d'un langage de macro programmation . MS Office ne droge pas cet usage. La particularit d'une macro est d'tre "attache" un logiciel spcifique. Il est impossible de l'excuter depuis un autre logiciel que celui qui l'a cre ( il y a cependant une exception partielle avec VBA5).

Historique du langage.
Le 1er tableur de Microsoft, Multiplan, tait dj dot d'un langage de programmation. Son successeur, Excel mais galement les autres applications de bureautique ont bnfici d'un tel outil. Jusqu' la version 4 d'Excel, la macro programmation consistait crire une suite de fonctions dans une feuille spcifique. Les fonctions ou mots cls des

macros avaient la mme syntaxe que les fonctions du tableur. Le code ainsi crit tait excut squentiellement c'est dire ligne par ligne et n'tait en aucun cas transposable une autre application Microsoft. Dans le mme temps, MS proposait des langages de proprammation destins crire des programmes autonomes ou directement excutables depuis un systme d'exploitation (on parle alors de langages compils). Ces langages taient aussi de type squentiels jusqu' QuickBasic inclu. Les langages squentiels taient assez bien adapts aux systmes dit de "commandes en lignes" comme MS-DOS mais il tait difficile de dvelopper des applications graphiques. Au dbut des annes 90, la firme de Seattle a donc propos un nouveau langage pour le dveloppent d'applications autonomes: Visual Basic (VB). Il est conu pour programmer dans un environnement graphique comme Windows. A partir de la version Excel5, MS remplac le langage de macro programmation propre chacune de ses applications par des adaptations de Visual Basic MS-Office rdig en "langue trangres" . Autrement dit, il existait un langage Visual Basic Applications pour chacune des langues dans lesquelles MS-Office tait crit. Cela a contraint Microsoft crire un traducteur de macros afin de permettre aux programmes crits dans une langue d'tre excut dans une application d'une autre langue. Malheureusement, ce traducteur fonctionna tellement mal que le fabriquant dcida de supprimer l'adaptation aux "langues trangres" c'est pourquoi VBA5 est crit uniquement en Anglais.

Quelques mots sur la Programmation Oriente Objet (POO)

La dfinition d'un objet en programmation est diffrente de celle des objets de la vie quotidienne puisqu'il s'agit gnralement d'objets virtuels. Mais comme dans la vie, un objet a des proprits et des mthodes. Une voiture a des proprits: la couleur, la carrosserie, le moteur. Elle a des mthodes: acclerer, freiner, rouler ... Il en va de mme pour un objet informatique. Un document Excel a des proprits:son nom, sa version... Il a des mthodes: ajouter une feuille. On appliquera la mthode "add" l'objet workbook (document) et l'on dterminera ses proprits en lui attribuant un nom, des options de protections ... Crer un objet en lui attribuant des proprits dfinies s'appelle l'instanciation. L'intrt de la POO est qu'il n'est pas necssaire de connaitre toutes les proprits d'un objet pour l'utiliser mais seulement celles sur les quelles on veut agir. Un objet peut contenir d'autres objets. C'est le cas de l'objet application (Excel par exemple) qui peut contenir les objets worbooks qui eux mmes peuvent contenir des objets worksheets (feuilles) qui eux mmes contiennent des objets ranges (cellules ou plages de cellules). On parle alors de conteneurs.

Comment situer VBA parmi les autres langages ?

Il n'est videment pas possible de citer tous les langages de programmation. On ne parlera que des outils les plus utiliss l'INRA. Classement par type de langage: Squentiel Structur Intermdiaire POO scripts, shell-scripts et batchs C VB Java macros XL et Word Pascal VBA C++ SAS , S+ VB * basic VBA* Fortran standard * L'appartenance de Visual Basic au groupe POO est quelques fois l'objet de polmiques. Un programme compil est du code directement lisible par le systme . "On parle alors de langage machine". La phase de compilation consiste traduire du code "intelligible" l'homme en code binaire. L'laboration d'un programme compil comprends donc une tape supplmentaire par rapport aux langages interprts mais son excution est plus rapide. En dehors de Java et du C, tous les langages compils cits ici sont des produits commerciaux. Le code d'un langage interprt est excut (interprt) en l'tat ou il a t crit mais son droulement tant dpendant d'un ou plusieurs logiciels, il est sensiblement plus lent. Les produits dit "interprts" sont gratuits ou fourni avec leur logiciel "maitre". Classement par type de fonctionnement: interprts interprts compils Dpendent uniquement d'une famille Dpendent de l'OS ET d'un dpendent gnralement d'une famille d'OS: logiciel: d'OS Java (ne dpend pas de l'OS mais d'une Scripts, Shell-scripts et Batchs macros machine virtuelle) PERL VBA C , C++

TCL/TK (existe maintenant sur Mac, Win32 et Unix)

Fortran , Pascal Basic , Visual Basic

Comment choisir un langage de programmation ?

On devrait plutt se demander: comment puis-je me positionner par rapport tous ces langages tant la question est vaste. Aussi, je me bornerai donner quelques conseils. Objectif Suivre ou dvelopper ou des applications "lourdes et performantes" Applications lourdes et portables sur diverses plateformes Applications de toutes natures Applications durables, moyennement compliques petites applications performantes et agrables, demandant un temps d'apprentissage raisonable Applications trs spcialises (Stats, BDD ...) rpondre un besoin spcifique motivation dicte par la profession dicte par la profession Enrichir son CV langage somme des gradients correspondan de difficult t C++ Java C++ et/ou Java, VB C++ ou Java ou VB VB, TCL/TK VB, VBA, macros, scripts, SAS ou S+ 200 200 180 150 100 100

Automatiser des tches reptitives, faciliter l'usage d'un logiciel

rpondre/anticip er des VBA, macros, besoins scripts particuliers Inconvnients

60

Lang ages C++

Avantages

Un des plus performants, portable moyennant qlq ardu, assez cher amnagements Gratuit, trs rpandu, portable sur Unix, Win32 et Mac. parfois lent pour des applications lourdes, le code Java Ne prsente pas de difficults pour les personnes qui pour le graphisme est complexe connaissent dj C, C++ Visua Reserv exclusivement aux PC, sa toute relative l Relativement facile apprendre. Prix abordable simplicit peut entrainer des erreurs graves et Basic compliquer le dveloppement TCL/ TK, PERL gratuits, rapidit d'apprentissage pour de petites applis et trs dpendants d'un OS y compris dans la syntaxe , performants, surtout le graphisme sous TCL/TK donc difficiles distribuer shellscript s Pas d'achat supplmentaire, la solution la plus facile dpend de MS-Office dans sa version actuelle donc VBA apprendre. Un excellent tremplin vers un langage plus ne tourne pas dans les versions antrieures. Comme difficile. tous les langages MS, son avenir est incertain Ce qui se fait de mieux dans le traitement de donnes. 2 logiciels chers. Sans intrt en dehors du SAS, Apprentissage accessible tous. Trs rpandus dans le traitement de donnes.Leur langage respectif S+ domaine scientifique mondial et les grandes entreprises commence vieillir.

Prise en main de l'diteur VBA5

On accde l'diteur depuis les menus d'application MS-Office, XL pour ce qui nous concerne: menbu Outils puis Macros puis Visual Basic Editor ou en appuyant simultanment sur ALT+F11.

L'cran est divis en cadres.L'explorateur de projets liste les documents ouverts et leur compositions (feuilles, modules ...).

La fentre de proprits indique les proprits du document slectionn. Si la fentre de code n'est pas l'cran, double-cliquez sur ThisWorkbook dans l'explorateur d'objets. Nous verrons plus tard comment appeler d'autres elments de l'diteur VBA5. L'explorateur d'objet a 3 boutons qui permettent de se dplacer dans les objets. L'diteur est semblable un petit logiciel de traitement de texte avec des fonctions propres. Si vous crivez dans le module sub nom(), il ajoute automatiquement 2 lignes plus bas les mots cls End Sub et place le curseur une tabulation en retrait. Il place galement des commentaires en cas d'enregistrement. Tout ce qui est plac gauche d'une apostrophe (') est un commentaire. L'diteur les crit en vert.Vous pouvez bien entendu personnaliser ces paramtres de couleur. Le cacactre invisible de fin de ligne est trait comme la fin de l'instruction. Lorsqu'une instruction ne peut pas tenir sur une seule ligne vous indiquez au systme de lire la suite dans la ligne suivante en mettant un espace et un "tiret bas" ( _ ) aprs le dernier caractre de la 1re ligne. Mise en forme du code. Bien que tout fait facultatif, il est d'usage de mettre en retrait les instructions faisant partie d'un bloc. Cela amliore la lisibilit du texte.

Premires macros
Enregistrement de la 1re macro Revenez dans la feuille XL. Il est trs pratique de pouvoir changer le mode de rfrencement des cellules en cours d'enregistrement. Assurez vous que la barre d'outils "Arrter l'enregistrement de macro" est coche depuis le menu Affichage puis barres d'outils puis Personnaliser et enfin l'onglet Barres d'outils. Tapez une valeur quelconque dans une cellule. Faites en sorte que la cellule soit slectionne. Allez dans le menu Outils puis Macro et Nouvelle macro. Dans le champ Nom de la macro, crivez essai1. Nous verrons plus loin comment utiliser les autres champs. Cliquez sur Ok . Allez dans le menu Format puis cellule cliquez sur l'onglet Police choisissez gras.

Allez dans le menu Outils puis Macro et choisissez Arrter l'enregistrement. ( En fonction de votre configuration, il est possible que la barre d'outils macros s'affiche l'cran. Dans ce cas, utilisez ses boutons) Activez l'diteur VBA. Vous constatez que le document actif contient un lment supplmentaire nomm Modules qui lui mme contient Module1. Double-cliquez le. Vous voyez le code que vous venez d'enregistrer. Refaite un enregistrement nomm "essai2" en utilisant non pas le menu Format ... Mais en cliquant sur l'icne "Gras" et comparez le code de essai1 et essai2. Vous voyez que la macro "essai2" est beaucoup plus compacte! Dans essai1, VBA a renseign chaque option (les proprits de l'objet) du menu Format alors que essai2 s'est limit aux instructions de mise en forme du texte. Tenez compte de cette diffrence avant d'enregister une macro. Vous obtiendrez un code beaucoup plus facile comprendre! Testez maintenant ces deux programmes: Dans la feuille XL, entrez une valeur dans une autre cellule et allez dans le menu Outils puis Macro et encore Macros. Slectionnez essai1 et cliquez sur Excuter. recommencez ensuite avec le programme essai2. Ecrire la 1re macro Gnralement, la 1re leon de programmation commence par l'criture du programme: "Hello World". Je n'ai aucune raison de vous viter cela! Allez dans l'diteur VBA. A la fin de "essai2", crivez: Sub hello() MsgBox "Salut tout le monde", vbOKOnly, "hi" End sub Revenez dans Xl et excutez la macro hello. Depuis l'diteur, copier le l'instruction: MsgBox "Salut tout le monde", vbOKOnly, "hi" dans essai2 et excutez cette dernire. Vous venez de voir 2 aspects complmentaires de VBA. La macro essai2 contient maintenant du code dont une partie a t enregistr et le reste a t crit par vous mme. Dvelopper en VBA consiste donc enregistrer tout ce qu'il est possible et complter par du code crit " la main". Le dveloppeur devra crire le code quand il voudra: - Afficher une boite de dialogue pour informer l'utilisateur ou lui demander d'intervenir - Crer des variables - mettre des tests dans le programme. Ce sont ces 3 points que nous allons dtailler mais auparavant, regardons la structure de nos 3 programmes. Vous avez pu constater que chaque programme commence par sub() et se termine par End sub . Sub doit tre interprt comme sous routine. Quand vous crivez le mot cl sub(), l'diteur l'interprte comme l'annonce d'un nouveau programme et il crit automatiquement End sub sur la ligne infrieure. Sub doit tre suivi par le nom de la macro. Ce nom doit commencer par une lettre. La commande msgbox Slectionnez le mot cl msgbox dans l'diteur VBA et appuyez sur la touche F1 pour obtenir de l'aide. Voici la syntaxe: MsgBox(prompt[, buttons] [, title] [, helpfile, context]) prompt est le message contenu dans la bote de dialogue.Le texte doit tre crit entre guillemets Anglais (" "). Les crochets signalent que cet argument est facultatif. buttons vous permet de choisir un type de bouton. VbOkOnly affiche une seul bouton. VbOkCancel affiche 2boutons. Chacun de ces boutons renvoie une valeur que vous pourrez exploiter par exemple avec un test IF (si) pour excuter une action en fonction de la rponse de l'utilisateur. Vous pouvez voir dans l'aide qu'il y a beaucoup de types de boutons disponibles. title donne un titre la bote qui doit tre crit lui aussi entre guillemets Les arguments de commandes doivent toujours tre spars par des virgules. Pour en savoir plus, voir le chapitre: Les botes de dialogue

Enregistrement et sauvegarde des programmes

Depuis XL, allez dans les menus Outils puis Macros et nouvelle macro. Le champ Touche de raccourci vous permet d'affecter un raccourci clavier la macro que vous vous apprter enregistrer. Afin de limiter les risques de cration de raccourcis dj affects dans le tableur, il est conseill d'utiliser pour les macros des raccourcis utilisant les touches Ctrl+Shift+lettre. Le champ Enregistrer la macro dans: permet de choisir la destination du programme. Si elle est enregistre dans "ce classeur", elle ne sera disponible que si le fichier est ouvert. Si elle est enregistre dans le classeur de macros personnelles, elle sera disponible chaque fois que XL sera lanc. Il y a cependant une diffrence en fonction des

systmes d'exploitation "scuriss" ou non. Sous NT et dans certains cas sous W9*, il existe un classeur de macros personnelles pour chaque utilisateur.

Intervention de l'auteur dans le code des programmes

VBA5 tant un langage macro ayant la facult d'enregistrement des programmes, le rle de l'auteur est trs limit par rapport aux autres langages. Cependant il est loin d'tre insignifiant. Ce paragraphe est destin rappeler quels types d'interventions doivent tre ralises pour rendre les programmes fonctionnels. Se positionner dans le document (Les adresses de cellules ou de plages): Pendant l'enregistrement, les dplacements sont nots en position relative de la cellule active ou en rfrences absolues par rapport au coin suprieur droit de la feuille mais il est trs frquent d'avoir modifier ces valeurs. Mmoriser une valeur afin de la rutiliser (les variables): Excuter une action en fonction d'une condition, d'un tat, d'une valeur (les conditions): Vous aurez souvent besoin d'excuter un programme en fonction d'une valeur, par exemple traiter diffrement les lignes d'une feuille en fonction d'une valeur prcise (nom d'lves, numros de placettes Etc). Dans ce cas, vous devrez utiliser les conditions IF ou Select Case. Paramtres de l'utilisateur (les botes de dialogue): Une macro n'est rutilisable que si l'utilisateur peut entrer des valeurs ou des rfrences de cellules ou des noms de fichiers. Les bote de dialogues et les userforms sont des moyens de communication avec l'utilisateur (les userforms ou feuilles VBA destines la cration d'interface graphiques complexes ne sont pas traites dans ce document en raison de leur complexit pour des dbutants) . Une partie de programme doit tre rpts (Les boucles): L'utilisateur peut fournir des informations inadaptes ou excuter le programme en dehors de son champ (Gestion des erreurs)

Les variables
Les variables sont destines stocker des valeurs en vue de leurs rutilisations ultrieures. Elles sont de plusieurs types. La mthode de dclaration est la suivante: dim nom_de_variable As type ; exemple de variables contenant du texte: dim toto As string cre la variable toto et la dclare de type chane de caractres mais toto est vide. Vous pouvez maintenant lui affecter une valeur: toto = "Hello World" la variable toto existe et a pour valeur "Hello World". Vous pourriez crire le programme hello comme ceci: sub hello2() dim txt as string ' dclaration dim titre as string ' dclaration txt = "Hello World" 'affectation titre="Salut" 'affectation MsgBox txt, vbOKOnly, titre 'utilisation des variables dans une instruction end sub L'avantage de ce programme est de vous permettre de rutiliser ultrieurement les variables sans tre oblig de recrire le contenu et votre code gagne en "lisibilit". Tout ce qui est droite d'une apostrophe (') est du commentaire et n'est pas lu par le programme. Vous n'tes pas obligs de dclarer la variable. Vous pouvez directement lui affecter une valeur ex: sub hello3() txt = "Hello World" titre="Salut" MsgBox txt, vbOKOnly, titre end sub Quand vous procdez ainsi, VBA dclare la variable pour vous. Vous pouvez aussi mler du texte et une variable dans une instruction, ex: sub hello4() dim txt as string dim titre as string txt = "Hello World"

titre="Salut" MsgBox "Exemple de texte et de variable: "& txt, vbOKOnly, titre ' le symbol et commercial (&) lie la chane et la variable end sub Chaque fois que vous dclarez une variable, le systme lui rserve un espace mmoire. Comme pour les variables "string", vous n'tes pas oblig de les dclarer ni de leur affecter une dimension. Cependant, si vous devez dvelopper des programmes gourmands en ressources ou si vous voulez les excuter sur des systmes limits, vous avez intrt dfinir prcisement vos variables. Pour limiter la longueur d'une chane: dim variable as string * nb_caractres ex: dim txt as string * 11 lime la chane 11 caractres. Les variables numriques: mmoire Type valeurs acceptes occupe Byte entier de 0 255 1 octet Integer entier entre -32768 et 32767 2 octets Long (entier long) entre -2 147 483 648 et 2 147 483 647 4 octets Single nombre virgule flottante 4 octets double double prcision 8 octets Currency virgule fixe avec 15 chiffres pour la partie entire et 4 pour la partie dcimale 8 octets Les variables de type matrice ou de type array encore appeles variable tableau Les variables que nous avons vu jusqu prsent ne stockent qu'une seule valeur. Les matrices stockent une liste d'lments. Il est obligatoire de les dclarer: dim nom(nb_ lments) as type ce qui revient : dim toto(5) as integer dclare la variable tableau de type "entier" ayant la possibilit de recevoir 5 valeurs. Attention, l'index commence zro. Avec ce type de dclarations, les lments du tableau seront indexs de 0 4. Vous pouvez modifier l'index dans la dclaration: dim toto(1 to 5 ) as integer Exemple de cration de matrice: sub matrice1() dim devinette(1 to 8) as string devinette(1) = "M et Mme Froid ont 7 enfants, quels sont leurs prnoms ?" devinette(2) = "Eva" devinette(3) = "Aude" devinette(4) = "Dan" devinette(5) = "Marc" devinette(6) = "Samson" devinette(7) = "Gilles" devinette(8) = "Ella" 'fin de la cration du tableau ' Ce qui suit est une boucle qui va afficher8 fois une boite de dialogue avec chacune des valeurs du tableau for compteur = 1 to 8 msgbox devinette(compteur) next compteur end sub L'exemple prcdent utilise une matrice unidimensionnelle mais un tableau peut tre multidimensionnel exemple: dim tab(1 to 10, 1 to 10) as integer cre un tableau 2 dimensions o chaque vecteur contient 10 valeurs. Ce type de tableau est trs utilis pour stocker des valeurs lues dans une plage de feuille XL. Vous pourrez crer des tableaux ayant autant de lignes et de colonnes que la plage. Accder aux valeurs d'un tableau Si une seule valeur vous intresses et que vous connaissez ses coordonnes dans le tableau, il suffit d'crire: A=tableau(3) pour affecter la variable "A" de la 3me valeur de tableau(). Nous verrons dans le chapitre sur les boucles comment rechercher une valeur dans un tableau. Les variables de type object Un objet peut dsigner un classeur (workbook), une feuille (sheet) ou tout autre partie de l'application. Une fois la variable de type object declare (obligatoire dans ce cas), vous lui affecter une valeur avec le mot cl SET : set C = activeworkbook affecte la variable C du nom du document actif

Porte et dure des variables

Jusqu' prsent, nous avons dclar les variables entre les mots cls sub() et end sub. Cette pratique limite la porte des variables ce module. Si vous appelez plusieurs modules depuis un projet et que vous souhaitez rendre certaines variables utilisables par tous les modules, vous devrez soit les faire prcder du mot cl public , soit les dclarer l'extrieur d'un module.

Rfrences des cellules

Se dplacer dans la feuille est interprt comme de la manipulation de l'objet range qui reprsente une ou plusieurs cellules. En plus de range, vous utiliserez cells qui est une collection de toutes les cellules du document actif. Les proprits row et column donnent le numro de la ligne et de colonne. Activecell renvoie un objet range indiquant la cellule slectionne. select slectionne un objet. offset dcale par rapport une plage indique et activate active un objet . Rfrences absolues C'est le mode d'enregistrement par dfaut. Le bouton Enregistrement relatif de la barre d'outils macro permet le passage d'un mode l'autre. L'expression: range("A1").select slectionne ou active la cellule A1. L'expression: range("A1:B10").select slectionne une plage de A1 B10 L'expression: range("A1, B10").select slectionne les cellules non contigus A1 et B10. Le code: range("A1, B10").select range("B10").activate slectionne les cellules A1 et B10 et donne le focus la cellule B10. Ce type de rfrences est autoris mme si la feuille active a un systme de notation de type L1C1 Pour faire rfrence une seule cellule, vous pouvez aussi utiliser: cells(1,1).select 'slectionne la cellule A1 Attention cependant, les valeurs entre les parenthses (n de ligne et n colonne) sont des rfrences absolues. cells(1,1) indique la ligne1 et colonne 1. Vous pourriez aussi remplacer ces valeurs par des variables. Rfrences relatives Ce type de rfrences n'est pas trs bien fait dans VBA5 et constitue la principale difficult du dbutant. Une rfrence relative est gnralement une adresse partir de la cellule active. L'expression: activecell.offset(0,1).range("A1").select ou activecell.offset(0,1).select dplace (offset) la slection vers une cellule place sur la mme ligne et une colonne droite. Pour se dplacer vers la gauche ou vers le haut, il suffit de donner des valeurs ngatives: activecell.offset(0,-1).select pour slectionner une plage de 4 cellules dans la colonne voisine de gauche: ActiveCell.Offset(0, -1).Range("A1:A4").Select Quelques exemples de slection de cellules ou de plages: Slection de toutes les cellules non-vides et contigus de la ligne 1: Range("A1", Range("A1").End(xlToRight)).Select Slection de toutes les cellules non vides autour de la cellule "A1" : Range("A1").CurrentRegion.Select (produit le mme rsultat que le raccourci clavier: Ctrl + * ) Slection de toutes les cellules non vides autour de la cellule active: Range(ActiveCell, ActiveCell.CurrentRegion).Select (produit le mme rsultat que le raccourci clavier: Ctrl + * ) Slection de toutes les cellules non vides autour de la cellule active et exclusion de la ligne 1: Dim Plage As Range With Selection.CurrentRegion Set Plage = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count) End With (produit le mme rsultat que les raccourcis clavier: Ctrl + Shift + flches) Slection de toutes les cellules non vides dans une seule colonne en dessous de la cellule active: Range(ActiveCell, ActiveCell.End(xlDown)).Select (produit le mme rsultat que le raccourci clavier: Ctrl + Shift + flche en bas) Slection de toutes les cellules non vides sur une seule ligne et droite de la cellule active: Range(ActiveCell, ActiveCell.End(xlToRight)).Select (produit le mme rsultat que le raccourci clavier: Ctrl + Shift + flche en droite) (Utilisez les instructions xlToLeft et xlUp pour les autres directions)

Slction de la dernire cellule non vide de la colonne: ActiveCell.End(xlDown).Select Slection de la dernire cellule du tableau: Range("A1").SpecialCells(xlLastCell).select (produit le mme rsultat que le raccourci clavier: Ctrl + touche fin) Slection de la dernire cellule contenant une valeur: Cells.Find("*", [A1], , , xlByRows, xlPrevious).Select Dplacer la slection d'une colonne vers la droite: Selection.Offset(, -1).Select

Structurer les projets

Avec VBA, on est trs loin des listings de langages comme le basic ou le langage macro XL4. Le developpeur tout intrt crire des procdures courtes et les appeler depuis un programme principale. Il est en effet possible d'utiliser la commande call pour appeler un module. Les parenthses de l'instruction sub hello() sont l pour passer des paramtres la procdure appelle. Vous pouvez structurer vos programmes de cette manire: sub hello() ' Programme principal call boite("Bonjour") ' fonctionne aussi avec: boite("bonjour") end sub sub boite(txt) ' programme auxiliaire msgbox txt end sub Dans ce cas, le programme hello appelle le programme botetout en lui passant un paramtre ("bonjour"). Dans les exemples precdents, nous avons recrit l'instruction msgbox dans chaque programme.En crivant de petites procdures spcialises, on peut les appeler autant de fois que l'on veut sans les recrire tout en leur faisant raliser une action lgrement diffrentes chaque fois. L'autre avantage au moins aussi important rside dans le fait qu'un projet "clat" en petits modules est beaucoup plus lisible donc la recherche d'erreur y est beaucoup plus facile. Dernier avantage et non des moindres, vous pouvez consacrer un peu de temps peaufiner une procdure si c'est pour la rutiliser souvent. Les diffrents types de procdures. Je n'en citerai que deux. Procdures sub J'en ai dj parl au dbut. Ce sont des sous-routines qui excute une srie d'instructions mais ne renvoient pas de valeur en fin d'excution. Elles sont structures comme ceci: sub nom_de_la procedure() instructions ... End sub Le nom ne peut pas contenir plus de 255 caractres, il doit commencer par une lettre et ne pas contenir les 5 caractres suivants: @&$#! En outre, le nom doit tre diffrent des mots cls du langage. Procdures Function Les fonctions contiennent elles aussi une srie d'instructions et renvoient une valeur. Voici leurs structures: function nom_fonction(variable ayant reu une valeur de la proc appelante) instructions ... nom_fonction=expression end function Exemple: function age_au_carre(age_capitaine) age_au_carre=age_capitaine*age_capitaine end function Si vous appellez cette fonction depuis une procdure sub, vous devez lui passer une valeur: call age_au_carre(10) que la fonction utilise puis renvoie le rsultat la procdure appelante Mais les fonctions ont aussi un autre usage. Depuis XL, allez dans le menu Insertion puis Fonctions puis Fonctions personnalises. Normalement, vous devez voir la fonction age_au_carre. vous venez d'ajouter une fonction au tableur. Si vous souhaitez en disposer chaque session, copiez la dans le classeur de macros perso.xls. Attention: vous devez ajouter l'instruction Application.Volatile pour que la fonction se recalcule automatiquement lors d'un changement de valeur de l'une des cellules utilises ce qui donne ceci:

function age_au_carre(age_capitaine) Application.Volatile age_au_carre=age_capitaine*age_capitaine end function

Les boucles
"tant que ..." Une boucle est un moyen de repter une instruction ou un bloc d'instructions autant de fois que c'est necssaire. Do ... Loop et While...Wend sont gnralement utilises pour repter une instruction sur tout le document. For each ... Next est utile pour une action sur une collection. While condition instructions Wend Vrifie d'abord la condition et excute les instructions si condition renvoie true (vrai) Do ... Loop est plus souple parce que la condition peut figurer aussi bien avant les instructions qu'aprs. Exemple: le test est ralis avant les instructions. Sub deplacement() Do while activecell.value <>"" ' tant que la cellule active n'est pas vide selection.offset(1,0).select loop End sub Le test est ralis aprs les instructions. Sub deplacement2() Do selection.offset(1,0).select loop while activecell.value <>"" End sub La boucle est raliss tant que la cellule n'est pas vide. Sub deplacement3() Do While IsEmpty(ActiveCell) = False Selection.Offset(1, 0).Select Loop End Sub

La boucle "pour ... suivant"

For ... Next repte la mme action un nombre de fois dtermin par l'utilisateur ou le programmeur. La syntaxe est: For compteur = dbut to fin step next compteur Compteur est une variable crer. Dbut est la valeur initial de compteur. fin est la dernire valeur de compteur. step est le pas de la boucle si diffrent de un. Exemple: Sub deplacement4() For compteur = 1 to 5 step 2 Selection.Offset(1, 0).Select Next compteur End Sub Slectionne 3 fois la cellule infrieure. Il est galement possible d'imbriquer des boucles. C'est fort utile quand on doit travailler sur des tableaux plus d'une dimension. Exemple: votre feuille XL contient dans une colonne des prnoms d'lves et dans l'autres des notes. Vous souhaitez stocker ces valeurs dans une variable matrice deux dimensions. Voici le tableau: Jean 10 Olane 18 Muriel 7 Luc 13 Sylvie 5 Le programme suivant lit les donnes dans la feuille et les stocke dans la variable matrice "tableau" Sub stock_tableau() Dim tableau(5, 2) For lignes = 1 To 5

For colonnes = 1 To 2 tableau(lignes, colonnes) = Cells(lignes, colonnes) Next colonnes Next lignes End Sub Dans la ralit, ce programme a un intrt limit puisqu'on connait rarement les dimensions de la feuille de donnes. L'exemple suivant compte le nombre de lignes et colonnes de la slection pour dimensionner la variable matrice (la cellule active doit tre A1): Sub stock_tableau2() Dim tableau() nb_lignes = Range("A1").End(xlDown).Row nb_colonnes = Range("A1").End(xlToRight).Column ReDim tableau(nb_lignes, nb_colonnes) For lignes = 1 To nb_lignes For colonnes = 1 To nb_colonnes tableau(lignes, colonnes) = Cells(lignes, colonnes) MsgBox tableau(lignes, colonnes) Next colonnes Next lignes End Sub Cette syntaxe parait complique pour stocker des valeurs accessibles dans le document Excel. Mais que l'on ne s'y trompe pas, l'accs au donnes d'un tableau est infiniment plus rapide que la lecture dans une feuille et vous affranchit des problmes de rfrence des cellules; d'autres parts, si vous devez un jour apprendre un langage compil, cette pratique sera indispensable!

La boucle "pour chaque ... suivant"

La boucle For Each lment ... instructions Next est utilise pour un traitement sur une collection d'objets. Dans l'exemple prcdent, tableau() tait une collection de valeurs. La procdure suivante lit chaque cellule d'une plage slectionne et met en gras les valeurs suprieures 10. Sub gras_maigre() Dim cellule As Range For Each cellule In Selection.Cells If cellule.Value >= 10 Then cellule.Font.Bold = True else cellule.Font.Bold =false End If Next cellule End Sub Nous venons de voir un des trs gros avantages de VBA sur l'ancien langage XL. Avec l'ancienne version, il tait impossible d'effectuer une action sur une cellule sans qu'elle soit slectionne ce qui obligeait le programme se dplacer sans cesse et ralentissait l'excution. N'hsitez pas utiliser les variables tableaux et les boucles pour agir sur une collection plutt que d'obliger le programme activer chaque lment. En outre, vous ne serez pas perturb si un jour vous deviez passer un langage compil.

Les conditions
Si ... Alors ...Sinon ...
Elle peut prendre plusieurs formes: If condition then instructions .. End If ou If condition then instructions .. else

instructions... End If ou If condition then instructions .. else instructions... elseif autre condition instructions.. End If Nous l'avons dj rencontr dans les exemples prcdents. Avec la boucle For ... Next , ce sont vraisemblablement les instructions les plus utilises. Une boucle If doit toujours tre suivi sur la mme ligne de l'instruction Then et se terminer par End if. Si la condition renvoie true(vrai), les instructions suivant Thensont excutes. Sinon, la boucle cherche l'instruction Else. Si elle est trouve, les instructions suivantes sont excutes. Comme nous avons dj rencontr plusieurs reprises la boucle If dans sa plus simple exprssion, il est inutile de la dtailler davantage cependant, les formes que peut prendre la condition mrite de s'y arrter: If isempty(activecell) then If activecell.value = "" then Ces 2 formes renvoient true si la cellule active est vide, sinon elles renvoient False(faux). On peut aussi utiliser le mot cl And dans une condition: If activecell.value >10 activecell.value <20 then Dans ce cas, la condition renvoie true seulement si les 2 conditions sont remplies (notez qu'il faut repter le nom de l'lment tester: activecell). If activecell.value <10 activecell.value >20 then La condition renvoie true si au moins une des 2 conditions est remplies. Les blocs d'instrutions If then ou If then else ne vrifient qu'une seule condition alors que: if condition1 then instructions elseif condition2 End If Vrifie deux conditions. Vous pouvez emboter ainsi autant de conditions que vous le souhaitez.

And or

La structure Select Case

Elle s'avre trs utile quand une condition peut renvoyer plusieurs situations. Select case expression case 1 instructions case 2 instructions case n instructions case Else instructions End Select La procdure suivante teste la valeur de la cellule active cense contenir un code postal et affiche le nom du dpartement correspondant: sub code_postal() Select Case ActiveCell.Value Case 54000 To 54999 'teste si la cellule contient une valeur entre 54000 et 54999 inclus MsgBox "le dpartement de Meurthe et Moselle " Case 55000 To 55999 MsgBox "dpartement de la Meuse" Case 57000 To 57999 MsgBox "dpartement de la Moselle" Case 88000 To 88999 MsgBox "dpartement des Vosges" Case Else MsgBox "la valeur ne corresponds pas un dpartement Lorrain"

End Select End Sub La valeur suivant l'instruction Case peut tre de n'importe quel type. Pensez aux valeurs avec dcimales. 1 est diffrent de 1,000001 et le comportement de Select Case sera diffrent.

L'instruction GoTo
Si vous voulez rendre vos programmes indeboguables, multipliez les instructions GoTo! Au de l de 3 instructions, vous aurez les pires difficults vous y retrouver. L'instruction GoTo est obligatoirement suivi d'une "tiquette" ou "balise". Une tiquette est le point de chute de GoTo. Si vous souhaitez que la procdure "saute" une ou plusieurs instructions, mettez une tiquette l'endroit o vous souhaitez qu'elle se rende, exemple: sub code_postal2() question= msgbox("souhaitez vous continuer l'excution de ce programme ?",vbOKCancel) if question = 2 then GoTo fin ' cas particulier de IF: pas d'instruction End If en prsence deGoTo Select Case ActiveCell.Value Case 54000 To 54999 MsgBox "le dpartement de Meurthe et Moselle " Case 55000 To 55999 MsgBox "dpartement de la Meuse" Case 57000 To 57999 MsgBox "dpartement de la Moselle" Case 88000 To 88999 MsgBox "dpartement des Vosges" Case Else MsgBox "la valeur ne corresponds pas un dpartement Lorrain" End Select fin: End Sub Si l'utilisateur clique sur Annuler, "question" prend la valeur 2. Dans ce cas, GoTo excute l'instruction suivant l'tiquette fin: Notez que GoTo n'a aucun intrt dans ce cas puisqu'il tait plus simple d'crire: if question = 2 then exit sub Malgrs tout le mal que j'ai pu en dire, GoTo reste incontournable dans un processus de gestion des erreurs. Nous le verrons plus loin.

Les botes de dialogue

Il faut faire la distinction entre les botes issues de fonctions VBA telles que msgbox et inputbox d'une part et les botes issues des mthodes de l'application d'autres parts. Nous avons suffisament utilis la fonction msgbox pour ne pas revenir dessus. La fonction inputbox permet de passer des valeurs de l'utilisateur au programme. En revanche, elle ne permet pas de spcifier un type de donnes ou de slectionner des donnes. Puisque la mthode inputbox le permet, nous n'utiliserons que celle ci. Inputbox est une mthode de l'objet application. Voici la syntaxe: Application.inputbox(prompt, title, default, left, top, helpfile, helpcontextID,type) Voyez l'aide pour connaitre les valeurs admises par l'argument type. sub test_input() set entree= application.inputbox(prompt:="Slectionnez une cellule", _ ' Texte affich dans la bote title:="Essai ", _ ' Titre left:=3, _ ' Position horizontale top:=-80, _ ' Position verticale type:=8) ' Type d'entre attendue end sub Vous pouvez galement utiliser les botes de dialogues de l'application: application.dialogs(XLdialogOpen).Show ' pour l'ouverture de fichier. application.dialogs(XLdialogSaveAs).show ' pour enregistrer sous

Les oprateurs logiques

Oprateur description Or renvoie vrai si l'une ou l'autre condition est vrifie And renvoie vrai si toutes les conditions sont vrifies Not ngation Il est possible d'utiliser plusieurs oprateurs dans une mme expression condition de les "enfermer" dans les parenthses.: If (condition1 Or condition2) And (condition3) then ... Le test renverra true si une des deux premires conditions est vrifi ainsi que le 3me.

Gestion des erreurs

Elles sont prvisibles si vos projets comportent des botes de dialogue. Si vous indiquez un type de donnes et que l'utilisateur en saisi un autre ou si un dplacement de la slection devient impossible. Supposons que la cellule active soit A1. si votre code contient une commande demandant au programme de slectionner la cellule contigu de gauche, cela gnre une erreur. La procdure suivante est crite pour dplacer la slection vers la gauche: Sub decale_gauche() Selection.Offset(0, -1).Resize(Selection.Rows.Count, Selection.Columns.Count).Select End Sub Si cette macro est excute depuis la colonne "A", une erreur est signale par VBA. Le code suivant prends en compte cet cas: Sub decale_gauche2() On Error GoTo ouste Selection.Offset(0, -1).Resize(Selection.Rows.Count, Selection.Columns.Count).Select Exit Sub ouste: msg = MsgBox(prompt:="dplacement impossible!", Buttons:=vbOKOnly, Title:="Erreur") End Sub Il faut placer la commande: On Error GoTo "balise" avant la commande susceptible de gnrer l'erreur. Notez la prsence de la commande Exit sub. Elle est indispensable pour sortir du programme si tout se droule normalement.