VBA – Arrays

In VBA ist ein Array eine einzelne Variable, die mehrere Werte enthalten kann. Stellen Sie sich ein Array wie einen Bereich von Zellen vor. Jede Zelle kann einen Wert speichern. Arrays können eindimensional (denken Sie an eine einzelne Spalte), zweidimensional (denken Sie an mehrere Zeilen und Spalten) oder mehrdimensional sein. Auf Array-Werte kann über ihre Position (Indexnummer) innerhalb des Arrays zugegriffen werden.

VBA-Array-Schnellübersicht

Arrays

VBA – Array-Schnellbeispiele

Schauen wir uns zunächst ein vollständiges Beispiel an, bevor wir uns mit den Einzelheiten befassen:

Sub ArrayBeispiel()
    Dim strNamen(1 To 4) As String

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"

    MsgBox strNamen(3)
End Sub

Hier haben wir ein eindimensionales String-Array namens strNamen mit der Größe vier (kann vier Werte aufnehmen) erzeugt und die vier Werte zugewiesen. Zuletzt zeigen wir den 3. Wert in einer Meldungsbox an.

In diesem Fall ist der Vorteil der Verwendung eines Arrays gering. Es ist nur eine Variablendeklaration anstelle von vier erforderlich.

Schauen wir uns jedoch ein Beispiel an, das die wahre Stärke eines Arrays zeigt:

Sub ArrayBeispiel2()
    Dim strNamen(1 To 60000) As String
    Dim i As Long

    For i = 1 To 60000
        strNamen(i) = Cells(i, 1).Value
    Next i
End Sub

Hier haben wir ein Array erstellt, das 60.000 Werte aufnehmen kann, und wir haben das Array schnell aus Spalte A eines Arbeitsblatts befüllt.

Vorteile eines Arrays? – Schnelligkeit!

Sie können sich Arrays ähnlich wie Excel-Arbeitsblätter vorstellen:

• Jede Zelle (oder jedes Element in einem Array) kann einen eigenen Wert enthalten
• Auf jede Zelle (oder jedes Element in einem Array) kann über ihre Zeilen- und Spaltenposition zugegriffen werden.
  • Arbeitsblatt-Beispiel: cells(1,4).value = „Zeile 1, Spalte 4“
  • Array-Beispiel: arrVar(1,4) = „Zeile 1, Spalte 4“

Warum sollte man sich also mit Arrays beschäftigen? Warum nicht einfach Werte direkt in Zellen in Excel lesen und schreiben? Ein Wort: Geschwindigkeit!

Das Lesen und Schreiben in Excel-Zellen ist ein langsamer Prozess. Die Arbeit mit Arrays ist viel schneller!

Erstellen / Deklarieren eines Arrays (Dim)

Hinweis: Arrays können mehrere „Dimensionen“ haben. Um die Dinge einfach zu halten, werden wir zunächst nur mit eindimensionalen Arrays arbeiten. Später im Tutorial werden wir Sie in mehrdimensionale Arrays einführen.

Statisches Array

Statische Arrays sind Arrays, die ihre Größe nicht ändern können. Im Gegensatz dazu können dynamische Arrays ihre Größe ändern. Sie werden etwas anders deklariert. Schauen wir uns zunächst statische Arrays an.

Hinweis: Wenn sich die Größe Ihres Arrays nicht ändern soll, verwenden Sie ein statisches Array.

Die Deklaration einer statischen Array-Variablen ist der Deklaration einer regulären Variablen sehr ähnlich, außer der Tatsache, dass Sie die Größe des Arrays definieren müssen. Es gibt verschiedene Möglichkeiten, die Größe eines Arrays festzulegen.

Sie können die Start- und Endposition eines Arrays explizit deklarieren:

Sub StatischesArray1()

    'Ein Array mit den Positionen 1,2,3,4 erzeugen
    Dim arrDemo1(1 To 4) As String
    
    'Ein Array mit den Positionen 4,5,6,7 erzeugen
    Dim arrDemo2(4 To 7) As Long
    
    'Ein Array mit den Positionen 0,1,2,3 erzeugen
    Dim arrDemo3(0 To 3) As Long

