Utilisation de l’Objet ArrayList en VBA

Un objet ArrayList est similaire à un objet Collection mais il possède beaucoup plus de méthodes et de propriétés, et donc beaucoup plus de flexibilité du point de vue de la programmation.

Un objet Collection ne possède que deux méthodes (Add, Remove) et deux propriétés (Count, Item), alors qu’une ArrayList en possède beaucoup plus. En outre, l’objet Collection est en lecture seule. Une fois que des valeurs ont été ajoutées, la valeur indexée ne peut pas être modifiée, alors que sur une liste de tableaux, la modification est possible.

De nombreuses méthodes de ArrayList utilisent des paramètres. Contrairement à de nombreuses méthodes VBA standard, aucun de ces paramètres n’est facultatif. En outre, la casse des noms de certaines méthodes et propriétés ne se formate pas automatiquement lorsqu’elles sont saisies, comme c’est le cas dans Excel VBA. Cependant, elles fonctionnent toujours.

La taille de l’objet ArrayList augmente et diminue en fonction du nombre d’éléments qu’il contient. Il n’est pas nécessaire de le dimensionner avant de l’utiliser comme un tableau.

L’objet ArrayList est unidimensionnel (comme l’objet Collection) et le type de données par défaut est Variant, ce qui signifie qu’il accepte tout type de données, qu’il s’agisse de données numériques, de texte ou de date.

À bien des égards, l’objet Array List corrige un certain nombre de lacunes de l’objet Collection. Il est certainement beaucoup plus flexible dans ce qu’il peut faire.

L’objet Array List ne fait pas partie de la bibliothèque VBA standard. Vous pouvez l’utiliser dans votre code Excel VBA en utilisant une liaison tardive ou anticipée

Sub ExempleLiaisonTardive()
Dim MyList As Object
Set MyList = CreateObject("System.Collections.ArrayList")
End Sub

Sub ExempleLiaisonAnticipée()
Dim MyList As New ArrayList
End Sub

Afin d’utiliser l’exemple de liaison anticipée, vous devez d’abord entrer dans VBA une référence au fichier ‘mscorlib.tlb’

Pour ce faire, sélectionnez ‘Tools | References’ dans la fenêtre de l’éditeur Visual Basic (VBE). Une fenêtre pop-up apparaîtra avec toutes les références disponibles. Faites défiler jusqu’à ‘mscorlib.dll’ et cochez la case correspondante. Cliquez sur OK et cette bibliothèque fait maintenant partie de votre projet :

L’un des grands inconvénients d’un objet Array List est qu’il n’a pas la fonction ‘Intellisense’. Normalement, lorsque vous utilisez un objet en VBA, tel qu’une plage, vous voyez apparaître une liste contextuelle de toutes les propriétés et méthodes disponibles. Ce n’est pas le cas avec un objet Array List, et il faut parfois vérifier attentivement que vous avez correctement orthographié la méthode ou la propriété.

De plus, si vous appuyez sur F2 dans la fenêtre VBE et que vous recherchez ‘arraylist’, rien ne s’affichera, ce qui n’est pas très utile pour un développeur.

Votre code s’exécutera beaucoup plus rapidement avec la liaison précoce, car tout est compilé dès le départ. Avec la liaison tardive, l’objet doit être compilé au fur et à mesure de l’exécution du code

Distribution de Votre Application Excel Contenant une Liste de Tableaux

Comme nous l’avons déjà souligné, l’objet ArrayList ne fait pas partie d’Excel VBA. Cela signifie que tous vos collègues à qui vous distribuez l’application doivent avoir accès au fichier ‘mscorlib.tlb’

Ce fichier est normalement situé dans :

C:\Windows\Microsoft.NET\Framework\v4.0.30319

Il pourrait être intéressant d’écrire du code (en utilisant la méthode Dir) pour vérifier que ce fichier existe lorsqu’un utilisateur charge l’application, afin qu’il connaisse un « atterrissage en douceur » s’il n’est pas trouvé. S’il n’est pas présent, et que le code s’exécute, des erreurs se produiront.

En outre, l’utilisateur doit avoir installé la bonne version de .Net Framework. Même si l’utilisateur dispose d’une version plus récente, la version 3.5 doit être installée, sinon votre application ne fonctionnera pas

Portée d’un Objet Array List

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

Si vous voulez que votre Array List soit disponible pour tous les codes de votre module de code, vous devez déclarer l’objet Array List dans la section Declare située tout en haut de la fenêtre du module

Cela garantira que tout votre code dans ce module pourra accéder à la liste de tableaux. Si vous souhaitez que tous les modules de votre classeur puissent accéder à l’objet Array List, définissez-le comme un objet global

