Formation VBA Excel

## Visual Basic for
Applications [Excel]
Olivier Le Goaër
olivier.legoaer@univ-pau.fr

2
|Plan de la formation|
✔Avant-propos
●
Quelques généralités pour partir du bon pied
✔Noyau Visual Basic
●
Programmation avec le langage généraliste Visual Basic
✔Modèle objet d'Excel
●
Exploiter le VB dans le contexte spécifique des objets
du tableur Microsoft Excel©
✔Motifs de code
●
Portions de code récurrents et réutilisables à travers
différents projets VBA Excel

4
| Niveau de la formation |
✔Niveau I
●
Découverte du tableur,
formules simples, TCD
✔Niveau II
●
Formules avancées,
solveur
✔Niveau III
●
Programmation VBA de
macros

5
| Bénéfices du VBA Excel |
✔Gain de productivité
●
Réaliser en quelques secondes ce qui prendrait des
heures à la main
✔Cohérence des données
●
Les mêmes traitements seront appliqués de manière
systématisée sur les données
✔Performance
●
Lorsque vous avez des milliers de lignes et des
formules, il est conseillé d'appliquer les formules sur les
nouvelles données via VBA et les copier en simples
valeurs

6
| Relation entre VB et VBA |
Visual Basic
for Applications (VBA)
Visual Basic
for Applications (VBA)
Visual Basic (VB)Visual Basic (VB)
Langage de
programmation
orienté objet
VB couplé au modèle
objet des
applications
Domaine Bureautique
Domaine CAO
Domaine Statistique

7
| Le Visual Basic |
✔Directement dérivé du BASIC
●
La partie « Visual » de Visual basic – l’éditeur de
fenêtres et les outils d’édition – rend la programmation
plus accessible
✔Contient les bases de la programmation Objet
●
Mais supporte toujours la programmation procédurale à
l'ancienne (GOTO, LABEL)
✔Nécessite un interpréteur pour être éxecuté
●
Déjà présent dans toute version de Windows > 2000
●
Mais peut être compilé en code natif depuis VB 5

8
| Le Visual Basic for Applications |
✔Est un programme VB qui s'exécute
nécessairement dans une application hôte
●
Cette application hôte peut être Excel, Word, Autocad…
✔Vise à automatiser des tâches d'habitude
réalisées manuellement dans l'hôte
✔L'application hôte expose des objets spécifiques
pour être pilotée programmatiquement
●
Objets Microsoft Excel : Classeur, Feuille, Plage...
●
Objets Microsoft Word : Document, Paragraphe...
●
Objets Autocad : Dessin, Calque...

9
| Macros Excel |
✔Macro programming language
●
Langage de script VBA avec accès direct aux
fonctionnalités d'Excel (via une API)
●
Incompatible avec les scripts oBasic de Open Office
Calc qui se basent sur une API différente
✔Macro virus
●
En plus d'Excel, VBA a accès à la plupart des appels
système de Windows
●
Il est donc aisé d'écrire du code malicieux !
●
Politique sécuritaire spécifique aux fichiers .xlsm
(classeurs Excel incluant des macros)

11
| Aspects lexicaux |
✔Séparateurs d'instructions
✔Commentaires
✔Césures
'Ceci est un commentaire en VB, totalement ignoré lors de l'exécution
CeciEstUneInstruction _
BeaucoupTropLongueAEcrireSurUneSeuleLigne
...
CeciEstUneInstruction
CeciEstUneAutreInstruction : SuiviDuneAutreSurLaMemeLigne
EtEnVoiciEncoreUneAutre
...
CoucouJeSuisLaDerniereInstruction

12
| Aspects lexicaux |
✔Blocs d'instructions
✔Les blocs s'imbriquent entre eux
✔Quelques blocs usuels (Voir suite des diapos)
● Sub...End sub, Function...End function,
If...End if, While...Wend, Do Until...Loop,
With...End With, For...Next, Select...End
Select, Try...Catch...finally
UnTruc 'Marqueur du début d'un bloc
...
... 'On indente (tabulation) le code interne au bloc
... 'pour une meilleure lisibilité
...
End UnTruc 'Marqueur de fin du bloc

13
| Système de types |
✔La déclaration des variables peut être rendue
obligatoire avec en préambule (conseillé)
✔Types de bases (ou primitifs)
●
Nombres entiers : Byte, Integer, Long, LongLong, LongPtr
●
Nombres décimaux : Single, Double, Decimal, Currency
●
Valeurs booléennes : Boolean
●
Chaînes de caractères : String
●
Dates : Date
●
Union de tous les types : Variant
Option Explicit

14
| Déclarer variables et constantes |
✔Une variable sert à mémoriser une valeur qui
peut changer au cours de l'exécution
✔Une constante sert à mémoriser une valeur
figée pour le reste de l'exécution
Dim nb_siege as Integer 'Déclaration en tant que nombre entier
nb_siege = 15 'Affectation de la valeur initiale
nb_siege = 89 'Nouvelle valeur (écrase la précédente)
Const PI as Double = 3.14159265359 'Affectation directe de la valeur
Const TVA as Double = 19.60
Const SIEGE_RESERVE as Byte = 2
nb_siege = nb_siege + SIEGE_RESERVE
TVA = 7.70 Impossible car constante !

15
| Quelques conventions |
✔Il est interdit d'utiliser des espaces ou des
caractères spéciaux dans les noms
✔Pour la lisibilité, préférez le caractère _ ou bien
l'écriture en « Camel Case »
✔Mais VB est en réalité insensible à la casse
Dim accèlération du vehicule as Double Faux !
Dim acceleration_du_vehicule as Double
Dim accelerationDuVehicule as Double
Dim prix as Double
Prix = 456.56
pRiX = PRIX * prix – priX + Prix 'Se réfèrent tous à la même variable

