Maîtrisez la classe Collection en VBA

Si vous avez déjà programmé en VBA, vous avez peut-être déjà utilisé des tableaux (array en anglais) pour stocker des valeurs en mémoire, et y accéder de façon indicée. Mais savez-vous qu’il existe un autre moyen plus poussé pour stocker une collection d’items ?

Vous allez maintenant découvrir en détails les possibilités de la classe Collection du VBA, et les 2 grandes façons de l’utiliser.

Qu’est-ce qu’une collection et à quoi ça sert ?

On peut voir une collection comme un tableau VBA dont la taille n’est pas définie à l’avance.

En effet, lorsque vous utilisez un tableau en VBA, vous devez :

  • Soit spécifier son nombre d’éléments dès sa déclaration
  • Soit redimensionner le tableau au fur et à mesure de l’ajout d’éléments avec l’instruction Redim Preserve, comme illustré dans l’extrait de code suivant :
Dim tableau() As String ' tableau sans taille définie au départ
Dim n As Long

For n =  To 10
   ReDim Preserve tableau(n)  ' Redimensionnement dynamique du tableau
   tableau(n) = "item" & CStr(n)
Next

Redimensionner dynamiquement un tableau VBA est donc tout-à-fait possible, mais il n’est pas pour autant judicieux de le faire. En fait, c’est même une très mauvaise pratique !

Mais pourquoi donc ?

Parce qu’à chaque fois que vous redimensionnez le tableau pour ajouter ou enlever un élément, son contenu est intégralement recopié en mémoire pour créer un nouveau tableau.
Plus le nombre d’éléments dans le tableau est élevé, plus cette opération est coûteuse en temps d’exécution.

C’est là que les collections viennent à la rescousse…

Une collection est un objet qui fait le même boulot qu’un tableau, sans qu’il soit nécessaire de définir sa taille. Vous pouvez donc y ajouter ou y enlever des éléments sans que cela entraine de recopie des valeurs précédentes. C’est donc beaucoup plus efficace.

Selon les langages de programmation, le terme collection peut désigner des concepts assez différents. Dans certains langages, il existe même une grande variété de collections, avec leurs caractéristiques propres.

En VBA, il n’y a qu’une seule classe de collection, nommée…Collection. Mais elle peut être utilisée de 2 façons différentes, comme vous allez le voir plus loin.

La classe Collection de VBA

Description

Cette page de la documentation Microsoft décrit la classe Collection.
A première vue, cette classe est très simple, car elle ne possède que les membres suivants :

  • Une méthode Add pour ajouter un élément
  • Une méthode Remove pour supprimer un élément
  • Une méthode Item pour accéder à un élément par son indice
  • Une propriété Count qui renvoie le nombre d’éléments de la collection

Mais derrière cette apparente simplicité se cachent pas mal de détails qu’il faut connaître pour exploiter au mieux cette classe.

La classe Collection de VBA est en fait une sorte d’hybride qui peut être utilisée comme une liste simple ou comme un dictionnaire.
Regardons cela de plus près :

Utiliser la collection VBA comme une liste simple

Voyons un exemple simple pour nous familiariser avec les collections.

Il s’agit de charger le contenu d’un tableau Excel de pays dans une collection, puis de l’afficher dans le panneau d’exécution de l’éditeur VBA, comme le montre l’image suivante :

Charger un tableau Excel dans une collection VBA
A gauche le tableau Excel à charger. A droite l’affichage dans le panneau d’exécution

Collection de valeurs simples

Dans un premier temps, nous n’allons charger dans la collection que les noms des pays, c’est-à-dire la 2ème colonne du tableau.

Le bouton au-dessus du tableau permet de déclencher une macro dont voici le code :

Sub BtnCharger_Clic()

' Récupère le contenu du tableau des pays dans un tableau VBA à 2 dimensions
Dim data As Variant
data = Sheets("Collections").ListObjects(1).DataBodyRange.Value2

' Ajoute les noms des pays dans une collection de type liste simple
Dim coll As Collection
Dim i As Long

Set coll = New Collection
For i = 1 To UBound(data, 1)
   coll.Add data(i, 2)
Next i

