Manchmal steht man als Anwender oder als Programmierer vor einem VBA-Programm, welches seine Verarbeitung zwar zuverlässig und fachlich richtig durchführt, aber dennoch einfach zu lange dafür braucht. Lange Laufzeit ist zwar per se kein großes Problem – wenn das Programm beispielsweise nur monatlich läuft und dafür 5 Stunden braucht, ist es mit Sicherheit verschmerzbar. Handelt es sich jedoch um ein täglich laufendes Programm, womöglich noch um eins, dessen Verarbeitungsergebnis noch aufwändig zu prüfen ist, dann stellt die lange Laufzeit in der Tat ein größeres Problem dar. Wenn Sie vor vor einem ähnlichen Problem stehen und sich fragen, wie VBA Performanceoptimierung angehen können, helfe ich Ihnen gerne mit meiner Expertise.
Warum sind Excel Programme (VBA) zu langsam?
In der Regel lassen sich fast alle Performanceprobleme auf diese beiden Ursachen zurückführen:
- nicht effiziente Verarbeitung / Programmablauf
- zahlreiche Interaktionen mit Excel-Tabellen
Wie sieht meine Unterstützung konkret aus?
Die effektivste Möglichkeit ist eine für Sie kostenfreie halbstündige Videokonferenz, wo ich mir einen groben Überblick über Ihr Programm und seine Fachlichkeit mit Ihnen zusammen verschaffe. Anschließend kann ich relativ gut einschätzen, ob und welche Ersparnis durch Optimierungen möglich ist und wie aufwändig diese sind.
Wenn Sie nach meiner Einschätzung auch der Meinung sind, dass sich die Performanceoptimierung für Sie lohnt, besprechen wir die nächsten Schritte und Sie erhalten von mir nach wenigen Tagen das optimierte Programm. Gerne gehen wir die einzelnen Optimierungspunkte durch, wenn Sie dies technisch genau nachvollziehen möchten.
Wie läuft die Optimierung technisch ab?
Um die Performance bestehender VBA-Programme zu verbessern, bediene ich mich als erfahrener Softwareentwickler unterschiedlicher „Werkzeugkoffer“, die ich in den folgenden Absätzen etwas genauer vorstelle.
Optimierungen, die den Quellcode nicht verändern
Zu dieser Kategorie gehören die ganz einfachen, allgemein bekannten und schnell umsetzbaren Instrumente. Sie lassen ihr Excel Programm schneller ablaufen ohne seine Verarbeitungslogik zu verändern. Ein Beispiel einer solchen Optimierung wäre das Deaktivieren von automatischen Formelberechnungen (Application.Calculation = xlCalculationManual) und Bildschirmaktualisierungen (Application.Screenupdating=false) während der Programmlaufzeit. Je nach dem, wie stark das Programm mit Tabellenelementen interagiert, sind dabei Laufzeitverbesserungen von 20%-30% möglich.
Optimierungen, die den Quellcode nur geringfügig verändern
Während die Optimierungen aus der ersten Kategorie für fast jedes VBA-Programm einsetzbar sind, eignen sich die etwas komplexeren Optimierungen, die ich hier vorstelle, nicht für jeden Anwendungsfall. Diese Optimierungen sind aufwändiger umzusetzen, ermöglichen jedoch viel höhere Optimierungsgrade. Hier sind einige der Optimierungsmaßnahmen:
- Mit einer Sortierung der Datensätze vor der Verarbeitung ist ein deutlich effizienteres Durchsuchen der Daten möglich. Sollen beispielsweise nur Datensätze eines bestimmten Zeitraums verarbeitet werden, so ist durch die Sortierung möglich, die Verarbeitung vorzeitig zu beenden, sobald das Programm die Datensätze außerhalb des erforderlichen Zeitraum erreicht hat. So wird das unnötige Durchlaufen verhindert.
- das Umsteigen auf passendere Datenstrukturen, also Arrays, Dictionarys oder Collections, ermöglicht teilweise enorme Performanceverbesserungen. Werden im Programm beispielsweise Daten verwaltet, die eindeutige Schlüsselwerte haben, so lohnt es sich, diese Daten nicht im Array, sondern im Dictionary zu verwalten. Denn der Zugriff auf eindeutige Schlüssel erfolgt im Dictionary direkt durch die Angabe des Schlüssels, während dafür ein Array im ungünstigsten Fall komplett durchlaufen werden muss.
- Viele VBA-Programme haben die Besonderheit, dass sie mit Excelsheets interagieren und die Daten direkt aus den einzelnen Zellen auslesen. Dieser Direktzugriff auf Tabellenelemente ist vor allem bei großen Datenmengen recht ineffizient. In vielen Programmen macht es deswegen viel mehr Sinn, die Tabelleninhalte vor der Verarbeitung direkt in eine passende interne Datenstruktur einzulesen, sie direkt im Hauptspeicher zu verarbeiten und erst nach erfolgter Verarbeitung in Excel auszugeben.
komplette Neuentwicklung laufzeitintensiver Funktionen
Wenn bereits vor der Optimierung absehbar ist, dass diese Ihr Programm nicht auf die gewünschte Performance bringen, können wir auch prüfen, ob die Programmierung mit einer anderen Technologie sinnvoller ist. Je nach Art der Verarbeitung können Technologien abseits von VBA um einiges schneller sein. Aufgrund des höheren Aufwandes macht dies in der Regel dann Sinn, wenn die Performance das absolut entscheidende und übergeordnete Ziel ist.
Stehen Sie vor der Fragestellung, „warum ist VBA denn so langsam“ und „wie mache ich Excel schneller“, dann sprechen Sie mich einfach unverbindlich an.