VBA Excel – Collections

Ce tutoriel montre comment utiliser les collections en VBA.

Qu’est-ce qu’une Collection?

Une collection est un objet qui contient un certain nombre d’éléments similaires. Il est facile d’y accéder et de les manipuler, même si la collection contient un grand nombre d’éléments.

Il existe déjà des collections intégrées à Excel VBA. La collection Worksheets (Feuilles) en est un exemple. Pour chaque feuille de calcul d’un classeur, il existe un élément dans la collection Worksheets.

Les collections intégrées disposent de beaucoup plus de propriétés et de méthodes, mais celles-ci ne sont pas disponibles dans les collections que vous créez vous-même.

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 savoir si elle est visible ou non. En utilisant une boucle For Each, vous pouvez itérer à travers chaque feuille de calcul de la collection.

Sub TestCollectionWorksheets()
    Dim fc As Worksheet
    For Each fc In Sheets
        MsgBox fc.Name
        MsgBox fc.Visible
    Next fc
End Sub

Vous pouvez également vous adresser à une feuille de calcul spécifique dans la collection en utilisant la valeur de l’index ou le nom réel de la feuille de calcul :

MsgBox Sheets(1).Name
MsgBox Sheets("Feuil1").Name

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.

Différences entre les Collections et les Tableaux

Les Tableaux et les collections ont des fonctions similaires dans la mesure où il s’agit de deux méthodologies permettant de stocker une grande quantité de données qui peuvent ensuite être facilement référencées à l’aide du code. Ils présentent toutefois un certain nombre de différences dans leur mode de fonctionnement :

1. Les tableaux peuvent être multidimensionnels alors que les collections ne sont qu’unidimensionnelles. Vous pouvez dimensionner un tableau à plusieurs dimensions, par exemple :

Dim MonTableau(10, 2) As String

Cela crée un tableau de 10 lignes avec 2 colonnes, presque comme une feuille de calcul. Une collection est effectivement une colonne unique. Le tableau est utile si vous avez besoin de stocker un certain nombre d’éléments de données qui sont liés les uns aux autres, par exemple le nom et l’adresse. Le nom sera placé dans la première dimension du tableau et l’adresse dans la seconde.

2. Lorsque vous remplissez votre tableau, vous avez besoin d’une ligne de code distincte pour placer une valeur dans chaque élément du tableau. Si vous aviez un tableau à deux dimensions, vous auriez en fait besoin de deux lignes de code – une ligne pour adresser la première colonne et une ligne pour adresser la deuxième colonne. Avec l’objet Collection, il suffit d’utiliser 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.
3. Si vous devez supprimer un élément de données, c’est plus compliqué dans un 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 itérer dans le tableau, la boucle renverra une valeur vide, ce qui nécessitera une logique conditionnelle pour s’assurer que la valeur vide est ignorée. Dans une collection, vous utilisez les méthodes Add ou Remove, et 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 les données de taille fixe, mais les collections sont préférables lorsque la quantité de données est susceptible de changer.
4. Les collections sont en lecture seule alors que les valeurs des tableaux peuvent être modifiées à l’aide de VBA. Avec une collection, vous devez d’abord supprimer la valeur à modifier, puis ajouter la nouvelle valeur modifiée.
5. 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. Toutefois, dans un tableau, vous pouvez utiliser des types de données personnalisés que vous avez conçus vous-même. Vous pouvez avoir une structure de tableau très complexe utilisant un type de données personnalisé qui, à son tour, possède plusieurs types de données personnalisés en dessous de lui. Dans une collection, vous pouvez ajouter des types de données pour chaque élément. Il peut s’agir d’une valeur numérique, d’une date ou d’une chaîne de caractères – l’objet collection accepte tous les types de données. Si vous essayez de placer une chaîne de caractères dans un tableau dont le type a été défini comme étant numérique, vous obtiendrez un message d’erreur.
6. Les collections sont généralement plus faciles à utiliser que les tableaux. En termes de programmation, lorsque vous créez un objet collection, il ne possède que deux méthodes (Add et Remove) et deux propriétés (Count et Item), de sorte que l’objet n’est en aucun cas compliqué à utiliser dans votre code.
7. Les collections peuvent utiliser des clés pour localiser les données. Les tableaux ne disposent pas de cette fonction et nécessitent généralement une boucle pour itérer dans le tableau afin de trouver des valeurs spécifiques.
8. 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é ‘Preserve’ si vous ne voulez pas perdre les données déjà contenues dans le tableau. Il n’est pas nécessaire de définir la taille d’une collection. Elle croît et décroît automatiquement lorsque des éléments sont ajoutés ou supprimés.

La Portée d’un Objet Collection