' Enumère les éléments de la collection et les affiche dans le panneau d'exécution
Dim elt
For Each elt In coll
   Debug.Print elt
Next

' Autre façon d'énumérer les éléments
For i = 1 To coll.Count
   Debug.Print coll.Item(i)
Next

End Sub

La variable data contient un tableau VBA (array) à 2 dimensions, généré automatiquement par Excel quand on fait appel à la propriété Value2 sur la plage correspondant au corps du tableau.

NB/ Vous trouverez plus d’infos sur la manipulation des ListObject en VBA sur cette page.

On crée la collection avec l’opérateur New, puis on la remplit à partir du tableau VBA en appelant la méthode Add pour chaque élément.

Cet article devrait aussi vous intéresser :  Revenir à la ligne dans Excel

On affiche ensuite le contenu de la collection dans le panneau d’exécution de l’éditeur VBA de 2 façons différentes : avec une boucle for…each, et avec une boucle for.

Collections d’objets

Supposons maintenant que nous voulions charger dans notre collection l’ensemble des informations des pays, sous forme d’objets.

Nous pouvons modéliser un pays au moyen d’une classe CPays contenant le code suivant :

Dim Code As String
Dim Nom As String
Dim Capitale As String

Remarque : j’utilise ici de simples champs publics pour simplifier, mais un puriste créerait plutôt des propriétés Get et Let pour l’accès en lecture et en écriture à chaque information.

On peut donc charger les pays dans une collection d’objets CPays de la façon suivante :

Sub BtnCharger_Clic()

' Récupère le contenu du tableau des pays dans un tableau VBA à 2 dimensions
Dim data As Variant
data = Sheets("Collections").ListObjects(1).DataBodyRange.Value2

' Ajoute les noms des pays dans une collection de type liste simple
Dim coll As Collection
Dim pays As CPays
Dim i As Long

Set coll = New Collection

For i = 1 To UBound(data, 1)
   ' Crée et initialise l'objet Pays
   Set pays = New CPays
   pays.Code = data(i, 1)
   pays.Nom = data(i, 2)
   pays.Capitale = data(i, 3)
   
   ' Ajoute le pays à la collection
   coll.Add pays
Next i

' Affiche les pays et leurs capitales dans le panneau d'exécution
For Each pays In coll
   Debug.Print pays.Code, pays.Nom, pays.Capitale
Next

End Sub

Voici le résultat affiché dans le panneau d’exécution de l’éditeur VBA :

DE            Allemagne     Berlin
AT            Autriche      Vienne
BE            Belgique      Bruxelles
BG            Bulgarie      Sofia
CY            Chypre        Nicosie
HR            Croatie       Zagreb
DK            Danemark      Copenhague
ES            Espagne       Madrid
EE            Estonie       Tallinn
...

Avantages de la collection par rapport à un tableau VBA

Les 2 exemples précédents vous ont montré comment charger et parcourir une collection.

Dans ces exemples, une collection n’apporte pas de plus-value par rapport à un tableau VBA, puisqu’on connaît le nombre de pays à l’avance.

Mais supposons que la collection soit utilisée pour créer un quiz dans lequel on demande au joueur la capitale de chaque pays dans un ordre aléatoire.

Pour éviter de poser plusieurs fois la même question, on pourrait supprimer le pays demandé de la collection après avoir posé chaque question.

Or, supprimer un élément d’un tableau n’est pas simple et demande de redimensionner le tableau, ce qui n’est pas efficace comme nous l’avons déjà vu. Tandis qu’avec la collection, la suppression est très simple :

coll.Remove p

…où p est l’indice du pays à supprimer (déterminé aléatoirement dans notre quiz).

Conseil : dès que vous commencez à manipuler des ensembles de données en mémoire dans votre code VBA, utilisez des collections plutôt que des tableaux VBA, même si vous n’en voyez pas tout de suite l’intérêt. Cela vous permettra d’ajouter ou d’enlever des éléments plus facilement si besoin, tout en gardant des performances optimales.

Utiliser la collection VBA comme un dictionnaire

Un dictionnaire est une collection de paires clé-valeur. Chaque valeur est associée à une clé unique qui sert d’identifiant.