Global MaCollection As New ArrayList

Remplissage et Lecture de Votre Array List

L’action la plus élémentaire que vous voulez entreprendre est de créer un objet Array List, d’y placer des données et de prouver que ces données peuvent être lues. Tous les exemples de code de cet article supposent que vous utilisez la liaison anticipée et que vous avez ajouté ‘mscorlib.tlb’ aux références VBA, comme décrit ci-dessus. Pour que le code fonctionne vous devez également vous assurer d’avoir la version 3.5 du .Net Framework d’installé et activé.

Sub ExempleArrayList()
    'Création d'un nouvel objet de liste de tableaux
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Itération dans la liste de tableaux pour prouver les valeurs
    For N = 0 To MaListe.Count - 1
        MsgBox MaListe(N)
    Next N
End Sub

Cet exemple crée un nouvel objet ArrayList, le remplit avec 3 éléments, et itère dans la liste en affichant chaque élément.

Notez que l’index de ArrayList commence à 0, et non à 1, et que vous devez donc soustraire 1 de la valeur Count

Vous pouvez également utiliser une boucle ‘For…Each’ pour lire les valeurs :

Sub ExempleArrayListForEach()
    'Création d'un nouvel objet de liste de tableaux
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Itération dans la liste de tableaux pour prouver les valeurs
    For Each I In MaListe
        MsgBox I
    Next I
End Sub

Édition et Modification des Éléments d’une ArrayList

Un avantage majeur d’une liste de tableaux par rapport à une collection est que les éléments de la liste peuvent être édités et modifiés dans votre code. L’objet Collection est en lecture seule alors que l’objet Array List est en lecture/écriture

Sub ExempleArrayListÉdition()
    'Création d'un nouvel objet de type liste de tableaux
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Changez l'élément 1 de "Item2" à "Modifié"
    MaListe(1) = "Modifié"
    
    'Itération dans la liste du tableau pour prouver que la modification a fonctionné
    For Each I In MaListe
        'Affiche le nom de l'élément
        MsgBox I
    Next I
End Sub

Dans cet exemple, le deuxième élément, ‘Item2’, est modifié et prend la valeur ‘Modifié’ (rappelez-vous que l’index commence à 0). Lorsque l’itération est exécutée à la fin du code, la nouvelle valeur est affichée

Ajout d’un Tableau de Valeurs à une ArrayList

Vous pouvez entrer des valeurs dans votre liste de tableaux en utilisant un tableau contenant une liste de ces valeurs ou des références à des valeurs de cellules sur une feuille de calcul

Sub ExempleAjoutTableauValeurs()
    'Création de l'objet Array List
    Dim MaListe As New ArrayList
    
    'Itération dans les valeurs du tableau pour les ajouter à la liste du tableau
    For Each v In Array("A1", "A2", "A3")
        'Ajout de chaque valeur de tableau à la liste
        MaListe.Add v
    Next v
    
    ' Itération sur les valeurs du tableau avec les références de la feuille de calcul, en les ajoutant à la liste du tableau
    For Each v In Array(Range("A5").Value, Range("A6").Value)
        MaListe.Add v
    Next v
    
    'Itération dans la liste du tableau pour prouver la présence des valeurs
    For N = 0 To MaListe.Count - 1
        ' Affichage de l'élément de la liste
        MsgBox MaListe.Item(N)
    Next N
End Sub

Lecture/Récupération d’une Plage d’Éléments dans une ArrayList

En utilisant la méthode GetRange sur une liste de tableau, vous pouvez spécifier une série d’éléments consécutifs à récupérer. Les deux paramètres requis sont la position de l’index de départ et le nombre d’éléments à récupérer. Le code remplit un deuxième objet Array List avec le sous-ensemble d’éléments qui peuvent ensuite être lus séparément.

Sub ExempleRécupérationPlage()
    'Définition des objets
    Dim MaListe As New ArrayList, MaListe1 As Object
    
    'Ajout d'éléments à l'objet 'MaListe'
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    MaListe.Add "Item6"
    MaListe.Add "Item4"
    MaListe.Add "Item7"
    
    'Capture de 4 éléments dans 'MaListe' à partir de la position d'index 2
    Set MaListe1 = MaListe.GetRange(2, 4)
    
    'Itération dans l'objet 'MaListe1' pour afficher le sous-ensemble d'éléments
    For Each I In MaListe1
        'Affiche le nom de l'élément
        MsgBox I
    Next I
End Sub

Recherche d’Éléments dans une Liste de Tableaux