End Sub

Sie können auch nur die Größe des Arrays eingeben:

Sub StaticArray2()

    'Ein Array mit den Positionen 0,1,2,3 erzeugen
    Dim arrDemo1(3) As String

End Sub

Wichtig! Beachten Sie, dass Arrays standardmäßig an der Position 0 beginnen. Dim arrDemo1(3) erstellt also ein Array mit den Positionen 0,1,2,3.

Sie können Option Base 1 am Anfang Ihres Moduls deklarieren, damit das Array stattdessen an Position 1 beginnt:

Option Base 1

Sub StaticArray3()

    'Ein Array mit den Positionen 1,2,3 erzeugen
    Dim arrDemo1(3) As String

End Sub

Ich finde es jedoch viel einfacher (und weniger verwirrend), einfach die Start- und Endpositionen von Arrays explizit zu deklarieren.

Dynamisches Array

Dynamische Arrays sind Arrays, deren Größe geändert werden kann bzw. nicht definiert werden muss.

Es gibt zwei Möglichkeiten, ein dynamisches Array zu deklarieren.

Variant-Arrays

Die erste Möglichkeit, ein dynamisches Array zu deklarieren, besteht darin, das Array auf den Typ Variant zu setzen.

Dim arrVar() As Variant

Bei einem Variant-Array müssen Sie die Größe des Arrays nicht definieren. Die Größe wird automatisch angepasst. Denken Sie nur daran, dass das Array an der Position 0 beginnt (es sei denn, Sie fügen Option Base 1 am Anfang Ihres Moduls hinzu).

Sub VariantArray()
    Dim arrVar() As Variant
    
    'Werte definieren (Größe = 0,1,2,3)
    arrVar = Array(1, 2, 3, 4)
    
    'Werte ändern (Größe = 0,1,2,3,4)
    arrVar = Array("1a", "2a", "3a", "4a", "5a")

    'Ausgabe Position 4 ("5a")
    MsgBox arrVar(4)

End Sub

Nicht-variierende (engl. non-variant) dynamische Arrays

Bei nicht-variierenden Arrays müssen Sie die Arraygröße definieren, bevor Sie dem Array Werte zuweisen. Der Prozess zur Erzeugung des Arrays ist jedoch etwas anders:

Sub DynamicArray1()
    Dim arrDemo1() As String

    'Die Größe des Arrays mit den Positionen 1,2,3,4 ändern
    ReDim arrDemo1(1 To 4)
    
End Sub

Zuerst deklarieren Sie das Array, ähnlich wie beim statischen Array, nur dass Sie die Arraygröße weglassen:

Dim arrDemo1() As String

Wenn Sie nun die Größe des Arrays festlegen wollen, verwenden Sie die ReDim-Anweisung, die in der Regel verwendet wird, um das Array zu ändern:

'Die Größe des Arrays wird mit den Positionen 1,2,3,4 definiert
ReDim arrDemo1(1 To 4)

ReDim ändert die Größe des Arrays. Im Folgenden wird der Unterschied zwischen ReDim und ReDim Preserve erläutert.

ReDim vs. ReDim Preserve

Wenn Sie den Befehl ReDim verwenden, löschen Sie alle vorhandenen Werte aus dem Array. Stattdessen können Sie ReDim Preserve verwenden, damit die Array-Werte erhalten bleiben:

'Die Größe des Arrays mit den Positionen 1,2,3,4 ändern (Beibehaltung der vorhandenen Werte)
ReDim Preserve arrDemo1(1 To 4)

Deklarieren von Arrays (vereinfacht)

Nachdem Sie Alles oben gelesen haben, fühlen Sie sich vielleicht überfordert. Um die Dinge einfach zu halten, werden wir für den Rest des Artikels hauptsächlich mit statischen Arrays arbeiten.

Array-Werte setzen

Das Setzen von Array-Werten ist sehr einfach.

Bei einem statischen Array müssen Sie jede Position des Arrays einzeln definieren:

Sub ArrayBeispiel()
    Dim strNamen(1 To 4) As String

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"
End Sub

