Une collection est un objet qui contient un certain nombre d'éléments similaires. Ceux-ci sont facilement accessibles et manipulés, même s'il y a un grand nombre d'éléments dans la collection.
Il existe déjà des collections intégrées avec Excel VBA. Un exemple est la collection Sheets. Pour chaque feuille de calcul d'un classeur, il existe un élément dans la collection Sheets.
Les collections intégrées ont beaucoup plus de propriétés et de méthodes à votre disposition, mais celles-ci ne sont pas disponibles dans vos propres collections que vous créez.
Par exemple, vous pouvez utiliser la collection pour obtenir des informations sur une feuille de calcul particulière. Par exemple, vous pouvez voir le nom de la feuille de calcul et également si elle est visible ou non. En utilisant une boucle For Each, vous pouvez parcourir chaque feuille de calcul de la collection.
| 1234567 | Feuilles de sous-test()Feuille de travail Dim Sh AsPour chaque Sh en feuillesMsgBox Sh.NameMsgBox Sh.VisibleSuivant ShFin du sous-marin |
Vous pouvez également adresser une feuille de calcul spécifique dans la collection à l'aide de la valeur d'index ou du nom réel de la feuille de calcul :
| 12 | Feuilles MsgBox(1).NomMsgBox Sheets("Sheet1").Nom |
Au fur et à mesure que des feuilles de calcul sont ajoutées ou supprimées, la collection Sheets augmente ou diminue en taille.
Notez qu'avec les collections VBA, le numéro d'index commence par 1 et non par 0
Collections versus tableaux
Les tableaux et les collections sont similaires dans leurs fonctions en ce sens qu'ils sont tous deux des méthodologies qui permettent de stocker une grande quantité de données qui peuvent ensuite être facilement référencées à l'aide de code. Cependant, ils présentent un certain nombre de différences dans leur mode de fonctionnement :
- Les tableaux sont multidimensionnels alors que les collections ne sont qu'à une seule dimension. Vous pouvez dimensionner un tableau avec plusieurs dimensions, par ex.
| 1 | Dim MyArray (10, 2) en tant que chaîne |
Cela crée un tableau de 10 lignes avec 2 colonnes, presque comme une feuille de calcul. Une collection est en fait une seule colonne. Le tableau est utile si vous avez besoin de stocker un certain nombre d'éléments de données qui se rapportent les uns aux autres, par ex. nom et adresse. Le nom serait dans la première dimension du tableau et l'adresse dans la deuxième dimension.
- Lorsque vous remplissez votre tableau, vous avez besoin d'une ligne de code distincte pour mettre une valeur dans chaque élément du tableau. Si vous aviez un tableau à deux dimensions, vous auriez en fait besoin de 2 lignes de code - une ligne pour adresser la première colonne et une ligne pour adresser la deuxième colonne. Avec l'objet Collection, vous utilisez simplement la méthode Add pour que le nouvel élément soit simplement ajouté à la collection et que la valeur de l'index soit automatiquement ajustée en conséquence.
- Si vous devez supprimer une donnée, c'est plus compliqué dans le tableau. Vous pouvez définir les valeurs d'un élément sur une valeur vide, mais l'élément lui-même existe toujours dans le tableau. Si vous utilisez une boucle For Next pour parcourir le tableau, la boucle renverra une valeur vide, qui nécessitera un codage pour s'assurer que la valeur vide est ignorée. Dans une collection, vous utilisez les méthodes Add ou Remove, et toute l'indexation et le redimensionnement sont automatiquement pris en charge. L'élément qui a été supprimé disparaît complètement. Les tableaux sont utiles pour une taille fixe de données, mais les collections sont meilleures lorsque la quantité de données est susceptible de changer.
- Les collections sont en lecture seule alors que les valeurs de tableau peuvent être modifiées à l'aide de VBA. Avec une collection, vous devrez d'abord supprimer la valeur à modifier, puis ajouter la nouvelle valeur modifiée.
- Dans un tableau, vous ne pouvez utiliser qu'un seul type de données pour les éléments qui est défini lorsque vous dimensionnez le tableau. Cependant, dans le tableau, vous pouvez utiliser des types de données personnalisés que vous avez vous-même conçus. Vous pourriez avoir une structure de tableau très compliquée utilisant un type de données personnalisé qui à son tour a plusieurs types de données personnalisés en dessous. Dans une collection, vous pouvez ajouter des types de données d'utilisation pour chaque élément. Vous pouvez avoir une valeur numérique, une date ou une chaîne - l'objet de collection prendra n'importe quel type de données. Si vous essayiez de mettre une valeur de chaîne dans un tableau dimensionné comme numérique, cela produirait un message d'erreur.
- Les collections sont généralement plus faciles à utiliser que les tableaux. En termes de codage, lorsque vous créez un objet de collection, il n'a que deux méthodes (Add et Remove) et deux propriétés (Count et Item), donc l'objet n'est en aucun cas compliqué à programmer.
- Les collections peuvent utiliser des clés pour localiser les données. Les tableaux n'ont pas cette fonction et nécessitent un code en boucle pour parcourir le tableau afin de trouver des valeurs spécifiques.
- La taille d'un tableau doit être définie lors de sa création. Vous devez avoir une idée de la quantité de données qu'il va stocker. Si vous devez augmenter la taille du tableau, vous pouvez utiliser « ReDim » pour le redimensionner, mais vous devez utiliser le mot-clé « Préserver » si vous ne voulez pas perdre les données déjà contenues dans le tableau. Il n'est pas nécessaire de définir une taille de collection. Il grandit et se rétrécit automatiquement au fur et à mesure que des éléments sont ajoutés ou supprimés.
Portée d'un objet de collection
En termes de portée, l'objet de collection n'est disponible que lorsque le classeur est ouvert. Il n'est pas enregistré lorsque le classeur est enregistré. Si le classeur est rouvert, la collection doit être recréée à l'aide du code VBA.
Si vous souhaitez que votre collection soit disponible pour tout le code de votre module de code, vous devez déclarer l'objet de collection dans la section Declare en haut de la fenêtre du module
Cela garantira que tout votre code dans ce module peut accéder à la collection. Si vous souhaitez qu'un module de votre classeur accède à la collection, définissez-le en tant qu'objet global
| 1 | Global MyCollection comme nouvelle collection |
Création d'une collection, ajout d'articles et accès aux articles
Un simple objet de collection peut être créé dans VBA à l'aide du code suivant :
| 123456 | Sous-CréerCollection()Dim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2"MaCollection.Ajouter "Item3"Fin du sous-marin |
Le code dimensionne un nouvel objet appelé 'MyCollection', puis les lignes de code suivantes utilisent la méthode Add pour ajouter 3 nouvelles valeurs.
Vous pouvez ensuite utiliser du code pour parcourir votre collection pour accéder aux valeurs
| 123 | Pour chaque élément de MyCollectionÉlément MsgBoxProchain point |
Vous pouvez également parcourir votre collection à l'aide d'une boucle For Next :
| 123 | Pour n = 1 à MyCollection.CountMsgBox MaCollection(n)Suivant n |
Le code obtient la taille de la collection en utilisant la propriété Count, puis l'utilise en commençant une valeur 1 pour indexer chaque élément
La boucle For Each est plus rapide que la boucle For Next mais elle ne fonctionne que dans un sens (indice bas à haut). La boucle For Next Loop a l'avantage de pouvoir utiliser une direction différente (indice élevé à faible) et vous pouvez également utiliser la méthode Step pour modifier l'incrément. Ceci est utile lorsque vous souhaitez supprimer plusieurs éléments car vous devrez exécuter la suppression de la fin de la collection au début car l'index changera au fur et à mesure des suppressions.
La méthode Add dans une collection a 3 paramètres facultatifs - Key, Before et After
Vous pouvez utiliser les paramètres « Avant » et « Après » pour définir la position de votre nouvel article par rapport aux autres déjà dans la collection
Cela se fait en spécifiant le numéro d'index auquel vous souhaitez que votre nouvel élément soit relatif.
| 123456 | Sous-CréerCollection()Dim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Article2", , 1MaCollection.Ajouter "Item3"Fin du sous-marin |
Dans cet exemple, « Item2 » a été spécifié pour être ajouté avant le premier élément indexé dans la collection (qui est « Item1 »). Lorsque vous parcourez cette collection, "Item2" apparaîtra en premier, suivi de "Item1" et "Item3"
Lorsque vous spécifiez un paramètre « Avant » ou « Après », la valeur d'index est automatiquement ajustée dans la collection de sorte que « Item2 » devienne la valeur d'index de 1 et « Item1 » est déplacé vers une valeur d'index de 2
Vous pouvez également utiliser le paramètre « Clé » pour ajouter une valeur de référence que vous pouvez utiliser pour identifier l'élément de collection. Notez qu'une valeur de clé doit être une chaîne et doit être unique dans la collection.
| 1234567 | Sous-CréerCollection()Dim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2", "MaClé"MaCollection.Ajouter "Item3"MsgBox MaCollection("MaClé")Fin du sous-marin |
« Item2 » a reçu une valeur « Clé » de « MyKey » afin que vous puissiez vous référer à cet élément en utilisant la valeur de « MyKey » au lieu du numéro d'index (2)
Notez que la valeur « Clé » doit être une valeur de chaîne. Il ne peut pas s'agir d'un autre type de données. Notez que la collection est en lecture seule et que vous ne pouvez pas mettre à jour la valeur de la clé une fois qu'elle a été définie. De plus, vous ne pouvez pas vérifier si une valeur clé existe pour un élément spécifique de la collection ou afficher la valeur clé, ce qui est un peu un inconvénient.
Le paramètre « Key » a l'avantage supplémentaire de rendre votre code plus lisible, surtout s'il est remis à un collègue pour le prendre en charge, et vous n'avez pas à parcourir toute la collection pour trouver cette valeur. Imaginez si vous aviez une collection de 10 000 articles à quel point il serait difficile de référencer un article spécifique !
Supprimer un élément d'une collection
Vous pouvez utiliser la méthode « Supprimer » pour supprimer des éléments de votre collection.
| 1 | MaCollection.Supprimer (2) |
Malheureusement, il n'est pas facile si la collection comporte un grand nombre d'éléments de déterminer l'index de l'élément que vous souhaitez supprimer. C'est là que le paramètre « Clé » est utile lors de la création de la collection
| 1 | MaCollection.Supprimer ("MaClé") |
Lorsqu'un élément est supprimé d'une collection, les valeurs d'index sont automatiquement réinitialisées tout au long de la collection. C'est là que le paramètre « Clé » est si utile lorsque vous supprimez plusieurs éléments à la fois. Par exemple, vous pouvez supprimer l'index d'élément 105, et instantanément l'index d'élément 106 devient l'index 105, et tout ce qui se trouve au-dessus de cet élément a sa valeur d'index déplacée vers le bas. Si vous utilisez le paramètre Key, vous n'avez pas à vous soucier de la valeur d'index à supprimer.
Pour supprimer tous les éléments de la collection et créer une nouvelle collection, vous utilisez à nouveau l'instruction Dim qui crée une collection vide.
| 1 | Dim MyCollection comme nouvelle collection |
Pour supprimer complètement l'objet de collection réel, vous pouvez définir l'objet sur rien
| 1 | Définir MaCollection = Rien |
Ceci est utile si la collection n'est plus requise par votre code. La définition de l'objet de collection sur rien supprime toute référence à celui-ci et libère la mémoire qu'il utilisait. Cela peut avoir des implications importantes sur la vitesse d'exécution de votre code, si un objet volumineux se trouve en mémoire et n'est plus requis.
Compter le nombre d'articles dans une collection
Vous pouvez facilement connaître le nombre d'articles dans votre collection en utilisant la propriété 'Count'
| 1 | MsgBox MaCollection.Count |
Vous utiliseriez cette propriété si vous utilisiez une boucle For Next pour parcourir la collection, car elle vous fournira la limite supérieure du numéro d'index.
Collection de tests pour une valeur spécifique
Vous pouvez parcourir une collection pour rechercher une valeur spécifique pour un élément à l'aide d'une boucle For Each
| 123456789101112 | Sous-RechercheCollection()Dim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2"MaCollection.Ajouter "Item3"Pour chaque élément de MyCollectionSi Article = "Article2" AlorsÉlément MsgBox & " Trouvé "Fin siProchainFin du sous-marin |
Le code crée une petite collection, puis parcourt celle-ci à la recherche d'un élément appelé "item2". S'il est trouvé, il affiche une boîte de message indiquant qu'il a trouvé l'élément spécifique
L'un des inconvénients de cette méthodologie est que vous ne pouvez pas accéder à la valeur de l'index ou à la valeur de la clé
Si vous utilisez plutôt une boucle For Next, vous pouvez utiliser le compteur For Next pour obtenir la valeur de l'index, bien que vous ne puissiez toujours pas obtenir la valeur « Clé ».
| 123456789101112 | Sous-RechercheCollection()Dim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2"MaCollection.Ajouter "Item3"Pour n = 1 à MyCollection.CountSi MaCollection.Item(n) = "Item2" AlorsMsgBox MyCollection.Item(n) & " trouvé à la position d'index " & nFin siSuivant nFin du sous-marin |
Le compteur For Next (n) fournira la position de l'index
Tri d'une collection
Il n'y a pas de fonctionnalité intégrée pour trier une collection, mais en utilisant une réflexion « out of the box », le code peut être écrit pour effectuer un tri, en utilisant la fonction de tri de feuille de calcul d'Excel. Ce code utilise une feuille de calcul vierge appelée « SortSheet » pour effectuer le tri proprement dit.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sous SortCollection()Dim MyCollection comme nouvelle collectionCompteur dim aussi long'Construire une collection avec des articles commandés au hasardMaCollection.Ajouter "Item5"MaCollection.Ajouter "Item2"MaCollection.Ajouter "Item4"MaCollection.Ajouter "Item1"MaCollection.Ajouter "Item3"« Capturez le nombre d'articles de la collection pour une utilisation futureCompteur = MaCollection.Count'Itérer dans la collection en copiant chaque élément dans une cellule consécutive sur'SortSheet' (colonne A)Pour n = 1 à MyCollection.CountSheets("SortSheet").Cells(n, 1) = MaCollection(n)Suivant n'Activez la feuille de tri et utilisez la routine de tri Excel pour trier les données par ordre croissantSheets("SortSheet").ActiverPlage("A1:A" & MyCollection.Count).SélectionnezActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.ClearActiveWorkbook.Worksheets("SortSheet"). Sort.SortFields.Add2 Clé :=Plage( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlTriNormalAvec ActiveWorkbook.Worksheets("SortSheet").Sort.SetRange Range("A1:A5").En-tête = xlDevinez.MatchCase = Faux.Orientation = xlHautVersBas.SortMethod = xlPinYin.AppliquerTerminer par« Supprimez tous les éléments de la collection - notez que cette boucle For Next Loop s'exécute dans l'ordre inversePour n = MyCollection.Count To 1 Step -1MaCollection.Supprimer (n)Suivant n'Copier les valeurs des cellules dans l'objet de collection vide en utilisant la valeur stockée (compteur) pour la 'bouclePour n = 1 Au compteurMaCollection.Add Sheets("SortSheet").Cells(n, 1).ValueSuivant n"Itinéraire dans la collection pour prouver l'ordre dans lequel les articles sont maintenant dansPour chaque élément de MyCollectionÉlément MsgBoxProchain point'Effacer la feuille de calcul (feuille de tri) - si nécessaire, supprimez-la égalementSheets("SortSheet").Range(Cells(1, 1), Cells(Counter, 1)).ClearFin du sous-marin |
Ce code crée d'abord une collection avec les éléments ajoutés dans un ordre aléatoire. Il les copie ensuite dans la première colonne d'une feuille de calcul (SortSheet).
Le code utilise ensuite la fonction de tri Excel pour trier les données de la colonne par ordre croissant. Le code peut également être modifié pour trier par ordre décroissant.
La collection est ensuite vidée de ses données à l'aide d'une boucle For Next. Notez que l'option step est utilisée pour qu'elle s'efface de la fin de la collection au début. En effet, lorsqu'il s'efface, les valeurs d'index sont réinitialisées, s'il est effacé depuis le début, il ne s'effacera pas correctement (l'index 2 deviendrait l'index 1)
Enfin, en utilisant une autre boucle For Next, les valeurs des éléments sont transférées dans la collection vide
Un autre For Each Loop prouve que la collection est maintenant dans un bon ordre croissant.
Malheureusement, cela ne traite pas des valeurs de clé qui peuvent avoir été saisies à l'origine, car les valeurs de clé ne peuvent pas être lues
Passer une collection à une sous/fonction
Une collection peut être passée à un sous ou à une fonction de la même manière que tout autre paramètre
| 1 | Fonction MyFunction(ByRef MyCollection as Collection) |
Il est important de transmettre la collection en utilisant 'ByRef'. Cela signifie que la collection originale est utilisée. Si la collection est transmise à l'aide de « ByVal », cela crée une copie de la collection qui peut avoir des répercussions malheureuses
Si une copie est créée à l'aide de « ByVal », alors tout ce qui modifie la collection au sein de la fonction ne se produit que sur la copie et non sur l'original. Par exemple, si au sein de la fonction, un nouvel élément est ajouté à la collection, celui-ci n'apparaîtra pas dans la collection d'origine, ce qui créera un bug dans votre code.
Renvoyer une collection à partir d'une fonction
Vous pouvez renvoyer une collection à partir d'une fonction de la même manière que n'importe quel objet. Vous devez utiliser le mot-clé Set
| 12345 | Sous ReturnFromFunction()Dim MyCollection en tant que collectionDéfinir MyCollection = PopulateCollectionMsgBox MaCollection.CountFin du sous-marin |
Ce code crée une sous-routine qui crée un objet appelé "MyCollection", puis utilise le mot-clé "Set" pour appeler efficacement la fonction pour remplir cette collection. Une fois cela fait, il affiche une boîte de message pour afficher le nombre de 2 éléments
| 1234567 | Fonction PopulateCollection() en tant que collectionDim MyCollection comme nouvelle collectionMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2"Définir PopulateCollection = MyCollectionFonction de fin |
La fonction PopulateCollection crée un nouvel objet de collection et le remplit avec 2 éléments. Il renvoie ensuite cet objet à l'objet de collection créé dans la sous-routine d'origine.
Conversion d'une collection en tableau
Vous pouvez convertir votre collection en tableau. Vous voudrez peut-être stocker les données là où elles peuvent être modifiées et manipulées. Ce code crée une petite collection puis la transfère dans un tableau
Notez que l'index de la collection commence à 1 alors que l'index du tableau commence à 0. Alors que la collection a 3 éléments, le tableau n'a besoin d'être dimensionné qu'à 2 car il y a un élément 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray()Dim MyCollection comme nouvelle collectionDim MyArray(2) en tant que chaîneMaCollection.Ajouter "Item1"MaCollection.Ajouter "Item2"MaCollection.Ajouter "Item3"Pour n = 1 à MyCollection.CountMonTableau(n - 1) = MaCollection(n)Suivant nPour n = 0 à 2MsgBox MyArray(n)Suivant nFin du sous-marin |
Conversion d'un tableau en une collection
Vous voudrez peut-être convertir un tableau en une collection. Par exemple, vous souhaiterez peut-être accéder aux données d'une manière plus rapide et plus élégante qu'en utilisant du code pour obtenir un élément de tableau.
Gardez à l'esprit que cela ne fonctionnera que pour une seule dimension du tableau car la collection n'a qu'une seule dimension
| 123456789101112131415 | Sub ConvertArrayIntoCollection()Dim MyCollection comme nouvelle collectionDim MyArray(2) en tant que chaîneMonTableau(0) = "élément1"MonTableau(1) = "Élément2"MonTableau(2) = "Élément3"Pour n = 0 à 2MaCollection.Ajouter MonTableau(n)Suivant nPour chaque élément de MyCollectionÉlément MsgBoxProchain pointFin du sous-marin |
Si vous souhaitez utiliser un tableau multidimensionnel, vous pouvez concaténer les valeurs du tableau pour chaque ligne du tableau en utilisant un caractère délimiteur entre les dimensions du tableau, de sorte que lors de la lecture de la valeur de la collection, vous puissiez utiliser par programme le caractère délimiteur pour séparer les valeurs.
Vous pouvez également déplacer les données dans la collection en supposant que la valeur de la première dimension est ajoutée (index 1), puis la valeur de la dimension suivante est ajoutée (index 2) et ainsi de suite.
Si le tableau avait, disons, 4 dimensions, chaque quatrième valeur de la collection serait un nouvel ensemble de valeurs.
Vous pouvez également ajouter des valeurs de tableau à utiliser comme clés (à condition qu'elles soient uniques), ce qui ajouterait un moyen facile de localiser des données spécifiques.
