VBA Collection dans Excel — la liste ordonnée qui s'agrandit (et pourquoi ce n'est pas un Dictionary)

TL;DR — une Collection est une liste ordonnée intégrée que vous agrandissez avec .Add et rétrécissez avec .Remove — sans ReDim, sans deviner la taille à l'avance. Sortez-la quand vous accumulez un nombre inconnu d'éléments dans l'ordre et que vous n'avez pas besoin de les retrouver par clé. Quatre règles la gardent honnête : elle est en base 1 (coll(1) est le premier élément), vous ne pouvez pas écraser un élément (seulement Add / Remove), une clé en double déclenche l'erreur 457, et il n'y a aucun .Exists — ce qui, au fond, la distingue d'un Dictionary.

Dim jobs As Collection
Set jobs = New Collection        ' Set + New : une Collection est un objet

jobs.Add "Export report"         ' s'agrandit à la demande — aucune taille déclarée
jobs.Add "Email the team"
jobs.Add "Archive last month"

Debug.Print jobs.Count           ' -> 3
Debug.Print jobs(1)              ' -> Export report   (base 1 !)

For Each task In jobs            ' parcourt dans l'ordre d'insertion
    Debug.Print task
Next task

Quand vous ignorez d'emblée combien de choses vous allez collecter — chaque ligne qui échoue à la validation, chaque client unique rencontré, chaque fichier d'un dossier — un tableau est malcommode. Il faudrait deviner une taille et faire des ReDim Preserve au fur et à mesure. Une Collection est la réponse de VBA : un objet qui part vide et grandit d'un .Add à la fois, dans l'ordre où vous ajoutez les choses. C'est l'un des objets les plus utiles du langage, et aussi l'un des plus discrètement mal employés, car il ressemble à un tableau et ne se comporte ni comme un tableau ni comme un Dictionary.

Ce que vous allez apprendre

Le modèle mental — une Collection est une liste ordonnée, pas un tableau indexé
Pourquoi elle est en base 1, et comment ce décalage se cache
Le piège de l'écrasement : coll(2) = "x" ne modifie pas — il déclenche une erreur
Comment fonctionnent les clés, pourquoi les doublons déclenchent l'erreur 457, et le .Exists manquant
Une règle de décision : Collection, Array ou Dictionary

Le modèle mental : une liste ordonnée qui grandit, pas une étagère numérotée

Un tableau est une étagère numérotée : vous déclarez Dim a(1 To 100), chaque case existe dès le départ, et vous atteignez la case i directement. Une Collection est une pile de cartes que vous continuez d'alimenter : elle part vide, chaque .Add dépose une nouvelle carte sur la pile dans l'ordre, et .Count grandit au fil de l'eau. Vous pouvez lire la n-ième carte et en retirer une, mais vous ne pouvez pas « affecter la case 5 » — il n'y a pas de case 5 fixe, juste la cinquième carte actuellement dans la pile.

Cette seule distinction explique chaque règle ci-dessous. Parce que c'est une liste qui grandit et non une étagère figée, les positions ne sont pas des adresses stables — ce n'est que la place actuelle de l'élément dans la file, et cette place se décale quand vous ajoutez ou retirez des choses.

La règle qui cause le premier bug : une Collection est en base 1

Les tableaux VBA sont en base 0 par défaut (a(0) est le premier élément). Une Collection est en base 1. Le premier élément est coll(1) ; coll(0) déclenche « Appel de procédure ou argument incorrect » (erreur 5).

Dim c As Collection
Set c = New Collection
c.Add "first"
c.Add "second"

Debug.Print c(1)        ' -> first
Debug.Print c(0)        ' -> erreur d'exécution 5

Ça mord le plus fort quand vous mélangez les deux dans une même macro — lire un tableau avec arr(0) et une collection avec coll(1) dans la même boucle, et le décalage d'une unité est quasi garanti. La règle se retient simplement : les tableaux commencent à 0, les Collections commencent à 1. Quand vous parcourez une Collection par indice, c'est toujours For i = 1 To coll.Count.

La règle qui surprend les habitués des tableaux : on ne peut pas écraser un élément

Avec un tableau, a(2) = "new" remplace la valeur de la case 2. Avec une Collection, la même idée est une erreur :

