Vous avez utilisé Excel depuis un certain temps, mais vous ne l’avez probablement jamais considéré comme un objet. Plus vous travaillez avec VBA, plus vous visualisez Excel dans ces termes. Vous comprendrez qu’Excel est un objet et qu’il contient d’autres objets. Ces objets, à leur tour, contiennent encore plus d’objets. En d’autres termes, la programmation VBA implique de travailler avec une hiérarchie d’objets.
Au sommet de cette hiérarchie, on a l’objet Application qui n’est en réalité que Excel lui-même. L’objet Application contient d’autres objets tels que Addin, Window, Workbook, etc. Chaque objet contenu dans l’objet Application peut contenir d’autres objets. Par exemple, l’objet Workbook contient des objets comme Chart, Name et Worksheet. À leur tour, chacun de ces objets peut contenir encore d’autres objets. Considérez un objet Worksheet, contenu dans un objet Workbook, qui se trouve dans l’objet Application. Certains des objets qui peuvent être contenus dans un objet Worksheet sont Comment, Hyperlink, Name, PivotTable et Range.
1 Les collections d’objets dans la programmation vba
Les collections sont un autre concept clé dans la programmation VBA. Dans la programmation orientée objet, une collection est un objet qui contient un groupe d’objets similaires. Et pour ajouter à la confusion, une collection est elle-même un objet.
Voici quelques exemples de collections couramment utilisées :
■ Workbooks: une collection de tous les objets Workbook actuellement ouverts.
■ Worksheets: une collection de tous les objets Worksheet contenus dans un classeur spécifique.
■ Charts: une collection de tous les objets Chart (feuilles de graphique) contenus dans un objet de classeur particulier.
■ Sheets: une collection de toutes des sheet contenues dans un objet de classeur particulier.
Quelle que soit la collection avec laquelle vous souhaitez travailler, vous pouvez effectuer les opérations suivantes:
■ Faites référence à un objet spécifique dans une collection en utilisant une valeur d’index. Par exemple, pour faire référence au deuxième objet de la collection de feuilles de calcul, utilisez l’une des instructions suivantes :
Worksheets(2).Select
ou
Worksheets("Sheet2").Select
■ Déterminez le nombre d’éléments de la collection à l’aide de la propriété Count. Par exemple, lorsque vous entrez dans la fenêtre Exécution, l’instruction:
?Worksheets.Count
VBA renverra le nombre total de feuilles de calcul dans le classeur actuel.
■ Insérez de nouveaux éléments dans la collection à l’aide de la méthode Add. Par exemple, lorsque vous entrez dans la fenêtre Exécution, l’instruction:
Worksheets.Add
VBA insérera dans le classeur actuel une nouvelle feuille de calcul. La collection Worksheets contient désormais un élément supplémentaire.
■ Parcourez tous les objets de la collection à l’aide de la boucle For Each … Next. Supposons que vous ayez ouvert un classeur contenant cinq feuilles de calcul portant les noms suivants: «Salaire journalier», «Salaire hebdomadaire», «Salaire mensuel», «Salaire annuel» et «Bonus». Pour supprimer les feuilles de calcul contenant le mot «salaire» dans le nom, vous pouvez écrire la procédure suivante:
Sub SuppressionFeuille() Dim maFeuille As Worksheet Application.DisplayAlerts = False For Each maFeuille In Worksheets If InStr(maFeuille.Name, "Salaire") Then maFeuille.Delete End If Next Application.DisplayAlerts = True End Sub
Commentaires
■ L’instruction Application.DisplayAlerts = False est utilisée pour supprimer certaines invites et messages qu’Excel affiche pendant l’exécution du code. Dans ce cas, nous souhaitons supprimer le message de confirmation qu’Excel affiche lorsque les feuilles de calcul sont supprimées.
■ La fonction InStr est très utile pour les comparaisons de chaînes car elle vous permet de trouver une chaîne dans une autre. L’instruction InStr (maFeuille.Name, “salaires”) indique à Excel de déterminer si le nom de la feuille de calcul (stocké dans la variable objet maFeuille) contient la chaîne de caractères “salaires”.
NOTE
Comme vous pouvez le constater, les noms de collections sont tous pluriels.
2 Manipulation des collections
Déclaration et utilisation d’une collection personnalisée
Pour créer une collection définie par l’utilisateur, vous devez commencer par déclarer une variable objet du type Collection:
Dim nom_collection as Collection Set nom_collection = New Collection ou Dim nom_collection As New Collection
Ajout d’objets à une collection personnalisée
Après avoir déclaré l’objet Collection avec le mot-clé Dim, vous pouvez insérer de nouveaux éléments dans la collection à l’aide de la méthode Add. La méthode Add ressemble à ceci:
objet.Add item[, key, before, after]
Vous devez spécifier uniquement l’objet et l’élément (item). L’objet est le nom de la collection. Il s’agit du même nom que celui utilisé dans la déclaration de l’objet Collection. L’élément est l’objet que vous souhaitez ajouter à la collection.
Bien que d’autres arguments soient facultatifs, ils sont très utiles. Il est important de comprendre que les éléments d’une collection se voient attribuer automatiquement des numéros commençant par 1. Cependant, une valeur de clé (key) unique leur est également attribuée. Au lieu d’accéder à un élément spécifique avec un index (1, 2, 3, etc.), vous pouvez attribuer une clé à cet objet au moment où un objet est ajouté à une collection. Par exemple, si vous créez une collection de feuilles personnalisées, vous pouvez utiliser un nom de feuille comme clé. Pour identifier une personne dans une collection d’étudiants ou d’employés, vous pouvez utiliser leur numéro d’identification comme clé.
Si vous souhaitez spécifier la position de l’objet dans la collection, vous devez utiliser un argument avant (before) ou après (after). L’argument avant est l’objet avant lequel le nouvel objet est ajouté. L’argument after est l’objet après lequel le nouvel objet est ajouté.
Suppression d’objets d’une collection personnalisée
La suppression d’un élément d’une collection personnalisée est aussi simple que l’ajout d’un élément. Pour supprimer un objet, utilisez la méthode Remove au format suivant:
objet.Remove item
L’objet est le nom de la collection personnalisée qui contient l’objet que vous souhaitez supprimer. L’élément (item) est l’objet que vous souhaitez supprimer de la collection.
3 Se référant aux objets dans une collection
Faire référence à un objet est important car vous devez identifier l’objet avec lequel vous voulez travailler. Pour référencer un seul objet d’une collection, vous devez mettre le nom ou le numéro d’index de l’objet entre parenthèses après le nom de la collection, comme ceci :
Worksheets ("Feuil1")
Notez que le nom de la feuille est entre guillemets. Si vous omettez les guillemets, Excel ne pourra pas identifier l’objet. Si Feuil1 est la première (ou la seule) feuille de la collection, vous pouvez également utiliser la référence suivante:
Worksheets (1)
Dans ce cas, le nombre n’est pas entre guillemets. Si vous faites référence à un objet en utilisant son nom, utilisez des guillemets. Si vous faites référence à un objet en utilisant son numéro d’index, utilisez un nombre simple sans guillemets. Une autre collection, appelée sheets, contient toutes les feuilles (feuilles de calcul et feuilles de graphique) dans un classeur. Si Feuil1 est la première feuille dans le classeur, vous pouvez le référencer comme
Sheets (1)
DIFFERENCE ENTRE WORKSHEET ET SHEET
Worksheet représente une feuille de calcul, alors que Sheet représente n’importe quelle feuille (feuille de calcul et feuille graphique). Ainsi, si dans un classeur, vous avez trois feuilles de calcul et une feuille de graphique, alors vous avez 3 worksheet et 4 sheet. La collection Worksheets est un sous-ensemble de la collection Sheets.
4 Naviguer dans la hiérarchie
Le modèle objet de toute application VBA est hiérarchisé. Le résultat de cette hiérarchie est ce qui détermine la syntaxe appropriée pour vos macros VBA.
Par exemple, si vous voulez entrer le mot “Bonjour” dans la cellule A1 de la feuille de calcul Feuil1 du classeur dans lequel vous travaillez actuellement, la ligne de code à gérer pourrait être la suivante:
Application.Workbooks("Classeur1.xlsx").Worksheets("Feuil1").Range("A1").Value = "Bonjour"
REMARQUE
Dans les versions antérieures à Excel 2007, le nom de fichier dans la procédure doit être :
Classeur1.xls et nom Classeur1.xlsx.
Workbook, Worksheet, Range et Value sont des expressions anglaise qui signifie en français, respectivement Classeur, Feuille de calcul, Plage de cellule et Valeur. Ce code signifie littéralement que nous avons saisi la valeur Bonjour dans la cellule A1 (Range(“A1”)), contenue dans la feuille Feuil1 (Worksheets(“Feuil1”)), qui se trouve actuellement dans un classeur Classeur1 (Workbooks(“Classeur1.xlsx “).), lui-même contenu dans Excel (Application). Si vous deviez qualifier complètement chaque référence d’objet que vous faites, votre code deviendrait assez long, et il pourrait être plus difficile à lire.
VBA est un langage intelligent. Il sait que vous travaillez dans Excel et il n’est plus donc nécessaire de le rappeler avec le mot Application. Le code précèdent devient :
Workbooks("Classeur1.xlsx ").Worksheets("Feuil1").Range("A1").Value = "Bonjour"
Si vous êtes sûr que Classeur1.xlsx est le classeur actif, vous pouvez également omettre cette référence et le code devient :
Worksheets("Feuil1").Range("A1").Value = "Bonjour"
Cela peut être raccourci davantage si vous travaillez sur Feuil1 lorsque la ligne de code est exécutée. Si l’objet parent Worksheet n’est pas spécifié, l’hypothèse par défaut de VBA est que vous voulez que la feuille active reçoive le mot “Bonjour” dans la cellule A1, et dans ce scénario, la ligne de code serait simplement ceci:
Range("A1").Value = "Bonjour"
NOTE
Excel n’a pas d’objet Cell (cellule). Une cellule est un élément de l’objet Range qui signifie plage de cellules.