VBA Collection in Excel — die geordnete, wachsende Liste (und warum sie kein Dictionary ist)

TL;DR — Eine Collection ist eine eingebaute, geordnete Liste, die Sie mit .Add wachsen und mit .Remove schrumpfen lassen — kein ReDim, kein Raten der Größe im Voraus. Greifen Sie zu ihr, wenn Sie eine unbekannte Anzahl von Elementen der Reihe nach ansammeln und sie nicht per Schlüssel nachschlagen müssen. Vier Regeln halten sie ehrlich: sie ist 1-basiert (coll(1) ist das erste Element), Sie können ein Element nicht überschreiben (nur Add / Remove), ein doppelter Schlüssel wirft Fehler 457, und es gibt kein .Exists — das, was sie zu einem Dictionary machen würde.

Dim jobs As Collection
Set jobs = New Collection        ' Set + New: eine Collection ist ein Objekt

jobs.Add "Export report"         ' wächst bei Bedarf — keine Größe deklariert
jobs.Add "Email the team"
jobs.Add "Archive last month"

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

For Each task In jobs            ' läuft in Einfügereihenfolge
    Debug.Print task
Next task

Wenn Sie im Voraus nicht wissen, wie viele Dinge Sie sammeln werden — jede Zeile, die die Validierung nicht besteht, jeder eindeutige Kunde, dem Sie begegnen, jede Datei in einem Ordner —, ist ein Array unhandlich. Sie müssten eine Größe raten und unterwegs ReDim Preserve machen. Eine Collection ist VBAs Antwort: ein Objekt, das leer startet und mit jedem .Add um eins wächst, in der Reihenfolge, in der Sie Dinge hinzugefügt haben. Sie ist eines der nützlichsten Objekte der Sprache und zugleich eines der am stillsten falsch genutzten, weil sie wie ein Array aussieht und sich weder wie ein Array noch wie ein Dictionary verhält.

Was Sie lernen

Das mentale Modell — eine Collection ist eine geordnete Liste, kein indiziertes Array
Warum sie 1-basiert ist und wie sich dieser Off-by-one-Bug versteckt
Die Überschreib-Falle: coll(2) = "x" ändert nichts — es wirft einen Fehler
Wie Schlüssel funktionieren, warum Duplikate Fehler 457 werfen und das fehlende .Exists
Eine Entscheidungsregel: Collection vs. Array vs. Dictionary

Das mentale Modell: eine wachsende geordnete Liste, kein nummeriertes Regal

Ein Array ist ein nummeriertes Regal: Sie deklarieren Dim a(1 To 100), jedes Fach existiert von Anfang an, und Sie greifen direkt auf Fach i zu. Eine Collection ist ein Stapel Karten, den Sie immer weiter ergänzen: er startet leer, jedes .Add legt eine neue Karte der Reihe nach auf den Stapel, und .Count wächst mit. Sie können die n-te Karte lesen und eine herausziehen, aber Sie können nicht „Fach 5 zuweisen“ — es gibt kein festes Fach 5, nur die fünfte Karte, die gerade im Stapel liegt.

Diese eine Unterscheidung erklärt jede Regel unten. Weil es eine wachsende Liste ist und kein festes Regal, sind Positionen keine stabilen Adressen — sie sind nur der aktuelle Platz des Elements in der Reihe, und dieser Platz verschiebt sich, wenn Sie Dinge hinzufügen oder entfernen.

Die Regel, die den ersten Bug verursacht: eine Collection ist 1-basiert

Arrays in VBA sind standardmäßig 0-basiert (a(0) ist das erste Element). Eine Collection ist 1-basiert. Das erste Element ist coll(1); coll(0) wirft „Ungültiger Prozeduraufruf oder ungültiges Argument“ (Fehler 5).

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

Debug.Print c(1)        ' -> first
Debug.Print c(0)        ' -> Laufzeitfehler 5

Das beißt am härtesten, wenn Sie beide in einem Makro mischen — ein Array mit arr(0) und eine Collection mit coll(1) in derselben Schleife lesen, und ein Off-by-one ist fast garantiert. Die Regel ist schlicht auswendig zu lernen: Arrays beginnen bei 0, Collections beginnen bei 1. Wenn Sie eine Collection per Index durchlaufen, ist es immer For i = 1 To coll.Count.

Die Regel, die Array-Nutzer überrascht: Sie können ein Element nicht überschreiben

Bei einem Array ersetzt a(2) = "new" den Wert in Fach 2. Bei einer Collection ist dieselbe Idee ein Fehler:

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

c(1) = "new"            ' Kompilierungs-/Laufzeitfehler — ein Collection-Element ist per Index schreibgeschützt

