VBA pour Excel - Objets et événements Excel Définitions Modèle Objet Excel Principaux objets Excel Principaux événements Excel Autres objets intéressants Autres objets, autres propriétés Objet implicite (With) Exemple 1: Changer la couleur d'arrière-plan des cellules modifiées. Exemple 2: Une fonction qui affiche le contenu réel d'une cellule. Exemple 3: Empêcher la modification des cellules dont l'arrière-plan est rouge. Exemple 4: Empêcher la modification des cellules ne faisant pas partie d'une plage. Exemple 5: Empêcher le déplacement du curseur Excel tant qu'une condition n'est pas respectée. Exemple 6: Trouver toutes les cellules d'une feuille contenant le mot Liberté. Exemple 7: Obtenir le nom et la taille du dossier contenant le classeur Excel actif.
Accueil Concepts de programmation VBE l'éditeur VBA Déclarations, types, références, expressions et assignations Tests et branchements Les boucles Gestion d'erreur Collections et tableaux Dialogues et formulaires Objets et événements Excel Conseils de programmation Liste d'instructions
Word 2003 Word 2007 Excel Excel PowerPoint 2003 PowerPoint 2007 Access Access 2010 Internet Général Intelligence financière Vidéos
Précédent Suivant Page Principale FAQ Nouveautés Plan du site Références Motivations Manuels de formation Fichiers de démonstration
Par courriel Abonnez-vous à l'infolettre LCI

Définitions

Modèle Objet Excel

VBA peut manipuler le contenu d'un classeur Excel par les objets qu'il contient. Mais comment trouver le nom de l'objet (ou de la classe) qu'il faut utiliser?

Le plus simple est d'utiliser l'enregistreuse de macro-commande.

Par exemple, pour trouver comment changer la couleur de fonds d'une feuille Excel,

