Formation Excel – VBA débutant

Loading...

Taking too long?

Reload document

| Open in new tab

Formation Excel – VBA débutant

INTRODUCTION …………………………………………………………………………………….. 8

L’ENVIRONNEMENT DE DEVELOPPEMENT ……………………………………………. 8

Présentation de l’éditeur………………………………………………………………………………………………………………. 9

Notions de module ……………………………………………………………………………………………………………………… 10

L’enregistreur de macro …………………………………………………………………………………………………………….. 12

VISUAL BASIC ………………………………………………………………………………………13

Présentation ………………………………………………………………………………………………………………………………. 13

Les variables ……………………………………………………………………………………………………………………………… 13
La portée ………………………………………………………………………………………………………………………………… 14
Le type …………………………………………………………………………………………………………………………………… 16
Conversion de type………………………………………………………………………………………………………………. 17
Les constantes…………………………………………………………………………………………………………………………. 17
Intérêts …………………………………………………………………………………………………………………………………… 18
Le type Variant ……………………………………………………………………………………………………………………….. 19
Type utilisateur ……………………………………………………………………………………………………………………….. 20
Énumération……………………………………………………………………………………………………………………………. 20
Masque binaire………………………………………………………………………………………………………………………… 21

Opérateurs ………………………………………………………………………………………………………………………………… 22
Opérateurs arithmétiques ………………………………………………………………………………………………………….. 22
Opérateurs de comparaison……………………………………………………………………………………………………….. 22
&, Opérateur de concaténation ………………………………………………………………………………………………….. 24
Opérateurs logiques …………………………………………………………………………………………………………………. 24
Opérateur And …………………………………………………………………………………………………………………….. 24
Opérateur Or……………………………………………………………………………………………………………………….. 24
Opérateur Eqv……………………………………………………………………………………………………………………… 24
Opérateur XOr…………………………………………………………………………………………………………………….. 25
Opérateur Imp……………………………………………………………………………………………………………………… 25
Opérateur Not……………………………………………………………………………………………………………………… 25
Combinaisons d’opérateur…………………………………………………………………………………………………………. 25
Opérateur d’affectation, = …………………………………………………………………………………………………………. 26
Logique binaire……………………………………………………………………………………………………………………….. 26

Procédures & fonctions………………………………………………………………………………………………………………. 29
Arguments ……………………………………………………………………………………………………………………………… 29
ByRef & ByVal …………………………………………………………………………………………………………………… 29
Optional……………………………………………………………………………………………………………………………… 30
ParamArray ………………………………………………………………………………………………………………………… 31
Arguments nommés ou passage par position……………………………………………………………………………. 32
Instructions et règles d’appel……………………………………………………………………………………………………… 32
Valeur retournée ……………………………………………………………………………………………………………………… 33

Les objets ………………………………………………………………………………………………………………………………….. 35

Les tableaux ………………………………………………………………………………………………………………………………. 37
Instructions et fonctions spécifiques …………………………………………………………………………………………… 38

Les blocs ……………………………………………………………………………………………………………………………………. 39

1

Structure décisionnelle……………………………………………………………………………………………………………….. 40
Les structures compactes ………………………………………………………………………………………………………….. 40
Immediate If (cid:214) IIf……………………………………………………………………………………………………………….. 40
Choose……………………………………………………………………………………………………………………………….. 40
Switch………………………………………………………………………………………………………………………………… 41
If … Then… Else…………………………………………………………………………………………………………………….. 42
ElseIf…Then ………………………………………………………………………………………………………………………. 43
Select Case……………………………………………………………………………………………………………………………… 44

Les boucles ………………………………………………………………………………………………………………………………… 46
For…Next………………………………………………………………………………………………………………………………. 46
Do…Loop ………………………………………………………………………………………………………………………………. 48

Énumérations & collections………………………………………………………………………………………………………… 50

FONCTIONS VBA …………………………………………………………………………………..51

Fonctions de conversions ……………………………………………………………………………………………………………. 51
Conversion de type ………………………………………………………………………………………………………………….. 51
Conversions spécifiques …………………………………………………………………………………………………………… 52
CVErr ………………………………………………………………………………………………………………………………… 52
Val …………………………………………………………………………………………………………………………………….. 53
Format, Format$………………………………………………………………………………………………………………….. 53
Conversion de valeur ……………………………………………………………………………………………………………….. 55
Hex, Hex$…………………………………………………………………………………………………………………………… 55
Oct, Oct$ ……………………………………………………………………………………………………………………………. 55
Int, Fix ……………………………………………………………………………………………………………………………….. 55

