Viele Programme, welche mit großen Datenmengen herumhantieren und komplizierte Berechnungen anstellen, kommen oft an den Punkt, an dem deren lange Laufzeit allmählig zum Problem wird. VBA-Programme, die unter Excel laufen, sind da keine Ausnahmen. Eher die Regel – vor allem dann, wenn das Programm dauernd Inhalte in Zellen reinschreibt. Denn bei jedem solcher Schreibvorgänge gehen wertvolle Millisekunden für die Bildschirmaktualisierungen und automatische Berechnungen anderer Zellen drauf. Nebenbei erwähnt, führt dies auch zum unschönen Flackern des Bildschirms während des Programmlaufs. Stöbert man in zahlreichen Foren, begegnet man oft dem Vorschlag, die Bildschirmaktualisierung sowie automatische Zellenberechnungen vor dem Beginn der Verarbeitung auszuschalten und sie nach der Verarbeitung wieder zu aktivieren.

Wie so oft, steckt auch bei diesem wirklich wirkungsvollen Vorschlag der Teufel im Detail, welches der Gegenstand dieses Beitrags sein soll.

Ausgangsbeispiel – nicht laufzeitoptimiertes Programm

Um zu verdeutlichen, was bei dieser Optimierung eventuell in die Hose gehen kann, schauen wir uns ein kleines Beispiel an. Im unten aufgeführten Listing sehen wir eine kurze lauffähige Prozedur, die an ihrer Einfachheit kaum zu überbieten ist. In jede Zelle in der Spalte 1 wird der Index reingeschrieben und in die daneben liegende Zelle der Spalte 2 wird eine Formel eingetragen, welche den Inhalt der Nachbarzelle mit 2 multipliziert. Und das ganze für 50.000 Zeilen. Dieser Code – stellvertretend für eine echte produktive Verarbeitung, die mit Sicherheit mit deutlich komplexeren Berechnungen und vielleicht auch mit mehr an Datensätzen daher kommt, benötigt in etwa 29 Sekunden.

Private Sub testDurchlauf()

    Dim lngZaehler          As Long
    Dim dblTestErgebnis     As Double
        
    For lngZaehler = 1 To 50000 
		With Tabelle1
			.Cells(lngZaehler, 1).Value = lngZaehler
			.Cells(lngZaehler, 2).FormulaLocal = "=" & .Cells(lngZaehler, 1).Address & " * 2 "
		End With
    Next lngZaehler

End Sub

Ausgangsbeispiel – gefährliche Laufzeitoptimierung ohne Fehlerbehandlung

Probieren wir nun die vorgeschlagene Optimierung mit dem Ausschalten der automatischen Berechnung aus.

Private Sub testDurchlauf()

    Dim lngZaehler          As Long
    Dim dblTestErgebnis     As Double
        
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For lngZaehler = 1 To 50000 
		With Tabelle1
			.Cells(lngZaehler, 1).Value = lngZaehler
			.Cells(lngZaehler, 2).FormulaLocal = "=" & .Cells(lngZaehler, 1).Address & " * 2 "
		End With
    Next lngZaehler
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
	
End Sub

Das Resultat kann sich wirklich sehen lassen. Mit einer StoppuhrApp bewaffnet, habe ich Pi Mal Daumen eine Verbesserung der Laufzeit um den Faktor 7 feststellen können! In einer idealen Welt, in der es keine Laufzeitfehler gibt, könnten wir uns als Entwickler für diese tolle Optimierung jetzt schon gegenseitig auf die Schultern klopfen.

In der echten Welt müssen wir der Tatsache in die Augen schauen, dass eine Routine auch mal abbrechen kann. Spielen wir mal dieses Szenario durch und schauen, was dabei passiert.

Ein Anwender lässt diese Routine laufen und bemerkt, dass sie ihren Dienst mitten in der Verarbeitung quittiert. Er benachrichtigt den Entwickler, der das Problem gleich lokalisieren und beheben kann, sodass das Programm nach dem Neustart ohne Probleme durchläuft. Im Prinzip ist nichts passiert.