Dim c As Collection
Set c = New Collection
c.Add "old"

c(1) = "new"            ' Erreur de compilation/exécution — un élément de Collection est en lecture seule par indice

Une Collection expose .Item en lecture seule. Vous pouvez lire c(1), mais vous ne pouvez pas lui affecter de valeur. Pour « changer » un élément, vous le retirez et ajoutez un remplaçant, en utilisant les arguments Before / After de .Add pour contrôler où atterrit le nouveau :

c.Remove 1              ' retirer l'ancien élément en position 1
c.Add "new", Before:=1  ' remettre le remplaçant au même endroit

Si vous vous surprenez à faire cela souvent, c'est le signe que la Collection est le mauvais outil — vous voulez probablement un tableau (pour les modifications positionnelles) ou un Dictionary (pour la modification par clé). Une Collection donne le meilleur d'elle-même quand les éléments entrent et sortent mais ne sont pas réécrits en place.

La règle des clés : des chaînes uniques, l'erreur 457 sur les doublons, et pas d'Exists

.Add accepte une clé facultative — une chaîne que vous pourrez ensuite utiliser à la place d'une position numérique :

Dim prices As Collection
Set prices = New Collection
prices.Add 9.99, "apple"      ' la valeur d'abord, puis la clé
prices.Add 4.5, "pear"

Debug.Print prices("apple")   ' -> 9.99   (recherche par clé)

Ça ressemble à un Dictionary, et cette ressemblance est un piège. Deux limites strictes :

Les clés doivent être uniques. Ajoutez un second élément avec une clé existante et vous obtenez « Cette clé est déjà associée à un élément de cette collection » (erreur 457). Il n'y a pas d'« ajouter ou mettre à jour » — une clé répétée est une erreur pure et simple.
Il n'y a aucun .Exists. Une Collection ne vous offre aucun moyen propre de demander « cette clé existe-t-elle déjà ? ». Vos seules options sont disgracieuses : envelopper la recherche dans On Error Resume Next et vérifier Err.Number, ou intercepter l'erreur 457 sur le .Add.

' La danse maladroite du « cette clé existe-t-elle ? » qu'impose une Collection
Dim v As Variant
On Error Resume Next
v = prices("banana")
Dim found As Boolean
found = (Err.Number = 0)
On Error GoTo 0

Ce .Exists manquant est la raison la plus nette de préférer un Dictionary dès que les clés comptent : un Scripting.Dictionary possède .Exists(key), permet d'écraser par clé, et ne lève rien sur une répétition. Voyez VBA Dictionary pour la structure orientée clé qu'une Collection ne fait qu'imiter.

La règle pour rétrécir une Collection : retirer à l'envers

Parce que les positions se décalent, retirer des éléments par indice dans une boucle avant saute des éléments — le même échec que lorsque vous supprimez des lignes ou utilisez For Each sur une collection qui change. Retirez l'élément 2, et l'ancien élément 3 glisse en position 2, mais votre boucle est déjà passée à l'indice 3 :

' CASSÉ — les indices se décalent à mesure que vous retirez
Dim i As Long
For i = 1 To c.Count
    If c(i) = "drop" Then c.Remove i   ' saute l'élément qui a glissé vers le haut
Next i

' CORRECT — décompter pour que les retraits ne perturbent pas le reste
For i = c.Count To 1 Step -1
    If c(i) = "drop" Then c.Remove i
Next i

C'est la même règle de parcours inversé que dans VBA For Loop, et la raison pour laquelle vous ne pouvez pas supprimer sans risque à l'intérieur d'un parcours VBA For Each. Chaque fois que l'appartenance rétrécit pendant une boucle, décomptez.

Quand choisir une Collection, un Array ou un Dictionary

Les trois contiennent un groupe de choses. Ils excellent à des tâches différentes :