Vous pouvez vérifier si un élément nommé se trouve dans votre liste en utilisant la méthode « Contains ». Cette méthode renvoie True ou False

MsgBox MyList.Contains("Item2")

Vous pouvez également trouver la position réelle de l’index en utilisant la méthode « IndexOf ». Vous devez spécifier l’indice de départ de la recherche (généralement 0). La valeur de retour est l’indice de la première instance de l’élément trouvé. Vous pouvez ensuite utiliser une boucle pour modifier le point de départ à la valeur d’index suivante afin de trouver d’autres instances s’il y a plusieurs valeurs en double.

Si la valeur n’est pas trouvée, une valeur de -1 est retournée

Cet exemple montre l’utilisation de « Contains », l’élément non trouvé et le passage en boucle dans la liste du tableau pour trouver la position de tous les éléments en double :

Sub ExempleRechercheListe()
    'Définition de la liste de tableaux et des variables
    Dim MaListe As New ArrayList, Sp As Integer, Pos As Integer
    
    'Ajout de nouveaux éléments, y compris un doublon
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    MaListe.Add "Item1"
    
    'Test de présence de l'élément "Item2" dans la liste - renvoie Vrai
    MsgBox MaListe.Contains("Item2")
    
    'Obtenir l'index d'une valeur inexistante - renvoie -1
    MsgBox MaListe.IndexOf("Item", 0)
    
    'Définir la position de départ de la recherche à zéro
    Sp = 0
    
    'Iterer dans la liste pour obtenir toutes les positions de "Item1"
    Do
        'Obtenir la position de l'index du prochain 'Item1' en fonction de la position de la variable 'Sp'
        Pos = MaListe.IndexOf("Item1", Sp)
        'Si aucune autre instance de 'Item1' n'est trouvée, sortez de la boucle
        If Pos = -1 Then Exit Do
        'Affiche la prochaine instance trouvée et la position de l'indice
        MsgBox MaListe(Pos) & " à l'indice " & Pos
        'Ajoutez 1 à la dernière valeur d'index trouvée - cela devient maintenant la nouvelle position de départ pour la prochaine recherche
        Sp = Pos + 1
    Loop
End Sub

Notez que le texte de recherche utilisé est sensible à la casse et que les caractères de remplacement ne sont pas acceptés.

Insertion et Suppression d’Éléments

Si vous ne souhaitez pas ajouter vos éléments à la fin de la liste, vous pouvez les insérer à une position d’index particulière afin que le nouvel élément se trouve au milieu de la liste. Les numéros d’index seront automatiquement ajustés pour les articles suivants.

Sub ExempleInsertion()
    'Définition d'un objet de liste de tableaux
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste de tableaux
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    MaListe.Add "Item1"
    
    'Insertion de l'élément 6 à la position d'index 2
    MaListe.Insert 2, "Item6"
    
    'Itération dans les éléments de la liste du tableau pour afficher le nouvel ordre et la position de l'indice
    For N = 0 To MaListe.Count - 1
        MsgBox MaListe(N) & " Index " & N
    Next N
End Sub

Dans cet exemple, l’élément 6 a été ajouté à la liste à la position d’index 2, et l’élément 3 qui était à la position d’index 2 est maintenant déplacé à la position d’index 3

Un élément individuel peut être supprimé à l’aide de la méthode « Remove ».

MaListe.Remove "Item"

Notez qu’aucune erreur n’est produite si le nom de l’élément n’est pas trouvé. Tous les numéros d’index ultérieurs seront modifiés en fonction de la suppression.

Si vous connaissez la position de l’index de l’élément, vous pouvez utiliser la méthode « RemoveAt », par exemple

MaListe.RemoveAt 2

Notez que si la position d’index donnée est supérieure au nombre d’éléments dans la liste du tableau, une erreur sera renvoyée.

Vous pouvez supprimer une plage de valeurs de la liste en utilisant la méthode « RemoveRange ». Les paramètres sont l’indice de départ, puis le nombre d’éléments à supprimer, par ex.

MaListe.RemoveRange 3, 2

Notez que vous obtiendrez une erreur dans votre code si le nombre d’éléments décalés par rapport à la valeur de départ est supérieur au nombre d’éléments de la liste du tableau.

Dans les méthodes « RemoveAt » et « RemoveRange », il serait souhaitable qu’une partie du code vérifie si les nombres d’index spécifiés sont supérieurs au nombre total d’éléments dans la liste du tableau afin de détecter toute erreur éventuelle. La propriété ‘Count’ donnera le nombre total d’éléments dans la liste du tableau.