Mit einem Variant-Array können Sie das gesamte Array mit einer Zeile definieren (praktisch nur für kleine Arrays):

Sub ArrayBeispiel_1Zeile()
    Dim strNamen() As Variant

    strNamen = Array("Shelly", "Steve", "Neema", "Jose")

End Sub

Wenn Sie versuchen, einen Wert für eine Array-Stelle zu definieren, die nicht vorhanden ist, erhalten Sie einen Fehler „Subscript Out of Range“ (Deutsch: Index außerhalb des gültigen Bereichs):

strNamen(5) = "Shannon"

Im folgenden Abschnitt „Bereich einem Array zuweisen“ zeigen wir Ihnen, wie Sie eine Schleife verwenden können, um Arrays eine große Anzahl von Werten schnell zuzuweisen.

Array-Werte Abrufen

Sie können Array-Werte auf die gleiche Weise abrufen. Im folgenden Beispiel werden wir Array-Werte in Zellen schreiben:

   Range("A1").Value = strNamen(1)
    Range("A2").Value = strNamen(2)
    Range("A3").Value = strNamen(3)
    Range("A4").Value = strNamen(4)

Einen Bereich einem Array zuweisen

Um einen Bereich einem Array zuzuweisen, können Sie eine Schleife verwenden:

Sub RangeToArray()
    Dim strNamen(1 To 60000) As String
    Dim i As Long

    For i = 1 To 60000
        strNamen(i) = Cells(i, 1).Value
    Next i
End Sub

Damit werden die Zellen A1:A60000 in einer Schleife durchlaufen und die Zellwerte dem Array zugewiesen.

Array in Bereich ausgeben

Sie können auch eine Schleife verwenden, um Array-Werte in einen Bereich zu schreiben:

   For i = 1 To 60000
        Cells(i, 1).Value = strNamen(i)
    Next i

Dies bewirkt den umgekehrten Vorgang (Zuweisung von Array-Werten zu den Zellen A1:A60000).

2D / Mehrdimensionale Arrays

Bis jetzt haben wir ausschließlich mit eindimensionalen (1D) Arrays gearbeitet. Arrays können jedoch bis zu 32 Dimensionen haben.

Stellen Sie sich ein 1D-Array wie eine einzelne Zeile oder Spalte von Excel-Zellen vor, ein 2D-Array wie ein ganzes Excel-Arbeitsblatt mit mehreren Zeilen und Spalten und ein 3D-Array wie eine ganze Arbeitsmappe, die mehrere Blätter mit jeweils mehreren Zeilen und Spalten enthält (Sie können sich ein 3D-Array auch wie einen Zauberwürfel vorstellen).

Beispiele für mehrdimensionale Arrays

Nun wollen wir Beispiele für die Arbeit mit Arrays verschiedener Dimensionen zeigen.

1D-Array-Beispiel

Diese Prozedur fasst die vorherigen Array-Beispiele in einer Prozedur zusammen und zeigt, wie Sie Arrays in der Praxis verwenden können.

Sub ArrayBeispiel_1d()
    Dim strNamen(1 To 60000) As String
    Dim i As Long
 
    'Dem Array Werte zuweisen
    For i = 1 To 60000
        strNamen(i) = Cells(i, 1).Value
    Next i
    
    'Array-Werte in den Bereich ausgeben
    For i = 1 To 60000
        Sheets("Ausgabe").Cells(i, 1).Value = strNamen(i)
    Next i
End Sub

2D-Array-Beispiel

Diese Prozedur enthält ein Beispiel für ein 2D-Array:

Sub ArrayBeispiel_2d()
    Dim strNamen(1 To 60000, 1 To 10) As String
    Dim i As Long, j As Long
 
    'Dem Array Werte zuweisen
    For i = 1 To 60000
        For j = 1 To 10
            strNamen(i, j) = Cells(i, j).Value
        Next j
    Next i
    
    'Array-Werte in den Bereich ausgeben
    For i = 1 To 60000
        For j = 1 To 10
            Sheets("Ausgabe").Cells(i, j).Value = strNamen(i, j)
        Next j
    Next i
