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.
| Objet: |
Un objet est une instance d'une classe (!). En fait, un objet est une "entité" informatique qu'un programme informatique peut manipuler. Dans le cadre du présent tutoriel, un objet est une entité externe à VBA que votre programme VBA peut (veut?) manipuler. Le cas le plus évident est une cellule de classeur. Pour la manipuler, VBA utilise une instance de la classe Range. |
| Classe: | Une classe est une catégorie (sorte) d'objets. Votre programme peut manipuler une cellule précise (ou un groupe de cellule), mais pas le concept de cellule. |
| Propriétés: |
Chaque objet possède des propriétés qui le décrivent. La liste des
propriétés varie d'une classe à l'autre, mais tous les objets d'une même
classe ont les mêmes propriétés. Ce qui les distingue, ce sont les
valeurs des propriétés. Par exemple, toutes les feuilles Excel ont la propriété Name, mais la valeur de la propriété Name est différente pour chaque feuille (du moins on le souhaite). |
| Méthode: |
Une méthode est une action que peut réaliser un objet. La liste des méthodes
varie d'une classe à l'autre, mais tous les objets d'une même classe ont les
mêmes méthodes. Par exemple, la méthode Copy d'une cellule permet de copier le contenu de cette cellule dans le presse-papier (ou dans une autre cellule). Évidemment, il arrive qu'une méthode change les propriétés d'un objet. Par exemple, la méthode PasteSpecial d'une cellule change une ou plusieurs propriétés de cette cellule. |
| Événement: |
Un événement permet d'associer une procédure VBA à un objet. Par exemple,
l'événement Change d'une feuille se produit lorsqu'une cellule de cette feuille
est modifiée et permet d'associer une procédure nommée Worksheet_Change
à cette feuille Excel. |
| Collection: |
Ensemble d'objets d'une même classe qui peuvent être adressés avec le
nom de la collection et un numéro d'item. Par exemple, un classeur Excel est une collection Sheets d'objets WorkSheet et/ou Chart |
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 à:
|
Sheets("Feuil1").Select Cells.Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = 0 .PatternTintAndShade = 0 End With |
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:
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.
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.
Une technique beaucoup moins utile (mais hélas trop répandue) est d'utiliser l'adresse de la plage.
Une variante de cette technique est l'utilisation de la méthode Evaluate (voir plus loin).
Aussi peu utile.
Deux autres façons utilisent des numéros de ligne et de colonne, et sont beaucoup plus utiles.
Attention, l'objet Cells utilise l'adressage Ligne, Colonne, alors que Excel utilise Colonne, Ligne (E5).
Ces deux dernières techniques peuvent être utilisées avec l'objet Range pour désigner des plages.
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:
| Cells |
Plage qui contient toutes les cellules d'une feuille:
|
| UsedRange |
Plage qui contient TOUTES les cellules occupées d'une feuille:
|
| CurrentRegion |
Plage continue (limitée par des lignes et des colonnes vides) dont fait
partie la cellule:
|
| Rows |
Collection des lignes d'une plage. Permet d'obtenir une ligne d'une
plage:
|
| Columns |
Collection des colonnes d'une plage. Permet d'obtenir une colonne d'une
plage:
|
| SpecialCells |
Plages spéciales d'une plage Excel:
|
Les objets Range ont un grand nombre de propriétés. Voici les plus intéressantes:
| Column |
Numéro de la colonne de gauche de la plage
|
| Columns.Count |
Nombre de colonnes de la plage
|
| Row | Numéro de la ligne du haut de la plage |
| Rows.Count | Nombre de lignes de la plage |
| Count | Nombre de cellules de la plage |
| CurrentRegion | Plage continue (limitée par des lignes et des colonnes vides) dont fait partie la cellule. |
| End(xlDown) | Cellule de la dernière ligne à la fin de la zone qui contient la plage. Correspond aux touches Fin+Bas. |
| End(xlToRight) | Cellule de la dernière colonne à la fin de la zone qui contient la plage. Correspond aux touches Fin+Droite. |
| Text | Valeur AFFICHÉE de la cellule, donc évaluée et formatée. |
| Value | Valeur ÉVALUÉE de la cellule. |
| Worksheet | Objet Worksheet qui est la feuille qui contient la plage |
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.
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:
| Evaluate | Permet d'exécuter en VBA une formule Excel (ce qui est
à droite du signe = dans une cellule). La méthode Evaluate peut être
remplacée par les par les crochets: [2+2] équivaut à Evaluate("2+2") et vaut 4. Attention, ce qui est incorrect dans une cellule est incorrect pour Evaluate.
|
| Intersect | Retourne une plage formée des cellules communes de deux
plages.
Cette méthode permet de savoir si une cellule appartient à une plage:
|
| Calculate | Recalcule la feuille ou le classeur Excel
|
| Save | Enregistre le classeur
|
| Quit | Ferme Excel. Attention, si le classeur a été modifié mais non sauvegardé, la boîte de dialogue de sauvegarde d'Excel sera affichée. Voir la méthode Save.
|
|
Clear ClearContents ClearFormats |
Effacent respectivement les cellules, les contenus ou
les formats d'une plage.
|
| Copy | Copie le contenu d'une plage dans une autre ou dans le
presse-papier
|
| PasteSpecial | Colle le contenu d'une plage ou du presse-papier dans
une plage. On peut spécifier le type de collage spécial: xlPasteAll
(défaut), xlPasteAllExceptBorders, xlPasteColumnWidths.
xlPasteComments, xlPasteFormats, xlPasteFormulas,
xlPasteFormulasAndNumberFormats, xlPasteValidation, xlPasteValues ou
xlPasteValuesAndNumberFormats.
|
|
Find et FindNext |
Ces deux méthodes permettent d'effectuer une recherche dans une plage de cellules. Voir Exemples. |
| Select | Sélectionne une cellule ou une feuille Excel. À éviter la plupart du temps en programmation VBA, car le déplacement du curseur Excel est rarement souhaitable pendant l'exécution d'un programme VBA. |
| Sort | Tri de plage. Voir l'aide VBA Excel pour en connaître le fonctionnement. Il est aussi utile d'utiliser l'enregistreuse de macro-commandes sur un tri manuel des données pour en analyser les paramètres. |
| Objet | Événement | Paramètres | Se produit lorsque | Utilisé pour |
| Workbook | Open | Le classeur Excel est ouvert |
|
|
| Workbook | BeforeClose | Cancel | Lorsqu'une demande de fermeture du classeur a été faite |
|
| Workbook | SheetChange |
Sh la feuille modifiée Target la plage modifiée |
Lorsqu'une cellule ou plage du classeur est modifiée |
|
| Worksheet | Change | Target la plage modifiée | Lorsqu'une cellule ou plage de la feuille qui contient la procédure Sub est modifiée |
|
| Worksheet | SelectionChange | Target la nouvelle plage sélectionnée | Lors de la sélection d'une autre plage dans un classeur |
|
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.
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.
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).
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:
| Drives | Collection d'objets Drive |
| Drive | Objet contenant un lecteur |
| Folders | Collection d'objets Folder |
| Folder | Objet contenant un dossier |
| Files | Collection d'objets File |
| File | Objet contenant un fichier |
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.
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.
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.
|
With objet [instructions] End With Où:
À l'intérieur du bloc With ... End With, toute référence commençant par un . (point) appartient à objet. ATTENTION: With N'est PAS une instruction de boucle ou de répétition ou de test. Si le code doit être répété, il doit être encadré d'une instruction de boucle (Do) ou de test (If). |
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
|
|
|
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 |
|
Il est possible de restreindre les modifications à une seule feuille:
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.
|
Function fnValeurCellule(rCellule) 'Auteur: Michel Berthiaume 'Retourne le contenu (formule ou valeur) de la cellule en paramètre If TypeName(rCellule) <> "Range" Then fnValeurCellule = "Erreur de paramètre" Exit Function End If fnValeurCellule = rCellule.Formula End Function
|
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.
|
|
|
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 |
|
|
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 ...
|
|
|
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 |
|
|
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.
|
Function fnDéplacementValide() 'Auteur: Michel Berthiaume 'Retourne Vrai si la somme des cellules de la plage ZoneDeSaisie est égale à 10 fnDéplacementValide = ([sum(ZoneDeSaisie)] = 10) End Function
Function
fnPlageAdansPlageB(plageA, PlageB) |
|
Public rCellulePrécédente As Range 'Contiendra la dernière cellule sélectionnée |
|
Set rCellulePrécédente = ActiveCell 'Initialiser à la sélection initiale (à l'ouverture) |
|
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.
|
Function fnTrouveMotDansPlage(sMot, rPlage) 'Auteur: Michel Berthiaume 'Retourne une plage formée des cellules de rPlage contenant sMot
Dim rTrouvée As
Range 'contiendra les cellules
trouvées |
|
fnTrouveMotDansPlage("Liberté", Range("A1:C6")).Count |
|
=NBVAL(fnTrouveMotDansPlage("Liberté";A1:C6)) |
|
fnTrouveMotDansPlage("Liberté", Range("A1:C6")).interior.color = vbRed |
Remarques sur cette fonction
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.
|
Function fnTrouveMotDansPlage(sMot, rPlage) 'Auteur: Michel Berthiaume 'Retourne une plage formée des cellules de rPlage contenant sMot
|
|
Function fnDossier(Optional param = 0) 'Auteur Michel Berthiaume 'Retourne le nom du dossier et/ou l'espace qu'il utilise 'La ligne suivante requiert une référence à Microsoft Scripting Runtime Dim fso As New Scripting.FileSystemObject If param = 1 Then fnDossier = ActiveWorkbook.Path Exit Function ElseIf param = 2 Then fnDossier = (fso.GetFolder(ActiveWorkbook.Path).Size) / 1024 Exit Function Else fnDossier = ActiveWorkbook.Path & ": " & _ Round(fso.GetFolder(ActiveWorkbook.Path).Size / 1024, 2) & _ " Ko" End If End Function |
Remarques sur cette fonction
et si le paramètre est omis, la fonction retourne les deux dans une chaîne de caractères.
Abonnez-vous à l'infolettre Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici. |
Ce site est hébergé par 1&1.com