Über unsMediaKontaktImpressum
Bernd Held 22. August 2014

Die Flucht in den Arbeitsspeicher - Massendaten in VBA blitzschnell verarbeiten

Seit der Version Excel 2007, bei der Microsoft die maximale Zeilenanzahl einer Tabelle auf den Wert von 1.048.576 Zeilen gelegt hat, gibt es in der Praxis mehr und mehr Probleme in Bezug auf Verarbeitungsgeschwindigkeit von Daten. Der Ansatz, bestimmte Aufgaben über Excel-eigene Tabellenfunktionen erledigen zu wollen, scheitert in vielen Fällen an der integrierten Berechnungsfunktion von Excel, die standardmäßig immer dann „angeworfen“ wird, wenn sich eine Änderung in einer Zelle, Tabelle oder Mappe ergibt. Oft ist minutenlanges Warten dann die Folge.

Anstatt eine Verarbeitung der Daten Zeile für Zeile in einer Tabelle von oben nach unten vorzunehmen, kann man diese Aufgabe auch elegant und blitzschnell direkt im Arbeitsspeicher über einen sogenannten Array durchführen lassen. Dabei werden Daten aus einer Tabelle 1:1 mit einem Befehl in den Arbeitsspeicher transportiert und danach verarbeitet. Da bei dieser Art der Verarbeitung keine Berechnung in Excel angestoßen wird, erfolgt diese Verarbeitung je nach Datenmenge schon gern einmal innerhalb einer Sekunde. Nach erfolgreicher Verarbeitung der Daten werden diese wiederum mit nur einem einzigen Befehl in eine Excel-Tabelle ausgegeben.

Aktionen im Arbeitsspeicher ausführen lassen

Beim folgenden einführenden Beispiel werden die Daten der Tabelle tbl_Daten 1:1 in den Arbeitsspeicher transportiert. Im Arbeitsspeicher selbst wird jeder Wert verdoppelt und anschließend in der Tabelle tbl_Ergebnis ausgegeben. In diesem ersten Beispiel soll es mehr um die angewandte Technik als die eigentliche Aufgabe gehen.

Listing 1

Sub BereichInArrayEinlesen()

Dim VarDat As Variant
Dim Zeile As Long
Dim Spalte As Long
Dim ZeileMax As Long
Dim Spaltemax As Long
  
On Error GoTo BereichInArrayEinlesen_Error

    With tbl_Daten
     'Array mit Daten aus der Tabelle füllen
     VarDat = .UsedRange
     ZeileMax = .UsedRange.Rows.Count
     Spaltemax = .UsedRange.Columns.Count
     'Array, Feld für Feld verarbeiten
     For Zeile = 1 To ZeileMax
      For Spalte = 1 To Spaltemax
        VarDat(Zeile, Spalte) = VarDat(Zeile, Spalte) * 2
      Next Spalte
     Next Zeile
     End With

    'geänderten Array in tbl_Ergebnis ausleeren
    With tbl_Ergebnis
       .Range(.Cells(1, 1), .Cells(ZeileMax, Spaltemax)) = VarDat
    End With
   On Error GoTo 0
   Exit Sub
BereichInArrayEinlesen_Error:
    MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
            ") in prozedur BereichInArrayEinlesen of Modul mdl_Array in Zeile "
End

Listing 1: Daten in Array einlesen, verarbeiten und ausgeben.

Im ersten Schritt des Makros aus dem vorherigen Listing übertragen Sie den kompletten, benutzten Bereich der Tabelle in das Datenfeld VarDat vom Typ Variant. Danach ermitteln Sie wie viele Zeilen bzw. Spalten dieser Bereich beinhaltet und speichern diese Information in den Variablen ZeileMax bzw. SpalteMax. Mittels einer geschachtelten Schleife wird nun Feld für Feld im Arbeitsspeicher abgearbeitet. Dabei wird der jeweilige Wert verdoppelt.

Tipp: Die einzelnen Berechnungen können Sie sehr gut im Lokalfenster der Entwicklungsumgebung verfolgen. Wählen Sie dazu in der Entwicklungsumgebung den Befehl Ansicht/Lokal-Fenster und arbeiten Sie das Makro Schritt für Schritt über die Taste F8 ab.

