Support de Cours VBA Excel

DELTALOG

MACROS ET PROGRAMMATION VBA SOUS EXCEL

Macros et Programmation VBA sous Excel

Sommaire I. Introduction A EXCEL VBA ........................................................................................................................ 5 II. L'enregistreur DE Macro ............................................................................................................................ 5 II.1. Enregistrer une Macro ......................................................................................................................... 5 II.2. Exécuter une Macro .............................................................................................................................. 6 II.3. Atelier .................................................................................................................................................... 6 III. L’environnement Visual Basic Editor ...................................................................................................... 7 III.1 L’explorateur de projets ....................................................................................................................... 7 III.2 La fenêtre propriétés............................................................................................................................ 8 III.3 La fenêtre Code .................................................................................................................................... 8 III.4 La fenêtre Exécution ............................................................................................................................ 9 III.5 La fenêtre Espions ............................................................................................................................... 9 III.6 L’explorateur d’objets ......................................................................................................................... 10 III.7 La boîte à outis ................................................................................................................................... 10 IV.Le langage VBA ......................................................................................................................................... 11 IV.1. Les variables et les constantes ......................................................................................................... 11 IV.1.1 Déclaration de variables .............................................................................................................. 11 IV.1.2 Les types des variables ................................................................................................................ 11 IV.1.3 Définition d’un type utilisateur .................................................................................................. 13 IV.1.4 La portée des variables ................................................................................................................ 13 IV.2 La déclaration des constantes............................................................................................................ 14 IV.3 L'instruction With...End With ........................................................................................................... 14 IV.4. Les énoncés conditionnels ................................................................................................................. 15 IV.4.1 L'instruction If écrite sur une seule ligne .................................................................................. 15 IV.4.2 Le bloc If ....................................................................................................................................... 15 IV.5 Les énoncés itératifs ........................................................................................................................... 16 IV.5.1 Utilisation d'instructions Do...Loop ............................................................................................ 16 IV.5.2 La boucle For...Next ..................................................................................................................... 17 IV.5.3 L’instruction Exit ......................................................................................................................... 17 IV.6 Travailler avec les tableaux ............................................................................................................... 18 IV.6.1 Déclaration d’un tableau ............................................................................................................. 18 IV.6.2 L’accès aux éléments du tableau................................................................................................. 18 IV.7 Les Entrées et Sorties Standards ...................................................................................................... 18 IV.7.1 La fonction MsgBox ..................................................................................................................... 18 IV.7.2 La Fonction InputBox .................................................................................................................. 19

Conseil et Ingénierie des systèmes d’information

2

Macros et Programmation VBA sous Excel IV.8 Procédures et Fonctions ..................................................................................................................... 20 IV.8.1 Les Fonctions ............................................................................................................................... 20 IV.8.2 Les procédures (Sub) ................................................................................................................... 20 IV.8.3 Utiliser le VBE pour insérer une procédure/fonction ................................................................ 20 V. Le modèle Objet d’Excel ............................................................................................................................ 21 V.1 Manipulation d’objets .......................................................................................................................... 23 V.1.1 Création d’un objet ........................................................................................................................ 23 IV.1.2 Le scope d’un objet ....................................................................................................................... 23 V.2 La base, l'objet Application ................................................................................................................. 24 V.2.1 Evènements ................................................................................................................................... 24 V.2.2 Propriétés ....................................................................................................................................... 24 V.2.3 Méthodes ........................................................................................................................................ 25 V.3 La collection WorkBooks ..................................................................................................................... 26 V.4 L’objet workbook (classeur) ................................................................................................................. 26 V.4.1 Méthodes de l’objet Workbook ...................................................................................................... 26 V.4.2 Evénements de l’objet Workbook.................................................................................................. 27 V.5 La collection Sheets (Les Feuilles) ...................................................................................................... 27 V.6 La collection Worksheets (Feuilles de calculs) ................................................................................... 27 V.6.1 Méthodes de la collection Worksheets ......................................................................................... 27 V.7 Feuille de calculs (WorkSheet) ............................................................................................................ 28 V.7.1 Propriétés de l’objet Worksheet .................................................................................................... 28 V.8 L’objet Range (Plage ou cellule) .......................................................................................................... 29 V.8.1 Propriétés de l’objet Range ........................................................................................................... 29 V.8.2 Méthodes de l’objet Range ............................................................................................................ 31 V.9 Conclusion ............................................................................................................................................ 32 VI. Débogage et gestion des erreurs.............................................................................................................. 33 VI.1 Les erreurs de compilation et d'exécution ........................................................................................ 33 VI.2 Les erreurs logiques ........................................................................................................................... 33 VI.3 Exemple pratique de débogage .......................................................................................................... 34 VI.4 Gestion des Erreurs ............................................................................................................................ 35 VII. Les objets Userform ................................................................................................................................ 37 VII.1 Créer un Objet UserForm ................................................................................................................. 37 VII.2 Afficher et fermer un objet UserForm ............................................................................................. 39 VII.2.1 Afficher un objet UserForm ....................................................................................................... 39 VII.2.2 Fermer ou masquer un objet UserForm ................................................................................... 40 VII.3 Associer du Code à Un Objet UserForm .......................................................................................... 40


3

Macros et Programmation VBA sous Excel Liste des Figures ............................................................................................................................................ 42 Liste des Tableaux ......................................................................................................................................... 42


4

Macros et Programmation VBA sous Excel I. INTRODUCTION A EXCEL VBA Excel VBA (Visual Basic pour Application) est un langage de programmation permettant d'utiliser du code Visual Basic pour exécuter les nombreuses fonctionnalités de l 'application EXCEL. Un programme écrit en VBA est souvent appelé une macro. Les macros permettent notamment d'automatiser des tâches répétitives réalisées sous EXCEL. Elles peuvent aussi être utilisées pour créer des boîtes de dialogue afin de rendre une application développée sous EXCEL plus conviviale. Une macro peut être créée en utilisant l'enregistreur de macros , qui ne nécessite aucune connaissance du langage VBA. Cependant une macro ainsi créée ne s'exécutera que sur un ensemble de cellules données et le code produit ne sera pas toujours très efficace. Pour pouvoir créer des macros propres à ses besoins, efficaces et interactives, il faut apprendre à programmer en VBA.

II. L'ENREGISTREUR DE MACRO L'enregistreur de macro permet d'écrire du code VBA à partir d'opérations effectuées manuellement sous EXCEL. II.1. ENREGISTRER UNE MACRO On va créer une macro qui va qui écrit dans la cellule A1 le texte "DELTALOG" en caractères gras 1. Ouvrez un nouveau classeur et enregistrez-le sous le nom Test-Macro.xlsx. 2. Les cellules dans une macro sont identifiées à l’aide d’une lettre (colonne) suivie d’un chiffre (ligne), comme par exemple la cellule A1. Afin de faciliter la lecture du code VBA généré, il est préférable de choisir la même identification des cellules dans le classeur dans lequel est enregistrée la macro. Si tel n’est pas le cas, cliquez sur le bouton office et choisissez la commande option Excel. Et dans Formules, décochez l’option « style de référence L1C1 ». 3. Positionner le curseur sur une autre cellule que la cellule A1 de la feuille de calcul FEUIL1. 4. Dans l’onglet « Développeur », choisissez la commande « enregistrer une nouvelle macro ». 5. Tapez « DELTALOG » dans la zone « Nom de macro » et ‘d’ dans la zone « raccourcis clavier ». 6. Dans la zone « Enregistrer la macro dans », sélectionner l’option « ce classeur ». 7. Dans la zone « Description », saisissez une description de ce que la macro est censée faire. 8. Cliquez sur le bouton « OK » Remarque: La zone ENREGISTRER LA MACRO DANS permet de déterminer l'endroit où la macro DELTALOG sera enregistrée. Il existe trois options :  Avec l'option Classeur de macros personnelles, la macro est enregistrée dans un classeur spécial appelé PERSO.XLS. Ce classeur est ouvert automatiquement lors du lancement d'Excel et ses macros sont donc disponibles dans tous les classeurs ouverts. La commande « Fenêtre