En termes de portée, l’objet collection n’est disponible que lorsque le classeur est ouvert. Il n’est pas sauvegardé lorsque le classeur est enregistré. Si le classeur est rouvert, la collection doit être recréée à l’aide du code VBA.

Si vous voulez qu’une collection soit disponible dans l’entièreté de votre module de code, vous devez déclarer l’objet collection dans la section « déclarations » en haut de la fenêtre du module

Cela garantit que tout votre code dans ce module peut accéder à la collection. Si vous souhaitez que tous les modules de votre classeur puissent accéder à la collection, définissez-la en tant qu’objet global

Global MaCollection as new Collection

Création d’une Collection, Ajout d’Éléments et Accès aux Éléments

Un objet collection simple peut être créé en VBA à l’aide du code suivant :

Sub CréerCollection()

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2"
MaCollection.Add "Élément 3"

End Sub

Le code dimensionne un nouvel objet appelé « MaCollection », puis les lignes de code suivantes utilisent la méthode Add pour ajouter 3 nouvelles valeurs. Vous pouvez ensuite utiliser du code pour itérer dans votre collection afin d’accéder aux valeurs

For Each Élément In MyCollection
    MsgBox Élement
Next Élément

Vous pouvez également parcourir votre collection en utilisant une boucle For Next :

For n = 1 To MaCollection.Count
    MsgBox MaCollection(n)
Next n

Le code obtient la taille de la collection en utilisant la propriété Count et l’utilise ensuite pour indexer chaque élément en commençant par la valeur 1.

La boucle For Each est plus rapide que la boucle For Next, mais elle ne fonctionne que dans un sens (de l’indice le plus bas à l’indice le plus haut). La boucle For Next présente l’avantage de pouvoir utiliser un sens différent (de l’indice haut vers l’indice bas) et d’utiliser la méthode Step pour modifier l’incrément. Cette méthode 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, étant donné que l’indice changera au fur et à mesure des suppressions.

La méthode Add dans une collection possède 3 paramètres optionnels : Key, Before et After

Vous pouvez utiliser les paramètres « Before » (Avant) et « After » (Après) pour définir la position de votre nouvel élément par rapport aux autres éléments déjà présents dans la collection.

Pour ce faire, vous devez spécifier le numéro d’index auquel vous souhaitez que votre nouvel élément soit relatif.

Sub CréerCollection2()

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2", , 1
MaCollection.Add "Élément 3"

End Sub

Dans cet exemple, il est spécifié que l’élément 2 doit être ajouté avant le premier élément indexé de la collection (qui est l’élément 1). Lorsque vous parcourez cette collection, l’élément 2 apparaîtra en premier, suivi de l’élément 1 et de l’élément 3.

Lorsque vous spécifiez un paramètre « Before » ou « After », la valeur de l’index est automatiquement ajustée dans la collection de sorte que l’élément 2 devienne la valeur d’index 1 et que l’élément 1 soit déplacé à la valeur d’index 2.

Vous pouvez également utiliser le paramètre « Key » pour ajouter une valeur de référence que vous pouvez utiliser pour identifier l’élément de la collection. Notez qu’une valeur clé doit être une chaîne de caractères et doit être unique dans la collection.

Sub CréerCollectionClé()

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2", "MaClé"
MaCollection.Add "Élément 3"

MsgBox MaCollection("MaClé")

End Sub

Une valeur clé correspondant à « MaClé » a été attribuée à l’élément 2 afin que vous puissiez faire référence à cet élément en utilisant la valeur « MaClé » au lieu du numéro d’index (2).

Notez que la valeur du paramètre « Key » doit être une chaîne de caractères. Elle ne peut pas être 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 même, vous ne pouvez pas vérifier si une valeur de clé existe pour un élément spécifique de la collection ou afficher la valeur de clé, ce qui est un peu un inconvénient.

Le paramètre « Key » présente l’avantage supplémentaire de rendre votre code plus lisible, en particulier s’il est remis à un collègue, et vous n’avez pas besoin d’itérer dans toute la collection pour trouver cette valeur. Imaginez que vous ayez une collection de 10 000 éléments, il serait difficile de faire référence à un élément spécifique!

Suppression d’un Élément dans une Collection

Vous pouvez utiliser la méthode « Remove » pour supprimer des éléments de votre collection.

MaCollection.Remove(2)

Malheureusement, si la collection comporte un grand nombre d’éléments, il n’est pas facile de déterminer l’index de l’élément que vous souhaitez supprimer. C’est là que le paramètre « Key » s’avère utile lors de la création de la collection

MaCollection.Remove("MaClé")

