Arrays, Collections und Dictionarys

2008, 6/2008, VBA und Programmiertechniken

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Unter Access bietet sich das Speichern von Daten in Tabellen an. Die legen Sie in der Datenbankdatei an und greifen über gebundene Formulare, Berichte und Steuerelemente darauf zu. Unter VBA geschieht dies fast genauso bequem mithilfe von DAO- oder ADO-Recordsets. Manche Konstellationen schließen die Verwendung von gebundenen Recordsets aus und Sie müssen auf andere Strukturen zum temporären Speichern Ihrer Daten zurückgreifen. Dieser Beitrag stellt die verschiedenen Möglichkeiten und ihre Vor- und Nachteile vor.

Wollen Sie nur eine einzige Information wie etwa den Namen einer Person so festhalten, dass Sie jederzeit schnell darauf zugreifen können, reicht eine simple String-Variable aus:

Dim strName As String
strName = "Sascha Trowitzsch"

Sobald Sie mehrere Daten zu einer Einheit zusammenfassen möchten, helfen die Standarddatentypen nicht mehr weiter. Hier können Sie benutzerdefinierte Strukturen mit einem festen Aufbau verwenden, deren Definition etwa so aussieht:

Type TPerson
Vorname As String
Nachname As String
Geburtsdatum As Date
End Type

Um diese Struktur einzusetzen, deklarieren Sie eine Variable auf Basis der Struktur und füllen deren Element wie in den folgenden Beispielanweisungen:

Dim Autor As TPerson
Autor.Vorname = "Sascha"
Autor.Nachname = "Trowitzsch"
Autor.Geburtsdatum = "11.1.1918"

Damit stoßen Sie spätestens dann an Ihre Grenzen, wenn Sie mehrere gleichartige Daten in einer Liste zusammenfassen wollen – im Beispiel etwa eine Gruppe von Personen. Dann benötigen Sie ein Datenfeld, wobei einem als Erstes das Array einfällt. Tatsächlich gibt es unter Access und VBA aber noch einige Möglichkeiten mehr:

  • Arrays
  • Collections
  • Dictionarys
  • TempVars (nur Access 2007)
  • ADODB-Recordsets
  • API-SafeArrays

Die ersten fünf Varianten stellen wir in den folgenden Abschnitten vor, die sechste würde leider den Rahmen dieses Beitrags sprengen.

ADODB-Recordsets

Wenn man an ADO-Recordsets denkt, zieht man direkt Parallelen zu ihrem DAO-Pendant und damit zur Bindung an Tabellen. Im Gegensatz zu DAO benötigt ein ADO-Recordset jedoch nicht zwingend einen Bezug zu einer physischen Datenherkunft und kann als reiner Datencontainer im Speicher dienen. Solche Recordsets, in denen die Eigenschaft Connection nicht gesetzt ist, nennt man auch ungebundene Recordsets (es gibt auch noch die Disconnected Recordsets, die zunächst mit Daten etwa aus einer Tabelle gefüllt und dann von dieser getrennt werden – mehr dazu im Beitrag Disconnected Recordsets, Shortlink 437).

Listing 1 zeigt beispielhaft, wie Sie solch ein ungebundenes Recordset anlegen und füllen.

Listing 1: Disconnected ADODB-Recordset als Variablenspeicher

Sub ADODBCollection()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Fields.Append "Nachname", adBSTR
    rst.Fields.Append "Vorname", adBSTR
    rst.Fields.Append "Geburtsdatum", adDate
    rst.Open , , adOpenKeyset
    rst.AddNew
    rst!Nachname = "Trowitzsch"
    rst!Vorname = "Sascha"
    rst!Geburtsdatum = "11.1.1918"
    rst.MoveFirst
    rst.Find "Nachname='Trowitzsch'"
    If Not rst.EOF Then
        Debug.Print rst(0), rst(1), rst(2)
    End If
    rst.Close
    End Sub

Wichtig ist hier die Open-Methode des Recordsets, die den Connection-Parameter auslässt, wodurch das Recordset keinerlei Bezug zu einer Tabelle der Datenbank selbst hat. Bei einem Disconnected Recordset bindet man das Recordset zunächst an eine Tabelle und deaktiviert anschließend die Verbindung:

Set rst.Connection = Nothing

Danach hat man wieder ein rein speicherbasiertes Recordset, in dem Sie Daten ändern können, ohne dass sich dies auf die Daten aus der Herkunftstabelle niederschlägt.

Das große Plus von ADODB ist seine hohe Flexibilität. Das Recordset lässt verschiedenste Operationen zu, wobei gerade die Suchmöglichkeiten einen wesentlichen Vorteil gegenüber den weiter unten vorgestellten Arrays und Collections ausmachen. Das hat jedoch seinen Preis: Der Verwaltungsaufwand für das Objekt und seine Schnittstelle ist hoch, was zulasten der Performance geht. Wer Millionen Berechnungen auf Basis der Elemente dieses Datencontainers ausführen möchte, wird am ADODB-Recordset keine rechte Freude haben.

Zum Einsatz kommen ungebundene Recordsets als reiner Variablenspeicher daher vor allem, wenn kompliziertere Operationen ausgeführt werden sollen, die sich mit Arrays und Collections nur durch hohen Programmieraufwand realisieren ließen.

TempVars

Mit Access 2007 hat Microsoft einen neuen Typ von Auflistungsvariablen eingeführt: die TempVars. Es handelt sich dabei um ein eindimensionales Array, das allerdings eher einer Collection ähnelt, weil Sie dessen Elemente über einen Key ansprechen können. Speichern können Sie in TempVars jedoch nur Standarddatentypen, aber keine Objekte, Arrays oder benutzerdefinierte Typen.

TempVars müssen Sie nicht extra als Variable deklarieren, weil Access die Auflistung von vornherein als Eigenschaft des Application-Objekts mitbringt. Neue Elemente fügen Sie etwa so hinzu:

TempVars.Add "Nachname", "Trowitzsch"
TempVars.Add "Vorname", "Sascha""
TempVars.Add "Geburtsdatum", CDate("1.11.1918")
TempVars("Vorname") = "Alexander" 'Inhalt ändern
Debug.Print TempVars("Vorname")

Das ist eine komfortable Möglichkeit, Daten schnell und ohne großen Aufwand in einem Datenfeld zu speichern. TempVars haben zudem eine ganz besondere Eigenschaft: Sie gehen nicht verloren. Während globale Variablen beim Auftreten eines nicht behandelten Fehlers unter VBA grundsätzlich zerstört werden, bleiben TempVars erhalten. Sie sind damit sichere Datenspeicher und wickeln ihre Geschäfte zudem recht flott ab. Die Performance der Auflistung beim Zugriff auf die enthaltenen Elemente ist sehr gut.

Andererseits sind die Features nicht gerade atemberaubend. Ein Knackpunkt etwa ist die fehlende Möglichkeit, neue TempVars-Variablen anzulegen. Man ist auf die eine eingebaute und flache Liste angewiesen. Damit eignen sich TempVars in erster Linie zum Speichern anwendungsbezogener Daten, in denen Sie beispielsweise einzelne Einstellungen unterbringen möchten.

Arrays

Der klassische Variablentyp zur Speicherung mehrerer Elemente ist das Array. Es kann beliebige Datentypen aufnehmen, also auch Objekte und benutzerdefinierte Typen und arbeitet wegen seines einfachen Aufbaus außerordentlich schnell.

Das Beispiel aus Listing 2 speichert mehrere der in einem früheren Beispiel angelegten Type-Strukturen in Form einer Personenliste.

Listing 3: Variablen setzen mit DAO

Sub SetVarDAO(ByVal strVarName As String, Wert As Variant)
    With rstVars
    .FindFirst "VarName='" & strVarName & "'"
    If .NoMatch Then
        .AddNew
        !VarName = strVarName
    Else
        .Edit
    End If
    !VarWert = CStr(Wert)
    .Update
    End With
    End Sub

