VBA-Array-Liste - Mega-Leitfaden für 2022 aktualisiert - Automate Excel (2023)

Verwendung einer VBA-Array-Liste

Eine Array-Liste ist ein VBA-Objekt, das zum Speichern von Werten verwendet werden kann. Es ist einemSammlungsobjektähnlich, ist aber aus programmiertechnischer Sicht wesentlich flexibler. Lassen Sie uns einige Unterschiede zwischen Array-Listen,Sammlungen undArraysdiskutieren.

• Das Sammlungsobjekt hat nur zwei Methoden (Add, Remove) und zwei Eigenschaften (Count, Item), während eine Array-Liste über viel mehr verfügt.
• Das Sammlungsobjekt ist schreibgeschützt. Sobald Werte hinzugefügt wurden, kann der indizierte Wert nicht mehr geändert werden, während eine Bearbeitung bei einer Array-Liste möglich ist.
• Das Array-Listenobjekt vergrößert und verkleinert sich je nach Anzahl der darin enthaltenen Elemente. Es muss vor der Verwendung nicht wie ein Array dimensioniert werden.
• Die Array-Liste ist eindimensional (wie das Sammlungsobjekt) und der Standard-Datentyp istVariante, was bedeutet, dass sie jeden Datentyp akzeptiert, egal ob es sich um Zahlen, Text oder Datum handelt.

In vielerlei Hinsicht behebt die Array-Liste eine Reihe von Mängeln des Sammlungsobjekts. Sie ist sicherlich viel flexibler in ihren Möglichkeiten.

Das Array-Listenobjekt ist nicht Teil der Standard-VBA-Bibliothek. Sie können es in Ihrem Excel-VBA-Code verwenden, indem Sie diespäte oder frühe Bindungnutzen.

Sub SpaeteBindungBeispiel()Dim MeineListe As ObjectSet MeineListe = CreateObject("System.Collections.ArrayList")End Sub

Sub FrueheBindungBeispiel()Dim MeineListe As New ArrayListEnd Sub

Um das Beispiel zur frühen Bindung zu verwenden, müssen Sie zunächstin VBAeinenVerweisauf die Datei „mscorlib.tlb“ eingeben.

Wählen Sie dazu im Fenster desVisual Basic-Editor (VBE)die Option „Extras | Verweise“. Es erscheint ein Pop-up-Fenster mit allen verfügbaren Verweisen. Scrollen Sie nach unten zu ‚mscorlib.dll‘ und markieren Sie das Kästchen daneben. Klicken Sie auf OK und diese Bibliothek ist nun Teil Ihres Projekts:

Einer der großen Nachteile eines Array-Listenobjekts ist die Tatsache, dass es keine „IntelliSense“ hat. Normalerweise sehen Sie bei der Verwendung eines Objekts in VBA, z. B. eines Bereichs, eine Popup-Liste mit allen verfügbaren Eigenschaften und Methoden. Bei einem Array-Listenobjekt ist dies nicht der Fall und manchmal müssen Sie sorgfältig prüfen, ob Sie die Methode oder Eigenschaft richtig geschrieben haben.

Auch wenn Sie im VBE-Fenster die Taste F2 drücken und nach „arraylist“ suchen, wird nichts angezeigt, was für den Entwickler nicht sehr hilfreich ist.

Ihr Code wird mit der frühen Bindung wesentlich schneller laufen, da alles im Voraus kompiliert wird. Bei der späten Bindung muss das Objekt kompiliert werden, während der Code aufgeführt wird.

Verteilen Ihrer Excel-Anwendung, die eine Array-Liste enthält

Wie bereits erwähnt, ist das Array-Listenobjekt nicht Teil von Excel VBA. Das bedeutet, dass jeder Ihrer Kollegen, an den Sie die Anwendung weitergeben, Zugriff auf die Datei ‚mscorlib.tlb‘ haben muss.

Diese Datei befindet sich normalerweise unter:C:\Windows\Microsoft.NET\Framework\v4.0.30319

Es könnte sich lohnen, etwas Code zu schreiben (unter Verwendung derDir-Methode), einszu prüfen, ob diese Datei vorhanden ist, wenn ein Benutzer die Anwendung lädt, damit er eine „weiche Landung“ erlebt, wenn sie nicht gefunden wird. Wenn sie nicht vorhanden ist und der Code ausgeführt wird, treten Fehler auf.

Außerdem muss der Benutzer die richtige Version von .Net Framework installiert haben. Selbst wenn der Benutzer eine neuere Version hat, muss V3.5 installiert sein, sonst funktioniert Ihre Anwendung nicht.

Umfang eines Array-Listenobjekts

Das Array-Listenobjekt ist 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 das Array-Listenobjekt mit VBA-Code neu erstellt werden.

Wenn Sie möchten, dass Ihre Array-Liste für den gesamten Code in Ihrem Code-Modul zur Verfügung steht, müssen Sie das Array-Listenobjekt im Declare-Abschnitt ganz oben im Modulfenster deklarieren

Dadurch wird sichergestellt, dass der gesamte Code in diesem Modul auf die Array-Liste zugreifen kann. Wenn Sie möchten, dass jedes Modul innerhalb Ihrer Arbeitsmappe auf das Array-Listenobjekt zugreifen kann, dann definieren Sie es alsglobales Objekt.

Global MeineSammlung As New ArrayList

Auffüllen und Auslesen Ihrer Array-Liste

Die grundlegendeste Aktion, die Sie durchführen möchten, besteht darin, eine Array-Liste zu erstellen, einige Daten darin zu speichern und dann nachzuweisen, dass die Daten gelesen werden können. Alle Codebeispiele in diesem Artikel gehen davon aus, dass Sie die frühe Bindung verwenden und „mscorlib.tlb“ zu den VBA-Verweisen wie oben beschrieben hinzugefügt haben.

Sub ArrayListeBeispiel()'Neues Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Elemente zur Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Durch die Array-Liste iterieren, um Werte zu prüfenFor N = 0 To MeineListe.Count - 1 MsgBox MeineListe(N)Next NEnd Sub

In diesem Beispiel wird ein neues Array-Listenobjekt erstellt, das mit 3 Elementen aufgefüllt wird, und dann wird die Liste durchlaufen, wobei jedes Element angezeigt wird.

Beachten Sie, dass der Array-Listenindex bei 0 und nicht bei 1 beginnt, so dass Sie 1 vom Count-Wert subtrahieren müssen.

Sie können auch eine‚Für…Jeden‘-Schleifeverwenden, um die Werte auszulesen:

Sub ArrayListeBeispiel()'Neues ArrayList-Objekt erstellenDim MeineListe As New ArrayList'Elemente zur Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Durch die Array-Liste iterieren, um Werte zu prüfenFor Each I In MeineListe MsgBox INext IEnd Sub

Elemente in einer Array-Liste bearbeiten und ändern

Ein großer Vorteil einer Array-Liste gegenüber einer Sammlung besteht darin, dass die Elemente in der Liste innerhalb Ihres Codes bearbeitet und geändert werden können. Das Sammlungsobjekt ist nur lesbar, während das Array-Listenobjekt schreib- und lesefähig ist.

Sub ArrayListeBeispiel()'Neues Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Elemente zur Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Eintrag 1 von 'Eintrag2' auf 'Geändert' ändernMeineListe(1) = "Geändert"'Durch die Array-Liste iterieren, um nachzuweisen, dass die Änderung stattgefunden hatFor Each I In MeineListe 'Name des Elements anzeigen MsgBox INext IEnd Sub

In diesem Beispiel wird das zweite Element („Element2“) auf den Wert „Geändert“ geändert (der Index beginnt bei 0). Wenn die Iteration am Ende des Codes ausgeführt wird, wird der neue Wert angezeigt.

Ein Array von Werten zu einer Array-Liste hinzufügen

Sie können Werte in Ihre Array-Liste einfügen, indem Sie ein Array, das eine Liste dieser Werte oder Verweise auf Zellenwerte in einem Arbeitsblatt enthält, verwenden.

Sub ArrayHinzufuegenBeispiel()'Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Durch Array-Werte iterieren und sie der Array-Liste hinzufügenFor Each W In Array("A1", "A2", "A3") 'Jeden Array-Wert zur Liste hinzufügen MeineListe.Add WNext'Array-Werte mit Arbeitsblattverweisen durchlaufen und zur Array-Liste hinzufügenFor Each W In Array(Range("A5").Value, Range("A6").Value) MeineListe.Add WNext'Durch die Array-Liste iterieren, um die Werte zu prüfenFor N = 0 To MeineListe.Count - 1 'Listenelement anzeigen MsgBox MeineListe.Item(N)Next NEnd Sub

Einen Bereich von Elementen aus einer Array-Liste auslesen/abrufen

Durch Anwendung der GetRange-Methode an einer Array-Liste können Sie einen Bereich von aufeinanderfolgenden Elementen, die abgerufen werden sollen, angeben. Die beiden erforderlichen Parameter sind die Startindexposition und die Anzahl der abzurufenden Elemente. Der Code füllt ein zweites Array-Listenobjekt mit der Teilmenge von Elementen, die dann separat gelesen werden können.

Sub BereichLesenExample()'Objekte definierenDim MeineListe As New ArrayList, MeineListe1 As Object'Elemente zum Objekt 'MeineListe' hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"MeineListe.Add "Element6"MeineListe.Add "Element4"MeineListe.Add "Element7"'4 Elementen in 'MeineListe' ab Indexposition 2 erfassenSet MeineListe1 = MeineListe.GetRange(2, 4)'Durch das Objekt "MeineListe1" iterieren, um die Teilmenge der Elemente anzuzeigenFor Each I In MeineListe1 'Name des Elements anzeigen MsgBox INext IEnd Sub

Suche nach Elementen innerhalb einer Array-Liste

Mit der Contains-Methode können Sie prüfen, ob ein bestimmtes Element in Ihrer Liste enthalten ist. Diese Methode gibtWAHRoderFALSCHzurück

MsgBox MeineListe.Contains("Element2")

Sie können auch die tatsächliche Indexposition mit der IndexOf-Methode ermitteln. Sie müssen den Startindex für die Suche angeben (normalerweise 0). Der Rückgabewert ist der Index der ersten Instanz des gesuchten Elements. Sie können dann eine Schleife verwenden, um den Startpunkt auf den nächsten Indexwert zu ändern, um weitere Instanzen zu finden, wenn es mehrere doppelte Werte gibt.

Wenn der Wert nicht gefunden wird, wird der Wert -1 zurückgegeben.

Dieses Beispiel demonstriert die Verwendung von ‚Contains‘, Element nicht gefunden und Durchlaufen der Array-Liste, um die Positionen aller doppelten Elemente zu finden:

Sub SucheInListeBeispiel()'Array-Liste und Variablen definierenDim MeineListe As New ArrayList, Sp As Integer, Pos As Integer'Neue Elemente einschließlich eines Duplikats hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"MeineListe.Add "Element1"'Prüfen, ob "Element2" in der Liste enthalten ist - gibt True zurückMsgBox MeineListe.Contains("Element2")'Index eines nicht vorhandenen Wertes ermitteln - gibt -1 zurückMsgBox MeineListe.IndexOf("Element", 0)'Die Startposition für die Suche auf Null setzenSp = 0'Durch die Liste iterieren, um alle Positionen von "Element1" zu erhaltenDo 'Die Indexposition vom nächsten 'Element1' anhand der Position in der Variablen 'Sp' ermitteln Pos = MeineListe.IndexOf("Element1", Sp) 'Wenn keine weiteren Instanzen von 'Element1' gefunden werden, dann die Schleife verlassen If Pos = -1 Then Exit Do 'Die nächste gefundene Instanz und die Indexposition anzeigen MsgBox MeineListe(Pos) & " bei Index " & Pos '1 zum letzten gefundenen Indexwert hinzuaddieren - dies wird nun die neue Startposition für die nächste Suche Sp = Pos + 1LoopEnd Sub

Beachten Sie, dass der verwendete Suchtext zwischen Groß- und Kleinschreibung unterscheidet und Platzhalter nicht zulässig sind.

Elemente einfügen und entfernen

Wenn Sie Ihre Elemente nicht am Ende der Liste hinzufügen möchten, können Sie sie an einer bestimmten Indexposition einfügen, so dass das neue Element in der Mitte der Liste steht. Die Indexnummern werden für die nachfolgenden Einträge automatisch angepasst.

Sub EinfuegenBeispiel()'Array-Listenobjekt definierenDim MeineListe As New ArrayList'Elemente zur Array-Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"MeineListe.Add "Element1"' 'Element6' an der Indexposition 2 einfügenMeineListe.Insert 2, "Element6"'Durch die Elemente in der Array-Liste iterieren, um die neue Reihenfolge und Indexpositionen anzuzeigenFor N = 0 To MeineListe.Count - 1 MsgBox MeineListe(N) & " Index " & NNext NEnd Sub

In diesem Beispiel wird „Element6“ an der Indexposition 2 in die Liste eingefügt, so dass „Element3“, das sich an der Indexposition 2 befand, nun an die Indexposition 3 verschoben wird.

Ein einzelnes Element kann mit der Remove-Methode entfernt werden.

MeineListe.Remove "Element"

Es ist zu beachten, dass es keinen Fehler gibt, wenn der Name des Elements nicht gefunden wird. Alle nachfolgenden Indexnummern werden gemäß diesem Entfernvorgang geändert.

Wenn Sie die Indexposition des Elements kennen, können Sie die RemoveAt-Methode verwenden, z. B.

MeineListe.RemoveAt 2

Beachten Sie, dass ein Fehler zurückgegeben wird, wenn die angegebene Indexposition größer als die Anzahl der Elemente in der Array-Liste ist.

Sie können einen Wertebereich aus der Liste entfernen, indem Sie die RemoveRange-Methode verwenden. Die Parameter sind der Startindex und dann die Anzahl der zu entfernenden Elemente, z.B.

MeineListe.RemoveRange 3, 2

Beachten Sie, dass Sie einen Fehler in Ihrem Code erhalten, wenn die Anzahl der Elemente ab dem Startwert größer ist als die Anzahl der Elemente in der Array-Liste ist.

Sowohl bei der RemoveAt- als auch bei der RemoveRange-Methode wäre es ratsam zu prüfen, ob die angegebenen Indexzahlen größer als die Gesamtzahl der Elemente in der Array-Liste sind, um mögliche Fehler abzufangen. Die Count-Eigenschaft gibt die Gesamtzahl der Elemente in der Array-Liste an.

Sub EntfernenBeispiel()'Array-Listenobjekt definierenDim MeineListe As New ArrayList'Elemente zur Array-Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"MeineListe.Add "Element1"MeineListe.Add "Element4"MeineListe.Add "Element5"' 'Element6' an Indexposition 2 einfügenMeineListe.Insert 2, "Element6"'Element2' entfernenMeineListe.Remove "Element2"' 'Element' entfernen - dieses existiert nicht in der Array-Liste, macht aber keinen FehlerMeineListe.Remove "Element"'Das Element an der Indexposition 2 entfernenMeineListe.RemoveAt 2'2 aufeinanderfolgende Elemente ab der Indexposition 2 entfernenMeineListe.RemoveRange 3, 2'Durch die Array-Liste iterieren, um zu zeigen, was übrig ist und an welcher Indexposition es sich jetzt befindetFor N = 0 To MeineListe.Count - 1 MsgBox MeineListe(N) & " Index " & NNext NEnd Sub

Beachten Sie bei der Verwendung von „RemoveAt“ zum Entfernen eines Elements an einer bestimmten Position, dass alle nachfolgenden Indexpositionen geändert werden, sobald dieses Element entfernt wurde. Wenn Sie mehrere Elemente über die Indexposition entfernen, ist es eine gute Idee, mit der höchsten Indexnummer zu beginnen und rückwärts bis zur Position Null zu gehen, damit Sie immer das richtige Element entfernen. Auf diese Weise haben Sie das Problem nicht

Eine Array-Liste sortieren

Ein weiterer großer Vorteil gegenüber einer Sammlung ist die Tatsache, dass Sie die Elemente in aufsteigender oder absteigender Reihenfolge sortieren können.

Das Array-Listenobjekt ist das einzige Objekt inExcel-VBAmit einer Sortiermethode. Die Sortiermethode ist sehr schnell und dies kann ein wichtiger Aspekt bei der Verwendung einer Array-Liste sein.

Beim Sammlungsobjekt musste man sich etwas einfallen lassen, um alle Elemente zu sortieren, aber mit einer Array-Liste ist das sehr einfach.

Die Sort-Methode sortiert in aufsteigender Reihenfolge und die Reverse-Methode in absteigender Reihenfolge.