Ein paar Tage später lässt der Anwender die Routine erneut laufen, welche dann wieder abbricht, diesmal ist der Entwickler nicht greifbar, sodass der Anwender sich erstmal seinen anderen Aufgaben widmet – er hat nämlich noch eine große Exceltabelle zu aktualisieren. Wenn er die aufmerksam pflegt, wird er vielleicht feststellen, dass sich die Werte, die durch Formeln berechnet werden, nicht mehr automatisch aktualisieren.

Was ist da genau passiert? Naja, durch den Abbruch, der sich nicht gleich beheben ließ, ist die Aktivierung der Bildschirmaktualisierung und der automatischen Zellenberechnung am Ende der Routine nicht ausgeführt worden. Das bedeutet, dass im gesamten Excel auf dem jeweiligen Rechner die automatische Zellenberechnung deaktiviert ist – solange bis die abgebrochene Routine wieder repariert und durch ihren erfolgreichen Lauf die automatische Berechnung wieder aktiviert hat.

Gerade diese Besonderheit bleibt leider selbst in der Fachliteratur oft unerwähnt. Klar – wird die Datei gespeichert, stößt Excel eine explizite Berechnung aller Zellen an. Wenn es allerdings ungünstig läuft und man z.B einen PDF-Abzug daraus macht, kann es passieren, dass dabei nicht aktualisierte Werte weitergegeben werden. Die Fehlerbehandlung ist, neben Doku und Test, zwar nicht die Lieblingsaufgabe vieler Entwickler, zeigt aber in der Praxis immer ihre Daseinsberechtigung. Wie würde denn unsere Beispielroutine aussehen, um das Problem gar nicht erst entstehen zu lassen?

Nun, dies sehen wir im unten stehenden Listing. Eins vorweg: in Sachen Fehlerbehandlung muss man in VBA auf prähistorische Konstrukte wie GoTo zurückgreifen, die bei uns Entwicklern wahlweise Herzrasen / Tränen in den Augen hervorrufen können:-) Diese Anweisung bewirkt, dass nach dem Abbruch an die Stelle fehlerbehandlung gesprungen wird, wo noch Aufräumarbeiten (nämlich das Wiederaktivieren der automatischen Berechnung) durchgeführt werden.

Zeitgemäße Fehlerbehandlungstechniken wie try-catch sucht man in VBA leider vergebens – inkonsequenterweise gibt es mit dem Raise-Befehl allerdings ein Äquivalent zu Throw, um eigene Exception auslösen zu können.

Ausgangsbeispiel – mit Laufzeitoptimierung und Fehlerbehandlung

Private Sub testDurchlauf()

    Dim lngZaehler          As Long
    Dim dblTestErgebnis     As Double
    
    On Error GoTo fehlerbehandlung
    
    Call deaktivierung
    
    For lngZaehler = 1 To 50000 
		With Tabelle1
			.Cells(lngZaehler, 1).Value = lngZaehler
			.Cells(lngZaehler, 2).FormulaLocal = "=" & .Cells(lngZaehler, 1).Address & " * 2 "
		End With
    Next lngZaehler
    
    Call aktivierung

    Exit Sub
    
fehlerbehandlung:
    
    Call aktivierung
    MsgBox "ein kritischer Fehler ist aufgetreten", vbCritical

End Sub

Private Sub aktivierung()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub deaktivierung()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
End Sub

Böse Zungen würden vielleicht sagen, selber schuld, wenn man sich freiwillig VBA antut. Sie haben insofern schon recht, dass VBA einige Aspekte moderner Programmiersprachen (vor allem aus dem Bereich Vererbung und Fehlerbehandlung) nicht kennt oder nicht vollständig unterstützt. Auf der anderen Seite kann man eigentlich ganz einfach mit allen Programmiersprachen, ob veraltet oder modern, viel Schindluder betreiben und kaum wartbare Software schreiben. Aus meiner Sicht ist das Know-How und die Erfahrung des Entwicklers in der Regel eher ausschlaggebend als die eingesetzte Technologie. Mit VBA ist die Entwicklung, gerade im kaufmännischen Umfeld, guter und stabiler Software möglich, wenn der fachliche Umfang der Software überschaubar und man als Entwickler bereit ist, aufgrund VBA-Spezifika mal einen längeren Weg zu gehen.