Démarrez l'enregistreuse de macro-commandes, en notant l'endroit où elle sera enregistrée.
Changez la couleur du fonds (sélectionner la feuille, afficher la boîte de dialogue Format de cellule, choisir l'onglet remplissage puis une couleur).
Arrêtez l'enregistreuse de macro-commande.
Affichez-là dans l'éditeur VBA. Elle ressemble à:
 

Dans cet enregistrement, on reconnait les objets:

Sheets("Feuil1"): la feuille Feuill1 .
Cells : collection de toutes les cellules de la feuille.
Interior: collection de tous les intérieurs des cellules.

et les méthodes:

• Select

Une façon plus complexe est d'explorer le modèle objet Excel.
L'aide VBA Excel 2007 permet de l'explorer en accédant à la rubrique:
Référence du développeur Excel 2007
Microsoft vous offre ici la liste des principaux objets Excel en ordre alphabétique, ce qui est pratiquement inutilisable.

Utilisez plutôt l'aide VBA Excel 2003, qui est installée à l'adresse
C:\Program Files\Microsoft Office\OFFICE11\1036\VBAXL10,CHM
Vous pouvez alors parcourir la hiérarchie des objets Excel:

et trouver l'objet qui vous intéresse en cliquant d'abord sur Range, puis en trouvant la propriété ou l'objet Interior, et en devinant qu'il faut adresser l'objet Interior de la collection Cells de la feuille voulue.
 

Principaux objets Excel

Les cellules Excel sont des objets de la classe Range.

La meilleure façon d'accéder aux propriétés d'une plage de cellules Excel depuis VBA est de nommer cette plage en Excel, puis d'utiliser ce nom comme référence de range.

• Range("plage") est un objet contenant la plage nommée... plage.

Une technique beaucoup moins utile (mais hélas trop répandue) est d'utiliser l'adresse de la plage.

• Range("A1:H5") est un objet contenant la plage A1:H5.

Une variante de cette technique est l'utilisation de la méthode Evaluate (voir plus loin).

• Evaluate("A1:H5") ou mieux encore [A1:H5] équivalent exactement à Range("A1:H5").

Aussi peu utile.

Deux autres façons utilisent des numéros de ligne et de colonne, et sont beaucoup plus utiles.

• Cells(3,5) est un objet contenant la cellule E5 (ligne 3, colonne 5).

Attention, l'objet Cells utilise l'adressage Ligne, Colonne, alors que Excel utilise Colonne, Ligne (E5).

• Range("A1").Offset(3,5) est un objet contenant la plage F4 soit un déplacement de 3 lignes et 5 colonnes à partir de A1.

Ces deux dernières techniques peuvent être utilisées avec l'objet Range pour désigner des plages.

• Range(Cells(3,5), Range("A1").Offset(3,5)) est un objet contenant la plage E3:F4.

L'utilisation d'un nom de plage au lieu d'une adresse permettra de déplacer la plage dans Excel sans devoir modifier la programmation VBA.
Il est donc FORTEMENT suggéré de manipuler les lignes d'une liste de données ainsi:

Nommez la cellule de gauche de l'entête de liste.
Utilisez la notation Range(nom).Offset(ligne,colonne) pour représenter les champs de la liste.

Cellule ou plage? Le modèle objet Excel ne fait pas la différence entre une plage et une cellule. Une cellule est simplement une plage ne contenant qu'une cellule.
Range("A1:B9") est un objet de la même classe que Range("A1") et possède donc les mêmes propriétés (dont les valeurs peuvent être différentes) et méthodes.

Un objet Range est aussi une collection de cellules, Comme une cellule est un Range (d'une cellule), c'est une collection ne comptant qu'un objet.
Un Range est donc une collection de collections.

Plages spéciales.  Certaines propriétés du modèle objet Excel contiennent des plages spécifiques:

Les objets Range ont un grand nombre de propriétés. Voici les plus intéressantes:

Les feuilles Excel sont des objets de la classe Worksheet.

Elles sont regroupées dans la collection Worksheets et dans la collection Sheets.

On peut manipuler une feuille de classeur par son nom:

Worksheets("Provinces") est la feuille nommée province.
Sheets("Provinces") est aussi la feuille nommée province.

La différence entre les deux collections est que la collection Sheets contient aussi les objets Chart du classeur.

Les classeurs Excel sont des objets de la classe Workbook.

Tous les classeurs ouverts sont regroupés dans la collection Workbooks.

Workbook("VBA Exemples.xls") est le classeur VBA Exemples.xls

La programme Excel lui-même, lorsque démarré, est un objet de la classe Application.

Le classeur actif est: Application.ActiveWorkbook ou plus simplement ActiveWorkbook.

Le classeur contenant le code VBA en cours d'exécution est:Application.ThisWorkbook ou plus simplement ThisWorkbook.

La feuille active du classeur actif est:Application.ActiveWorkbook.ActiveSheet ou plus simplement ActiveSheet.

La cellule active de la feuille active du classeur actif est:Application.ActiveWorkbook.ActiveSheet.ActiveCell ou plus simplement ActiveCell.

La cellule active de la feuille nommée Province du classeur actif est:Application.ActiveWorkbook.Worksheets("Province").ActiveCell ou plus simplement Worksheets("Province").ActiveCell.

etc.

L'objet Selection est l'objet passe-partout, qui contient ce qui est sélectionné dans le classeur Excel. Ce peut être une plage, un graphique, un tableau croisé ou n'importe quoi. L'utilisation de Selection en VBA est à éviter, puisqu'il risque fort de contenir une sélection inattendue lors de l'exécution du programme.

La barre d'état de la fenêtre Excel est l'objet StatusBar.

Méthodes Excel

La plupart des opérations que l'utilisateur peut faire en Excel peuvent être programmées en VBA grâce aux méthodes des différents objets du modèle objet Excel.

Voici les plus intéressantes:

Principaux événements Excel

Remarquez que dans les événements SheetChange, Change et SelectionChange, VBA n'a pas directement accès aux valeurs précédant le changement,
ni à un paramètre Cancel pour annuler le changement. Il faut utiliser une astuce pour empêcher le changement. Voir les exemples.

Notez finalement qu'on peut désactiver le traitement des événements, en utilisant la propriété EnableEvents.

Autres objets intéressants

Me

C'est un objet Excel ou VBA contenant le code en cours d'exécution. Si le code appartient à une feuille, c'est un objet Worksheet contenant cette feuille.
Me est le plus souvent utilisé pour désigner le formulaire VBA (Forms) contenant le code en cours d'exécution. On évite ainsi de nommer le formulaire
dans le code VBA , ce qui donne des programmes plus faciles à utiliser dans d'autres formulaires.

Me.Name contient le nom du formulaire en cours d'exécution.

Utilisation la plus fréquente:

Me.Close ferme le formulaire qui contient cette ligne.

Err

C'est un objet VBA contenant la dernière erreur rencontrée. Ses propriétés permettent d'en connaître la nature:

Err.Description contient la description de la dernière erreur rencontrées par VBA.

VBA peut déclencher une erreur avec Err:

Err.Raise 18 simule une interruption par l'utilisateur (Ctrl-Break).

FileSystemObject

C'est l'objet contenant des instructions permettant à VBA d'accéder à l'arborescence de dossiers et de fichiers de l'ordinateur.

ATTENTION ce n'est pas un objet VBA ni un objet Excel, mais un objet de la librairie Microsoft Scripting Runtime.

Vous devez référencer cette librairie dans l'éditeur de code VBA avant de l'utiliser. Voir Exemples.

Objets intéressants:

Un objet FileSystemObject contient une collection Drives.
Chaque objet Drive de la collection Drives contient une collection Folders et une collection Files.
Chaque objet Folder d'une collection Folders contient une collection Folders et une collection Files.
Chaque objet File d'une collection Files contient un fichier (document Windows).

Cette structure permet de parcourir l'arborescence de documents en VBA comme avec l'explorateur Windows.

Autres objets, autres propriétés

Les environnements Excel, Office et Windows offrent une richesse incroyable d'objets et de propriétés qu'il est impossible de décrire ici
(ni ailleurs d'ailleurs), ne serait-ce que parce que cette liste change constamment

Il vous faudra donc développer l'habilité de soupçonner l'existence de l'objet que vous souhaitez utiliser et d'en découvrir le nom et la documentation.
Bonne chance.

Objet implicite (With)

Il arrive fréquemment qu'une série d'instructions utilise le même objet. L'écriture (et la lecture) du code VBA devient alors fastidieuse, avec la répétition incessante de la même référence.

L'instruction With permet de réduire les répétitions.

Dans l'exemple 2 ci-dessous, le code:

    If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then
        Application.EnableEvents = False 'Empêcher une boucle d'événements
        Application.Undo                 'Annuler le changement
        Application.EnableEvents = True  'Très important de réactiver
    End If

pourrait être:   

    With Application
        If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then
            .EnableEvents = False 'Empêcher une boucle d'événements
            .Undo                 'Annuler le changement
            .EnableEvents = True  'Très important de réactiver
        End If
    End With

Exemples

Exemple 1: Changer la couleur d'arrière-plan des cellules modifiées.

Créez la procédure événementielle Double-cliquez sur ThisWorkbook pour ouvrir le module VBA associé au classeur Sélectionnez Workbook dans la liste d'objets Sélectionnez SheetChange dans la liste d'événements La procédure Sub Workbook_SheetChange est créée pour vous.
Insérez le code ci-contre	Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Auteur: Michel Berthiaume 'Changer l'arrière-plan d'une cellule modifiée     'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)     Target.Interior.Color = vbYellow 'Jaune End Sub
Testez le programme en Excel: toute cellule modifiée devient jaune.

Il est possible de restreindre les modifications à une seule feuille:

• en insérant le code dans la procédure événementielle Worksheet_Change du module associé à la feuille voulue.
  ou
• en insérant un test sur le nom de la feuille (Sh.Name) dans le code suggéré (en le laissant dans le module associé au classeur).

Si les deux techniques fonctionnent, la seconde limite l'éparpillement du code dans plusieurs modules et produit donc un programme plus facile à comprendre et à modifier plus tard.

Exemple 2: Une fonction qui affiche le contenu réel d'une cellule.

Dans une (ou plusieurs) cellules du classeur Excel, utilisez la fonction
=fnValeurCellule(A2)
en remplaçant A2 par une référence à une cellule Excel dont vous voulez afficher le contenu réel.

Exemple 3: Empêcher la modification des cellules dont l'arrière-plan est rouge.

Ouvrez le module VBA associé au classeur Excel
Insérez le code ci-contre	Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Auteur: Michel Berthiaume 'Empêcher la modification de cellules dont l'arrière-plan ' est d'une couleur choisie par l'utilisateur 'Note: la feuille doit contenir une cellule nommée CouleurVerrou ' de la couleur choisie 'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)     If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then         Application.EnableEvents = False 'Empêcher une boucle d'événements         Application.Undo                 'Annuler le changement         Application.EnableEvents = True  'Très important de réactiver     End If End Sub
Dans Excel, changez l'arrière-plan d'une plage de cellules. Créez une plage nommée CouleurVerrou à partir d'une cellule de la plage ainsi colorée. Testez le programme en Excel: toute changement à une cellule de la plage colorée est annulée par le programme.

Remarquez ici l'utilisation de la propriété EnableEvents pour empêcher que l'instruction suivante (la méthode Undo) provoque l'événement Workbook_SheetChange et entraîne ainsi une succession sans fin d'exécution de la procédure Sub correspondante.
Workbook_SheetChange appelant Workbook_SheetChange appelant Workbook_SheetChange appelant Workbook_SheetChange ...

Exemple 4: Empêcher la modification des cellules ne faisant pas partie d'une plage.

Ouvrez le module VBA associé au classeur Excel
Insérez le code ci-contre	Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Auteur: Michel Berthiaume 'Empêcher la modification de cellules en dehors ' d'une plage nommée ZoneDeSaisie 'Note: le classeur doit contenir une plage nommée ZoneDeSaisie     'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)     If Intersect(Target, Range("ZoneDeSaisie")) Is Nothing Then         Application.EnableEvents = False 'Empêcher une boucle d'événements         Application.Undo                 'Annuler le changement         Application.EnableEvents = True  'Très important de réactiver     End If End Sub
Dans Excel, créez une plage nommée ZoneDeSaisie. Testez le programme en Excel: toute changement à une cellule ne faisant pas partie de la plage ZoneDeSaisie est annulée par le programme.

Exemple 5: Empêcher le déplacement du curseur Excel tant qu'une condition n'est pas respectée.

L'événement qui nous intéresse est SelectionChange, qui n'a pas de paramètre Cancel, donc qu'on ne peut pas annuler directement.
La méthode Undo ne fonctionne pas non plus, car elle n'annule pas le déplacement. De plus, elle risquerait d'annuler une modification, alors qu'on ne veut annuler que le déplacement.
Il faudra donc que le programme garde trace de la dernière cellule sélectionnée.

Dans le programme ci-dessous, dès que l'utilisateur sélectionne une cellule de la plage ZoneDeSaisie, il ne peut pas déplacer le curseur hors de la plage tant que la somme des cellules de la plage sera différente de 10.

Exemple 6: Trouver toutes les cellules d'une feuille contenant le mot Liberté.

Technique 1, utilisant une boucle testant chaque cellule d'une plage. Notez bien que le résultat est une plage de cellules disjointes, chacune contenant le mot cherché.

Notez aussi qu'on a généralisé la solution, qui permet de chercher n'importe quelle chaîne de caractères dans n'importe quelle plage Excel.

Remarques sur cette fonction

• Puisqu'elle retourne une plage de cellules, elle ne peut pas être utilisée directement dans une cellule du classeur Excel. En effet, une cellule ne peut pas contenir une plage.
  Mais on peut l'utiliser comme paramètre de type plage dans une fonction Excel, comme dans
  =NBVAL(fnTrouveMotDansPlage("Liberté";A1:C6))
• La fontion Excel Nb.Si() joue un rôle semblable, mais dans sa version simple (=NB.SI(A1:C6;"Liberté")) ne comptera que les cellules dont la valeur est égale à la valeur cherchée, contrairement à fnTrouveMotDansPlage qui trouve les cellules qui contiennent la valeur. De plus, fnTrouveMotDansPlage retourne la plage et non le nombre. On peut donc manipuler le résultat en VBA en lui assignant un nom de variable:
  dim rPlageTrouvée as Range
  Set rPlageTrouvée = fnTrouveMotDansPlage("Liberté", Range("A1:C6"))

Technique 2, utilisant les méthodes Find et Findnext dans une boucle cherchant les cellules d'une plage. Notez bien que le résultat est le même que dans la technique 1.

Exemple 7: Obtenir le nom et la taille du dossier contenant le classeur Excel actif.

Remarques sur cette fonction

• Une fonction ne peut retourner qu'une seule valeur, alors qu'ici on veut le nom te la taille du dossier. On utilise donc un paramètre pour spécifier la valeur voulue.
  • 1 retourne le nom
  • 2 retourne la taille

  et si le paramètre est omis, la fonction retourne les deux dans une chaîne de caractères.

• Cette fonction utilise une librairie d'objets qui n'est pas inclue par défaut dans l'éditeur VBE. Il faut donc ajouter une référence à cette libraire:
  Dans VBE:
  • Ouvrez le menu Outils
  • Sélectionnez Références
  • Trouvez et cochez Microsoft Scripting Runtime:
    

Ce site est hébergé par 1&1.com