Excel VBA-Sammlungen

In diesem Tutorial wird gezeigt, wie man Sammlungen in VBA verwendet.

Was ist eine Sammlung?

Eine Sammlung ist ein Objekt, das eine Reihe ähnlicher Elemente enthält. Auf diese kann leicht zugegriffen und sie können bearbeitet werden, selbst wenn die Sammlung eine große Anzahl von Elementen enthält.

In Excel-VBA gibt es bereits integrierte Sammlungen. Ein Beispiel ist die Sheets-Sammlung. Für jedes Arbeitsblatt in einer Arbeitsmappe gibt es ein Element in der Sheets-Sammlung.

Die eingebauten Sammlungen verfügen über weit mehr Eigenschaften und Methoden, die jedoch in Ihren eigenen von Ihnen erstellten Sammlungen nicht verfügbar sind.

Sie können die Sammlung zum Beispiel verwenden, um Informationen über ein bestimmtes Arbeitsblatt zu erhalten. Sie können zum Beispiel den Namen des Arbeitsblatts sehen und auch, ob es sichtbar ist oder nicht. Mit Hilfe einer For-Each-Schleife können Sie jedes Arbeitsblatt in der Sammlung durchlaufen.

Sub TestArbeitsBlaetter()
Dim Blt As Worksheet
For Each Blt In Sheets
    MsgBox Blt.Name
    MsgBox Blt.Visible
Next Blt
End Sub

Sie können auch ein bestimmtes Arbeitsblatt in der Sammlung über den Indexwert oder den tatsächlichen Namen des Arbeitsblatts ansprechen:

MsgBox Sheets(1).Name
MsgBox Worksheets("Blatt1").Name

Wenn Arbeitsblätter hinzugefügt oder gelöscht werden, wächst oder schrumpft die Sheets-Sammlung.

Beachten Sie, dass die Indexnummer bei VBA-Sammlungen mit 1 und nicht mit 0 beginnt

Sammlungen vs. Arrays

Arrays und Sammlungen ähneln sich in ihren Funktionen, da beide Methoden es ermöglichen, eine große Menge an Daten zu speichern, auf die dann mit Hilfe von Code leicht verwiesen werden kann. Sie haben jedoch eine Reihe von Unterschieden in der Art und Weise, wie sie funktionieren:

1. Arrays sind mehrdimensional, während Sammlungen nur eindimensional sind. Sie können ein Array mit mehreren Dimensionen gestalten, z. B.

Dim MeinArray(10, 2) As String

Damit wird ein Array mit 10 Zeilen und 2 Spalten, fast wie ein Arbeitsblatt, erstellt. Eine Auflistung ist tatsächlich eine einzelne Spalte. Das Array ist nützlich, wenn Sie eine Reihe von Daten speichern müssen, die sich aufeinander beziehen, z. B. Name und Adresse. Der Name würde in der ersten Dimension des Arrays stehen und die Adresse in der zweiten.

