VBA-Arrays

In VBA, und Array ist eine einzelne Variable, die mehrere Werte enthalten kann. Stellen Sie sich ein Array wie einen Zellbereich 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-Kurzübersicht

Arrays

BeschreibungVBA-CodeSchaffenDim arr(1 bis 3) als Variante
arr(1) = „eins“
arr(2) = „zwei“
arr(3) = "drei"Aus Excel erstellenDim arr(1 bis 3) als Variante
Dim cell As Range, i As Integer
i = LBound(arr)
Für jede Zelle im Bereich ("A1: A3")
ich = ich + 1
arr(i) = cell.value
Nächste ZelleAlle Artikel lesenDim ich als Long
Für i = LBound(arr) Zu UBound(arr)
MsgBox arr(i)
Als nächstesLöschenarr löschenArray zu StringDim sName als String
sName = Join(arr, „:“)Größe vergrößernReDim Preserve arr(0 bis 100)Wert einstellenarr(1) = 22

VBA-Array-Schnellbeispiele

Schauen wir uns ein vollständiges Beispiel an, bevor wir in die Einzelheiten eintauchen:

12345678910

Sub-ArrayBeispiel()Dim strNames(1 bis 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"msgbox strNames(3)End Sub

Hier haben wir das eindimensionale String-Array erstellt: strNames mit der Größe vier (kann vier Werte aufnehmen) und die vier Werte zugewiesen. Zuletzt zeigen wir den 3. Wert in einer Message Box an.

In diesem Fall ist der Vorteil der Verwendung eines Arrays gering: Statt vier ist nur eine Variablendeklaration erforderlich.

Schauen wir uns jedoch ein Beispiel an, das die wahre Leistung eines Arrays zeigt:

12345678

Sub-ArrayBeispiel2()Dim strNames(1 To 60000) As StringDim ich so langFür i = 1 bis 60000strNames(i) = Zellen(i, 1).WertAls nächstesEnd Sub

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

Vorteile des Arrays? - Geschwindigkeit!

Sie könnten an Arrays denken, die Excel-Arbeitsblättern ähneln:

• 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 Bsp. cell(1,4).value = „Zeile 1, Spalte 4“
  • Array Bsp. arrVar(1,4) = „Zeile 1, Spalte 4“

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

Das Lesen / Schreiben in Excel-Zellen ist ein langsamer Prozess. Das Arbeiten mit Arrays ist viel schneller!

Erstellen / Deklarieren eines Arrays (Dim)

Hinweis: Arrays können mehrere „Dimensionen“ haben. Der Einfachheit halber arbeiten wir zunächst nur mit eindimensionalen Arrays. Später in diesem Tutorial werden wir Ihnen mehrdimensionale Arrays vorstellen.

Statisches Array

Statische Arrays sind Arrays, deren Größe nicht geändert werden kann. Umgekehrt, Dynamische Arrays kann die Größe ändern. Sie werden etwas anders deklariert. Betrachten wir zunächst statische Arrays.

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

Das Deklarieren einer statischen Array-Variablen ist dem Deklarieren einer regulären Variablen sehr ähnlich, außer 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:

123456789101112

Sub StaticArray1()'Erzeugt ein Array mit den Positionen 1,2,3,4Dim arrDemo1(1 bis 4) als String'Erzeugt ein Array mit den Positionen 4,5,6,7Dim arrDemo2 (4 bis 7) so lang'Erzeugt Array mit den Positionen 0,1,2,3Dim arrDemo3(0 bis 3) so langEnd Sub

Oder Sie können nur die Array-Größe eingeben:

123456

Sub StaticArray2()'Erzeugt Array mit den Positionen 0,1,2,3Dim arrDemo1(3) As StringEnd Sub

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

Sie können erklären Option Basis 1 oben in Ihrem Modul, sodass das Array stattdessen an Position 1 beginnt:

12345678

Option Basis 1Sub StaticArray3()'Erzeugt ein Array mit den Positionen 1,2,3Dim arrDemo1(3) As StringEnd Sub

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

Sind Sie es leid, nach VBA-Codebeispielen zu suchen? Probieren Sie AutoMacro aus!

Dynamisches Array

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

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

Varianten-Arrays

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

1	Dim arrVar() als Variante

Mit einem Variante Array, Sie müssen die Array-Größe nicht definieren. Die Größe wird automatisch angepasst. Denken Sie daran, dass das Array mit Position 0 beginnt (es sei denn, Sie fügen Option Base 1 oben in Ihr Modul hinzu).

12345678910111213

UntervarianteArray()Dim arrVar() als Variante'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")'Ausgangsposition 4 ("5a")MsgBox arrVar(4)End Sub

Dynamische Arrays ohne Varianten

Bei nicht-varianten Arrays müssen Sie die Array-Größe definieren, bevor Sie dem Array Werte zuweisen. Der Prozess zum Erstellen des Arrays ist jedoch etwas anders:

1234567

Unter DynamicArray1()Dim arrDemo1() As String'Größe des Arrays mit den Positionen 1,2,3,4ReDim arrDemo1(1 bis 4)End Sub

Zuerst deklarieren Sie das Array, ähnlich dem statischen Array, außer dass Sie die Array-Größe weglassen:

1	Dim arrDemo1() As String

Wenn Sie nun die Array-Größe einstellen möchten, verwenden Sie die ReDim Befehl zur Größe des Arrays:

12	'Größe des Arrays mit den Positionen 1,2,3,4ReDim arrDemo1(1 bis 4)

ReDim ändert die Größe des Arrays. Lesen Sie unten den Unterschied zwischen ReDim und ReDim Preserve.

ReDim vs. ReDim Preserve

Wenn Sie das verwenden ReDim Befehl löschen Sie alle vorhandenen Werte aus dem Array. Stattdessen können Sie verwenden ReDim Preserve Array-Werte beizubehalten:

12	'Größe des Arrays mit den Positionen 1,2,3,4 ändern (Bestehende Werte beibehalten)ReDim Preserve arrDemo1(1 bis 4)

Arrays vereinfacht deklarieren

Sie könnten sich überfordert fühlen, nachdem Sie alles oben gelesen haben. Der Einfachheit halber werden wir für den Rest des Artikels hauptsächlich mit statischen Arrays arbeiten.

Array-Werte festlegen

Das Einstellen von Array-Werten ist sehr einfach.

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

12345678

Sub-ArrayBeispiel()Dim strNames(1 bis 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"End Sub

Bei einem Variant Array können Sie das gesamte Array mit einer Zeile definieren (nur sinnvoll bei kleinen Arrays):

123456

Sub-ArrayExample_1Line()Dim strNames() als VariantestrNames = Array("Shelly", "Steve", "Neema", "Jose")End Sub

Wenn Sie versuchen, einen Wert für einen nicht vorhandenen Array-Speicherort zu definieren, erhalten Sie einen Fehler "Tiefgestellt außerhalb des Bereichs":

1	strNames(5) = "Shannon"

Im Abschnitt „Bereich zu Array zuweisen“ zeigen wir Ihnen weiter unten, wie Sie mithilfe einer Schleife schnell eine große Anzahl von Werten an Arrays zuweisen können.

Array-Wert abrufen

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

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

VBA-Programmierung | Code Generator arbeitet für Sie!

Bereich dem Array zuweisen

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

12345678

UnterbereichToArray()Dim strNames(1 To 60000) As StringDim ich so langFür i = 1 bis 60000strNames(i) = Zellen(i, 1).WertAls nächstesEnd Sub

Dies durchläuft die Zellen A1:A60000 und weist die Zellenwerte dem Array zu.

Ausgabe-Array zu Bereich

Oder Sie können eine Schleife verwenden, um einem Bereich ein Array zuzuweisen:

123	Für i = 1 bis 60000Zellen(i, 1).Wert = strNames(i)Als nächstes

Dies geschieht umgekehrt: Weisen Sie den Zellen A1: A60000 Array-Werte zu

2D / mehrdimensionale Arrays

Bisher 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 ist wie eine ganze Arbeitsmappe, die mehrere Blätter mit jeweils mehreren Zeilen und Spalten enthält (Sie könnte man sich ein 3D-Array auch wie einen Zauberwürfel vorstellen).

Beispiele für mehrdimensionale Arrays

Lassen Sie uns nun Beispiele für die Arbeit mit Arrays unterschiedlicher Dimensionen demonstrieren.

VBA-Programmierung | Code Generator arbeitet für Sie!

1D-Array-Beispiel

Dieses Verfahren kombiniert die vorherigen Array-Beispiele in einem Verfahren und zeigt, wie Sie Arrays in der Praxis verwenden können.

1234567891011121314

Sub-ArrayEx_1d()Dim strNames(1 To 60000) As StringDim ich so lang'Werte dem Array zuweisenFür i = 1 bis 60000strNames(i) = Zellen(i, 1).WertAls nächstes'Array-Werte in Bereich ausgebenFür i = 1 bis 60000Sheets("Ausgabe").Cells(i, 1).Value = strNames(i)Als nächstesEnd Sub

2D-Array-Beispiel

Dieses Verfahren enthält ein Beispiel für ein 2D-Array:

123456789101112131415161718

Sub-ArrayEx_2d()Dim strNames(1 bis 60000, 1 bis 10) als StringDim i As Long, j As Long'Werte dem Array zuweisenFür i = 1 bis 60000Für j = 1 bis 10strNames(i, j) = Zellen(i, j).WertNächstes jAls nächstes'Array-Werte in Bereich ausgebenFür i = 1 bis 60000Für j = 1 bis 10Sheets("Ausgabe").Cells(i, j).Wert = strNames(i, j)Nächstes jAls nächstesEnd Sub

3D-Array-Beispiel

Dieses Verfahren enthält ein Beispiel für ein 3D-Array zum Arbeiten mit mehreren Blättern:

12345678910111213141516171819202122

Sub-ArrayEx_3d()Dim strNames(1 bis 60000, 1 bis 10, 1 bis 3) als StringDim i As Long, j As Long, k As Long'Werte dem Array zuweisenFür k = 1 bis 3Für i = 1 bis 60000Für j = 1 bis 10strNames(i, j, k) = Sheets("Sheet" & k).Cells(i, j).WertNächstes jAls nächstesNächstes k'Array-Werte in Bereich ausgebenFür k = 1 bis 3Für i = 1 bis 60000Für j = 1 bis 10Sheets("Ausgabe" & k).Cells(i, j).Wert = strNames(i, j, k)Nächstes jAls nächstesNächstes kEnd Sub

Array-Länge / -Größe

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

VBA-Programmierung | Code Generator arbeitet für Sie!

UBound- und LBound-Funktionen

Der erste Schritt, um die Länge / Größe eines Arrays zu erhalten, besteht darin, die Funktionen UBound und LBound zu verwenden, um die Ober- und Untergrenzen des Arrays zu ermitteln:

123456

Unter UBoundLBound()Dim strNames(1 bis 4) als StringMsgBox UBound(strNames)MsgBox LBound(strNames)End Sub

Durch Subtrahieren der beiden (und Addieren von 1) erhalten Sie die Länge:

1	GetArrLength = UBound(strNames) - LBound(strNames) + 1

Array-Längenfunktion

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

1234567

Öffentliche Funktion GetArrLength(a As Variant) As LongWenn IstLeer(a) DannGetArrLength = 0AndersGetArrLength = UBound(a) - LBound(a) + 1Ende WennEndfunktion

Müssen Sie die Größe eines 2D-Arrays berechnen? Schauen Sie sich unser Tutorial an: Berechnen Sie die Größe des Arrays.

Loop-Through-Array

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

12345678910111213

Sub-ArrayExample_Loop1()Dim strNames(1 bis 4) als StringDim ich so langstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Für i = 1 bis 4MsgBox strNames(i)Als nächstesEnd Sub

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

12345678910111213

Sub-ArrayExample_Loop2()Dim strNames(1 bis 4) als StringDim ich so langstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Für i = LBound(strNames) Zu UBound(strNames)MsgBox strNames(i)Als nächstesEnd Sub

Für jede Array-Schleife

Die zweite Methode ist mit einer For Each-Schleife. Dies durchläuft jedes Element im Array:

12345678910111213

Sub-ArrayExample_Loop3()Dim strNames(1 bis 4) als StringDim ArtikelstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"Für jedes Element in strNamesMsgBox-ElementNächstes ObjektEnd Sub

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

VBA-Programmierung | Code Generator arbeitet für Sie!

2D-Array durchschleifen

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

1234567891011121314151617181920

Sub-ArrayExample_Loop4()Dim strNames(1 bis 4, 1 bis 2) als StringDim i As Long, j As LongstrNames(1, 1) = "Shelly"strNames(2, 1) = "Steve"strNames(3, 1) = "Neema"strNames(4, 1) = "José"strNames(1, 2) = "Shelby"strNames(2, 2) = "Steven"strNames(3, 2) = "Nemo"strNames(4, 2) = "Jesse"Für j = LBound(strNames, 2) Zu UBound(strNames, 2)Für i = LBound(strNames, 1) Zu UBound(strNames, 1)MsgBox strNames(i, j)Als nächstesNächstes jEnd Sub

Andere Array-Aufgaben

Array löschen

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

1	strNames löschen

Anwendungsbeispiel:

12345678910

Sub-ArrayBeispiel()Dim strNames(1 bis 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"strNames löschenEnd Sub

Alternativ können Sie das Array auch neu dimmen, um die Größe zu ändern und einen Teil des Arrays zu löschen:

1	ReDim-StrNames(1 bis 2)

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

Zählfeld

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

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

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

123456789101112131415

UnterarrayLoopandCount()Dim strNames(1 bis 4) als StringDim i As Long, n As LongstrNames(1) = "Shelly"strNames(2) = "Steve"Für i = LBound(strNames) Zu UBound(strNames)Wenn strNames(i) "" Thenn = n + 1Ende WennAls nächstesMsgBox n & " Nicht leere Werte gefunden."End Sub

VBA-Programmierung | Code Generator arbeitet für Sie!

Duplikate entfernen

Irgendwann möchten Sie vielleicht Duplikate aus einem Array entfernen. Leider hat VBA dafür keine eingebaute Funktion. Wir haben jedoch eine Funktion zum Entfernen von Duplikaten aus einem Array geschrieben (es ist zu lang, um es in dieses Tutorial aufzunehmen, aber besuchen Sie den Link, um mehr zu erfahren).

Filter

Mit der VBA-Filterfunktion können Sie ein Array filtern. Dies geschieht, indem ein neues Array nur mit den gefilterten Werten erstellt wird. Unten ist ein kurzes Beispiel, aber lesen Sie den Artikel für weitere Beispiele für unterschiedliche Bedürfnisse.

1234567891011121314

Unterfilter_Match()'Array definierenDim strNames als VariantestrNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filter-ArrayDim strSubNames als VariantestrSubNames = Filter(strNames, "Smith")'Gefiltertes Array zählenMsgBox "Gefunden" & UBound(strSubNames) - LBound(strSubNames) + 1 & " Namen."End Sub

IsArray-Funktion

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

123456789101112

Unter IsArrayEx()'Erzeugt Array mit den Positionen 1,2,3Dim arrDemo1(3) As String'Erzeugt eine reguläre String-VariableDim str als StringMsgBox IsArray(arrDemo1)MsgBox IsArray(str)End Sub

Array beitreten

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

123456789101112

Sub-Array_Join()Dim strNames(1 bis 4) als StringDim joinNames als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "José"joinNames = Join(strNames, ", ")MsgBox joinNamesEnd Sub

VBA-Programmierung | Code Generator arbeitet für Sie!

String in Array aufteilen

Die VBA-Split-Funktion teilt eine Textzeichenfolge in ein Array mit Werten aus der ursprünglichen Zeichenfolge auf. Schauen wir uns ein Beispiel an:

123456789

Sub-Array_Split()Dim Names() As StringDim JoinNames As StringjoinNames = "Shelly,Steve,Nema,Jose"Namen = Split(joinedNames, ",")MsgBox-Namen(1)End Sub

Hier teilen wir diese Textzeichenfolge „Shelly,Steve,Nema,Jose“ in ein Array (Größe 4) mit einem Komma-Trennzeichen (“,).

Konst.-Array

Eine Anordnung kann nicht in VBA als Konstante deklariert werden. Sie können dies jedoch umgehen, indem Sie eine Funktion erstellen, die als Array verwendet wird:

123456789

' ConstantArray definierenFunktion ConstantArray()ConstantArray = Array(4, 12, 21, 100, 5)Endfunktion' ConstantArray-Wert abrufenSub RetrieveValues()MsgBox ConstantArray(3)End Sub

Array kopieren

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

12345678910111213141516171819

Unter CopyArray()Dim Arr1(1 bis 100) solangeDim Arr2(1 bis 100) solangeDim ich so lang'Array1 erstellenFür i = 1 bis 100Arr1(i) = iAls nächstes'Array1 nach Array2 kopierenFür i = 1 bis 100Arr2(i) = Arr1(i)Als nächstesMsgBox Arr2(74)End Sub

Transponieren

Es gibt keine integrierte 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.

VBA-Programmierung | Code Generator arbeitet für Sie!

Funktions-Return-Array

Eine häufige Frage von VBA-Entwicklern ist, wie man eine Funktion erstellt, die ein Array zurückgibt. Ich denke, die meisten Schwierigkeiten werden durch die Verwendung von Variant Arrays gelöst. Wir haben einen Artikel zum Thema geschrieben: VBA Function Return Array.

Verwenden von Arrays in Access VBA

Die meisten der obigen Array-Beispiele funktionieren in Access VBA genauso wie in Excel VBA. Der einzige Hauptunterschied besteht darin, dass Sie beim Auffüllen eines Arrays mit Access-Daten das RecordSet-Objekt anstelle des Range-Objekts durchlaufen müssen.

1234567891011121314151617181920212223

Sub RangeToArrayAccess()Bei Fehler Fortsetzen als nächstesDim strNames() As StringDim ich so langDim iCount As LongDim dbs als DatenbankZuerst als Recordset dimmenSetze dbs = CurrentDbSet rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Mit rst.Zuletzt verschieben.Zuerst verschiebeniCount = .RecordCountReDim strNames(1 bis iCount)Für i = 1 zu iCountstrNames(i) = rst.Fields("ClientName").Nächstes verschiebenAls nächstesEnde mitzuerst.SchließenSetze zuerst = NichtsSetze dbs = NichtsEnd Sub

Array-Tutorials
Array Mega-Guide	Jawohl
Array-Größe abrufen
Array löschen
Filter-Array
Array transponieren
Funktions-Return-Array
Duplikate entfernen