Sub ExempleSuppression()
    'Définition d'un objet de type tableau
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste du tableau
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    MaListe.Add "Item1"
    MaListe.Add "Item4"
    MaListe.Add "Item5"
    
    'Insérer 'Item6' à la position d'index 2
    MaListe.Insert 2, "Item6"
    'Suppression de l'élément 2
    MaListe.Remove "Item2"
    'Suppression de l'élément - il n'existe pas dans la liste du tableau, mais il n'y a pas d'erreur
    MaListe.Remove "Item"
    'Supprime l 'élément situé à la position 2 de l'index
    MaListe.RemoveAt 2
    'Supprime 2 éléments consécutifs à partir de la position 2 de l'indice
    MaListe.RemoveRange 3, 2
    
    'Itération dans la liste du tableau pour montrer ce qui reste et à quelle position d'index il se trouve maintenant
    For N = 0 To MaListe.Count - 1
        MsgBox MaListe(N) & " Index " & N
    Next N
End Sub

Notez que si vous utilisez la fonction ‘RemoveAt’ pour supprimer un élément à une position spécifique, dès que cet élément est supprimé, toutes les positions d’index suivantes sont modifiées. Si vous avez plusieurs suppressions utilisant la position d’index, il est conseillé de commencer par le numéro d’index le plus élevé et de revenir en arrière jusqu’à la position zéro, afin de toujours supprimer le bon élément. De cette façon, vous n’aurez pas le problème

Tri d’une Liste de Tableaux

Un autre avantage important par rapport à une collection est que vous pouvez trier les éléments dans un ordre croissant ou décroissant.

L’objet Liste de tableaux est le seul objet d’Excel VBA doté d’une méthode de tri. Cette méthode de tri est très rapide, ce qui peut être un élément important à prendre en compte lors de l’utilisation d’une ArrayList.

Dans l’objet collection, il fallait faire preuve d’imagination pour trier tous les éléments, mais avec une liste de tableaux, c’est très simple.

La méthode « Sort » trie par ordre croissant et la méthode « Reverse » trie par ordre décroissant.

Sub ExempleTriArrayList()
    'Création de l'objet Array List
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments dans un ordre non trié
    MaListe.Add "Item1"
    MaListe.Add "Item3"
    MaListe.Add "Item2"
    
    'Tri des éléments dans l'ordre croissant
    MaListe.Sort
    'Itération dans les éléments pour afficher l'ordre croissant
    For Each I In MaListe
        'Affiche le nom de l'élément
        MsgBox I
    Next I
    
    'Trier les éléments dans l'ordre décroissant
    MaListe.Reverse
    'Itération dans les éléments pour afficher l'ordre décroissant
    For Each I In MaListe
        'Affiche le nom de l'élément
        MsgBox I
    Next I
End Sub

Clonage d’une ArrayList

Une liste de type tableau a la possibilité de créer un clone ou une copie d’elle-même. C’est utile si un utilisateur modifie les éléments à l’aide d’une interface graphique et de votre code VBA, mais que vous devez conserver une copie des éléments dans leur état d’origine comme sauvegarde.

L’utilisateur pourrait ainsi disposer d’une fonction « Annuler ». Il peut avoir effectué les modifications et souhaiter revenir à la liste d’origine.

Sub ExempleClonage()
    'Définissez deux objets : une liste de tableaux et un objet
    Dim MaListe As New ArrayList, MaListe1 As Object
    
    'Remplissez le premier objet avec des éléments
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Copie de la liste d'éléments dans MaListe1
    Set MaListe1 = MaListe.Clone
    
    'Itération dans MaListe1 pour prouver le clonage
    For Each I In MaListe1
        'Affiche le nom de l'élément
        MsgBox I
    Next I
End Sub

MaListe1 contient maintenant tous les éléments de MaListe dans le même ordre

Copie d’une ArrayList dans un Objet Tableau VBA Conventionnel

Vous pouvez utiliser une méthode simple pour copier l’objet ArrayList dans un tableau VBA normal :

Sub ExempleTableauConventionnel()
    'Création d'un objet de type liste de tableaux et d'un objet de type tableau standard
    Dim MaListe As New ArrayList, NouveauTableau As Variant
    
    'Remplir la liste de tableaux avec des éléments
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Copie de la liste du tableau dans le nouveau tableau
    NouveauTableau = MaListe.ToArray
    
    'Itérez dans le nouveau tableau - notez que le nombre de listes de tableaux fournit l'indice maximum
    For N = 0 To MaListe.Count - 1
        'Affichez le nom de l'élément
        MsgBox NouveauTableau(N)
    Next N
End Sub

Copie d’une ArrayList dans une Plage de Feuille de Calcul