Afficher » permet d'afficher ce classeur et la commande « Fenêtre Masquer » de le masquer.  L'option Nouveau classeur permet d'enregistrer la macro dans un nouveau classeur.  L'option Ce classeur permet d'enregistrer la macro dans le classeur actif.


5

Macros et Programmation VBA sous Excel 9. La commande « Arrêter l’enregistrement » apparaît dans le ruban, onglet « développeur », ce qui marque le début de l'enregistrement. Par défaut, l'enregistrement d'une macro s'effectue en utilisant des références absolues aux cellules. 10. Positionner le curseur sur la cellule

A1, taper le texte "DELTALOG" et validez. Revenez dans

la cellule A1 et sélectionner le style gras. 11. Arrêter l'enregistrement en cliquant sur le carré bleu du ruban. Il ne faut jamais oublier d’arrêter l’enregistrement d’une macro. II.2. EXÉCUTER UNE MACRO 1. Effacer le contenu de la cellule A1. 2. Positionner le curseur sur une autre cellule que la cellule

A1

de la feuille de calcul

FEUIL1. 3. Dans l’onglet développeur, cliquer sur la

commande

macro,

sélectionner DELTALOG et appuyer sur « exécuter ». 4. Vous pouvez également exécuter la macro en utilisant le raccourci que vous avez défini lors de la création de la macro. 5. Vérifier que la chaîne de caractères « DELTALOG » s’insère dans la cellule A1.

II.3. ATELIER Créer une macro « MACROMOIS » qui écrit les noms de mois Janvier, Février et Mars en colonne à partir de la cellule active. Utiliser l'enregistreur de macro avec référence relative aux cellules.


6

Macros et Programmation VBA sous Excel III. L’ENVIRONNEMENT VISUAL BASIC EDITOR Nous avons vu comment créer une macro à l'aide de l'enregistreur de macro. Nous allons maintenant examiner le code VBA produit. Pour ce faire, il faut utiliser l'éditeur de Visual Basic, Visual Basic Editor (VBE), qui s'exécute dans une fenêtre différente de celle d'E xcel. Ouvrir VBE en cliquant sur la commande Visual Basic de l’onglet « développeur ».

Figure 1 L’environnement VBE

III.1 L’EXPLORATEUR DE PRO JETS A chaque classeur EXCEL ouvert est associé un projet VBA. L'explorateur de projets affiche une liste hiérarchisée des différents projets VBA associés aux classeurs EXCEL ouverts. Un projet VBA associé à un classeur regroupe les éléments du classeur, comme ses feuilles de calcul ou des boîtes de dialogue, et les procédures et les fonctions associées au classeur et stockées dans un ou plusieurs modules de code. Le projet VBA associé au classeur TEST-MACRO.XLS est composé de deux dossiers :  

le dossier MICROSOFT EXCEL OBJETS qui contient les éléments attachés au projet : le classeur TEST-MACRO.XLS (THISWORKBOOK) et ses feuilles de calcul FEUILI, FEUIL2 et FEUIL3 ; le dossier MODULES qui contient les modules de code du projet : le module MODULEI qui contient la macro « DELTALOG ».


7

Macros et Programmation VBA sous Excel III.2 LA FENETRE PROPRIETES A chaque fois que vous sélectionnez un module, une feuille ou des contrôles, La fenêtre propriété affiche ses propriétés. Cette fenêtre nous sera très utile pour changer les propriétés des objets. Nous pouvons par exemple changer le nom de la feuille de calcul « feuil1 » pour lui donner un nom plus significatif. Dans l’exemple ci-dessous, nous avons changé le nom de la première feuille du classeur « Feuil1 » par « Tarifs ». Vous verrez par la suite que cette fenêtre nous sera très utile lorsque l’on crée l’interface utilisateur.

Figure2. La fenêtre propriété de VBE III.3 LA FENETRE CODE La fenêtre Code permet d'écrire, d'afficher et de modifier le code Visual Basic. Vous pouvez ouvrir autant de fenêtres Code que vous avez de modules, ce qui vous permet d'afficher facilement le code des différentes feuilles ou modules et d'effectuer des opérations de copier-coller de l'un à l'autre. Vous pouvez ouvrir une fenêtre Code à partir de :  

la fenêtre Projet, en sélectionnant une feuille ou un module, et en choisissant le bouton Afficher le code ; une fenêtre UserForm, en double-cliquant sur un contrôle ou sur une feuille, en choisissant Code dans le menu Affichage ou en appuyant sur F7.

Figure3. Fenêtre code de VBE


8

Macros et Programmation VBA sous Excel III.4 LA FENETRE EXECUTION La fenêtre exécution permet la saisie de commande et de les exécuter directement. Cette fenêtre ne sera très utile lors du débogage

Figure4. La fenêtre exécution de VBE Vous pouvez également copier le code de la fenêtre Exécution pour le coller dans la fenêtre Code une fois assuré que le code fait bien ce qu’il est censé faire. Vous pouvez faire glisser et déplacer la fenêtre Exécution n'importe où sur votre écran, sauf si vous en faites une fenêtre ancrable à l'aide de l'onglet Ancrage de la boîte de dialogue Options. Pour fermer la fenêtre, cliquez sur la case de fermeture. Si celle-ci n'est pas visible, double-cliquez sur la barre de titre pour qu'elle apparaisse, puis cliquez sur la case de fermeture. III.5 LA FENETRE ESPIONS La fenêtre Espion est utilisée pour inspecter les valeurs de variable et est très utilisée pour debugger le code. Nous reviendrons en détail sur cette fenêtre lorsqu’on abordera le débogage.

Figure5. La fenêtre Espions de VBE


9

Macros et Programmation VBA sous Excel III.6 L’EXPLORATEUR D’OBJE TS

Figure 6. L'explorateur d'objets de VBE L’explorateur d’objets affiche les classes, propriétés, méthodes, événements et constantes disponibles dans les bibliothèques d'objets et les procédures de votre projet. Il vous permet de rechercher et d'utiliser des objets que vous créez ainsi que des objets provenant d'autres applications. Cet outil sera votre ami durant le développement (l’écriture de codes). Nous reviendrons plus en détails sur l’explorateur d’objets lorsque nous aborderons le modèle objets d’Excel. III.7 LA BOITE A OUTIS Affiche les contrôles Visual Basic standard ainsi que les contrôles ActiveX et les objets insérables placés dans votre projet. Pour personnaliser la boîte à outils en y ajoutant des pages ou des contrôles, utilisez la commande Contrôles supplémentaires du menu Outils. Lorsque vous insérez une page, l'outil Sélectionner des objets est toujours disponible sur cette page.


10

Macros et Programmation VBA sous Excel IV.LE LANGAGE VBA Nous présentons dans ce chapitre les éléments du langage VBA qui sont communs à toutes les applications MICROSOFT OFFICE et plus largement aux langages de programmation, même si la syntaxe et les mots clés varient d'un langage à l'autre. IV.1. LES VARIABLES ET LES CONSTANTES Une variable est un emplacement de stockage nommé contenant des données pouvant être modifiées au cours de l'exécution du programme. Chaque variable possède un nom qui l'identifie de manière univoque au sein de sa portée.

IV.1.1 Déclaration de variables Le mot clé dim permet de déclarer explicitement une variable, on écrira par exemple : dim NomVariable Où NomVariable est une suite de caractères formés avec des lettres, des chiffres et le caractère souligné. Le premier caractère est obligatoirement une lettre. Les minuscules sont distinguées des majuscules. Le langage VBA permet de ne pas déclarer explicitement les variables. Un des intérêts de la déclaration explicite des variables est d'éviter les erreurs de frappes malheureusement très fréquentes en programmation. Pour éviter ces erreurs, Il est préférable de forcer la déclaration explicite des variables en VBA. Pour ce faire, il suffit de placer l'instruction Option Explicit en haut des modules de code avant toutes procédures et toutes fonctions. Option Explicit

IV.1.2 Les types des variables La plupart des langages de programmation imposent de déterminer le type de données qui peut être stockée dans une variable lors de sa déclaration. En VBA, ce n'est pas obligatoire. Par défaut, une variable non typée est du type Variant, qui permet de stocker n'importe quel type de données. Dans un souci d'efficacité du code, il est préférable de typer ses variables. Une variable de type Variant prend, en effet, plus de mémoire que n'importe quel autre type et utilise plus de ressource système pour son traitement (identification du type effectif de la variable et éventuellement conversion). La déclaration du type d'une variable se fait comme suit: Dim NomVariable as Type ‘où type détermine le type de la valeur (voir tableau ci-dessous)


