Microsoft Excel et VBA : Version 97, 2000, XP, 2003 et 2007

551 Pages·2009·9.166 MB·French

by  Mikael Bidault

Microsoft®
Excel VBA
®
et
Développez des macros
compatibles avec toutes
les versions d’Excel
(de 1997 à 2007)
Mikaël Bidault
Livre Macros excel.book Page I Vendredi, 3. avril 2009 7:45 07
L E P R O G R A M M E U R
Microsoft®

Excel® & VBA
Version 97, 2000, XP, 2003 et 2007

Mikaël Bidault
Livre Macros excel.book Page II Vendredi, 3. avril 2009 7:45 07
Pearson Education France a apporté le plus grand soin à la réalisation de ce livre afin de ous four-
nir une information complète et fiable. Cependant, Pearson Education France n’assume de respon
sabilités, ni pour son utilisation, ni pour les contrefaçons de brevets ou atteintes aux droits de tierces
personnes qui pourraient résulter de cette utilisation.
Les exemples ou les programmes présents dans cet ouvrage sont fournis pour illustrer les descriptions
théoriques. Ils ne sont en aucun cas destinés à une utilisation commerciale ou professionnelle.
Pearson Education France ne pourra en aucun cas être tenu pour responsable des préjudices
ou dommages de quelque nature que ce soit pouv ant résulter de l’utilisation de ces e xemples ou
programmes.
Tous les noms de produits ou marques cités dans ce li vre sont des marques déposées par leurs
propriétaires respectifs.
Publié par Pearson Education France
47 bis, rue des Vinaigriers
75010 PARIS
Tél. : [phone]
www.pearson.fr
Mise en pages : TyPAO
ISBN : 978-2-7440-4108-2
Copyright© 2009 Pearson Education France
Tous droits réservés
Aucune représentation ou reproduction, même partielle, autre que celles prévues à l’article L. 122-5 2° et 3° a) du code de la
propriété intellectuelle ne peut être faite sans l’autorisation expresse de Pearson Education France ou, le cas échéant, sans le
respect des modalités prévues à l’article L. 122-10 dudit code.
Livre Macros excel.book Page III Vendredi, 3. avril 2009 7:45 07
Sommaire
Introduction ................................................. 1 11. Intégrer des applications VBA
dans l’interface d’Excel ......................... 305
Partie I – Découvrir
la programmation Excel .......................... 11 Partie III – Développer
1. Notions fondamentales des interfaces utilisateur........................... 317
de la programmation orientée objet ........ 13 12. Créer des interfaces utilisateur .............. 319
2. Premières macros .................................... 35 13.Exploiter les propriétés
3. Déplacement et sélection des contrôles ActiveX ........................... 347
dans une macro Excel ............................. 63
14. Programmer les interfaces utilisateur .... 387
4. Découvrir Visual Basic Editor ................ 85
Partie IV – Notions avancées
Partie II – Programmer en Visual Basic. 127 de la programmation Excel ..................... 423
5. Développer dans Visual Basic Editor ..... 129 15. Programmer des événements utilisateur 425
6. Variables et constantes ............................ 171 16. Protéger et authentifier des projetsVBA 437
7. Contrôler les programmes VBA ............. 205
17. Bonus : exemple complet
8. Fonctions Excel et VBA ......................... 251 d’application Excel ............................... 461
9. Manipulation des chaînes de caractères .. 267 Annexe ........................................................ 517
10. Débogage et gestion des erreurs ............ 283 Index ............................................................ 521
Livre Macros excel.book Page IV Vendredi, 3. avril 2009 7:45 07
Livre Macros excel.book Page V Vendredi, 3. avril 2009 7:45 07
Table des matières
Introduction ............................................ 1 CHAPITRE 2. Premières macros ............. 35
VBA, c’est quoi ? ................................ 2 Créer une macro GrasItalique ............. 36
Des programmes .............................. 4 Démarrer l’enregistrement ............... 37
Une application hôte et des projets ... 5 Enregistrer les commandes
Un langage de programmation ......... 6 de la macro ...................................... 39
Un environnement de travail ............ 8 Exécuter la macro ............................ 40
Structure de la macro ....................... 42
Conventions typographiques ............... 9
Améliorer la macro .......................... 47
I – Découvrir la programmation Une autre méthode d’enregistrement .... 50
Excel 11 Enregistrement ................................. 50
Structure de la macro ....................... 51
CHAPITRE 1. Notions fondamentales Ecrire la macro .................................... 51
de la programmation orientée objet 13
Exécution de la macro ...................... 53
Comprendre le concept d’objet ........... 14
Choisir l’accessibilité des macros ....... 54
Objets et collections d’objets ........... 14
Accessibilité globale ou limitée ........ 54
Application hôte et modèles d’objets 16
Classeurs et modèles ........................ 55
Accéder aux objets ........................... 19
Le classeur de macros personnel ...... 55
Les propriétés .................................. 21
Les macros complémentaires ............ 56
Les méthodes ................................... 26
Définir le classeur de stock ge
Les événements ................................ 28
lors de l’enregistrement d’une macro 61
Les fonctions ................................... 28
Accéder aux macros d’un classeur
Le modèle d’objets d’Excel ................ 29 spécifque ........................................ 61
Livre Macros excel.book Page VI Vendredi, 3. avril 2009 7:45 07
VI Excel et VBA
CHAPITRE 3. Déplacement et sélection Ajouter un module .................................. 148
dans une macro Excel .............................. 63 Supprimer un module ............................. 150
Méthodes de sélection dans une feuille Créer une procédure ................................... 151
Excel .......................................................... 64
Ecrire l’instruction de déclaration .......... 151
Clavier....................................................... 64
La boîte de dialogue Ajouter
Souris ..................................................... 66 une procédure ......................................... 152
Notion de cellule active ........................... 66 La notion de portée ................................ 154
Références relatives et références absolues . 7 6
Ecriture et mise en forme du code ........... 155
Coder les déplacements effectués Déplacer une procédure ......................... 160
lors de l’enregistrement d’une macro ........ 69
Appel et sortie d’une procédure ................. 161
Référence absolue aux cellules ................ 69
Appel d’une procédure Sub ..................... 161
Référence relative aux cellules ................ 77
Appels de procédures Function
Référence aux cellules fonction
et Property ............................................. 162
de leur contenu ....................................... 80
Passage d’arguments .............................. 163
Référence aux plages de cellules nommées 83
Sortie d’une procédure ........................... 165
CHAPITRE 4. Découvrir Visual Basic Editor 85 Sortie d’un programme ........................... 166
Accéder à Visual Basic Editor .................... 86
Exécuter du code ....................................... 167
Les outils et les fenêtres de Visual Basic Editor 88
Aide à l’écriture de code ............................ 167
L’Explorateur de projet ........................... 89
Vérification automatique de la syntax .... 168
L’Explorateur d’objets ............................ 93
Complément automatique des instructions 169
La fenêtre UserForm ............................... 100
Info express automatique ........................ 170
La fenêtre Code ...................................... 103
La fenêtre Propriétés .............................. 115 CHAPITRE 6. Variables et constantes ............ 171
Les barres d’outils .................................. 119 Déclarer une variable ................................. 172
Paramétrer Visual Basic Editor .................. 123 Déclaration implicite .............................. 172
Déclaration explicite .............................. 173
II – Programmer en Visual Basic 127 Types de données des variables ................. 176
Chaînes de caractères ............................. 176
CHAPITRE 5. Développer dans Valeurs numériques ................................ 178
Visual Basic Editor ................................... 129 Valeurs booléennes ................................. 181
Structure des programmes Visual Basic ..... 130 Dates ..................................................... 181
Les modules ............................................ 130 Type Variant ........................................... 182
Les procédures ........................................ 131 Variables de matrice ............................... 183
Les instructions ...................................... 133 Variables objet ....................................... 187
Les différents types de procédures ............. 135 Types de données personnalisés .............. 192
Procédures Sub ....................................... 136 Constantes ............................................. 194
Procédures Function ............................... 140 Validation et conversion des types
Procédures Property ............................... 143 de données ............................................. 195
Des projets bien structurés ......................... 148 Portée et durée de vie des variables ........... 198
Livre Macros excel.book Page VII Vendredi, 3. avril 2009 7:45 07
Table des matières VII
Portée de niveau procédure ..................... 198 CHAPITRE 9. Manipulation des chaînes
Portée de niveau module privée .............. 198 de caractères ............................................ 267
Portée de niveau module publique ........... 199 Modifier des chaînes de caractères............ 268
Variables statiques ................................. 199 Concaténer des chaînes .......................... 268
Insérer des caractères non accessibles
Traitement interapplications à l’aide
au clavier ............................................... 270
de variables objet ....................................... 200
Répéter une série de caractères .............. 271
CHAPITRE 7. Contrôler
Supprimer les espaces superfus
les programmes VBA ............................... 205
d’une chaîne .......................................... 272
Répéter une série d’instructions :
Extraire une partie d’une chaîne ............. 272
les boucles .................................................. 206
Effectuer des remplacements au sein
La boucle While...Wend .......................... 206 d’une chaîne .......................................... 274
La boucle Do...Loop ............................... 210 Modifer la casse des chaînes de caractères 275
La boucle For...Next ................................ 214
Comparer des chaînes de caractères .......... 275
La boucle For Each...Next ....................... 219
Rechercher dans les chaînes de caractères 277
Utiliser des instructions conditionnelles .... 223 Rechercher une chaîne dans une chaîne ... 277
La structure de contrôle If...Then...Else ... 223 Rechercher une chaîne dans une variable
La structure de contrôle Select Case ....... 227 de matrice .............................................. 280
Définir l’instruction suvante avec GoTo ... 229 CHAPITRE 10. Débogage et gestion
Interagir avec l’utilisateur via des boîtes des erreurs ................................................ 283
de dialogue ................................................. 229 Les étapes et les outils du débogage .......... 284
La fonction InputBox .............................. 230 Test du projet ......................................... 285
La méthode InputBox .............................. 234 Exécuter pas à pas ................................. 287
La fonction MsgBox ............................... 236 La fenêtre Variables locales .................... 288
Affi hage de boîtes de dialogue Excel ...... 241 Les points d’arrêt ................................... 290
Modifer l’ordre d’exécution
Utiliser les opérateurs logiques .................. 246
des instructions ...................................... 291
Trier des données ....................................... 247
La fenêtre Exécution ............................... 291
CHAPITRE 8. Fonctions Excel et VBA ........... 251 Les espions ............................................ 292
Utiliser les fonctions Excel dans VBA ...... 252 La pile des appels .................................. 294
Créer des fonctions Excel personnalisées .... 252 Exemple de débogage ................................ 295
Intégrer une fonction via l’Explorateur Recherche du bogue ............................... 297
d’objets ...................................................... 254 Résolution du bogue ............................... 298
Insérer une fonction VBA dans votre code 254 Gestion des erreurs et des exceptions ........ 301
Insérer une fonction Excel dans votre code 255 Exemple de gestion d’erreur ...................... 303
Recommandations pour l’écriture CHAPITRE 11. Intégrer des applications
de fonctions Excel ..................................... 256 VBA dans l’interface d’Excel ................. 305
Les limites de la cellule ............................ 256 Affecter une macro à un raccourci clavier . 306
Des fonctions toujours à jour .................. 257
Personnaliser la barre d’outils
Principales fonctions VBA ........................ 257 Accès rapide d’Excel 2007 ........................ 307
Livre Macros excel.book Page VIII Vendredi, 3. avril 2009 7:45 07
VIII Excel et VBA
Affecter une macro à un bouton ................. 309 Uniformiser l’espace entre les contrôles . 337
Affecter une macro à une commande Centrer les contrôles .............................. 338
de menu ...................................................... 310 Réorganiser les boutons .......................... 339
Grouper ou séparer des contrôles ........... 340
Supprimer une commande de menu ........... 312
Personnaliser la boîte à outils .................... 341
Modifier l’image et/ou le nom
d’une commande ........................................ 313 Ajouter/supprimer un contrôle ................ 341
Ajouter/supprimer une page .................... 343
Affecter une macro à un objet .................... 315
Afficher/masquer une feuille ..................... 345
III – Développer CHAPITRE 13. Exploiter les propriétés
des interfaces utilisateur 317 des contrôles ActiveX ............................... 347
Propriété Name .......................................... 349
CHAPITRE 12. . Créer des interfaces Apparence .................................................. 350
utilisateur .................................................. 319
Alignment ................................................. 350
Les phases de développement de feuilles .. 320 BackColor .............................................. 350
Créer une feuille ......................................... 321 BackStyle ............................................... 351
BorderColor ........................................... 351
Les contrôles de la boîte à outils ................ 323
BorderStyle ............................................ 351
Outil Sélection ........................................ 323
Caption .................................................. 352
Contrôle Label ....................................... 323
ControlTipText ........................................ 353
Contrôle TextBox .................................... 324
ForeColor ............................................... 353
Contrôle ComboBox ............................... 325
SpecialEffect .......................................... 353
Contrôle Frame ...................................... 325
Style ....................................................... 354
Contrôle ListBox .................................... 325
Value ...................................................... 354
Contrôle CheckBox ................................. 326
Visible .................................................... 356
Contrôle OptionButton ........................... 327
Comportement ........................................... 359
Contrôle ToggleButton ............................ 327
AutoSize .................................................... 359
Contrôle CommandButton ...................... 327
AutoTab .................................................. 360
Contrôle TabStrip ................................... 328
AutoWordSelect ...................................... 360
Contrôle MultiPage ................................ 328
Cancel ................................................... 361
Contrôle ScrollBar ................................. 329
Default ................................................... 362
Contrôle SpinButton ............................... 330
Enabled .................................................. 362
Placer des contrôles sur une feuille ............ 330
EnterKeyBehavior .................................. 364
Copier-coller des contrôles ..................... 332
HideSelection ......................................... 364
Sélectionner plusieurs contrôles .............. 333
Locked ................................................... 364
Supprimer des contrôles ......................... 334
MaxLenght ............................................. 365
Mise en forme des contrôles ...................... 334 MultiLine ............................................... 365
La grille ................................................. 335 SelectionMargin ..................................... 365
Aligner les contrôles ............................... 336 Style ....................................................... 366
Uniformiser la taille des contrôles .......... 337 TabKeyBehavior ..................................... 367
Livre Macros excel.book Page IX Vendredi, 3. avril 2009 7:45 07
Table des matières IX
TextAlign ................................................ 367 Contrôle ComboBox ............................... 406
TripleState .............................................. 367 Contrôle ListBox .................................... 411
WordWrap .............................................. 368 Contrôles CheckBox et OptionButton ...... 414
Défilement ................................................ 369 Contrôle ScrollBar ................................. 415
ScrollBars ................................................. 369 Contrôle SpinButton ............................... 417
KeepScrollsVisible .................................. 370
Exploiter les informations
Delay ..................................................... 370
d’une feuille VBA ...................................... 420
Max et Min ............................................. 371
SmallChange .......................................... 372
IV – Notions avancées
LargeChange .......................................... 372
de la programmation Excel 423
Divers ......................................................... 373
Accelerator ............................................... 373
CHAPITRE 15. Programmer des événements
GroupName ............................................ 374
utilisateur ................................................. 425
HelpContextID ....................................... 375
L’objet Application .................................... 426
MouseIcon ............................................. 375
Déclaration et instanciation
MousePointer ......................................... 376
de l’objet Application ............................. 426
TabIndex ................................................ 377
Création de procédures événementielles
TabStop .................................................. 379
de niveau application ............................. 427
Tag ......................................................... 379
Propriétés de l’objet Application ............ 429
Emplacement ............................................. 379
Méthodes de l’objet Application ............. 431
Height et Width ......................................... 379
Left et Top .............................................. 379 L’objet ThisWorkbook ............................... 431
StartUpPosition ...................................... 380 L’objet Worksheet ...................................... 433
Image ......................................................... 381
CHAPITRE 16. Protéger et authentifier
Picture ...................................................... 381 des projets VBA ....................................... 437
PictureAlignment .................................... 382
Les virus macro ......................................... 438
PictureSizeMode .................................... 383
Se protéger des virus macros ..................... 438
PicturePosition ....................................... 384
Définir un niveau de sécurit
PictureTiling .......................................... 384
dans Excel 2007 .................................... 438
Police ......................................................... 385
Définir un niveau de sécurité
Font ........................................................... 385
avec Excel 2000, XP et 2003 .................... 440
CHAPITRE 14. Programmer Effectuer des sauvegardes des macros ..... 444
les interfaces utilisateur ........................... 387
Protéger l’accès aux macros ...................... 445
Créer des procédures événementielles ....... 388
Verrouiller un projet ............................... 445
Créer une procédure ............................... 388
Limiter les droits d’exécution d’une macro 447
Les événements ....................................... 394
Exemples d’exploitation des contrôles ...... 400 Authentifier ses macros ............................. 457
Contrôle Label ....................................... 401 Obtenir une authentifcation ................... 458
Contrôle TextBox .................................... 403 Authentifer une macro ........................... 459