Excel-VBA und Arrays | Informatik Aktuell (2023)

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 TypVariante. Danach können Sie das noch leere Datenfeld über die FunktionArraybefü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 VariableVarDatawird als DatentypVariantedeklariert. Dieser Datentyp hat 16 Bytes und ist somit der größte Datentyp. Befüllt werden kann diese Variable über die FunktionArray. 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 mitF8Schritt für Schritt durch den Quellcode schreiten, dann können Sie die Befüllung des Datenfeldes direkt im Lokal-Fenster betrachten.

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") Für jedes VarFeld in VarDat Debug.Print VarFeld Next VarFeldEnd 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 derTabelle 2in 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 dieTabelle 2geschrieben.

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 WithEnd Sub

Das Datenfeld kann direkt aus der Tabelle befüllt werden. Dabei ermittelt die EigenschaftUsedRangeden benutzten Bereich der Tabelle. Über die EigenschaftWertwird der komplette Bereich in das Datenfeld geschrieben. Die Aufteilung der Zellen in die Felder des Arrays erfolgt hierbei voll automatisch.

In einer folgenden, geschachteltenFürs nächste-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 derTabelle 2wieder vorgehalten werden. Wird ein Array über den benutzten Bereich einer Tabelle befüllt, dann beginnt der erste Eintrag im Datenfeld mit dem Wert1. Das bedeutet, dass in diesem Fall nicht am Zählerstand herumgespielt werden muss und der komplette Datenblock in die Tabelle zurückgeschrieben werden kann.

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 SpalteA, 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 WithEnd Sub

Stellen Sie die gewünschten Städte über die FunktionArrayein und übergeben Sie diese der MethodeAutomatischer Filterüber den ParameterKriterien1. Als zu filterndes Feld geben Sie im ParameterFeldden Inhalt der VariablenintFilteran. Geben Sie am Ende noch im ParameterOperatordie KonstantexlFilterValuesan, 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 Werten4711und4720. 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 FunktionArraydirekt im DatenfeldVarData. Danach stellen Sie sicher, dass die SpalteAfrei von eventuell vorher gesetzten Färbungen ist. Weisen Sie dazu der EigenschaftFarbindexdie KonstantexlColorIndexNonezu.

Durchlaufen Sie in einerFürs nächste-Schleife jeden einzelnen Suchbegriff aus dem gerade gefüllten DatenfeldVarData. Innerhalb der Schleife wenden Sie die MethodeFindenan, um nach den Nummern zu suchen. Als wichtige Parameter wäre hier die ParameterWasundAnsehenzu 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 einerDo While-Schleife wiederholen Sie solange die Suche, bis die zuerst gefundene Zelle erneut gefunden wird.

In einer anschließendenFürs nächste-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 FunktionTransponierengedreht, 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 WithEnd Sub

Geben Sie zunächst an, in welchen Zellen die Namen der Tabelle verzeichnet sind. Speichern Sie diese Information in der Range-VariablenrngBereich. In einer anschließendenFür jeden Nächsten-Schleife wird Zelle für Zelle dieses Bereichs abgearbeitet. In der Schleife wenden Sie die MethodeHinzufügenauf das ObjektHyperlinksan, 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 inTabelle 10eingefü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 WithEnd Sub

Löschen Sie zu Beginn des Makros den Inhalt der ZieltabelleTabelle 10mit Hilfe der MethodeKlar. Öffnen Sie danach die TextdateiUmsätze.csvüber die AnweisungOffen. Speichern Sie den kompletten Inhalt dieser Textdatei in der VariablenstrDateivom TypZeichenfolge. Setzen Sie dazu die FunktionTeiltein, die anhand des Zeilenvorschubs die kompletten Daten in den ArrayVarArrbefördert.

Danach wird gemessen, wie groß dieser Array genau ist. Dazu setzen Sie die FunktionUBoundein, die die Anzahl der darin enthaltenen Zeilen zurückgibt. Danach wird der erste Datensatz komplett anhand des Trennzeichens Semikolon in den ArrayJaTempzerlegt. Jetzt kann gemessen werden, wie viele Spalten im ArrayVarZielbenötigt werden. Über die AnweisungReDimwird 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 ArrayJaTemp. 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 ArrayVarZielbefüllt.

Bei Schleifenaustritt ist der Ziel-Array befüllt und muss in die ZieltabelleTabelle 10entleert werden. Der ArrayVarZielist 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 ArrayVarZielkönnen Sie der VariablenlngZZentnehmen. An der Anzahl der Spalten hat sich nichts geändert – diesen Wert entnehmen Sie der VariablenlngSpalte.

Bernd Held

Bernd Held ist Dozent, VBA-Entwickler und -Programmierer aber auch Autor zahlreicher Fachbücher und Computer-Artikel.>>Weiterlesen

Kommentare (5)

• Wolfgang Schmidt

  am 17.12.2020

  Antworten

  Hallo,
  meine 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.2020

    Antworten

    Hallo Herr Schmidt,
    mö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.2020

  Antworten

  Insgesamt sehr anschaulich erklärt, vielen Dank! Wenn ich den Code:
  Sub TabelleInDatenfeldPackenUndZurueck()
  VarDat als Variante dimmen
  Dimmen Sie die lngZeile so lange
  Dimmen Sie lngZeileMax so lange
  Dim lngSpalte As Long
  Dim lngSpalteMax As Long
  Dimmen Sie dblWert als Double

  Mit Tabelle2

  VarDat = .UsedRange.Value
  lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
  lngSpalteMax = .Cells(1, .Columns.Count).End(xlToLeft).Column

  Für lngZeile = 1 bis lngZeileMax
  Für lngSpalte = 1 bis lngSpalteMax
  dblWert = VarDat(lngZeile, lngSpalte)
  VarDat(lngZeile, lngSpalte) = dblWert * 2
  Nächste lngSpalte
  Nächste lngZeile

  .Range(.Cells(1, 1), _
  .Cells(lngZeileMax, lngSpalteMax)).Value = VarDat

  Ende mit
  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.2020

    Antworten

    Hallo Jan,
    Du 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

    Antworten

    ... Ich habe den Fehler gefunden. Das Objekt Tabelle 2 musste eindeutig benannt werden.

Neuen Kommentar schreiben