16
| Modes Lecture/Ecriture |
✔La position d'une variable par rapport à
l'opérateur d'affectation est primordial
●
A gauche de celui-ci : la variable est en mode écriture;
elle se réfère au contenant
●
A droite de celui-ci : la variable est en mode lecture;
elle se réfère au contenu
Const nb_siege_total as Integer = 450
Dim nb_siege_occupe as Integer
nb_siege_occupe = 45
nb_siege_occupe = nb_siege_total
nb_siege_occupe = nb_siege_occupe + 10
nb_siege_occupe = nb_siege_total _
* nb_siege_occupe ^ 0
Dites, pour chaque
variable ou constante,
si elle est utilisée
en lecture ou
bien en écriture

17
| Types et opérateurs |
✔La déclaration vous contraint uniquement aux
opérateurs qui se rapportent au type choisi
●
Pour les nombres (entiers ou décimaux) : +, -, /, *, ^
●
Pour les booléens : not, or, and, xor
●
Pour les chaînes de caractères : &
✔Les fonctions obéissent au même principe...
Dim X as Double
Dim Y as Boolean
Dim Z as String
X = (5 + 6) * -1
Y = not (True and False)
Z = "Le silence " & "des agneaux"

18
|Structure de contrôles |
✔Le flux d'exécution par défaut d'un programme
est séquentiel (instruction après instruction)
✔Mais il peut être contrôlé grâce à des structures
spéciales
●
Condition : If expression Then … Else … End If
●
Boucles : While expression … Wend
Do … Loop While expression
Do until expression … Loop
●
Itération : For valDebut To valFin … Next
✔Ces structures forment des blocs

19
| Exemples... |
Dim jour, Chomage, Emploi as Integer
Dim hollandePresident, Insurrection as Boolean
HollandePresident = true
Insurrection = false
While (HollandePresident and not Insurrection)
For jour = 1 To 365 Step 1
If (Chomage > Emploi) Then
Insurrection = true
End If
Next
Wend
✔La logique booléenne est au coeur de ces
structures de contrôle
●
Savoir si oui ou non, un bloc doit être exécuté
Repérez les expressions
logiques présentes dans
les structures de
contrôles ci-contre

20
| Type tableau |
✔Un type supplémentaire est disponible,
construit à partir des autres types : le tableau
✔Apparition d'une structure de contrôle dédiée
Dim t_prix(1 to 10) as Double 'indice numérique allant de 1 à 10
t_prix(1) = 78.863
t_prix(2) = 45.456 + t_prix(1)
Dim i as Byte
Dim total as Double
Total = 0
For i=1 to 10 step 1
Total = total + t_prix(i)
Next i
Dim p as Double
Dim total as Double
Total = 0
For each p in t_prix
Total = total + p
Next value
Version classique avec For Version avec For Each
⇔

21
| Sous-programmes |
✔Un programme est scindé en sous-parties : les
sous-programmes
●
Appliquer l'adage « diviser pour mieux régner »
●
Ceci fait naître une hiérarchie de programmes ayant
alternativement les rôles appelant/appelé
●
Le programme dit "principal" n'a donc pas d'appelant
✔Deux types de sous-programmes existent
●
Les fonctions : ces sous-programmes renvoient un
résultat au programme qui les a appelé
●
Les procédures : ces sous-programmes ne renvoient
aucun résultat au programme qui les a appelé

22
| Hierarchie d'appels |
Programme
(principal)
Sous-Programme
#1
Sous-Programme
#2
Sous-Programme
#1.1
Sous-Programme
#2.1
Sous-Programme
#2.2
Sous-Programme
#2.3
appelant appelant
appelant
appelant
appelant appelant
appelé appelé
appelé appelé appelé appelé
appelé

23
| Fonctions versus procédures |
Procedure
Fonction
#1
Procedure
#2
Fonction
#1.1
Procedure
#2.1
Procedure
#2.2
Fonction
#2.3
appelant appelant
appelant
appelant
appelant appelant
appelé appelé
Resultat Retour
Resultat Retour
Resultat Retour
appelé

24
|Définition de sous-programmes |
✔Définition d'une fonction
✔Définition d'une procédure
Function pumpItUp() as Boolean
...
...
pumpItUp = uneValeurBooleenne 'Mécanisme de retour (en tout dernier)
End Function
Sub pumpItUp()
...
...
...
End Sub

25
| Portée des variables |
✔Les variables déclarées au sein des sous-
programmes sont dites « locales »
●
Elles n'ont pas d'existence en dehors de ceux-ci
✔Comment alors les sous-programmes peuvent
s'échanger des données entre eux ?
●
Via des valeurs de retour ou via des paramètres...
Function f1() as Boolean
Dim exemple as Integer 'Portée locale à f1()
...
End Function
Sub p1()
exemple = exemple * 2 Impossible depuis p1() !
End Sub

26
| Sous-programmes paramétrés |
✔Un sous-programme peut nécessiter 0 ou
plusieurs valeurs pour fonctionner
●
On défini alors des paramètres qui possèdent chacun
un nom et un type
Function f1() as Integer '0 paramètre
...
End Function
Sub p1(isReady as Boolean) '1 paramètre
...
End Sub
Sub p2(msg1 as String, msg2 as String) '2 paramètres
...
End Sub

