INFORMATIQUEChapitre 1 : IntroductionI Importance de Visual Basic

Excel 97/2000 permet d’utiliser 2 langages de programmation. L’ancien langage de programmation d’Excel 4, qui n’existe + que pour assurer la

comptabilité avec les applications existantes. Cet ancien langage de macro commandes est tout à fait comparable à ceux des tableurs Quattro Pro ou Lotus. La survie de ce langage n’est + assurée.

Visual Basic For Application que Microsoft recommande d’utiliser pour les nouvelles applications depuis la sortie d’EXCEL 5.

La stratégie de Microsoft est de servir de ce langage VBA pour uniformiser l’automatisation de tous ses produits bureautiques, y compris le traitement de textes WORD et la base de données ACCESS.C’est également un dérivé de Visual Basic qui est utilisé dans les Active Server Pages (ASP) destinées à générer dynamiquement des pages HTML pour le World Wide Web.

Remarque :En face des navigateurs comme Internet Explorer se trouvent des serveurs http comme IIS (Internet Information Server).HTTP est LE protocole utilisé pour la transmission des données sur le World Wide Web.HTML est LE langage de description de pages le plus répandu sur le Web.(Il existe maintenant un langage plus général et plus puissant, XML, mais celui-ci n’est encore reconnu que par de rares navigateurs).Le langage des ASP (c’est-à-dire 1 VB) est interprété par le serveur IIS pour générer du HTML qu’IIS encapsulera ensuite dans le protocole HTTP pour l’envoyer sur le réseau.VB pour Application, en Anglais, Visual Basic For Application est donc un langage d’une grande importance dans la stratégie Microsoft. C’est lui que nous étudierons ici. Nous abrégerons son nom en VBA.VBA est un langage complet, moderne, qui n’a pratiquement plus rien à voir avec les premières versions du langage BASIC. Sa puissance expressive approche celle de PACAL ou C.Puisque les actions réalisées par le code VBA s’effectuent à la demande de l’utilisateur, ce code est toujours écrit sous forme de sous programme. Les sous programmes de type procédure (Subroutine ou Sub) servent à réaliser des

actions. Le cas le plus simple est celui d’une procédure sans paramètre qui peut être appelé par un clic sur un bouton (c’est ainsi que ce programme en VBA l’équivalent des anciens macro). Les procédures avec paramètres ne peuvent être appelées que depuis une autre procédure ou fonction.

Les sous programmes de type Fonction (Function) ont pour but de calculer une valeur et de la renvoyer à l’appelant. Cet appelant peut être une autre procédure ou fonction, ou une formule d’un tableau EXCEL.

C’est ainsi qu’on peut créer de nouvelles fonctions de feuille de calcul.L’appel d’une fonction peut également être utilisé comme terme dans une expression.

Exemple : z = (max (x; y) + 3) / 6L’appel de fonction : max (x; y)est inclus dans une expression où sa valeur est augmentée de 3 puis divisée par 6 avant d’être affectée à z.

VBA possède une structure de blocs pour les instructions conditionnelles : If ... End if Select case ... End selectet pour les instructions répétitives (boucles) : Do ... Loop For ... Next For Each ... Next While ... Wendcontrairement à PASCAL ou C, cette structure n’est pas généralisée.En effet, PASCAL ou C permettent de créer des blocs, n’importe où dans le programme en les encadrant par Begin ... End pour PASCAL ou par [ ... ] pour C.VBA, au contraire, ne crée des blocs que dans les cas d’instructions conditionnelles et des instructions répétitives (boucles) vues plus haut.VBA est orienté objets. Les objets y ont utilités : fournir un interface entre le programme VBA et le contenu des feuilles de calcul EXCEL