Eine Collection stellt .Item schreibgeschützt bereit. Sie können c(1) abrufen, aber Sie können ihm nichts zuweisen. Um ein Element zu „ändern“, entfernen Sie es und fügen einen Ersatz hinzu, wobei Sie mit den Argumenten Before / After von .Add steuern, wo das neue landet:

c.Remove 1              ' das alte Element an Position 1 herausnehmen
c.Add "new", Before:=1  ' den Ersatz an dieselbe Stelle zurücklegen

Wenn Sie das oft tun, ist das ein Signal, dass die Collection das falsche Werkzeug ist — Sie wollen wahrscheinlich ein Array (für positionsbezogene Änderungen) oder ein Dictionary (für Änderung per Schlüssel). Eine Collection ist am besten, wenn Elemente hinein- und herauskommen, aber nicht an Ort und Stelle neu geschrieben werden.

Die Regel zu Schlüsseln: eindeutige Strings, Fehler 457 bei Duplikaten und kein Exists

.Add nimmt einen optionalen Schlüssel — einen String, den Sie später statt einer numerischen Position nutzen können:

Dim prices As Collection
Set prices = New Collection
prices.Add 9.99, "apple"      ' Wert zuerst, dann Schlüssel
prices.Add 4.5, "pear"

Debug.Print prices("apple")   ' -> 9.99   (Nachschlagen per Schlüssel)

Das sieht aus wie ein Dictionary, und diese Ähnlichkeit ist eine Falle. Zwei harte Grenzen:

Schlüssel müssen eindeutig sein. Fügen Sie ein zweites Element mit einem vorhandenen Schlüssel hinzu, bekommen Sie „Dieser Schlüssel ist bereits einem Element dieser Auflistung zugeordnet“ (Fehler 457). Es gibt kein „hinzufügen oder aktualisieren“ — ein wiederholter Schlüssel ist ein harter Fehler.
Es gibt kein .Exists. Eine Collection gibt Ihnen keinen sauberen Weg zu fragen „existiert dieser Schlüssel schon?“ Ihre einzigen Optionen sind hässlich: das Nachschlagen in On Error Resume Next einpacken und Err.Number prüfen, oder Fehler 457 beim .Add abfangen.

' Der umständliche „existiert dieser Schlüssel?“-Tanz, den eine Collection Ihnen aufzwingt
Dim v As Variant
On Error Resume Next
v = prices("banana")
Dim found As Boolean
found = (Err.Number = 0)
On Error GoTo 0

Dieses fehlende .Exists ist der klarste einzelne Grund, ein Dictionary vorzuziehen, sobald Schlüssel wichtig sind: ein Scripting.Dictionary hat .Exists(key), lässt Sie per Schlüssel überschreiben und wirft bei einem wiederholten Schlüssel nicht. Siehe VBA Dictionary für die schlüsselzentrierte Struktur, die eine Collection nur imitiert.

Die Regel zum Verkleinern einer Collection: rückwärts entfernen

Weil sich Positionen verschieben, überspringt das Entfernen von Elementen per Index innerhalb einer Vorwärtsschleife Elemente — dasselbe Versagen wie beim Löschen von Zeilen oder beim Verwenden von For Each auf einer sich ändernden Collection. Entfernen Sie Element 2, rutscht das alte Element 3 in Position 2, aber Ihre Schleife ist bereits zu Index 3 weitergezogen:

' KAPUTT — Indizes verschieben sich beim Entfernen
Dim i As Long
For i = 1 To c.Count
    If c(i) = "drop" Then c.Remove i   ' überspringt das nachgerutschte Element
Next i

' KORREKT — abwärts zählen, damit Entfernungen nicht verschieben, was übrig ist
For i = c.Count To 1 Step -1
    If c(i) = "drop" Then c.Remove i
Next i

Das ist dieselbe Rückwärts-Iterations-Regel aus VBA For-Schleife und der Grund, warum Sie innerhalb eines VBA For Each-Durchlaufs nicht sicher löschen können. Immer wenn die Zugehörigkeit während einer Schleife schrumpft, zählen Sie abwärts.

Wann Collection, wann Array, wann Dictionary

Alle drei halten eine Gruppe von Dingen. Sie sind für verschiedene Aufgaben gut:

Array — ein fest dimensionierter, nummerierter Satz, an dem Sie positions- oder zahlenbezogene Arbeit erledigen (Mathematik, matrixartiger Zugriff, ein ganzes Range auf einmal lesen). Am schnellsten für numerische Massenarbeit; unhandlich, wenn die Anzahl unbekannt ist. Siehe VBA Array.
Collection — eine geordnete Liste unbekannter Länge, die Sie durch Anhängen aufbauen und der Reihe nach lesen. Perfekt, wenn Sie „alle passenden Elemente“ ansammeln und sie einmal durchlaufen. Kein verlässliches Nachschlagen per Schlüssel, keine Änderungen an Ort und Stelle.
Dictionary — ein Schlüssel → Wert-Speicher, wenn Sie schnelles Nachschlagen, Entdopplung oder „habe ich das schon gesehen?“ brauchen. Sein .Exists und das Überschreiben per Schlüssel sind genau das, was einer Collection fehlt. Siehe VBA Dictionary.

Das schnelle Indiz: Elemente der Reihe nach anhängen → Collection; sie per Name nachschlagen oder entdoppeln → Dictionary; fest dimensionierte numerische Rechnerei → Array.

Wie ExcelMaster hilft

Den richtigen Container wählen — und daran denken, dass eine Collection 1-basiert ist, nicht überschrieben werden kann und kein Exists hat — ist die Art von Low-Level-Entscheidung, die Zeit frisst, bevor Sie auch nur in die Nähe des eigentlich gewünschten Ergebnisses kommen.

ExcelMaster lässt Sie das überspringen. Beschreiben Sie das Ergebnis — „liste jeden Kunden in Spalte C, der mehr als einmal vorkommt“ oder „sammle alle überfälligen Rechnungen in einem Übersichtsblatt“ — und es schreibt und führt die Logik aus, wählt die richtige Struktur (ein Set zur Entdopplung, eine geordnete Liste zur Ansammlung), ohne dass Sie Collection gegen Dictionary von Hand abwägen. Wenn Sie ein Makro nach Zeitplan pflegen, wählen Sie den Container weiterhin selbst; für den alltäglichen Einzelfall ist das Nennen des Ziels schneller, als die Datenstruktur von Hand richtig zu treffen.

Häufig gestellte Fragen

Wie füge ich Elemente zu einer VBA Collection hinzu?

Erzeugen Sie sie mit Set c = New Collection, dann rufen Sie c.Add value auf. Die Liste wächst automatisch — keine Größe zu deklarieren. Sie können optional einen Schlüssel übergeben (c.Add value, "mykey") für späteres Nachschlagen und Before / After, um die Einfügeposition zu steuern. Lesen Sie Elemente mit c(1) (1-basiert) oder c("mykey") zurück und zählen Sie sie mit c.Count.

Ist eine VBA Collection 0-basiert oder 1-basiert?

1-basiert. Das erste Element ist c(1) und das letzte c(c.Count); c(0) wirft Laufzeitfehler 5. Das ist das Gegenteil von VBA-Arrays, die standardmäßig 0-basiert sind — eine häufige Quelle von Off-by-one-Bugs, wenn Sie beide in einem Makro nutzen.

Was ist der Unterschied zwischen einer Collection und einem Dictionary in VBA?

Eine Collection ist eine geordnete Liste, optimiert fürs Anhängen und Lesen in Reihenfolge; ein Scripting.Dictionary ist ein Schlüssel → Wert-Speicher, optimiert fürs Nachschlagen. Die entscheidenden Unterschiede: Ein Dictionary hat .Exists(key) und lässt Sie einen Wert per Schlüssel überschreiben, während eine Collection keins von beidem hat — das Hinzufügen eines doppelten Schlüssels wirft Fehler 457, und Elemente sind per Index schreibgeschützt. Nutzen Sie ein Dictionary, sobald Schlüssel oder Entdopplung wichtig sind.

Warum bekomme ich Fehler 457 beim Hinzufügen zu einer Collection?

Fehler 457 — „Dieser Schlüssel ist bereits einem Element dieser Auflistung zugeordnet“ — bedeutet, dass Sie .Add value, key mit einem bereits vorhandenen Schlüssel aufgerufen haben. Collection-Schlüssel müssen eindeutig sein, und es gibt keinen eingebauten Weg, das vorher zu prüfen. Verfolgen Sie Schlüssel separat, fangen Sie den Fehler ab oder nutzen Sie ein Scripting.Dictionary, dessen .Exists-Prüfung das Problem ganz vermeidet.

Wie prüfe ich, ob ein Schlüssel in einer Collection existiert?

Es gibt keinen sauberen Weg — das ist die größte Schwäche der Collection. Sie müssen das Nachschlagen in On Error Resume Next einpacken, c(key) versuchen und Err.Number prüfen (0 bedeutet, er existiert). Wenn Sie diese Prüfung regelmäßig brauchen, wechseln Sie zu einem Scripting.Dictionary und nutzen Sie d.Exists(key).

Getestet in

Getestet mit: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 01.08.2026.

Verwandte Anleitungen: VBA Dictionary · VBA Array · VBA For Each · VBA For-Schleife · VBA With