In diesem Tutorial wird erläutert, wie Sie VBA-Makros und andere bewährte VBA-Methoden beschleunigen.
Einstellungen zur Beschleunigung des VBA-Codes
Im Folgenden finden Sie einige Tipps, um Ihren VBA-Code zu beschleunigen. Die Tipps sind lose nach Wichtigkeit geordnet.
Der einfachste Weg, die Geschwindigkeit Ihres VBA-Codes zu verbessern, besteht darin, ScreenUpdating und automatische Berechnungen zu deaktivieren. Diese Einstellungen sollten in allen großen Verfahren deaktiviert werden.
Bildschirmaktualisierung deaktivieren
Standardmäßig zeigt Excel Änderungen an Arbeitsmappen in Echtzeit an, während VBA-Code ausgeführt wird. Dies führt zu einer massiven Verlangsamung der Verarbeitungsgeschwindigkeit, da Excel die meisten Änderungen für jede Codezeile interpretiert und anzeigt.
So deaktivieren Sie die Bildschirmaktualisierung:
| 1 | Application.ScreenUpdating = False |
Am Ende Ihres Makros sollten Sie die Bildschirmaktualisierung wieder aktivieren:
| 1 | Application.ScreenUpdating = True |
Während Ihr Code ausgeführt wird, müssen Sie möglicherweise den Bildschirm „aktualisieren“. Es gibt keinen „Aktualisieren“-Befehl. Stattdessen müssen Sie die Bildschirmaktualisierung wieder aktivieren und wieder deaktivieren.
Berechnungen auf Manuell setzen
Immer wenn ein Zellenwert geändert wird, muss Excel dem „Berechnungsbaum“ folgen, um alle abhängigen Zellen neu zu berechnen. Wenn eine Formel geändert wird, muss Excel außerdem den „Berechnungsbaum“ aktualisieren und alle abhängigen Zellen neu berechnen. Je nach Größe Ihrer Arbeitsmappe können diese Neuberechnungen dazu führen, dass Ihre Makros unangemessen langsam ausgeführt werden.
So stellen Sie Berechnungen auf Manuell ein:
| 1 | Anwendung.Berechnung = xlManual |
So berechnen Sie die gesamte Arbeitsmappe manuell neu:
| 1 | Berechnung |
Beachten Sie, dass Sie bei Bedarf auch nur ein Blatt, einen Bereich oder eine einzelne Zelle berechnen können, um die Geschwindigkeit zu verbessern.
So stellen Sie die automatischen Berechnungen wieder her (am Ende Ihres Vorgangs):
| 1 | Anwendung.Berechnung = xlAutomatic |
Wichtig! Dies ist eine Excel-Einstellung. Wenn Sie die Berechnungen nicht auf automatisch zurücksetzen, wird Ihre Arbeitsmappe nicht neu berechnet, bis Sie dies anweisen.
Sie werden die größten Verbesserungen der obigen Einstellungen sehen, aber es gibt mehrere andere Einstellungen, die einen Unterschied machen können:
Ereignisse deaktivieren
Ereignisse sind „Auslöser“, die besonderes bewirken Ereignisprozeduren laufen. Beispiele sind: wenn sich eine Zelle in einem Arbeitsblatt ändert, wenn ein Arbeitsblatt aktiviert wird, wenn eine Arbeitsmappe geöffnet wird, bevor eine Arbeitsmappe gespeichert wird usw.
Das Deaktivieren von Ereignissen kann bei der Ausführung von Makros zu geringfügigen Geschwindigkeitsverbesserungen führen, aber die Geschwindigkeitsverbesserung kann viel größer sein, wenn Ihre Arbeitsmappe Ereignisse verwendet. Und in einigen Fällen ist das Deaktivieren von Ereignissen erforderlich, um das Erstellen von Endlosschleifen zu vermeiden.
So deaktivieren Sie Ereignisse:
| 1 | Application.EnableEvents = False |
So aktivieren Sie Ereignisse wieder:
| 1 | Application.EnableEvents = True |
PageBreaks deaktivieren
Das Deaktivieren von PageBreaks kann in bestimmten Situationen hilfreich sein:
- Sie haben zuvor eine PageSetup-Eigenschaft für das entsprechende Arbeitsblatt festgelegt und Ihre VBA-Prozedur ändert die Eigenschaften vieler Zeilen oder Spalten
- ODER Ihre VBA-Prozedur zwingt Excel, Seitenumbrüche zu berechnen (Anzeigen der Seitenansicht oder Ändern von Eigenschaften von PageSetup).
So deaktivieren Sie PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
So aktivieren Sie PageBreaks wieder:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Best Practices zur Verbesserung der VBA-Geschwindigkeit
Vermeiden Sie das Aktivieren und Auswählen
Wenn Sie ein Makro aufzeichnen, sehen Sie viele Aktivierungs- und Auswahlmethoden:
| 12345678 | Sub Slow_Example()Sheets("Sheet2").SelectBereich("D9").WählenActiveCell.FormulaR1C1 = "Beispiel"Bereich("D12").WählenActiveCell.FormulaR1C1 = "demo"Bereich("D13").WählenEnd Sub |
Das Aktivieren und Auswählen von Objekten ist normalerweise unnötig, sie machen Ihren Code unübersichtlich und sind sehr zeitaufwändig. Sie sollten diese Methoden nach Möglichkeit vermeiden.
Verbessertes Beispiel:
| 1234 | Sub Fast_Example()Sheets("Sheet2").Range("D9").FormulaR1C1 = "Beispiel"Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo"End Sub |
Vermeiden Sie das Kopieren und Einfügen
Das Kopieren erfordert viel Speicher. Leider können Sie VBA nicht anweisen, den internen Speicher zu löschen. Stattdessen löscht Excel seinen internen Speicher in (scheinbar) bestimmten Intervallen. Wenn Sie also viele Kopier- und Einfügevorgänge ausführen, laufen Sie Gefahr, zu viel Speicher zu belegen, was Ihren Code drastisch verlangsamen oder sogar Excel zum Absturz bringen kann.
Anstatt zu kopieren und einzufügen, sollten Sie die Werteigenschaften von Zellen festlegen.
| 123456789 | Sub CopyPaste()'LangsamerRange("a1:a1000").Copy Range("b1:b1000")'SchnellerRange("b1:b1000").Value = Range("a1:a1000").ValueEnd Sub |
Verwenden Sie die For Each-Schleifen anstelle von For-Schleifen
Beim Durchlaufen von Objekten ist die For Each-Schleife schneller als die For-Schleife. Beispiel:
Diese For-Schleife:
| 123456 | Unterschleife1()dim i als RangeFür i = 1 bis 100Zellen(i, 1).Wert = 1Als nächstesEnd Sub |
| 123456 | Unterschleife2()Zelle als Bereich dimmenFür jede Zelle im Bereich("a1:a100")Zelle.Wert = 1Nächste ZelleEnd Sub |
Variablen deklarieren / Option Explizit verwenden
VBA erfordert nicht, dass Sie Ihre Variablen deklarieren, es sei denn, Sie fügen Option Explicit am Anfang Ihres Moduls hinzu:| 1 | Option explizit |
| 1234 | UnteroptionExplicit()var1 = 10MsgBox varlEnd Sub |
Verwenden mit - Ende mit Anweisungen
Wenn Sie mehrmals auf dieselben Objekte verweisen (z. B. Bereiche, Arbeitsblätter, Arbeitsmappen), sollten Sie die With-Anweisung verwenden. Es ist schneller zu verarbeiten, kann Ihren Code leichter lesbar machen und Ihren Code vereinfachen.Mit Aussagebeispiel:| 12345678 | Sub Faster_Example()Mit Sheets("Sheet2").Range("D9").FormulaR1C1 = "Beispiel".Range("D12").FormulaR1C1 = "demo".Range("D9").Font.Bold = True.Range("D12").Font.Bold = TrueEnde mitEnd Sub |
| 123456 | Sub Slow_Example()Sheets("Sheet2").Range("D9").FormulaR1C1 = "Beispiel"Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo"Sheets("Sheet2").Range("D9").Font.Bold = TrueSheets("Sheet2").Range("D12").Font.Bold = TrueEnd Sub |
Fortgeschrittene Best-Practice-Tipps
Nur UserInterface schützen
Es empfiehlt sich, Ihre Arbeitsblätter vor der Bearbeitung ungeschützter Zellen zu schützen, um zu verhindern, dass der Endbenutzer (oder Sie!) die Arbeitsmappe versehentlich beschädigt. Dies schützt jedoch auch das/die Arbeitsblatt(e) davor, dass VBA Änderungen vornehmen kann. Daher müssen Sie den Schutz von Arbeitsblättern aufheben und erneut schützen, was bei vielen Arbeitsblättern sehr zeitaufwändig ist.
| 12345 | Unter UnProtectSheet()Sheets("sheet1").Unprotect ”password”'Blatt 1 bearbeitenSheets("sheet1").Schützen Sie "Passwort"End Sub |
Stattdessen können Sie Blätter mit der Einstellung UserInterfaceOnly:=True schützen. Auf diese Weise kann VBA Änderungen an Blättern vornehmen und sie dennoch vor dem Benutzer schützen.
| 1 | Sheets("sheet1").Protect Password:="password", UserInterFaceOnly:=True |
Wichtig! UserInterFaceOnly wird jedes Mal auf False zurückgesetzt, wenn die Arbeitsmappe geöffnet wird. Um diese großartige Funktion zu verwenden, müssen Sie also die Ereignisse Workbook_Open oder Auto_Open verwenden, um die Einstellung jedes Mal festzulegen, wenn die Arbeitsmappe geöffnet wird.
Fügen Sie diesen Code in das Modul Thisworkbook ein:
| 123456 | Private Unterarbeitsmappe_Open()Dim ws als ArbeitsblattFür jedes ws in Arbeitsblätternws.Protect Password:="password", UserInterFaceOnly:=TrueNächstes wsEnd Sub |
oder dieser Code in einem regulären Modul:
| 123456 | Privates Sub Auto_Open()Dim ws als ArbeitsblattFür jedes ws in Arbeitsblätternws.Protect Password:="password", UserInterFaceOnly:=TrueNächstes wsEnd Sub |
Verwenden von Arrays zum Bearbeiten großer Bereiche
Es kann sehr zeitaufwändig sein, große Zellbereiche (z. B. 100.000+) zu manipulieren. Anstatt Zellenbereiche zu durchlaufen und jede Zelle zu manipulieren, können Sie die Zellen in ein Array laden, jedes Element im Array verarbeiten und dann das Array wieder in ihre ursprünglichen Zellen ausgeben. Das Laden der Zellen in Arrays zur Manipulation kann viel schneller sein.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange()Zelle als Bereich dimmenDim tAls Double startentStart = TimerFür jede Zelle im Bereich("A1:A100000")cell.Value = cell.Value * 100Nächste ZelleDebug.Print (Timer - tStart) & "Sekunden"End SubSub-LoopArray()Dim arr als VarianteArtikel als Variante dimmenDim tAls Double startentStart = Timerarr = Bereich("A1:A100000").WertFür jeden Artikel In arrArtikel = Artikel * 100Nächstes ObjektBereich("A1:A100000").Wert = arrDebug.Print (Timer - tStart) & "Sekunden"End Sub |