2. Wenn Sie Ihr Array auffüllen, benötigen Sie eine eigene Codezeile, um jedem Element des Arrays einen Wert zuzuweisen. Bei einem zweidimensionalen Array würden Sie zwei Codezeilen benötigen – eine Zeile für die erste Spalte und eine für die zweite Spalte. Mit dem Collection-Objekt verwenden Sie einfach die Add-Methode, so dass das neue Element einfach in die Sammlung eingefügt wird und der Indexwert automatisch angepasst wird.
3. Wenn Sie ein Datenelement löschen müssen, ist dies im Array komplizierter. Sie können die Werte eines Elements auf einen leeren Wert setzen, aber das Element selbst existiert weiterhin im Array. Wenn Sie eine For-Next-Schleife verwenden, um durch das Array zu iterieren, gibt die Schleife einen leeren Wert zurück, die Tatsache die im Code behandelt werden muss, um sicherzustellen, dass der leere Wert ignoriert wird. In einer Sammlung verwenden Sie die Methoden Add oder Remove und die gesamte Indizierung und Größenanpassung wird automatisch vorgenommen. Das entfernte Element verschwindet vollständig. Arrays sind nützlich für eine feste Datenmenge, aber Sammlungen sind besser geeignet, wenn sich die Datenmenge ändern kann.
4. Sammlungen sind schreibgeschützt, während Array-Werte mit VBA geändert werden können. Bei einer Sammlung müssen Sie zuerst den zu ändernden Wert entfernen und dann den neuen Wert hinzufügen.
5. In einem Array können Sie nur einen einzigen Datentypen für die Elemente verwenden, der bei der Dimensionierung des Arrays festgelegt wird. Allerdings können Sie in einem Array auch benutzerdefinierte Datentypen verwenden, die Sie selbst entworfen haben. Sie könnten eine sehr komplizierte Array-Struktur haben, die einen benutzerdefinierten Datentypen verwendet, der wiederum mehrere benutzerdefinierte Datentypen unter sich hat. In einer Sammlung können Sie Datentypen für jedes Element verwenden. Sie können einen numerischen Wert, ein Datum oder eine Zeichenkette verwenden. Das Sammlungsobjekt kann jeden Datentypen annehmen. Wenn Sie versuchen würden, einen String-Wert in ein Array einzufügen, das als numerisch dimensioniert ist, würde eine Fehlermeldung erscheinen.
6. Sammlungen sind im Allgemeinen einfacher zu verwenden als Arrays. Wenn Sie ein Sammelobjekt erstellen, verfügt es nur über zwei Methoden (Add und Remove) und zwei Eigenschaften (Count und Item), so dass die Programmierung des Objekts keineswegs kompliziert ist.
7. Sammlungen können Schlüssel zum Auffinden von Daten verwenden. Arrays haben diese Funktion nicht und erfordern einen Schleifencode, um durch das Array zu iterieren und bestimmte Werte zu finden.
8. Die Größe eines Arrays muss bei seiner ersten Erstellung festgelegt werden. Sie müssen eine Vorstellung davon haben, wie viele Daten es speichern soll. Wenn Sie die Größe des Arrays erhöhen müssen, können Sie es mit ‚ReDim‚ verkleinern, aber Sie müssen das Schlüsselwort ‚Preserve‚ verwenden, wenn Sie die bereits im Array enthaltenen Daten nicht verlieren wollen. Die Größe einer Sammlung muss nicht definiert werden. Sie wächst und schrumpft einfach automatisch, wenn Elemente hinzugefügt oder entfernt werden.

Umfang eines Sammelobjekts

Was den Umfang betrifft, ist das Sammelobjekt nur verfügbar, solange die Arbeitsmappe geöffnet ist. Es wird nicht gespeichert, wenn die Arbeitsmappe gespeichert wird. Wenn die Arbeitsmappe erneut geöffnet wird, muss die Sammlung mit VBA-Code neu erstellt werden.

Wenn Sie möchten, dass Ihre Sammlung für den gesamten Code in Ihrem Codemodul verfügbar ist, müssen Sie das Sammelobjekt im Abschnitt Declare oben im Modulfenster deklarieren.

Dadurch wird sichergestellt, dass Ihr gesamter Code innerhalb dieses Moduls auf die Sammlung zugreifen kann. Wenn Sie möchten, dass jedes Modul innerhalb Ihrer Arbeitsmappe auf die Sammlung zugreifen kann, dann definieren Sie sie als globales Objekt.

Global MeineSammlung As New Collection

Erstellen einer Sammlung, Hinzufügen von Elementen und Zugriff auf Elemente

Ein einfaches Sammelobjekt kann in VBA mit folgendem Code erstellt werden:

Sub SammlungErstellen()
Dim MeineSammlung As New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2"
MeineSammlung.Add "Element3"
End Sub

Der Code dimensioniert ein neues Objekt mit dem Namen „MeineSammlung“ und die folgenden Codezeilen verwenden die Add-Methode, um 3 neue Werte hinzuzufügen.

Anschließend können Sie den Code verwenden, um Ihre Sammlung zu durchlaufen und auf die Werte zuzugreifen

For Each Item In MeineSammlung
    MsgBox Item
Next Item

Sie können Ihre Sammlung auch mit einer For-Next-Schleife durchlaufen:

For n = 1 To MeineSammlung.Count
    MsgBox MeineSammlung(n)
Next n

Der Code ermittelt die Größe der Sammlung mithilfe der Eigenschaft Count und verwendet dann diesen Wert ab 1, um jedes Element zu indizieren.

Die For-Each-Schleife ist schneller als die For-Next-Schleife, aber sie funktioniert nur in einer Richtung (unten nach oben). Die For-Next-Schleife hat den Vorteil, dass Sie eine andere Richtung verwenden können (oben nach unten) und Sie können auch die Step-Methode verwenden, um das Inkrement zu ändern. Dies ist nützlich, wenn Sie mehrere Elemente löschen möchten, da Sie die Löschung vom Ende der Sammlung bis zum Anfang durchführen müssen, da sich der Index beim Löschen ändert.

Die Methode Add in einer Sammlung hat 3 optionale Parameter: Key, Before und After.

Mit den Parametern „Before“ und „After“ können Sie die Position des neuen Elements im Verhältnis zu den anderen Elementen in der Sammlung festlegen.

Dies geschieht durch die Angabe der Indexnummer, zu der das neue Element in Beziehung stehen soll.

Sub SammlungErstellen()
Dim MeineSammlung As New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2", , 1
MeineSammlung.Add "Element3"
End Sub

In diesem Beispiel wurde festgelegt, dass „Item2“ vor dem ersten indizierten Element in der Sammlung (d. h. „Item1“) hinzugefügt wird. Wenn Sie diese Sammlung durchlaufen, erscheint ‚Item2‘ als erstes, gefolgt von ‚Item1‘ und ‚Item3‘.

Wenn Sie einen ‚Before‘– oder ‚After‘-Parameter angeben, wird der Indexwert innerhalb der Sammlung automatisch angepasst, so dass ‚Item2‘ den Indexwert 1 erhält und ‚Item1‘ auf den Indexwert 2 verschoben wird.

Sie können auch den Parameter ‚Key‘ verwenden, um einen Referenzwert hinzuzufügen, den Sie zur Identifizierung des Elements der Sammlung verwenden können. Beachten Sie, dass ein Schlüsselwert ein String sein muss und innerhalb der Sammlung eindeutig sein muss.

Sub SammlungErstellen()
Dim MeineSammlung As New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2", "MeinSchlüssel"
MeineSammlung.Add "Element3"
MsgBox MeineSammlung("MeinSchlüssel")
End Sub

Item2″ hat den ‚Key‘-Wert „MeinSchlüssel“ erhalten, so dass Sie auf dieses Element über den Wert von „MeinSchlüssel“ anstelle der Indexnummer (2) verweisen können.

Beachten Sie, dass der ‚Key‘-Wert ein String-Wert sein muss. Er kann kein anderer Datentyp sein. Beachten Sie, dass die Sammlung schreibgeschützt ist und Sie den Schlüsselwert nicht aktualisieren können, sobald er festgelegt wurde. Außerdem können Sie nicht überprüfen, ob ein Schlüsselwert für ein bestimmtes Element in der Sammlung vorhanden ist, oder den Schlüsselwert anzeigen, was ein gewisser Nachteil ist.

Der Parameter ‚Key‘ hat den zusätzlichen Vorteil, dass Ihr Code besser lesbar ist, insbesondere wenn er einem Kollegen zur Unterstützung übergeben wird, und Sie müssen nicht die gesamte Sammlung durchlaufen, um den Wert zu finden. Stellen Sie sich vor, Sie hätten eine Sammlung mit 10.000 Einträgen, wie schwierig es wäre, einen bestimmten Eintrag zu finden!

Entfernen eines Objekts aus einer Sammlung

Sie können die Methode ‚Remove‘ verwenden, um Elemente aus Ihrer Sammlung zu löschen.

MeineSammlung.Remove (2)

Leider ist es bei einer großen Anzahl von Einträgen in der Sammlung nicht einfach, den Index des zu löschenden Eintrags herauszufinden. In diesem Fall ist der Parameter ‚Key‘ bei der Erstellung der Sammlung sehr nützlich

MeineSammlung.Remove ("MeinSchlüssel")

Wenn ein Element aus einer Sammlung entfernt wird, werden die Indexwerte in der gesamten Sammlung automatisch zurückgesetzt. Aus diesem Grund ist der Parameter ‚Key‘ so nützlich, wenn Sie mehrere Elemente auf einmal löschen. Wenn Sie z. B. das Element mit dem Index 105 löschen, wird das Element mit dem Index 106 zum Index 105, und der Indexwert aller Elemente oberhalb dieses Elements wird nach unten verschoben. Wenn Sie den Parameter ‚Key‘ verwenden, müssen Sie sich nicht darum kümmern, welcher Indexwert entfernt werden muss.

Um alle Elemente der Sammlung zu löschen und eine neue Sammlung zu erstellen, verwenden Sie wieder die Dim-Anweisung, mit der eine leere Sammlung erstellt wird.

Dim MeineSammlung As New Collection

Um das eigentliche Sammlungsobjekt vollständig zu entfernen, können Sie das Objekt auf Nothing setzen

Set MeineSammlung = Nothing

Dies ist nützlich, wenn die Sammlung in Ihrem Code nicht mehr benötigt wird. Wenn Sie das Sammlungsobjekt auf Nothing setzen, werden alle Verweise auf das Objekt entfernt und der von ihm verwendete Speicher wird freigegeben. Dies kann erhebliche Auswirkungen auf die Ausführungsgeschwindigkeit Ihres Codes haben, wenn ein großes Objekt im Speicher liegt, das nicht mehr benötigt wird.

Zählen der Anzahl von Elementen in einer Sammlung

Sie können die Anzahl der Objekte in Ihrer Sammlung leicht herausfinden, indem Sie die Eigenschaft ‚Count‘ verwenden

MsgBox MeineSammlung.Count

Sie würden diese Eigenschaft verwenden, wenn Sie eine For-Next-Schleife verwenden, um durch die Sammlung zu iterieren, da sie Ihnen die Obergrenze für die Indexnummer angibt.

Testen einer Collection auf einen bestimmten Wert

Mit einer For-Each-Schleife können Sie eine Sammlung durchlaufen, um nach einem bestimmten Wert für ein Element zu suchen.

Sub SammlungDurchsuchen()
Dim MeineSammlung as New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2"
MeineSammlung.Add "Element3"

For Each Item In MeineSammlung
        If Item = "Element2" Then
            MsgBox Item & " Gefunden"
        End If
Next
End Sub

Der Code erstellt eine kleine Sammlung und durchläuft sie dann auf der Suche nach einem Element namens „item2“. Wenn es gefunden wird, wird eine Meldung angezeigt, dass das spezifische Element gefunden wurde.

Einer der Nachteile dieser Methode ist die Tatsache, dass Sie nicht auf den Index- oder den Schlüsselwert zugreifen können.

Wenn Sie stattdessen eine For-Next-Schleife verwenden, können Sie den For-Next-Zähler verwenden, um den Indexwert zu erhalten, obwohl Sie immer noch nicht auf den Schlüsselwert zugreifen können

Sub SammlungDurchsuchen()
Dim MeineSammlung As New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2"
MeineSammlung.Add "Element3"

For n = 1 To MeineSammlung.Count
        If MeineSammlung.Item(n) = "Element2" Then
            MsgBox MeineSammlung.Item(n) & " gefunden an Indexposition " & n
        End If
Next n
End Sub

Der For-Next-Zähler (n) liefert die Indexposition.

Sortieren einer Sammlung

Es gibt keine eingebaute Funktion zum Sortieren einer Sammlung, aber mit ein wenig Querdenken kann ein Code geschrieben werden, um eine Sortierung durchzuführen, indem die Arbeitsblattsortierfunktion von Excel verwendet wird. Dieser Code verwendet ein leeres Arbeitsblatt namens ‚SortierBlatt‘, um die eigentliche Sortierung durchzuführen.

Sub SammlungSortieren()
Dim MeineSammlung As New Collection
Dim Zaehler As Long