11

Macros et Programmation VBA sous Excel Les différents types disponibles en VBA sont résumés dans le tableau suivant : Type de données

Taille d'enregistrement

Plage

Byte

1 octet

0 à 255

Boolean

2 octets

True ou False

Integer

2 octets

-32 768 à 32 767

Long (entier long)

4 octets

-2 147 483 648 à 2 147 483 647

Single (à virgule flottante en simple précision)

4 octets

-3,402823E38 à -1,401298E-45 pour les valeurs négatives ; 1,401298E-45 à 3,402823E38 pour les valeurs positives

Double (à virgule flottante en double précision)

8 octets

-1,79769313486231E308 à -4,94065645841247E-324 pour les valeurs négatives ; 4,94065645841247E324 à 1,79769313486232E308 pour les valeurs positives

Currency (entier à décalage)

8 octets

-922 337 203 685 477,5808 à 922 337 203 685 477,5807

Decimal

14 octets

+/79 228 162 514 264 337 593 543 950 335 sans séparateur décimal ; +/-7,9228162514264337593543950335 avec 28 chiffres à droite du séparateur décimal ; le plus petit nombre différent de zéro est +/0.0000000000000000000000000001.

Date

8 octets

1er janvier 100 au 31 décembre 9999

Object

4 octets

Toute référence à des données de type Object

String (longueur variable)

10 octets + longueur de la chaîne

0 à environ 2 milliards

String (longueur fixe)

Longueur de la chaîne

1 à environ 65 400

Variant (nombres)

16 octets

Toute valeur numérique, avec la même plage de valeurs qu'une donnée de type Double

Variant (caractères)

22 octets + longueur de la chaîne

Même plage de valeurs qu'une donnée de type String de longueur variable

Type défini par l'utilisateur (avec Type)

En fonction des éléments

La plage de valeurs de chaque élément correspond à celle de son type de données.

Tableau 1. Les types de données VBA Exemples :


12


IV.1.3 Définition d’un type utilisateur En plus des types de données disponibles dans VBA, vous pouvez également définir de nouveaux types si besoin y est, et ce en utilisant le mot clé Type Dans l’exemple suivant, on définit le type Personne pour représenter les personnes du monde réel.

IV.1.4 La portée des variables La portée d'une variable définit quelles procédures ou fonctions peuvent utiliser cette variable. Les variables déclarées à l'intérieur d'une procédure ou d'une fonction ne sont accessibles qu'à l'intérieur de cette procédure ou de cette fonction. Exemple:

Pour qu'une variable soit accessible à l'ensemble des procédures et des fonctions d'un module, elle doit être déclarée au début du module à l'extérieur de toute procédure et de toute fonction. Pour qu'une variable soit accessible à l'ensemble des procédures et des fonctions d'un projet, elle doit être déclarée au début d'un module à l'extérieur de toute procédure et de toute fonction, à l'aide du mot clé Public. Exemple :


13


IV.2 LA DECLARATION DES CONSTANTES Une constante permet d'attribuer un nom à une valeur fixe. La déclaration d'une constante se fait à l'aide du mot clé Const comme suit : Const NomConstante as [type] =valeur Une constante a la même portée qu'une variable. Exemple :

IV.3 L'INSTRUCTION WITH...END WITH L'instruction With ….End With est utile lorsque les références à un objet sont répétées plusieurs fois dans une petite section de code. Exemple:

Tout ce qui commence par un point dans un bloc d'instructions With…. End With est une propriété ou une méthode de l'objet qui suit l'instruction With.


14

Macros et Programmation VBA sous Excel IV.4. LES ENONCES CONDITIONNELS VBA permet de traiter les énoncés conditionnels à l'aide de l'instruction If. On distingue l'instruction If écrite sur une seule ligne et le bloc If.

IV.4.1 L'instruction If écrite sur une seule ligne L'instruction If écrite sur une seule ligne a la syntaxe suivante : If (condition) Then Else Les sections Then et else d'une instruction If ne peuvent contenir qu'une seule instruction et doivent être écrites sur une seule ligne.

Exemple :

IV.4.2 Le bloc If La structure de bloc If permet aux sections Then et Else de contenir plusieurs instructions. Le bloc If peut avoir plusieurs sections ElseIf.

Exemple :


15

Macros et Programmation VBA sous Excel IV.5 LES ENONCES ITERATIF S VBA fournit deux structures pour répéter en boucle les mêmes instructions: l'instruction Do….Loop et l'instruction For….Next.

IV.5.1 Utilisation d'instructions Do...Loop Les instructions Do...Loop permettent d'exécuter un bloc d'instructions un nombre de fois indéfini, tant qu'une condition a la valeur True ou jusqu'à ce qu'elle prenne la valeur True. Vous pouvez utiliser le mot clé While de deux manières pour vérifier une condition dans une instruction Do...Loop. Vous pouvez contrôler la condition avant d'entrer dans la boucle, ou le faire après au moins une exécution de la boucle. Exemple :

Dans la procédure WhileAuDebut, vous vérifiez la condition avant d'entrer dans la boucle. Si numéro prend la valeur 9 et non 20, les instructions à l'intérieur de la boucle ne sont jamais exécutées. Dans la procédure WhileALaFin, les instructions à l'intérieur de la boucle ne sont exécutées qu'une seule fois avant que la condition prenne la valeur False. Vous pouvez utiliser à la place du mot clé While, le mot clé Until qui s'utilise lui aussi de deux manières pour vérifier une condition dans une instruction Do...Loop. Vous pouvez contrôler la condition avant d'entrer dans la boucle (comme dans la procédure WhileAuDebut), ou le faire après au moins une exécution de la boucle (comme dans la procédure WhileALaFin). La boucle se poursuit tant que la condition conserve la valeur False. Il existe également une structure pour l’exécution d’instructions itératives (la structure While…..Wend) mais on lui préférera la structure (Do….Loop).


16

Macros et Programmation VBA sous Excel IV.5.2 La boucle For...Next La boucle For...Next est utilisée lorsque l'on connait à l'avance le nombre de fois où l'on veut que les instructions soient répétées. Les instructions de la boucle For...Next sont exécutées un nombre déterminé de fois. Lorsque l'option Step (pas) n'est pas renseignée, le compteur est incrémenté de 1 à chaque itération. Exemple : La procédure suivante calcule la somme des 10 premiers entiers et affiche le résultat.

NB : Pour itérer dans les collections, on utilisera la structure For Each……..Next

IV.5.3 L’instruction Exit Il est parfois inutile de continuer à boucler dès qu’une condition se réalise. On utilisera l’instruction Exit à chaque fois que ce cas de figure se présente. L’exemple suivant teste si le nombre (10) est premier ou pas, dès qu’on découvre que 2 devise 10, on arrête d’itérer et on annonce que 10 n’est pas premier.

NB : Vous pouvez également sortir d’une fonction (Exit Function), d’une procédure (Exit Sub) et d’une boucle For (Exit For). Conseil et Ingénierie des systèmes d’information

17

Macros et Programmation VBA sous Excel IV.6 TRAVAILLER AVEC LES TABLEAUX Vous pouvez déclarer un tableau pour traiter un ensemble de valeurs du même type de données. Un tableau est une variable simple comportant de nombreux compartiments permettant de stocker autant de valeurs, par opposition à une variable classique dotée d'un compartiment destiné à une seule valeur.

IV.6.1 Déclaration d’un tableau L'instruction suivante déclare la variable de tableau Tarifs avec 365 éléments. Par défaut, un tableau étant indexé à partir de zéro, la limite supérieure de ce tableau est 364 et non 365. Dim Tarifs(364) As Currency Vous pouvez changer l’index par défaut comme suit : Dim Tarifs(1 to 365) As Currency Enfin, vous pouvez également déclarer un tableau en utilisant la fonction Array() et une variable de type Variant. Exemple : Dim ListeJours As Variant ListeJours = Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi", "Dimanche")