End Sub

3D-Array-Beispiel

Diese Prozedur enthält ein Beispiel für ein 3D-Array für die Arbeit mit mehreren Blättern:

Sub ArrayBeispiel_3d()
    Dim strNamen(1 To 60000, 1 To 10, 1 To 3) As String
    Dim i As Long, j As Long, k As Long
 
    'Dem Array Werte zuweisen
    For k = 1 To 3
        For i = 1 To 60000
            For j = 1 To 10
                strNamen(i, j, k) = Sheets("Sheet" & k).Cells(i, j).Value
            Next j
        Next i
    Next k
    
    'Array-Werte in den Bereich ausgeben
    For k = 1 To 3
        For i = 1 To 60000
            For j = 1 To 10
                Sheets("Output" & k).Cells(i, j).Value = strNamen(i, j, k)
            Next j
        Next i
    Next k
End Sub

Array Länge/Größe

Bisher haben wir Ihnen die verschiedenen Arten von Arrays vorgestellt und Ihnen beigebracht, wie Sie Arrays deklarieren und Array-Werte abrufen bzw. setzen können. Als Nächstes werden wir uns auf andere notwendige Themen für die Arbeit mit Arrays konzentrieren.

Die Funktionen UBound und LBound

Der erste Schritt, um die Länge/Größe eines Arrays zu ermitteln, ist die Verwendung der Funktionen UBound und LBound, um die oberen und unteren Grenzen des Arrays zu ermitteln:

Sub UBound_LBound()
    Dim strNamen(1 To 4) As String
    
    MsgBox UBound(strNamen)
    MsgBox LBound(strNamen)
End Sub

Durch Subtraktion der beiden (und Addition von 1) erhalten Sie die Länge:

ArrLaengeErmitteln = UBound(strNamen) - LBound(strNamen) + 1

Funktion zur Ermittlung der Array-Länge

Hier ist eine Funktion, um die Länge eines eindimensionalen Arrays zu ermitteln:

Public Function ArrLaengeErmitteln(a As Variant) As Long
   If IsEmpty(a) Then
      ArrLaengeErmitteln = 0
   Else
      ArrLaengeErmitteln = UBound(a) - LBound(a) + 1
   End If
End Function

Möchten Sie die Größe eines 2D-Arrays berechnen? Sehen Sie sich unser Tutorial an: Berechne Größe eines Arrays.

Array mit einer Schleife durchlaufen

Es gibt zwei Möglichkeiten, ein Array mit einer Schleife zu durchlaufen. Die erste Schleife durchläuft die Ganzzahlen, die den Nummernpositionen des Arrays entsprechen. Wenn Sie die Größe des Arrays kennen, können Sie sie direkt angeben:

Sub ArrayBeispiel_Schleife_1()
    Dim strNamen(1 To 4) As String
    Dim i As Long

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"
    
    For i = 1 To 4
        MsgBox strNamen(i)
    Next i
End Sub

Wenn Sie jedoch die Größe des Arrays nicht kennen (wenn das Array dynamisch ist), können Sie die Funktionen LBound und UBound aus dem vorherigen Abschnitt verwenden:

Sub ArrayBeispiel_Schleife_2()
    Dim strNamen(1 To 4) As String
    Dim i As Long

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"
    
    For i = LBound(strNamen) To UBound(strNamen)
        MsgBox strNamen(i)
    Next i
End Sub

For-Each-Schleife bei Arrays

Die zweite Methode besteht aus einer For-Each-Schleife. Diese Schleife durchläuft jedes Element im Array:

Sub ArrayBeispiel_Schleife_3()
    Dim strNamen(1 To 4) As String
    Dim Item

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"
    
    For Each Item In strNamen
        MsgBox Item
    Next Item
End Sub

Die For-Each-Schleife funktioniert mit mehrdimensionalen Arrays zusätzlich zu eindimensionalen Arrays.

2D-Array mit einer Schleife durchlaufen

