Si vous avez déjà programmé en VBA, vous avez peut-être déjà utilisé des tableaux (array en anglais) pour stocker vos listes de valeurs en mémoire. Mais saviez-vous qu’il existe un autre moyen plus souple ?
Vous allez maintenant découvrir comment gérer efficacement vos dictionnaires et listes en VBA, à l’aide de la classe Collection.
Différence entre un tableau et une liste
Une liste est semblable à un tableau 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 = 0 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 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 listes viennent à la rescousse…
Une liste 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.
Dans certains langages, il existe différents types de listes et dictionnaires, avec leurs caractéristiques propres.
En VBA, il n’y a qu’une seule classe de liste nommée Collection, qui peut être utilisée de 2 façons différentes, comme vous allez le voir bientôt.
La classe Collection du 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 classe Collection comme une liste simple en VBA
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 :
Liste 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.
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.
Liste 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 classe Collection par rapport à un tableau VBA
Les 2 exemples précédents vous ont montré comment charger et parcourir une liste.
Dans ces exemples, la classe Collection n’apporte pas de plus-value par rapport à un tableau VBA, puisqu’on connaît le nombre de pays à l’avance.
Mais supposons qu’on veuille 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 liste 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 classe 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 listes de données en VBA, utilisez des collections plutôt que des tableaux, 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 classe Collection 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 0
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.
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 LuxembourgComment 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.
Si vous voulez que vos macros fonctionnent aussi bien sur Windows que sur MacOS, n’utilisez pas Scripting.Dictionary !
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 :
- Pour créer un dictionnaire, on utilise la syntaxe suivante :
Set dico = CreateObject(« Scripting.Dictionary ») - Ce dictionnaire permet d’utiliser des clés de tous types, et pas seulement String.
- Il permet de récupérer la liste des clés sous forme de tableau
- 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 vu dans les exemples précédents.
Enfin, 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.
Synthèse
Vous avez vu au travers d’exemples simples comment mettre en œuvre de façon concrète des listes et dictionnaires en VBA.
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). Cela 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 !
Devenez rapidement opérationnel sur Excel avec Excel Efficace
