Collections & Dictionary

Arrays sind super, wenn du die Anzahl der Elemente kennst. Sobald du aber laufend Werte hinzufügst und entfernst oder über einen Schlüssel zugreifen willst, sind Collection und Dictionary die bessere Wahl. Beide wachsen dynamisch mit — ohne ReDim.

Das Collection-Objekt

Eine Collection ist in VBA eingebaut, du brauchst keinen Verweis. Du legst sie mit New an und fügst Elemente mit Add hinzu:

Option Explicit

Public Sub CollectionBasics()
    Dim namen As Collection
    Set namen = New Collection

    namen.Add "Anna"
    namen.Add "Ben"
    namen.Add "Carla"

    Debug.Print namen.Count       ' 3
    Debug.Print namen.Item(1)     ' Anna (1-basiert!)
    Debug.Print namen(2)          ' Ben  (.Item ist Standard)
End Sub

Wichtig: Eine Collection ist immer 1-basiert. Das erste Element hat Index 1, nicht 0. Item ist die Standardeigenschaft, deshalb kannst du auch kurz namen(2) schreiben.

Entfernen mit Remove

Remove löscht ein Element über seinen Index. Achtung: Danach rutschen alle folgenden Elemente eine Position nach vorne:

namen.Remove 1        ' "Anna" raus, "Ben" ist jetzt Index 1
Debug.Print namen(1)  ' Ben
Debug.Print namen.Count ' 2

Willst du eine Collection in einer Schleife leeren, arbeite von hinten nach vorne (For i = .Count To 1 Step -1), sonst überspringst du durch das Nachrücken Elemente.

Add mit Schlüssel

Du kannst jedem Element beim Add einen eindeutigen String-Schlüssel geben und später direkt darüber zugreifen — ohne den Index zu kennen:

Dim preise As Collection
Set preise = New Collection

preise.Add 19.99, "Apfel"     ' Wert, dann Schlüssel
preise.Add 9.5, "Birne"

Debug.Print preise("Apfel")   ' 19.99
Debug.Print preise.Item("Birne") ' 9.5

Der Schlüssel muss ein String sein und eindeutig. Fügst du denselben Schlüssel zweimal hinzu, gibt es Laufzeitfehler 457 ("Der Schlüssel ist bereits vorhanden").

For Each über eine Collection

Am bequemsten durchläufst du eine Collection mit For Each. Die Laufvariable muss ein Variant oder Object sein:

Dim namen As Collection
Set namen = New Collection
namen.Add "Anna": namen.Add "Ben": namen.Add "Carla"

Dim n As Variant
For Each n In namen
    Debug.Print n
Next n

Die Grenzen der Collection

So praktisch die Collection ist — sie hat zwei ärgerliche Lücken:

• Kein Exists. Du kannst nicht sauber prüfen, ob ein Schlüssel existiert. Der übliche Umweg ist ein On Error-Konstrukt: Zugriff versuchen, Fehler abfangen.
• Keys sind nicht auslesbar. Du kommst über den Schlüssel an den Wert, aber du bekommst die Liste der vergebenen Schlüssel nicht zurück. Es gibt kein .Keys.

' Umständliche Existenzprüfung bei einer Collection:
Public Function CollHatKey(c As Collection, key As String) As Boolean
    Dim dummy As Variant
    On Error Resume Next
    dummy = c(key)
    CollHatKey = (Err.Number = 0)
    On Error GoTo 0
End Function

Genau hier setzt das Dictionary an.

Das Scripting.Dictionary

Das Dictionary stammt aus der "Microsoft Scripting Runtime". Es speichert Paare aus Schlüssel und Wert und kann alles, was die Collection nicht kann. Es gibt zwei Wege, es einzubinden:

Früh gebunden (empfohlen zum Entwickeln): Im VBA-Editor unter Extras → Verweise die "Microsoft Scripting Runtime" anhaken. Dann hast du IntelliSense:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Spät gebunden (kein Verweis nötig, robuster beim Verteilen): über CreateObject:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Beide Varianten verhalten sich gleich. Bei später Bindung fehlt nur die Autovervollständigung.