27
|Exemples... |
✔Version à 1 paramètre
✔Version à 2 paramètres
Pour chaque version
repérez les variables
ou constantes locales,
et les paramètres
Function calculerPrix(prixHT as Double) as Double
Const TVA as Double = 19.60
Dim prixTTC as Double
prixTTC = prixHT + prixHT * TVA/100
calculerPrix = prixTTC
End Function
Function calculerPrix(prixHT as Double, TVA as Double) as Double
End Function

28
| Paramètres optionnels |
✔On peut considérer certains paramètres comme
pouvant être omis, placés en derniers
✔Il est alors nécessaire de leur choisir une valeur
littérale par défaut en cas d'ommission
Sub pumpItUp(a as Integer, b as Byte, Optional c as String)
...
... 'attention, c peut ne pas avoir de valeur !
...
End Sub
Sub pumpItUp(a as Integer, b as Byte, Optional c as String = "Olivier")
...
...
...
End Sub

29
|Exemple... |
✔Version à 2 paramètres, dont 1 est optionnel
●
Si l'on omet d'indiquer l'argument TVA, le calcul se fera
par défaut avec une TVA à 19.6% par défaut
Function calculerPrix(prixHT as Double, Optional TVA as Double = 19.60) as Double
End Function

30
| Appel de sous-programmes |
✔La finalité de tout sous-programme est d'être
appelé par un programme appelant
●
Cas de l'appel d'une fonction
●
Cas de l'appel d'une procedure : pas de parenthèses *
Dim test as Boolean
...
test = f1()
...
'test contient uneValeurBooleenne
Function f1() as Boolean
...
...
f1 = uneValeurBooleenne
End Function
...
p1
...
Sub p1()
...
...
End Sub
* Mais il existe une variante avec le mot clé Call où les paranthèses sont nécessaires

31
| Passage de paramètres |
✔Le programme appelant doit passer des valeurs
aux paramètres des sous-programmes
●
Il s'agit d'une liaison d'arguments avec les paramètres
correspondants, dans le respect du typage
●
x et False sont des arguments
●
val et ok sont des paramètres
Dim x as Byte
x = 23
p1 x, False
Sub p1(val as Byte, ok as Boolean)
...
...
End Sub
programme appelant programme appelé

