Array

Arrays kurz vorgestellt

Arrays sind standardmäßig Null-basierend -- das bedeutet, dass das erste Element den Index 0 besitzt, das zweite Element den Index 1, und das letzte Element den Index Anzahl - 1. Die Elemente eines Arrays haben feste Plätze, Sie können also nicht nachträglich ein Element 'dazwischenschieben' (wie es bei einer Collection möglich ist). Sie müssen in einem solchen Fall erst alle darauffolgenden Elemente um einen Platz verschieben, damit das neue Element zwischen diese gesetzt werden kann. Der Wert eines Speicherplatzes in einem Array kann jederzeit überschrieben werden. Plätze, welchen keine Werte erhalten, bleiben weiterhin in der Auflistung erhalten.

Alternativen

Alternativ zu einem eindimensionalen Array (also einer Liste) können Sie auch eine Collection verwenden. Die Collection ist etwas einfacher zu handhaben und oft leichter verständlich (sie ist zum Beispiel 1-basierend). Ein Großteil der Auflistungen in den Anwendungs-Objekten basieren auf der Collection (zum Beispiel Application.Documents, ActiveDocument.Fields, etc.). Bitte beachten Sie hierzu den Vergleich zwischen Collection und Array. Eine Alternative zu mehrdimensionalen Arrays gibt es nicht.

Arrays deklarieren

Statische Arrays

Bei statischen Arrays wird bei der Deklaration festgelegt, wie viele Speicherplätze das Array bereithalten soll. Die Größe des Arrays kann später niemals geändert werden, sie ist statisch. Dim strListWeekdays(6) As String In diesem Beispiel wird das Ausmaß des Arrays fest vorgegeben (0-6). Die Angabe '6' betrifft den Index des letzten Elements, NICHT die Anzahl der Elemente! In diesem Fall werden 7 (!) Plätze für Daten reserviert.

Dynamische Arrays

Bei dynamischen Arrays wird nicht bei der Deklaration festgelegt, wie viele Speicherplätze reserviert werden sollen. Dies ist insbesondere dann nützlich, wenn Sie die Anzahl der benötigten Speicherplätze erst ermitteln müssen. Dim strListOfNames() As String Dynamische Arrays müssen vor ihrer Verwendung mit 'ReDim' dimensioniert werden. Die angegebene Dimension entspricht NICHT der Anzahl der geforderten Elemente, sondern den obersten vergebenen Index. Bei einer Basis von '0' (Null) entspricht dies der Anzahl - 1. lngCount = 5 ReDim strListOfNames(lngCount - 1) Bei diesem Beispiel wird das Ausmaß des Arrays nach Ermittlung der benötigten Größe dimensioniert. Die Angabe '4' (5 - 1) betrifft den Index des letzten Elements, NICHT die Anzahl der Elemente! In diesem Fall werden die gewünschten 5 (!) Plätze für Daten reserviert und erhalten die Indizes 0-4.

Deklaration mit ReDim

Bitte beachten Sie folgendes: Der Befehl 'ReDim' kann nicht nur ein dynamisches Array neu dimensionieren, sondern zusätzlich auch deklarieren. Dies bedeutet, dass, wenn Sie beim 'ReDim' ein noch nicht mit 'Dim' deklariertes Array angeben, dieses beim 'ReDim' deklariert wird. Dies kann wiederum dazu führen, dass Schreibfehler im Variablennamen bei der 'ReDim'-Anweisung ignoriert werden und eine zweite, falsch geschriebene Variable entsteht: Dim strArray() As String ReDim strAray(2) ' strArray falsch geschrieben (nur ein 'r') strArray(0) = "Peter" ' Erzeugt Fehler, denn strArray wurde noch nicht dimensioniert! Das 'Option Explicit' hat auf dieses Verhalten keinen Einfluss, weil 'ReDim' auch deklarieren kann.

Die Basis eines Arrays