'Sammlung mit Elementen in zufälliger Reihenfolge erstellen
MeineSammlung.Add "Element5"
MeineSammlung.Add "Element2"
MeineSammlung.Add "Element4"
MeineSammlung.Add "Element1"
MeineSammlung.Add "Item3"
'Erfassen der Anzahl der Elemente in der Sammlung zur späteren Verwendung
Zaehler = MeineSammlung.Count

'Durch die Sammlung Iterieren und jedes Element in eine aufeinanderfolgende Zelle auf dem 'SortierBlatt' (Spalte A) kopieren
For n = 1 To MeineSammlung.Count
    Sheets("SortierBlatt").Cells(n, 1) = MeineSammlung(n)

Next n
'Das Sortierblatt aktivieren und die Excel-Sortierroutine verwenden, um die Daten in aufsteigender Reihenfolge zu sortieren
Sheets("SortierBlatt").Activate
Range("A1:A" & MeineSammlung.Count).Select
    ActiveWorkbook.Worksheets("SortierBlatt").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SortierBlatt"). Sort.SortFields.Add Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("SortierBlatt").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Alle Elemente in der Sammlung löschen - beachten Sie, dass diese For-Next-Schleife in umgekehrter Reihenfolge abläuft
For n = MeineSammlung.Count To 1 Step -1
     MeineSammlung.Remove (n)

Next n
'Die Zellenwerte in das leere Sammelobjekt zurückkopieren, indem Sie den gespeicherten Wert (Zaehler) für die Schleife verwenden
For n = 1 To Zaehler
    MeineSammlung.Add Sheets("SortierBlatt").Cells(n, 1).Value

Next n

'Durch die Sammlung iterieren, um die Reihenfolge der Elemente zu ermitteln
For Each Item In MeineSammlung
    MsgBox Item

Next Item
'Das Arbeitsblatt (SortierBlatt) leeren - ggf. auch löschen
Sheets("SortierBlatt").Range(Cells(1, 1), Cells(Zaehler, 1)).Clear
End Sub

In diesem Code wird zunächst eine Sammlung erstellt, der die Elemente in zufälliger Reihenfolge hinzugefügt werden. Anschließend werden sie in die erste Spalte eines Arbeitsblatts (SortierBlatt) kopiert.

Der Code verwendet dann die Excel-Sortierfunktion, um die Daten in der Spalte in aufsteigender Reihenfolge zu sortieren. Der Code könnte auch geändert werden, um in absteigender Reihenfolge zu sortieren.

Die Sammlung wird dann mit einer For-Next-Schleife geleert. Beachten Sie, dass die Step-Option so verwendet wird, dass vom Ende der Auflistung zum Anfang geleert wird. Das liegt daran, dass beim Löschen die Indexwerte zurückgesetzt werden. Würde man vom Anfang aus löschen, würde dies nicht korrekt geschehen (Index 2 würde zu Index 1 werden).

Schließlich werden die Werte der Elemente mit einer weiteren For-Next-Schleife zurück in die leere Sammlung übertragen.

Eine weitere For-Each-Schleife beweist, dass die Sammlung nun in guter aufsteigender Reihenfolge ist.

Leider werden dabei die eventuell ursprünglich eingegebenen Schlüsselwerte nicht berücksichtigt, da diese nicht gelesen werden können.

Übergabe einer Sammlung an eine Sub/Funktion

Eine Sammlung kann wie jeder andere Parameter an eine Sub oder eine Funktion übergeben werden

Function MeineFunktion(ByRef MeineSammlung As Collection)

Es ist wichtig, die Sammlung mit ‚ByRef‘ zu übergeben. Dies bedeutet, dass die ursprüngliche Sammlung verwendet wird. Wenn die Sammlung mit ‚ByVal‘ übergeben wird, wird eine Kopie der Sammlung erstellt, was unangenehme Folgen haben kann.

Wenn eine Kopie mit ‚ByVal‘ erstellt wird, wirkt sich alles, was die Sammlung innerhalb der Funktion ändert, nur auf die Kopie und nicht auf das Original aus. Wenn zum Beispiel innerhalb der Funktion ein neues Element zur Sammlung hinzugefügt wird, erscheint dieses nicht in der ursprünglichen Sammlung, was zu einem Fehler in Ihrem Code führt.