32
| Passage par nom ou par position|
✔Passage de paramètres par position
●
Les arguments sont liés implicitement dans l'ordre des
paramètres (source d'ambiguïtés quand optionnels)
✔Passage de paramètres par nommage
●
La liaison des arguments avec les paramètres sont
explicités avec :=
x = 23
p1 x, False
...
End Sub
1 2
1 2
x = 23
p1 ok:=False, val:=x
'l'ordre n'importe plus ici
...
End Sub

33
| Passage par valeur ou référence |
✔Passage de paramètres par valeur
●
Le sous-programme ne peut pas agir sur la valeur de
l'argument du programme appelant
✔Passage de paramètres par référence
●
Le sous-programme peut agir sur la valeur de
l'argument du programme appelant (par défaut)
x = 23
p1 x, False
'x vaut toujours 23
Sub p1(ByVal val as Byte, ok as Boolean)
val = val * 4
End Sub
x = 23
p1 x, False
'x vaut désormais 92
Sub p1(ByRef val as Byte, ok as Boolean)
val = val * 4
End Sub

34
| Gestion des erreurs |
✔Il est possible gérer le cas où l'exécution du
code plante
Public Sub calculDeLaMortQuiTue(ByVal diviseur As Integer)
Dim resultat as Double
On Error GoTo casDesespere
' Insérer ici le code susceptible de planter
resultat = 143 / diviseur ' Et si diviseur vaut 0 ? hein ???
Exit Sub
casDesespere:
' Insérer le code à réaliser en cas d'erreur
Msgbox "T'as l'air malin maintenant ! Pffff....."
Resume Next
End Sub

35
| Programmation objet |
✔Une classe d'objet est une structure regroupant
deux catégories de membres
●
Les propriétés : valeurs attachées aux objets (données)
●
Les méthodes : fonctions ou procédures attachés aux
objets (traitements)
'3 propriétés (les données)
nom as String
prenom as String
age as Byte
'1 méthode (le traitement)
Sub sayHello()
MsgBox "Je suis" & Me.nom & " " & Me.prenom
End Sub
Classe « Client »
Auto-référence avec
le mot clé « Me »

36
| Visibilité des membres |
✔La visibilité de chaque membre peut être :
●
Publique (par défaut) : propriété ou méthode de l'objet
utilisable depuis n'importe où dans le programme
●
Privé : propriété ou méthode de l'objet utilisable
uniquement au sein de la même structure
'3 propriétés + visibilité
Public nom as String
Public prenom as String
Private age as Byte
'1 méthode + visibilité
public Sub sayHello()
MsgBox "Je suis" & Me.nom & " " & Me.prenom
End Sub
Classe « Client »

37
| Accesseurs et mutateurs |
✔Le VB gère explicitement les méthodes dites «
accesseurs » et « mutateurs »
●
Accesseur : fonction destinée exclusivement à récupérer
la valeur d'une propriété (mode lecture)
●
Mutateur : procédure destinée exclusivement à affecter
une valeur à une propriété (mode écriture)
Private nom as String
Public Property Get Nom() As String
Nom = UCase(Me.nom)
End Property
Public Property Let Nom(n as String)
Me.nom = n
End Property
Classe « Client »

38
| Création et manipulation d'objet |
✔Déclaration d'une variable typée par une classe
✔Affectation d'un nouvel objet à la variable
✔L'accès aux membres de l'objet utilise une
notation pointée
Dim c1 as Client 'Type non-primitif (=>Classe)
Set c1 = new Client 'Affectation d'un objet à une variable
c1.nom = "Le Goaër" 'écriture d'une propriété de l'objet
c1.prenom = "Olivier" 'écriture d'une propriété de l'objet
c1.age = 32 impossible car visibilité privée !
c1.sayHello 'appel de la procédure de l'objet
...

39
| Références sur objet |
✔Une variable typée par une classe possède une
référence à un objet en mémoire
✔Cas de la référence nulle
Dim c5 as Client 'déclaration mais manque l'affectation
If c5 is Nothing Then
...
End If
Dim c1,c2,c3,c4 as Client
Set c1 = new Client
Set c2 = c1 'recopie d'une référence
Set c3 = c2
Set c4 = c1
objet
Client
c1
c2
c3
c4
c5

40
| Accès en cascade |
✔L'accès aux membres d'un même objet peut
avoir une syntaxe simplifiée
✔Très utile pour initialiser rapidement les
propriétés d'un objet notamment
Dim c1 as Client
Set c1 = new Client
c1.nom = "Le Goaër"
c1.prenom = "Olivier"
c1.sayHello
Dim c1 as Client
Set c1 = new Client
With c1
.nom = "Le Goaër"
.prenom = "Olivier"
.sayHello
End With
⇔

41
| Collection d'objets|
✔Un objet collection est un objet spécial qui sert
à contenir de 0 à n autres objets
✔Objet itérable à l'aide de For Each...Next
count as Long
item() as Truc
<<Collection>>
DesSuperTrucs
Function add() as Truc
Sub remove()
Sub clear()
Indique le nombre d'objets présents
dans la collection
Permet de récupérer un objet de la collection
(accès par index numérique*)
Vide la collection
Ajoute un nouvel objet à la collection.
Renvoi la référence à ce dernier
Supprime un objet de la collection
Dim unCertainTruc as Truc
For Each unCertainTruc in maCollec 'objet instance de DesSuperTrucs
unCertainTruc.uneMethodeDeLaClasseTruc()
Next
* ou par nom si la classe Truc possède spécifiquement une propriété name de type String

43
| Outils du développeur |
✔L'onglet développeur est caché par défaut dans
le ruban Excel
●
Réservé à des utilisateurs plus "avertis"
●
Passage du monde de la bureautique à celui du
développement informatique
✔Manipulations pour le faire apparaître
●
Version 2007 et + : menu > options Excel >Standard >
cochez Activez l'onglet Développeur dans le ruban
●
Version 2016 et + : fichier > Options > Personnaliser le
ruban > cochez Développeur

44
| Projet VBA |
✔A chaque fichier Excel est associé un projet VBA
●
Contiendra le code des macros
●
Savoir où positionner son code est primordial !
Code lié au classeur et aux feuilles chargées
(Mot clé « Me » autorisé)
Code pour créer des formulaires
(Interface graphique utilisateur)
Code indépendant
Classes d'objet personnalisées

45
| Hierarchie d'appels des macros|
Macro
#1
Macro
#2
Macro
#1.1
Macro
#2.1
Macro
#2.2
Macro
#2.3
appelant appelant
appelant
appelant
appelant appelant
appelé appelé
appelé

46
| Lancer une macro|
✔Depuis un projet VBA ✔Depuis le tableur
Fonctionne uniquement pour les procédures,
de surcroît sans paramètres...

47
| Explorateur d'objets de classes |
Moteur de recherche
(classes et
membres de classes)
Modules de classes,
Modules, Enumérations
fournis par VBA Excel
Membres d'une
classe donnée
Propriété
Méthode
Signature du membre Lien de navigation
(vers d'autres classes)
+ Documentation Officielle (en français)
Documentation du
membre (en français)

48
| Signature des membres |
✔Signature des propriétés
✔Signature des méthodes
Liste des paramètres :
● Leur nom
● Leur type (parfois)
● Leur optionnalité avec []
(Cf. Diapos 28 et 32)
Type de la propriété
Utilisation en mode
lecture/écriture
(Cf. Diapo 16)
Hélas, certaines
signatures
sont incomplètes
ou erronées...
Procédure (Sub) ou
fonction (Function)
(Cf. Diapos 24 et 30 )

49
| Bibliothèque de fonctions Excel|
✔La bibliothèque des fonctions Excel est en fait
un module spécial contenant des macros VBA
●
Module WorksheetFunction
●
On utilise le nom anglais standardisé des fonctions
Nom francisé Nom anglais
SOMME() SUM()
MOYENNE AVERAGE()
NB.SI() COUNTIF()
BDMOYENNE() DAVERAGE()
RECHERCHEV() VLOOKUP
NBVAL() COUNTA()
... ...

50
| Enrichir la bibliothèque Excel|
✔Cette bibliothèque peut tout à fait être enrichie
par vos propres fonctions personnalisées
●
Doivent être positionnées dans un Module propre
●
Doivent retourner un type de base et avoir pour
paramètres des types de bases ou la classe Range
●
Leur code doit être écrit de manière 100% générique
●
Nécessitent parfois d'être déclarées "volatiles"
✔Macros automatiquement appelées depuis la
zone de formules du tableau Excel
● E5 = maFonctionCustom(B6:V45; K3) * 2

51
| Fonction personnalisée : exemple|
Convention de nommage
de vos fonctions :
●
Majuscule
●
Pas de chiffre
●
Différent des noms des
fonctions natives

52
| Réutilisation des macros |
✔Problème : le code est lié à un projet VBA et
donc à un classeur donné
●
Or on a souvent besoin des mêmes macros d'un
classeur à l'autre. C'est dommage de devoir les reécrire.
●
Surtout quand on enrichi la bibliothèque de fonctions...
✔Solution : le classeur personnel (Personal.xlsb)
●
Classeur spécialement conçu à cet effet, chargé
automatiquement au démarrage d'Excel
●
Se trouve (normalement) dans le répertoire
C:Users<username>AppDataRoamingMicrosoftEx
celXLSTART

53
|Modèle objet Excel |
✔En VBA Excel, vous disposez d'un ensemble
d'objets déjà instanciés à partir de classes
●
Ces objets sont le strict reflet de ce que vous avez sous
les yeux dans le tableur Excel
✔Principe de synchronisation objet/tableur
●
Chaque action réalisée au niveau des objets a un
impact au niveau du tableur, et vice versa
✔La mauvaise idée de VBA Excel (selon moi)
●
Les objets et les propriétés portent souvent le même
nom que leurs classes d'appartenance -> confus

54
| Métaphore de l'iceberg |
Public name as String
Public path as String
...
Public Sub saveAs(Optional fileName as String, ...)
...
End Sub
...
Classe « WorkBook »
(préalablement définie par un
ingénieur de chez Microsoft)
Dim ThisWorkbook as WorkBook
Set ThisWorkbook = new WorkBook
ThisWorkbook.name = le_nom_du_classeur_chargé
ThisWorkbook.path = le_chemin_du_classeur_chargé
ThisWorkbook.saveAs filename:="Bilan Comptable.xlsx"
Code exécuté au
chargement du fichier Excel
(pour refléter ce que vous avez sous
les yeux : principe de synchronisation)
← Vous

55
| Classes usuelles |
Classe Objet(s)
Application Représente Excel (objet unique/singleton)
WorkBook Représente un classeur ouvert
WorkSheet Représente une feuille de calcul ouverte
Chart Représente un graphique
Control Représente un contrôle de formulaire (Bouton, Case à
cocher, ...)
... ...
Range Représente un ensemble de cellules (une seule,
colonnes entières, lignes entières...)
WorkBooks Représente un ensemble de classeurs
WorkSheets Représente un ensemble de feuilles de calcul
Sheets Représente un ensemble de feuilles (calcul et autres)
Charts Représente un ensemble de graphiques
Controls Représente un ensemble de contrôles de formulaires
Classes dérivées
de la classe
« Collection »

56
| Diagramme de classes (UML) |
name as String
version as String
workbooks as WorkBooks
Application
Sub calculateFull()
count as Long
item() as WorkBook
<<Collection>>
WorkBooks
Function add() as WorkBook
name as String
path as String
workSheets as Sheets
WorkBook
Sub close()
Sub saveAs()
count as Long
item() as WorkSheet
<<Collection>>
Sheets
Function add() as WorkSheet
name as String
index as Long
cells(,) as Range
range() as Range
columns as Range
WorkSheet
Sub activate()
<<Collection>>
Range
cells(,) as Range
rows as Range
row as Long
count as Long
item() as Range
interior as Interior
value as Variant
Sub select()
Les propriétés de
type non-primitif
engendrent la
navigation pointée

57
| Diagramme de classes (UML) |
name as String
version as String
workbooks as WorkBooks
Application
Sub calculateFull()
count as Long
item() as WorkBook
<<Collection>>
WorkBooks
Function add() as WorkBook
name as String
path as String
workSheets as Sheets
WorkBook
Sub close()
Sub saveAs()
count as Long
item() as WorkSheet
<<Collection>>
Sheets
Function add() as WorkSheet
WorkSheet
cells(,) as Range
rows as Range
row as Long
count as Long
item() as Range
interior as Interior
value as Variant
<<Collection>>
Range
Sub select()
...
name as String
index as Long
cells(,) as Range
range() as Range
columns as Range
Sub activate()
Les propriétés de
type non-primitif
engendrent la
navigation pointée

58
| Diagramme d'objets (UML) |
name="Excel"
version=10.7
o1:Application
count=1
o2:WorkBooks
name="Shadok.xlsx"
path="c:/Bureau/"
o3:Workbook
count=4
o4:Sheets
name="Ga"
visible=xlSheetVisible
o5:WorkSheet
name="Bu"
o7:WorkSheet
name="Zo"
o6:WorkSheet
name="Meu"
visible=xlSheetHidden
o8:WorkSheet
.workbooks
.item(1)
.worksheets
.item(1) .item(2) .item(3) .item(4)

59
|Naviguation entre les objets |
'Ecriture dans une propriété d'un objet
Application.workbooks.item(1).worksheets.item(3).Range("B3").value = "yep !"
ApplicationApplication
WorkbooksWorkbooks
(Collection)
SheetsSheets
(Collection)
WorkBookWorkBook
WorkSheetWorkSheetWorkSheetWorkSheet
RangeRange
VariantVariant
'Appel d'une méthode d'un objet (ici une procédure, avec 1 argument)
ThisWorkbook.saveAs filename:="Bilan Comptable.xlsx"
WorkBookWorkBook

60
| Décomposons la navigation |
Dim monExcel as Application
Dim collectionClasseurs as Workbooks
Dim unClasseur as WorkBook
Dim collectionFeuilles as Sheets
Dim uneFeuille as WorkSheet
Dim uneCellule as Range
Set monExcel = Application
Set collectionClasseurs = monExcel.workbooks
Set unClasseur = collectionClasseurs.item(1)
Set collectionFeuilles = unClasseur.worksheets
Set uneFeuille = collectionFeuilles.item(3)
Set uneCellule = uneFeuille.Range("B3")
uneCellule.value = "yep !"
'Ecriture dans une propriété d'une cellule (notation complète et explicite)
Application.workbooks.item(1).worksheets.item(3).Range("B3").value = "yep !"

61
| Effets de la localisation du code |
✔Si votre code est lié aux objets du projet VBA
✔Sinon (Module, Module de Classe, ...)
'Me est l'auto-référénce à l'objet qui contient le code
'On suppose ci-dessous que c'est l'objet Feuil2
Me.name = "Hello"
'Navigation complète depuis l'objet racine Application
'Accès à un classeur/feuille via son index ou via son petit nom
Application.workbooks.item(1).worksheets.item(3).name = "Hello"
Application.workbooks.item("Shadock.xslx").worksheets.item("Zo").name = "Hello"
'Accès direct à l'objet feuille considéré
Feuil2.name = "Hello"
'En deux temps, via l'objet ActiveSheet
Application.workbooks.item(1).worksheets.item(3).activate
ActiveSheet.name = "Hello"

62
| Objets racines|
✔Les objets « racines » sont autant de points de
départ incontournables pour écrire votre code
Objet Classe Remarque
Application Application L'application Excel dans sa globalité
ThisWorkbook Workbook Le classeur auquel le code VBA est lié
ActiveWorkbook Workbook Le classeur actif actuellement
ActiveSheet Worksheet La feuille active actuellement
ActiveChart Chart Le graphique actif actuellement
ActiveCell Range Cellule active (de la feuille active)
Selection Range Plage sélectionnée actuellement
Me Workbook ou Worksheet
ou Form (selon le cas)
Est relatif à l'endroit où vous mettez le
code (Modules exclus)
Feuil1, Feuil2,
Feuil3, ...
Worksheet Incarne chaque feuille présente dans
le classeur auquel le code VBA est lié

63
| Cas des énumérations |
✔Les énumérations sont des modules de classes
un peu particuliers
●
Elles servent à typer (à l'instar des classes)
●
Mais leurs membres sont uniquement des constantes
entières, choisies symboliquement
✔Conventions de nommage
●
Celles liées au noyau VB sont préfixées par « Vb »
– VbYesNo, VbCalendar, VbArchive...
●
Celles liées au tableur Excel sont préfixées par « Xl »
– XlChartType, XlDataTable, XlSheetVisible...

64
| Evènements |
✔Il existe en réalité une troisième catégorie de
membres de classes : les évènements
●
Ce sont des méthodes déclenchées quand quelque-
chose se produit sur l'objet de la classe
✔Exemples d'évènement VBA Excel
●
Liés au tableur Excel
– Un classeur vient d'être ouvert ou bien fermé, une feuille
vient d'être activée, une plage est sélectionnée...
●
Liés aux contrôles de formulaire (Cf. User Form)
– Un bouton vient d'être pressé, une case vient d'être
cochée, un champs est en cours de saisie...

65
| Programmation par evènements|
✔Explorateur de classes ✔Code projet VBA
Le code est nécessairement placé à l'intérieur de ces objets
Choix de l'évènement

66
| Programmation par evènements|
✔La signature de la procédure vous est imposée
●
Car c'est Excel qui va l'appeler le moment venu
●
Car c'est Excel qui décide quoi passer en arguments
'On suppose que le code est à l'intérieur d'un objet feuille (ex: Feuil1)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 8 Then 'Si cellule H3 selectionnée
Target.interior.colorIndex = 6 'mise en couleur de son fond
End If
End Sub
Private Sub Worksheet_Activate()
MsgBox "Bienvenue sur la feuille " & Me.name
End Sub

67
| Outils de débogage |
✔Fenêtre Exécution
●
Affiche n'importe quelle valeur primitive à l'aide de
Debug.print
●
Remplace avantageusement les MsgBox
✔Fenêtre Variables Locales
●
Recense le contenu des variables locales implicites et
explicites (Dim) de votre sous-programme
✔Fenêtre Espions
●
Plus puissant que la fenêtre variables locales. Capable
d'afficher le contenu d'expressions complexes

68
| Débogage : illustration |
✔Espionnage ✔Exécuter la macro en
mode pas-à-pas (F8)

69
| Enregisteur de macros |
✔Outil qui génère automatiquement un code
équivalent à ce que vous faites dans le tableur
●
Pratique quand vous ne savez pas quel code VBA écrire
✔Mais n'est certainement pas la solution à tout...
●
Le code généré contient de nombreux parasites, et va
même à l'encontre des bonnes pratiques
●
L'immense majorité des cas pratiques repose sur des
problèmes d'ordre algorithmique où l'enregisteur n'est
d'aucun secours
●
La tentation d'avoir recours à l'enregisteur n'aide pas à
comprendre, donc à progresser.

70
| Enregistreur : cas d'école n°1 |
Range("A19").Select
Selection.Font.ColorIndex = 3
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.Weight = xlMedium
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With ActiveSheet.range("A19")
.Font.ColorIndex = 3
.HorizontalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
Les performances
et l'intelligibilité
du code sont bien
meilleures ainsi non ?
Code généré par l'enregistreur de macro
Code équivalent écrit par un humain

71
| Enregistreur : cas d'école n°2 |
'colorie une ligne sur 2 jusqu'à la ligne 10
Rows("2:2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("4:4").Select
.Pattern = xlSolid
.Color = 49407
.TintAndShade = 0
End With
Rows("6:6").Select
.Pattern = xlSolid
.Color = 49407
.TintAndShade = 0
End With
Rows("8:8").Select
.Pattern = xlSolid
.Color = 49407
.TintAndShade = 0
End With
Dim iLigne As Byte
For iLigne = 2 To 10 Step 2
With ActiveSheet.Rows.item(iLigne)
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 49407
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
Next
Ceci est un algorithme
(la récurrence de la
tâche est gérée
par une boucle)
Code généré par l'enregistreur de macro
Code équivalent écrit par un humain

72
| Interface graphique utilisateur |
✔Formulaires utilisateur (« User Form »)
●
Sert à constuire des interfaces graphique par dessus le
tableur, mais en complément de ce dernier
✔Introduit un modèle objet additionnel
●
Classes d'objets (propriétés, méthodes, évènements)
spécifiques aux aspects graphiques des interfaces
✔Approche WYSIWYG
●
"What You See Is What You Get" : créez votre interface
sans écrire une seule ligne de code VB
●
Ecrivez uniquement le code associé aux évènements

73
|WYSIWYG par l'exemple|
Ensemble d'objets graphique
instanciés et positionnés à la souris
Propriétés d'un objet (peuvent également être valuées par programmation)
Types d'objets graphique
=
les classes
Propriété
technique

74
| Exemple simple |
name = TextBox1
name = TextBox2
name = UserForm1 name = CommandButton1
'Evènement correspondant au clic sur le bouton
Private Sub CommandButton1_Click()
Const TVA = 19.6
Dim resultat as Double
resultat = TextBox1.Value * (1 + TVA / 100)
TextBox2.Value = resultat
'Sauvegarde du résultat dans une cellule de la feuille active
ActiveSheet.cells(1,1).value = resultat
End Sub

76
| Entrées/sorties standards |
Dim annee as Integer
Dim info as String
Dim choix as Byte
'Méthode de saisie basique (crashe si valeur de type incorrect !)
annee = Interaction.inputBox(prompt:="Marignan ?", title:="Quizz")
'Méthode de saisie avec contrôle du type (exemple: 1 -> nombre)
annee = Application.inputBox(prompt:="Marignan ?", title:="Quizz", type:=1)
info = "Marignan " & annee & "!"
'Méthode d'affichage utilisée en tant que procédure
Interaction.msgBox prompt:=info, buttons:=vbInformation
'Méthode d'affichage utilisée en tant que fonction
choix = Interaction.msgBox(prompt:=info, buttons:=vbYesNo)
'Méthode d'affichage du débogueur (apparaîtra dans la fenêtre d'exécution)
Debug.Print "L'utilisateur a entré " & info

77
| Manipulation de feuille de calcul |
Dim nouveau as Worksheet
'Creation d'une nouvelle feuille dans la collection
Set nouveau = ThisWorkbook.worksheets.add(after:=Me)
'Accès aux propriétés en cascade
With nouveau
.name = "Bilan"
.visible = xlSheetVisible
.cells(1,2).value = "Ventes prévisionnelles"
.enableCalculation = False
End with
'Ramène la feuille en toute première position
nouveau.move before:=ThisWorkbook.worksheets.item(1)
'Lance l'impression de la feuille en 6 exemplaires
nouveau.PrintOut copies:=6

78
| Manipuler les plages |
'Selection d'une plage réduite à une seule cellule
Feuil1.Range("C7").Select 'equivaut à Feuil1.cells(7,3).Select
'Selection d'une plage de cellules contigües
Feuil1.Range("A2:R45").Select
'Selection d'une plage de cellules non-contigües
Feuil1.Range("A2:D25;F4:B9").Select
'Selection de la plage encadrée par les colonnes D à P incluses
Feuil1.Range(Feuil1.columns(4), Feuil1.columns(16)).Select
'Selection de la plage encadrée par les lignes 8 à 153 incluses
Feuil1.Range(Feuil1.rows(8), Feuil1.rows(153)).Select
'Selection de la plage de diagonale C7 à H33
Feuil1.Range(Feuil1.Range("C7"), Feuil1.Range("H33")).Select
'Selection de la plage par décalage d'une plage d'origine
Feuil1.Range("G22:R45").Offset(8,-2).Select 'Soit désormais E30:P53

79
| Fond & forme des plages |
'Valeur interne de la cellule stockée par le tableur (Cf. codage de données)
Feuil1.cells(2,2).value2 = 29930
'Valeur formatée de la cellule (souvent, valeur formatée = valeur interne)
Feuil1.cells(2,2).value = #10/12/1981#
'Séance de maquillage pour notre cellule
With Feuil1.cells(2,2)
.Font.ColorIndex = 3 'couleur tirée de la palette Excel (parmi 56)
.Font.Bold = True
.Interior.Color = RGB(59, 89, 152) 'couleur libre (> 16 millions)
.Borders.Weight = 4
End With
'Remise à zero de la forme et du fond
With Feuil1.cells(2,2)
.clear
.clearContent
.Interior.Pattern = xlPatternNone
End With

80
| Compter les lignes utilisées|
Dim CombienDeLignes as Integer
'usedRange est une plage automatiquement déterminée par le tableur
CombienDeLignes = Feuil1.usedRange.rows.count
'Rechercher « à taton » la dernière cellule vide d'une colonne donnée
Dim iLigne as Integer
iLigne = 1
While not IsEmpty(Feuil1.Cells(iLigne, 1).value)
iLigne = iLigne + 1
Wend
CombienDeLignes = iLigne - 1
'Récupérer le n° de la dernière ligne d'une colonne donnée
CombienDeLignes = Feuil1.columns.item(2).end(xlDown).row
'Réutiliser la fonction tableur NBVAL() sur une colonne donnée
CombienDeLignes = WorksheetFunction.CountA(Feuil1.columns.item("A"))
'Passer par la fonction SpecialCells de la classe Range
CombienDeLignes = Feuil1.Cells.SpecialCells(xlCellTypeLastCell).Row

81
| Copier/coller |
'Copier/collé rudimentaire d'une cellule à une autre
Feuil2.Range("A2").value = Feuil1.Range("B78").value
'Copier/collé de plages de cellules
Feuil1.Range("A2:R45").copy
'...Données mises en zone mémoire tampon (le « presse-papier »)...
Feuil2.paste 'En A2:R45 par défaut
Feuil2.paste destination:=Feuil2.Range("B4:S47") 'Destination explicite
'Version plus immédiate
Feuil1.Range("A2:R45").copy destination:=Feuil2.Range("B4:S47")
'Sur le même principe, copier/coller de colonnes ou de lignes
Feuil1.Columns("E").copy destination:=Feuil2.Columns(4)

82
| Ouverture d'un répertoire |
Dim vaFiles As Variant
Dim i As Long
vaFiles = Application.GetOpenFilename(FileFilter:= _
"Excel Files (*.xls), *.xls", Title:="Ouvrir Fichier(s)", MultiSelect:=True)
If Not IsArray(vaFiles) Then Exit Sub 'Sortie si aucun fichiers
With Application
'Désactive le rafraîchissement de l'affichage (meilleures performances)
.ScreenUpdating = False
For i = 1 To UBound(vaFiles)
.workbooks.Open vaFiles(i) 'Ouverture de chaque classeur
Next i
.ScreenUpdating = True 'Réactive le rafraîchissement de l'affichage
End With

83
| Parcours d'une plage |
Dim iLigne, iColonne as Integer
Dim plage as Range
Dim cellule as Range
Set plage = Feuil1.Range("A2:R45")
'Version classique (risque de poser problème si non contigües)
For iLigne=1 to plage.rows.count
For iColonne=1 to plage.columns.count
plage.cells(iLigne, iColonne).value = "Yeaaaaaah"
Next
Next
'Version par itération sur une collection
For each cellule in plage
cellule.value = "Yeaaaaaah"
Next

84
| Génération d'un graphique |
Dim courbe as Chart
'ajoute une feuille de graphique au classeur (≠ feuille de calcul)
Set courbe = ThisWorkbook.Charts.Add()
With courbe
'graphique type ligne (Voir enumération XlChartType)
.ChartType = xlLineMarkers
'sélection de la source de données. Ici une plage de feuille n°1
.SetSourceData Source:=Feuil1.Range("B10:C56")
.HasTitle = True
.ChartTitle.Text = "Ceci est un graphique"
.SeriesCollection(1).Name = "Nom de la série"
End With
'Déplace la feuille de graphique en dernière position
'Sheets représente la collection des feuilles (de graphique ET de calcul)
ActiveChart.Move after:=ThisWorkbook.Sheets.item(ThisWorkbook.Sheets.Count)

85
| Open Data & Services Web |
Function VILLESELONCODEPOSTAL(CP as String) as String
Dim URL As String
Dim httpRequest As Object
Dim jsonResult As String
Dim lFirstChar, lLastChar As Long
'URL d'un service web gratuit de conversion CP->Ville
URL = "http://www.cp-ville.com/cpcom.php?cpcommune=" & CP
Set httpRequest = CreateObject("MSXML2.XMLHTTP.6.0")
httpRequest.Open "GET", URL, False
httpRequest.Send
jsonResult = httpRequest.responseText
'Parsing JSON très rudimentaire, mais qui marche :-P
lFirstChar = InStr(jsonResult, "{""ville"":") + 10
lLastChar = InStr(lFirstChar, jsonResult, ",") - 1
VilleSelonCodePostal = Mid$(jsonResult, lFirstChar, lLastChar - lFirstChar)
End Function

86
| Barre de menu personnalisée |
Dim my_bar as CommandBar
Dim my_btn as CommandBarButton
Set my_bar = Application.CommandBars.Add(Name:="TURBOMECA SAFRAN")
Set my_btn = my_bar.Controls.Add()
With my_btn
.style = MsoButtonStyle.msoButtonIcon
.caption = "Consolidation"
.faceId = 733 'code icône (http://fring.developpez.com/vba/excel/faceid/)
.onAction = "NameOfASubInYourVBACode" 'nom de la macro à associer
.tooltipText = "Consolidation des données turbines"
End With
With my_bar
.visible = True
.position = MsoBarPosition.msoBarTop
End With

87
| UserForm programmatiquement |
'Creation du UserForm par programmation uniquement
'Technique réservée à ceux qui sont assez fous pour se passer du WYSIWYG
Dim myForm As Object
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)
MyForm.name = "OLG2016"
With myForm
.Properties("Caption") = "Ceci est une jolie fenêtre"
.Properties("Width") = 300
.Properties("Height") = 270
End With
VBA.UserForms.Add(myForm.Name).Show 'affichage

88
| Participez ! |
Vous pensez à un code
suffisamment général et
utile pour être réutilisable
par tous ?
Envoyez-le à
olivier.legoaer@univ-pau.fr

Formation VBA Excel

Contenu connexe

Tendances

En vedette

Similaire à Formation VBA Excel

Plus de Olivier Le Goaër

Dernier

Formation VBA Excel