Performance-Optimierung mit Variants

Neben der Zweckerfüllung und Richtigkeit von Berechnungen, stellt Performance eine der wichtigsten Anforderungen an ein Excel-VBA-Makro dar. Häufig lässt sich aber gerade in diesem Bereich einiges optimieren.

 

Dieser Excel-Tipp ist für alle Entwickler gedacht, die performante Lösungen benötigen (z.B. weil sie größere Datenmengen in ein Arbeitsblatt schreiben müssen). Einigen Entwicklern sind die folgende Problematik und deren Lösung sicherlich bereits bekannt. Anderen wiederum möglicherweise nicht, oder zumindest nicht bewusst. Daher soll an dieser Stelle etwas genauer darauf eingegangen werden. Sicherlich finden sich damit die einen oder anderen Möglichkeiten, die eigenen Makros zu optimieren und vor allem zu beschleunigen.

Die Grundproblematik ist die folgende:

Es stehen zahlreiche Datensätze zur Verfügung, die mittel VBA-Makro verarbeitet und in ein Arbeitsblatt geschrieben werden müssen.

Für diesen Schritt bietet es sich auf den ersten Blick an, die Daten einzeln, direkt in die Zielzellen zu schreiben. Im folgenden Codebeispiel werden mittels einer Funktion „m_getDataRecalculation die Daten in Abhängigkeit der übergebenen Indizes berechnet. Die genaue Struktur dieser Funktion ist an dieser Stelle zunächst nicht weiter relevant. Der Zielbereich „rngTarget“ umfasst den gesamten Zieldatenbereich, der im Vorfeld bereits erzeugt wurde (z.B. ähnlich wie in http://www.ms-corporate-solutions.de/2015/07/21/verwendung-von-template-zeilen/).

 

'[…]

For lngRow = 1 To rngTarget.Rows.Count

       For lngCol = 1 To rngTarget.Columns.Count

              rngTarget.Cells(lngRow, lngCol).Value = m_getDataRecalculation(lngRow, lngCol)

       Next lngCol

Next lngRow

 

Grundlegend ist dieses Verfahren zweckdienlich und liefert das gewünschte Ergebnis, birgt jedoch einen entscheidenden Nachteil, der bei wenigen Datensätzen nicht direkt sichtbar wird, jedoch bereits bei höherer Zahl an Datensätzen massiv zu erkennen ist: Performance-Verlust.

 

In jedem Schleifendurchlauf erfolgt ein direkter Zellzugriff um die Zielzelle mit einem Wert zu befüllen. Bei einer fiktiven Zahl an Datensätzen von z.B. 2.000 Datensätzen, mit je 10 Attributen (Spalten), erfolgen also mit obigem Code 20.000 einzelne Zellzugriffe. Bei jedem dieser Zellzugriffe werden intern alle Zellinformationen kurzzeitig ausgelesen, was zu einem enormen Zeitverlust und somit Performanceverlust führt.

 

Dieser Nachteil lässt sich jedoch leicht umgehen, indem man den Zell- bzw. Bereichszugriff auf ein Minimum reduziert – auf einen Zugriff. Dazu werden die Daten zuvor intern im Speicher berechnet und in ein Variant/Array geschrieben und anschließend „in einem Rutsch“ in den Zielbereich geschrieben.

 

'[…]

Dim varData As Variant

Dim lngRowsMax, lngColsMax As Long

 

lngRowsMax = rngTarget.Rows.Count

lngColsMax = rngTarget.Rows.Count

 

'Variant/Arraydimensionieren

ReDim varData(1 To lngRowsMax, 1 To lngRowsMax)

 

'Alternativ lässt sich der Zielbereich in das Variant/Array "pressen" und dieses so dimensionieren

'varData = rngTarget

 

'Daten berechnen und ins Variant/Array schreiben

For lngRow = 1 To lngRowsMax

       For lngCol = 1 To lngRowsMax

              varData(lngRow, lngCol) = m_getDataRecalculation(lngRow, lngCol)

       Next lngCol

Next lngRow

 

'Nach der Beschreibung des Variants, dieses wieder in den Zielbereich schreiben

rngTarget = varData

 

Auf den ersten Blick erscheint dieser Code etwas aufgebläht, erfüllt jedoch bestens seinen Zweck.Der Zugriff auf die Variant-"Zellen" ist um ein vielfaches schneller als ein direkter Zellzugriff. Damit veringert sich die Zeit für diesen Prozess auf ein Minimum.

Wenn Sie beide Codes einmal verwenden für eine Zahl von 2.000 Datensätzen á 10 Spalten, werden Sie den deutlichen Unterschied feststellen.