Vous pouvez copier votre liste de tableaux vers une feuille de calcul et une référence de cellule spécifiques sans avoir besoin d’itérer dans la liste de tableaux. Il vous suffit de spécifier la première référence de cellule

Sub ExempleCopieVersPlage()
    'Création d'un nouvel objet de type tableau
    Dim MaListe As New ArrayList
    
    'Ajout d'éléments à la liste
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Effacement de la feuille cible
    Sheets("Feuil1").UsedRange.Clear
    'Copie des éléments sur une ligne
    Sheets("Feuil1").Range("A1").Resize(1, MaListe.Count).Value = MaListe.toArray
    'Copie des éléments dans une colonne
    Sheets("Feuil1").Range("A5").Resize(MaListe.Count, 1).Value = _
    WorksheetFunction.Transpose(MaListe.toArray)
End Sub

Vider tous les Éléments d’une Liste de Tableaux

Il existe une fonction simple (Clear) pour effacer complètement la ArrayList

Sub ExempleEffacerListe()
    'Création D 'un objet de type tableau
    Dim MaListe As New ArrayList
    
    'Ajout de nouveaux éléments
    MaListe.Add "Item1"
    MaListe.Add "Item2"
    MaListe.Add "Item3"
    
    'Affiche le nombre d'éléments
    MsgBox MaListe.Count
    'Effacer tous les éléments
    MaListe.Clear
    'Afficher le nombre d'éléments pour prouver que l'effacement a fonctionné
    MsgBox MaListe.Count
End Sub

Cet exemple crée des éléments dans une liste de type tableau, puis efface cette liste. Les boîtes de message montrent avant et après le nombre d’éléments dans la liste du tableau.

Résumé des Méthodes de Création de Tableaux pour Excel VBA

Tâche	Paramètres	Exemples
Ajouter / Modifier un élément	Valeur	MaListe.Add « Item1
		MaListe(4)= « Item2 »
Cloner une liste de type tableau	Aucun	Dim MaListe As Object
		Set MaListe2 = MaListe.Clone
Copie dans un tableau	None	Dim MonTableau As Variant
		MonTableau = MaListe.ToArray
Copie dans une plage de feuilles de calcul (rangée)	Aucun	Sheets(« Feuil1 »).Range(« A1 »).Resize(1, MaListe.Count).Value = MaListe.ToArray
Copie dans une feuille de calcul de la plage (colonne)	Aucun	Sheets(« Feuil1 »).Range(« A3 »).Resize(MaListe.Count, 1).Value = WorksheetFunction.Transpose(MaListe.ToArray)
Créer	« System.Collections.ArrayList » (Liste de tableaux)	Dim MaListe As Object
		Set MaListe = CreateObject(« System.Collections.ArrayList »)
Déclarer	N/A	Dim MaListe As Object
Recherche / vérification de l’existence d’un élément	Élément à trouver	MaListe.Contains(« Item2 »)
Trouver la position d’un élément dans la liste matricielle	1. Elément à trouver.	Dim IndexNo As Long
	2. Position de départ de la recherche.	IndexNo = MaListe.IndexOf(« Item3 », 0)
		IndexNo = MaListe.IndexOf(« Item5 », 3)
Obtenir le nombre d’éléments	Aucun	MsgBox MaListe.Count
Insérer un élément	1. Index – position à insérer.	MaListe.Insert 0, « Item5 »
	2 Value – objet ou valeur à insérer.	MaListe.Insert 4, « Item7 »
Lire l’élément	Index – nombre entier long	MsgBox MaListe.Item(0)
		MsgBox MaListe.Item(4)
Lire le dernier élément ajouté	Index – entier long	MsgBox MaListe.Item(MaListe.Count – 1)
Lire l’élément ajouté en premier	Index – entier long	MsgBox MaListe.Item(0)
Lire tous les éléments (For Each)	N/A	Dim element As Variant
		For Each item In MaListe
		MsgBox item
		Next item
Lire tous les éléments(For)	Index – nombre entier long	Dim i As Long
		For i = 0 To MaList.Count – 1
		MsgBox i
		Next i
Supprimer tous les éléments	Aucun	MaList.Clear
Supprime l’élément à la position	Position de l’index où se trouve l’élément	MaList.RemoveAt 5
Suppression d’un élément par son nom	L’élément à supprimer de la liste de tableaux	MaListe.Remove « Item3 »
Suppression d’une série d’éléments	1. Index – position de départ.	MaListe.RemoveRange 4,3
	2. Count – le nombre d’éléments à supprimer.
Trier par ordre décroissant	Aucun	MaListe.Reverse
Trier par ordre croissant	Non	MaListe.Sort