3. April 2012 - 20:32 Uhr
In letzter Zeit schreibe ich öfter als mir lieb ist in Sprachen, die ich nicht mag. Lehrreich ist das ja trotzdem immer. Mein Ziel war es, in Excel einen Filter für eine Tabelle zu schreiben, der sich über mehrere Zellen erstreckt. Dabei sollte eine Zeile angezeigt werden, wenn in einer Zelle ein Teil der Zeichenkette ein bestimmtes Argument enthält. Der Autofilter hilft einem da wenig weiter (glaube ich jedenfalls).
Meine Grundidee war eine doppelt verschachtelte For-Schleife, die das Argument sucht und bei Nicht-finden die vollständige Zeile ausblendet.
Der erste Versuch hat zwar auch gleich funktioniert, war allerdings nicht unbedingt der Performanteste. Milde ausgedrückt, denn die Verarbeitung von gut 400 Zeilen über acht Spalten dauerte gut 90 bis 100 Sekunden. Dabei flackerte der Bildschirm lustig vor sich hin und man hörte sämtliche Lüfter der Maschine anspringen, was ja irgendwie auf eine erhöhte Prozessortätigkeit hindeutet :-)
Ein kurzer Besuch bei Onkel Google brachte mir die Erkenntnis, dass alleine die Zeilen
Application.ScreenUpdating = False
//komplizierter Algorithmus
Application.ScreenUpdating = True
Wunder bewirken können. Von anfänglichen 100 Sekunden blieben noch - je nach Konstellation - 30 bis 40 Sekunden übrig. Natürlich ist das auch nichts für den alltäglichen Gebrauch und so kam ich auf die Idee, mal zu prüfen, warum der verbleibende Code eigentlich so langsam ist, bzw. wo sich das Bottleneck befindet.
Es handelte sich eindeutig um die Ausblende-Funktion von einzelnen Zeilen oder Spalten, die bei mir direkt in der Schleife ausgeführt wurde. Als ich diese Zeile auskommentierte, verblieben etwa 0,7 Sekunden, die es zur Bearbeitung des vollständigen Codes bedurften.
Ohne das Ausblenden ist allerdings die vollständige Routine überflüssig, so dass ich auf die Idee kam, die auszublendenden Zeilen alle vorzuselektieren um sie anschließend auf einen Schlag auszublenden. Dazu musste ich nur verstehen, wie man eine Range (Excel-VBA-Kenner wissen, was gemeint ist) erstellt, die keinen zusammenhängenden Bereich beinhaltet, sondern mehrere Insel-Bereiche vereint. Also in etwa einer Mehrfach-Selektion gleichend.
Und siehe da: So etwas gibt es wirklich. Die fehlende Funktion heißt Union() und nimmt mehrere Ranges entgegen und vereinigt diese zu einer großen Range. Etwa so
Dim tmpRange1 as Range
Dim tmpRange2 as Range
Dim gesRange As Range
Set tmpRange1 = Range("A3")
Set tmpRange2 = Range("A17")
Set gesRange = Union(tmpRange1, tmpRange2)
gesRange.Select
Schaut man sich nun das Excel-Sheet an, sind die Zellen A3 und A17 selektiert. Der große Vorteil ist natürlich im Beispiel nicht erkennbar. Die temporären Ranges kann man sich hervorragend in einer Schleife setzen und zur großen Range hinzufügen. Das geht ohne markante Zeitverzögerung.
Am Ende außerhalb der Schleife habe ich dann die Range selektiert und die enthaltenen Zeilen ausgeblendet, also die zeitkritische Funktion. Die Zeit für die vollständige Verarbeitung liegt am Ende bei etwa 1 bis 2 Sekunden.
Ich hatte bisher nie großen Spaß an Code-Optimierung, aber in diesem Fall macht die Erkenntnis und das Ergebnis sehr glücklich.
Für alle, die bis zu diesem Punkt durchgehalten haben: Hut ab! Zur Belohung der vollständige Funktions-Code als Sahnehäubchen. Wer noch mehr Verbesserungen findet, ab in die Kommentare damit.
digit ist übrigens die Zahl, die in die Funktion als Suchparameter eingegeben wurde.
Set Bereich = Range(Cells(StartZeile, StartSpalte), Cells(EndZeile, EndSpalte))
For Each Zelle In Bereich
txt = Zelle.Value
If Len(txt) > 0 Then
If InStr(txt, "-") Then
aTXT = Split(txt, "-")
found = False
For u = 0 To UBound(aTXT)
If (aTXT(u) = digit) Then
found = True
Exit For
End If
Next
If Not found Then
'Die temporäre Adresse wird zum Ausblenden vorgemerkt
Set rTmp = Range(Zelle.Address)
End If
Else
vorhTXT = CInt(Trim(txt))
If (vorhTXT digit) Then
'Wenn die Zahl in der Zelle nicht der gesuchten Zahl entspricht, wird die
'Zeile zum Ausblende vorgemerkt
Set rTmp = Range(Zelle.Address)
End If
End If
'Auswertung der vorgemerkten Zellen:
'Wenn eine solche existiert, dann
If Not rTmp Is Nothing Then
If Not rGes Is Nothing Then
'füge sie der GesamtRange zu
Set rGes = Union(rGes, rTmp)
Set rTmp = Nothing
Else
Set rGes = rTmp
End If
End If
End If
Next Zelle
'Markiere die GesamtRange
rGes.Select
'Blende ihre Zeilen aus
rGes.EntireRow.Hidden = True
'Bildschirmaktualisierung wieder an
Application.ScreenUpdating = True
'Speicher freigeben
Set Bereich = Nothing
Set rGes = Nothing
Der Code-bewanderte Leser sieht, dass der Text in den Zellen, in denen gesucht wird, entweder als einzelne Zahl vorkommt oder in der Form 4-67-23 vorliegt. Jeweils als String formatiert. Anstelle einer For-Schleife habe ich mich aus Performance-Gründen für eine For-Each-Schleife entschieden. Diese soll in VBA schneller als While- oder (klassische) For-Schleifen sein.
Ich hoffe jenen damit zu helfen, die vor einem ähnlichen Problem stehen und dadurch vielleicht auf diesen Artikel stoßen. An alle anderen: ja, mir geht es gut :-)