IV.6.2 L’accès aux éléments du tableau On accède aux éléments du tableau en utilisant leurs index. L’exemple suivant déclare une variable de type tableau nommée ListeJours et affiche l’élément qui est dans la 1ère position (index =0)

IV.7 LES ENTREES ET SORTIES STANDARDS Nous présentons deux fonctions d'entrées/sorties standards de VBA : la fonction MsgBox et la fonction InputBox.

IV.7.1 La fonction MsgBox La fonction MsgBox affiche un message dans une boîte de dialogue, attend que l'utilisateur clique sur un bouton, puis renvoie un entier indiquant le bouton choisi par l'utilisateur. Elle a la syntaxe suivante (les arguments entre crochets sont facultatifs) :

MsgBox(prompt[,buttons][,title][,helpfile, context]) Où prompt est le message affiché dans la boîte de dialogue, buttons correspond au type de boutons de la boîte de dialogue et title est le titre de la boîte de dialogue (cf. aide en ligne de l'éditeur de Visual Basic à l'aide de la touche F1.).


18

Macros et Programmation VBA sous Excel Exemple :

IV.7.2 La Fonction InputBox La fonction InputBox affiche une invite dans une boîte de dialogue, attend que l'utilisateur tape du texte ou clique sur un bouton, puis renvoie le contenu de la zone de texte sous la forme d'une chaîne de caractère. Elle a la syntaxe suivante (les arguments entre crochets sont facultatifs) :