Bitte denken Sie daran, bei Arrays, deren Basis nicht bekannt ist, die 'LBound'-Funktion zu verwenden, um den Index des ersten Elementes zu ermitteln.

Null-basierend

Wie schon besprochen, basieren 'natürliche' Arrays auf Null, das erste Element hat also den Index 0. Wenn Sie keine weitere Angabe bei der Dimensionierung machen bzw. die 'Split'-Funktion verwenden, um ein Array aus einer Liste erzeugen zu lassen, wird automatisch ein Null-basierendes Array erzeugt.

Eins-basierend

In der Regel muss bei Arrays, die nicht Null-basierend sind, bei der Dimensionierung angegeben werden, mit welchem Index das Array beginnen soll. Die Zeile Redim strArray(1 To 3) erzeugt demnach ein Array, dessen erstes Element den Index 1 hat. Der letzte Index beträgt 3, also enthält das Array drei Elemente mit den Indizes 1, 2 und 3. Bitte beachten: Wenn Sie ein Array aus einem Excel Zellenbereich einlesen, wird automatisch ein Eins-basiertes Array erstellt! Aber: Funktionen, welche Arrays zum Beispiel aus Listen erstellen (siehe Split), setzt sich die Funktion über die bestehende Dimensionierung hinweg und erzeugt automatisch ein Null-basierendes Array!

Arrays mit frei definiertem Anfangs-Index

Der Index des ersten Elementes eines Arrays kann bei der Dimensionierung frei angegeben werden, solange er kleiner als der Index des letzten Elementes ist: Redim strArray(-12 To 34)

Arrays mit Daten füllen

Im folgenden Beispiel wird ein dynamisches Array erzeugt, dimensioniert und mit Daten gefüllt: Dim strListOfNames() As String Dim lngCount As Long lngCount = 5 ReDim strListOfNames(lngCount - 1) strListOfNames(0) = "Peter Paulsen" strListOfNames(1) = "Max Mustermann" strListOfNames(2) = "Maxine Musterfrau" strListOfNames(3) = "Kalle Blomquist" strListOfNames(4) = "Emil Tischbein"

Dynamische Arrays nachträglich erweitern

Bei einer erneuten Dimensionierung beachten Sie bitte, dass Sie den Zusatz 'Preserve' verwenden: lngCount = 7 ' ReDim strListOfNames(lngCount - 1) ' Dimensioniert auf neue Größe, löscht alle Daten ReDim Preserve strListOfNames(lngCount - 1) ' Preserve erhält vorhandene Daten strListOfNames(5) = "Lotte Reininger" strListOfNames(6) = "Sandra Bullock"

Daten aus einem Array auslesen

Die einzelnen Speicherplätze werden mit Angabe des Speicherindexes wieder ausgelesen: Debug.Print strArray(0) ' Gibt das erste Element im Direktbereich aus

Arrays zurücksetzen: Erase

Der 'Erase'-Befehl setzt ein Array zurück.

• Bei statischen Arrays werden alle Elemente auf den Standardwert gesetzt.
• Bei dynamischen Arrays werden sämtliche Elemente gelöscht. Erase strArray

Arrays in Prozeduren übergeben und aus Funktionen erhalten

Arrays als Übergabeparameter

Die Übergabe eines Arrays an eine Prozedur erfolgt wie bei der Dim-Anweisung (runde Klammern nach dem Variablennamen). Eine statische Dimensionierung ist hier nicht möglich! Bitte beachten: Arrays können NICHT 'ByVal' übergeben werden! Private Sub PrintArrayElements(ByRef strArray() As String) Dim lngLow As Long, lngUpper As Long, lngIndex As Long lngLow = LBound(strArray) lngUpper = UBound(strArray) For lngIndex = lngLow To lngUpper Debug.Print strArray(lngIndex) Next lngIndex End Sub

Rückgabewert einer Funktion

