Eine Sammlung ist ein Objekt, das eine Reihe ähnlicher Elemente enthält. Diese sind leicht zugänglich und manipulierbar, auch wenn sich eine große Anzahl von Objekten in der Sammlung befindet.
Es gibt bereits integrierte Sammlungen mit Excel VBA. Ein Beispiel ist die Sheets-Auflistung. Für jedes Arbeitsblatt in einer Arbeitsmappe gibt es ein Element in der Sheets-Auflistung.
Für die integrierten Sammlungen stehen Ihnen weit mehr Eigenschaften und Methoden zur Verfügung, diese sind jedoch in Ihren eigenen Sammlungen, die Sie erstellen, nicht verfügbar.
Sie können die Sammlung beispielsweise verwenden, um Informationen zu einem bestimmten Arbeitsblatt abzurufen. Sie können beispielsweise den Namen des Arbeitsblatts sehen und auch, ob es sichtbar ist oder nicht. Mit einer For Each-Schleife können Sie jedes Arbeitsblatt in der Auflistung durchlaufen.
| 1234567 | Unter TestArbeitsblätter()Dim Sh As ArbeitsblattFür jeden Sh in den BlätternMsgBox Sh.NameMsgBox Sh.VisibleNächster ShEnd Sub |
Sie können ein bestimmtes Arbeitsblatt in der Sammlung auch über den Indexwert oder den tatsächlichen Namen des Arbeitsblatts ansprechen:
| 12 | MsgBox-Blätter(1).NameMsgBox Sheets("Sheet1").Name |
Wenn Arbeitsblätter hinzugefügt oder gelöscht werden, wächst oder verkleinert sich die Sheets-Sammlung.
Beachten Sie, dass bei VBA-Sammlungen die Indexnummer mit 1 beginnt und nicht mit 0
Sammlungen im Vergleich zu Arrays
Arrays und Sammlungen ähneln sich in ihren Funktionen, da es sich bei beiden um Methoden handelt, die das Speichern großer Datenmengen ermöglichen, auf die dann mithilfe von Code leicht verwiesen werden kann. Sie haben jedoch eine Reihe von Unterschieden in der Art und Weise, wie sie funktionieren:
- Arrays sind mehrdimensional, während Sammlungen nur eindimensional sind. Sie können ein Array mit mehreren Dimensionen dimensionieren, z.B.
| 1 | Dim MyArray(10, 2) als String |
Dadurch entsteht ein Array von 10 Zeilen mit 2 Spalten, fast wie ein Arbeitsblatt. Eine Sammlung ist effektiv 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. Name wäre in der ersten Dimension des Arrays und Adresse in der zweiten Dimension.
- Wenn Sie Ihr Array auffüllen, benötigen Sie eine separate Codezeile, um in jedes Element des Arrays einen Wert einzufügen. Wenn Sie ein zweidimensionales Array hätten, würden Sie eigentlich 2 Zeilen Code benötigen - eine Zeile für die erste Spalte und eine Zeile für die zweite Spalte. Beim Collection-Objekt verwenden Sie einfach die Add-Methode, sodass das neue Element einfach in die Collection aufgenommen und der Indexwert automatisch angepasst wird.
- Wenn Sie ein Datenelement löschen müssen, ist es im Array komplizierter. Sie können die Werte eines Elements auf einen leeren Wert setzen, aber das Element selbst existiert noch innerhalb des Arrays. Wenn Sie eine For Next-Schleife verwenden, um das Array zu durchlaufen, gibt die Schleife einen leeren Wert zurück, der codiert 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ößenänderung wird automatisch durchgeführt. Das entfernte Element verschwindet vollständig. Arrays sind für eine feste Datengröße nützlich, Sammlungen sind jedoch besser für Bereiche geeignet, in denen sich die Datenmenge ändern kann.
- Sammlungen sind schreibgeschützt, während Arraywerte mit VBA geändert werden können. Bei einer Sammlung müssten Sie zuerst den zu ändernden Wert entfernen und dann den neuen geänderten Wert hinzufügen.
- In einem Array können Sie nur einen einzigen Datentyp für die Elemente verwenden, der bei der Dimensionierung des Arrays gesetzt wird. Im Array können Sie jedoch benutzerdefinierte Datentypen verwenden, die Sie selbst entworfen haben. Sie könnten eine sehr komplizierte Array-Struktur haben, die einen benutzerdefinierten Datentyp verwendet, der wiederum mehrere benutzerdefinierte Datentypen darunter hat. In einer Sammlung können Sie für jedes Element Verwendungsdatentypen von Daten hinzufügen. Sie können einen numerischen Wert, ein Datum oder eine Zeichenfolge haben - das Sammlungsobjekt nimmt jeden Datentyp an. Wenn Sie versuchten, einen Zeichenfolgenwert in ein als numerisch dimensioniertes Array einzufügen, wurde eine Fehlermeldung ausgegeben.
- Sammlungen sind im Allgemeinen einfacher zu verwenden als Arrays. Codiert, wenn Sie ein Sammlungsobjekt erstellen, hat es nur zwei Methoden (Add und Remove) und zwei Eigenschaften (Count und Item), so dass das Objekt keineswegs kompliziert zu programmieren ist.
- Sammlungen können Schlüssel verwenden, um Daten zu finden. Arrays verfügen nicht über diese Funktion und erfordern Schleifencode, um das Array zu durchlaufen, um bestimmte Werte zu finden.
- Die Größe eines Arrays muss beim ersten Erstellen definiert werden. Sie müssen eine Vorstellung davon haben, wie viele Daten gespeichert werden. Wenn Sie die Größe des Arrays erhöhen müssen, können Sie es mit „ReDim“ ändern, aber Sie müssen das Schlüsselwort „Preserve“ verwenden, wenn Sie die bereits im Array enthaltenen Daten nicht verlieren möchten. Eine Sammlungsgröße muss nicht definiert werden. Es wächst und verkleinert sich einfach automatisch, wenn Elemente hinzugefügt oder entfernt werden.
Umfang eines Sammlungsobjekts
Vom Umfang her ist das Sammlungsobjekt nur verfügbar, während 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 Sammlungsobjekt im Abschnitt Deklarieren oben im Modulfenster deklarieren
Dadurch wird sichergestellt, dass Ihr gesamter Code in diesem Modul auf die Sammlung zugreifen kann. Wenn Sie möchten, dass ein Modul in Ihrer Arbeitsmappe auf die Sammlung zugreift, definieren Sie es als globales Objekt
| 1 | Global MyCollection als neue Kollektion |
Erstellen einer Sammlung, Hinzufügen von Elementen und Zugreifen auf Elemente
Ein einfaches Sammlungsobjekt kann in VBA mit dem folgenden Code erstellt werden:
| 123456 | Unter CreateCollection()Dim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
Der Code dimensioniert ein neues Objekt namens "MyCollection", und dann verwenden die folgenden Codezeilen die Add-Methode, um 3 neue Werte hinzuzufügen.
Sie können dann Code verwenden, um durch Ihre Sammlung zu iterieren, um auf die Werte zuzugreifen
| 123 | Für jeden Artikel in MyCollectionMsgBox-ElementNächstes Objekt |
Sie können Ihre Sammlung auch mit einer For Next-Schleife durchlaufen:
| 123 | Für n = 1 Zu MyCollection.CountMsgBox MyCollection(n)Nächste Nr |
Der Code ruft die Größe der Sammlung mithilfe der Count-Eigenschaft ab und verwendet diese dann ab dem Wert 1, um jedes Element zu indizieren
Die For Each-Schleife ist schneller als die For Next-Schleife, aber sie funktioniert nur in eine Richtung (niedriger Index zu hoch). Die For Next-Schleife hat den Vorteil, dass Sie eine andere Richtung verwenden können (High Index zu Low) und Sie können auch die Step-Methode verwenden, um die Schrittweite zu ändern. Dies ist nützlich, wenn Sie mehrere Elemente löschen möchten, da Sie den Löschvorgang vom Ende der Sammlung bis zum Anfang ausführen müssen, da sich der Index während des Löschvorgangs ändert.
Die Add-Methode in einer Sammlung hat 3 optionale Parameter - Key, Before und After
Mit den Parametern „Vorher“ und „Nachher“ können Sie die Position Ihres neuen Artikels relativ zu den anderen bereits in der Sammlung befindlichen Artikeln festlegen
Dazu geben Sie die Indexnummer an, zu der Ihr neues Element relativ sein soll.
| 123456 | Unter CreateCollection()Dim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2", , 1MyCollection.Add "Item3"End Sub |
In diesem Beispiel wurde "Item2" so angegeben, dass es vor dem ersten indizierten Element in der Sammlung (das "Item1" ist) hinzugefügt wird. Wenn Sie diese Sammlung durchlaufen, wird zuerst „Item2“ angezeigt, gefolgt von „Item1“ und „Item3“.
Wenn Sie einen „Before“- oder „After“-Parameter angeben, wird der Indexwert innerhalb der Sammlung automatisch angepasst, sodass „Item2“ zu einem Indexwert von 1 wird und „Item1“ zu einem Indexwert von 2 verschoben wird
Sie können auch den Parameter „Key“ verwenden, um einen Referenzwert hinzuzufügen, mit dem Sie das Sammlungselement identifizieren können. Beachten Sie, dass ein Schlüsselwert eine Zeichenfolge sein muss und innerhalb der Sammlung eindeutig sein muss.
| 1234567 | Unter CreateCollection()Dim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection("MyKey")End Sub |
‘Item2’ hat den ‘Key’-Wert ‘MyKey’ erhalten, damit Sie auf diesen Artikel verweisen können, indem Sie den Wert ‘MyKey’ anstelle der Indexnummer verwenden (2)
Beachten Sie, dass der Wert „Schlüssel“ ein Zeichenfolgenwert sein muss. Es kann kein anderer Datentyp sein. Beachten Sie, dass die Sammlung schreibgeschützt ist und Sie den Schlüsselwert nicht aktualisieren können, nachdem 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 kleiner Nachteil ist.
Der Parameter „Key“ hat den zusätzlichen Vorteil, dass Ihr Code lesbarer wird, insbesondere wenn er an einen Kollegen zum Support übergeben wird, und Sie müssen nicht die gesamte Sammlung durchlaufen, um diesen Wert zu finden. Stellen Sie sich vor, Sie hätten eine Sammlung von 10.000 Artikeln, wie schwierig es wäre, auf einen bestimmten Artikel zu verweisen!
Entfernen eines Elements aus einer Sammlung
Sie können die Methode "Entfernen" verwenden, um Elemente aus Ihrer Sammlung zu löschen.
| 1 | Meine Sammlung.Entfernen (2) |
Leider ist es nicht einfach, wenn die Sammlung eine große Anzahl von Elementen enthält, den Index des Elements zu ermitteln, das Sie löschen möchten. Hier ist der Parameter ‚Key‘ praktisch, wenn die Sammlung erstellt wird
| 1 | MyCollection.Remove („MyKey“) |
Wenn ein Element aus einer Sammlung entfernt wird, werden die Indexwerte automatisch während der gesamten Sammlung zurückgesetzt. Hier ist der Parameter „Key“ sehr nützlich, wenn Sie mehrere Elemente gleichzeitig löschen. Zum Beispiel könnten Sie den Artikelindex 105 löschen, und sofort wird der Artikelindex 106 zu Index 105, und alles über diesem Artikel hat seinen Indexwert nach unten verschoben. Wenn Sie den Parameter Key verwenden, müssen Sie sich keine Gedanken darüber machen, welcher Indexwert entfernt werden muss.
Um alle Sammlungselemente zu löschen und eine neue Sammlung zu erstellen, verwenden Sie erneut die Dim-Anweisung, die eine leere Sammlung erstellt.
| 1 | Dim MyCollection als neue Kollektion |
Um das eigentliche Sammlungsobjekt vollständig zu entfernen, können Sie das Objekt auf nichts setzen
| 1 | Setze MyCollection = Nichts |
Dies ist nützlich, wenn die Sammlung von Ihrem Code nicht mehr benötigt wird. Wenn Sie das Auflistungsobjekt auf nichts setzen, werden alle Verweise darauf entfernt und der verwendete Speicher freigegeben. Dies kann wichtige Auswirkungen auf die Ausführungsgeschwindigkeit Ihres Codes haben, wenn sich ein großes Objekt im Speicher befindet, das nicht mehr benötigt wird.
Zählen Sie die Anzahl der Elemente in einer Sammlung
Sie können die Anzahl der Artikel in Ihrer Sammlung leicht herausfinden, indem Sie die Eigenschaft „Zählen“ verwenden
| 1 | MsgBox MyCollection.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 liefert.
Testsammlung für einen bestimmten Wert
Sie können eine Sammlung durchlaufen, um mithilfe einer For Each-Schleife nach einem bestimmten Wert für ein Element zu suchen
| 123456789101112 | Unter SearchCollection()Dim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2"MyCollection.Add "Item3"Für jeden Artikel in MyCollectionWenn Item = "Item2" ThenMsgBox-Element & "Gefunden"Ende WennNächsteEnd Sub |
Der Code erstellt eine kleine Sammlung und durchläuft sie dann auf der Suche nach einem Element namens "item2". Wenn es gefunden wird, zeigt es ein Meldungsfeld an, dass das bestimmte Element gefunden wurde
Einer der Nachteile dieser Methode besteht darin, dass Sie nicht auf den Indexwert 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 abzurufen, obwohl Sie den „Key“-Wert immer noch nicht abrufen können
| 123456789101112 | Unter SearchCollection()Dim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2"MyCollection.Add "Item3"Für n = 1 Zu MyCollection.CountWenn MyCollection.Item(n) = "Item2" ThenMsgBox MyCollection.Item(n) & " an Indexposition gefunden " & nEnde WennNächste NrEnd Sub |
Der For Next-Zähler (n) liefert die Indexposition
Sortieren einer Sammlung
Es gibt keine integrierte Funktionalität zum Sortieren einer Sammlung, aber mit etwas „out of the box“-Denken kann Code geschrieben werden, um eine Sortierung durchzuführen, wobei die Excel-Arbeitsblatt-Sortierfunktion verwendet wird. Dieser Code verwendet ein leeres Arbeitsblatt namens "SortSheet", um die eigentliche Sortierung durchzuführen.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Untersortiersammlung()Dim MyCollection als neue KollektionZähler so lang dimmen'Sammlung mit zufälligen Bestellgegenständen erstellenMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Artikel1"MyCollection.Add "Item3"„Erfassen Sie die Anzahl der Elemente in der Sammlung für die zukünftige VerwendungZähler = MyCollection.Count‘Iterieren Sie durch die Sammlung und kopieren Sie jedes Element in eine aufeinanderfolgende Zelle auf ‘SortSheet’ (Spalte A)Für n = 1 Zu MyCollection.CountSheets("SortSheet").Cells(n, 1) = MyCollection(n)Nächste Nr‘Aktivieren Sie das Sortierblatt und verwenden Sie die Excel-Sortierroutine, um die Daten in aufsteigender Reihenfolge zu sortierenSheets("SortSheet").AktivierenRange("A1:A" & MyCollection.Count).AuswählenActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.ClearActiveWorkbook.Worksheets("SortSheet"). Sort.SortFields.Add2 Schlüssel:=Bereich( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSortNormalMit ActiveWorkbook.Worksheets("SortSheet").Sort.SetRange Range("A1:A5").Header = xlGuess.MatchCase = Falsch.Orientierung = xlTopToBottom.SortMethod = xlPinYin.AnwendenEnde mit„Alle Elemente in der Sammlung löschen – beachten Sie, dass diese For Next-Schleife in umgekehrter Reihenfolge ausgeführt wirdFür n = MyCollection.Count To 1 Step -1Meine Sammlung.Entfernen (n)Nächste Nr‘Kopieren Sie die Zellenwerte zurück in das leere Sammlungsobjekt, indem Sie den gespeicherten Wert (Zähler) für die ‘Schleife’ verwendenFür n = 1 zum ZählerMyCollection.Add Sheets("SortSheet").Cells(n, 1).ValueNächste Nr„Durchlaufen Sie die Sammlung, um die Reihenfolge zu überprüfen, in der sich die Artikel jetzt befindenFür jeden Artikel in MyCollectionMsgBox-ElementNächstes Objekt‘Löschen Sie das Arbeitsblatt (Sortierblatt) – ggf. auch löschenSheets("SortSheet").Range(Cells(1, 1), Cells(Counter, 1)).LöschenEnd Sub |
Dieser Code erstellt zunächst eine Sammlung mit den hinzugefügten Elementen in zufälliger Reihenfolge. Anschließend werden sie in die erste Spalte eines Arbeitsblatts (SortSheet) kopiert.
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 von Daten geleert. Beachten Sie, dass die Schrittoption verwendet wird, damit sie vom Ende der Sammlung bis zum Anfang gelöscht wird. Dies liegt daran, dass beim Löschen die Indexwerte zurückgesetzt werden. Wenn es von Anfang an gelöscht wird, wird es nicht korrekt gelöscht (Index 2 würde zu Index 1)
Schließlich werden die Item-Werte mit einer weiteren For Next-Schleife zurück in die leere Collection übertragen
Ein weiterer For Each Loop beweist, dass die Kollektion nun gut aufsteigend geordnet ist.
Leider handelt es sich hierbei nicht um eventuell ursprünglich eingegebene Key-Werte, da die Key-Werte nicht gelesen werden können
Übergabe einer Sammlung an eine Sub / Funktion
Eine Collection kann wie jeder andere Parameter an eine Sub oder eine Funktion übergeben werden
| 1 | Funktion MyFunction(ByRef MyCollection als 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, die unglückliche Auswirkungen haben kann
Wenn eine Kopie mit „ByVal“ erstellt wird, geschieht alles, was die Sammlung innerhalb der Funktion ändert, nur auf der Kopie und nicht auf dem Original. Wenn beispielsweise innerhalb der Funktion ein neues Element zur Sammlung hinzugefügt wird, wird dieses nicht in der ursprünglichen Sammlung angezeigt, was zu einem Fehler in Ihrem Code führt.
Rückgabe einer Collection aus einer Funktion
Sie können eine Sammlung von einer Funktion auf die gleiche Weise zurückgeben, wie Sie ein beliebiges Objekt zurückgeben. Sie müssen das Schlüsselwort Set verwenden
| 12345 | Sub ReturnFromFunction()Dim MyCollection als SammlungSet MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Dieser Code erstellt eine Unterroutine, die ein Objekt namens „MyCollection“ erstellt und dann das Schlüsselwort „Set“ verwendet, um die Funktion zum Auffüllen dieser Sammlung effektiv aufzurufen. Sobald dies erledigt ist, wird ein Meldungsfeld angezeigt, in dem die Anzahl von 2 Elementen angezeigt wird
| 1234567 | Funktion PopulateCollection() als SammlungDim MyCollection als neue KollektionMyCollection.Add "Artikel1"MyCollection.Add "Item2"Setze PopulateCollection = MyCollectionEndfunktion |
Die Funktion PopulateCollection erstellt ein neues Collection-Objekt und füllt es mit 2 Items. Anschließend übergibt es dieses Objekt an das Sammlungsobjekt, das in der ursprünglichen Unterroutine erstellt wurde.
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 manipuliert werden können. Dieser Code erstellt eine kleine Sammlung und überträgt sie dann in ein Array
Beachten Sie, dass der Sammlungsindex bei 1 beginnt, während der Array-Index bei 0 beginnt. Während die Sammlung 3 Elemente enthält, muss das Array nur auf 2 dimensioniert werden, da es ein Element 0 gibt
| 1234567891011121314151617 | Unter ConvertCollectionToArray()Dim MyCollection als neue KollektionDim MyArray(2) als StringMyCollection.Add "Artikel1"MyCollection.Add "Item2"MyCollection.Add "Item3"Für n = 1 Zu MyCollection.CountMyArray(n - 1) = MyCollection(n)Nächste NrFür n = 0 bis 2MsgBox MyArray(n)Nächste NrEnd Sub |
Konvertieren eines Arrays in eine Sammlung
Vielleicht möchten Sie ein Array in eine Sammlung umwandeln. Beispielsweise möchten Sie möglicherweise schneller und eleganter auf die Daten zugreifen, als wenn Sie Code zum Abrufen eines Array-Elements verwenden.
Beachten Sie, dass dies nur für eine einzige Dimension des Arrays funktioniert, da die Sammlung nur eine Dimension hat
| 123456789101112131415 | Sub ConvertArrayIntoCollection()Dim MyCollection als neue KollektionDim MyArray(2) als StringMyArray(0) = "Element1"MyArray(1) = "Element2"MyArray(2) = "Element3"Für n = 0 bis 2MyCollection.Add MyArray(n)Nächste NrFür jeden Artikel in MyCollectionMsgBox-ElementNächstes ObjektEnd 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, sodass Sie beim Lesen des Sammlungswerts das Trennzeichen programmgesteuert verwenden können, um trennen Sie die Werte.
Sie können die Daten auch auf der Grundlage in die Sammlung verschieben, dass der Wert der ersten Dimension hinzugefügt wird (Index 1) und dann der Wert der nächsten Dimension hinzugefügt wird (Index 2) und so weiter.
Wenn das Array beispielsweise 4 Dimensionen hätte, wäre jeder vierte Wert in der Sammlung ein neuer Satz von Werten.
Sie könnten auch Array-Werte hinzufügen, die als Schlüssel verwendet werden (sofern sie eindeutig sind), was eine einfache Möglichkeit zum Auffinden bestimmter Daten bietet.