(par exemple : les objets de classe Range, qui représentent des ensembles de cellules (on peut traduire Range par étendue, zone ou domaine)

permettre la réalisation d’interfaces graphiques permettant au programme VBA de communiquer avec l’utilisateur (objets boutons, objets boîtes de dialogue, objets zones de texte).

permettre au programme VBA de communiquer avec d’autres applications grâce à COM (jadis ce produit s’appelait OLE (Object Linking and Embedding = lier et incorporer, mettre dans le lit)).

COM est un mécanisme permettant d’appeler des objets d’autres applications (MS-WORD) ou MS-ACCESS à partir du VBA d’EXCEL par exemple, COM permet d’inclure un document Word dans un tableau Excel, ou inversement. VBA permet au programmateur de définir et d’utiliser des procédures et des fonctions avec passage d’arguments : soit positionnel (les arguments sont identifiés par leur position dans un liste), soit par nom.Par ailleurs, ces paramètres peuvent être passées par valeur (on en passe une copie) ou référence (on passe leur adresse).Les procédures et fonctions sont le moyen naturel d’étendre le langage pour l’adapter aux besoins.

II Modules

Dans les tableurs qui ont précédé Excel (Multiplan, Lotus 123, Quattro Pro), les macro-commandes étaient incluses dans les feuilles de calcul elle-même; moyennant une syntaxe particulière pour les différencier des fonctions de feuille de calcul. Il y avait donc mélange de données et de programmes dans une même entité.

Dès qu’il était nécessaire de modifier une macro-commande, il pouvait être nécessaire de déplacer cette macro ou de la faire déborder dans des zones de données qui devraient à leur tour être déplacées.

Le travail de maintenance était très lourd et exigeait souvent des personnels de niveau MIAGe.

Cette procédure était une procédure artisanale conçu pour des tableurs artisanaux. Depuis Microsoft a doté les tableurs d’une véritable structure de langage informatique,

où les programmes sont séparés des données (ou presque, car les cellules peuvent toujours contenir des appels de fonction).

Pour séparer les programmes des données, les procédures et fonctions VBA pour EXCEL 97 et EXCEL 2000 se situent dans des structures particulières nommées modules.Il est également possible d’associer des procédures à des évènements liés à des objets présent sur des feuilles de calcul ou des boîtes de dialogue.Par exemple si l’évènement positionnement du pointeur de souris sur l’objet “bouton A” se produit, alors déplacer “bouton A”.

La blague classique consiste à créer une boîte de dialogue avec le texte “êtes-vous capable de cliquer sur ce bouton ?”. Lorsque l’utilisateur tente d’approcher le pointeur de souris sur le bouton ... celui-ci s’éloigne!!!

III Langue utilisée

Contrairement à VBA d’EXCEL 5, le VBA d’EXCEL 97 et d’EXCEL 2000 ne permet plus de programmer dansd’autres langues que l’Anglais.L’aide en ligne, quand elle est disponible n’est plusvalable que pour des instructions et des noms d’objets exprimés en Anglais. Cette restriction ne vaut que pour les mots clés du langage, les noms, définis par le programmeur (types, variables, procédures et fonctions) peuvent être des chaînes de caractères quelconques ou presque.

Dans Excel 97 et 2000l’Anglais s’est imposé comme languede programmation

IV Enregistrement direct d’un macro

Excel permet de créer des macros (procédures sans paramètres) par enregistrement des actions de l’utilisateur. Cette façon de faire permet de “rejouer” un nombre indéterminé de fois une même séquence d’opérations réalisées une première fois manuellement. Vous pourrez aussi, par ce biais, avoir une idée d’une des façons possibles d’écrire en VBA ce que vous désirez faire, avant d’incorporer ce fragment dans une procédure que vous êtes en train d’écrire, quitte à modifier plus ou moins profondément le code généré pour qu’il s’adopte à vos besoins.

Chapitre 2 : DéclarationI Types et variations

Comme la plupart des langages de programmation VBA fait usage de variables pour vous permettre de ranger des résultats intermédiaires de calcul.

Une variable est un emplacement en mémoire auquel est attaché un nom.A la notion de variables est associée celle de type de données.Le type de données d’une variable décrit la structure des informations que cette variable peut contenir et les opérations qui lui sont applicables.Exemple : Dim Vartoto As Integer déclare que la variable varioto est de type Integer, c’est-à-dire qu’elle peut contenir un nombre entier compris entre - 32768 et + 32767, les opérations autorisées sur ce type de variables sont entre autres les 4 opérations arithmétiques.Le programme peut, grâce à l’instruction Type, définit lui-même de nouveaux types de données combinant plusieurs données dans une structure complexe. Des exemples seront donnés dans l’étude de ce genre de déclaration, après l’étude des types prédéfinis.A. Types prédéfinis

Il existe dans VBA plusieurs types de données prédéfnis. Ce sont : Boolean : une variable de type Boolean ne peut contenir qu’une des 2 variables True ou

False.Exemple : Dim Vartoto2 As Boolean. Integer : une variable de type Integer peut contenir une valeur entière comprise entre -

32768 et + 33767, soit - 2^15 et 2^15 - 1.Exemple : Dim Vartoto As Integer

On utilise ce type par exemple pour un calcul de boucle dans un compteur. Il nécessite 2 octets. Long : une variable de type Long peut contenir une valeur entière signée comprise entre

environ - 2 milliards et + 2 milliards, plus précisément entre - 2^31 et + 2^31 - 1.Exemple : Dim Vartoto3 As LongCe type permet de faire ce que les Integer ne peuvent pas faire, notamment compter plus loin que 30000 environ; par exemple, désigner un numéro de ligne dans une feuille EXCEL. Il nécessite 4 octets. Byte : une variable de type Byte peut contenir une valeur entière sans signe comprise

entre 0 et 255.Exemple : Dim Vartoto4 As ByteCe type peut servir à stocker les codes des caractères ANSI, Byte est codé sur un seul octet. Currency : une variable de type Currency peut contenir une valeur en virgule fixe, signée

avec au + 15 chiffres avant la virgule et 4 chiffres après.Ce type n’est pas sujet aux erreurs d’arrondi et permet de compter jusqu’à presque 100 milliards.Exemple : Dim Vartoto5 As Currency permet de faire des calculs en virgule fixe sans perte de précision, donc essentiellement des calculs financiers. Currency est stocké sous la forme d’une entrée, la virgule est implicite. Malheureusement, comme il n’y a que 4 chiffres après la virgule on ne peut pas s’en servir pour ranger le coefficient de conversion des € en Francs. Une variable de type Currency est stockée sur 12 octets. Single : une variable de type Single contient un nombre en virgule flottante et en simple

précision. Ce type de données permet d’aller d’environ - 3*10 à - 10^- 45 et d’environ 10^45 à 3*10^38 (pour les valeurs exactes, voir un manuel de référence) et permet aussi de représenter la valeur zéro, mais il est sujet aux erreurs d’arrondi, car stocké en binaire.

Exemple : Dim Vartoto6 As SingleUne variable de type Single n’a que 6 chiffres significatifs et des erreurs d’arrondi sont donc possibles sur les chiffres suivants, on s’en sert pour des % ou des statistiques simples. VBA stocke une variable Single sur 32 bits soit 4 octets.

Double : une variable de type Double contient un nombre en virgule flottante et en double précision. Ce type de données permet d’aller d’environ - 10^308 à - 4*10^- 324 et d’environ + 4*10^- 324 à + 10^308 et permet aussi de représenter la valeur zéro, mais il est sujet aux erreurs d’arrondi, car stocké en binaire.

Exemple : Dim Vartoto7 As DoubleUne variable de type Double est comme Single un nombre en virgule flottante, mais sa précision est de 15 chiffres justes. On s’en sert pour des calculs, qui nécessitent une précision relativement importante c’est-à-dire pour presque tous les autres calculs. Double est stocké sur 64 bits soit 8 octects.C’est sous cette forme que sont renvoyés la valeur numérique des cellules d’une feuille. Décimal est un nombre sur 12 octets où la virgule est semi-fixe car elle peut bouger.Décimal permet donc de pouvoir faire les conversions en €.Exemple : Dim Vartoto10 As Décimal Date : une variable de type Date est stockée sous forme d’une Double, soit sur 8 octets

mais :- sa partie entière représente une date compriseentre le 01/01/100 et le 31/12/9999.Note : les dates d’un tableau Excel commencent au 01/01/1900 (sic!) pour la valeur 0.La valeur 1 donne le 01/01/1900.- sa partie fractionnaire représente une heure comprise entre 0 et 23H59 min 59s, sous la forme d’une fraction de journée.

Les fonctions de conversion acceptent de créer une date à partir d’une chaîne de caractères dont la date en claire.Exemple : Dim Vartoto8 As Date String : une variable de type String est une chaîne d’au plus 2 milliards de caractères.

Chaque caractère est choisi dans le jeu UNICODE soit 16 bits.L’unicode comprend actuellement environ 50 000 caractères différents et permet de coder l’Indi, le Coréen, le Katakana, le Hiragana, tous les Kanjis Japonais les plus utilisés, ainsi que les différentes versions graphiques Pijin et autres des idéogrammes Chinois.Exemple : Dim Vartoto9 As String définit une chaîne de taille variable.

Objet : une variable de type Objet contient une référence (adresse) à un objet VBA quelconque (en particulier à un objet EXCEL).

Exemple : Dim Vartoto11 As ObjetLa variable Vartoto11 pourra contenir ou bien un objet de type Range, ou bien un objet de type Column, ou bien un objet de type Row, ou bien un objet de type CommandButton et ainsi de suite.Remarque : Des types prédéfinis existent pour tous les objets utilisés par EXCEL (par exemple : le type Range est prédéfini). On n’utilisera donc le type Objet que lorsqu’on est dans l’impossibilité de prévoir le type d’achat exact de l’objet qu’on désire stocker dans la variable.

Variant : une variable de type Variant peut contenir une valeur de n’importe lequel des types vus + haut, y compris les types Objets.

Exemple : Dim Vartoto12 As VariantExemple d’utilisation des types prédéfinis avec EXCEL :Les divers types ont chacun leur utilité. Le stockage d’un numéro de colonne, qui est compris entre 1 et 256, se contentera d’une variable de type Integer.Le stockage d’un numéro de lignes, qui peut être compris entre 1 et 65 536, nécessitera quant à lui une variable de type Long.Les données numériques des tableaux EXCEL sont des Doubles. Il est possible de les ranger dans des variables de type Single, mais on perd alors une partie de la précision.Le stockage de la valeur numérique d’un caractère (code ANSI, sur 8 bits), qui est comprise entre 0 et 255, se satisfera parfaitement d’une variable de type Byte.Enfin, il est logique de stocker une date dans une variable de type Date, et une chaîne de caractères dans une variable de type String.

Remarque : Le type de la valeur d’une date dans un tableau EXCEL est stockée comme un variant Date. La structure d’un tel variant date est celle d’un Double.Le numéro du jour est représenté par la partie entière, la partie fractionnaire représente l’heure sous forme d’une fraction de journée. Une seconde est donc = à 1/86 400.(86 400 = 60 secondes*60 minutes * 24 heures)B. Déclaration de types

Il est possible en VBA de définir ses propres types à partir des types préfinis ou d’autres types personnalisés.Pour ce faire, le programme utilise l’instruction Type.

1. Déclaration d’un type comportant plusieurs champs

La déclaration d’un type personnalisé comprend : sur la 1ère ligne, le mot-clé Type, suivi du nom du type à définir. sur chacune des lignes suivantes, une définition de champ : le nom du champ choisi par le programmeur le mot-clé As le nom du type de champ, qui peur être un type

prédéfini ou un type personnalisé déjà défini. sur une dernière ligne, les mots End Type.

Exemple :Type exempletype :Champ1 As IntegerChamp2 As DateChamp3 As VariantEnd TypeRemarque : Un champ peut être un tableau.

2. Déclaration d’un type tableau

Un tableau est une collection de données toutes de même type, distinguées les unes des autres par un indice entier.Exemple : Un tableau pourrait contenir le prénom des élèves.Données : Élodie, Stéphanie, Valérie, RaphaëlIndice : 1 , 2 , 3 , 4Dans ce cas, la déclaration du champ comporte devant le mot-clé As, les limites des valeurs du ou des indice(s) sous forme de couples dont les éléments sont séparés par le mot-clé To, les couples étant séparés par des virgules et le tout entre ().

Exemple 1 :Type listeprénomdonnées (1 To 4) As StringEnd Type

Exemple 2 :Si je souhaite faire un tableau à plusieurs dimensions, j’utilise les numéros de TD des élèves et l’ordre de classement au partiel.

1er (major) 2ème 3ème

2601 Raphaël Sophie Olivier

2602 Élodie Stéphane Sylvie

2603 William Valérie Brice

Type classement :Élèves (2601 To 2603, 1 To 3) As StringEnd Type

On peut aussi ne pas déclarer de limites < pour l’indice d’un tableau. Dans ce cas, seule la limite figure entre les (), le mot To ne figure pas. La limite < utilisée est alors 0 ou 1, comme indiqué par une directive Option Base 0 ou Option Base 1 située en tête du module.En l’absence de cette directive, la limite < par défaut des indices de tableau est 0.

Exemple 3 : sans l’instruction Option BaseType listeprénomdonnées (3) As String

End TypeIci, on a 4 chaînes (1er exemple de prénoms des élèves) qui sont indicées par défaut à partir de l’indice zéro (il n’y a pas d’instruction Option Base).données : Élodie, Stéphanie, Valérie, Raphaëlindice : 0 , 1 , 2 , 3

Exemple 4 : avec l’instruction Option Base 0Option Base 0Type listeprénomdonnées (4) As StringEnd TypeOn a ici 5 chaînes (1er exemple : prénoms des élèves) qui sont indicées par défaut à partir de l’indice zéro (en raison Option Base 0).données : Élodie, Stéphanie, Valérie, Raphaël, Amélieindice : 0 , 1 , 2 , 3 , 4

Exemple 5 : avec l’instruction Option Base 1Option Base 1élèves (2601 To 2603) As StringEnd TypeIci, je suis obligé de définir l’Option Base 1; car le ou la major de promo supporterait mal d’être le ou la ZERO!!Sinon, on met le + nul en tête car il a été dit dans le Écritures “les 1ers seront les derniers.”

Exemple 6 :Type Montypeamoinom As Stringprénom As Stringdatenais As Datepartdubutin As Currencynombre condamnation As Integercondamnation (1 To 100) As Datemotif (1 To 100) As StringEnd Type

Remarque 1: Les champs condamnation et motif sont des tableaux dont l’indice peut aller de 1 à 100. On peut donc enregistrer jusqu’à 100 condamnations dans une variable de type Montypeamoi.Remarque 2 : Les types doivent être déclarés en tête de module, avant la 1ère procédure ou fonction globale. Le mot-clé Type peut être précédé du mot Public ou Private. un type Private n’est utilisable que dans le module ^ù il est défini. un type Public est utilisable dans tous les modules du projet : c’est le mode par défaut.

Exemple 7 :Public Type Montypeamoi(suite identique à l’exemple 6)

Exemple 8 :Private Type Montypeamoi(suite identique à l’exemple 6)

Nota : en VBA, il existe 3 sortes de modules module standardContient :- des déclarations de type

- des déclarations de variables- des procédures et fonctions module de classe (qui sert à définir une nouvelle classe d’objets)Contient :- des déclarations de variables membres de cette classe- des procédures et fonctions- des accesseurs (procédures et fonctions spéciales s’appelant Let, Get et Set destinées à contrôler l’accès aux variables)

module de formulaire (qui sert à définir un formulaire avec des boutons, zone de saisi, etc...)

Contient :- la description du formulaire- des déclarations de variables- des procédures et fonctions appelées lors d’une action sur un des contrôles du formulaire.On voit qu’à part dans les modules standards, on ne peut pas déclarer de type Public réutilisable ailleurs.

III Déclarations de variables

La déclaration d’une variable ressemble à celle d’un champ dans une déclaration de type personnalisé. Elle est introduite par le mot-clé Dim. Ce mot-clé est suivi du nom de la variable, puis du mot-clé As et enfin du nom du type choisi par la variable.

Dim Numéro Condamnation As Integer

Dans une déclaration de variables, le mot-clé Dim peut être remplacé par l’un des mots-clés suivants :- Private- Public- Static

Private = la variable ainsi déclarée doit être globale à un module (déclarée en tête de module, avant toute déclaration de procédure ou fonction). Elle n’est alors accessible que depuis le module où elle est déclarée.

Exemple :Private var As String

Public = la variable ainsi déclarée est accessible dans tous les modules du projet. Elle doit être déclarée en tête du module, en dehors de toute procédure ou fonction.Public est la variable par défaut lorsqu’on utilise Dim.

Exemple :Public titi As StringPrivate qoqo As longSub test ()Dim toto As IntegerEnd SubLa variable titi est visible par tous les modules. Si au même endroit, je l’avais déclaré par Dim Titi As String rien n’aurait changé.La variable qoqo n’est visible que dans le module. Si elle avait été déclarée avec Dim (Dim qoqo As Long), elle aurait été visible dans tous les modules.La variable toto est visible dans la procédure test uniquement (Sub ou subroutine). Les mots Public ou Private sont interdits dans une telle déclaration.

Static = une variable ne peut être déclarée ainsi que si elle est locale à un procédure ou fonction. La variable ainsi déclarée n’est accessible que depuis la procédure ou fonction où elle a été déclarée, mais elle garde sa valeur entre 2 appels de ladite procédure ou fonction, alors qu’habituellement une variable définie dans une procédure ou fonction est réinitialisées au moment du lancement de la procédure ou fonction.

Exemple :Sub test ()Dim toto As IntegerStatic tutu As LongEnd Sub

Ici tutu pourra être utilisée comme un compteur du nombre de fois où la procédure test sera appelée.Tutu est une variable privée de la procédure, on ne peut y accéder que depuis cette procédure test.

Exemple récapitulatif :Module 1Public V1 As IntegerPrivate V2 As Boolean

Sub un-unDim V3 As StringStatic V4 As StringEnd Sub

Function un-deuxDim V5 As IntegerEnd Function

Module 2Public V8 As DatePrivate V6 As Object

Sub deux-unDim V7 As BooleanEnd Sub

V1 est accessible dans tous les modules 1 et 2.V2 est accessible dans les procédures un-un et un-deux du module 1.V3 est accessible seulement dans la procédure un-un.V4 est accessible seulement dans la procédure un-un et garde sa valeur entre 2 appels à cette procédure.V5 est accessible seulement dans la procédure un-deux.V6 est accessible dans toutes les procédures ou fonctions du module 2, mais pas depuis celles du module 1.V7 n’est accessible que depuis la procédure deux-un du module 2.V8 est accessible dans toutes les procédures des modules 1 et 2.

Remarque :La déclaration des variables n’est obligatoire en VBA que si, en tête de module, figure la directive.

Option Explicit :En l’absence de cette directive, il est possible de ne pas déclarer une ou plusieurs variables. Celle-ci ou celles-ci sont alors créées “à la volée” avec le type Variant lors de leur 1ère utilisation.Il est recommandé d’utiliser cette directive.

Option Explicitdans tous les modules dont la complication atteint un certain degré. En effet, l’obligation de déclarer les variables évite de nombreux ennuis liés à une erreur dans les noms de variable.

Exemple : Supposons que la directive Option Explicitne figure pas en tête d’un module et que dans une procédure de module, on utilise une variable nommée etat. Quelques lignes +loin, on l’orthographie état avec un accent aigu sur le e. On se trouve alors en présence de 2 variables distinctes alors qu’on pense n’en avoir qu’une. Les modifications de etat ne sont pas répercutées dans état et réciproquement. Ce genre d’erreurs n’est pas toujours facile à découvrir.

En présence de la directive Option Explicit et d’une déclaration de la variable etat, l’utilisation de la variable état provoquera une erreur de compilation qui apparaîtra immédiatement.

Exemple récapitulatif :Option ExplicitOption base1Private var As StringPublic titi As String

Sub test ()Dim toto As IntegerStatic tutu As DoubleDim tablo (5) As Bytetata = 2End SubOn note ici que l’on tente d’utiliser la variable tata qui n’a été déclarée nulle part.

Le compilateur VBA va générer un message d’erreur et refuser de terminer la compilation : “erreur de compilation variable non défini”Dim Tata As VariantTata = 2

a) Tableau

