Master 1ère Année Faculté De Paris XIII

VBA
CHAPITRE 1 : PRESENTATION DE L’INTERFACE VBA ..................................... 4

1. Introduction ................................................................................................................................................... 4

1.1 Lancement de VBA .................................................................................................................................... 5

2. Menu Contextuel Affichage .......................................................................................................................... 6

3. Menu Contextuel Insertion........................................................................................................................... 8

4. Menu Contextuel Déboguage ....................................................................................................................... 8

5. Le menu Contextuel outil ............................................................................................................................. 9

CHAPITRE2 LES OBJETS EXCEL ..................................................................... 10

1 - L’objet Application .................................................................................................................................... 10

2. Les objects Workbooks ............................................................................................................................... 10

3 Les objects Sheet .......................................................................................................................................... 11

4 Les Cellules .................................................................................................................................................. 12

CHAPITRE 3 : LES VARIABLES ET CONSTANTES .......................................... 14

1. Les chaînes de caractères (variable de type String) ................................................................................ 15

2. Les valeurs de type numériques :.............................................................................................................. 16

3. Les variables de type Date .......................................................................................................................... 17

4. Les variables Booléennes et constantes ..................................................................................................... 17

5. Les variables de type Variant ..................................................................................................................... 18

6. Les variables de types Object .................................................................................................................... 18

7. Les variables de type tableaux ................................................................................................................... 18

8. Les variables de type personnalisées ......................................................................................................... 19

9. Conversion de type de donnée entre elles .................................................................................................. 19

10. Portée et durée de vie des variables ......................................................................................................... 20

CHAPITRE 4 PROGRAMMATION SEQUENTIELLE ET BOUCLE

D’ITERATIONS ..................................................................................................... 21

1. Les différents types de procédures ............................................................................................................ 21

2. Les Structures de contrôle VBA ................................................................................................................ 23

Modélisation de l’économie et de la Finance Internationales

-2-
CHAPITRE 5 LES TABLEAUX CROISES DYNAMIQUE : LES PIVOT TABLES26

1. Première approche ...................................................................................................................................... 26

2. Méthodes et propriétés des pivotTables .................................................................................................... 28

3 Les Graphiques (Charts) et les Objets Graphiques (ChartsObjects) ..................................................... 30

CHAPITRE 6 : LES FEUILLES VBA OU USERFORMS ...................................... 32

1 - Présentation de la Fenêtre des UserForms .............................................................................................. 32

2 .La Boite à outils ........................................................................................................................................... 33

3 Utilisation des userforms ............................................................................................................................. 35

CHAPITRE 7 : LES QUERYTABLES ET OBJET ADODB.................................. 37

1 Référence des objets ADO .......................................................................................................................... 37

2. Les étapes des requêtes ............................................................................................................................... 38

3. Utilisation des données ................................................................................................................................ 39

4. Fin des objets crées ..................................................................................................................................... 40

5. Les propriétés des connections et les transformations de tables ............................................................. 40

6. Les QueryTables ......................................................................................................................................... 41

Modélisation de l’économie et de la Finance Internationales

-3-
 Chapitre 1 : Présentation de l’interface VBA

1. Introduction

Je vais commencer par donner un descriptif assez schématique des différentes fenêtres et
propriétés utiles pour une bonne appréhension de l’outil VBA dans son ensemble. Ce
descriptif n’est évidemment pas exhaustif et je laisse le soin à chacun de découvrir par lui-
même cet environnement.

Zone Objet Zone procédure Evènement

pour une feuille ou un
contrôle. Affiche pour le
module sélectionné ses
Fenêtre de Projet procédures et fonctions

Fenêtre de Code (F7)

Fenêtre des Propriétés

d’objets

Modélisation de l’économie et de la Finance Internationales

-4-
1.1 Lancement de VBA

Modélisation de l’économie et de la Finance Internationales

-5-
2. Menu Contextuel Affichage

2.1 Fenêtre d’explorateur de projet (Ctrl+R)

Cette fenêtre permet de visualiser l’architecture du projet dans lequel nous sommes, c’est-à-
dire sa composition (Sheets, worksheets, xla, module, module de classe, référence,
Userform,etc.)
Nous verrons ces différents éléments un peu plus loin.

2.2 Fenêtre de propriétés (F4)

Chaque objet est défini par un ensemble de propriétés qui lui confère un comportement
particulier, c’est grâce à cette fenêtre que nous allons pouvoir directement attribuer
certaines particularités à un objet.

2.3 Fenêtre de code (F7)

C’est dans cette partie que nous allons implémenter notre code.

2.4 Fenêtre espions

Cette fenêtre est très utile pour connaître l’état de nos variables en cours d’exécution, elle
nous présente son type, sa valeur et le contexte dans lequel cette variable se trouve ainsi que
ses attributs, un simple glisser/déplacer dans cette fenêtre permet d’accéder à ces
informations ou clic droit /ajouter un espions.

Modélisation de l’économie et de la Finance Internationales

-6-
 2.5 Fenêtre Variables locales
A l’instar de la fenêtre espion elle recense toutes les variables qui composent le module ou
nous nous trouvons.

2.6 Fenêtre Pile des appels

Très utile lorsqu’on est perdu, on peut dire que c’est le GPS de VBA, cela nous indique le
cheminement des fonctions appelantes et appelées.

2.7 Fenêtre Explorateur d’objet (F2)

Zone projet Afficher/Masquer les

résultat de la recherche

Affiche la définition

Volet Détail Liste Membres des

Liste Classe classes.

Cette fenêtre nous renseigne sur le types de variables qui sont renvoyés par les propriétés et
méthodes et surtout sur son appartenance à une classe car comme nous le verrons tous les
objets Excel sont contenus dans une collection qui elle-même est une sous partie d’un autre
objet jusqu’à l’application entière Excel (principe des vases communicants)

Modélisation de l’économie et de la Finance Internationales

-7-
2.8 Fenêtre d’exécution

Cette fenêtre est utile pour vérifier la syntaxe d’une variable string par exemple en cas
d’erreur, car dans certains cas la variable string peut être une combinaison de variable et de
déclaration fixe qui doivent répondre à un protocole bien défini (par exemple c’est le cas
d’une requête dynamique dans une base Access)
Pour accéder à cette information il faut faire précéder la déclaration de cette variable d’un
point d’interrogation. De plus si l’on souhaite établir des traces d’une variable MaVariable
à travers tout le code pour s’assurer du bon déroulement, il suffit de placer dans notre code
la syntaxe suivante debug.print MaVariable.

3. Menu Contextuel Insertion

Permet d’introduire des modules, des modules de classe et des Userforms dans vote projet
(un simple clic droit dans la fenêtre d’explorateur de projet fait très bien l’affaire)

4. Menu Contextuel Déboguage

Modélisation de l’économie et de la Finance Internationales

-8-
4.1 Compile VBA Project
Le premier sous-menu est la compilation. Ce n’est qu’une traduction en langage machine
de votre code afin que la machine puisse l’interpréter. Une compilation réussie ne veut pas
dire que le code n’est pas rempli d’imperfection.

4.2 Pas à Pas détaille (F8)

C’est le plus utilisé des modes de déboguage car cela permet de bien contrôler son code est
de visualiser à chaque étape les valeurs des variables en cours (exécution pas à pas).

4.3 Déboguage basculer les points d’arrêts (F9)

Permet de stopper l’exécution sur une ligne de code bien déterminée et donc de passer des
étapes et ne vérifier que les zones où est susceptible de se trouver l’erreur.

Les autres sous menus ne sont que des redondances de ce qui précède.

5. Le menu Contextuel outil

5.1 Références
Ce sous menu présente toutes les références associées a votre projet, par exemple si vous
avez besoin d’utiliser des connexions à une table Access il vous faudra cocher « Microsoft
ADO Ext 2.8 for DLL and Library » afin de pouvoir utiliser l’aide express et les outils
relatifs au base Access. Il est important de noter que s’il manque une référence à votre
projet le débogueur vous présentera une fausse erreur d’interprétation sur n’importe quelle
partie du code (non compréhension de la variable de type string par exemple). Dans ce cas
il faut juste aller dans les références et rajouter celle manquante.