Lorsqu’un élément est supprimé d’une collection, les valeurs d’index sont automatiquement réinitialisées dans toute la collection. C’est pourquoi le paramètre « Key » est si utile lorsque vous supprimez plusieurs éléments à la fois. Par exemple, vous pouvez supprimer l’élément 105, et instantanément l’élément 106 devient l’élément 105, et tout ce qui se trouve au-dessus de cet élément voit sa valeur d’index déplacée vers le bas. Si vous utilisez le paramètre « Key », vous n’avez pas à vous préoccuper de savoir quelle valeur d’index doit être supprimée.

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.

Dim MaCollection As New Collection

Pour supprimer complètement l’objet de collection actuel, vous pouvez lui attribuer la valeur « Nothing » (Rien)

Set MaCollection = Nothing

Cette méthode est utile si la collection n’est plus requise par votre code. La mise à zéro de l’objet de collection supprime toute référence à celui-ci et libère la mémoire qu’il utilisait. Cela peut avoir des conséquences importantes sur la vitesse d’exécution de votre code, si un objet volumineux se trouve en mémoire alors qu’il n’est plus nécessaire.

Compter le Nombre d’Éléments dans une Collection 

Vous pouvez facilement trouver le nombre d’éléments dans votre collection en utilisant la propriété ‘Count’

MsgBox MaCollection.Count

Vous utiliserez cette propriété si vous utilisez une boucle For Next pour itérer dans la collection, car elle vous fournira la limite supérieure de l’indice.

Rechercher une Valeur Spécifique dans une Collection 

Vous pouvez itérer dans une collection pour rechercher une valeur spécifique pour un élément en utilisant une boucle For Each

Sub RechercheCollection()

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2"
MaCollection.Add "Élément 3"

For Each Élément In MaCollection
    If Élément = "Élément 2" Then
        MsgBox Élément & " Trouvé!"
    End If
Next Élément

End Sub

Ce code crée une petite collection, puis la parcourt en itérant à la recherche d’un élément appelé « Élément 2 ». S’il le trouve, il affiche un message indiquant qu’il a trouvé l’élément en question.

L’un des inconvénients de cette méthode est que vous ne pouvez pas accéder à la valeur de l’index ou à la valeur de la clé.

Si vous utilisez une boucle For Next à la place, vous pouvez utiliser le compteur For Next pour obtenir la valeur de l’index, mais vous ne pouvez toujours pas obtenir la valeur de la « clé ».

Sub RechercheCollection2()

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2"
MaCollection.Add "Élément 3"

For n = 1 To MaCollection.Count
    If MaCollection.Élément(n) = "Élément 2" Then
        MsgBox MaCollection.Élément(n) & " trouvé à la position d'index " & n
    End If
Next n

End Sub

Le compteur For Next (n) fournira la position de l’index.

Trier une Collection

Il n’existe pas de fonctionnalité intégrée pour trier une collection, mais il est possible d’écrire du code pour effectuer un tri en utilisant la fonction de tri des feuilles de calcul d’Excel. Ce code utilise une feuille de calcul vierge appelée « FeuilleDeTri » pour effectuer le triage.

Sub TriageCollection()

Dim MaCollection As New Collection
Dim Compteur As Long

'Créez une collection avec des éléments en ordre aléatoire
MaCollection.Add "Élément 5"
MaCollection.Add "Élément 2"
MaCollection.Add "Élément 4"
MaCollection.Add "Élément 1"
MaCollection.Add "Elément 3"

'Capture du nombre d'éléments dans la collection pour une utilisation ultérieure
Compteur = MaCollection.Count

'Itération dans la collection en copiant chaque élément dans une cellule consécutive de la 'Feuille de tri' (colonne A)
For n = 1 To MaCollection.Count
    Sheets("FeuilleDeTri").Cells(n, 1) = MaCollection(n)
Next n

'Activez la feuille de tri et utilisez la routine de tri Excel pour trier les données dans l'ordre croissant
Sheets("FeuilleDeTri").Activate
Range("A1:A" & MaCollection.Count).Select
    ActiveWorkbook.Worksheets("FeuilleDeTri").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("FeuilleDeTri").Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
        
    With ActiveWorkbook.Worksheets("FeuilleDeTri").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Supprimez tous les éléments de la collection - notez que cette boucle For Next fonctionne dans l'ordre inverse
For n = MaCollection.Count To 1 Step -1
     MaCollection.Remove (n)
Next n

'Recopie les valeurs des cellules dans l'objet de collection vide en utilisant la valeur stockée (Compteur) pour la 'boucle
For n = 1 To Compteur
    MaCollection.Add Sheets("FeuilleDeTri").Cells(n, 1).Value
Next n

'Itération dans la collection pour vérifier l'ordre dans lequel les éléments se trouvent maintenant
For Each Élément In MaCollection
    MsgBox Élément
Next Élément