InputBox(prompt[,title][,default][,xpos][,ypos] [,helpfile,context]) Où prompt est le message affiché dans la boîte de dialogue et title est le titre de la boîte de dialogue (cf. aide en ligne de l'éditeur de Visual Basic). Si l'utilisateur ne saisit rien dans la zone de texte, la fonction InputBox renvoie la chaîne de caractères vide "". Dans l’exemple suivant, on demande à l’utilisateur de saisir son age ; ensuite on l’affiche dans une autre boîte de dialogue.


19

Macros et Programmation VBA sous Excel IV.8 PROCEDURES ET FONCTIONS Tous les exemples utilisés dans ce manuel utilisent soit des fonctions ou des procédures. L’objectif de cette partie du manuel est de vous montrer comment utiliser l’IDE pour insérer une fonction ou une procédure dans vos modules.

IV.8.1 Les Fonctions VBA offre la possibilité de créer ses propres fonctions, qui peuvent être utilisées dans EXCEL comme n’importe quelle fonction intégrée. Une fonction est une suite d'instructions qui retourne une valeur. Elle commence par le mot clé Function suivi du nom de la fonction et d’une liste d’arguments entre parenthèses, qui peut être vide. Elle se termine par le mot clé End Function. Une fonction a la syntaxe suivante : Function NomFonction([argument_1,..., argument_n]) Instructions NomFonction = Expression

‘valeur de retour, on doit utiliser le nom de la fonction dans l’affectation

End Function Une fonction indique la valeur à retourner en initialisant son nom avec la valeur de retour.

IV.8.2 Les procédures (Sub) A l’inverse des fonctions, les procédures peuvent être utilisées pour exécuter une suite d’instructions sans pour autant retourner une valeur au programme appelant. Pour développer des procédures (Sub), utiliser la syntaxe suivante Sub NomProcédure(Argument1, Argument2,……,Argument2) Instructions End Sub Référez vous aux exemples développés dans les sections précédentes pour des exemples pratiques.

IV.8.3 Utiliser le VBE pour insérer une procédure/fonction Au lieu de saisir la déclaration d’une fonction/procédure, vous pouvez utiliser le menu Insertion de VBE pour saisir son squelette. Vous n’aurez par la suite qu’à ajouter du code dans son corps. Cela vous permettra de gagner beaucoup de temps. Cliquez sur le menu Insertion et choisissez l’entrée Procédure. Une boîte de dialogue vous permettant de spécifier le nom de la procédure/Fonction apparaît. Vous aurez également à spécifier le type (fonction, procédure ou propriété) et la portée (public ou privée)


20

Macros et Programmation VBA sous Excel V. LE MODELE OBJET D’EXCEL Nous allons à partir de maintenant travailler sur le modèle objet d’Excel. Contrairement à ce que l’on croit généralement, cette partie ne présente aucune difficulté par rapport aux bases du langage que nous avons vu précédemment. Lors de l’étude des objets, la liste des propriétés et méthodes que nous verrons sera limitée à une programmation de base d’Excel. Sachez qu’il existe dans chaque objet d’autres propriétés et méthodes que nous ne verrons pas dans ce cours mais qui peuvent vous être utiles. Le modèle objet d’Excel est un monstre énorme lorsqu’on le considère dans sa globalité. La documentation de chez Microsoft (MSDN library) le représente sous une forme conventionnelle d’arborescence. La figure ci-dessous présente une partie de ladite représentation :

Figure 7.Une partie du modèle Objet d'Excel 2007 Il n’est pas question ici de détailler l’ensemble de ce modèle objet. Nous allons nous intéresser à l’étude des principaux objets utiles dans des macros classiques. Plus explicitement, on détaillera l’objet Application, l’objet Workbook (classeur), l’objet worksheet (feuille de calcul) et l’objet Range (plage), en plus de quelques collections importantes.


21

Macros et Programmation VBA sous Excel Avant de procéder à la présentation de ces objets, un petit glossaire s’impose Terme

Explications

Objet

Groupe de code et de données réagissant comme une entité. La plupart des objets que nous allons aborder dans le modèle Excel ont une représentation graphique, ce qui les rends plus facile à appréhender.

Méthode

Dans la théorie Objet, tous les membres d’un objet sont des méthodes. Dans le jargon courant, les méthodes sont les fonctions ou les procédures d’un objet influant sur celui-ci.

Evénement

Un événement est un signal émis par un objet généralement lors d’une interaction ou d’un changement d’état de celui-ci. Ce signal peut être intercepté par des procédures ayant une signature définie par l’évènement.

Interface

En VBA, c’est l’ensemble des membres publics d’un objet. On entend par membre, les méthodes, propriétés et évènement d’un objet.

Collection

Une collection est un jeu d’éléments (objets) indexés. Ces objets sont généralement tous du même type (à l’exception de la collection Sheets)

Vu la densité du modèle objet d’Excel, il serait presque impossible d’avoir une vision simple des interfaces de chaque objet. Le rôle de l’explorateur d’objet est justement de mettre à notre disposition ces différentes interfaces. Ainsi le développeur peut à n’importe quel moment explorer les objets susceptibles d’être utilisés dans son code. Le tableau suivant présente toutes les icônes qui sont utilisées par l’explorateur d’objets.

Icône :

Représentant un/une/des :

Icônes

Représentant un/une/des

Propriété

Module

Propriété par défaut

Classe

Méthode

Type défini par l'utilisateur

Méthode par défaut

Global

Événement

Bibliothèque

Constante

Projet

Types et mots clés intégrés

Énumération

Tableau 2. Icônes utilisées dans l'explorateur d'objets


22

Macros et Programmation VBA sous Excel V.1 MANIPULATION D’OBJETS Un objet doit toujours être crée (instancié) et détruit (finalisé). Le moment entre ses deux événements est la durée de vie. Vous ne pouvez utiliser un objet que pendant sa durée de vie et uniquement dans sa portée, c'est-à-dire d’un endroit où il est visible.

V.1.1 Création d’un objet Pour créer un objet, on utilise soit le mot clé New ou la fonction CreateObject définie dans le module Interraction qui fait parti de la bibliothèque VBA. 1. Avec New Dans l’exemple suivant, on déclare une variable objet de type élève et on l’instancie en utilisant le mot clé New.

2. Avec la fonction CreateObject Le code suivant va créer une nouvelle instance de l’objet Sheet.

Il faut comprendre que dans le modèle Objet Excel et en utilisant VBA, vous n’aurez quasiment jamais à instancier les objets Excel, Il existe presque toujours une méthode Add où Excel prends le mécanisme de création de l’objet en charge. Autrement dit, vous n’avez pas à gérer l’instanciation ni la finalisation des objets Excel. Si l’on revient à l’exemple précédent, La ligne de code ci-après permet d’ajouter une feuille de calcul au classeur actif,

IV.1.2 Le scope d’un objet Le scope d’un objet est similaire au scope des variables, référez vous au chapitre précédent pour plus de détails.


23

Macros et Programmation VBA sous Excel V.2 LA BASE, L'OBJET APP LICATION Cet objet représente l'application Excel. Il est l'objet parent de tous les autres, et contient des propriétés méthodes et évènements très intéressants, que nous allons regarder maintenant.

V.2.1 Evènements On utilise rarement les événements au niveau de l'application, pour la raison simple qu'ils ne sont pas accessibles directement. Pour pouvoir les utiliser, il faut dans le projet créer un module de classe dans lequel on met le code suivant : Public WithEvents App As Application Comme la plupart des événements gérés se retrouvent dans les objets classeur et feuille, nous les utiliseront plutôt à ce niveau.

V.2.2 Propriétés En voici quelques propriétés très utiles de l'objet application Propriété

Explication

AskToUpdateLinks

Si sa valeur est False la mise à jour des liaisons se fait sans appel d'une boite de dialogue, qui sinon apparaît automatiquement

Calculation

Définit le mode de recalcul d'Excel. Il s'agit d'une propriété très importante pour l'optimisation du temps d'exécution. En effet, pour les feuilles contenant des formules de calcul, le mode de calcul automatique peut être très coûteux en temps. Excel recalcule intégralement une feuille de calcul à chaque modification ou suppression. C'est pourquoi, en général on bloque le mode de calcul au départ.

CutCopyMode

Définit si on utilise un mode par défaut copier ou couper. En fait, on l'utilise souvent sous la forme « Application.CutCopyMode=False » pour vider le presse papier

DisplayAlerts

Si sa valeur est False, les messages d'avertissements d'Excel ne seront pas affichés ✭ Il faut toujours remettre la valeur à True en fin d'exécution

Interactive

Désactive toutes les interactions entre Excel et le clavier/souris ✭ Il faut toujours remettre la valeur à True en fin d'exécution

International

Renvoie des informations relatives aux paramètres régionaux et internationaux en cours. Cette propriété est en lecture seule. On s'en sert généralement pour connaître les séparateurs

ReferenceStyle

Permet de basculer entre les modes L1C1 et A1. Il est à noter que cela change l'affichage des feuilles mais que cela peut aussi avoir une influence sur le code. En effet certaines formules comme les règles de validations doivent être écrites dans le même système de référence que celui de l'application.

ScreenUpdating

Permet de désactiver la mise à jour d'écran. Pour des raisons de vitesse d'exécution il est conseillé de toujours la désactiver ✭ Il faut toujours remettre la valeur à True en fin d'exécution, sous peine de récupérer un écran figé.


24

Macros et Programmation VBA sous Excel V.2.3 Méthodes

Méthode

Explication

Calculate

Permet de forcer le calcul. La syntaxe Application.Calculate est peu utilisée. On l'utilise principalement sous la forme MaFeuille.Calculate. Sachez toutefois que l'on peut restreindre le calcul à une plage à des fins de performance. Exemple : Worksheets(1).Rows(2:3).Calculate ne fait les calculs que sur les lignes 2 et 3.

Evaluate

Permet de convertir une chaîne en sa valeur ou en l'objet auquel elle fait référence. En voici quelques exemples : Interprétation de formule de calcul : Imaginons que ma cellule A1 contient le texte (12*3) +4, écrire : Range("A2").Value = Application.Evaluate(Range("A1").Value) Renverra 40 en A2. De même on pourra écrire : Resultat= Application.Evaluate("(12*3)+4") La méthode permet aussi d'évaluer une formule respectant la syntaxe Excel (en anglais) ; on peut écrire Resultat= Application.Evaluate("Sum(A1:E5)") Interprétation d'une adresse : Si ma cellule A1 contient B1:B2 je peux écrire Application.Evaluate(Range("A1").Value).Font.Bold=True

FindFile

Permet de lancer une recherche de fichier dans laquelle on spécifie les critères. La collection FoundFiles contient les fichiers trouvés correspondant.

GetOpenFileName

Ouvre la boite de dialogue "Ouvrir un fichier" mais n'ouvre pas le fichier. La méthode renvoie juste le nom complet du fichier sélectionné.

Intersect

Renvoie une plage qui est l'intersection de n plages. Bien que les plages appartiennent à des objets feuilles, la méthode Intersect appartient directement à l'objet Application.

OnKey

Permet l'exécution d'une macro lors de l'appui sur une touche ou sur une combinaison de touches. Application.OnKey "^{A}", "MaProc" lance la procédure MaProc sur l'appui de CTRL-A

OnTime

Permet l'exécution d'une macro à une heure fixée ou après un délai précisé. Application.OnTime Now + TimeValue("00:00:15"), "MaProc" attend 15 secondes avant d'exécuter la procédure MaProc


25

Macros et Programmation VBA sous Excel V.3 LA COLLECTION WORKBO OKS Cette collection contient l'ensemble des classeurs ouverts. L'ordre des classeurs dans la collection (index) est l'ordre d'ouverture. En voici les méthodes les plus importantes de cette collection : Méthode

Explications

Add

Permet d'ajouter un nouveau classeur à la collection et non pas d'ouvrir un classeur existant. Le classeur créé devient le classeur actif. Elle suit la syntaxe Worbooks.Add(Template). Si Template est omis un nouveau classeur standard est créé.

Close

Cette méthode ferme tous les classeurs de la collection. Donc attention : ne pas confondre WorkBooks.Close et WorkBooks(1).Close

Open

Ouvre un classeur Excel. .

V.4 L’OBJET WORKBOOK (CLAS SEUR) Le classeur est en général l'objet central de la programmation Excel. Lorsque l'application va utiliser un seul classeur, on peut utiliser ActiveWorkbook, mais dans le cas d'une application multi-classeurs, il convient de les mettre dans des variables afin d'en simplifier la manipulation.

V.4.1 Méthodes de l’objet Workbook Méthode

Explications

Close

Ferme le classeur. La syntaxe est : MonClasseur.Close(SaveChanges, FileName, RouteWorkbook) Si SaveChanges est omis, une boite de demande d'enregistrement apparaît. Donner une valeur FileName différentes de celle du classeur revient à faire un SaveAs.

PrintOut

Lance l'impression du classeur. Sa syntaxe est : MonClasseur.PrintOut(from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate). A noter que From et To sont des pages d'imprimante et non les feuilles du classeur, et que ActivePrinter permet de choisir l'imprimante

Protect / UnProtect

Active ou désactive la protection du classeur. La syntaxe est : MonClasseur.Protect(Password, Structure, Windows) pour activer la protection MonClasseur.UnProtect(Password) pour la retirer. A ce propos, la protection du classeur et des feuilles est une chose indépendante. La protection du classeur sert à bloquer la structure de celui-ci, celle de la feuille à protéger les objets qu'elle contient. On peut parfaitement protéger une feuille sans protéger le classeur et inversement.

Save / SaveAs

Sauvegarde le classeur ou l’enregistrer sous un autre nom


26

Macros et Programmation VBA sous Excel V.4.2 Evénements de l’objet Workbook L'objet classeur possède beaucoup d'événements, ceux qui sont le plus souvent utilisés sont détaillés dans le tableau suivant : Evénement(s)

Explications

BeforeClose, BeforePrint, BeforeSave

Se produisent respectivement avants la fermeture, l’impression et la sauvegarde du classeur.

Open

Se produit à l'ouverture du classeur. Permet donc de faire des macros à exécution automatique

SheetBeforeDoubleClick, SheetBeforeRightClick

Ces évènements sont des évènements de feuille. On utilise l'événement au niveau classeur lorsqu’on veut centraliser l'événement pour plusieurs feuilles du classeur. S'il existe aussi une procédure pour l'événement au niveau feuille, elle s'exécutera avant la procédure du classeur. Cet événement ne se produit pas sur les feuilles graphiques. L'objet "Sh" est un objet WorkSheet qui représente la feuille sur laquelle l'événement se produit.

V.5 LA COLLECTION SHEETS (LES FEUILLES) Un classeur Excel est composé d'une ou plusieurs feuilles. Ces feuilles peuvent être de cinq types : Feuille de calcul, Feuille graphique, Macro Excel4, Macro Excel4 International, feuilles de boîtes de dialogue Excel 5. La collection Sheets contient toutes les feuilles du classeur quel que soit leur type. En fait, on utilise très peu cette collection sauf pour connaître le nombre total de feuille avec la propriété Count ou parfois pour ajouter une feuille de type macro Excel4. V.6 LA COLLECTION WORKSHEETS (FEUILLES DE CALCUL S) Cette collection contient l'ensemble des feuilles de calcul du classeur. Le numéro d'ordre d'un objet WorkSheet dans cette collection est son ordre dans les onglets du classeur.

V.6.1 Méthodes de la collection Worksheets Méthode

Explication

Add

Cette méthode permet d’ajouter une voire plusieurs feuilles au classeur. L’argument Count détermine le nombre de feuilles à ajouter.

Copy

Worksheets(index).Copy(Before, After) Duplique une feuille dans le classeur ou crée une copie dans un autre classeur. Attention lors de la copie d'une feuille, la collection "Names" des plages nommées appartient à l'objet WorkBook et à l'objet WorkSheet. Lors de la duplication de la feuille, Excel va créer deux noms presque identiques, ce qui peut engendrer des bugs.

Delete

Supprime la feuille spécifiée par index. Un classeur doit toujours contenir au moins une feuille.


27

Macros et Programmation VBA sous Excel FillAcrossSheets

WorkSheets(collection). FillAcrossSheets (Range, Type) Permet de recopier une plage sur plusieurs feuilles en même temps. Range détermine la plage à copier, Type définit le mode de copie (xlFillWithAll, xlFillWithContents ou xlFillWithFormulas). Il faut passer une collection ou un tableau d'objets WorkSheet contenant les feuilles concernées par la recopie à la méthode. Cette collection doit toujours contenir la feuille contenant la plage source.

Move

Worksheets(index).Move(Before, After) Similaire à copy mais déplace la feuille. Dans ce cas il n'y a pas de problème avec les noms.

V.7 FEUILLE DE CALCUL S (WORKSHEET) L'objet WorkSheet doit suivre les mêmes règles de programmation que l'objet classeur. On utilise ActiveSheet que dans le cas d'une application ne mettant en jeu qu'une seule feuille. Sinon on référence la feuille par : Dim MaFeuille as WorkSheet Set MaFeuille=ActiveWorkSheet

V.7.1 Propriétés de l’objet Worksheet

Propriété

Explications

Names

Renvoie la collection des noms spécifique à la feuille de calcul. Attention les plages nommées appartiennent à l'objet WorkBook. La collection Names renvoyée par cette propriété ne contient donc pas les plages nommées contenues par la feuille sauf si l'on a défini ce nom comme spécifique .

PageSetup

Renvoie un objet PageSetup qui contient toutes les informations de mise en page de la feuille.

Shapes

Renvoie la collection Shapes de toutes les formes présentes sur la feuille. Cette collection peut contenir beaucoup d'objets de types différents. On n’abordera pas dans cette formation la programmation en utilisant l’objet Shape.

Visible

Affiche ou masque la feuille. La propriété peut prendre la valeur xlVeryHidden qui masque la feuille de telle façon qu'elle ne puisse être rendue visible que par le code ou par l'explorateur de projet.


28

Macros et Programmation VBA sous Excel V.8 L’OBJET RANGE (PLAGE OU CELLULE) Nous allons aborder ici la clé de la programmation Excel VBA. Une plage de cellules (objet Range) représente n'importe quel groupe de cellules d'une feuille. La plage peut être constituée d'une cellule, d'une plage continue ou de plusieurs plages. L'objet Range, lorsqu'il représente des cellules, appartient toujours à un objet WorkSheet. L'objet Range ne possède pas d'événement spécifique, néanmoins on peut lui faire utiliser les événements de feuille ou de classeur. Excel fonctionne avec un système d'adresse. Il existe en fait deux styles de référence, comme déjà évoqué au début de ce manuel : 1. R1C1 : Les colonnes et les lignes sont représentées par des nombres 2. A1 : Les colonnes sont des lettres et les lignes des nombres. Le changement de style au cours d'un programme ne pose pas de problème vis-à-vis des formules déjà existantes dans une feuille. Sachez toutefois que le code doit être rédigé dans le même style de référence que l'application, sous peine de voir parfois se déclencher des erreurs. En mode A1 on utilise un range comme suit : MaFeuille.Range("A1") : une cellule MaFeuille.Range("A1:E10") ou Range("A1", "E10") : une plage continue En mode R1C1 MaFeuille.Cells(1,1) ‘ une cellule MaFeuille.Range(Cells(1,1),Cells(10,5)) ‘ une plage continue Quel que soit le style, les plages discontinues s'obtiennent à l'aide de la méthode Union de l’objet Application.

V.8.1 Propriétés de l’objet Range

Propriété(s)

Explication

Address

MonRange.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo). RowAbsolute et ColumnAbsolute détermine si la référence renvoyée est relative ou absolue, ReferenceStyle détermine le style de référence, External ajoute le nom du classeur et le nom de la feuille. Comme nous l'avons vu, peu importe le style utilisé puisque la propriété Address renvoie la référence dans le style désiré.