Am Ende der beiden Schleifen stehen alle Ergebnisse im Arbeitsspeicher in der Variablen VarDat bereit und müssen noch in die Zieltabelle tbl_Ergebnis befördert werden. Auch für diesen Vorgang bedarf es nur eines einzigen Befehls. Dabei muss die Größe des Datenfelds ermittelt werden. Diese ergibt sich durch die Variablen ZeileMax und SpalteMax. Das Datenfeld muss dabei in seiner ganzen Größe an einen dazu passenden Bereich in der Tabelle tbl_Ergebnis übergeben werden.

Bestimmte Daten aus einer Tabelle elegant löschen

Beim folgenden Beispiel liegen in der Tabelle tbl_Gesamt Zahlenwerte vor. Die Aufgabe besteht nun darin, nur Werte, die in einem bestimmten Wertebereich liegen, in die Tabelle tbl_Rest zu transferieren.

Um diese Aufgabe schnellstmöglich zu lösen, setzen Sie das Makro aus dem folgenden Listing ein.

Sub ZeilenLöschen()
  Dim VarDat As Variant   Dim VardatZiel As Variant   Dim ZeileArr As Long   Dim Zeile As Long 
   On Error GoTo ZeilenLöschen_Error
  Debug.Print "Start:" & Now
  tbl_Rest.UsedRange.ClearContents
  With tbl_Gesamt.UsedRange
     'Array aus Tabelleninhalt befüllen
      VarDat = tbl_Gesamt.UsedRange
      Zeile = 1
      'gleichgroßen Zielarray anlegen
      ReDim VardatZiel(1 To .Rows.Count, 1 To .Columns.Count)
      For ZeileArr = 1 To UBound(VarDat)
       If VarDat(ZeileArr, 1) > 90 Then
         VardatZiel(Zeile, 1) = VarDat(ZeileArr, 1)
         Zeile = Zeile + 1
       End If
      Next ZeileArr
  End With
  With tbl_Rest
   .Range(.Cells(1, 1), .Cells(Zeile, UBound(VardatZiel, 2))) = VardatZiel
  End With
   Debug.Print "Ende:" & Now
   On Error GoTo 0
   Exit Sub
ZeilenLöschen_Error:
    MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
    ") in prozedur ZeilenLöschen of Modul mdl_Löschen"
End Sub

Listing 2: Bestimmte Zeilen sollen gelöscht werden.

Das "Löschen der Daten"

Das „Löschen der Daten“ wird hier mit Hilfe einer zusätzlichen Tabelle erledigt, d.h. es werden nicht wirklich Daten gelöscht, sondern eben nur die gewünschten Daten auf eine Zieltabelle ausgegeben. Dazu wird die Tabelle tbl_Rest vorab sicherheitshalber über die Methode ClearContents, die Sie auf den benutzten Bereich der Tabelle anwenden, eingesetzt.

Dazu gehen Sie wie folgt vor: Zunächst werden zwei Datenfelder vom Typ Variant deklariert. Die Größe der Datenfelder liegt zu diesem Zeitpunkt des Makros noch nicht vor. Zusätzlich benötigen Sie noch zwei Variablen vom Typ Long, um die Zeilen im Datenfeld verarbeiten zu können.

Danach wird der Startzeitpunkt über die Anweisung Debug.Print und der Funktion Now festgehalten. Damit wird ein Zeitstempel im Direktfenster der Entwicklungsumgebung gesetzt. Am Ende des Makros wird diese Anweisung wiederholt. Dies soll hier zur Orientierung dienen, wie schnell dieses Makro verarbeitet wird.

Im nächsten Schritt wird die komplette Tabelle, hier bestehend aus einer Spalte, in ein Datenfeld mit dem Namen VarDat transportiert. Jetzt wird ein gleichgroßer Ziel-Array mit dem Namen VarDatZiel angelegt. Denn es könnte rein theoretisch sein, dass alle Daten in die Zieltabelle übertragen werden würden. Zu diesem Zeitpunkt des Makros ist die Größe (=Zeilenanzahl) des Arrays bekannt. Um diesen Array jetzt neu zu dimensionieren, also seine tatsächliche Größe zuzuweisen, verwenden Sie die Anweisung ReDim. Es handelt sich hierbei um einen zweidimensionalen Array, der genau wie eine Tabelle aus Zeilen und Spalten besteht.

Im Anschluss daran wird der Array VarDat mittels einer For Next-Schleife Zeile für Zeile durchlaufen. Über die Funktion UBound, die Sie am Array VarDat ansetzen, können Sie die letzte Zeile des Arrays ermitteln. Innerhalb der Schleife prüfen Sie, ob der jeweilige Wert größer als der Wert 90 ist. Wenn ja, dann übertragen Sie den Wert aus dem Array VarDat in den Array VarDatZiel. Erhöhen Sie danach den Zeilenzähler jeweils um den Wert 1.