'Effacez la feuille de calcul (feuille de tri) - si nécessaire, supprimez-la également
Sheets("FeuilleDeTri").Range(Cells(1, 1), Cells(Compteur, 1)).Clear

End Sub

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 (FeuilleDeTri).

Le code utilise ensuite la fonction de tri d’Excel pour trier les données de la colonne dans l’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 de manière à ce que le nettoyage s’effectue à partir de la fin de la collection en remontant vers le début. En effet, lors de la suppression, les valeurs d’index sont réinitialisées. Si le nettoyage s’effectuait depuis le début, le résultat ne serait pas celui attendu (l’index 2 deviendrait l’index 1).

Enfin, à l’aide d’une autre boucle For Next, les valeurs des éléments sont transférées dans la collection vide.

Une autre boucle For Each prouve que la collection est maintenant dans l’ordre ascendant.

Malheureusement, cette opération ne tient pas compte des valeurs de clé qui ont pu être saisies à l’origine, puisque ces valeurs ne peuvent pas être lues.

Transmission d’une Collection à une autre Procédure ou Fonction

Une collection peut être transmise à une autre procédure ou à une fonction de la même manière que n’importe quel autre paramètre

Function MaFonction(ByRef MaCollection 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 en utilisant ‘ByVal’, cela crée une copie de la collection, ce qui peut avoir des répercussions inattendues.

Si une copie est créée à l’aide de ‘ByVal’, tout ce qui modifie la collection dans la fonction ne se produit que sur la copie et non sur l’original. Par exemple, si dans la fonction, un nouvel élément est ajouté à la collection, il n’apparaîtra pas dans la collection originale, ce qui créera un bug dans votre code.

Retourner une Collection à l’Aide d’une Fonction

Vous pouvez retourner une collection à partir d’une fonction de la même manière que vous retournez n’importe quel objet. Vous devez utiliser le mot-clé Set

Sub RetournerUneCollection()

Dim MaCollection As Collection
Set MaCollection = RemplirCollection

MsgBox MaCollection.Count

End Sub

Ce code crée un objet appelé ‘MaCollection’ et utilise ensuite le mot clé ‘Set’ pour appeler une fonction de remplissage personnalisée qui retourne la collection remplie. Une fois que cela est fait, une boîte de message s’affiche pour indiquer le nombre d’éléments ajoutés (2).

Function RemplirCollection() As Collection

Dim MaCollection As New Collection

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2"

Set RemplirCollection= MaCollection

End Function

La fonction RemplirCollection crée un nouvel objet de type collection et le remplit de deux éléments. Elle renvoie ensuite cet objet à l’objet collection créé dans la procédure d’origine.

Conversion d’une Collection en Tableau

Vous pouvez souhaiter convertir votre collection en un tableau. Vous voudrez peut-être stocker les données à un endroit où elles pourront être modifiées et manipulées. Ce code crée une petite collection et la transfère ensuite dans un tableau.

Remarquez 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é que sur 2 car il y a un élément 0.

Sub ConvertirCollectionVersTableau()

Dim MaCollection As New Collection
Dim MonTableau(2) As String

MaCollection.Add "Élément 1"
MaCollection.Add "Élément 2"
MaCollection.Add "Élément 3"

For n = 1 To MaCollection.Count
    MonTableau(n - 1) = MaCollection(n)
Next n

For n = 0 To 2
    MsgBox MonTableau(n)
Next n

End Sub

Convertir un Tableau en Collection

Vous pouvez souhaiter convertir un tableau en une collection. Par exemple, vous pouvez souhaiter 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 du tableau.

Gardez à l’esprit que cette méthode ne fonctionnera que pour une seule dimension du tableau, car la collection n’a qu’une seule dimension

Sub ConvertirTableauVersCollection()

Dim MaCollection As New Collection
Dim MonTableau(2) As String

MonTableau(0) = "Élément 1"
MonTableau(1) = "Elément 2"
MonTableau(2) = "Élément 3"

For n = 0 To 2
    MaCollection.Add MonTableau(n)
Next n

For Each Élément In MaCollection
    MsgBox Élément
Next Élément

End Sub

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 de délimitation entre les dimensions du tableau. Ainsi, lorsque vous lisez la valeur de la collection, vous pouvez utiliser le caractère de délimitation de manière programmatique pour séparer les valeurs.

Vous pouvez également déplacer les données dans la collection en partant du principe que la valeur de la première dimension est ajoutée (indice 1), puis la valeur de la dimension suivante est ajoutée (indice 2) et ainsi de suite.

Si le tableau a, disons 4 dimensions, chaque quatrième valeur de la collection sera un nouvel ensemble de valeurs.

Vous pourriez également ajouter des valeurs de tableau à utiliser comme clés (à condition qu’elles soient uniques), ce qui constituerait un moyen facile de localiser des données spécifiques.