VBA – Geschwindigkeit verbessern und andere praktische Tipps

Dieses Tutorial behandelt die Beschleunigung von VBA-Makros und andere bewährte Praktiken in VBA.

Einstellungen zur Beschleunigung von VBA-Code

Im Folgenden finden Sie mehrere Tipps zur Beschleunigung Ihres VBA-Codes. Die Tipps sind grob nach Wichtigkeit geordnet.

Am einfachsten lässt sich die Geschwindigkeit Ihres VBA-Codes durch die Deaktivierung von ScreenUpdating (Bildschirmaktualisierung) und automatischen Berechnungen verbessern. Diese Einstellungen sollten in allen großen Prozeduren deaktiviert werden.

Bildschirmaktualisierung deaktivieren

Standardmäßig zeigt Excel Änderungen an Arbeitsmappen in Echtzeit an, wenn VBA-Code ausgeführt wird. Dies führt zu einer massiven Verlangsamung der Verarbeitungsgeschwindigkeit, da Excel die meisten Änderungen für jede Codezeile interpretieren und anzeigen muss.

So deaktivieren Sie die Bildschirmaktualisierung:

Application.ScreenUpdating = False

Am Ende Ihres Makros sollten Sie die Bildschirmaktualisierung wieder einschalten:

Application.ScreenUpdating = True

Während Ihr Code läuft, könnte es vorkommen, dass Sie den Bildschirm „aktualisieren“ müssen. Es gibt keinen Befehl namens „Aktualisieren“. Stattdessen müssen Sie die Bildschirmaktualisierung wieder aktivieren und dann erneut 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 zusätzlich zur Neuberechnung aller abhängigen Zellen auch den Berechnungsbaum aktualisieren. Je nach Größe Ihrer Arbeitsmappe können diese Neuberechnungen dazu führen, dass Ihre Makros unangemessen langsam laufen.

So setzen Sie die Berechnungen auf Manuell:

Application.Calculation = xlManual

Um die gesamte Arbeitsmappe manuell neu zu berechnen:

Calculate

Beachten Sie, dass Sie auch nur ein Blatt, einen Bereich oder eine einzelne Zelle berechnen können, wenn dies für eine höhere Geschwindigkeit erforderlich ist.

Um die automatischen Berechnungen (am Ende Ihrer Prozedur) wiederherzustellen:

Application.Calculation = xlAutomatic

Wichtig! Dies ist eine Excel-Einstellung. Wenn Sie die automatischen Berechnungen nicht wiederherstellen, wird Ihre Arbeitsmappe erst dann neu berechnet, wenn Sie es anordnen.

Die größten Verbesserungen ergeben sich aus den oben genannten Einstellungen, aber es gibt noch weitere Einstellungen, die einen Unterschied machen können:

Ereignisse deaktivieren

Ereignisse sind „Auslöser“, die die Ausführung spezieller Ereignisprozeduren bewirken. Beispiele dafür sind es, wenn sich eine Zelle auf einem Arbeitsblatt ändert, wenn ein Arbeitsblatt aktiviert wird, wenn eine Arbeitsmappe geöffnet wird, bevor eine Arbeitsmappe gespeichert wird, usw.

Die Deaktivierung von Ereignissen kann zu geringfügigen Geschwindigkeitsverbesserungen führen, wenn Makros ausgeführt werden, aber die Geschwindigkeitsverbesserung kann viel größer sein, wenn Ihre Arbeitsmappe Ereignisse verwendet. Und in einigen Fällen ist die Deaktivierung von Ereignissen notwendig, um Endlosschleifen zu vermeiden.

So deaktivieren Sie Ereignisse:

Application.EnableEvents = False

Um Ereignisse wieder einzuschalten:

Application.EnableEvents = True

Seitenumbrüche deaktivieren

Die Deaktivierung von Seitenumbrüchen kann in bestimmten Situationen hilfreich sein:

• Sie haben zuvor eine PageSetup-Eigenschaft für das betreffende Arbeitsblatt festgelegt und Ihre VBA-Prozedur ändert die Eigenschaften vieler Zeilen oder Spalten
• ODER Ihre VBA-Prozedur zwingt Excel, Seitenumbrüche zu berechnen (durch Anzeige der Druckvorschau oder Änderung von Eigenschaften von PageSetup).

So deaktivieren Sie Seitenumbrüche:

ActiveSheet.DisplayPageBreaks = False

Um Seitenumbrüche wieder zu aktivieren:

ActiveSheet.DisplayPageBreaks = True

Best Practices zur Verbesserung der VBA-Geschwindigkeit

Aktivieren und Auswählen vermeiden

Wenn Sie ein Makro aufzeichnen, werden Sie viele Activate- und Select-Methoden sehen:

Sub Langsames_Beispiel()
 Sheets("Tabelle2").Select
 Range("D9").Select
 ActiveCell.FormulaR1C1 = "Beispiel"
 Range("D12").Select
 ActiveCell.FormulaR1C1 = "Demo"
 Range("D13").Select
End Sub

Das Aktivieren und Auswählen von Objekten ist in der Regel unnötig, macht Ihren Code unübersichtlich und ist sehr zeitaufwändig. Sie sollten diese Methoden wenn möglich vermeiden.

Verbessertes Beispiel:

Sub Schnelles_Beispiel()
 Sheets("Tabelle2").Range("D9").FormulaR1C1 = "Beispiel"
 Sheets("Tabelle2").Range("D12").FormulaR1C1 = "Demo"