Par exemple, notre collection de pays peut être gérée sous forme de dictionnaire dans lequel les clés sont les codes ISO des pays (exemple : DE, FR…), et les valeurs sont des objets CPays. Pour cela, il suffit de modifier la ligne d’ajout comme ceci :

coll.Add pays, key:=data(i, 1)

Le premier paramètre est la valeur, et le second la clé.

Un dictionnaire a les caractéristiques suivantes :

  • Il garantit l’unicité de chaque élément en interdisant l’ajout de 2 clés identiques. Ainsi, dans notre exemple, l’ajout de 2 pays avec le même code ISO provoquerait une erreur.
  • Il permet d’accéder aux éléments via leurs clés. On peut ainsi par exemple récupérer la France par le code suivant :
    set pays = coll.Item(“FR”)
  • L’accès à une clé qui n’existe pas provoque une erreur.
    Exemple : set pays = coll.Item(“YY”) provoque une erreur
  • Les clés doivent être des chaînes (type string)

Conseil : lors du parcours d’un dictionnaire avec une boucle for ou for…each, il n’y a aucune possibilité de récupérer la valeur de la clé de l’élément courant. Il est donc recommandé de stocker la clé dans un champ de l’objet lui-même. C’est ce que nous avons fait avec le champ Code de la classe CPays.

Astuce : si les éléments à stocker dans le dictionnaire sont identifiés par un champ de type entier, vous pouvez tout de même utiliser ce dernier comme clé en le transformant en chaîne avec la fonction CStr :
coll.Add personne, key :=CStr(Id)

Gérer l’erreur de doublon de clé

Si vous chargez dans un dictionnaire des données qui contiennent potentiellement des doublons de clés, le chargement sera interrompu au premier doublon rencontré. Vous pouvez éviter cela en gérant l’erreur afin que le chargement se poursuive en ignorant simplement les doublons.

Voici le code à ajouter pour cela autour de la ligne d’ajout des éléments :

   On Error Resume Next
   coll.Add pays, key:=data(i, 1)
   On Error GoTo 

La première ligne indique de continuer à exécuter le code qui suit si une erreur se produit.
La dernière rétablit le comportement normal, qui interrompt le code en cas d’erreur.

Astuce : charger une liste d’éléments dans un dictionnaire en gérant les erreurs est un bon moyen d’éliminer les doublons !

Gérer l’erreur d’accès à une clé inconnue

Supposez qu’on vous fournisse une liste de codes ISO et qu’on vous demande d’afficher le détail des pays correspondants.

Cet article devrait aussi vous intéresser :  Exemple de construction d'un classeur Excel

Vous allez pour cela rechercher chaque code dans le dictionnaire avec la syntaxe : coll.Item(“code”)

Mais si la liste contient un code ISO d’un pays non présent dans votre dictionnaire, une erreur se produira.

Pour que le traitement ne soit pas interrompu au premier code ISO non valide rencontré, voici ce que vous pouvez faire :

' Affiche les pays correpondant à une liste de codes ISO
Dim codesISO
codesISO = Array("BE", "DK", "XX", "LU")

Dim code
For Each code In codesISO
   On Error GoTo GestionErreur
   Set pays = coll.Item(code)

   Debug.Print pays.code, pays.Nom, pays.Capitale
   GoTo PaysSuivant
   
GestionErreur:
   Debug.Print code, "Inconnu"
PaysSuivant:
Next

Le tableau VBA (Array) contient les codes ISO des pays à rechercher.
On le parcourt à l’aide d’une boucle for each pour récupérer les pays correspondants.

L’instruction On Error GoTo permet de sauter l’étape d’affichage normal des infos du pays, et d’exécuter le code qui suit l’étiquette « GestionErreur », c’est-à-dire d’afficher le code ISO suivi de “Inconnu”.

L’instruction GoTo PaysSuivant permet de ne pas exécuter le code qui suit l’étiquette GestionErreur et d’aller directement à l’instruction Next.

Ce code produit l’affichage suivant :

BE            Belgique      Bruxelles
DK            Danemark      Copenhague
XX            Inconnu
LU            Luxembourg    Luxembourg