Column, row

Renvoie le numéro de la première colonne / ligne de la plage

ColumnWidth, RowHeight

Renvoie ou définit la largeur / hauteur des colonnes / lignes de la plage.

Formula FormulaR1C1

Définit ou renvoie la formule de la plage, cette formule étant en anglais.

FormulaLocal

Identique à Formula mais dans la langue de l'utilisateur. Conseil et Ingénierie des systèmes d’information

29

Macros et Programmation VBA sous Excel Hidden

Masque une ou plusieurs ligne(s)/colonne(s). Attention l'ensemble de la ligne ou de la colonne doit être sélectionnée.

HorizontalAlignment

Définit l'alignement dans la plage. Peut permettre un centrage multi-colonnes si la plage contient plusieurs colonnes.

Locked

Renvoie ou définit si les cellules sont verrouillées. Le verrouillage n'a aucun effet tant que la feuille n'est pas protégée.

MergeCells

Renvoie Vrai si la cellule fait partie d'une plage fusionnée. A ce propos, évitez si possible de fusionner les cellules. C'est très souvent une source de problèmes.

Name

Renvoie ou définit le nom de la plage. Comme nous le verrons tout au long de la formation, les plages nommées sont très utiles.

NumberFormat

Renvoie ou définit le format des cellules de la plage. Lors de la lecture, renvoie NULL si tous les formats ne sont pas identiques (la plage contient plusieurs cellules).

ShrinkToFit

Force le contenu à s'adapter aux dimensions de la cellule.

WrapText

Force le retour à la ligne si le contenu dépasse la largeur de la cellule.

Areas

Renvoie la collection Areas de l'objet Range. Si la plage est une plage continue, la collection Areas ne contient qu'un élément qui est l'objet Range. Si cette plage contient plusieurs plages discontinues, il y a autant d'éléments que de plages continues dans l'objet Range.

Borders

Renvoie une collection des objets Border d'une cellule ou d'une plage. On peut manipuler tous ces objets en même temps avec un appel à Borders ou en spécifier un avec sa propriété Item.

Cells

Cette propriété renvoie un objet Range (une cellule) avec des coordonnées relatives à la première cellule de l'objet Range. Il faut faire très attention à cette erreur relativement fréquente.


30

Macros et Programmation VBA sous Excel Characters

Renvoie les caractères d’un objet texte. L’exemple suivant modifie la police du texte saisi dans la plage ‘plage’.

Columns / Rows

Renvoie la collection de toutes les colonnes / lignes contenues dans la plage. Cela peut permettre certains raccourcis de programmation intéressant. Par exemple : MaPlage.Columns(2).Value="" Efface le contenu de toutes les cellules de la colonne 2 dans la plage.

Dependents DirectDependents Precedents DirectPrecedents

Interior

Renvoie un objet Range contenant les cellules dépendantes ou antécédentes. Une cellule antécédente est une cellule qui contient dans sa formule de calcul la référence à la cellule cible. Une cellule dépendante est une cellule dont la référence est contenue dans la formule de calcul de la cellule cible. L'objet Range renvoyé peut être une plage multiple. Cette propriété peut être extrêmement intéressante pour déterminer quelle plage doit être recalculée. Nous verrons des utilisations dans la deuxième partie, mais je vais vous donner un exemple.