Array — un ensemble à taille fixe, numéroté sur lequel vous ferez du travail positionnel ou numérique (calcul, accès de type matrice, lecture d'un Range entier d'un coup). Le plus rapide pour le calcul numérique en masse ; malcommode quand le nombre est inconnu. Voyez VBA Array.
Collection — une liste ordonnée de longueur inconnue que vous construisez par ajout et lisez dans l'ordre. Parfaite quand vous accumulez « tous les éléments qui correspondent » et les parcourrez une fois. Aucune recherche par clé sur laquelle compter, aucune modification en place.
Dictionary — un magasin clé → valeur quand il vous faut une recherche rapide, un dédoublonnage ou un « l'ai-je déjà vu ? ». Son .Exists et son écrasement par clé sont exactement ce qui manque à une Collection. Voyez VBA Dictionary.

Le repère rapide : empiler des éléments dans l'ordre → Collection ; les retrouver par un nom ou les dédoublonner → Dictionary ; du calcul numérique à taille fixe → Array.

Comment ExcelMaster aide

Choisir le bon conteneur — et se rappeler qu'une Collection est en base 1, ne peut pas être écrasée et n'a pas d'Exists — est le genre de décision bas niveau qui engloutit du temps avant même que vous n'approchiez du résultat que vous vouliez.

ExcelMaster vous laisse zapper cette étape. Décrivez le résultat — « liste chaque client de la colonne C qui apparaît plus d'une fois » ou « rassemble toutes les factures en retard dans une feuille de synthèse » — et il écrit et exécute la logique, en choisissant la bonne structure (un ensemble pour le dédoublonnage, une liste ordonnée pour l'accumulation) sans que vous ayez à peser Collection contre Dictionary à la main. Quand vous maintenez une macro planifiée, c'est encore vous qui choisirez le conteneur ; pour le coup ponctuel du quotidien, énoncer l'objectif va plus vite que réussir la structure de données à la main.

Questions fréquentes

Comment ajouter des éléments à une Collection VBA ?

Créez-la avec Set c = New Collection, puis appelez c.Add value. La liste grandit automatiquement — aucune taille à déclarer. Vous pouvez passer une clé facultative (c.Add value, "mykey") pour une recherche ultérieure, et Before / After pour contrôler la position d'insertion. Relisez les éléments avec c(1) (base 1) ou c("mykey"), et comptez-les avec c.Count.

Une Collection VBA est-elle en base 0 ou en base 1 ?

En base 1. Le premier élément est c(1) et le dernier est c(c.Count) ; c(0) déclenche l'erreur d'exécution 5. C'est l'inverse des tableaux VBA, qui sont en base 0 par défaut — une source fréquente de bugs de décalage d'une unité quand vous utilisez les deux dans une même macro.

Quelle est la différence entre une Collection et un Dictionary en VBA ?

Une Collection est une liste ordonnée optimisée pour l'ajout et la lecture dans l'ordre ; un Scripting.Dictionary est un magasin clé → valeur optimisé pour la recherche. Les différences décisives : un Dictionary possède .Exists(key) et permet d'écraser une valeur par clé, tandis qu'une Collection n'a ni l'un ni l'autre — ajouter une clé en double déclenche l'erreur 457, et les éléments sont en lecture seule par indice. Utilisez un Dictionary dès que les clés ou le dédoublonnage comptent.

Pourquoi ai-je l'erreur 457 en ajoutant à une Collection ?

L'erreur 457 — « Cette clé est déjà associée à un élément de cette collection » — signifie que vous avez appelé .Add value, key avec une clé qui existe déjà. Les clés d'une Collection doivent être uniques, et il n'y a aucun moyen intégré de vérifier d'abord. Soit vous suivez les clés à part, soit vous interceptez l'erreur, soit vous utilisez un Scripting.Dictionary, dont le contrôle .Exists évite entièrement le problème.

Comment vérifier qu'une clé existe dans une Collection ?

Il n'y a pas de moyen propre — c'est la plus grande faiblesse de la Collection. Vous devez envelopper la recherche dans On Error Resume Next, tenter c(key), et inspecter Err.Number (0 signifie qu'elle existe). Si vous avez besoin de ce contrôle régulièrement, passez à un Scripting.Dictionary et utilisez d.Exists(key).

Testé dans

Testé avec : Excel 365 (Windows 11), VBA 7.1 — dernière vérification le 01/08/2026.

Guides associés : VBA Dictionary · VBA Array · VBA For Each · VBA For Loop · VBA With