Beim Schleifenaustritt ist der Array VarDatZiel mit Daten gefüllt. Dieser Array muss jetzt in die Tabelle tbl_Rest eingefügt werden. Dazu muss die Größe des Bereichs in der Tabelle vorher genau angegeben werden.

Hinweis: Mit der Anweisung UBound(VardatZiel, 2) erhalten Sie die letzte gefüllte Spalte des Arrays, mit der Anweisung UBound(VardatZiel, 1) erhalten Sie die letzte gefüllte Zeile des Arrays.

Diese Informationen können Sie übrigens elegant im Direktfenster abfragen, indem Sie zunächst einen Haltepunkt mit der Taste F9 setzen und danach das Makro mit der Taste F5 bis zu der in Abbildung 4 angezeigten Stelle abarbeiten lassen. Danach fragen Sie die beiden „Eckkoordinaten“ im Direktfenster ab.

Was aber hat genau diese Art der Verarbeitung für einen Vorteil? Nun, Sie haben die ursprüngliche Tabelle tbl_Gesamt nicht verändert und lediglich die gewünschten Daten in eine andere Tabelle transportiert.

Werte anhand mehrerer Kriterien blitzschnell ausfiltern

Die gleiche Technik wie gerade beschrieben funktioniert genauso gut, wenn Daten anhand mehrerer Kriterien ausgefiltert werden sollen. Sehen Sie sich dazu einmal die Ausgangssituation aus Abbildung 5 an.

Mögliche Filterkriterien könnten beispielsweise der Umsatz und die PLZ sein. So sollen alle Kunden aus der PLZ-Region 8000 und höher mit einem Umsatz über 250 Euro in die Tabelle tbl_PLZ_Umsatz transportiert und anschließend nach Umsatzhöhe sortiert werden. Diese Filterung wird im Makro aus dem folgenden Listing angewendet.

Sub ZeilenFilternMehrereKriterien()
  Dim VarDat As Variant
  Dim VardatZiel As Variant
  Dim ZeileArr As Long
  Dim Zeile As Long
  Dim Spalte As Long
  Dim SpalteMax As Long
   On Error GoTo ZeilenFiltern_Error
  Debug.Print "Start:" & Now   tbl_PLZ_Umsatz.UsedRange.ClearContents
  With tbl_Kunden.UsedRange
     'Array aus Tabelleninhalt befüllen
      VarDat = tbl_Kunden.UsedRange
      SpalteMax = .Columns.Count
      Zeile = 1
            'gleichgroßen Zielarray anlegen
      ReDim VardatZiel(1 To .Rows.Count, 1 To .Columns.Count)
      For ZeileArr = 1 To UBound(VarDat)
       If VarDat(ZeileArr, 4) > 80000 And VarDat(ZeileArr, 6) > 250 Then
        For Spalte = 1 To SpalteMax
         VardatZiel(Zeile, Spalte) = VarDat(ZeileArr, Spalte)
         Next Spalte
         Zeile = Zeile + 1
       End If
      Next ZeileArr
  End With
  With tbl_PLZ_Umsatz
   .Range(.Cells(1, 1), .Cells(Zeile, UBound(VardatZiel, 2))) = VardatZiel
   .Range("A:F").Sort Key1:=.Range("F1"), Order1:=xlDescending, _
    Key2:=.Range("A1"), order2:=xlAscending, Header:=xlYes
    .Range("A:F").Columns.AutoFit
  End With
   Debug.Print "Ende:" & Now
   On Error GoTo 0
   Exit Sub
ZeilenFiltern_Error:
    MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
    ") in prozedur ZeilenFilternMehrereKriterien of Modul mdl_Löschen"
End Sub

Listing 3: Eine Filterung mit mehreren Kriterien anwenden.

Wie schon im vorherigen Beispiel legen Sie zu Beginn des Makros aus dem vorherigen zwei Variablen vom Typ Variant an. Diese werden später die Daten aufnehmen.