5.2 Options
Permet de définir des choix pour le paramétrage de quelques fenêtres, Mode ancrage des
fenêtres de code ou autres, utilisation de l’info express, info bulle automatique, etc…
Il peut être judicieux de cocher dans l’onglet éditeur la case « Déclaration des variables
obligatoires » mais on peut aussi rajouter en tout début de code l’expression Option
explicit qui aura les mêmes conséquences

5.3 Propriétés de VBAProject

La seule utilisation vraiment intéressante est de pouvoir mettre un mot de passe à votre
code VBA dans l’onglet « Protection »

Et enfin l’aide en ligne, la touche reine par excellence : F1.

Modélisation de l’économie et de la Finance Internationales

-9-
 Chapitre2 Les objets Excel
Il faut se souvenir d’une règle d’or, vous ne pouvez influer une propriété d’un objet que si
vous avez sélectionné cet objet, sinon VB vous affichera des erreurs pas toujours facilement
interprétable. L’arborescence des objets Excel se présente de la manière suivante :
l’application Excel comporte une collections de classeurs, chaque classeur comporte une
collection de feuilles qui elle-même rassemble une collections de cellules.

1 - L’objet Application
Le principal problème de VBA et sa rapidité mais il existe quelques propriétés et méthodes
qui permettent d’améliorer cet inconvénient.

ScreenUpdating=false
Pour des raisons essentiellement de rapidité il peut être intéressant de figer l’écran
lorsqu’on est amené à sélectionner plusieurs feuilles dans un même classeur cela permet
d’éviter les scintillements dus aux changements de feuilles de calcul.

DisplayAlerts = false
Cette propriété permet de supprimer les messages d’alertes d’Excel tel que ceux renvoyés
par Excel lors de la suppression d’une feuille par exemple (fenêtre de validation des
actions).

StatusBar = « Message à l’attention des utilisateur »

Lorsque les traitements sont lourds et prennent du temps, il peut être intéressant de laisser
des messages pour signaler aux utilisateurs ce que fait le programme actuellement.

Calculation= xlCalculationAutomatic, ou xlCalculationManual ou