Fonctions de Date & Heure ………………………………………………………………………………………………………… 55
Récupération du temps système…………………………………………………………………………………………………. 56
Date, Date$…………………………………………………………………………………………………………………………. 56
Time, Time$ ……………………………………………………………………………………………………………………….. 56
Timer …………………………………………………………………………………………………………………………………. 56
Now …………………………………………………………………………………………………………………………………… 56
Fonctions de conversions………………………………………………………………………………………………………….. 57
DateValue, TimeValue …………………………………………………………………………………………………………. 57
DateSerial …………………………………………………………………………………………………………………………… 57
TimeSerial ………………………………………………………………………………………………………………………….. 58

Fonctions d’extraction ……………………………………………………………………………………………………………….. 58
Fonctions spécifiques …………………………………………………………………………………………………………… 58
WeekDay……………………………………………………………………………………………………………………………. 58
DatePart……………………………………………………………………………………………………………………………… 59
Fonctions de calculs…………………………………………………………………………………………………………………. 61
DateAdd …………………………………………………………………………………………………………………………….. 61
DateDiff……………………………………………………………………………………………………………………………… 62
Exemples classiques d’utilisation……………………………………………………………………………………………….. 63

Fonctions de fichiers ………………………………………………………………………………………………………………….. 65
Système de fichier……………………………………………………………………………………………………………………. 65
ChDir…………………………………………………………………………………………………………………………………. 65
ChDrive ……………………………………………………………………………………………………………………………… 65
CurDir………………………………………………………………………………………………………………………………… 65
Dir …………………………………………………………………………………………………………………………………….. 65
FileAttr ………………………………………………………………………………………………………………………………. 66
FileCopy …………………………………………………………………………………………………………………………….. 66
FileDateTime………………………………………………………………………………………………………………………. 67
FileLen ………………………………………………………………………………………………………………………………. 67
GetAttr & SetAttr ………………………………………………………………………………………………………………… 67

2

Kill…………………………………………………………………………………………………………………………………….. 68
MkDir & RmDir………………………………………………………………………………………………………………….. 68
Manipulation de fichier…………………………………………………………………………………………………………….. 69
L’instruction Open ……………………………………………………………………………………………………………….. 69
FreeFile………………………………………………………………………………………………………………………………. 70
Close………………………………………………………………………………………………………………………………….. 70
EOF …………………………………………………………………………………………………………………………………… 70
LOF …………………………………………………………………………………………………………………………………… 70
Loc…………………………………………………………………………………………………………………………………….. 70
Seek …………………………………………………………………………………………………………………………………… 70
Instructions d’écriture …………………………………………………………………………………………………………… 70
Instructions de lecture ………………………………………………………………………………………………………….. 71
Exemples ……………………………………………………………………………………………………………………………. 71

Fonctions d’informations……………………………………………………………………………………………………………. 79
Fonctions de couleur………………………………………………………………………………………………………………… 79
QBColor …………………………………………………………………………………………………………………………….. 79
RGB…………………………………………………………………………………………………………………………………… 80

Fonctions d’interactions……………………………………………………………………………………………………………… 80
Environ ………………………………………………………………………………………………………………………………. 80
InputBox…………………………………………………………………………………………………………………………….. 80
MsgBox ……………………………………………………………………………………………………………………………… 80

Fonctions mathématiques…………………………………………………………………………………………………………… 82
Fonctions standards …………………………………………………………………………………………………………………. 82
Fonctions spécifiques……………………………………………………………………………………………………………….. 82
Round ………………………………………………………………………………………………………………………………… 82
Tirage aléatoire, Randomize et Rnd ……………………………………………………………………………………….. 82

Fonctions de chaînes ………………………………………………………………………………………………………………….. 83
Comparaison de chaînes …………………………………………………………………………………………………………… 83
Traitement des caractères………………………………………………………………………………………………………….. 84
Asc & Chr…………………………………………………………………………………………………………………………… 84
Recherche & Extraction……………………………………………………………………………………………………………. 85
StrComp …………………………………………………………………………………………………………………………….. 85
Instr …………………………………………………………………………………………………………………………………… 86
Left, Mid & Right………………………………………………………………………………………………………………… 86
Len…………………………………………………………………………………………………………………………………….. 87
InStrRev …………………………………………………………………………………………………………………………….. 87
Split …………………………………………………………………………………………………………………………………… 88
Filter ………………………………………………………………………………………………………………………………….. 88
Modification …………………………………………………………………………………………………………………………… 89
LTrim, RTrim & Trim………………………………………………………………………………………………………….. 89
Replace ………………………………………………………………………………………………………………………………. 89
LCase & Ucase……………………………………………………………………………………………………………………. 89
StrConv………………………………………………………………………………………………………………………………. 90
StrReverse ………………………………………………………………………………………………………………………….. 90
Construction……………………………………………………………………………………………………………………………. 90
Join ……………………………………………………………………………………………………………………………………. 90
Space …………………………………………………………………………………………………………………………………. 90
String …………………………………………………………………………………………………………………………………. 91

GESTION DES ERREURS……………………………………………………………………….92