Löschen Sie danach die Zieltabelle tbl_PLZ_Umsatz mit Hilfe der Methode ClearContents. Damit werden nur die Werte aber keine Formatierungen gelöscht. Danach übertragen Sie wie vorher schon beschrieben alle Daten aus der Tabelle tbl_Kunden in das Datenfeld VarDat. Jetzt wird ermittelt, wie viele Spalten denn überhaupt in Verwendung sind. Dieser Wert wird in der Variablen SpalteMax für die spätere Verwendung zwischengespeichert. Danach wird der Ziel-Array, der die gefilterten Daten aufnehmen soll, redimensioniert. Der Ziel-Array VarDatZiel ist dabei zu Beginn genauso groß wie auch der Quell-Array VarDat.

Im Anschluss daran wird der Quell-Array Zeile für Zeile mit einer For Next-Schleife durchlaufen. Innerhalb der Schleife werden die vierte Spalte sowie die sechste Spalte des Datenfelds geprüft. Nur wenn die Postleitzahl größer als 8000 und der Umsatz größer als 250 Euro betragen, wird die jeweilige Zeile in den Ziel-Array VarDatZiel geschoben. Die Befüllung erfolgt wiederum über eine For Next-Schleife, die Spalte für Spalte innerhalb der „gefilterten“ Zeile abarbeitet und im Ziel-Array ablegt. Wurden alle Zeilen des Quell-Arrays VarDat abgearbeitet, kann die wirkliche Größe des Ziel-Arrays VarDatZiel ermittelt werden. Diese Größe wird als Bereich in der Tabelle tbl_PLZ_Umsatz vorgehalten und der Ziel-Array VarDatZiel wird in einem Schritt in die Tabelle geschoben. Der Neuberechnungsaufwand ist dabei sehr gering.

Als nächstes werden die eingefügten Daten direkt in der Tabelle mit Hilfe der Methode Sort zunächst nach Höhe des Umsatzes und dann nach dem Namen sortiert. Damit die Daten gut lesbar sind, wenden Sie die Methode AutoFit auf die Spalten A-F an, die dafür sorgt, dass die Spalten nach ihrem Platzbedarf angepasst werden.

Daten schneller konvertieren

Ein wahrer Zeitfresser sind Arbeiten, die mit dem Vorbereiten und Konvertieren von Daten zusammenhängen. Auch hier können Makros helfen, Routinearbeiten automatisch und vor allem sehr schnell mit Hilfe von Arrays zu erledigen.

Bei der folgenden Aufgabe liegt eine Tabelle mit dem Namen tbl_Konvertieren wie in Abbildung 6 gezeigt vor.

Im Datenbestand liegen einige Probleme vor:

  • In Spalte B der Tabelle tbl_Konvertieren liegen teilweise negative Werte vor. Leider befinden sich die Minuszeichen dabei auf der falschen Seite. Diese müssen am rechten Rand abgeschnitten und am linken Rand eingesetzt werden.
  • In Spalte C gibt es bei einigen Zellen führende bzw. nachgestellte Leerzeichen. Nicht nur, dass man diese unerwünschten Zeichen schlecht sieht, nein, sie stellen auch bei späteren Auswertungen eine große Gefahr da. Auch diese Leerzeichen müssen entfernt werden.
  • In Spalte D soll das Zeichen „/“ durch ein Minuszeichen ersetzt werden.
  • Die Spalten E und F sollen zusammengeklebt und als Schlüssel verwendet werden.

Neben reinen Konvertierungsarbeiten müssen oft auch zusätzliche Informationen generiert werden, die im eigentlichen Datenbestand noch nicht vorhanden sind.

Tipp: Auch bei Konvertierungsarbeiten ist es ratsam, nie die Datenquelle zu überschreiben, sondern die Ergebnisse der Konvertierung auch einer anderen Tabelle auszugeben. Aus Gründen der Nachvollziehbarkeit ist es immer besser, später sagen zu können: „Das habe ich bekommen und das habe ich daraus gemacht!“. Wenn Sie die Quelle überschreiben, dann ist es später immens schwer, einen möglichen Fehler nachzuvollziehen. Daher werden bei dieser Aufgabe die Ergebnisse der Konvertierung in der Tabelle tbl_KonvertierungZiel ausgegeben.

Lösen Sie alle diese Aufgabenstellungen mit dem folgenden Makro.