CalculationSemiAutomatic
Cela permet encore d’accélérer le processus car il évite que Excel recalcule à chaque
rafraîchissement les formules toutes les cellules.
.
Dialogs(xlDialogOpen).Show
Permet d’afficher toutes les boites de dialogues d’Excel (la formulation ci-dessus affiche la
boite de dialogue ouvrir aux utilisateurs.

Run(« NomdeMacro », Argument1 de la macro, Argument2 de la macro)

Cette méthode sert à exécuter une macro ou appelle une fonction. Elle peut être utilisée
pour exécuter une macro écrite en VBA ou une DLL ou XLL. Nous verrons plus loin qu’il
existe une autre façon de procéder pour activer un code externe dans les macros (fonction
SHELL).

2. Les objects Workbooks

Ce qui faut immédiatement retenir c’est que généralement les noms qui se termine par un
« s » signifie que cet objet représente la collection d’objet et non pas l’objet lui-même.
Workbooks représente donc la collection des classeurs ouverts. L’ordre des classeurs dans
la collection est l’ordre d’ouverture des classeurs. Il ne faut pas confondre l’appel

Modélisation de l’économie et de la Finance Internationales

- 10 -
ThisWorkbook et Workbook. Workbook représente un classeur actif il faut donc spécifier
celui dont on parle alors que thisworkbook représente le classeur actif, c’est à travers ce
fichier que l’on peut gérer les procédures événementielles

2. 1 Les méthodes

Add
Permet d’ajouter un nouveau classeur à la collection. Le classeur crée devient le classeur
actif. On accède aux membres de la collection par un indice workbooks(index) ou le nom
workbooks(« Nom du classeur »)

Close
Si on ne spécifie pas un classeur alors tous les classeurs composant cette collection seront
fermés.

Open
Ouvre un classeur Excel
Workbook.open(FileNAme,UpdateLink,readonly,Format,Password,WriteResPassword,Ign
oreReadOnlyrecommended, Origin, Delimiter,Editable, Notify, Converter, AddtoMRU)
Seul Filename est obligatoire.

OpenText
Permet d’ouvrir un fichier text

2.2 Les propriétés

L’appel à des fonctions se fait à travers l’affectation à des variables.

Names
C’est une collection de name c’est-à-dire que cette collection contient toutes les cellules
typées contenues dans le classeur actif. Pour accéder aux noms de l’objet name, on déclare
une variable MaVariable de type name et on écrit MaVariable.name.

FullName
Permet d’afficher ou de connaître le chemin du classeur actif

3 Les objects Sheet

La collection Sheets est une collection regroupant tous les onglets d’un classeur quelque
soit son type : feuille de calcul (objet worksheet), feuille graphique (objet Chart), etc….
C’est à travers cette collection que l’on pourra ajouter, supprimer, comptabiliser, déplacer,
cacher, activer des feuilles de tout genre.

3.1 Les méthodes

Add(Before, After, count, type)

Before/after permet de préciser la position où la feuille doit être ajoutée. L’argument count
détermine le nombre de feuilles à ajouter.
Vous pouvez aussi ajouter et nommer directement une sheet dans la même expression
Sheets.Add.Name = « NomDeSheet »

Modélisation de l’économie et de la Finance Internationales

- 11 -
Delete
Supprime l’onglet sélectionné.

Move(Before, After)
Déplace la feuille en question (à noter que l’expression correcte est : Sheets(index ou
NomDeSheet).Move Before:=Sheets(position voulue)

Visble=xlsheethidden
Masque la sheet dans l’objet workbook mais l’utilisateur peut encore la voir dans
Format\Feuille\Afficher\ alors que la formule suivante
Visble=xlsheetveryhidden
Masque la sheet pour l’utilisateur, elle n’est visible que par le code on peut la faire
réapparaître par Visble=xlsheetvisible

3.2 Fonctions intéressantes des objets Sheet :

UsedRange : .rows.count ou .columns.count : indique aussi le numéro de la dernière ligne

et de la dernière colonne utilisée dans la sheet

4 Les Cellules

4.1 Les fonctions intéressantes

Row, Column
Renvoie le numéro de ligne ou de colonne de la cellule

End(xlDown, xlUp).row
Indique par exemple la dernière (resp la première) ligne de la cellule non vide en dessous
(au-dessus) de la cellule sélectionnée.

End(xlToRight,xlToLeft).column
Indique la dernière colonne non vide à droite (resp à gauche) de la cellule sélectionnée.

Le typage des cellules est très utilisé lui aussi car il permet de situer l’objet uniquement
avec le nom associé à la cellule. Vous devez sélectionner une cellule puis dans la Zone
« Zone nom » donner un nom à votre cellule.

Modélisation de l’économie et de la Finance Internationales

- 12 -
Cette zone permet de
définir un nom pour la
cellule sélectionnée

Ensuite pour accéder à cette cellule vous devez utiliser soit Range(« NomDeCellule »)
Ou [NomDeCellule]

CurrentRegion
Indique la zone non disjointe autour d’une range de référence (ici Macell)
Très utile si vous voulez travailler dans une zone définie ou pour délimiter une zone de
travail

La zone surlignée est la

zone qui correspond à
l’instruction
MaCell.CurrentRegion

Address
Renvoie l’adresse de l’objet sélectionné. Ceci simplifie parfois beaucoup les choses et
permet de situer entièrement l’objet avec une seule variable.

Offset (Ligne,Colonne)
On se décale de Ligne et colonne par rapport à la range de référence.

AutoFit
Ajuste la largeur de colonne et la hauteur en fonction du contenu. Si l’on veut
redimensionner la feuille entière il suffit d’écrire Cells.Columns.Autofit.

Find et FindNext(what, After, LookIn, LookAt, SearchOrder, SearchDirection,

MatchCase, MatchByte)
Permet de faire des recherches sur l’élément recherché qui est What, si l’on veut que la
recherche soit exhaustive il faut mettre la propriété LookAt à xlwhole)

Modélisation de l’économie et de la Finance Internationales

- 13 -
 Chapitre 3 : Les variables et constantes
Les variables sont un élément important de la programmation. Elles permettent de stocker
les informations dont vous avez besoin tout au long de votre programme. Dans des
programmes conséquents il faut faire attention aux types de données choisis car cela est
directement lié à l’espace mémoire alloué à la machine. Si vous utilisez beaucoup d’espace
mémoire inutilement cela se répercutera sur la rapidité de votre programme. Il faut
évidemment qu’il y ait une concordance entre le type de variable déclaré et le type de
variable stocké.
Je vous conseille de toujours déclarer toutes vos variables car c’est la base d’un
programme robuste. Pour cela vous devez avant chaque développement faire apparaître la
mention Option Explicit sur la première ligne en dehors de toute méthode ou fonction.
Ces mots clés de VB impliquent la déclaration explicite de toutes les variables sinon VB
générera des erreurs lors de la compilation ou même lors d’une exécution partielle.
Une deuxième solution est d’aller sous Outil/Options et de cocher « Déclaration
des variables obligatoires », et de vérifier si les autres options d’aide à la programmation
sont bien référencées (complément automatique des instructions et info express
automatique par exemple).

Modélisation de l’économie et de la Finance Internationales

- 14 -
1. Les chaînes de caractères (variable de type String)

Dim MaVariable as string

Pour affecter une expression qui ne résulte pas d’une fonction ou d’une propriété à une
variable de type string il faut la placer entre guillemets (chaîne de caractère constante)
MaVariable = « Faculté, Paris XIII »
Sinon on peut directement l’affecter à celle-ci (propriété name d’une feuille ou d’un
classeur)
MaVariable = Activeworkbook.name
Il est possible de mixer les expressions et les variables dans une même expression de type
string avec l’opérateur de concaténation « & » de la façon suivante :
MaVariable = « Ce Classeur est enregistré sous : « & Activeworkbook.path
On peut aussi utiliser l’opérateur « + » mais je le déconseille.

Ces variables sont dites de longueur variable et peuvent être constituées d’environ deux
milliards de caractères, dans un souci d’économie de mémoire et donc de performance il est
possible de limiter le nombre de caractères (de 1 à 65 400 exactement) par la syntaxe :
Din Mavariable as string * longueur avec longueur = nombre de caractères voulus

Si Mavariable reçoit une chaîne de caractère supérieur à longueur, les éléments à partir de
longueur +1 ne seront pas pris en compte, celle-ci sera rognée. Si Mavariable reçoit une
chaîne de caractère inférieur à longueur alors VBA comblera la chaîne de caractères avec
des espaces vides.

Il y a beaucoup de fonctions ou de méthodes relatives à la manipulation des variables de

type String, les plus usuelles sont les suivantes :

Trim(MaVariable)
Supprime les espaces vides de MaVariable très utile si MaVariable est insérée à l’intérieur
d’un Select Case.

Ucase(MaVariable)
Met Mavariable en majuscule, cela permet de se délester du problème de la casse rentré par
l’utilisateur.

Len(Mavariable)
Permet de connaître la longueur d’une chaîne de caractères, cette fonction retourne une
valeur numérique de type Long.

Mid(Mavariable, début, longueur)

Permet de connaître les caractères qui se situent entre début et longueur dans la chaîne
Mavariable, retourne une valeur de type variant (string).

Instr(Début,String1,String2,TypeComparaison)
Permet de savoir si string2 est compris dans string1 en commençant par le debut ième
caractère, cette fonction retourne une variable de type Long.(= 0 signifie que String2
n’appartient pas à String1

Modélisation de l’économie et de la Finance Internationales

- 15 -
Left (MaVariable, longueur)
Retourne une variable de type variant contenant tous les caractères de la string MaVariable
en partant sur la gauche jusqu’à longueur.

Right (MaVariable, longueur)

Retourne une variable de type variant contenant tous les caractères de la string MaVariable
en partant de la droite jusqu’à longueur.

StrComp(String1, String2, TypeComparaison)

Permet de savoir si la string1 est alphabétiquement supérieur ou inférieur à la string2.
Attention : Cette fonction ne répond pas à la nomenclature internationale ASCII mais à la
nomenclature Microsoft (par exemple Microsoft place le underscore avant la lettre A alors
que selon la norme ASCII il est situé après). Permet de faire des recherches accélérées dans
des listes triées au préalable.

2. Les valeurs de type numériques :

Type de Données Valeur acceptées Mémoire

Byte Nombre entier compris entre 0 et 255 1 octet
Integer Nombre entier compris entre -32768 et 32 767 2 octets

Long Compris entre -2.147 M et 2 147 M 4 octets

Single Nombre à virgule flottante compris entre 4 octets
E(-45) et E(38)
Double Nombre à virgule flottante compris entre 8 octets
E(-324) et E(308)

Au même titre que les variables de type string vous pouvez affecter à une variable de type
numérique des résultats de fonctions ou de propriété ainsi que des formules.
VB intègre de nombreuses fonctions permettant de manipuler des valeurs numériques, en
voici quelques unes :

Abs(nombre) :
Renvoie la valeur absolue d’un nombre

Int(nombre) :
Renvoie la partie entière d’un nombre

RoundUp(nombre,précision)
Arrondie le nombre au nombre supérieur à précision chiffre après la virgule
Si nombre = 12.137, RoundUp(12.137,2)=12.14. Il existe la même fonction avec
RoundDown.

Généralement nous pouvons accéder à toutes les fonctions excel VB en utilisant

WorkSheetFunction.(Min, Max, etc…..)

Modélisation de l’économie et de la Finance Internationales

- 16 -
3. Les variables de type Date

Syntaxe
Dim MyDate as date

Ce type de variable peut recevoir des dates comprises entre le 1er janvier 100 et le 31
décembre 99999 Elles sont stockées sous 8 octets.
La aussi il existe de nombreuses fonctions permettant de manipuler des dates.

Year(MyDate), Month(MyDate), Day(MyDate)

Retourne respectivement l’année de date, le mois et le jour.

Now() ou today()
Retourne la date d’aujourd’hui.

DateDiff(intervalle, date1, date2)

Permet de connaître le nombre d’intervalles entre date1 et date2. L’intervalle correspond à
l’unité de temps utilisé pour calculer la différence entre date1 et date2. Par exemple si on
souhaite le nombre de jour DateDiff(«d»,date1,date2)

Weekday(MyDate)
Renvoie un variant qui correspond au jour de la semaine
Weekday(MyDate)=1 , signifie que Mydate est un dimanche et ainsi de suite jusqu’au
samedi. On peut se servir de cette fonction pour éviter de comptabiliser les weekends
Mydate = if(weekday(today()-1=1 ; today()-3 ;today()-1) on exclut de mydate les samedi et
dimanche.

DateAdd(Intervalle, nombre, Mydate)

Permet d’ajouter à la variable Mydate, le nombre sur l’unité intervalle choisi, renvoie une
variable de type Date.
Par exemple DateDiff(«d»,3, « 12/25/2006 ») = «12/28/2006»

4. Les variables Booléennes et constantes

Ce type de variables est utilisé pour stocker le résultat d’expression logique. Elle renvoie
donc les valeur « true » ou « false »
Dim Mavariable as boolean

Elle peut être aussi le résultat d’une fonction qui renvoie une variable de type integer dans
ce cas 0 sera la valeur false et 1 la valeur true. Généralement ces variables sont utilisées
dans les boucles de répétition while

La syntaxe pour les déclarations de constantes est un peu différente, il faut noter que par
exemple les tableaux n’acceptent pas d’autres types pour la définition de la dimension.
Const Mavariable as Integer = 5
Une fois qu’une valeur a été définie à une constante celle-ci ne peut-être modifiée

Modélisation de l’économie et de la Finance Internationales

- 17 -
5. Les variables de type Variant

Elles remplacent n’importe quelles types de données et peuvent être converties

automatiquement d’un type à l’autre, tant que sa valeur est compatible avec le type vers
lequel s’opère la conversion. Elles sont donc très pratiques puisqu’elles sont par nature
multi types mais l’inconvénient c’est qu’elles sont très gourmandes en matière de mémoire
(16 octets pour les valeurs numériques et 22 pour les valeurs de chaînes). Dans la syntaxe
de déclaration vous pouvez omettre le type associé mais je vous le déconseille.
Dim MaVariable
Syntaxe correcte : Dim MaVariable as variant

6. Les variables de types Object

Elles font référence à un objet que vous pouvez spécifier et occupent 4 octets en mémoire.
La syntaxe est un peu différente car en plus de la déclaration de type il faut lui affecter un
objet précis par l’usage du mot clé Set
Dim MaVariable as object
Set MaVariable = Expression
Où expression est une expression renvoyant un objet de l’application.
Pour annuler l’affectation à un objet et libérer la mémoire allouée à cet objet, il faut affecter
à cette variable objet la valeur nothing avec l’instruction suivante :

Set MaVariable = Nothing

7. Les variables de type tableaux

Les tableaux ne sont que des vecteurs de données. On peut donc vectoriser toutes variables
y compris des tableaux.
A noter que l’on ne peut pas définir les variables tableaux dans les modules de classe en
propriété public, il faut dans ce cas définir des objets de type Collection. Il existe une
contrainte dans l’utilisation de tableaux, c’est que la dimension des tableaux doit être
exprimée par une constante, ce qui en cas de non connaissance au préalable du nombre
d’objets peut-être préjudiciable. Normalement la dimension des tableaux est initialisée à
zéro mais l’on peut modifier cette règle en plaçant en dehors de toute procédures
l’expression
Option base 1
La syntaxe est la suivante

Dim Montableau (10) as string, variant, Types Personnalisés, etc…

Ce tableau contiendra donc 11 éléments.

Si l’on ne connaît pas le nombre d’éléments, la première solution est dans un premier temps
de comptabiliser et stocker cette information dans une variable et de redimensionner le
tableau avec l’instruction :

Redim MonTableau(n) as String

Modélisation de l’économie et de la Finance Internationales

- 18 -
Nous pouvons faire des tableaux à dimension dynamique mais ATTENTION seule la
dernière dimension du tableau peut-être redimensionnée sinon on perd les informations
contenues dans le tableau (tableau à 3 dimensions, seules la 3ième dimension peut-être
redimensionnée).

La syntaxe est la suivante :

Dim Montableau() as integer : Tableau de type entier. Les parenthèses signifient que c’est
un tableau

Redim Preserve MonTableau(i) : on peut redimensionner la dernière dimension du tableau

à chaque itérations à l’aide de la variable i

8. Les variables de type personnalisées

Ces variables sont intéressantes car elles permettent de regrouper différents types de
données sous un même objet. Ces variables s’adaptent bien dans le cas ou l’on souhaite
définir un objet dont les composantes peuvent avoir plusieurs formes. L’exemple
généralement utilisé est celui relatif à une personne. A travers l’objet Personne de type
Type. On peut définir son adresse (string), son âge (integer), sa couleur de cheveux, etc…
Attention : On ne peut pas définir ce type de variable dans un module de classe ou dans
une procédure, on doit le déclarer dans la section des déclarations d'un module. Tout code
situé en dehors d'une procédure est dit « de niveau module ». Les déclarations doivent
apparaître avant les procédures.

La syntaxe est la suivante :

Type Personne
MonAge as Byte
MonAdresse as string
End type

Pour accéder aux propriétés de ce type il suffit de déclarer la syntaxe suivante

Dim MaVariable as Personne
Donc pour modifier l’âge d’une personne, on écrira MavAriable.MyAge =20 et
MaVariable.adresse = »monAdresse….. ».
On touche un peu à la programmation de type objet,
A signaler qu’il existe une fonction permettant de savoir le type d’une variable mais elle
n’a que très peu d’utilité (TypeName(MaVariable))

9. Conversion de type de donnée entre elles

On peut être obligé de forcer le mode de donnée des variables pour que le système ne les
rejette pas.

Fonctions Définition
CByte(mavar) Convertit mavar en une variable de type Byte
CDate(mavar) La variable mavar sera de type Date
CDbl(mavar) Convertit mavar en une variable de type Double
CStr(mavar) Convertit mavar en une variable de type String

Modélisation de l’économie et de la Finance Internationales

- 19 -
Il en existe d’autres mais je laisse le soin aux personnes intéressées de consulter l’aide en
ligne de VB

10. Portée et durée de vie des variables

Les variables sont caractérisées par leur accessibilité dans le programme ce que l’on appelle
leur portée et leur durée de vie. En effet une variable peut conserver sa valeur tant qu’une
procédure s’exécute et être réinitialisée lorsque la procédure est terminée ou conserver sa
valeur entre les différents appels de procédures.

10.1 Portée de niveau procédure

Ce sont les variables déclarées à l’intérieur d’une procédure sans autres formes de
déclarations que celle-ci :
Dim Mavariable as TypeChoisi

10.2 Variables Static

Une variable conserve sa valeur tant que le programme s’exécute dans son champ de
portée. Si le programme sort de la procédure les variables propres à celles-ci sont
automatiquement réinitialisées, sauf si on a placé le mot clé Static avant la déclaration de
celle-ci :
Static MaVariable as type
Elle conserve sa valeur entre chaque appel

10.3 Portée de niveau Module

Elles doivent être déclarées à l’extérieur de toutes fonctions ou procédures, dans la section
déclaration d’un module. Elles peuvent être privées ou publiques. Si on les déclare de
niveau module avec le mot clé Private elles ne seront lisibles que par les procédures du
module, si on les déclare par le mot clé Public cela signifie qu'elles seront disponibles pour
toutes les procédures de tous les modules de toutes les applications.

Public/Private MaVariable as Type.

Modélisation de l’économie et de la Finance Internationales

- 20 -
 Chapitre 4 Programmation Séquentielle et boucle
d’itérations
Un programme est composé de module, de module de classe permettant de structurer des
objets et des Userforms. (click droit sur ThisWorkbook dans la fenêtre vierge de
l’explorateur de projet)

Il faut savoir que ce n’est pas absolument nécessaire de connaître la syntaxe d’un appel de
fonction car il y a toujours l’aide en ligne et l’enregistreur de macro (Outil/Macro/Nouvelle
Macro) pour nous tirer d’un mauvais pas mais le code issu d’un enregistrement n’est jamais
très efficient il faut l’adapter à la situation.
Dans un premier temps nous allons voir l’utilisation du code dans un module

1. Les différents types de procédures

Il y a trois formes de déclarations

Les procédures de types Sub
Les procédures de types Function
Les procédures de types Property

1. 1 Les procédures Sub

Les procédures Sub sont une série d’instructions entourées des mots clés
Sub NomDeMaProcedure([ByVal] | [ByRef] arg1 as type, [ByVal] | [ByRef] |
[Optional]arg2 as type)
Instructions
End sub

On peut passer des arguments à toutes ces procédures en valeur ou en référence ou en

mode optionnel, si on ne spécifie rien le mode par défaut est le mode ByRef .Le mode
ByVal signifie que l’argument passé à la procédure pourra prendre différentes valeurs à
l’intérieur de celle-ci mais il sera réinitialisé lors de sa sortie à sa valeur d’appel. En fait

Modélisation de l’économie et de la Finance Internationales

- 21 -
lors d’un passage par valeur c’est la copie de l’argument qui est passée et non pas l’adresse
et comme la portée de cette copie n’est effective qu’à l’intérieur de la procédure il est
normal que cette variable ne soit pas affectée dans la procédure appelante.

Exemple de passage de paramètre à une fonction

Sub essai
Dim i as integer
i=2
Call passageparametre(i) OU passageparametre i
Msgbox i
End sub

Sub passageparametre( ByVal u as integer)

u=u+3
end sub

Si vous exécutez l’exemple ci-dessus, la valeur retournée par msgbox sera 2, alors que si
vous omettez l’instruction ByVal elle sera de 5.
Le mode Optional signifie que cette valeur lors de l’appel de la procédure peut-être omis,
mais il doit être situé en dernière position dans la liste des arguments. Cela permet de
surcharger une procédure. L’appel de cette procédure se fait grâce à l’emploi du mot clé
Call et la liste des arguments doit être mis en parenthèse OU Call peut être omis mais dans
ce cas les arguments ne doivent pas être mis entre parenthèses
Les fonctions Sub ne retournent pas de valeur, ni d’objet elles sont faites pour
exécuter une tâche.

1.2 Les procédures Functions

Ces procédures sont une série d’instructions exécutant une tâche déterminée au sein du
projet et renvoyant une valeur ou un objet qui sera ensuite exploité au sein du programme.
Elle est structurée de la façon suivante

Déclaration d’une fonction

Function NomDeFonction( Liste d’arguments) as type
Instructions
NomDeFonction = expression
End function

Dans le cas de passage d’objet par une fonction il faut précéder celle-ci du mot clé SET.
Function() as type permet de spécifier le type de la valeur retournée par la fonction, même
remarque que précédemment pour la liste d’arguments et donc même conséquences. On
pourra aussi l’utiliser comme n’importe quel fonction Excel en faisant
Insertion/functions/Liste personalisée

La fonction ci-dessous aura les mêmes conséquences que la procédure ci-dessus.

Utilisation d’une fonction

Function passageparametre( ByVal u as integer)
u=u+3
passageparametre =u

Modélisation de l’économie et de la Finance Internationales

- 22 -
End Function
Je ne développerai pas les fonctions Property Get,Set et Let dans cette partie car ce sont des
fonctions majoritairement utilisées dans les modules de classe. Pour résumer se sont des
fonctions qui permettent de définir des propriétés en mode de lecture, écriture.et de
retourner la valeur de la propriété en question, c’est ce qu’on appelle l’encapsulation.

2. Les Structures de contrôle VBA

2.1 Les boucles

Les boucles d’instructions servent à améliorer la lisibilité de votre code et à répéter une
série d’instruction. Il en existe plusieurs

La boucle While Condition ….Instructions … .Wend

Cette structure permet de répéter une série d’instruction tant qu’une condition spécifique est
remplie, c’est-à-dire tant que la condition est vraie. Il n’est pas possible de sortir de cette
instruction tant que la condition n’est pas remplie. A noter que dans cette structure de
répétition les instructions peuvent ne pas être effectuées

While Condition… instruction … Next

While Condition
Instructions
Wend

La boucle For .. Instruction Next

Cette structure est utile lorsqu’on connaît le nombre d’éléments de répétition. A noter qu’il
existe une instruction permettant de sortir d’une boucle for prématurément Exit For

For … instruction … Next

For i = 1 to N step Pas
Instructions
Next i

La variable i (appelée variable d’incrément) est à chaque passage dans la boucle augmentée
de Pas. Si l’argument Step Pas est omis la variable est incrémentée de 1 par défaut.

La boucle Do..Loop
Même chose que pour l’instruction While mais dans ce cas il existe une instruction qui
permet de sortir de cette Loop, il s’agit de Exit Do

Il existe plusieurs déclarations

Instructions Do ..Loop pouvant ne pas être effectuées

Do until condition Do while Condition
Instructions… Instructions
Loop Loop

Modélisation de l’économie et de la Finance Internationales

- 23 -
Instructions Do….Loop effectuées au moins une fois
Do Do
Instructions…. Instructions…
Loop while condition Loop until Condition

La boucle For Each element In Collection .. Instructions … Next

Cette structure permet de se déplacer à l’intérieur d’une collection
Le code ci-dessous permet d’appliquer une police de couleur rouge à l’ensemble des
cellules composant la sélection

Exemple
Dim MyRange as range

[celluleType].CurrentRegion.Select
For each MyRange in Selection
MyRange.Font.ColorIndex=3
Next

2.2 Les boucles Conditionnelles

La boucle If… Then …Else

Cette structure permet de spécifier différentes options d’exécution dans une procédure en
fonction de l’état des variables ou de l’application

Exemple
If Condition Then
Série d’instructions1
Else
Série d’instructions2
End if

Les boucles conditionnelles indéterminées

If ….Then….elseif then….end if

Exemple
If Condition1 then
Instruction1
Elseif condition2 then
Instruction2
Else
Instruction3
End if

Modélisation de l’économie et de la Finance Internationales

- 24 -
L’instruction else dans ce cas ne porte pas de condition car c’est instruction3 qui agira
comme instruction de défaut.

Select Case
Cette structure permet d’envisager différentes valeurs pour une même expression et de
spécifier les instructions selon chaque cas envisagé.

Exemple d’instruction Select Case

Select Case Expression
Case valeur1
Instruction1
Case valeur2
Instruction2
Case valeur3
Instrcution3
Case else
Instruction4
End Select

Modélisation de l’économie et de la Finance Internationales

- 25 -
 Chapitre 5 Les Tableaux croisés dynamique : Les pivot
Tables
Lorsque vous avez des tâches à effectuer il est préférable de travailler directement avec les
outils Excel plutôt que de faire des choses bricolées dans son coin avec des boucles
d’instructions dans tous les sens. C’est le cas lorsque vous avez beaucoup de données à
manipuler en un minimum de temps, les pivot tables en terme d’organisation et de rapidité
sont très performantes mais le souci nous le verrons c’est que les pivot tables ne sont pas
exempts de bugs et de contre sens.

1. Première approche

Si vous essayez de passer par l’activateur de macro automatique, voici ce que donnera le
code généré par Excel, ce code n’est pas très efficient mais il permet au moins de visualiser
les objets utiles pour l’utilisation d’une pivot table (PivotFields, AddDataFields,
CurrentPage,etc…). L’ennui c’est que l’on ne peut pas créer ce tableau croisé dynamique
facilement en tant qu’objet lui-même et il n’y a pas de variables déclarées.

Macro vue par le générateur de code automatique

Sub PremiereMacro()

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Datas!R1C1:R1408C20").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Portfolios")
.Orientation = xlRowField
.position = 1
End With
ActiveWindow.SmallScroll Down:=-27
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Nominal Activity")
.Orientation = xlPageField
.position = 1
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Center")
.Orientation = xlPageField
.position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Delta"), "Somme de Delta" _
, xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Center"). _
CurrentPage = "KM"

End Sub

Modélisation de l’économie et de la Finance Internationales

- 26 -
Voici le résultat de la macro du code ci-dessus

PivotCaches (Méthode des workbooks)

Cet objet est une collection qui représente le cache de mémoire des rapports de tableau
croisé dynamique d'un classeur. Chaque cache de mémoire est représenté par un objet
PivotCache. Personnellement je n’utilise pas cette méthode pour créer des tableaux croisés
dynamique car ensuite il faut savoir manipuler cette donnée dans le cache de mémoire si on
veut modifier des paramètres.

PivotTableWizard (Méthode des feuilles de calculs)

Elle doit recevoir plusieurs paramètres
SourceType Argument facultatif cela représente la source des données du rapport, il en
existe de plusieurs types :

xlConsolidation. Plusieurs plages de consolidation

xlDatabase. Liste ou base de données Microsoft Excel
xlExternal. Données d'une autre application
xlPivotTable. Même source qu'un autre tableau croisé dynamique

SourceData : argument de type variant facultatif Il peut s'agir d'un objet Range, d'un
tableau de plages ou d'une constante de texte représentant le nom d'un autre rapport. Pour
une base de données externe, SourceData est un tableau de chaînes contenant la chaîne de
requête SQL.

TableDestination : objet de type variant facultatif, s’il n’est pas spécifié celui-ci sera la
cellule active.

TableName : Facultatif, elle indique le nom du rapport croisé dynamique.

Dans l’exemple ci-dessous, DataSheet est une worksheet, ZoneTable est un objet Range
(ensemble de cellules spécifiées) et Mytable sera le nom de mon tableau

Exemple de Tableau croisé dynamique

DatasSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=ZoneTable,
TableDestination:=tempsheet.Range("A3"), TableName:="MyTable"

Modélisation de l’économie et de la Finance Internationales

- 27 -
PivotTable
Cet objet représente un rapport de tableau croisé dynamique dans une feuille de calcul, il
est membre de la collection PivotTables

Pour associer le nom choisi Mytable à une pivot table on doit définir la syntaxe ci-dessous :

Déclaration de Tableau croisé dynamique

Dim Mytable as PivotTable
Set Mytable = tempsheet.PivotTables(« MyTable »)

Car MyTable est une pivotTable appartenant à la collection PivotTables de la sheet

TempSheet.
Et ensuite pour retrouver toutes les propriétés d’une pivotTable, ou d’un objet en général on
encadre le nom de l’objet avec les mots clés With … et End with
With Mytable
Instructions…
End with

2. Méthodes et propriétés des pivotTables

Pour construire une pivot Table, il faut bien sûr que la zone qui a servi de point de départ
possède des noms d’en-tête de colonne. Une fois la pivotTable définie il faut bien sûr lui
donner des attributs avec l’aide de quelques propriétés et méthodes.

PivotFields(nameChamp)
Cette méthode renvoie un objet qui représente soit un seul champ de tableau croisé
dynamique, soit une collection de champ visibles et masqués. Cela permet en quelque sorte
de spécifier au préalable le champ sur lequel nous allons travailler.

.Orientation=xlPivotFieldOrientation
Cela rajoute le champ NameChamp à la pivotTable à l’endroit spécifié, il en existe
quatre types principaux.
xlPageField : rajout en en-tête de Pivot
xlRowField : rajout en ligne
xlColonneField : rajout en colonne
xlDataField : rajout dans le zone Data

AddFields RowFields :=Array(«Name1 », »Name2 »), ColumnFields:=Array(“C1”,”C2”),

PageFields :=Array(“P1”,”P2”)
Cette méthode permet de structurer toute la pivotTable sur une seule ligne d’instruction

AddDataField (PivotFields(NomChamp), Caption(=titre), fonction associée à ce champ)

Cette méthode permet d’associer à la PivotTable un champ dans la zone Data. Il faut lui
définir un champ, un titre associé et une fonction qui sera appliquée aux données : Sum,
Max, min etc…

Modélisation de l’économie et de la Finance Internationales

- 28 -
Mais nous pouvons aussi écrire :

DataPivotField.Calculation = xlPivotFieldCalculation , ce champ peut prendre toutes les

valeurs référencées ci-dessous :

xlDifferenceFrom
xlIndex
xlNoAdditionalCalculation
xlPercentDifferenceFrom
xlPercentOf
xlPercentOfColumn
xlPercentOfRow
xlPercentOfTotal
xlRunningTotal

Il est important de noter que ces propriétés ne sont valides que pour des champs de
données (Zone Data)

Pour naviguer dans ces champs, il faut définir maintenant des objets PivotItem avec la
syntaxe suivante :
Dim MyPvtItem as PivotItem

Si l’on veut naviguer par exemple dans le champ ChampName il faut utiliser :

Syntaxe utilisée
For each MyPvtItem in .PivotFields(ChampName).PivotItems
Instructions
Next MyPvtItem

Nous pouvons aussi utiliser une autre méthode qui consiste à se servir des propriétés
RowRange, ColumnRange, PageRange
Ces propriétés permettent de naviguer respectivement dans les lignes, les colonnes et les
en-têtes de page du tableau croisé dynamique. Elles possèdent toutes des propriétés propres
comme la fonction Count qui donne le nombre de ligne, de colonne et d’en-tête.

ATTENTION les propriétés VisibleItems et HiddenItems sur les pivotFields ne semblent

pas fonctionner, lorsqu’on les utilise elles nous retournent les mêmes valeurs que pour la
propriété PivotItems.

ColumnGrand
Cette propriété a la valeur True si le rapport de tableau croisé dynamique affiche des totaux
généraux de colonnes, le type de donnée est un boolean (true, false)

RowGrand
Cette propriété a la valeur True si le rapport de tableau croisé dynamique affiche des totaux
généraux de lignes, le type de donnée est un boolean (true, false)

Modélisation de l’économie et de la Finance Internationales

- 29 -
PivotSelect « NomChamp », xlLabelOnly
Cette méthode sélectionne une partie d'un rapport de tableau croisé dynamique

GetData Et GetPivotData
Ces deux méthodes permettent de rapatrier des données dans la pivot Table par rapport à
des critères de sélections choisis. La syntaxe est la suivante :
GetPivotData(Caption,, « champ1 », donnée1, « champ2 »,donnée2)

ASTUCE
Si vous ne voulez pas avoir des sous totaux de ligne dans votre tableau croisé dynamique, il
faut pour chaque Champ mettre dans la propriété SubTotals 12 fois false . Le code pourrait
être le suivant :

Astuce
With MyTable
.PivotFields("Champ1").Subtotals = Array(False, False, False, False, False, False,
False, False, False, False, False, False)
End With

3 Les Graphiques (Charts) et les Objets Graphiques

(ChartsObjects)

Les objets Charts sont des feuilles graphiques (alors que les WorkSheets sont des feuilles
de calculs Excel). Pour ajouter une feuille graphique à votre classeur actif, il suffit comme
pour le cas des feuilles de calcul de se servir de la méthode Add sur l’objet Workbook.

Rajout d’un graphique

Activeworkbook.Charts.Add

Alors qu’un objet ChartObjects est aussi un graphique mais il est incorporé dans une
feuille de calcul (il appartient donc à l’objet Worksheet). Nous allons voir que nous
pouvons passer de l’un à l’autre par l’usage d’une méthode. Les objets Charts sont définis
principalement par l’objet SériesCollection qui contient toutes les Séries du graphique et les
Séries sont elles-mêmes définies par la collection Points .

3.1 La méthode principale:

La méthode ci-dessous permet de fabriquer de toute pièce un graphique, elle est issue d’un
objet Chart. Par cette méthode on ne maîtrise pas les SériesCollection.

.ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels,

HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)

Source : Elle spécifie la plage de donnée nécessaire à l’élaboration du graphique. Il vaut

mieux mettre au préalable cette donnée dans une variable Range. A noter que l’on peut
aussi définir la plage de donnée source du graphique par la méthode SetSourceData

Modélisation de l’économie et de la Finance Internationales

- 30 -
Gallery définit le style de graphe que l’on souhaite.(xlChartType)
PlotBy : Permet de spécifier si les données à prendre en compte sont en lignes ou en
colonnes xlRows ou xlColumns

Title : Titre du graphique

CategoryTitle : Titre de l’axe des abscisses
ValueTitle : Titre de l’axe des ordonnées
ExtraTitle : Titre de l’axe des séries pour les graphiques 3D

SetSourceData(Source, PlotBy)
Voir la définition précédente.

Location(Where, Name)
Permet de passer d’un objet ChartsObject à un objet Charts et vice-versa.

Série et SériesCollection
C’est l’objet principal des graphes. La collection SeriesCollection représente toutes les
séries du graphique. On peut donc ajouter une série à l’aide de la méthode Add.

NewSeries
Cette méthode permet de rajouter une série à la Collection SériesCollection. A mon sens
cette méthode est à privilégier par rapport aux autres.

Extend (Source, RowCol :=xlrows/xlcolumns, CategoryLabel :=true/False)

Cette méthode permet de rajouter des points à la SériesCollection
RowCol spécifie si les nouvelles valeurs se situent dans les lignes ou dans les colonnes de
la plage source indiquée.
CategoryLabel = True signifie que la première ligne ou colonne contient le nom des
étiquettes de catégorie

Modélisation de l’économie et de la Finance Internationales

- 31 -
 Chapitre 6 : Les Feuilles VBA ou UserForms

Les feuilles VBA sont un point de programmation à na pas négliger car elles donnent une
allure plus professionnelle à vos développements et permettent une interaction entre
l’utilisateur final et le code. En effet les feuilles sont des zones dans lequel vous pouvez
mettre des activeX tels que des cases à cocher, des zones de texte ou des boutons de
commande. L’utilisation des feuilles VBA s’avère utile dans le cas où l’on souhaite passer
des informations avec un certain formalisme à notre code, par exemple à l’aide d’une
Listbox nous pouvons délimiter le choix des utilisateurs ou par l’intermédiaire de cases à
cocher dont nous maîtrisons les effets.

1 - Présentation de la Fenêtre des UserForms

Nous retrouvons à nouveau l’organisation habituelle, à savoir la fenêtre de projet, de

propriété mais ce qui est nouveau c’est que nous n’avons plus la fenêtre de code mais la
UserForm ainsi que la boite à outil qui accompagne l’initialisation de la userform. Pour
l’instant la fenêtre ci-dessous est vide, nous devons lui donner un nom physique (propriété
Name) qui sera repris par la suite dans le code pour faire référence à celle-ci et un nom
usuel qui est la propriété Caption tel que le visualiserons les utilisateurs.

Modélisation de l’économie et de la Finance Internationales

- 32 -
 2 .La Boite à outils

Outil Sélection Contrôle Label

Contrôle TextBox Contrôle ComboBox

Contrôle ListBox Contrôle CheckBox

Contrôle OptionButton Contrôle ToggleButton

Contrôle Frame Contrôle Commande Button

Contrôle TabStrip Contrôle MultiPage

Contrôle ScrollBar Contrôle SpinButton

Contrôle Picture Contrôle RefEdit

Je ne vais pas énumérer tous les types ci-dessus, seulement les plus usuels

2.1 Le contrôle Label (intitulé)

Il permet de placer un intitulé sur la feuille. Il sert généralement à placer une étiquette à
côté d’un contrôle ne possédant pas cet attribut

2.2 Le contrôle TextBox

Ce contrôle permet de placer sur la feuille une zone de texte ou l’utilisateur pourra saisir
des informations, c’est en quelque sorte l’équivalent de la fonction Inputbox .Cette fonction
permet une interaction dynamique avec l’utilisateur, la valeur renvoyée est de type String..

InputBox "Rentrer l'information", "utilsation"

Modélisation de l’économie et de la Finance Internationales

- 33 -
La fonction Msgbox permet de laisser des messages variés aux utilisateurs, la valeur
retournée par les différents boutons est de type Integer.

MsgBox "essai de la fonction msgbox", vbCritical, "Avertissement"

2.3 Contrôle Button

Permet une mise en action de votre userforms.

2.3 Contrôle ListBox

Cette zone de liste permet d’afficher des options parmi lesquelles l’utilisateur peut effectuer
des sélections (les propriétés d’une liste box permet d’effectuer une ou plusieurs sélections)

2.4 Contrôle ComboBox

Ce contrôle est très proche d’une ListBox puisqu’il permet à l’utilisateur de faire un choix
parmi un ensemble de sélection mais il peut aussi rentrer une valeur de son choix.

2.5 Les contrôles Options button

Les contrôles OptionButton permettent de proposer à l’utilisateur un choix parmi plusieurs
options. Lorsque plusieurs contrôles OptionButton sont associés, seul l’un d’entre eux
peut–être activé, l’activation d’un bouton entraîne l’inactivation des autres.

2.6 Les Contrôles frames

Le contrôle Frame permet de placer un cadre présentant un intitulé sur une feuille afin d’y
placer des contrôles. Il permet de faire une distinction harmonieuse entre les catégories de
contrôles

Modélisation de l’économie et de la Finance Internationales

- 34 -
 2.7 Exemple
Les feuilles Excel ne sont que des userform optimisées avec des contrôles et du code
répondant aux tâches de l’utilisateur.

Contrôle TextBox Contrôle MultiPage

Case à cocher

Contrôle Frame

Contrôle Label

Option button

Contrôle Commande
Bouton

3 Utilisation des userforms

3.1 Procédure d’utilisation

Je ne vais donner ici que les principales fonctions nécessaires à l’utilisation des userforms.
C’est un schéma de procédure d’utilisation de ce genre de feuilles personnalisées. Il faut
avant toute chose donner un nom à notre feuille personnalisée (propriété (name) de la
userform par exemple MyFeuillePerso), ensuite il faut placer les contrôles souhaités dans
notre feuille et leur attribuer le code nécessaire à leur utilisation.

Load MaFeuillePerso
Permet une mise en mémoire de la feuille dans notre projet

MaFeuillePerso.Show
Permet de visualiser à l’écran la feuille finie

Modélisation de l’économie et de la Finance Internationales

- 35 -
MaFeuillePerso.Hide
Permet de cacher la feuille à l’utilisateur mais ne libère pas la mémoire allouée à la feuille

Unload MyFeuillePerso
Cette fonction libère la mémoire de l’ordinateur. Une fois cette méthode invoquée il nous
est plus possible de faire apparaître à nouveau cette feuille à moins de la recharger en
mémoire, cette fonction est à invoquer uniquement en fin de procédure.

3.2 Fonction d’une ListBox

De nombreuses fonctions permettent de manipuler les éléments d’une listbox.

.AddItem « Moi » ou NomDeVariable

Cette fonction peut-être utilisée si la propriété RowSource de la ListBox n’est pas affectée à
des données, on y accède avec la syntaxe suivante :
MaFeuille.MaListeBox.AddItem « Moi »

ListCount
Renvoie le nombre d'entrées de liste d'un contrôle. Si aucun élément n'est sélectionné, la
valeur de la propriété ListCount est 0.

ListIndex
La propriété ListIndex contient un index de la ligne sélectionnée dans la liste. Les valeurs
de la propriété ListIndex sont comprises entre –1 et le nombre total de lignes de la liste
moins 1 (c'est-à-dire, ListCount – 1).

RemoveItem
Permet de retirer un membre de la listBox à partir de son ListIdex, si vous avez incrémenté
une variable i qui parcourt l’ensemble des éléments de votre ListBox alors la syntaxe global
est :
MaUserForm.MaListBox.RemoveItem i

RowSource
Permet de spécifier une source dans un fichier Excel mais cette source n’admet pas l’objet
Range, il faut sélectionner la feuille Excel où se trouve les données au préalable et ensuite
lui affecter les valeurs
RowSource = « A1 :A3 »
Ou directement dans la fenêtre Propriété RowSource Feuil1 !a1 :a3

Selected
Renvoie ou définit l'état de sélection des éléments dans un contrôle ListBox
Si vous avez incrémenté une variable i qui parcours l’ensemble des éléments de votre
ListBox alors la syntaxe global est : MaUserForm.MaListBox.Selected (i)

Modélisation de l’économie et de la Finance Internationales

- 36 -
 Chapitre 7 : Les QueryTables et objet ADODB

Dans ce chapitre je ne vais pas référencer toutes les fonctionnalités des objets DAO (Data
Access Object) ou ADODB (ActiveX Data Object) car ce serait trop long mais juste vous
donner des bases afin d’exploiter des données contenues dans des bases ACCESS et de
savoir les exporter sous Excel.
Nous verrons les étapes successives à effectuer afin d’obtenir des informations contenues
dans une table :
- Il faut avant tout établir la référence avec les objets ADO
- Il faut établir une connexion avec la table
- Définir un objet recordset qui va contenir les informations

1 Référence des objets ADO

Nous en avons déjà parlé en préambule de ce cours, lorsque nous souhaitons faire des
développements avec une source externe il faut établir une référence (ou un lien) entre ces
objets et Excel. Celui-ci se fait à travers le champ Outils\Références (ici il s’agit de cocher
Microsoft ActiveX Data Objects 2.8 library ou une version antérieure)

Modélisation de l’économie et de la Finance Internationales

- 37 -
2. Les étapes des requêtes

Maintenant que la référence à été ajoutée nous allons pouvoir avoir accès à toutes les
fonctionnalités des objets ADO et notamment les connexions.

2.1 Déclarations des variables pour la connexion

La déclaration se fait de la manière suivante

Dim Myconnect as ADODB.connection
Set MyConnect = New ADODB.connection

Le mot clé New signifie que nous avons crée un pointeur sur un objet ADODB.connection
et que la place en mémoire lui a été allouée.

Il faut à présent lui définir le chemin de connexion, ici nous allons attaquer la base exemple
d’ACCESS : FPWIND

MyConnect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\Program Files\Microsoft
Office\Office10\1036\FPNWIND.mdb"

Le chemin peut-être mis dans une variable chaîne de caractère et bien sûr être affecté à la
propriété ConnectionString de l’objet ADODB.Connection

Et enfin établissement de la connexion avec la méthode Open

MyConnect.Open

2.2. Construction d’une requête SQL

Une fois la connexion avec la table ACCESS activée il faut établir le dialogue avec elle et
ceci se fait par l’intermédiaire de la requête SQL (Structured Query Language)

Dim MySQL as string

MySQL = « SELECT * FROM Clients WHERE Ville=’Mexico D.F’ »

Explications: La table Clients est composée de différents champs dont Ville. L’étoile
signifie que l’on souhaite faire remonter tous les champs de cette table Clients. Mais on
pourrait très bien ne spécifier que certains champs de la table avec une variable ou une
chaîne de caractères. La déclaration aurait été :
MySQL = »SELECT Champ1, Champ2, Champ3 FROM Clients Ville=’Mexico D.F’ »
Ou
Mavariable = »Champ1, Champ2, Champ3 de type string
MySQL = « SELECT « & Mavariable & « FROM Clients Ville=’Mexico D.F’ »

De plus les éléments sélectionnés doivent se mettre entre simple côte si c’est une constante.
Ville=’Mexico D.F’
Dans le cas d’une variable on doit rajouter des doubles côtes :
Ville=’” & Mavariable & “’

Modélisation de l’économie et de la Finance Internationales

- 38 -
2.3. Construction d’un objet Recordset

Afin de pouvoir stocker les informations renvoyées par la requête nous avons besoin de
déclarer un objet recordset :

Dim Myrs as ADODB.recordset

Set Myrs = New ADODB.recordset

Un recordset est un ensemble d'enregistrements. Chaque entrée de cet ensemble contient les
données relatives à un enregistrement. L'illustration suivant schématique une telle entité

Pour vous déplacer dans cette structure, une notion de curseur vous est fournie. Le
curseur est une entité que vous pouvez, via différentes méthodes, balader sur les
enregistrements. Ces méthodes sont au nombre de cinq : MoveFirst, MovePrevious,
MoveNext, MoveLast et enfin Move (qui déplace le curseur sur un enregistrement bien
particulier). Attention, deux cases du RecordSet sont particulières : la première et la
dernière, respectivement appelées "Begin Of File" et "End Of File". Elles permettent de
savoir si oui, ou non, nous sommes en dehors des éléments retournés par la requête de
sélection.

Ensuite nous devons faire le lien entre la requête et la table avec la fonction OPEN
Myrs.Open MySQL, MyConnect

3. Utilisation des données

Maintenant il nous reste plus qu’à nous balader dans notre recordset qui est un véhicule
contenant toutes les informations dont nous avons besoin, nous y accédons par
l’intermédiaire des champs (Fields). Les champs sont accessibles soient par leur nom soient
par leur numéro. Les champs disponibles sont listés de zéro à Fields.Count-1 (count est
une propriété de Fields)

Syntaxe pour récupérer les noms des champs

For i = 0 To myrs.Fields.Count – 1
MySheet.Cells(1, i + 1) = myrs.Fields(i).Name
Next i

Syntaxe pour récupérer les valeurs des champs

For i = 0 To myrs.Fields.Count - 1
.Cells(irow, i + 1) = myrs.Fields(i).Value
Next i

Modélisation de l’économie et de la Finance Internationales

- 39 -
Evidemment afin d’avoir l’exhaustivité des données il est préférable d’inclure ceci dans une
boucle dont la sortie sera spécifiée si on rencontre l’opérateur EOF (End of File) et de
passer d’un enregistrement à un autre par la méthode MoveNext de l’objet Recordset

4. Fin des objets crées

Lorsque le processus est fini il faut impérativement fermer nos variables objets et les
effacer aussi en mémoire.

MyConnect.Close (fermeture de la connexion)

Set MyConnect = Nothing (effacement en mémoire)

Myrs.close
Set Myrs = Nothing

5. Les propriétés des connections et les transformations de tables

5.1 Les transformations : Insertion et Mise à jour

Vous serez peut-être amenés à vouloir introduire dans votre table des données de manière
dynamique ou de modifier des données pour mettre à jour par exemple l’adresse d’un de
vos clients ou son changement de téléphone

5.1.1 La fonction Insertion

Il y a deux façons d’insérer des données dans une table, soit avec la méthode AddNew à
déconseiller car si vous avez une table qui contient plusieurs millions ou quelques milliers
de records celle-ci sera très lente mais la syntaxe est la suivante :

Syntaxe d’insertion
With Myrs
.AddNew
for i =0 to .Fields.Count-1
.Fields(i) = MaValeur
Next i
End With

Sinon une méthode plus élégante et beaucoup plus rapide consiste à utiliser la méthode
INSERT
MySQL = "INSERT INTO Clients (Champ1, Champ2, Champ3,…, ChampN) VALUES
(""Valeur1"",""Valeur2",""Valeur3"",…,""ValeurN"")"

Ensuite la prise en compte de cette requête se fait par l’intermédiaire de la fonction Exécute
de la connexion

MyConnect.Execute MySQL, recordsetAffected

Modélisation de l’économie et de la Finance Internationales

- 40 -
La requête INSERT ... SELECT permet de rapidement insérer dans une table un grand
nombre de lignes d'une ou plusieurs autres tables
INSERT INTO TABLE2 (champ1, Champ2, .., ChampN) SELECT * FROM TABLE1
WHERE Conditions.

5.1.2 La fonction Update (Mise à jour)

Lorsque vous souhaitez mettre à jour un champ de la table seulement vous devez utiliser
soit la méthode de l’objet Recordset Update soit la méthode Exécute de la connexion
explicitée ci-dessous

MySQL ="UPDATE Clients SET Champ1 = "NouvelleValue1",

Champ2="NouvelleValue2" WHERE Conditions

Si on ne spécifie pas les conditions alors tous les enregistrements de la table subiront cette
mise à jour.

5.2 Les propriétés de la connexion

Si vous effectuez des changements dans votre table, notamment avec les méthodes insert et
updates (mise à jour) vous devez valider cette transformation car sinon celle-ci ne sera pas
prise en compte dans la table. La validation se fait par l’intermédiaire de la méthode
CommitTrans de votre objet Connexion

MyConnect.CommitTrans

Si on contraire vous souhaitez annuler la transformation parce que par exemple vous n’êtes
pas satisfait du retour de votre macro ou si vous avez détecté une erreur, vous pouvez le
faire par la méthode RollBackTrans

MyConnect.RollBackTrans

6. Les QueryTables

Les Querytables sont des requêtes pour importer des données externes (Web, base de
données, etc..) vers Excel. Il me semble que cette méthode est un peu plus rapide que la
précédente mais je ne l’ai pas expertisée sur des bases de données importantes, elle répond
à une syntaxe bien particulière :
Vous devez au préalable avoir déclarer un objet ADODB.recordset, et bien évidemment
une instruction SQL pour la requête. Ensuite avec la connexion que vous avez déclarée
vous devez exécuter votre requête

With MyConnect
MyConnect.ConnectionString = " Chaîne de connexion"
MyConnect.Open
Set MyRecordset = .Execute(MySQL)
End with

Modélisation de l’économie et de la Finance Internationales

- 41 -
Ensuite vous devez déclarer une variable de type QueryTable et utiliser la méthode Add de
la collection QueryTables, celle-ci renvoyant un objet QueryTable.

Dim MyQueryT as QueryTable

Set MyQueryT = MySheet.QueryTables.Add(MyRecordset, DestinationRange)

Où DestinationRange est la Range où vous souhaitez positionner votre QueryTable

Ensuite pour récupérer les données il ne vous reste plus qu’à invoquer la méthode Refresh
de l’objet QueryTable
MyQueryT.Refresh

A noter que le résultat de cette QueryTable peut aussi être la source d’autres QueryTable ou
PivotTable dans ce cas il est préférable de mettre la propriété BackGroundQuery à False
pour passer la main à la procédure qu'une fois toutes les données récupérées dans la feuille
de calcul.

Modélisation de l’économie et de la Finance Internationales

- 42 -