Einige Besonderheiten sind im Umgang mit Arrays zu beachten:

  • Die Zahl der enthaltenen Elemente können Sie zwar schon bei der Deklaration der Variable voreinstellen (statisches Array), günstiger ist jedoch die Deklaration als dynamisches Array ohne Angabe der Dimensionen. Diese können Sie jederzeit ändern, was bedeutet, dass Sie beispielsweise nachträglich neue Elemente hinzufügen können. Anfangs initialisieren Sie das dynamische Array daher mit einem einzigen Element: ReDim arrPerson(0)
  • Wenn Sie neue Elemente in ein dynamisches Array aufnehmen, müssen Sie es zuvor neu dimensionieren. Damit bei diesem Vorgang nicht alle bereits gespeicherten Elemente verloren gehen, fügen Sie das Schlüsselwort Preserve ein: ReDim Preserve arrPerson(1)
  • Der Zugriff auf Elemente des Arrays geschieht immer über ihre Ordinalzahl, also den Index des Elements. Sie können ein Element nicht wie bei Collection– und Dictionary-Auflistungen über einen Schlüssel ansprechen. Damit entfällt auch eine gezielte Suche nach Elementen, sodass Sie Suchmechanismen selbst programmieren müssen.
  • Die Elemente eines Arrays können Sie über die Direktive Erase leeren. Das ist gerade beim Speichern von Objekten im Array wichtig, weil Erase dann alle Objektbezüge auf Nothing setzt. Somit bestehen keine Referenzen auf die Ursprungsobjekte mehr, die dazu führen könnten, dass diese im Speicher verbleiben.

Die Zahl der Elemente fragen Sie mit der Funktion UBound ab:

Debug.Print UBound(arrPerson) + 1

Da UBound den Index des letzten Elements zurückgibt, das Array normal aber nullbasiert ist, ergibt sich die Anzahl aus dem höchstem Index plus eins. Ist das Array noch gar nicht dimensioniert worden, liefert der Aufruf von UBound nicht etwa den Wert 0, sondern löst einen Fehler aus.

Arrays können auch andere Arrays als Elemente aufnehmen. Dadurch ist, vor allem im Zusammenspiel mit benutzerdefinierten Typen, der Aufbau von Strukturen möglich. So einfach und schnell solche Gebilde auch sind, ein Manko haftet ihnen doch an: Um ein Element im Array zu finden, müssen Sie leider alle Elemente in einer Schleife durchlaufen und mit einer Suchvariablen vergleichen. Der Zeitaufwand für die Suche steigt damit linear mit der Anzahl der Elemente.

Nehmen wir an, Sie hätten im Array des Beispiels aus Listing 2 genau 100 Personen gespeichert. Um nun den Vornamen der Person Müller zu erfahren, müssen Sie alle Nachnamen-Elemente des Arrays mit diesem Suchbegriff vergleichen, um schließlich nach dem hundertsten Durchlauf Gleichheit festzustellen. Über den nun ermittelten Index des gefundenen Elements erhalten Sie den Vornamen:

strFind = "Müller"
For i = 0 To UBound(arrPerson)
    If arrPerson(i).Nachname = strFind Then
        Debug.Print arrPerson(i).Vorname
        Exit For
    End If
Next i

Unter Performance-Gesichtspunkten ist also die Verwendung von Arrays kontraindiziert, wenn gezielt Elemente bearbeitet werden sollen.

Collections

Collections brauchen Sie im Gegensatz zu Arrays nicht zu dimensionieren. Sie erleichtern die Suche nach Elementen über ihren Schlüssel (Key). Man spricht deshalb bei Collections und ihren Derivaten auch von assoziativen Arrays. Eine Collection-Variable ist schnell angelegt und gefüllt:

Sub PersonenCollection()
    Dim colPersonen As VBA.Collection
    Set colPersonen = New VBA.Collection
    With colPersonen
    .Add "Minhorst", "Nachname"
    .Add "André", "Vorname"
    .Add CDate("26.05.1929"), "Geburtsdatum"
    Debug.Print "Anzahl Elemente:" & .Count
    Debug.Print !Nachname
    End With
    Debug.Print colPersonen(2)
    Set colPersonen = Nothing
    End Sub