Die runden Klammern werden in diesem Fall hinter den Datentyp gesetzt. In einer Funktion, welche ein Array zurückgeben soll, müssen Sie ein eigenes Rückgabe-Array innerhalb der Funktion füllen und dieses dann mithilfe des Funktionsnamens übergeben: Private Function LoadData() As String() Dim strReturn() As String ' Rückgabe-Array ReDim strReturn(2) strReturn(0) = "Maren" strReturn(1) = "Monika" strReturn(2) = "Melanie" LoadData = strReturn End Function Private Sub Test() PrintArrayElements LoadData() End Sub Bitte beachten: Eine Array-Funktion kann ausschließlich dynamisch deklariert werden. Folgendes führt zu einem Kompilierfehler: Private Function LoadData() As String(3) Wichtig: Wenn Sie ein Array aus einer Funktion in eine Variable übernehmen, darf diese Variable nicht ein statisches Array sein! Begründung: VBA darf das statische Array nicht beliebig erweitern oder verringern, kann jedoch bei der Funktion, welche ein Array zurückgibt, nicht prüfen, ob es mit der Größe des statisch vorgegebenen Array übereinstimmt. Folgendes klappt daher nicht: Private Sub Test() Dim strData(8) As String strData = LoadData() ' Die LoadData-Funktion gibt ein dynamisches Array zurück :-( PrintArrayElements strData End Sub Siehe auch Fehlermeldungen: Keine Zuweisung an Datenfeld möglich!

Mehrdimensionale Arrays

Ein eindimensionales Array können wir uns als Liste vorstellen, in der mehrere Einträge untereinander stehen. Ein zweidimensionales Array entspricht dann einer Tabelle, welche wir zeilen- und spaltenweise befüllen und ansprechen können. Ein dreidimensionales Array können wir uns wie einen Quader vorstellen, der aus vielen kleinen Würfeln besteht, welche jeweils Daten enthalten können. Oder als eine Sammlung von Tabellen, welche den Zustand einer Datenmenge zu einem unterschiedlichen Zeitpunkt darstellen (wie Messwerte, welche jedes Jahr in eine neue Tabelle einfließen und somit von Jahr zu Jahr vergleichbar sind).

Statisches mehrdimensionales Array

Die Dimensionen werden bei der Deklaration in der runden Klammer angegebene und durch Kommata getrennt: Dim strArrayStatic(3, 5) As String ' Zweidimensionales statisches Array Dim strArrayStatic(3, 5, 2) As String ' Dreidimensionales statisches Array

Dynamisches mehrdimensionales Array

Die Deklaration eines dynamischen mehrdimensionalen Arrays erfolgt identisch zur Deklaration eines dynamischen eindimensionalen Arrays: Dim strArrayDynamic() As String Die Angabe der verschiedenen Dimensionen erfolgt dann bei der Dimensionierung mit 'ReDim': ReDim strArrayDynamic(3, 5) ' Zweidimensionales dynamisches Array ReDim strArrayDynamic(3, 5, 2) ' Dreidimensionales dynamisches Array Private Sub TwoDimensionalArray() Dim strRow As String Dim lngRow As Long, lngColumn As Long ' Ein eindimensionales Array entspricht einer Liste ' Ein zweidimesionales Array entspricht einer Tabelle Dim strArrayStatic(3, 5) As String Dim strArrayDynamic() As String ' Deklaration eines dynamischen Arrays wie bei eindimensionalen Arrays ReDim strArrayDynamic(3, 4) ' 4 Zeilen, 5 Spalten strArrayDynamic(0, 0) = "Huhu" strArrayDynamic(0, 1) = "Haha" ' ... strArrayDynamic(2, 3) = "Irgendwo mittendrin" strArrayDynamic(3, 4) = "Ende" For lngRow = LBound(strArrayDynamic, 1) To UBound(strArrayDynamic, 1) For lngColumn = LBound(strArrayDynamic, 2) To UBound(strArrayDynamic, 2) strRow = strRow & "|" & strArrayDynamic(lngRow, lngColumn) & " " Next lngColumn Debug.Print strRow & " |" strRow = "" Next lngRow End Sub Wichtig: Sie können bei Arrays mit ReDim lediglich die letzte Dimension neu dimensionieren, alle vorangehenden Dimensionen müssen erhalten bleiben!

Systemfunktionen für Arrays

Array aus einzelnen Werten erstellen: Array

Die Array-Funktion erstellt einen Variant-Array aus den beliebig vielen übergebenen Werten. varArray = Array("Peter", "Paul", "Jonas", "Leon", "Brigitte", "Leonie", "Sandra", "Katrin") Unterschiede zur Split-Funktion:

• Die Array-Funktion kann keine Liste von Elementen entgegennehmen, Elemente müssen getrennt übergeben werden.
• Die Array-Funktion kann ausschließlich auf Arrays, welche vom Datentyp Variant sind, angewendet werden.
• Die Array-Funktion kann auch Zahlen, Datumsangaben etc. entgegennehmen.

Array aus einer Liste erzeugen: Split

Erzeugt aus einer Liste mit Werten ein Array. Das Trennzeichen kann angegeben werden (Standard: Leerzeichen). strArray = Split("Peter,Paul,Jonas,Leon,Brigitte,Leonie,Sandra,Katrin", ",") Die Split-Funktion arbeitet ausschließlich mit Zeichenketten. Arrays vom Typ Long können daher zum Beispiel nicht mit der Split-Funktion befüllt werden! lngArray = Split("10 20 30", " ") ' Fehlermeldung: Typen unverträglich!

Array in eine Liste umwandeln: Join

Erzeugt eine Liste aus einem Array mit Werten. Das Trennzeichen kann angegeben werden (Standard: Leerzeichen). strList = Join(strArray, vbCrLf)

Elemente herausfiltern: Filter

Erzeugt ein neues (String) Array, welches nur die Elemente enthält, welche die gesuchte Zeichenkette enthalten.

 strArray = Split("Peter, Paul, Jonas, Leon, Brigitte, Leonie, Sandra, Katrin", ", ")
 strArray = Filter(strArray, "on")
 Debug.Print Join(strArray, ", ") ' Jonas, Leon, Leonie 

Oder ein Array, welches nur die Elemente enthält, welche nicht die gesuchte Zeichenkette enthalten.

 strArray = Split("Peter, Paul, Jonas, Leon, Brigitte, Leonie, Sandra, Katrin", ", ")
 strArray = Filter(strArray, "on", False)
 Debug.Print Join(strArray, ", ") ' Peter, Paul, Brigitte, Sandra, Katrin

IsArray

Die IsArray-Funktion prüft, ob der Inhalt einer Variablen ein Datenfeld ist. Diese Prüfung ist insbesondere dann sinnvoll, wenn die Variable ein Variant ist und deshalb nicht zwingend ein Array enthalten könnte. Dabei ist es unerheblich, ob das Array schon dimensioniert wurde bzw. Elemente enthält: Dim strText() As String Debug.Print IsArray(strText) ' True == Arrays und Excel Arbeitsblätter== Arrays können verwendet werden, um direkt auf Excel Tabelleninhalte zuzugreifen bzw. diese zu ändern. Dies kann zu deutlichen Verbesserungen der Laufzeit einer Lösung führen, weil dann nicht mehr auf die einzelnen Zellen eines Bereichs zugegriffen werden muss, sondern alle Zugriffe direkt auf den Hauptspeicher erfolgen können.

Werte aus einem Zellenbereich in ein Array einlesen

Hierzu muss ein Variant-Array verwendet werden, weil Excel in seinen Zellen verschiedene Datentypen aufnehmen kann (Text, Zahlen, Datumsangaben, ...). Es wäre zwar möglich, die Werte aus jeder Zelle einzeln auszulesen und dann in den vom Array erwarteten Datentyp umzuwandeln, dies kann jedoch nicht für ein komplettes Array in einem Schritt erfolgen. Dim varValues() As Variant varValues = Selection.Value ' bzw. varValues = Range("A1:F4").Value Hierbei bitte beachten:

• Die 'Value'-Eigenschaft gibt, obwohl sie in der Einzahl erscheint ('Value', nicht 'Values'!), im Zusammenhang mit einem Zellbereich alle Werte der einzelnen Zellen zurück.
• Das Array wird automatisch von Excel korrekt dimensioniert.
• Das Array wird nicht Null-basierend, sondern Eins-basierend sein (der unterste Index ist die 1)!

Werte aus einem Array in einen Zellenbereich schreiben

Beim Schreiben von Daten aus einem Array in einen Zellenbereich muss nicht zwingend ein Variant-Array verwendet werden, weil Excel in seine Zellen beliebige Daten aufnehmen kann. Bitte beachten Sie trotzdem, dass, wenn Sie zum Beispiel Zahlenwerte in Excel Zellen schreiben, die als Datumswerte formatiert sind, Excel diese Werte als Datumsangaben darstellt. Das Zellenformat hat immer Vorrang. Dim strValues() As String ReDim strValues(1 To 3, 1 To 5) ' Eins-basierend strValues(1, 1) = "Anfang" strValues(1, 5) = "Oben rechts" strValues(2, 3) = "Mitte" strValues(3, 1) = "Unten links" strValues(3, 5) = "Ende" Range("A1:E3").Value = strValues bzw. Null-basierend: Dim strValues() As String ReDim strValues(2, 4) ' Null-basierend strValues(0, 0) = "Anfang" strValues(0, 4) = "Oben rechts" strValues(1, 2) = "Mitte" strValues(2, 0) = "Unten links" strValues(2, 4) = "Ende" Range("A1:E3").Value = strValues Hierbei bitte beachten:

• Das Array, aus dem die Daten stammen, sollte zweidimensional angelegt sein. Die erste Dimension enthält die Zeilen, die zweite die Spalten.
• Die Daten aus einem eindimensionalen Array werden ausschließlich als Daten einer Zeile verstanden.
• Wenn Sie die Werte aus einem eindimensionalen Array übernehmen, werden sie in die erste Zeile des Zellenbereichs übernommen.
• Wenn der Zellenbereich eine Spalte statt einer Zeile umfasst, wird nur der erste Wert übernommen un in den weiteren Zeilen wiederholt.
• Beim Schreiben der Werte aus einem Array ist die Basis (0 oder 1) unerheblich (siehe beide Varianten des Beispiels).
• Wichtig: Achten Sie darauf, dass der Bereich, in welchen die Daten geschrieben werden sollen, korrekt dimensioniert ist. Wenn Sie ihn zum Beispiel zu groß wählen, können in den überflüssigen Zellen Fehler angezeigt werden.

Eigene Lösungen für Arrays

ArrayContains: Ist ein Element enthalten?

VBA bietet keine eigene Möglichkeit an, zu prüfen, ob ein Element im Array (schon) enthalten ist. Die natürlichste Lösung könnte darin bestehen, mithilfe einer Schleife zu prüfen, ob eines der Elemente dem gesuchten Kriterium entspricht. Dies kann allerdings bei mehrfacher Ausführung bei einem größeren Array sehr zeitraubend sein. Eine effizientere Lösung besteht darin, das Array in eine Liste (Zeichenkette) umzuwandeln und dann nach dem Element zu suchen: Public Function ArrayContains(ByRef strArray() As String, ByVal strFind As String) As Boolean Dim strTest As String ' 'Join' wandelt das Array in eine Liste um. ' Als Trennzeichen dient hier das '|'-Symbol (kann beliebig erweitert werden). ' WICHTIG: Das Trennzeichen darf NICHT Teil eines Elementes des Arrays sein! ' Im Zweifelsfall das Trennzeichen erweitern, wie '|~%§'. strTest = Join(strArray, "|") ' Das Trennzeichen wird zusätzlich an Liste vorne und hinten angeheftet: strTest = "|" & strTest & "|" ' Nun wird in der Liste nach dem gesuchten Begriff (mit '*' erweitert und ebenfalls ' in Trennzeichen eingehüllt) gesucht. ' Mithilfe der umschließenden Trennzeichen wird sicher gestellt, dass ausschließlich ' komplette Übereinstimmungen gefunden werden: ArrayContains = strTest Like "*|" & strFind & "|*" End Function Private Sub Test() Dim strArray() As String ReDim strArray(2) strArray(0) = "Max Muster" strArray(1) = "Berta Beispiel" strArray(2) = "Peter Paulsen" Debug.Print ArrayContains(strArray, "Max") ' Falsch: Teilweise Übereinstimmung wird ignoriert ' Die Liste '|Max Muster|Berta Beispiel|Peter Paulsen|' enthält nicht '|Max|' Debug.Print ArrayContains(strArray, "Max Muster") ' Wahr Debug.Print ArrayContains(strArray, "Maxine Musterfrau") ' Falsch End Sub

Elemente in einem (String-)Array sortieren

Da die einzelnen Elemente in einem Array einen festen Platz haben, erfolgt die Sortierung über ein wiederholtes Austauschen einzelner Elemente mithilfe eines Zwischenspeichers (der Variablen strTemp). Wenn festgestellt wird, dass ein Element weiter hinten einsortiert sein sollte, wird der Wert des nächsten Elementes im Zwischenspeicher gelagert, das Element um eine Stelle nach hinten kopiert, und dann der gespeicherte Wert in das Original-Element übernommen. Dies wird so lange wiederholt, bis keine weiteren Verschiebungen mehr nötig waren (blnWasSorted bleibt wahr). Public Function SortStringArray(ByRef strArray() As String) As String() ' created 2016-02-22 p.wania Dim strTemp As String Dim strSorted() As String Dim lngIndex As Long Dim blnWasSorted As Boolean ' Da das Original-Array als Referenz übergeben werden muss, ' sollte in einem neuen Array sortiert werden, ' damit das Original erhalten bleibt ReDim strSorted(LBound(strArray) To UBound(strArray)) strSorted = strArray Do blnWasSorted = True For lngIndex = LBound(strSorted) To UBound(strSorted) - 1 If strSorted(lngIndex) > strSorted(lngIndex + 1) Then blnWasSorted = False strTemp = strSorted(lngIndex) strSorted(lngIndex) = strSorted(lngIndex + 1) strSorted(lngIndex + 1) = strTemp End If Next lngIndex Loop Until blnWasSorted = True SortStringArray = strSorted End Function

Arrays zusammenführen (Append)

Function AppendArrays(ByRef strArray01() As String, ByRef strArray02() As String) As String() Dim lngIndex As Long, lngTargetIndex As Long lngTargetIndex = UBound(strArray01) ReDim Preserve strArray01(UBound(strArray01) + UBound(strArray02) - LBound(strArray02) + 1) For lngIndex = LBound(strArray02) To UBound(strArray02) lngTargetIndex = lngTargetIndex + 1 strArray01(lngTargetIndex) = strArray02(lngIndex) Next lngIndex AppendArrays = strArray01 ' Erstellt eine Kopie, denn strArray01 wurde per Referenz übernommen (ByRef), ' seine Originaldaten liegen also weiterhin in der aufrufenden Prozedur ' und dürfen entsprechend nicht gelöscht! End Function Sub TestAppendArrays() Dim strFirst() As String, strSecond() As String strFirst = LoadNames ReDim strSecond(1) strSecond(0) = "Katrin" strSecond(1) = "Stephanie" strFirst = AppendArrays(strFirst, strSecond) ' AppendArrays strFirst, strSecond End Sub