Sub ArrayListeBeispiel()'Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Elemente in unsortierter Reihenfolge hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element3"MeineListe.Add "Element2"'Die Elemente in aufsteigender Reihenfolge sortierenMeineListe.Sort'Durch die Elemente iterieren, um die aufsteigende Reihenfolge anzuzeigenFor Each I In MeineListe 'Name des Elements anzeigen MsgBox INext I'Die Elemente in absteigender Reihenfolge sortierenMeineListe.Reverse'Durch die Elemente iterieren, um die absteigende Reihenfolge anzuzeigenFor Each I In MeineListe 'Name des Elements anzeigen MsgBox INext IEnd Sub

Eine Array-Liste klonen

Eine Array-Liste hat die Möglichkeit, einen Klon oder eine Kopie von sich selbst zu erstellen. Dies ist nützlich, wenn der Benutzer über ein Frontend und Ihren VBA-Code Änderungen an den Elementen vornimmt, Sie aber eine Kopie der Elemente in ihrem ursprünglichen Zustand als Backup behalten müssen.

Dies könnte dem Benutzer eine Undo-Funktion bieten. Möglicherweise hat er die Änderungen vorgenommen und möchte zur ursprünglichen Liste zurückkehren.

Sub KlonenBeispiel()'Zwei Objekte definieren - eine Array-Liste und ein ObjektDim MeineListe As New ArrayList, MeineListe1 As Object'Erstes Objekt mit Elementen auffüllenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'MeineListe in MeineListe1 kopierenSet MeineListe1 = MeineListe.Clone'Durch MeineListe1 iterieren, um das Klonen nachzuweisenFor Each I In MeineListe1 'Namen des Elements anzeigen MsgBox INext IEnd Sub

MeineListe1″ enthält nun alle Elemente von „MeineListe“ in der gleichen Reihenfolge

Eine Array-Liste in ein herkömmliches VBA-Array-Objekt hineinkopieren

Sie können eine einfache Methode verwenden, um die Array-Liste in ein normales VBA-Array zu kopieren:

Sub ArrayBeispiel()'Array-Listenobjekt und ein Standard-Array-Objekt erstellenDim MeineListe As New ArrayList, NeuesArray As Variant'Array-Liste mit Elementen auffüllenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Die Array-Liste in das neue Array kopierenNeuesArray = MeineListe.ToArray'Durch das neue Array Iterieren - beachten Sie, dass die Anzahl der Array-Listen den maximalen Index liefertFor N = 0 To MeineListe.Count - 1 'Namen des Elements anzeigen MsgBox NeuesArray(N)Next NEnd Sub

Eine Array-Liste in einen Arbeitsblattbereich hineinkopieren

Sie können Ihre Array-Liste in ein bestimmtes Arbeitsblatt und einen Zellenbezug hineinkopieren, ohne diese durchlaufen zu müssen. Sie müssen nur den ersten Zellenbezug angeben

Sub BereichBeispiel()'Neues Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Elemente zur Liste hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Das Zielblatt leerenSheets("Tabelle1").UsedRange.Clear'Elemente in eine Zeile kopierenSheets("Tabelle1").Range("A1").Resize(1, MeineListe.Count).Value = MeineListe.toArray'Elemente in eine Spalte kopierenSheets("Tabelle1").Range("A5").Resize(MeineListe.Count, 1).Value = _WorksheetFunction.Transpose(MeineListe.toArray)End Sub

Alle Elemente aus einer Array-Liste löschen

Es gibt eine einfache Funktion (Clear), um die Array-Liste vollständig zu leeren.

Sub ListeLeerenBeispiel()'Array-Listenobjekt erstellenDim MeineListe As New ArrayList'Neue Elemente hinzufügenMeineListe.Add "Element1"MeineListe.Add "Element2"MeineListe.Add "Element3"'Anzahl der Elemente anzeigenMsgBox MeineListe.Count'Alle Einträge löschenMeineListe.Clear'Die Anzahl der Elemente anzeigen, um nachzuweisen, dass das Löschen funktioniert hatMsgBox MeineListe.CountEnd Sub

In diesem Beispiel werden Elemente in einer Array-Liste erstellt und dann wird diese geleert. Meldungsboxen zeigen Die Anzahl der Elemente in der Array-Liste von vorher und nachher.

Methoden derArray-Liste – Zusammenfassung für Excel VBA