Über unsMediaKontaktImpressum
Bernd Held 30. Oktober 2018

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.

Autor

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
    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
      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
    Insgesamt sehr anschaulich erklärt, vielen Dank! Wenn ich den Code:
    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

    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
      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
      ... Ich habe den Fehler gefunden. Das Objekt Tabelle 2 musste eindeutig benannt werden.

Neuen Kommentar schreiben