Autres objets, autres propriétés
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,
|
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:
et les méthodes:
Le document pdf ci-dessous illustre le fonctionnement de l'enregistreuse:
Enregistrer
une macro-commande
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.
Le document suivant explique comment installer l'aide locale VBA:
Vous pouvez y 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:
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:
On peut adresser une cellule de cette plage: Sheets("Province").Cells(3,5) désigne la cellule E3 de la feuille Province. |
| UsedRange |
Plage délimitée par la première (haut-gauche) et la dernière
(bas-droite) des 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:
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.
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. Si vous utilisez l'objet Selection, n'oubliez pas de valider le type de l'objet qu'il contient.
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 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 Exemple 6. |
| 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. |
| Volatile |
Force l'exécution d'une Function à chaque fois que le classeur est recalculé.
Normalement, Excel recalcule les formules des cellules lorsque leurs antécédants sont modifiés. Dans le cas des fonctions personnalisées VBA (Function) utilisées dans Excel, le recalcul ne se fait pas toujours. Application.Volatile utilisée dans une procédure Function assure que la Function sera réexécutéer à chaque recalcul du classeur Excel.
|
| 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.
Utilisation la plus fréquente:
C'est un objet VBA contenant la dernière erreur rencontrée. Ses propriétés permettent d'en connaître la nature:
VBA peut déclencher une erreur avec Err:
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
Range("Liste").SpecialCells(xlCellTypeLastCell).Row + 1
Range("Liste").End(xlDown).Row + 1
Range("Liste")(1).CurrentRegion.Rows.Count + 1
[MATCH(TRUE,ISBLANK(Liste),0)]
Notez que la dernière instruction
[MATCH(TRUE,ISBLANK(Liste),0)] est en fait une formule Excel qui est utilisable
directement dans le classeur.
La version Excel française de la formule est:
{=EQUIV(VRAI;ESTVIDE(Liste);0)}
Les {} indiquent que c'est une fonction
matricielle, qui doit donc être entrée dans un classeur Excel avec
Ctrl-Maj-Enter.
|
|
|
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.
Le programme utilise une plage Excel nommée DernièreCellule pour conserver l'adresse de la dernière cellule sélectionnée.
|
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) |
|
[DernièreCellule] = ActiveSheet.Name & "!" & ActiveCell.Address |
|
'La destination doit être à l'extérieur de la ZoneDeSaisie If fnPlageAdansPlageB(Target, [ZoneDeSaisie]) Then [DernièreCellule] = ActiveSheet.Name & "!" & ActiveCell.Address Exit Sub End If If fnDéplacementValide() Then [DernièreCellule] = ActiveSheet.Name & "!" & ActiveCell.Address Exit Sub End If Application.EnableEvents = False 'Empêcher une boucle d'événements Application.Goto Reference:=Range([DernièreCellule]) 'Annuler le déplacement Application.EnableEvents = True |
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.
Le fichier Excel Tutoriel Exemples Objets.xlsm contient les exemples présentés dans la présente section.