Weitere Bemerkungen zum Collection-Objekt:

  • Eine Collection instanzieren Sie per New-Anweisung. Vermeiden Sie, das bereits bei Deklaration der Variablen zu tun, weil damit immer unklar ist, in welchem Zustand sie sich befindet. Gelöscht wird die Instanz mit Setzen auf Nothing.
  • Elemente fügen Sie mit der Add-Methode hinzu, wobei Sie einen Schlüssel zur späteren Identifizierung hinzugeben können. Ohne den Schlüssel ist der Zugriff dann nur noch über den Index, also die Position des Elements im Array, möglich.
  • Ein Element können Sie auch zwischen bestehenden Elementen einfügen. Sie müssen dazu die optionalen Parameter before und after angeben. Erläuterungen finden Sie in der Visual Basic-Hilfe.
  • Ein Element können Sie über drei Syntaxarten direkt abfragen: Collection("Schlüssel"), Collection!Schlüssel oder Collection(Index)
  • Der Zugriff auf die Elemente über den Ordinalindex beginnt im Unterschied zu Arrays mit dem Index 1 und nicht mit 0. Das erste Element im Beispiel ist colPersonen(1).
  • Ein einmal hinzugefügtes Element können Sie nicht mehr verändern.

Der letzte Punkt ist deutlich auf der Minusseite zu verbuchen. In einem Array können Sie jederzeit ein bestehendes Element mit einem neuen Wert überschreiben, in einer Collection geht das nicht. Der Workaround sieht so aus: Löschen Sie das bestehende Element und fügen Sie es mit gleichem Schlüssel wieder an gleicher Position hinzu:

.Remove "Vorname"
.Add "Krischna", "Vorname", , "Nachname"

Nachname ist hier der Parameter after, also der Schlüssel des Elements, hinter dem der neue Wert platziert werden soll. Das ist etwas umständlich und kostet wieder einiges an Performance, weil das Löschen und Einfügen intern einige Speicherverschiebungen voraussetzt. Bemerkbar dürfte sich das allerdings kaum machen, weil Collections sowohl beim Lesen wie Schreiben ohnehin sehr schnell sind.

Die Tatsache, dass Collections auch Objekte als Elemente aufnehmen können (und somit auch andere Collections), macht sie für Verschachtelungen und Objektmodelle interessant. Eine Eigenschaft wurde noch verschwiegen: Collections sind enumerationsfähig, was bedeutet, dass sie ihre Elemente mit For Each-Schleifen durchlaufen können:

Dim itm As Variant
For Each itm In colPersonen
    Debug.Print itm, TypeName(itm)
Next itm

Das ist zweifellos praktischer als das Hantieren mit UBound und Schleifenzählern, wie es Arrays notwendig machen.

Dictionarys

In Access-Kreisen offensichtlich selten benutzt wird das Dictionary-Objekt. Das ist nicht verwunderlich, weil es nicht aus den unmittelbar in Access eingesetzten Standardbibliotheken kommt, sondern aus der Library Microsoft Scripting Runtime. Mit zusätzlichen Referenzen soll man ja in VBA-Projekten im Interesse von Kompatibilität und Stabilität nicht wuchern, doch dieser Verweis ist ziemlich unverdächtig, findet sich die Datei scrrun.dll schließlich in jedem Windows-System wieder. Früher wurde sie aus Sicherheitsgründen von Administratoren gerne mal deaktiviert, doch heute sollte das passé sein, weil viele Windows-Funktionen auf dieser Bibliothek aufbauen.

Wenn Sie keinen Verweis auf die Bibliothek setzen möchten, dann erhalten Sie das Dictionary-Objekt auch über Late Binding:

Dim objDictionary as Object
Set objDictionary = CreateObject("Scripting.µ
Dictionary")

Wie immer bei Late Binding müssen Sie dann aber kleinere Performance-Einbußen hinnehmen, die dem Marshalling der Objekte geschuldet sind, und können im VBA-Editor nicht die Dienste von IntelliSense in Anspruch nehmen.

Ein Dictionary-Objekt ist eigentlich auch eine Collection, hat jedoch mehr Eigenschaften und Methoden, wodurch es flexibler und einfacher zu bedienen ist.

Der Code zum Instanzieren und Füllen des Dictionary-Objekts ist fast identisch mit dem für Collections. Der folgende Beispielcode verwendet zur Abwechslung mal Late Binding:

Sub PersonenDictionary()
    Dim dictPersonen As Object
    Set dictPersonen = CreateObject("Scripting.µ
    Dictionary")
    With dictPersonen
    !Nachname = "Trowitzsch"
    !Vorname = "Sascha"
    !Geburtsdatum = CDate("11.1.1918")
    Debug.Print "Anzahl Elemente:" & .Count
    Debug.Print !Nachname
    .Add "Nachname", "Minhorst" 'Fehler!
    End With
    Set dictPersonen = Nothing
    End Sub

Es fällt auf, dass für das Hinzufügen von Elementen keine Add-Methode zum Einsatz kommt, sondern lediglich eine direkte Zuweisung des Werts über einen Schlüssel. Das schreit nach Erläuterung, wobei die folgende Übersicht als Erweiterung dessen gilt, was Sie bereits zu Collections lesen konnten:

  • Elemente eines Dictionary-Objekts sind immer mit einem obligatorischen Schlüssel verknüpft. Ein Zugriff über einen Index, wie bei Collections, ist nicht direkt möglich. Der Grund hierfür ist, dass Sie als Schlüssel nicht nur Zeichenketten verwenden können, sondern beliebige Datentypen und sogar Objekte (Ausnahme: Arrays). Infolgedessen weiß ein Dictionary-Objekt bei Angabe einer Ordinalzahl nicht, ob es sich hierbei um einen Index oder einen Key handelt.
  • Neue Elemente können Sie durch einfache Zuweisung eines Schlüssel-Wert-Paares anlegen.
  • Es gibt dennoch eine Methode Add, die immer dann fehlschlägt, wenn ein Schlüssel bereits im Dictionary-Objekt vorhanden ist.
  • Elemente können Sie im Gegensatz zu denen von Collections verändern. Der Wert wird unter Angabe des Schlüssels neu gesetzt, zum Beispiel:

dictPersonen!Nachname = "Minhorst"

  • Aus der Kombination der bisherigen Ausführungen folgt eine Eigentümlichkeit von Dictionarys, der besonderes Augenmerk zu widmen ist. Versuchen Sie, auf ein Element zuzugreifen, das noch nicht existiert, also dessen Schlüssel noch nicht in der Keys-Auflistung zu finden ist, dann legt das Objekt einfach ein neues Element mit diesem Key an und weist ihm den Wert Empty zu. Die folgende Anweisung zum Abfragen des nicht existierenden Elements Adresse gibt demnach weder den Wert Null zurück, noch ereignet sich ein Fehler:

Debug.Print dictPersonen!Adresse

Stattdessen legt die Anweisung ein neues Element mit leerem Inhalt an.

  • Um solche Probleme zu vermeiden, sieht das Dictionary die Funktion Exists vor, mit der Sie das Vorhandensein eines Elements abfragen können:

If dictPersonen.Exists("Adresse") Then _

Debug.Print dictPersonen!Adresse

  • Nicht nur den Wert eines Elements können Sie ändern, sondern auch dessen Schlüssel. Das mag Stirnrunzeln hervorrufen, denn wozu sollte man den Key ändern – schließlich än-dert man die ID eines Datensatzes auch nicht, nachdem er einmal angelegt wurde. Sinn bekommt das Ganze aber etwa in Anbetracht der Tatsache, dass man die Elemente eines Dictionarys nicht wie mit einer Abfrage sortieren kann, ohne sie zu vertauschen. Sieht man für ein Dictionary numerische Schlüssel vor, dann kann man nach Sortierung nach Wert der Elemente einfach die Schlüssel in aufsteigender Reihenfolge neu vergeben und hat nun in Zukunft eine sortierte Liste vor sich.

Den Schlüssel eines Elements ändern Sie so:

arrPersonen.Key("Nachname") = "Vorname"

Als Schlüssel für Dictionary-Elemente können Sie auch Objekte angeben. Betrachten Sie folgendes Beispiel:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar