Wenn Excel Makros langsam laufen, weil sie immer komplexeren Anforderungen genügen müssen, kann man folgende Tricks anwenden, um eine schnellere Laufzeit zu ermöglichen:
VBA Tipps
Einfaches Caching einbauen
siehe Beitrag VBA Excel Key-Value Store Cache mit Dictionary
Einfaches Profiling um Engpässe in der Applikation zu finden
Die Laufzeit bestimmter Berechnungen lässt sich bestimmen, indem man in den VBA Code an passender Stelle Zeitmessungen platziert und auf der Debug Console ausgeben lässt. Damit kann analysiert werden, welche Teile der Applikation sich langsam verhalten.
Events deaktivieren währen der Scriptlaufzeit
Damit keine Events geworfen werden wie z.B.:
Private Sub Worksheet_Change(ByVal Target As Range)
kann man das Eventwerfen verhindern und so unnötige Excel-Events verhindern:
Application.EnableEvents = False ... 'your macro ... Application.EnableEvents = True
Aktualisieren der View deaktivieren
Es nimmt viel Performance in Anspruch, jedes mal auch die Excel Tabelle View für den Benutzer zu refreshen, wenn durch das Marko Änderungen am Frontend vorgenommen wurden. Das kann man für die Laufzeit des Makros deaktivieren und am Ende wieder aktivieren, damit wieder alles normal funktioniert.
Application.ScreenUpdating= False ... 'your macro ... Application.ScreenUpdating= True
Debug.print nicht verwenden
Die Ausgabe von Informationen zur Laufzeit auf den Direktbereich im Makro Editor ist sehr praktisch um einen Überblick zu erhalten, was im Programm zur Laufzeit passiert. Jedoch benötigt auch dies Performance und ein Refreshen der View und sollten auf ein Minimum (1 mal Minute) beschränkt werden bei langlaufenden Skripten, um Performance zu gewinnen.
Also besser auskommtieren von viel verwendeten Ausgaben:
'debug.print("SPAM")
unnötige Schleifendurchläufe vermeiden
Eine for Schleife kann meist vorzeitig verlassen oder Berechnungen übersprungen werden:
For index As Integer = 1 To 100000 ' If index is between 5 and 7, continue ' with the next iteration. If index >= 5 And index <= 8 Then Continue For End If ' Display the index. Debug.Write(index.ToString & " ") ' If index is 10, exit the loop. If index = 10 Then Exit For End If Next
Auch Goto kann dafür verwendet werden, wenn es aus Performancegründen positiv ist und der Code logisch und verständlich bleibt.
Sub gotoStatementDemo() Dim number As Integer = 1 Dim sampleString As String ' Evaluate number and branch to appropriate label. If number = 1 Then GoTo Line1 Else GoTo Line2 Line1: sampleString = "Number equals 1" GoTo LastLine Line2: ' The following statement never gets executed because number = 1. sampleString = "Number equals 2" LastLine: ' Write "Number equals 1" in the Debug window. Debug.WriteLine(sampleString) End Sub
Damit lassen sich unötige Operationen überspringen. Es gehört allerdings zum schlechten Programmierstil und sollte bedacht eingesetzt werden.
Hardware Tuning: MacBook Pro vs Thinkpad
Thinkpad X220: 2,5GHz Intel Core i5, 8GB Ram, SSD, Windows 8
MacBook Pro: 2,5GHz Intel Core i5, 16GB Ram, SSD, OS X 10.9
Ergebnis: Das MacBook Pro war um 94% schneller im Vergleich zu dem Thinkpad, wobei bei Windows PCs auch das Betriebsystemalter eine große Rolle spielt. Mein Windows System ist mit der zeit viel langsamer geworden.
Eine Investition in gute Hardware lohnt sich, für Mac User das MacBook Pro und unter Windows ein Lenovo Yoga lohnt sich bei langsamen Makros.