Rnvoie le motif utilisé dans une plage. L’exemple suivant applique la couleur rouge pour le fond de la cellule B2.

V.8.2 Méthodes de l’objet Range Méthode(s)

Explication

Copy

Cette méthode utilise ou non l'argument Destination. Lorsque l'argument est omis, la méthode copie la plage dans le presse-papiers, sinon le collage à lieu dans le même temps. Sauf cas très particulier il faut toujours préciser l'argument Destination. Cet argument doit être soit un objet Range de la même dimension que la plage source, soit une cellule unique qui deviendra le coin supérieur gauche de la plage collée.

Insert /Delete

Permet d'insérer ou de supprimer une plage de cellules. Utilise l'argument Shift pour définir le sens de déplacement des cellules. Pour insérer une ligne ou une colonne entière utiliser EntireRow / EntireColumn

AddComment

Ajoute un commentaire pour une plage composée d’une seule cellule.


31

Macros et Programmation VBA sous Excel Calculate

Vous pouvez appeler cette méthode dans un objet de type Worksheet, mais pour des fins de performance, il vaut mieux recalculer juste une plage.

SpecialCells

Revoie toutes les cellules de la plage qui sont d’un type particulier. Le code suivant récupères toutes les cellules commentées dans la plage A1 :E9

V.9 CONCLUSION Nous avons vu ensemble une petite partie du modèle objets d’Excel. La programmation Excel VBA est relativement simple et repose en fait sur votre connaissance du modèle objet et donc sur votre connaissance d’Excel. Pour une vue plus globale des objets définis dans Excel, je vous invite à consulter MSDN (Microsoft Developers Network) et à participer activement dans les forums qui traitent du sujet.


32

Macros et Programmation VBA sous Excel VI. DEBOGAGE ET GESTION DES ERREURS Le débogage consiste à régler les erreurs directement liées au code d'un programme. Trois types d'erreur peuvent affecter un programme écrit en VBA :

•

des erreurs de compilation qui surviennent lorsque VBA rencontre une instruction qu'il ne reconnaît pas ;

•

des erreurs d'exécution : les erreurs d’exécution sont difficiles à déceler, Il faut gérer les erreurs d’exécution au niveau de chaque procédure/fonction développée afin de découvrir ce genre d’erreurs.

•

des erreurs logiques : le programme s'exécute mais le résultat obtenu ne correspond pas à celui attendu.

VI.1 LES ERREURS DE COMPI LATION ET D'EXECUTIO N La première étape pour tester un programme consiste à compiler le programme à l'aide de la commande « Débogage, Compiler vbaproject ». Les erreurs de compilation sont mises en évidence. Recommencer la compilation jusqu'à ce que toutes les erreurs de compilation soient corrigées. Lorsqu'il n'y a plus d'erreurs de compilation, « Exécuter le programme » à l'aide de la commande « Exécution Exécuter Sub/UserForm ». Si une erreur d'exécution est générée, l'instruction coupable est mise en évidence. Corriger l'erreur et réinitialiser le programme à l'aide de la commande « Exécution, Réinitialiser » Recommencer l'exécution jusqu'à ce que toutes les erreurs d’exécution soient corrigées. VI.2 LES ERREURS LOGIQUES Il s'agit maintenant de corriger les erreurs logiques qui sont les plus difficiles à repérer. L'éditeur de Visual Basic propose plusieurs outils de débogage dans le menu DEBOGAGE. Les principaux outils de débogage offerts par le VBE sont : 1. Exécution pas à pas : pour exécuter un programme pas à pas, placer le curseur dans la procédure à exécuter et activer la commande « Débogage, pas à pas détaillé ». L'exécution d'une procédure pas à

2.

3.

4.

5. 6.

pas permet d'en examiner le déroulement instruction après instruction. La procédure est dite en mode arrêt. On peut à tout moment retourner à un mode d'exécution normal. Les bulles d'aide: pour visualiser la valeur d'une variable, activer la commande Outils, Options. Cliquez ensuite sur l’onglet Editeur et cochez la case Info-bulles automatiques. Lorsque le curseur est placé au dessus d'une variable, la valeur de celle-ci apparaît dans une bulle d'aide. La fenêtre Variables locales : pour visualiser la valeur des variables et constantes aux différents stades de l'exécution pas à pas d'un programme, activer la commande « Affichage, Fenêtre variables locales » Les points d'arrêt: les points d'arrêt permettent d'interrompre l'exécution d'un programme sur une instruction précise. Pour définir un point d'arrêt sur une instruction douteuse, activer la commande « Débogage, Basculer le point d’arrêt ». Les espions: pour espionner la valeur d'une variable, activer la commande « Débogage, Ajouter un espion ». Pour afficher la fenêtre espions, activer la commande « Affichage, Fenêtre Espions ». La pile d'appels: la pile d'appels recense toutes les procédures ou fonctions en cours d'exécution, selon leur ordre d'appel. Pour afficher la pile des appels, activer la commande « Affichage, Pile des appels ».


33

Macros et Programmation VBA sous Excel VI.3 EXEMPLE PRATIQUE DE DEBOGAGE Soit la fonction VerifieSiPremier() dont le code est comme suit :

La fonction affiche à la fin de son exécution que le nombre 20 est premier, ce qui n’est pas normal. Pour découvrir le problème, nous allons utiliser les points d’arrêts qu’on mettra dans les instructions susceptibles d’avoir causé l’erreur. NB : L’erreur dans ce cas est une erreur logique. Le code a été compilé avec succès et la fonction s’est exécutée. La figure suivante affiche l’aspect du VBE après avoir ajouté des points d’arrêts.

Remarquez d’ores l’info-bulle qui apparaît lorsque l’on met le pointeur de la souris au dessus de la variable n. Conseil et Ingénierie des systèmes d’information

34

Macros et Programmation VBA sous Excel Affichant la fenêtre des variables locales pour visualiser leurs changements durant l’exécution du code. Toutes les variables locales utilisées dans la fonction y sont énumérées en spécifiant leurs valeurs actuelles et leurs types.

Vous pouvez également utiliser la fenêtre Exécution pour évaluer une expression. Dans cet exemple, nous l’avons utilisé pour évaluer l’expression ( n - Int( n / i ) ) qui est utilisée dans la condition de l’instruction If. L’erreur logique est décelée, au lieu d’utiliser la condition actuelle, il fallait plutôt écrire : If ((n - Int(n / i) * i) = 0) Then En effet, lorsque l’on exécute la fonction en mode débogage, on constate que le reste de la division de 20 sur 2 est de 10, alors que le programme devrait trouver 0. Les erreurs logiques sont courantes et difficiles à corriger. Il faut savoir utiliser tous les outils de débogage disponibles dans l’éditeur VBE. VI.4 GESTION DES ERREURS La gestion des erreurs dans VBA nécessite l’ajout de code supplémentaire pour toutes les procédures et fonctions que vous allez développer. Le gestionnaire d’erreur est le code ajouté par le programmeur pour capter une éventuelle erreur afin soit de la cacher à l’utilisateur final ou afin de la lui expliquer avec un message personnalisé qu’il peut comprendre facilement. Dans l’exemple suivant, nous avons simulé une division par zéro. Lorsqu’on exécute la fonction, Un message d’erreur apparaît, mais ce même message n’indique pas à l’utilisateur final ce qu’il faut faire.

En ajoutant un gestionnaire d’erreur, Nous pouvons afficher un message personnalisé.


35


En plus de l’affichage d’un message plus approprié, le gestionnaire d’erreur s’assure de terminer la fonction convenablement. La gestion des erreurs répond toujours au schéma suivant Sub NomProcedure (arguments) On Error GoTo Erreur Instructions de la procedure Exit_Procedure : Exit Sub Erreur : Gérer l’erreur, afficher un message, ou cacher l’erreur si elle n’est pas grave, ….etc. Resume Exit_Procedure ‘Aller à l’étiquette Exit_Procedure pour terminer la procédure comme il se doit End Sub


36

Macros et Programmation VBA sous Excel VII. LES OBJETS USERFORM

Les objets UserForm sont des boîtes de dialogue définies par l'utilisateur. Ce chapitre explique comment créer une boîte de dialogue permettant de saisir les clients d'une entreprise, comme le montre la capture d'écran e :

1. Ouvrir un nouveau classeur et l'enregistrer sous SAISIECLIENTS.XLS. 2. Pour renommer la feuille de calcul FEUILI, cliquer avec le bouton droit sur l'onglet FEUILI, activer la commande RENOMMER et taper CLIENTS. 3. Saisir dans la feuille de calcul CLIENTS, les données suivantes :

4. Renommer DIVERS la feuille de calcul FEUIL2 et saisir les données suivantes :

On peut distinguer deux phases dans la création d'un objet UserForm : A. Le dessin de l'objet UserForm Le dessin d'un objet UserForm consiste à placer des contrôles sur l'objet. Les contrôles sont les éléments constitutifs d'un objet UserForm tels qu'une case à cocher, une zone de texte, une zone de liste modifiable ou un bouton de commande permettant une intervention de l'utilisateur. Un contrôle est un objet : il possède des propriétés, des méthodes et des événements définis. B. l'association de code à l'objet UserForm et à ses différents contrôles Il s'agit de déterminer le comportement de l'objet UserForm et de ses contrôles face aux différents événements utilisateur pouvant l'affecter et d'écrire le code permettant d'exploiter les actions effectuées par l'utilisateur. VII.1 CREER UN OBJET USERFORM 1. Ouvrir l'éditeur de Visual Basic et activer la commande « Insertion UserForm ». Un objet UserForml apparaît, ainsi qu'une boîte à outils permettant d'ajouter des contrôles à l'objet.


37


Figure 8.Création et conception de l'objet UserForm

L'explorateur de projets permet d'afficher le code d'un objet UserForm à l'aide du clic droit de la souris et de l'option CODE ; ou d'afficher l'objet lui-même à l'aide du clic droit de la souris et de l'option « Afficher l'objet ». 2. Modifier le nom de l'objet UserForm par défaut, UserForml, en FenetreSaisieClients dans la propriété NAME de la fenêtre Propriétés. Ce nom est le nom qui sera utilisé dans le code pour faire référence à l'objet UserForm. La propriété Caption de l'objet UserForm correspond au libellé qui apparaît dans sa barre de titre. Modifier cette propriété en « Saisie Clients » . Le formulaire qu’on veut créer est composé des contrôles suivants : Un

INTITULE et une ZONE DE LISTE pou saisir le titre du client (Madame, Mademoiselle ou Monsieur). Trois INTITULES et trois ZONES DE TEXTE pour saisir le nom, le prénom et le téléphone du client. Deux BOUTONS DE COMMANDE OK et Annuler. MODIFIABLE

Pour placer un contrôle dans l'objet UserForm, cliquer sur l'objet voulu de la BOITE A OUTILS et le faire glisser vers l'objet UserForm. Dès qu'un contrôle a été placé sur l'objet UserForm, définir son nom grâce à la propriété NAME.


38

Macros et Programmation VBA sous Excel Attention: Il est recommandé d'utiliser des noms évocateurs pour ses contrôles, qui permettent d'identifier le type d'objet qu'ils représentent et leur utilité. 4. Placer les différents contrôles de l'objet UserForm et modifier leurs propriétés comme suit: Contrôle

propriété NAME

propriété CAPTION

propriété FONT POLICE

Times New Roman,

Intitule

IntituléTitre

Zone de liste modifiable

ComboBoxTitre

Intitule

IntituléNom

Zone de texte

TextBoxNom

Intitule

IntituléPrénom

Zone de texte

TextBoxPrénom

Intitule

IntituléTél

Zone de texte

TextBoxTél

Bouton de Commande

ButtonOK

OK

‘’

Bouton de Commande

ButtonAnnuler

Annuler

‘’

Titre :

Taille 10 "

Nom :

‘’ ‘’

Prénom :

‘’ ‘’

Téléphone :

‘’ ‘’

Tableau 3. Propriétés des contrôles de l'objet UserForm VII.2 AFFICHER ET FERMER UN OBJET USERFORM

VII.2.1 Afficher un objet UserForm L'instruction Load permet de charger un objet UserForm en mémoire sans l'afficher. La méthode Show de l'objet UserForm permet d'afficher un objet UserForm et de le charger en mémoire, si cela n'a pas déjà été fait. Ecrivons la procédure qui permet d'afficher la boîte de dialogue de saisie d'un client. 1. Dans l'éditeur de Visual Basic, activer la commande « Insertion Module ». Un module de code ModuleI s'ouvre. Renommer le en ModuleSaisieClients à l'aide de la propriété NAME. 2. Saisir la procédure suivante dans le module de code :

3. Exécuter cette procédure (pour fermer la boîte de dialogue FenetreSaisieClients, cliquer sur son bouton de fermeture X). Conseil et Ingénierie des systèmes d’information

39

Macros et Programmation VBA sous Excel VII.2.2 Fermer ou masquer un objet UserForm L'instruction Unload permet de fermer un objet UserForm et de l'effacer de la mémoire, les valeurs de ses contrôles sont alors perdues. La méthode Hide de l'objet UserForm permet de faire disparaître un objet UserForm de l'écran sans le supprimer de la mémoire. L'instruction Unload ou la méthode Hide sont généralement placées dans les procédures événementielles attachées aux boutons de validation de l'objet UserForm, comme par exemple les boutons de commande OK et Annuler. VII.3 ASSOCIER DU CO DE A UN OBJET USERFORM Associer le code nécessaire au bouton de commande OK pour que la boîte de dialogue soit fermée lorsque l'on clique dessus. Aller dans l'éditeur de Visual Basic. Dans l'explorateur de projets, sélectionner l'objet UserForm de nom FenetreSaisieClients et ouvrir son module de code à l'aide du clic droit de la souris et de l'option CODE (ou en double-cliquant dessus). 1. Dans la liste de gauche au sommet du module de code, sélectionner l'option ButtonOK. Dans la liste de droite au sommet du module de code, sélectionner l'événement Click. Complétez le code pour aboutir à ce qui suit :

2. Aller dans l'éditeur de Visual Basic. Dans l'explorateur de projets, sélectionner l'objet UserForm de nom FenetreSaisieClients et ouvrir son module de code à l'aide du clic droit de la souris et de l'option Code. 3. Dans la liste de gauche au sommet du module de code, sélectionner l'option UserForm. Dans la liste de droite au sommet du module de code, sélectionner l'événement Initialize. 4. Compléter la procédure événementielle comme suit :

Ce code sert à affecter les valeurs "Madame", "Mademoiselle" et "Monsieur" à la zone de liste modifiable ComboBoxTitre, en utilisant les valeurs qui se trouvent dans la plage de cellule A1 :A3 de la feuillle de calcul DIVERS. Il nous reste juste à ajouter les informations saisies par l’utilisateur dans la feuille CLIENTS Ouvrez le module de code de l(objet UserForm et complétez la procédure événementielle ButtonOK_Click comme suit


40


Après avoir exécuté le module UserForm, rempli les champs du formulaire et cliqué sur le bouton OK, on obtient :


41

Macros et Programmation VBA sous Excel LISTE DES FIGURES Figure 1 L’environnement VBE ...................................................................................................................... 7 Figure2. La fenêtre propriété de VBE ............................................................................................................ 8 Figure3. Fenêtre code de VBE ......................................................................................................................... 8 Figure4. La fenêtre exécution de VBE ............................................................................................................ 9 Figure5. La fenêtre Espions de VBE............................................................................................................... 9 Figure 6. L'explorateur d'objets de VBE ....................................................................................................... 10 Figure 7.Une partie du modèle Objet d'Excel 2007 ..................................................................................... 21 Figure 8.Création et conception de l'objet UserForm .................................................................................. 38

LISTE DES TABLEAUX Tableau 1. Les types de données VBA .......................................................................................................... 12 Tableau 2. Icônes utilisées dans l'explorateur d'objets ................................................................................ 22 Tableau 3. Propriétés des contrôles de l'objet UserForm ............................................................................ 39


42

Support de Cours VBA Excel

Recommend Documents