Sie können auch die Funktionen UBound und LBound verwenden, um ein mehrdimensionales Array mit einer Schleife zu durchlaufen. In diesem Beispiel werden wir eine Schleife bei einem 2D-Array verwenden. Beachten Sie, dass Sie mit den Funktionen UBound und LBound angeben können, in welcher Dimension des Arrays die obere und untere Grenze zu finden ist (1 für die erste Dimension, 2 für die zweite Dimension).

Sub ArrayBeispiel_Schleife_4()
    Dim strNamen(1 To 4, 1 To 2) As String
    Dim i As Long, j As Long

    strNamen(1, 1) = "Shelly"
    strNamen(2, 1) = "Steve"
    strNamen(3, 1) = "Neema"
    strNamen(4, 1) = "Jose"
    
    strNamen(1, 2) = "Shelby"
    strNamen(2, 2) = "Steven"
    strNamen(3, 2) = "Nemo"
    strNamen(4, 2) = "Jesse"
    
    For j = LBound(strNamen, 2) To UBound(strNamen, 2)
        For i = LBound(strNamen, 1) To UBound(strNamen, 1)
            MsgBox strNamen(i, j)
        Next i
    Next j
End Sub

Andere Array-Aufgaben

Array löschen

Um ein ganzes Array zu löschen, verwenden Sie die Erase-Anweisung:

Erase strNamen

Verwendungsbeispiel:

Sub ArrayBeispiel()
    Dim strNamen(1 To 4) As String

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"

    Erase StrNamen
End Sub

Alternativ können Sie das Array auch mit ReDim verkleinern, um einen Teil des Arrays zu löschen:

ReDim strNamen(1 To 2)

Dies ändert die Größe des Arrays auf 2 und löscht die Positionen 3 und 4.

Array-Positionen zählen

Sie können die Anzahl der Positionen in jeder Dimension eines Arrays mithilfe der Funktionen UBound und LBound (siehe oben) zählen.

Sie können auch die Anzahl der eingegebenen Positionen (oder der Positionen, die bestimmte Kriterien erfüllen) zählen, indem Sie das Array in einer Schleife durchlaufen.

In diesem Beispiel wird ein Array von Objekten in einer Schleife durchlaufen und dabei die Anzahl der nicht leeren Zeichenfolgen gezählt:

Sub ArrayDurchlaufenUndZaehlen()
    Dim strNamen(1 To 4) As String
    Dim i As Long, n As Long

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    
    For i = LBound(strNamen) To UBound(strNamen)
        If strNamen(i) <> "" Then
            n = n + 1
        End If
    Next i
    
    MsgBox n & " nicht-leere Werte gefunden."
End Sub

Duplikate entfernen

Es kann vorkommen, dass Sie Duplikate aus einem Array entfernen möchten. Leider gibt es in VBA keine eingebaute Funktion, die dies ermöglicht. Wir haben jedoch eine Funktion zum Entfernen von Duplikaten aus einem Array geschrieben. Sie ist zu lang, um in dieses Tutorial aufgenommen zu werden, aber besuchen Sie den Link, um mehr zu erfahren.

Filtern

Mit der VBA-Filterfunktion können Sie ein Array filtern. Dazu wird ein neues Array erstellt, das nur die gefilterten Werte enthält. Nachfolgend finden Sie ein kurzes Beispiel, aber lesen Sie unbedingt den Artikel für weitere Beispiele für verschiedene Bedarfe.

Sub Filter_Treffer()
 
    'Array definieren
    Dim strNamen As Variant
    strNamen = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")
 
    'Array filtern
    Dim strSubNamen As Variant
    strSubNamen = Filter(strNamen, "Smith")
    
    'Gefiltertes Array zählen
    MsgBox "Gefunden " & UBound(strSubNamen) - LBound(strSubNamen) + 1 & " Namen."
 
End Sub

IsArray-Funktion

Sie können mit der IsArray-Funktion testen, ob eine Variable ein Array ist:

Sub IsArray_Beispiel()

    'Ein Array mit den Positionen 1,2,3 erzeugen
    Dim arrDemo1(3) As String
    
    'Eine reguläre String-Variable erzeugen
    Dim str As String
    
    MsgBox IsArray(arrDemo1)
    MsgBox IsArray(str)