End Sub

Kopieren und Einfügen vermeiden

Das Kopieren erfordert viel Speicherplatz. Leider können Sie VBA nicht anweisen, den internen Speicher zu löschen. Stattdessen löscht Excel den internen Speicher in (scheinbar) bestimmten Abständen. Wenn Sie also viele Kopier- und Einfügevorgänge durchführen, besteht die Gefahr, dass Sie zu viel Speicher verbrauchen, was Ihren Code drastisch verlangsamen oder Excel sogar zum Absturz bringen kann.

Anstatt zu kopieren und einzufügen, sollten Sie die Werteigenschaften von Zellen festlegen.

Sub KopierenEinfuegen()

 'Langsamer
 Range("a1:a1000").Copy Range("b1:b1000")

 'Schneller
 Range("b1:b1000").Value = Range("a1:a1000").Value

End Sub

For Each-Schleifen anstelle von For-Schleifen verwenden

Beim Durchführen von Schleifen durch Objekte ist die For Each-Schleife schneller als die For-Schleife. Beispiel:

Diese For-Schleife:

Sub Schleife1()
 dim i as Integer
 For i = 1 To 100
 Cells(i, 1).Value = 1
 Next i
End Sub

Sub Schleife2()
 Dim Zelle As Range
 For Each Zelle In Range("a1:a100")
 Zelle.Value = 1
 Next Zelle
End Sub

Variablen deklarieren / Option Explicit verwenden

Option Explicit

Sub OptionExplicit()
 var1 = 10
 MsgBox varl
End Sub

Verwendung von With…End With-Anweisungen

Sub Schnelles_Beispiel()
 With Sheets("Tabelle2")
.Range("D9").FormulaR1C1 = "Beispiel"
.Range("D12").FormulaR1C1 = "Demo"
.Range("D9").Font.Bold = True
.Range("D12").Font.Bold = True
 End With
End Sub

Sub Langsames_Beispiel()
 Sheets("Tabelle2").Range("D9").FormulaR1C1 = "Beispiel"
 Sheets("Tabelle2").Range("D12").FormulaR1C1 = "Demo"
 Sheets("Tabelle2").Range("D9").Font.Bold = True
 Sheets("Tabelle2").Range("D12").Font.Bold = True
End Sub

Erweiterte Tipps zu Best Practices

Schutzoption UserInterfaceOnly

Es ist eine gute Praxis, 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. Dadurch werden jedoch auch die Arbeitsblätter davor geschützt, dass VBA Änderungen vornehmen kann. Sie müssen also den Schutz aufheben und die Arbeitsblätter erneut schützen, was sehr zeitaufwändig ist, wenn Sie viele Blätter bearbeiten.

Sub BlattschutzAufheben()
 Sheets("Tabelle1").Unprotect "Passwort"
 'Tabelle1 bearbeiten
 Sheets("Tabelle1").Protect "Passwort"
End Sub

Stattdessen können Sie Blätter mit der Einstellung UserInterfaceOnly:=True schützen. Dadurch kann VBA Änderungen an Blättern vornehmen, während sie gleichzeitig vor dem Benutzer geschützt sind.

Sheets("Tabelle1").Protect Passwort:="Passwort", UserInterFaceOnly:=True

Wichtig! UserInterFaceOnly wird jedes Mal, wenn die Arbeitsmappe geöffnet wird, auf False zurückgesetzt. Um diese großartige Funktion zu nutzen, müssen Sie also die Ereignisse Workbook_Open oder Auto_Open verwenden, um die Einstellung bei jedem Öffnen der Arbeitsmappe festzulegen.

Fügen Sie den folgenden Code in das Modul Thisworkbook ein:

Private Sub Workbook_Open()
 Dim ws As Worksheet
 For Each ws In Worksheets
 ws.Protect Password:="Passwort", UserInterFaceOnly:=True
 Next ws
End Sub

oder dieser Code in einem beliebigen regulären Modul:

Private Sub Auto_Open()
 Dim ws As Worksheet
 For Each ws In Worksheets
 ws.Protect Password:="Passwort", UserInterFaceOnly:=True
 Next ws
End Sub

Arrays verwenden, um große Bereiche zu bearbeiten

Es kann sehr zeitaufwändig sein, große Zellenbereiche (z. B. 100.000) zu bearbeiten. Anstatt die Zellenbereiche in einer Schleife zu durchlaufen und jede Zelle zu bearbeiten, können Sie die Zellen in ein Array laden, jedes Element im Array bearbeiten und dieses dann wieder in die ursprünglichen Zellen ausgeben. Das Laden der Zellen in Arrays zur Bearbeitung kann viel schneller sein.

Sub BereichDurchlaufen()

 Dim Zelle As Range
 Dim tStart As Double

 tStart = Timer

 For Each Zelle In Range("A1:A100000")
 Zelle.Value = Zelle.Value * 100
 Next Zelle

 Debug.Print (Timer - tStart) & " seconds"

End Sub

Sub ArrayDurchlaufen()

 Dim arr As Variant
 Dim Element As Variant
 Dim tStart As Double

 tStart = Timer

 arr = Range("A1:A100000").Value
 For Each Element In arr
 Element = Element * 100
 Next Element
 Range("A1:A100000").Value = arr

 Debug.Print (Timer - tStart) & " seconds"

End Sub