Excel-VBA im Zusammenspiel mit Arrays

Bei einem Array handelt es sich um eine Tabelle, die im Arbeitsspeicher temporär angelegt wird und für bestimmte Aufgaben benutzt werden kann. Die Frage stellt sich hierbei, warum man so etwas überhaupt braucht, da Excel ebenso Tabellen anbietet. Nun, die Frage ist relativ schnell beantwortet: Der Hauptgrund liegt selbstverständlich in der Verarbeitungsgeschwindigkeit von Excel. Immer, wenn Sie eine Zelle schreiben, wird automatisch eine Neuberechnung ausgelöst. Selbst wenn Sie die Berechnungsfunktion von Excel temporär lahmlegen, wird Excel immer eine Zeit lang benötigen, um alle Aufgaben erledigen zu können.
Daher wird die Flucht in den Arbeitsspeicher mittels Datenfeldern (=Arrays) angetreten. Bei der Array-Technik können auch riesige Datenbestände innerhalb von wenigen Sekunden abgearbeitet werden. Lernen Sie in diesem Artikel anhand einiger praxisnaher Beispiele, wie Sie Arrays auch für Ihre Aufgaben einsetzen können.
Einen Array anlegen und befüllen
Schauen wir uns zunächst einmal einen einfachen Array an. Wie wird er befüllt und wie kann man ihn einsehen und auslesen? Beim ersten Beispiel definieren Sie zunächst eine Variable von Typ Variant. Danach können Sie das noch leere Datenfeld über die Funktion Array befüllen.
Sub ArrayFuellenUndAusgeben()
Dim VarDat As Variant
Dim rngBereich As Range
With Tabelle1
VarDat = Array("VW", "Audi", "Opel", "BMW")
Set rngBereich = .Range("A1").Resize(UBound(VarDat) + 1)
rngBereich.Value = Application.WorksheetFunction.Transpose(VarDat)
End With
End Sub
Die Variable VarDat wird als Datentyp Variant deklariert. Dieser Datentyp hat 16 Bytes und ist somit der größte Datentyp. Befüllt werden kann diese Variable über die Funktion Array. Geben Sie in Klammern danach die einzelnen Inhalte für das Datenfeld getrennt mit Komma an. Wenn Sie das Lokal-Fenster in der Entwicklungsumgebung einblenden und mit F8 Schritt für Schritt durch den Quellcode schreiten, dann können Sie die Befüllung des Datenfeldes direkt im Lokal-Fenster betrachten.
Im nächsten Schritt muss die Größe des Arrays gemessen und in der Ergebnistabelle in gleicher Länge nach unten vorgehalten werden. Mit der Funktion UBound können Sie die Größe des Arrays feststellen. Da ein Array aber standardmäßig mit dem Wert 0 beginnt, muss in der Tabelle für den Vorhaltebereich der Wert 1 hinzuaddiert werden. Über die Eigenschaft Resize wird beginnend bei der Zelle A1 der benötigte Platzbedarf zur Aufnahme des Arrays reserviert. Dieser so ermittelte Bereich wird in der Objektvariablen rngBereich mit Hilfe der Anweisung Set bekanntgegeben. Mit Hilfe der Funktion Transpose wird das Datenfeld gedreht, damit das Datenfeld von oben nach unten in den Bereich eingefügt werden kann.
Als alternative Variante kann aber auch der komplette Bereich in einem Aufwasch an den Array übergeben werden. Sehen Sie sich dazu das folgende Makro an.
Sub ArrayAusEinerTabelleGanzerBereich() Dim VarDat As Variant Dim VarFeld As Variant VarDat = Tabelle2.Range("A1:D10") For Each VarFeld In VarDat Debug.Print VarFeld Next VarFeld End Sub
Der Zellenbereich in der Tabelle kann direkt im Datenfeld gespeichert werden. Die Aufsplittung der Daten in die einzelnen Felder des Arrays wird hierbei voll automatisch vorgenommen.
Einen Array aus einer Tabelle befüllen, verändern und in anderer Tabelle ausgeben
In Excel wird dazu die folgende Technik verwendet: Dabei wird der Inhalt einer Tabelle mit einem einzigen Kommando in ein Datenfeld befördert und dort weiterverarbeitet. Mit dem folgenden Makro wird der verwendete Bereich der Tabelle2 in den Arbeitsspeicher befördert. Im Arbeitsspeicher wird das Datenfeld Feld für Feld abgearbeitet und verändert. Am Ende wird der komplette Datenblock aus dem Arbeitsspeicher zurück in die Tabelle2 geschrieben.
Sub TabelleInDatenfeldPackenUndZurueck()
Dim VarDat As Variant
Dim lngZeile As Long
Dim lngZeileMax As Long
Dim lngSpalte As Long
Dim lngSpalteMax As Long
Dim dblWert As Double
With Tabelle2
VarDat = .UsedRange.Value
lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
lngSpalteMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
For lngZeile = 1 To lngZeileMax
For lngSpalte = 1 To lngSpalteMax
dblWert = VarDat(lngZeile, lngSpalte)
VarDat(lngZeile, lngSpalte) = dblWert * 2
Next lngSpalte
Next lngZeile
.Range(.Cells(1, 1), _
.Cells(lngZeileMax, lngSpalteMax)).Value = VarDat
End With
End Sub
Das Datenfeld kann direkt aus der Tabelle befüllt werden. Dabei ermittelt die Eigenschaft UsedRange den benutzten Bereich der Tabelle. Über die Eigenschaft Value wird der komplette Bereich in das Datenfeld geschrieben. Die Aufteilung der Zellen in die Felder des Arrays erfolgt hierbei voll automatisch.
In einer folgenden, geschachtelten For Next-Schleife wird jedes Feld im Array nacheinander verarbeitet. Innerhalb der Schleife wird das jeweilige Feld zunächst in einer Variablen gespeichert und danach im Wert verdoppelt und in das Feld zurückgeschrieben.
Ist die Verarbeitung der beiden Schleifen am Ende angekommen, muss der Ergebnisbereich in der Tabelle2 wieder vorgehalten werden. Wird ein Array über den benutzten Bereich einer Tabelle befüllt, dann beginnt der erste Eintrag im Datenfeld mit dem Wert 1. Das bedeutet, dass in diesem Fall nicht am Zählerstand herumgespielt werden muss und der komplette Datenblock in die Tabelle zurückgeschrieben werden kann.
Mehrere Kriterien beim Datenfilter einstellen
Bei der folgenden Aufgabenstellung liegt eine Tabelle mit mehreren Städten und deren Einwohnerzahlen vor. Über einen Filter, bei dem mehrere Städte voreingestellt werden, soll der Datenfilter angewendet werden
Das nachstehende Makro soll die Städte Hamburg, Berlin und Stuttgart filtern. Dabei soll die zu filternde Spalte, in diesem Fall die Spalte A, dynamisch ermittelt werden.
Listing 1: Mehrere Filter über einen Array setzen
Sub MehrereFilterkriterienEinstellen()
Dim lngZeileMax As Long
Dim rngBereich As Range
Dim intFilter As Integer
With Tabelle5
lngZeileMax = .Range("A" & .Rows.Count).End(xlUp).Row
Set rngBereich = .Range("A1:B" & lngZeileMax)
intFilter = WorksheetFunction.Match("Stadt", rngBereich.Rows(1), 0)
If .AutoFilterMode = False Then
rngBereich.AutoFilter
End If
rngBereich.AutoFilter Field:=intFilter, Criteria1:=Array( _
"Hamburg", "Berlin", "Stuttgart"), Operator:=xlFilterValues
End With
End Sub
Ermitteln Sie zunächst die Anzahl der belegten Zellen in Spalte A. Danach bilden Sie einen Bereich, der die beiden Spalten A und B enthält. Im nächsten Schritt suchen Sie über die Funktion Match nach der Überschrift Stadt und speichern Sie die gefundene Filterspalte in der Variablen intFilter. Prüfen Sie im nächsten Schritt über die Eigenschaft AutoFilterMode, ob bereits ein Datenfilter gesetzt ist. Wenn nicht, dann schalten Sie diesen über die Methode AutoFilter ein. Damit werden die Filterpfeile eingeblendet – eine Voreinstellung der Filter ist zu diesem Zeitpunkt noch nicht vorgenommen worden.
Stellen Sie die gewünschten Städte über die Funktion Array ein und übergeben Sie diese der Methode AutoFilter über den Parameter Criteria1. Als zu filterndes Feld geben Sie im Parameter Field den Inhalt der Variablen intFilter an. Geben Sie am Ende noch im Parameter Operator die Konstante xlFilterValues an, um dem Filter mitzuteilen, dass Sie nach Werten filtern möchten.
Eine Mehrfachsuche in einer Tabelle durchführen
Bei der Suche nach mehreren Werten, die in einer Tabelle mehrfach vorkommen könnten, leistet uns ein Array, den wir als Suchbegriff an die Standardsuche von Excel übergeben, eine große Unterstützung. Schauen Sie sich zunächst die Ausgangssituation an (s. Abb. 6).
Die eigentliche Aufgabenstellung lautet: Kennzeichne alle Zelle mit den Werten 4711 und 4720. Starten Sie zur Umsetzung dieser Aufgabe das folgende Makro.
Sub MehrereSuchbegriffeFinden()
Dim strAdr As String
Dim VarDat As Variant
Dim rngTreffer As Range
Dim lngZ As Long
VarDat = Array("4711", "4720")
With Tabelle9.Range("A:A") .Interior.ColorIndex = xlColorIndexNone
For lngZ = LBound(VarDat) To UBound(VarDat)
Set rngTreffer = .Find(What:=VarDat(lngZ), LookAt:=xlWhole)
If Not rngTreffer Is Nothing Then
strAdr = rngTreffer.Address
Do
rngTreffer.Interior.ColorIndex = 4
Set rngTreffer = .FindNext(rngTreffer)
Loop While Not rngTreffer Is Nothing And rngTreffer.Address <> strAdr
End If
Next lngZ
End With
End Sub
Geben Sie im ersten Schritt des Makros an, nach welchen Nummern Sie suchen möchten. Erfassen Sie diese Suchnummern mit Hilfe der Funktion Array direkt im Datenfeld VarDat. Danach stellen Sie sicher, dass die Spalte A frei von eventuell vorher gesetzten Färbungen ist. Weisen Sie dazu der Eigenschaft ColorIndex die Konstante xlColorIndexNone zu.
Durchlaufen Sie in einer For Next-Schleife jeden einzelnen Suchbegriff aus dem gerade gefüllten Datenfeld VarDat. Innerhalb der Schleife wenden Sie die Methode Find an, um nach den Nummern zu suchen. Als wichtige Parameter wäre hier die Parameter What und LookAt zu nennen. Über den einen Parameter geben Sie den Suchbegriff und im anderen legen Sie fest, dass eine Suche nach exakter Übereinstimmung stattfinden soll. Da es sich um eine Mehrfachsuche handelt, muss eine weitere Schleife geschaltet werden.
In einer Do While-Schleife wiederholen Sie solange die Suche, bis die zuerst gefundene Zelle erneut gefunden wird.
Alle Tabellen in Array einlesen und ausgeben
Bei der nächsten Aufgabenstellung sollen die Namen aller in der Arbeitsmappe enthaltenen Tabelle in einem Array gesammelt werden und anschließend in der Tabelle tbl_Übersicht untereinander ausgegeben werden. Setzen Sie dazu das folgende Makro ein.
Sub TabellenInArrayEinlesenUndAusgeben()
Dim Vardat As Variant
Dim lngZZ As Long
ReDim Vardat(ThisWorkbook.Worksheets.Count - 1)
For lngZZ = 0 To UBound(Vardat)
Vardat(lngZZ) = ThisWorkbook.Worksheets(lngZZ + 1).Name
Next lngZZ
tbl_Überblick.Range("A1:A" & lngZZ) = _
Application.WorksheetFunction.Transpose(Vardat)
End Sub
Deklarieren Sie zunächst ein Datenfeld über die Anweisung Dim. Zu diesem Zeitpunkt ist noch nicht bekannt, wie groß der Array genau werden soll. Diese Information ergibt sich aus der Gesamtanzahl der in der Mappe befindlichen Tabellen. Diese Anzahl der Tabellen können Sie über die Funktion Count abfragen. In dem Auflistungsobjekt Worksheets sind standardmäßig alle Tabellen der Mappe enthalten. Von der ermittelten Gesamtanzahl der Tabellen müssen Sie den Wert 1 subtrahieren, da Arrays standardmäßig, sofern nicht anders angegeben, mit dem Index 0 starten.
In einer anschließenden For Next-Schleife wird Feld für Feld des Arrays angesteuert und der jeweilige Namen der Tabelle in den Array geschrieben. Nach Schleifenaustritt wird der benötigte Platz in der Zieltabelle vorgehalten und das Datenfeld über die Funktion Transpose gedreht, sodass die Inhalte des Arrays vertikal ausgegeben werden können.
Eine tolle Erweiterung des letzten Makros wäre noch, wenn man hinter die Tabellennamen noch einen Hyperlink hinterlegen könnte, damit man mit einem Klick in die entsprechende Tabelle wechseln kann. Diese Aufgabe wird vom folgenden Makro gelöst.
Sub HyperlinksErstellenTabellenNamen()
Dim rngBereich As Range
Dim rngZelle As Range
With tbl_Überblick
Set rngBereich = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
For Each rngZelle In rngBereich
rngZelle.Hyperlinks.Add rngZelle, ThisWorkbook.Name, _
"'" & rngZelle.Value & "'!A1", rngZelle.Value, rngZelle.Value
Next rngZelle
End With
End Sub
Geben Sie zunächst an, in welchen Zellen die Namen der Tabelle verzeichnet sind. Speichern Sie diese Information in der Range-Variablen rngBereich. In einer anschließenden For Each Next-Schleife wird Zelle für Zelle dieses Bereichs abgearbeitet. In der Schleife wenden Sie die Methode Add auf das Objekt Hyperlinks an, um den jeweiligen Link zu setzen.
Textdatei in einen Array einlesen und weiterverarbeiten
Bei der nächsten Ausgabenstellung soll eine große Textdatei in einen Array eingelesen werden. Dabei soll anhand eines Merkmals entschieden werden, ob der jeweilige Satz weiterverarbeitet werden soll oder nicht. Sehen Sie sich vorab einmal die folgende Ausgangssituation an.
Aus dieser Textdatei sollen zunächst alle Datensätze in einem Aufwasch in einen Array eingelesen werden. Danach werden alle Datensätze mit einem Umsatz größer 5000 in einen zweiten Array geschrieben, der dann in Tabelle10 eingefügt werden soll.
Das Makro für die Lösung dieser Aufgabenstellung sehen Sie im folgenden Listing:
Sub CSVinArrayLesen()
Dim strDatei As String
Dim i As Integer
Dim VarArr As Variant, VarTemp As Variant, Dim VarZiel() As Variant
Dim lngZeileMax As Long, lngSpalteMax As Long
Dim lngZeile As Long, lngSpalte As Long, lngZZ As Long
Tabelle10.UsedRange.Clear
strDatei = ThisWorkbook.Path
strDatei = strDatei & "\Umsätze.csv"
i = FreeFile
Open strDatei For Input As i
strDatei = Input$(LOF(i), #i)
Close i
' Anhand Zeilenvorschub die Daten in den Array VarArrr einlesen
VarArr = Split(strDatei, vbCrLf)
' Den Array nach dem beanspruchten Platz dimensionieren
lngZeileMax = UBound(VarArr)
VarTemp = Split(VarArr(0), ";")
lngSpalteMax = UBound(VarTemp)
ReDim VarZiel(lngZeileMax, lngSpalteMax)
'Die Daten in den Array VarZiel einlesen
For lngZeile = 0 To lngZeileMax
If Len(VarArr(lngZeile)) > 0 Then
VarTemp = Split(VarArr(lngZeile), ";")
If VarTemp(2) > 5000 Then
For lngSpalte = 0 To lngSpalteMax
VarZiel(lngZZ, lngSpalte) = VarTemp(lngSpalte)
Next lngSpalte
lngZZ = lngZZ + 1
End If
End If
Next lngZeile
With Tabelle10
.Range(.Cells(1, 1), .Cells(lngZZ, lngSpalte)).Value = VarZiel
End With
End Sub
Löschen Sie zu Beginn des Makros den Inhalt der Zieltabelle Tabelle10 mit Hilfe der Methode Clear. Öffnen Sie danach die Textdatei Umsätze.csv über die Anweisung Open. Speichern Sie den kompletten Inhalt dieser Textdatei in der Variablen strDatei vom Typ String. Setzen Sie dazu die Funktion Split ein, die anhand des Zeilenvorschubs die kompletten Daten in den Array VarArr befördert.
Danach wird gemessen, wie groß dieser Array genau ist. Dazu setzen Sie die Funktion UBound ein, die die Anzahl der darin enthaltenen Zeilen zurückgibt. Danach wird der erste Datensatz komplett anhand des Trennzeichens Semikolon in den Array VarTemp zerlegt. Jetzt kann gemessen werden, wie viele Spalten im Array VarZiel benötigt werden. Über die Anweisung ReDim wird diese benötigte Größe für den Ziel-Array definiert.
In einer anschließenden Schleife wird der ursprüngliche Array, der noch alle Daten aus der Textdatei enthält, durchlaufen. Innerhalb der Schleife wird geprüft, ob es sich um einen gültigen Satz handelt. Wenn ja, dann erfolgt die Aufsplittung anhand des Semikolons in den temporären Array VarTemp. Danach kann der Umsatz geprüft werden. Er steht in diesem Fall in der dritten Spalte des Arrays. Ist der Umsatz größer 5000, dann wird der Array VarZiel befüllt.
Bei Schleifenaustritt ist der Ziel-Array befüllt und muss in die Zieltabelle Tabelle10 entleert werden. Der Array VarZiel ist nicht komplett befüllt, sondern eben nur mit den Daten, bei denen der Umsatz größer als 5000 war.
Diesen teilweise befüllten Array geben Sie in der Zieltabelle aus, indem Sie den dafür benötigten Platz in der Tabelle vorreservieren. Die Anzahl der belegten Zeilen im Array VarZiel können Sie der Variablen lngZZ entnehmen. An der Anzahl der Spalten hat sich nichts geändert – diesen Wert entnehmen Sie der Variablen lngSpalte.
wolfgang schmidt
am 17.12.2020meine vba Funktion liefert 4 Ergebnisse, die ich in 4 Zellen einer Excel Tabelle übergeben möchte. Wie geht das? Wenn es nicht geht, müsste ich für jedes Ergebnis je eine Funktion erstellen.
Danke im voraus.
Gruß
W. Schmidt
Bernd Held
am 22.12.2020mögen Sie mir dazu eine e-Mail mit Anhang senden, damit ich mir die Sache ansehen kann?
VG
Bernd Held
Jan Meyer
am 24.07.2020Sub TabelleInDatenfeldPackenUndZurueck()
Dim VarDat As Variant
Dim lngZeile As Long
Dim lngZeileMax As Long
Dim lngSpalte As Long
Dim lngSpalteMax As Long
Dim dblWert As Double
With Tabelle2
VarDat = .UsedRange.Value
lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
lngSpalteMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
For lngZeile = 1 To lngZeileMax
For lngSpalte = 1 To lngSpalteMax
dblWert = VarDat(lngZeile, lngSpalte)
VarDat(lngZeile, lngSpalte) = dblWert * 2
Next lngSpalte
Next lngZeile
.Range(.Cells(1, 1), _
.Cells(lngZeileMax, lngSpalteMax)).Value = VarDat
End With
End Sub
ausführe, wird der Fehler '424' bei VarDat = .UsedRange.Value ausgegeben.
Woran kann das liegen? Vielen Dank für die Antwort!
Jan
Bernd Held
am 24.07.2020Du musste den Codenamen der Tabelle dabei verwenden. Schau mal in der Entwicklungsumgebung im Projekt-Explorer, wie Deine Tabelle wirklich heißt. Der Codename ist der Tabellenname, der links steht.
VG
Bernd
Jan Meyer
am 24.07.2020