End Sub

Array-Elemente verbinden

Mit der Join-Funktion können Sie schnell ein ganzes Array „zusammenfügen“:

Sub Array_Verbinden()
    Dim strNamen(1 To 4) As String
    Dim joinNamen As String

    strNamen(1) = "Shelly"
    strNamen(2) = "Steve"
    strNamen(3) = "Neema"
    strNamen(4) = "Jose"
    
    joinNamen = Join(strNamen, ", ")
    MsgBox joinNamen
End Sub

String in Array aufteilen

Die VBA Split-Funktion teilt eine Textzeichenkette in ein Array auf, das die Werte der ursprünglichen Zeichenkette enthält. Schauen wir uns ein Beispiel an:

Sub Array_Aufteilen()
    Dim Namen() As String
    Dim joinedNamen As String
    
    joinedNamen = "Shelly,Steve,Nema,Jose"
    Namen = Split(joinedNamen, ",")

    MsgBox Namen(1)
End Sub

Hier teilen wir diese Textzeichenfolge „Shelly,Steve,Nema,Jose“ in ein Array (Größe 4) auf, indem wir ein Komma als Trennzeichen (,“) verwenden.

Konstantes Array

Ein Array kann in VBA NICHT als Konstante deklariert werden. Sie können dies jedoch umgehen, indem Sie eine Funktion erstellen, die Sie als Array verwenden können:

' KonstantesArray definieren
Function KonstantesArray()
    KonstantesArray = Array(4, 12, 21, 100, 5)
End Function

' KonstantesArray-Wert abrufen
Sub WerteAbrufen()
    MsgBox KonstantesArray(3)
End Sub

Array kopieren

Es gibt keine eingebaute Möglichkeit, ein Array mit VBA zu kopieren. Stattdessen müssen Sie eine Schleife verwenden, um die Werte eines Arrays einem anderen zuzuweisen.

Sub ArrayKopieren()

    Dim Arr1(1 To 100) As Long
    Dim Arr2(1 To 100) As Long
    Dim i As Long
    
    'Array1 erstellen
    For i = 1 To 100
        Arr1(i) = i
    Next i
    
    'Array1 nach Array2 kopieren
    For i = 1 To 100
        Arr2(i) = Arr1(i)
    Next i
    
    MsgBox Arr2(74)

End Sub

Transponieren

Es gibt keine eingebaute VBA-Funktion, mit der Sie ein Array transponieren können. Wir haben jedoch eine Funktion zum Transponieren eines 2D-Arrays geschrieben. Lesen Sie den Artikel, um mehr zu erfahren.

Array als Funktionsrückgabe

Eine häufige Frage, die sich VBA-Entwickler stellen, ist, wie man eine Funktion erstellt, die ein Array zurückgibt. Ich denke, die meisten Schwierigkeiten lassen sich durch die Verwendung von Variant-Arrays lösen. Wir haben einen Artikel zu diesem Thema geschrieben: VBA Function Return Array.

Verwendung von Arrays in Access-VBA

Die meisten der oben genannten Array-Beispiele funktionieren in Access-VBA genau so wie in Excel-VBA. Der einzige wesentliche Unterschied besteht darin, dass Sie eine Schleife durch das RecordSet-Objekt und nicht durch das Range-Objekt verwenden müssen, wenn Sie ein Array mit Access-Daten füllen möchten.

Sub BereichZuArray_Access()
   On Error Resume Next
   Dim strNamen() As String
   Dim i As Long
   Dim iAnzahl As Long
   Dim dbs As Database
   Dim rst As Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblKunden", dbOpenDynaset)
   With rst
      .MoveLast
      .MoveFirst
      iCount = .RecordCount
      ReDim strNamen(1 To iAnzahl)
      For i = 1 To iCount
         strNamen(i) = rst.Fields("Kundenname")
         .MoveNext
      Next i
   End With
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
End Sub

Array Tutorials
Array Mega-Guide	yes
Get Array Size
Clear Array
Filter Array
Transpose Array
Function Return Array
Remove Duplicates