Comment trier les éléments d’une collection ?

Que la collection soit utilisée comme une liste simple ou comme un dictionnaire, les éléments sont par défaut stockés les uns à la suite des autres, dans l’ordre d’ajout.

Cependant, la méthode Add permet de spécifier la position de l’élément ajouté dans la collection, comme ceci :

   ' Ajout de l'élément avant celui d'indice 1
   coll.Add pays, key:=data(i, 1), Before:=1
      
   ' Ajout de l'élément après celui d'indice 1
   coll.Add pays, key:=data(i, 1), After:=1

On peut spécifier la position de l’élément à ajouter au moyen des paramètres Before ou After (un seul des 2).
La position peut être une valeur d’indice ou une valeur de clé si la collection est utilisée comme un dictionnaire.

Ceci ouvre donc la possibilité d’un tri des éléments, mais c’est à vous d’implémenter l’algorithme nécessaire.

Dans un prochain article, je vous présenterai en détails un algorithme très performant de tri par dichotomie.

Collection VBA vs Scripting.Dictionary

Pour créer des dictionnaires, il existe une autre classe nommée Scripting.Dictionary. Mais elle est spécifique à Windows, et ne fait pas partie des classes standard du langage VBA.

N’utilisez pas Scripting.Dictionary si vous voulez que vos macros fonctionnent aussi bien sur Windows que sur MacOS !

Scripting.Dictionary n’offre de toutes façons guère plus de possibilités que la classe Collection. Les principales différences sont les suivantes :

  1. Pour créer un dictionnaire, on utilise la syntaxe suivante :
    Set dico = CreateObject(“Scripting.Dictionary”)
  2. Ce dictionnaire permet d’utiliser des clés de tous types, et non pas seulement String.
  3. Il permet de récupérer la liste des clés sous forme de tableau
  4. Il ne provoque pas d’erreur lors de l’accès à une clé non connue

Même si la classe Collection du VBA ne gère pas les points 2 et 3, ce n’est pas vraiment gênant, car on peut se débrouiller autrement, comme nous l’avons dans les exemples précédents.

Le point 4 n’est pas forcément un avantage, car on obtient dans ce cas un objet ayant la valeur Nothing, et il faut donc vérifier cela avant d’utiliser l’objet.

Pendant une période, j’ai pas mal utilisé la classe Scripting.Dictionary car je ne connaissais pas la possibilité d’utiliser la classe Collection comme dictionnaire. Mais j’utilise désormais uniquement la classe Collection pour que mon code soit compatible avec MacOS.

Synthèse

Vous avez vu au travers d’exemples simples comment mettre en œuvre de façon concrète les collections en VBA, dans différents scénarios.

Dans vos macros VBA, à chaque fois que vous avez besoin de stocker en mémoire un nombre d’éléments non connu à l’avance, utilisez une collection plutôt qu’un tableau VBA (Array), car elle vous permettra de faire bien plus de choses, et avec de meilleures performances.

Déterminez au départ si vous avez plutôt besoin d’une liste simple, ou d’un dictionnaire. Dans ce dernier cas, n’oubliez pas de gérer correctement les erreurs potentielles lors de l’ajout de clés en double, et lors de l’accès à des clés non connues, en vous inspirant des exemples de cet article.

La classe Collection est incontournable pour tout programmeur VBA qui se respecte. Alors si vous ne maîtrisez pas encore son utilisation, j’espère que cet article vous y aidera !

Si vous avez des questions ou remarques à propos de cet article, n’hésitez pas à m’en faire part en commentaire 😊

vote
Notez cet article
Si vous avez aimé cet article, un like et un partage me feraient très plaisir ;)
  •   
  •   
  •  
  •   
  •   
S’abonner
Notifier de
guest

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

1 Commentaire
plus récent
plus ancien Le plus populaire
Commentaires en ligne
Voir tous les commentaires
Darcel
Darcel
01/10/2020 07:25

Je voulais vous remercier du partage et de votre travail, même si la lecture de cet article dans laquelle je me suis aventuré dépasse largement mes capacités. En tout cas bravo pour votre travail et de l’offrir à tous. Merci