Dictionary: Add, Exists, Keys, Items, Remove

Public Sub DictBasics()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "Apfel", 19.99     ' Schlüssel, dann Wert
    dict.Add "Birne", 9.5
    dict.Add "Kirsche", 4.2

    ' Existenz prüfen — sauber, kein On Error nötig:
    If dict.Exists("Apfel") Then
        Debug.Print dict("Apfel")   ' 19.99
    End If

    ' Wert ändern oder neu anlegen in einem Schritt:
    dict("Apfel") = 21.99           ' überschreibt vorhandenen Schlüssel

    ' Alle Schlüssel und Werte auslesen:
    Dim k As Variant
    For Each k In dict.Keys
        Debug.Print k & " = " & dict(k)
    Next k

    Debug.Print dict.Count          ' 3
    dict.Remove "Birne"             ' einzelnes Paar löschen
    dict.RemoveAll                  ' alles löschen
End Sub

Beachte den Unterschied zur Collection: Beim Dictionary steht der Schlüssel zuerst, dann der Wert. .Keys und .Items liefern jeweils ein Array — damit kommst du an alle Schlüssel bzw. Werte heran.

Ein Detail zum Zuweisen: dict("Neu") = 5 legt den Schlüssel automatisch an, wenn er noch nicht existiert. Mit dict.Add dagegen gibt es einen Fehler, wenn der Schlüssel schon da ist. Zum sicheren Überschreiben nimm die Zuweisung.

Collection oder Dictionary — was nehmen?

Faustregel: Brauchst du nur eine einfache, wachsende Liste ohne Schlüssel, reicht die Collection. Sobald du über Schlüssel arbeitest, prüfen willst, ob etwas existiert, oder die Schlüssel/Werte auslesen musst, nimm das Dictionary.

Beispiel: Zählen und Gruppieren

Das ist die Paradedisziplin des Dictionary. Hier zählst du, wie oft jeder Ort in einer Liste vorkommt:

Public Sub OrteZaehlen()
    Dim orte As Variant
    orte = Array("Berlin", "Hamburg", "Berlin", "Köln", "Berlin", "Hamburg")

    Dim zaehler As Object
    Set zaehler = CreateObject("Scripting.Dictionary")

    Dim ort As Variant
    For Each ort In orte
        If zaehler.Exists(ort) Then
            zaehler(ort) = zaehler(ort) + 1   ' hochzählen
        Else
            zaehler(ort) = 1                  ' erstmalig anlegen
        End If
    Next ort

    Dim k As Variant
    For Each k In zaehler.Keys
        Debug.Print k & ": " & zaehler(k)
    Next k
    ' Berlin: 3
    ' Hamburg: 2
    ' Köln: 1
End Sub

Dasselbe Muster funktioniert prima mit Daten aus einem Recordset: Du durchläufst die Datensätze und summierst pro Schlüssel (z. B. Umsatz pro Kunde) in ein Dictionary — deutlich schneller und flexibler als eine verschachtelte Abfrage im Code.

Ein Recordset zeilenweise durchlaufen und in ein Dictionary aggregieren ist im Speicher blitzschnell. Für große Datenmengen ist eine GROUP BY-Abfrage in SQL aber oft die sauberere Lösung — nutze das Dictionary vor allem, wenn du die Zwischenwerte direkt im Code weiterverarbeitest.

Zusammengefasst

• Die Collection ist eingebaut, 1-basiert und ideal für einfache, wachsende Listen; Add, Item, Count, Remove.
• Ihre Schwächen: kein Exists, Schlüssel lassen sich nicht auslesen — Existenzprüfung nur per On Error.
• Das Scripting.Dictionary (Verweis "Microsoft Scripting Runtime" oder CreateObject) speichert Schlüssel-Wert-Paare mit Exists, Keys, Items, Remove.
• Beim Dictionary kommt der Schlüssel zuerst; dict(key) = wert legt an oder überschreibt.
• Nimm das Dictionary, sobald du über Schlüssel arbeitest, Existenz prüfst oder zählst/gruppierst.