Rückgabe einer Sammlung aus einer Funktion

Die Rückgabe einer Sammlung aus einer Funktion erfolgt auf die gleiche Weise wie die Rückgabe eines beliebigen Objekts. Sie müssen das Schlüsselwort Set verwenden

Sub RueckgabeAusFunktion()
Dim MeineSammlung As Collection
Set MeineSammlung = SammlungAuffuellen
MsgBox MeineSammlung.Count
End Sub

In diesem Code wird eine Sub-Routine erstellt, die ein Objekt mit dem Namen „MeineSammlung“ erzeugt und dann das Schlüsselwort „Set“ verwendet, um die Funktion zum Auffüllen dieser Sammlung aufzurufen. Sobald dies geschehen ist, wird ein Meldungsfeld angezeigt, das die Anzahl von 2 Elementen angibt

Function SammlungAuffuellen() As Collection
Dim MeineSammlung As New Collection
MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2"

Set SammlungAuffuellen = MeineSammlung
End Function

Die Funktion SammlungAuffuellen erstellt ein neues Sammlungsobjekt und füllt es mit 2 Elementen. Anschließend übergibt sie dieses Objekt an das in der ursprünglichen Sub-Routine erstellte Sammlungsobjekt zurück.

Konvertieren einer Sammlung in ein Array

Vielleicht möchten Sie Ihre Sammlung in ein Array umwandeln. Möglicherweise möchten Sie die Daten dort speichern, wo sie geändert und bearbeitet werden können. Dieser Code erstellt eine kleine Sammlung und überträgt sie dann in ein Array.

Beachten Sie, dass der Index der Sammlung bei 1 beginnt, während der Index des Arrays bei 0 beginnt. Während die Sammlung 3 Elemente hat, muss das Array nur auf 2 dimensioniert werden, da es ein Element 0 gibt.

Sub SammlungInArrayUmwandeln()
Dim MeineSammlung As New Collection
Dim MeinArray(2) As String

MeineSammlung.Add "Element1"
MeineSammlung.Add "Element2"
MeineSammlung.Add "Element3"

For n = 1 To MeineSammlung.Count
    MeinArray(n - 1) = MeineSammlung(n)
 
Next n

For n = 0 To 2
    MsgBox MeinArray(n)
Next n
End Sub

Konvertieren eines Arrays in eine Sammlung

Vielleicht möchten Sie ein Array in eine Sammlung umwandeln. Sie möchten zum Beispiel schneller und eleganter als beim Abrufen eines Array-Elements mit dem Code auf die Daten zugreifen.

Beachten Sie, dass dies nur für eine einzige Dimension des Arrays funktioniert, da die Sammlung nur eine Dimension hat.

Sub ArrayInSammlungUmwandeln()
Dim MeineSammlung As New Collection
Dim MeinArray(2) As String
MeinArray(0) = "Element1"
MeinArray(1) = "Element2"
MeinArray(2) = "Element3"

For n = 0 To 2
    MeineSammlung.Add MeinArray(n)

Next n
For Each Item In MeineSammlung
    MsgBox Item
Next Item
End Sub

Wenn Sie ein mehrdimensionales Array verwenden möchten, können Sie die Array-Werte für jede Zeile innerhalb des Arrays mit einem Trennzeichen zwischen den Array-Dimensionen verketten, so dass Sie beim Lesen des Sammlungswerts das Trennzeichen programmatisch verwenden können, um die Werte zu trennen.

Sie könnten die Daten auch so in die Sammlung verschieben, dass der Wert der ersten Dimension hinzugefügt wird (Index 1), dann der Wert der nächsten Dimension (Index 2) und so weiter.

Hätte das Array z. B. 4 Dimensionen, wäre jeder vierte Wert in der Sammlung ein neuer Satz von Werten.

Sie könnten auch Array-Werte als Schlüssel hinzufügen (vorausgesetzt, sie sind eindeutig), was das Auffinden bestimmter Daten erleichtern würde.