Pour déclarer une variable tableau, on doit ajouter les limites des valeurs du ou des indice(s).

Exemple :

Dim NbJCondamnation (1 To 100) As Integer

b) Instructions Option Base

Dans la déclaration d’un tableau, il est possible d’omettre la valeur minimum de l’indice et le mot-clé To. Dans ces conditions, la valeur minimum de l’indice est contrôler par une instruction.

Option Base0 ou Option Base1 doit être située en tête du module et fixe à 0 ou 1 la valeur minimum par défaut des indices de tableau dans le module.

Exemple 1 :

Option Base 0 Dim toto (100) As Integer

Toto sera un tableau constitué de 101 entiers, avec des indices allant de 0 à 100.

Exemple 2 :

Option Base 1 Dim titi (50) As String

Titi sera un tableau de 50 chaînes avec des indices allant de 1 à 50.

Remarque :

Si l’instruction Option Base n’apparaît pas dans un module, les indices minima sont par défaut la valeur 0.

c) Tableau à plusieurs indices

On utilise alors plusieurs couples mini To maxi. En effet, on n’est en aucun cas obligé de commencer à l’indice 0 ou 1, comme précisé par Option Base.

Exemple :Dim T1 (2 To 8, 6 To 31) As IntegerLe 1er indice va donc de 2 à 8, le second de 6 à 31.On remarque que les déclarations Option Base ne servent ici à rien.

Option Base 0 Dim T2 (40, 4 To 6) As Integer

Il est possible de panacher les indices avec une valeur minimum par défaut et ceux avec une valeur minimum explicite.Ici, le 1er indice de T2 va de 0 à 40 (à cause de Option Base 0) et le second de 4 à 6.

B. Accès à un élément d’une variable non élémentaire1. Accès à un élément d’un tableau

Pour accéder à un élément d’une variable de type tableau, on fait suivre le nom de la variable par le ou les indice(s) de l’élément, séparé par des virgules et encadrés par des parenthèses.Exemple :Si T1 est un tableau d’entiers à un seul indice, déclaré par Dim T1 (1 To 21) As IntegerOn accèdera au 18ème élément de ce tableau T1 par la notation : T1 (18)Si T2 est un tableau à 2 indices déclarés par : Dim T2 (1 To 10, 1 To 16) As IntegerOn accèdera à la case L8, C15 (8ème ligne, 15ème colonne) de ce tableau T2 par la notation : T2 (8,15)

NB : Dans un tableau Excel ou VBA, le 1er indice est la ligne, et le second la colonne.

La valeur utilisée comme indice peut être une expression entière quelconque, faisant intervenir des opérateurs, des fonctions...

Exemple : Considérons le tableau T3 défini par :Dim T3 (5 To 20) As String(ce tableau contient 16 éléments)Alors la notation T3 (3*i+2) permettra d’accéder aux éléments suivants, selon la valeur i.

T3 élément i 3i+2 élément obtenu

5 Marcel 1er < 1 < 5 aucun = erreur

6 Jean 1 5 Marcel7 Pierre 2 8 Paul8 Paul 4ème 3 11 Marie9 Jacques 4 14 Alexandre10 Lucie 5 17 Hervé11 Marie 7ème 6 20 Isabelle

12 Marion 6 20 aucun = erreur

13 Mathilde14 Alexandre 10ème15 Henry16 John17 Hervé 13ème

18 Adrien19 Grégory20 Isabelle 16ème

Toute tentative d’accès à un élément de tableau en utilisant un indice trop petit ou trop grand provoque une Runtime error 9 (Subscript out of range), ou encore en français, Erreur d’Exécution 9 (L’indice n’apparaît pas à la sélection.)

2. Accès aux éléments d’une variable d’un type comportant plusieurs champs

On accède aux éléments d’une variable dont le type comporte plusieurs champs en préférant le nom du champ par le nom de la variable suivi d’un point.

Rappel :Public Type Montypeamoinom As Stringprénom As StringDatenais As DatePartdubutin As Currencynbcondamnation As IntegerCondamnation (1 To 100) As DateMotif (1 To 100) As StringEnd Type

Exemple :Dim Avis de recherche As Montypeamoi

On accèdera au champ nbcondamnation de la variable Avis de recherche par la notation :Avisderecherche . nbcondamnationOn peut combiner les 2 notations pour accéder à un élément d’un champ qui est un tableau.Exemple :Avisderecherche.condamnation (18)permet d’accéder à la 18ème colonne de l’individu recherché.

Avisderecherche.motif (46)permet d’accéder au 46ème motif de condamnation de la racaille recherchée.

Comme on l’a signalé plus haut, l’indice peut être calculé.

Exemple : Avisderecherche.condamnation (2j+5)permettra d’accéder à la condamnation dont l’indice s’obtient en multipliant par 2 la valeur j puis en ajoutant 5 au produit obtenu, et ceci pour l’individu dont le casier judiciaire est rangé dans la variable Avisderecherche.

Avisderecherche.motif (Avisdercherche.condamnation)permettra d’obtenir le motif de la dernière condamnation du criminel.En effet, nbcondamnation est le nombre total de condamnations enregistrées au casier judiciaire de l’individu.Puisque les indices des tableaux, motifs et condamnations commencent à 1, l’individu du dernier élément utile de chacun de ces 2 tableaux est nbcondamnation.

Avis de recherche :

Nom Pépé lePrénom KokoDatedenais 1984Part du butin 24 millions €Nbcondamnation 95Motif

Raquette Baston Vol MeurtreMultiples assassinats flics + hold up

1 2 18 46 95

Condamnation

Raquette Baston Vol MeurtreAssassinats flics + hold up

19 1994 1998 2001 2004

Avisderecherche.nbcondamnation = 95Avisderecherche.condamnation (18) = VolAvisderecherche.motif (46) = meurtreAvisderecherche.motif (Avisderecherche.nbcondamnation) = avisderecherche.motif (95) = multiples assassinats + hold up

Exemple :Avisderecherche : nbcondamnation = 3motif : Indice Valeur1 Vol2 Agression3 RaquetteAvisderecherche.mtof (Avisderecherche.nbcondamnation) est équivalent dans ce cas à Avisderecherche.motif (3) qui vaut “Raquette”.

3. Accès aux propriétés et méthode d’un objet

Un type Objet est un type comportant : plusieurs champs (les membres d’un objet) certains de ces champs ont des procédures ou des fonctions (méthodes de l’objet).

Remarque :Quand il s’agit d’objets, on remplace habituellement le mot type par le mot classe.

En VBA, une classe d’objet est : soit prédéfinie, par exemple CommandButton, ou Range soit conçue par le développeur et décrite dans un module de classe.

On obtient un module de classe par le menu contextuel du VBA projet (obtenu par un clic droit), on sélectionne ensuite insérer puis module classe.

Dans un module de classe, on ne peut définir qu’une classe car le nom de la classe est le nom du module.Par exemple : On ajoute un module de classe au projet courant. On modifie la propriété Name de ce module pour que le module s’appelle désormais

Tartempion à la place de la valeur par défaut Module 1.

Désormais, on peut déclarer des variables de ce type : Dim Varz As Tartempion- Les champs qui sont des procédures ou des fonctions portent le nom générique de méthode de l’objet.- Les autres champs sont les propriétés de l’objet (Nous verrons + loin que certaines propriétés peuvent en réalité être définies par des procédures et fonctions particulières Property Let, Property Set, Property Get, mais elles apparaissent au monde extérieur comme étant des champs ordinaires.)

SYNTHESE

Module = un paquet de codes VBAModule de classe = un paquet de codes VBA qui définit une classe d’objetsLa classe est aux objets ce que le type est aux variables ordinaires.

Rappel (à savoir pour partiel) : Définition d’un type pour les variablesle type de données d’une variable décrit : la structure des informations que cette variable peut contenir les opérations qui lui sont applicables

Donc (idem) :une classe d’objets décrit : la structure des informations qu’un objet de cette classe peut contenir (propriétés) les méthodes qui lui sont applicables

Méthode de classe = opérations applicables aux objets d’une classe Conclusion : une classe d’objets est un canevas de construction d’objets. Objet = instanciation d’une classe instancier = faire une instance instancier une classe = construire une constante structurée d’informations appelée objet,

à partir d’un canevas constitué par la définition de la classe.

Exemple de création et d’utilisation d’objet :Nous allons créer un module de classe appelé classe - élève.

Rappel : Le nom d’un module de classe est le nom de la classe. Ce module de classe définit donc une classe nommée également classe - élève.

(On fixe le nom du module en modifiant la propriété name dans la fenêtre de propriétés de l’éditeur Visual Basic.)

Nous définissons d’abord les membres (variables de la classe) comme des variables globales au module grâce à Public ou Private.

Un membre de classe déclaré Public est équivalent à un champ d’un type comportant plusieurs champs (types que nous appèlerons désormais types composés.)Un objet qui résulte de l’instanciation de la classe aura pour membres, ceux déclarés dans la classe. On peut donc accéder aux membres correspondant de l’objet (instance de la classe) par la notation :

Nom _ d’objet _ nom _ de _ propriété, qui est le pendant la notation utilisée pour les types composés :nom _ de _ variable. nom _ de _ champ

Un membre de classe déclaré Private, comme ci-dessous n’est accessible que par l’intermédiaire de méthodes, que ce soit pour le modifier ou pour le consulter.Private member _ nom As String

Nous définirons ensuite toutes les méthodes de notre classe classe - élève.

Tout d’abord, un accesseur qui sera la seule possibilité pour affecter une valeur au membre, membre _ nom (unique variable de notre classe, classe - élève) car ce membre a été déclaré Private, il nous faut donc une méthode pour lui affecter une valeur. Un accesseur fait apparaître un membre privé d’un objet, instance d’une classe, comme s’il s’agissait d’une simple propriété, mais il peut réaliser au passage des vérifications de cohérence.

Un tel accesseur d’affectation de valeur se déclare comme une procédure, et sa déclaration est introduite par les mots Property Let. Il a pour nom le nom sous lequel on veut qu’apparaisse, pour le monde extérieur, la propriété, ici nom.

Property Let Nom (nom _ temps As String)membre _ nom = nom _ tempsEnd Type

On utilisera cet accesseur de la manière suivante :élève. nom = “classe”où élève est un objet résultant de l’instanciation de la classe, classe - élève. On voit que grâce à l’accesseur, nous avons retrouvé la possibilité d’écrire :nom _ objet. nom _ de _ propriété = valeur(ici, la valeur ‘classe” est un centre de chaîne)

Nous allons également créer une méthode permettant à un objet de classe, classe - élève d’affecter son propre nomSub affiche _ nom ()Msg Box member _ nomEnd SubLa subroutine affiche _ nom va affecter une boîte de dialogue (Msg Box) avec pour seul texte le contenu du membre, membre _ nom.

Dans un module standard nous allons utiliser la classe, classe - élève défini ci-dessus.

Tout d’abord nous allons définir un objet élève instance de la classe, classe - élève.Dim élève As Nom Classe - élève

Puis nous allons utiliser cet objet élève en affectant une valeur à sa propriété nom.Sub essai1 ()élève. nom = “Claire”

Nous allons enfin demander à l’objet élève d’afficher lui-même le nom :élève. affiche _ nomEnd Sub

Récapitulation de l’exemple :

1. Dans le module de classe nommée classe-élève :définition de la classe, classe-élèvePrivate membre-nom As StringProperty Let nom (nom _ temps As String)membre _ nom = nom _ tempsEnd Property

Sub affiche. nom ()MsgBox membre _ nomEnd Sub

2. Dans un module standard, utilisation de cette classeDim élève As New Classe - élèveSub essai1 ()élève. nom = “Claire”élève. affiche. nomEnd Sub

Property Let nom = propriété d’action sur le nom

Conclusion : accès aux propriétés d’un objetOn accède à ces propriétés par la notation générale d’accès à des éléments d’une variable comportant plusieurs champs.

Exemple : Active Sheet. Cells (3,4)est l’appel de la propriété Cells de l’objet ActiveSheet.Nota :ActiveSheet qui peut se traduire par “feuille de calcul active” est une variable globale prédéfinie dans le VBA d’EXCEL. Cette variable contient une référence (adresse) à un objet de classe Sheet c’est-à-dire une feuille de calcul.Cells est une propriété de cet objet de classe Sheet.Cette propriété est l’ensemble des cellules de la feuille.On peut utiliser cette collection (ensemble de cellules) comme un tableau à 2 dimensions d’où les () et les indices.Les nombres 3 et 4 placés entre () après le nom de la méthode sont les indices dans le tableau des cellules.En réalité, la propriété Cells des objets de classe Sheet est réalisée grâce à une function Property Get qui accepte 2 arguments facultatifs. Le 1er argument indique la ligne, le 2nd la colonne où se trouve la cellule.)Lorsque 2 arguments lui sont fournis, la Function Property Get renvoie un objet de classe Range constitué d’une seule cellule.Lorsque aucun argument ne lui est fourni, la Function Property Get renvoie un objet de classe Range qui contient toutes les cellules de la feuille.

Exemple :Active Sheet _ Borders. Line Style = x1 Medium

Nota : une function Property Get sert à récupérer sous forme d’une propriété, une partie des caractéristiques d’un objet. C’est le pendant des procédures Property Let, qui servent, elles, à faire apparaître, sous forme d’une affectation à une propriété, la modification d’une partie des caractéristiques d’un objet.Nous étudierons ces questions en détail dans la partie de cours relative à la définition des classes.

C. Principales classes d’objets prédéfinis dans Excel

La plupart des classes d’objets prédéfinis par Excel constituent une hiérarchie. Au sommet de cette hiérarchie se trouve la classe Application.La plupart de ces objets prédéfinis possèdent au moins un objet conteneur parmi leurs propriétés.L’objet conteneur fondamental de la classe Application est la propriété Workbooks de cette classe.Le conteneur Workbooks contient tous les classeurs ouverts par l’application Excel. Chacun des classeurs est représenté par un objet de classe Workbooks.

Représentation visuelle :ApplicationWorkbooks (Workbook) Worksheets (Worksheet) Charts (Chart)On constate que l’objet Application contient parmi ses différentes propriétés l’objet Workbooks (qui contient un ensemble d’objets Workbooks).De même, chaque objet Workbook contient une propriété Worksheets (qui contient un ensemble d’objets Worksheet et une propriété Charts (qui contient un ensemble d’objets Chart).De telles propriétés représentant des ensembles portent le nom générique de conteneurs. Ici, nous venons de voir que les objets Workbooks, Worksheets et Charts sont des objets conteneurs.Les feuilles de calcul sont représentées par des objets de classe Worksheet.L’accès à un objet contenu dans un conteneur se fait grâce à un plusieurs arguments qu’on passe au conteneur, identifiant l’objet contenu visé.Par conséquent, la feuille Feuil2 du classeur MonClasseur courant ouvert s’appellera :Application Workbooks (“MonClasseur. xls”). Woksheets (“Feuil2”).

Par cet appel :- j’appelle l’objet Application- puis je lui demande sa propriété Workbooks qui est un objet conteneur- ensuite je demande au conteneur Wokbooks de fournir parmi les objets qu’il contient, celui qui s’appelle “MonClasseur. xls”- après avoir obtenu le Wokbooks “MonClasseur. xls” je demande à celui-ci sa propriété Worksheets qui est un objet conteneur.- enfin je demande à cette collection (mot synonyme de “objet conteneur”) l’objet qui a pour nom “Feuil2”.

Sub toto ()Dim s As WorksheetSet s = Application. Workbooks (“MonClasseur. xls”). Workbooks (“MonClasseur. xls”). Woksheets (“Feul2”)End Sub

La séquence de caractères espace suivi par espace souligné signale que l’instruction se continue sur + d’une ligne.

Je définis la procédure tot, puis la variable s de type Worksheet, enfin, grâce à Set, j’affecte à s la Feul2.Finalement... tout ça pour récupérer une banale feuille de calcul§ que j’aurai pu avoir en ouvrant Excel tout simplement!

Mais, dans la suite de la procédure toto, nous pourrions automatiser grâce à VBA, un traitement portant sur lz contenu de MonClasseur. Feuil2*Un objet de classe Worksheet : contient un objet Cells qui est un contenu d’objets Range, qui représentent chacune des

cellules de la feuille de calcul. par ailleurs, on peut créer un ou plusieurs objets de classe Range qui contiennent des

sous ensembles de l’objet Cells. Un objet Worksheet possède une méthode appelée Range, qui prend comme argument les coordonnées du sous ensemble de cellules à sélectionner.

Par exemple, le nom complet du bloc B3:G9 de la feuille Feuil1 sera :ApplicationWorkbooks (“MonClasseur. xls”). Worksheets (“Feuil1”). Range (“B3:G9”)

En exécutant en VBA cela donne :Sub toto ()Dim r As RangeSet r = Application. Workbooks (“MonClasseur. xls”). Worksheets (“Feuil1”). Range (“B3:G9”)End Sub

Il est également possible d’identifier les objets contenus dans un conteneur par un numéro. On pourra donc ainsi utiliser des notations telles que :Application Workbooks (1). Worksheets (3). Range (“B3:G9”)Cette notation permettra d’accéder à la sélection de cellule B3:G9 de la 3ème feuille de calcul (Worksheets(3)) du 1er classeur (“Worbooks(1)).Ceci donne à l’exécution :Sub toto ()Dim r As RangeSet r = Application. Workbooks (1). Worksheets (3). Range (“B3:G9”)End Sub

On peut remplacer Range (“B3:G9”) par Range (argument 1; argument 2)argument 1 contient une référence à la cellule B3argument 2 contient une référence à la cellule G9

Pour cela, il va falloir définir une variable temporaire pour faciliter la référence à la feuille de calcul.Workbooks (1). Worksheets (3)Le programme devient :Sub toto ()Dim r As RangeDim w As WorksheetSet w = Application. Workbooks (1). Worksheets (3)Set r = w. Range (w. Cells (2,2). w. Cells (9,7)End Sub

Rappel : B3 est en 3ème ligne, 2ème colonne d’où Cell (3,2) G9 est en 9ème ligne, 7ème colonne d’où Cell (9,7)

Voici la capture d’écran montrant le programme en train de tourner, et montrant que la valeur désirée des variables a bien été calculé.

En effet, au niveau de l’espion r. address nous obtenons “$B$3:$G$9” qui est l’adresse absolue de Range désiré, B3:G9.Bien entendu, nous n’utilisons ce genre d’adressage que lorsque nous avons besoin de faire calculer les adresses d’un bloc.

Nota : Nous aurions pu écrire + simplement :Set r = Range. (w. Cells (3,2). w. Cells (9,7))

D. Variables prédéfinies simplifiant la navigation

Une référence au classeur Excel qui contient la feuille dans laquelle se trouve actuellement le curseur de cellule est rangée par Excel dans une variable prédéfinie, nommée ActiveWorkbook.Une référence au classeur contient le code VBA en cours d’exécution est rangée dans une variable prédéfinie ThisWorkbook.Une référence à la feuille Excel dans laquelle se trouve actuellement le curseur de cellule est rangée par Excel dans la variable prédéfinie ActiveSheet.Classeur actif ActiveWorkbookFeuille ActiveSheetProgramme VBA en cours ThisWorkbook

Enfin, si aucun objet n’est précisé lors de l’utilisation de Range ou de Cells, VBA sous entend automatiquement ActiveSheet.Par conséquent, pour désigner le bloc d’une cellule située en ligne 6, colonne 7 (c’est-à-dire G6) de la feuille courante, on pourra se contenter d’écrire : Cells (6,7)Au lieu d’écrire in extenso :ActiveSheet. Cells (6,7)On remarque que nous adressons ici en VBA un Range d’une seule cellule.Comme en Excel habituel, quand nous cliquons sur la feuille active pour adresser la cellule G6.Un objet de classe Range, comme celui défini auparavant Cells (6,7) possède à son tour différentes propriétés.Les + importantes sont : Value qui désigne la valeur contenue ou calculée de la cellule. Le type VBA, sous lequel

cette propriété est attendue (pour écrire dans la cellule) ou renvoyée (pour lire la cellule) est :

- Double pour les nombres- String pour les textes- Boolean, par exemple, pour les résultats de comparaisons tels que celui renvoyé par la formule : =(a1=B1)- Date pour les dates

Formula qui désigne la formule contenue dans la cellule. Cette propriété est de type String, c’est le texte de la formule tel qu’on l’introduit dans la zone de saisie de la barre de formules d’Excel.

Format qui désigne l’ensemble des caractéristiques de formatage de la cellule ou du bloc, par exemple, l’alignement horizontal ou vertical, le nom de la fonte, la graisse (gras ou maigre). Cette propriété est un objet.

Borders qui est un conteneur pour les objets de classe Border précisant les caractéristiques des côtés (bordure) de la cellule ou du bloc.

Remarque : les propriétés Value et Formula d’un Range contenant plusieurs cellules sont des tableaux.

Exemple d’utilisation du conteneur Range et du conteneur Cells.

Pour donner une valeur à toutes les cellules d’un bloc connu à l’avance, on utilisera l’objet conteneur Range avec pour argument une constante de type String. Range (“B8:D17”). Value = 147 donnera la valeur 147 à toutes les cellules du bloc

B8:D17.Ici, on voit que l’argument “B8:D17” passé à Range est une constante de type String.

Voici le programme et son résultat :Sub tot ()Range (“B8:D17”). Value = 147End Sub

Si on s’intéresse à une cellule dont le numéro de ligne et le numéro de colonne ont été calculés et se trouvent donc dans des variables, on utilisera le conteneur Cells.Dim numligne As LongDim numcolonne As Integer

Cells (numligne, numcolonne).Value = 18donnera la valeur 18 à la cellule dont le numéro de ligne se trouve dans numligne et le numéro de colonne dans numcolonne.

Voici le programme correspondant :Sub toto ()Dim numligne As LongDim numcolonne As Integernumligne = 5numcolonne = 2Cells (numligne, numcolonne).Value = 18End Sub

Dans cet exemple, le “calcul” de numligne et numcolonne consiste à leur affecter les valeurs 5 et 2 (ligne 5, colonne B).Enfin, si on s’intéresse à un bloc dont on connaît les numéros des premières et dernières lignes, et premières et dernières colonnes, on devra fournir comme argument au conteneur Range 2 objets de classe Range, fournis par le conteneur Cells.En effet, il n’existe pas de classe d’objet Cellule, c’est pourquoi il faut pour désigner une cellule, utiliser un objet Range qui ne contient que cette cellule.

Rem pl = première ligneRem dl = dernière ligneDim pl As Long, dl As LongRem pc = première colonne

Rem dc = dernière colonneDim pc As Integer, dc As IntegerRem csg = coin supérieur gaucheRem cid = coin inférieur droitDim csg As Range, cid As Range

Set csg = cells (pl, pc)Set cid = cells (dl, dc)Range (csg, cid).Value = 666Cett séquence d’instructions donnera la valeur 666 (le chiffre de l’abbé bête) à toutes les cellules du bloc délimité par les lignes dont les numéros se trouvent dans pl et dl et les colonnes dont les numéros se trouvent dans pc et dc.

Remarque : csg et cid étant des objets, nous verrons + tard qu’il faut impérativement utiliser l’instruction Set pour leur affecter des valeurs.

Voici le programme à l’exécution :Sub toto ()Rem pl = première ligneDim pl As LongRem dl = dernière ligneDim dl As LongRem pc = première colonneDim pc As IntegerRem dc = dernière colonneDim dc As IntegerRem csg = coin supérieur gaucheDim csg As RangeRem cid = coin supérieur droitDim cid As Rangepc = 1dc = 3pl = 5dl = 7Set csg = Cells (pl, pc)Set cid = Cells (dl, dc)Range (csg, cid).Value = 666End SubSur le transparent suivant, nous découvrons le résultat de l’exécution.

A B C

1

2

3

4

5 666 666 666

6 666 666 666

7 666 666 666

La lecture de la propriété Value d’une cellule peut également être utile. On rencontrera souvent dans les TD des instructions If ayant l’allure suivante :

Dim nl As Long

If IsEmpty (Cells (nl,3).Value) Then

End If

La fonction IsEmpty fournit un booléen qui vaut True (vraie) lorsque l’argument de la fonction est la valeur spéciale Empty.Empty est la valeur de l’attribut Value d’une cellule vide. Si la cellule (nl, nc) contient un texte, sa propriété Value est un String qui contient ce texte. Si la cellule (nl, nc) contient un nombre, sa propriété Value est un Double qui convient ce nombre. Si la cellule (nl, nc) est vide de sa propriété Value est Empty (la valeur Empty est en fait un objet Empty, unique représentant de la classe Empty).Les instructions situées entre Then et End If s’effectueront donc si la cellule située en ligne nl colonne 3 est vide.On pourra s’intéresser aussi aux propriétés de formatage d’une cellule ou d’un bloc.

Exemple 1 : pour mettre en caractère gras le contenu d’une cellule, on écrira :Cells (nl, nc).Font.Bold = True

Exemple 2 : pour donner un format Date à une cellule on écrira :Cells (nl, nc).NumberFormat = “dmmmyyyy”On verra également dans les TD la modification des bordures d’une cellule ou d’un bloc, qui se fait en utilisant les objets Border fournis par le conteneur Borders de l’objet Range.

Voici un petit programme récapitulatif :Sub toto ()Dim nl As LongDim nc As Integernl = 2nc = 4If IsEmpty (Cells (nl, 3) Value) ThenCells (nl, nc) = 333*nlEnd if

On met en caractères gras :Cells (nl, nc).Font.Bold = True

On met en Format Date :Cells (nl, nc).NumberFormat = “d mmm yyyy”End Sub

A B C D1

2 27 oct 1901

3

Ceci montre que le (333*2) = 666ème jour du calendrier selon Saint Billou est le 27 octobre 1901.

D. Déclarations des procédures et fonctions1. Procédures

La déclaration d’une procédure se compose : du mot-clé Sub du nom de la procédure, choisi librement par le programmeur en dehors de la liste des

mots réservés du langage, ainsi que des noms de procédures ou de fonctions, objets, variables prédéfinies.

de la liste des paramètres formels de la procédure, séparés par des virgules. La liste elle-même doit être mise entre parenthèses.

Le corps de la procédure suit sa déclaration. Il est terminé par une ligne End Sub.

Exemples de déclarations de procédures : Procédure sans paramètre :Sub toto () Procédure avec paramètre :Sub titi (nl As Long, nc As Integer)

Chacun des paramètres formels (ici nl et nc) se comporte comme une variable locale à la prodécure.PASSAGE DE PARAMETRESVocabulaireL’appelant passe un argument.L’appelé reçoit un paramètre.

Les paramètres peuvent être passés. par référence grâce au mot-cl” ByRef (c’est la technique par défaut). le programme appelant passe à la subroutine l’adresse de son argument la subroutine reçoit l’adresse de l’argument passé.

Ainsi le programme appelant et subroutine peuvent modifier l’un et l’autre la zone de la variable concernée (qu’elle soit appelée par l’appelant Argument ou paramètre par la Subroutine.) Toute modification du paramètre par la Subroutine est donc répercutée à la variable d’origine dans l’appelant.

N.B : Il faut alors que l’argument soit une variable car on ne pourrait écrire dans une formule.

par valeur grâce au mo-clé ByVal l’appelant passe alors à la Subroutine une copie de l’argument la Subroutine reçoit cette copie dans son paramètre

La Subroutine peut donc modifier dans son paramètre la valeur de l’argument. De +, c’est la seule technique possible pour passer en argument une expression (qui par définition n’a pris d’adresse).Chaque paramètre formel doit être suivi par son type (ou sa classe s’il s’agit d’un objet). Il en est séparé par le mot-clé As.

Exemple :Sub recopie (source As String, dest As String)“Recopie” est une procédure qui accepte 2 paramètres, “source” et “dest“, tous 2 de type String, tous 2 passés par référence. Toute modification dans la procédure “recopie”d’1 des 2 paramètres se répercutera dans l’appelant.Le nom d’un paramètre peut également être précédé du mot-clé Optional qui comme le nom l’indique, précise que le paramètre est optionnel, ou plutôt qu’il n’est pas obligatoire de fournir, lors de l’appel, un argument correspondant.Il est possible de préciser pour un paramètre déclaré Optional, une valeur par défaut qui se place après le nom du paramètre et en est séparé par le caractère =.

Exemple de déclaration d’une procédure avec un paramètre optionnel :Sub Aracecercle (byval x As Double, byval y As Double, Optional byval r = 1 As Double)est la déclaration d’une procédure destinée à tracer un cercle.Tous les paramètres sont passés par valeur.Les 2 premiers paramètres, x et y sont les coordonnées du cercle.Le 3ème paramètre r, est le rayon du cercle.La fourniture d’un argument pour le paramètre r est facultative. Si aucune valeur n’est fournie pour ce rayon, c’est la valeur 1 qui sera utilisée.

Exemples d’appels de la procédure tracecercle :tracecercle 2,3tracera un cercle dont le centre a pour coordonnées x = 2 et y = 3 et dont le rayon r vaut 1 (valeur par défaut).

Remarque : la syntaxe de l’appel d’une procédure ne comporte pas de parenthèses. Le but est de faire le + possible ressembler l’appel d’une procédure à une instruction. Enfin, il est possible de terminer la liste des paramètres par un tableau précédé du mot-clé ParamArray.C’est un tableau de Variants (pas de mot-clé As ni de type déclaré), qui est déclaré avec (), sans indications des valeurs minimales et maximales d’indices, on utilisera les 2 fonctions prédéfinies Lbound et Ubound.Le tableau introduit par ParamArray étant par nature Optional, ce mot-clé Optional ne doit pas figurer dans sa description.Puisque les éléments d’un tableau introduit par ParamArray sont des Variants, le type des arguments correspondants peut être quelconque.

Exemple d’utilisation de ParramArray :Dans cet exemple, on voit que trucs () est un tableau de variants, car ni le mot-clé As, ni aucune indication de type ne figurent après les parenthèses qui suivent le nom “trucs” du tableau. Les limites des indices de ce tableau ne sont pas précisées car il n’y rien entre les parenthèses.Sub essai1 (titre As String, ParamArray trucs ())Dim i As Integer

Dim s As Strings = “ “For i = LBound (trucs) To UBound (trucs)s = s “ “ a trucs (i)NextMsgBox s, vbokOnly, titreEnd Sub

Sub essai2essai1 “Test”,”Stéphanie”,”Virginie”,”Christophe”End Sub

La procédure essai2 appelle la procédure essai1 en lui passant comme arguments :- “Test” qui sera mis en correspondance avec le paramètre titre- ensuite 3 arguments “Stéphanie”,”Virginie”,”Christophe” qui seront tous 3 absorbés par le tableau trucs () déclaré comme ParamArray.Grâce à la boucle For Next, on va concaténer dans la variable s tout ce qui est contenu dans le tableau trucs () en les séparant par des espaces.On affiche ensuite s et titre grâce à MsgBox. Le contenu de titre se retrouve dans la barre de titre de la fenêtre, le contenu de s dans la boîte elle-même.Nota : vbOkOnly est un dé indiquant qu’on ne désire qu”un seul bouton OK dans la boîte de msg.

Voici ce qu’on obtient :

TEST

Stéphanie, Virginie, ChristopheOK

2. Fonctions

La déclaration d’une fonction comporte les mêmes éléments que celle d’une procédure.Rappel de la syntaxe d’une déclaration de procédure :sub toto (liste de paramètres)

Il y a 2 différences : Une déclaration de fonction commence par le mot Function au lieu de Sub Une fonction renvoie une valeur dont il faut définir le type dans la déclaration

A cet effet, il suffit de considérer la déclaration de fonction comme une variable un peu spéciale et d’ajouter As le type.

Syntaxe :Function titi (liste de paramètres) As Type

Pour renvoyer une valeur, il suffit de l’affecter à une pseudo variable ayant pour nom celui de la fonction.

Exemple 1 :Soit la fonction Incremente, elle reçoit un entier et elle renvoie l’entier + 1.Function Incremente (i As Integer) As IntegerIncremente = i + 1End Function

La fonction Incremente augmente de 1 l’entier reçu en argument.La procédure test_fonction appelle la fonction Incremente en lui donnant la valeur de départ à 1.

Voici l’évolution des valeurs des variables :

K Incremente i ce qui est effectué

sous programme

0 _ _ dim k as integer test_fonction

0 _ 1 incremente (1) incremente

0 _ 1 i + 1 incremente

0 2 1 incremente = i + 1 incremente

2 _ _k = incremente (1)

test_fonction

_ = inexistant ou non affecté

Exemple 2 :

Function max ( _ Byval x As Integer, _ Byval y As Integer, _ ) As Integer If n > y Then max = x Else max = y End Function

Cette fonction fournit la valeur du + grand des 2 Integer. Les paramètres étant passés par la valeur, les arguments correspondants peuvent être des expressions.

Voici ce qu’on obtient à l’exécution :Function max (Byval n As Integer, Byval y As Integer) As IntegerIf n > y Then max = x Else max = yEnd Function

Sub test _ max ()Dim mx As Integermx = max (12, 48)End Sub

Exemple 3 :Problème : on veut faire dire à VBA combien de feuilles de calcul sont créées par Excel lors de la création d’un nouveau classeur.Pour cela, on va définir une fonction sans paramètre NouveauClasseur () qui renverra un objet Workbook tout neuf, qui contiendra donc le nombre de feuilles recherchées. L’entête de cette fonction sera Function NouveauClasseur () As Workbook.Le corps de la fonction sera constitué de l’unique instruction :Set NouveauClasseur = Application.Workbooks.Add

Rappel : Application.Workbooks est un conteneur d’objets de classe Workbook (aussi appelé

collection d’objets de classe Workbook) dans lequel on peut ajouter ou supprimer des objets de classe Workbook.

Parmi les méthodes de la collection Workbooks, il existe une fonction add qui permet d’ajouter un Workbook à la collection et qui rend le nouveau Workbook.

N’oublions pas la :End FunctionCe nouvel objet Workbook renvoyé par la fonction NouveauClasseur va être stocké par l’appelant de cette fonction dans une variable wb, qui aura elle-même été déclarée de type Workbook.Set wb = NouveauClasseur ()Ensuite on va récupérer le nombre de feuilles de calcul présentes dans le classeur au travers de la propriété Count de l’objet collection de feuilles de calcul (Worksheets) qui fait lui-même parti des propriétés de notre nouvel objet Workbook.nbsheets = wb.Worksheets.CountPuis on affiche :MgBox “un nouveau classeur a”&nbsheets&”feuilles par défaut”vbInformation + vbOKOnly,”Nouveau Classeur créé”

vbInformation est une constante qui provoque l’affichage du i à l’intérieur de la bulle. Nous avons déjà vu que vbOkOnly provoque l’affichage du bouton OK. La constante de chaîne Nouveau classeur créé, passée comme 3ème argument à MsgBox, s’affiche dans la barre de titre.

Voici la boîte de message :

Nouveau Classeur créé

Un nouveau classeur a 3 feuilles par défautOK

Enfin, il faut détruire les objets dont on n’a + besoin, sinon ils encombrent la mémoire, et les logiciels ont comportement erratique.

‘ fermeture du classeurwb. Close‘ destruction de l’objetSet wb = Nothing

Function NouveauClasseur () As WorkbookSet NouveauClasseur = Application.Workbooks.AddEnd Function

Sub test_workbook ()Dim wb As WorkbookDim nbsheets As Interger‘ création d’un nouveau classeurSet wb = NouveauClasseur ()‘ le nombre de feuilles dans le classeurnbsheets = wb. Worksheets. CountMsgBox “Un nouveau classeur a” & nbsheets & “feuilles par défaut “, vbInformation + vbOkOnly, “Nouveau classeur cré锑fermeture de classeurwb. Close‘destruction de l’objetSet vb = NothingEnd Sub

Mais on peut faire + simple, comme nous allons le voir en page suivante :Parmi les propriétés de l’objet Application figure une Integer nommé SheetsInNewWorkbook. Cette propriété fournit précisément le nombre de feuilles par défaut d’un nouveau classeur.Créons donc une fonction Nbf (Nombre de Feuilles)Function Nbf () As IntegerNbf = Application. SheetsInNewWorkbookEnd Functionet utilisons la dans une procédure Essai simplifiée :

Sub essai ()MsgBox “il y a “ + CStr (Nbf) + “feuilles dans un nouveau classeur “, vbInformation + vbOkOnly, “c’est + simple ainsi”End Sub

On voit que : L’appel de la fonction sans paramètres Nbf n’a en réalité pas besoin de parenthèses. Cet appel de fonction peut servir d’argument à l’appel de la fonction CStr, qui transforme

un nombre en chaîne de caractères. Il est + simple d’utiliser les propriétés qui vont bien, ici SheetsInNewWorkbook qui donne

directement la valeur cherchée.

Voici la boîte de dialogue :

C’est + simple ainsi

i Il y a 3 feuilles dans un nouveau classeurOK

E. Déclaration de type objet

Pour déclarer un nouveau type objet, le programmeur VBA doit utiliser un module d’un type particulier dit module de classe.

Rappel 1 :Pour accéder à VBA d’Excel, il suffit de faire Alt-F11 ou bien de cliquer dans les menus suivants (Outils, Macro, Visual Basic Editor)

Rappel 2 :Pour obtenir le module de classe, on clique dans l’arborescence. VBA Project puis Insertion Module de Classe.

Le type objet ainsi déclaré porte le même nom que le module de classe. Par conséquent, on ne peut déclarer qu’un type objet par module de classe.Il faut ABSOLUMENT changer le nom du module de classe sinon vous garderez toujours les noms par défaut et serez très ennuyés pour les changer ultérieurement.Technique pour changer les noms par défaut du module de classe.A la place de Classe1, j’ai introduit le nom de ma nouvelle classe “produit”.

Propriétés : produitProduit : ClasseModuleAlphabétique : Par conséquent(Name) ProduitInstauring Private

Comme tout le monde, un module de classe peut contenir des déclarations de types de variables, ainsi que des procédures et des fonctions.

Remarques :Par défaut, les variables globales d’un module de classe sont Private. Elles ne sont pas visibles depuis l’extérieur de ce module.Si nous voulions créer des variables directement accessibles depuis les autres modules, il faudrait les désigner explicitement Public. Elles deviendraient alors des propriétés de chacun des objets créés à partir de la classe.Dans notre exemple, nous ne voulons pas que cet accès direct aux variables depuis un autre module soit possible. Nous aurions pu déclarer non variables avec Dim, elles auraient été Private par défaut. Mais ce qui va sans dire va encore mieux en le disant, c’est pourquoi nous les déclarons explicitement Private.Ici, je définis la référence, la description et le prix du produit :Private mRéférence As StringPrivate mDescription As StringPrivate mPrix As Currency

Propriétés de l’objet créé dans le module de classe :Les propriétés sont de 2 sortes : les variables publiques du module sont des propriétés accessibles à la fois en lecture et

en écriture. les autres propriétés sont élaborées à partir des variables du module par l’intermédiaire

de méthodes particulières Property Get, Property Let ou Property Set. une function Property Get permet de créer une propriété accessible en lecture par le

“monde extérieur à l’objet”.La valeur de cette propriété pourra être une combinaison quelconque de valeurs et de variables privées qui, sinon, seraient inaccessibles.

Exemple 1 : dans le module de classe produit, nous écrivonsPrivate mRéférence As String

Private mDescription As StringPrivate mPrix As CurrencyProperty Get Prix () As CurrencyPrix = mPrixEnd Property

Ceci permettra, par exemple, à un programme de requête d’extraire d’un objet de classe produit, le prix dans le monde réel du produit décrit par cet objet.

Exemple 2 :Je définis une nouvelle classe LigneDeFacture (dans un module de classe qui s’appelle lui aussi LigneDeFacture) qui contient la référence à un objet de classe Produit (mArticle), la quantité vendue (mQuantité) et le taux de TVA à appliquer. J’écris donc dans le module de classe LigneDeFacture :Private mArticle As ProduitPrivate mQuantité As IntegerPrivate mTVA As CurrencyProperty Get MontantTTC () As CurrencyMontantTTC = (mArticle. Prix + mQuantité) + (mTVA + 100)/100End Property

La propriété “MontantTTC” calcule le montant TTC de la ligne de facture. Cet exemple est introduit pour montrer qu’une Property Get est une fonction qui peut renvoyer le résultat d’un calcul complexe, et pas seulement la valeur d’une variable.Par ailleurs, cet exemple montre comment on peut accéder à la propriété Prix d’un objet mArticle de classe Produit, défini dans un objet de la classe LigneDeFacture.L’utilisation dans une expression de mArticle.Prix provoque en fait l’appel de la méthode Property Get Prix décrite à la page précédente.

Exemple 3 :Maintenant encore plus fort : une Property Get peut rendre un objet! Ajoutons la propriété suivante à la classe LigneDeFacture (c’est-à-dire dans le module de classe LigneDeFacture)Property Get article () As ProduitSet Article = mArticleEnd Property

Dans ce dernier exemple, notre nouvelle méthode Property Get Article va récupérer l’objet mArticle (de classe Produit) qui se trouve dans la ligne de facture.Pour nous rendre cet objet, elle doit utiliser une instruction Set.

Synthèse :Une fonction Property Get permet de créer une propriété en lecture renvoyant (à l’exception des tableaux) à un type quelconque. Types simples prédéfinis : Integer, Byte, Long, Float, Double, Currency ... Types composés comme MonTypeAMoi Types objets prédéfinis comme Range, Workbook, etc...

Récapitulation/ Nous avons désormais : Dans le module de classe Produit :Private mRéférence As StringPrivate mDescription As StringPrivate mPrix As CurrencyProperty Get Prix () As CurrencyPrix = mPrixEnd Property

Dans le module de classe LigneDeFacture :Private mArticle As ProduitPrivate mQuantité As IntegerPrivate mTVA As CurrencyProperty Get MontantTTC () As CurrencyMontantTTC = (mArticle.Prix “mQuantité”) (mTVA + 100)/100.End PropertyProperty Get Article () As ProduitSet Article = mArticleEnd Property

La classe Produit possède une propriété Prix.La classe LigneDeFacture possède 2 propriétés :MontantTTC et Article Une procédure Property Let permet de déclarer accessible en écriture une propriété d’un

type simple (Integer, String, Byte...) ou d’un type composé (MonTypeAMoi).

Property Let permet d’affecter une valeur à la propriété. Dans tous les dérivés de BASIC (Basic, QBasic, G W Basic,Visual Basic, VBA ...) l’instruction d’affectation de valeur est Let, (l’usage de ce mot-clé est d’ailleurs facultatif).Property Let n’accepte ni les types objets (qui sont traités par des Property Set), ni les tableaux (que l’instruction Let ne sait pas gérer non +).

Nous allons créer une propriété Quantité accessible en écriture de la classe LigneDeFacture. Pour ce faire, nous ajoutons dans le module de classe LigneDeFacture, dans lequel nous avons déjà :Private mQuantité As Integerla procédure Property Let Quantité qui suit :Property Let Quantité (Quantité As Integer)mQuantité = QuantitéEnd Property

Cette propriété peut être utilisée dans une procédure appelante, comme le montre l’exemple suivant :Sub test ()Dim LDF As New Ligne De FactureLDF. Quantité = 3End Sub

Dans cette procédure test, l’instruction :Dim LDF As New Ligne de Facturea un triple effet elle déclare une variable LDF, de classe LigneDeFacture elle crée dans un zone de mémoire destinée à recevoir les objets créés dynamiquement,

le tas ou heap, un objet de classe LigneDeFacture.

Cet objet, encore anonyme, n’est connu que par sa position dans la mémoire : son adresse. si elle range dans la variable LDF, non pas l’objet lui même mais son adresse également

appelée référence. A partir de ce moment, l’objet est connu sous le nom LDF.

Une variable de type Objet ne contient jamais l’objet lui même, ce qui prendrait trop de place, mais toujours une référence à cet objet.

L’instruction :LDF Quantité = 3utilise la procédure Property Let Quantité pour ranger la valeur 3 dans la variable mQuantité de l’objet LDF.

Lorsque le compilateur trouve le terme LDF.Quantité en comptant le module qui contient la procédure test :

Il constate que ce terme est en position de recevoir une valeur (à gauche d’un signe = )

Il constate également que la valeur (ici 3) n’est pas un objet. Il recherche alors une propriété nommée Quantité de l’objet LDF, lequel est de classe

LigneDeFacture.Il cherche donc cette propriété dans le module LigneDeFacture.Cette propriété peut être une variable publique, ou une property let (une property set est exclue, puisque la valeur n’est pas un objet).

Il trouve notre Property Let Quantité. Il génère donc un appel à cette procédure, et lui passe en argument la valeur

affectée, ici 3.L’instruction LDF.Quantité = 3 est donc remplacée, sans que nous puissions la voir, par quelque chose qui ressemble à LDF.Quantité 3 (syntaxe habituelle d’un appel de procédures).

Cette valeur se range immédiatement dans le paramètre Qté de la procédure. L’instruction mQuantité = Qté de la procédure s’exécute alors pour garnir avec la

valeur 3, comme on le souhaitait, la variable mQuantité de l’objet LDF.L’avantage d’une propriété définie par Property Let par rapport à une propriété qui est une variable publique est que la procédure Property Let peut réaliser des tests de validité sur les données en entrée. Dans notre exemple, on pourrait vérifier que la quantité facturée est bien un nombre entier strictement positif.

Une Procédure Property Set permet de déclarer accessible en écriture une propriété qui est un objet. La Property Set ressemble en tout point au Property Let à la différence près qu’il s’applique à un objet et qu’il faut donc utiliser le mot-clé Set pour l’affectation.

On écrira dans le module de classe : LigneDeFacture.

Property Set Article (P As Produit)Set mArticle = PEnd Property

Et, dans un autre module appelant :

Sub test ()Dim LDF As New LigneDeFactureDim A As New Produit

On mettra ici des instructions destinées à garnir les propriétés de l’objet A de manière à ne pas insérer dans LDF un objet dont les variables privées valent selon leur type soit « », soit ().Rappel : La valeur par défaut d’une chaîne est une chaîne vide, la valeur par défaut d’un nombre est ().

Set LDF.Article = AEnd Sub

Dans ce module test également, il ne faut pas oublier d’utiliser le mot-clé Set pou l’affectation de l’objet A à la propriété Article.Rappel : L’affectation à une propriété se traduit par l’appel de la procédure Property Let ou de la procédure Property Set correspondante.

Module de classe PRODUIT :

Private mReference As StringPrivate mDescription As StringPrivate mPrix As Currency

Property Get Prix () As CurrencyPrix = mPrixEnd Property

‘Property pour lire mReferenceProperty Get Reference () As StringReference = mReferenceEnd Property

‘ Property pour lire mDescriptionProperty Get Description () As StringDescription = mDescriptionEnd Property

‘Property pour écrire dans mPrixProperty Let Prix (lePrix As Currenct)mPrix = lePrixEnd Property

‘Property pour écrire dans mReferenceProperty Let Reference (laReference As String)mReference = laReferenceEnd Property

‘Property pour écrire dans mDescriptionProperty Let Description (laDescription As String)mDescription = laDescriptionEnd Property

Module de LigneDeFacture

Private mArticle As ProduitPrivate mQuantité As IntegerPrivate mTVA As Currency

Property Get MontantTTC () As CurrencyMontantTTC = (mArticle.Prix * mQuantité)*(mTVA + 100)/100End Property

Property Get article () As ProduitSet Article = mArticleEnd Property

‘Property Get pour lire la quantité vendue

Property Get Quantité () As IntegerQuantité = mQuantitéEnd Property

Property Let Quantité (Qté As Integer)mQuantité = QtéEnd Property

Property Set article (P As Produit)Set mArticle = PEnd Property

‘Property Let pour écrire le taux de TVAProperty Let TxTVA (taux As Currency)mTVA = tauxEnd Property

Ce module permet de tester les 2 classes qui précèdent.Module normal Module 1

Sub test ()Dim LDF As New LigneDeFactureDim A As New Produit

‘Pour l’instant toutes les variables privées‘de l’objet A ont pour valeur : « »‘pour les String et O pour les nombres‘nous allons les remplir

A.Prix = 45.89A.Reference = « usbkey – 789 – 512 Mo »A.Description = “clé USB (Universal Serial Bus)”

LDF. Quantité = 3Set LDF.Article = A‘Il faut aussi fixer le taux de TVA‘a appliquerLDF.TxTVA = 19.6‘Maintenant on va enfin pouvoir afficher‘ce qu’on peut du nouvel objet LDFMsgBox « Nous avons vendu » +CStr (LDF.Quantité) + « » + LDF article.Description + « ref » + LDF article.Reference + « pour un montant TTC de » + CStr (LDF.MontantTTC)End Sub

Récapitulation : Quand une propriété d’un objet apparaît dans une expression, c’est la fonction

Property Get Propriété qui est mise en œuvre.Exemple :Dim LDF As New LigneDeFacture……………………………………MsgBox “vous devez” + CStr (LDF.MontantTTC)

met en œuvre la fonction Property Let MontantTTC de la classe LigneDeFacture. La valeur renvoyée par cette fonction est utilisée dans l’expression.

Quand une propriété qui n’est pas un objet apparaît à gauche d’un signe d’affectation =, (c’est la destination de l’affectation) c’est la procédure Property Let Propriété qui est mise en œuvre.

Exemple :Dim LDF New LigneDeFacture…………………………………LDF.Quantité = 3

met en oeuvre la procédure Property Let Quantité de la classe LigneDeFacture. La valeur 3 affectée à la propriété Quantité de l’objet LDF est passée en argument à la procédure Property Let Quantité de la classe LigneDeFacture.

Quand une propriété qui est un objet apparaît à gauche d’un signe d’affectation =, c’est la procédure Property Set Propriété qui est mise en œuvre.

Exemple :Dim LDF As New LigneDeFactureDim A As New Produit…………………………Set LDF.Article = A

met en oeuvre la procédure Property Set Article de la classe LigneDeFacture. La valeur A affectée à la propriété Article de l’objet LDF est passée en argument à la procédure Property Set Article de la classe LigneDeFacture.

Propriétés évoluées   :

Le dernier paramètre des procédures Property Let ou Property Set est la valeur à écrire dans la propriété.Les autres paramètres pourront être utilisés pour préciser plus finement les conditions d’écriture de cette valeur. Par exemple, dans un objet qui est une liste, on y mettre le rang de l’élément visé.

Property Let

Exemple : Créer un module de classe objet Test qui définit la classe ObjetTest et contient.

Property Let ListeValeurs (indice As Integer.Valeur As Integer)MsgBox “mettre la quantité” & valeur & “en position” & indiceEnd Property

Et une sub essai test dans un module standard pour tester

Sub essaitest ()Dim t As New objet testt.ListeValeurs (5) = 3End Sub

Explication de cet exemple   :

Le module de classe ObjetTest représente la façon de créer des objets de la classe ObjetTest.

Chacun de ces objets de classe ObjetTest possède une propriété appelée ListeValeurs qui déclare 2 paramètres indice et valeur, et est vue par les procédures appelantes comme un tableau de valeurs à une dimension (vecteur). On accède en effet à cette propriété par la syntaxe qui permet d’accéder en écriture à un élément d’un tableau :

ListeValeurs (i) = V

Ce « tableau » est en fait une procédure qui prend comme paramètres l’indice dans le tableau et la valeur à mettre dans la case concernée.

Conclusion : le dernier paramètre de la procédure Property Let ListeValeurs est valeur et c’est bien la valeur à écrire dans la propriété. L’autre paramètre indice sert à préciser l’indice de la case de notre pseudotableau dans lequel l’appelant pensait écrire la valeur.

Property Get

La valeur lue de la propriété est la valeur renvoyée par la fonction Property Get correspondante.

Technique   : 1. D ‘abord, il faut définir la structure de nos données : je crée d’abord la structure

d’une classe Client, en écrivant dans le module de classe Client.

Public nomcli as StringPublic adrcli as StringPublic sdcli as Currency

Remarque : les 3 variables étant publiques, elles définissent 3 propriétés de la classe Client, sans qu’il soit besoin de Property Let, ni Property Get.

Puis, je définis une classe Listecli dans un module de classe de même nom Listecli qui contient un tableau de pointeurs vers des objets individuels de classe Client Dim tabli () As Client.La dimension du tableau n’est pas indiquée de manière à pouvoir le redimensionner dynamiquement dans la procédure Property Set sans quoi nos objets Listecli n’auraient pas plus de fonctionnalités que des tableaux ordinaires.

2. Il faut ensuite créer des occurrences à partir des structures de données définies précédemment.

Dans le programme applicatif (module standard)Gpescli je vais définir mes n occurrences de ListeCli

Dim ClientsNormaux As ListecliDim ClientsDouteux As ListecliDim ClientsGrincheux As ListecliDim ClientsJeunes As Listecli

3. Maintenant j’ajoute la propriété item à la classe Listecli en écrivant dans le module de classe Listecli.

Property Get item (indice As Integer) As ClientSet item = tableau (indice)End Property

Cette propriété est une fonction qui rend le Client stocké à la position “indice” dans tabcli. Puisque Client est un objet, l’utilisation de Set est obligatoire.Rappel : Il faudrait également écrire pour la classe Listecli une procédure Property Set item avec 2 paramètres, indice et valeur.

Property Set item (indice As Integer. Valeur As Client)Set tabcli (indice) = valeurEnd Property

Pour simplifier, nous n’avons pas fait figurer ici les isntructions de redimensionnement dynamique du tableau tabcli.

4. Je vais demander le 3ème personnage dans ma Listecli de ClientsGrincheux. J’ajoute donc dans le module standard Gescli les lignes suivantes (comme le tableau tabcli par défaut commence à zéro, je demande l’indice 2).

Sub RechCli ()Dim Cli As ClientSet Cli = Clients Grincheux item (2)End Sub

Par consequent, le type renvoyé par une fonction Property Get doit être le même que celui du dernier paramètre de la procédure Property Let ou Property Set correspondante (on ne peut lire des informations (aller chercher ces informations) que si ces informations ont été écrites dans la même structure de données).

Avec Property Get comme dans le cas des procédures Property Let ou Property Set, les paramètres d’indice en général permettent de décrire plus finement la valeur recherchée.

Synthèse   :

Ecrire sur les tableaux de clients

Module de classe Client

Public nomcli As StringPublic adrcli As StringPublic sdcli As Currency

‘Pas besoin de Property Set ou Let‘toutes les variables sont publics

Commentaires   : Comme le dit le commentaire dans le code, le fait que toutes les variables globales de ce module de classe soient publics évite d’avoir à déclarer des accesseurs (property get, property let) pour faire de ces variables des propriétés. Les objets de la classe Client auront chacun une propriété nomcli, une propriété adrcli et une propriété sdcli, accessibles aussi bien en lecture qu’en écriture.

Module de la classe Listecli

Dim tabcli () As Client

Property Get item (indice As Integer) As ClientSet item = tabcli (indice)End Property

Property Set item (indice As Integer. Valeur As Client)Dim indmax As IntegerIndmax = - 1On Error Resume NextIndmax = Ubound (tabcli)On Error GoTo 0If indice > indmax ThenReDim Préserve tabcli (indice)End ifSet tabcli (indice) = valeurEnd Property

Commentaire :Il n’y a qu’une variable globale au module, c’est un tableau d’objets Clients, nommé tabcli qui, lors de sa création, n’a pas de taille définie.Il sera dimensionné dynamiquement au fur et à mesure de nos besoins par des instructions présentes dans la Property Set item.

Module standard Gescli

Dim ClientsNormaux As ListecliDim ClientsDouteux As ListecliDim ClientsGrincheux As ListecliDim ClientsJeunes As Listecli

Sub Rechcli ()Dim cli = ClientsGrincheux.item (2)Debug.Printcli.nomcliSet cli = ClientsGrincheux.item (3)Debug.Printcli.nomcliEnd Sub

Sub InstalleClient ()Set ClientsGrincheux = New ListecliSet ClientsGrincheux.item (2) = New ClientClientsGrincheux.item (2).nomcli = “Raffarin”Set ClientsGrincheux.item (3) = New ClientClientsGrincheux.item (3).nomcli = “Jospin”End Sub

Commentaire : L’exécution doit commencer par la procédure InstalleClient car, avant de rechercher

un client, il faut l’avoir mis en place. Pour obtenir cette exécution, on peut : Placer le curseur d’insertion dans la procédure Demander l’exécution par

Exécuter Exécuter Sub/Userform Choisir la procédure InstalleClient dans la liste de toutes les sub sans paramètre qui

s’affichent alors, et cliquer sur le bouton exécuter.Ou bien

Provoquer l’ouverture de la fenêtre Exécution (Immédiate)Grâce à l’activation de la rubrique de menu Affichage Fenêtre de exécution de l’Editeur Visual Basic.Cette fenêtre d’exécution permet :

D’introduire des instructions VBA qui doivent être exécuter immédiatement, d’où son nom Immédiate Window en Anglais.

D’afficher les données provenant des instructions Debug.Print Taper dans cette fenêtre le nom de la procédure à exécuter puis la touche Entrée.

Sub installeclient ()Set ClientsGrincheux = New ListecliSet ClientsGrincheux.item (2) = New ClientClientsGrincheux.item (2).nomcli = “Raffarin”Set ClientsGrincheux.item (3) = New ClientClientsGrincheux.item (3).nomcli = “Jospin”End Sub

La 1ère instruction :Set ClientsGrincheux = New ListecliCrée un nouvel objet de classe Listecli et range sa référence dans la variable ClientsGrincheux.

La 2nde instruction :Set ClientsGrincheux.item (2) = New Client

Si le tableau est trop petit, l’agrandirOn peut à présent comparer cette valeur maximale indmax de l’indice à la position indice à laquelle on désire ranger la référence qui se trouve dans le paramètre valeur, et qui est la référence à un objet de classe Client nouvellement créé par le paramètre appelé InstalleClient.Cette comparaison se fait grâce à l’instructionIf indice > indmax then

Bien entendu, au 1er passage dans la procédure, lorsque le tableau tabcli n’est pas encore dimensionné, toute valeur possible de indice est > à la valeur – 1 présente dans indmax. On est donc certain, à ce 1er passage, de redimensionner le tableau tabcli. L’instruction qui permet de modifier la taille d’un tableau est l’instruction ReDim :ReDim Preserve tabcli (indice)L’option Preserve permet de conserver dans le tableau les valeurs préexistantes. Le tableau tabcli accepte désormais comme indice tous les entiers compris entre 0 et indice.

Ranger la référence à l’objetCe rangement se fait grâce à l’instruction :Set tabcli (indice) = valeurProvoque l’appel de la Property Set.item. Le 1er argument, passé dans le paramètre indice, est la valeur 2 qui sert d’indice au « tableau » dont la Property Set simule l’existence. Le 2nd

argument, passé dans le paramètre valeur, est un objet de classe Client nouvellement créé (par New Client).

Remarque : ici la Property Set item permet en fait d’accéder à un vrai tableau.

Nous entrons à présent dans l’étude de la procédure : Property Set item.

La procédure Property Set item du module de classe ListecliLe principe de cette procédure peut se résumer par les 3 étapes suivantes :

Vérifier que le tableau tabcli est assez grand pour accueillir une donnée à l’indice précisé.

Si le tableau tabcli n’est pas assez grand, l’agrandir. Mettre ensuite en place la référence à l’objet passé comme dernier argument à la

procédure (paramètre valeur) à la position indiquée par le 1er argument (paramètre indice).

Rappelons les valeurs des paramètres : Indice contient la valeur 2, située entre les () dans l’instruction d’affectation.

Set ClientsGrincheux.item (2) = New Client Valeur contient une référence à un objet de classe Client récemment créé par New

Client dans la procédure appelé InstalleClient.

Vérifier que le tableau est assez grand.

On peut obtenir l’indice maximum autorisé dans un tableau quelque conque, grâce à la fonction UBound à laquelle on passe en argument le tableau, ici tabcli.Toutefois l’usage de cette fonction UBound déclenche une erreur d’exécution lorsque le tableau n’a jamais été dimensionné, donc lorsqu’on y accède pour la 1ère fois. C’est pourquoi on agira de la manière suivante :

On crée une variable de travail, ici indmax (indice maximum). On y met une valeur < à la + petite valeur d’indice possible qui est 0. Dans cet

exercice, on a choisi la valeur –1. On tente d’y ranger la valeur maximum de l’indice autorisé pour le tableau, tel qu’il

est actuellement, par l’instruction indmax = UBound (tabcli).On protège l’exécution de cette instruction en supprimant le traitement des erreurs par l’instruction On Error Resume Next que l’on a placé avant l’instruction intmax = UBound (tabcli). Lorsque l’instruction indmax = UBound (tabcli) a été exécuté, correctement ou pas, on rétablit le traitement normal des erreurs grâce à l’instruction On Error GoTo 0.Si elle ne s’est pas exécutée correctement, elle a laissé la valeur –1 dans la variable indmax.VI Exécution

Provoquer l’affichage de la fenêtre Exécution, en activant la rubrique de menu : Affichage Fenêtre Exécution

Vider cette fenêtre de son contenu éventuel, par la technique suivante : Sélectionner la totalité du contenu de la fenêtre Enfoncer la touche [Suppr]

Réinitialiser le projet, par l’une des techniques suivantes : Activer la rubrique de menu

Exécution RéinitialiserOu bien

Cliquer le bouton Réinitialiser de la barre d’outils VB.

Faire un clic dans la fenêtre Exécution, puis activer larubrique de menu Réinitialiser du menucontextuel qui vient d’y apparaître.

Lancer la procédure InstalleClient par l’une des 3 techniques expliquées au début. Placer le curseur d’insertion dans la procédure Demander l’exécution par : Ran Run Sub/Userform

Ou bien Placer le curseur en-dehors de toute procédure Demander l’exécution par : Run Run Sub/Userform Choisir la procédure InstalleClient dans la liste de toutes

ses sub sans paramètre qui s’affichent alors, et cliquersur le bouton Exécuter.Ou bien :

Provoquer l’ouverture de la fenêtre Exécution (Immédiate)grâce à l’activation de la rubrique de menu Affichage Fenêtre Exécution de l’Editeur Visual Basic

Taper dans cette fenêtre le nom de la procédure à exécuter , puis la touche [Entrée].

Lancer la procédure Rechcli, par une de ces 3 techniques.Le résultat de la procédure Rechcli s’affiche dans la fenêtre Exécution.

B.Méthodes de l’objet

Toutes les procédures et fonctions situées dans un module de classe et (manque qq mots) Ou bien dont l’accès n’est pas spécifié Ou bien qui sont déclarés spécifiquement Public (c’est le mode standard par défaut)

constitués les méthodes de la classe. Elles sont répertoriées en tant que telles par l’explorateur d’objets, qui prend en compte les classes créées par le programmeur. De même, toutes les procédures, fonctions et variables globales du module de classe déclarées Private constituent des fonctionnalités internes de l’objet. Elles sont répertoriées par l’explorateur d’objets en tant que méthodes ou propriétés privés de la classe.

C. Initialisation de l’objet

Il existe des procédures spéciales appelées procédures évènementielles qui sont appelées lorsque l’une des 2 situations suivantes se produisent :

Class_Initialize est appelée lors de la création d’une instance d’un objet. Class_Terminate est appelée lors de la destruction d’une instance d’un objet.

Exemple de Class_Initialize :Considérons un module de classe qui contient une variable globale nommée val.Dim val As Integer

Nous voulons que tous les objets de cette classe aient cette variable positionnée à 1, dès leur création.Nous pouvons créer dans le module de classe une procédure Class_Initialize qui réalise cette opération.

Private Sub Class_Initialize ()Val = 1End Sub

Exemple de Class_Terminate pour la classe Listecli vue + haut :Private Sub Class_Terminate ()Dim I As IntegerFor I = 0 To Ubound (tabcli)Set tabcli (i) = NothingNextEnd Sub

Rappel : Ubound (tab) donne le numéro du dernier élément du tableau.

Explication : ici on met chaque case de tabcli tour à tour à Nothing c’est-à-dire que ces cases ne pointent + sur aucun objet. Ceci permet de libérer une partie de la mémoire car : un objet sur lequel ne pointe aucun pointeur est effacé automatiquement de la mémoire. En effet, VBA considère que personne n’en a + besoin.Ces procédures Class_Initialize et Class_Terminate sont automatiquement créées avec la mention Private.

Chapitre III : Instructions

Contrairement aux déclarations étudiées dans le chapitre précédent, les instructions de VBA réalisent le travail effectif.

Les instructions de VBA se partagent en plusieurs catégories :

Affectation Contrôle de l’exécution

Instructions conditionnelles Instructions répétitives Branchement Déroulement

Entrées sorties Appels de procédure

Remarque : Un appel de fonction n’est pas une instruction car il est toujours inclus dans une expression, elle-même la plupart incluse dans une instruction d’affectation : un appel de fonction se compose du nom de la fonction, suivi des arguments placés entre () et séparés par des virgules.

I Instructions d’affectation

Il existe en VBA 2 instructions d’affectation :

L’affectation de valeurL’affectation de référence

Ces 2 catégories d’affectation sont très différentes l’une de l’autre :

La 1ère manipule des valeurs La 2ème manipule en fait des adresses

A. Affectation d’une valeur

L’instruction correspondante s’appelle Let. Le mot-clé Let est d’ailleurs facultatif, et la plupart des programmeurs l’omettent.Cette instruction permet d’affecter à une variable la valeur d’une expression.

La variable peut être un élément d’un tableau ou d’une variable constituée de plusieurs champs. Ce peut aussi être une propriété d’un objet.

Dans l’expression peuvent intervenir des variables, des constantes, des appels de fonction, des éléments de tableau, des champs de variable ou des propriétés d’objets.

Syntaxe : [Let] variable = expression

Nota : les crochets carrés dénotent, dans les descriptions syntaxiques des éléments facultatifs.

Exemple :

Dim Mavar As Integer Dim Autrevar As Integer

…………………………… Let Mavar = 3*Autrevar + 2

Ou + simplement Mavar = 3* Autrevar + 2

La variable à gauche du signe = peut intervenir dans l’expression, c’est son ancienne valeur qui est alors prise en compte.

Exemple : Mavar = Mavar + 3

Autre exemple utilisant des tableaux : Typer t_tabl Nbelem As Integer Datemodif (1 To 100) As Date Somme As Currency Monnaie As Currency End Type Dim var1 As t_tab ……………………. Var1 nbelem = var1.nbelem + 1 ‘Date de modification Var1.datemodif (var1.nbelem) = Now ‘On transforme en € Var1.somme = var1.somme/6.55957 Var1.monnaie = « EUR »

Explication :

Le type t_tab comporte 4 champs : Le 1er champ, nbelem, est une entrée qui contiendra le nombre d’éléments utiles du

2nd champ

Le 2nd champ, datemodif, est un tableau de dates contenant 100 éléments d’indices 1 à 100.

Le 3ème champ, somme, est de type Currency (monnaie) Le 4ème champ, monnaie, est de type String (chaîne de caractères) La variable var1 est de type t_tab La 1ère instruction d’affectation :

Var1.nbelem = var1.nbelem + 1Augmente d’une unité le nombre d’éléments utilise afin de se souvenir qu’une nouvelle case a été utilisée.

La 2nde instruction d’affectation :Var1.datemodif (var1.nbelem) = NowUtilise la nouvelle valeur de ce nombre d’éléments comme indice pour ranger la date et l’heure de l’opération dans un élément jusqu’ici inutilisé du tableau datemodif.

Remarque : la fonction précédente, sans paramètre, Now, fournit la date et l’heure courante. On pourrait également écrire son appel Now () pour mieux faire ressortir l’absence d’arguments.

Autre exemple :En utilisant une propriété d’un objet, cet objet étant lui-même obtenu grâce à un appel d’une méthode d’un autre objet

Cells (1, 2).Value = « toto » Cells (4, 3).Value = Cells (1, 2).Value

Cells est une méthode Property Get de la classe d’objets Worksheet, qui s’applique ici à l’objet ActiveSheet qui est sous entendu. Cette méthode fournit un objet anonyme de classe Range, constitué d’une seule cellule, à la propriété Value duquel nous affectons une valeur.Retenons que :

Cells (1, 2) est le cellule B1 Cells (4, 3) est la cellule C4

Remarque : traditionnellement lorsqu’on parle d’objets, on emploie le mot classe au lieu de type.

La 1ère instruction remplace la propriété Value de la cellule B1 par la chaîne « toto ». La 2nde instruction remplace la propriété Vallue de la cellule C4 par celle de la cellule

B1. Après l’exécution de ces 2 instructions la chaîne « toto » s’affichera dans les 2

cellules B1 et C4.

B. Affectation d’une référence

L’instruction Set permet d’affecter la référence d’une variable de type objet à une autre variable de même type objet, du type générique Objet, ou du type Variant.

Remarque : Nous avons vu + haut, au chapitre des déclarations, que le programmeur VBA peut créer ses propres classes d’objets. L’instruction Set peut être utilisée pour l’affectation de référence de tels objets.

Syntaxe : Set variable1 = variable 2

Le mot Set est obligatoire pour distinguer Set et Get.

Les noms de variables liés par Set désignent désormais le même emplacement de mémoire c’est-à-dire la même adresse

A partir de l’exécution de l’instruction Set, les 2 variables sont équivalentes : toute affectation de valeur à une propriété de l’une d’entre elles résulte en la modification de la valeur de la propriété correspondante de l’autre.Cet état de choses perdure jusqu’à l’exécution d’une autre instruction Set qui fera pointer la 1ère variable sur un autre objet, ou bien qui utilisera le mot réservé Nothing (Rien).