Traitement centralisé …………………………………………………………………………………………………………………. 93

Traitement immédiat …………………………………………………………………………………………………………………. 97

3

Erreurs successives…………………………………………………………………………………………………………………….. 99
Programmation sans échec………………………………………………………………………………………………………… 99
Validation et activation…………………………………………………………………………………………………………… 100

MODELE OBJET ………………………………………………………………………………….102

Présentation …………………………………………………………………………………………………………………………….. 103

Fondamentaux…………………………………………………………………………………………………………………………. 106
Glossaire ………………………………………………………………………………………………………………………………. 106
Les aides dans l’éditeur …………………………………………………………………………………………………………… 106
Explorateur d’objet …………………………………………………………………………………………………………….. 106
IntelliSense ……………………………………………………………………………………………………………………….. 108
Manipulation d’objets……………………………………………………………………………………………………………… 109
Durée de vie & Portée ………………………………………………………………………………………………………… 109
Qualification & Manipulation des membres…………………………………………………………………………… 111
Gérer les références ……………………………………………………………………………………………………………. 113
Architecture Excel …………………………………………………………………………………………………………………. 114
Les pièges …………………………………………………………………………………………………………………………….. 116
Référence implicite…………………………………………………………………………………………………………….. 116
La propriété Sheets…………………………………………………………………………………………………………….. 116
Membre par défaut …………………………………………………………………………………………………………….. 116

Application………………………………………………………………………………………………………………………………. 117
Propriétés renvoyant des collections…………………………………………………………………………………………. 117
CommandBars…………………………………………………………………………………………………………………… 117
Dialogs …………………………………………………………………………………………………………………………….. 117
Windows…………………………………………………………………………………………………………………………… 117
Workbooks ……………………………………………………………………………………………………………………….. 118
Propriétés ……………………………………………………………………………………………………………………………… 118
Calculation & CalculateBeforeSave (Boolean) ………………………………………………………………………. 118
Caller ……………………………………………………………………………………………………………………………….. 118
CutCopyMode (Boolean) ……………………………………………………………………………………………………. 118
DecimalSeparator (String)…………………………………………………………………………………………………… 119
DisplayAlerts (Boolean)……………………………………………………………………………………………………… 119
EnableCancelKey (XlEnableCancelKey)………………………………………………………………………………. 119
EnableEvents (Boolean) ……………………………………………………………………………………………………… 119
Interactive (Booléen) ………………………………………………………………………………………………………….. 120
International ……………………………………………………………………………………………………………………… 120
ScreenUpdating (Boolean) ………………………………………………………………………………………………….. 120
SheetsInNewWorkbook (Long)……………………………………………………………………………………………. 120
StatusBar (String) ………………………………………………………………………………………………………………. 121
WorksheetFunction (WorksheetFunction)……………………………………………………………………………… 121
Méthodes ……………………………………………………………………………………………………………………………… 124
Calculate…………………………………………………………………………………………………………………………… 124
ConvertFormula…………………………………………………………………………………………………………………. 124
Evaluate……………………………………………………………………………………………………………………………. 125
GetOpenFilename & GetSaveAsFilename …………………………………………………………………………….. 125
InputBox…………………………………………………………………………………………………………………………… 126
Intersect & Union ………………………………………………………………………………………………………………. 127
Quit………………………………………………………………………………………………………………………………….. 127

Workbooks & Workbook …………………………………………………………………………………………………………. 128
Manipuler la collection Workbooks………………………………………………………………………………………….. 128
Propriété Item (Workbook) …………………………………………………………………………………………………. 128
Propriété Count (Long) ………………………………………………………………………………………………………. 128
Méthode Add…………………………………………………………………………………………………………………….. 128
Méthode Close…………………………………………………………………………………………………………………… 128
Méthode Open …………………………………………………………………………………………………………………… 129

4

Méthode OpenText…………………………………………………………………………………………………………….. 129
Propriétés de l’objet Workbook renvoyant une collection ……………………………………………………………. 131
BuiltinDocumentProperties …………………………………………………………………………………………………. 131
Charts ………………………………………………………………………………………………………………………………. 131
Names………………………………………………………………………………………………………………………………. 131
Sheets ………………………………………………………………………………………………………………………………. 133
Worksheets ……………………………………………………………………………………………………………………….. 133
Quelques propriétés & méthodes de l’objet Workbook………………………………………………………………… 133
Propriétés FullName, Name & Path (String)………………………………………………………………………….. 133
Propriété ReadOnly (Boolean) …………………………………………………………………………………………….. 133
Propriété Saved (Boolean)…………………………………………………………………………………………………… 133
Méthode Close…………………………………………………………………………………………………………………… 134
Méthode Protect ………………………………………………………………………………………………………………… 134
Méthodes Save, SaveAs & SaveCopyAs ………………………………………………………………………………. 134
Méthode Unprotect…………………………………………………………..