Sub DatenKonvertierenImArbeitsspeicher()
  Dim VarDat As Variant
  Dim Zeile As Long

   On Error GoTo DatenKonvertierenImArbeitsspeicher_Error

  With tbl_Konvertieren
    ZeileMax = .UsedRange.Rows.Count
    SpalteMax = .UsedRange.Columns.Count + 1 '+1, wegen der zusätzlichen Schlüsselspalte
    
     'Array befüllen
      VarDat = .Range(.Cells(1, 1), .Cells(ZeileMax, SpalteMax))
     
     'Daten im Array manipulieren
      For Zeile = LBound(VarDat) To UBound(VarDat)

       'Minuszeichen umstellen, bei Werten wo das Minuszeichen auf der falschen Seite steht
       If Right(VarDat(Zeile, 2), 1) = "-" Then
         VarDat(Zeile, 2) = Left(VarDat(Zeile, 2), Len(VarDat(Zeile, 2)) - 1) * -1
       End If

       'Trim (Entfernen der vorangestellten bzw. nachgestellten Leerzeichen
        VarDat(Zeile, 3) = Trim(VarDat(Zeile, 3))
       
        'Replace (Das Zeichen "/" wird durch das Zeichen "-" ersetzt
        VarDat(Zeile, 4) = Replace(VarDat(Zeile, 4), "/", "-")
       
        'Schlüssel bilden (Kst+Kontierung)
        VarDat(Zeile, 7) = VarDat(Zeile, 5) & VarDat(Zeile, 6)

      Next Zeile
  End With
 
  'Ausleeren des konvertierten Arrays in die Zieltabelle
 With tbl_KonvertierungZiel
  .Range(.Cells(1, 1), .Cells(Zeile, UBound(VarDat, 2))) = VarDat
 End With

   On Error GoTo 0
   Exit Sub


DatenKonvertierenImArbeitsspeicher_Error:

    MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
  ") in prozedur DatenKonvertierenImArbeitsspeicher of Modul mdl_Konvertieren"

End Sub

Listing 4: Problematisches Datenmaterial wird bereinigt.

Zu Beginn des Makros aus dem vorherigen Listing wird zunächst der benutzte Bereich der Tabelle tbl_Konvertieren ermittelt. Dazu wird die Eigenschaft UsedRange sowohl für die Zeilen als auch für die Spalten angewendet und das Ergebnis daraus in den Variablen ZeileMax und SpalteMax zwischengespeichert. Dabei müssen Sie darauf achten, dass eine zusätzliche Spalte in der Zieltabelle tbl_KonvertierungZiel angelegt werden soll, der die beiden Bezeichner Kst und Konto zu einem Schlüssel verbinden soll.

Im Anschluss daran wird der vorher ermittelte benutzte Bereich mit der zusätzlich benötigen Spalte in den Array VarDat geschrieben.

Nachdem die Daten aus der Tabelle im Array angekommen sind, wird der Array Zeile für Zeile mit Hilfe einer For Next-Schleife abgearbeitet.

Mit der Funktion LBound können Sie die erste Zeile des Arrays VarDat ermitteln, mit der Funktion UBound bekommen Sie die letzte Zeile des Arrays gemeldet.

Innerhalb der Schleife stellen Sie zunächst das Minuszeichen in der zweiten Spalte bei Bedarf um. Dazu setzen Sie die Funktion Left ein und übertragen die Zahl bis vor das Minuszeichen hin. Dabei messen Sie mit Hilfe der Funktion Len die Gesamtlänge der Zahl und subtrahieren davon den Wert 1, um das ganz rechts stehende Minuszeichen auszuschließen. Nachdem Sie das Minuszeichen auf diese Weise vom rechten Rand entfernt haben, muss das Minuszeichen an den linken Rand des Feldes. Um dies relativ elegant zu machen, multiplizieren Sie den Wert -1.

In der dritten Spalte des Datenfeldes befinden sich unerwünschte Leerzeichen am rechten und linken Rand des Feldes. Diese können Sie direkt im Array mit Hilfe der Funktion Trim automatisch entfernen lassen.

Um das Zeichen „/“ gegen das Minuszeichen zu tauschen, wenden Sie die Funktion Replace an, die Sie in der vierten Spalte des Arrays anwenden.

In der siebten Spalte des Arrays werden die beiden Spalten mit der Kostenstelle und dem Konto miteinander verbunden und geschrieben. Dazu wird der Verkettungsoperator & eingesetzt.

Die Konvertierung der Daten erfolgt ausnahmslos im Array VarDat. Am Ende des Makros wird dieser Array 1:1 in die Tabelle tbl_KonvertierungZiel gekippt.

Autor

Bernd Held

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

botMessage_toctoc_comments_9210