Da Ihre Excel-Makros immer robuster und komplexer werden, können Sie feststellen, dass sie an Leistung verlieren. Wenn über Makros gesprochen wird, ist das Wort Leistung normalerweise gleichbedeutend mit Geschwindigkeit . Geschwindigkeit ist, wie schnell Ihre VBA-Prozeduren ihre beabsichtigten Aufgaben ausführen. Im Folgenden finden Sie zehn Möglichkeiten, wie Sie Ihre Excel-Makros auf optimalem Leistungsniveau ausführen können.
Blattberechnungen anhalten
Wussten Sie, dass Excel jedes Mal, wenn eine Zelle, die eine Formel in Ihrem Arbeitsblatt beeinflusst, geändert oder manipuliert wird, das gesamte Arbeitsblatt neu berechnet? In Arbeitsblättern mit vielen Formeln kann dieses Verhalten Ihre Makros drastisch verlangsamen.
Sie können die Application.Calculation-Eigenschaft verwenden, um Excel anzuweisen, in den manuellen Berechnungsmodus zu wechseln. Wenn sich eine Arbeitsmappe im manuellen Berechnungsmodus befindet, wird die Arbeitsmappe nicht neu berechnet, bis Sie eine Berechnung explizit durch Drücken der Taste F9 auslösen.
Versetzen Sie Excel in den manuellen Berechnungsmodus, führen Sie Ihren Code aus und wechseln Sie dann zurück in den automatischen Berechnungsmodus.
Untermakro1()
Application.Calculation = xlCalculationManual
'Platzieren Sie Ihren Makrocode hier
Application.Calculation = xlCalculationAutomatic
End Sub
Das Zurücksetzen des Berechnungsmodus auf xlCalculationAutomatic löst automatisch eine Neuberechnung des Arbeitsblatts aus, sodass Sie nach der Makroausführung nicht die Taste F9 drücken müssen.
Deaktivieren der Blattbildschirmaktualisierung
Möglicherweise stellen Sie fest, dass Ihr Bildschirm beim Ausführen Ihrer Makros ziemlich flackert. Dieses Flackern ist, dass Excel versucht, den Bildschirm neu zu zeichnen, um den aktuellen Status des Arbeitsblatts anzuzeigen. Leider beansprucht Excel jedes Mal, wenn es den Bildschirm neu zeichnet, Speicherressourcen.
Sie können die Application.ScreenUpdating-Eigenschaft verwenden, um Bildschirmaktualisierungen zu deaktivieren, bis Ihr Makro abgeschlossen ist. Das Deaktivieren der Bildschirmaktualisierung spart Zeit und Ressourcen, sodass Ihr Makro etwas schneller ausgeführt werden kann. Nachdem Ihr Makrocode ausgeführt wurde, können Sie die Bildschirmaktualisierung wieder aktivieren.
Untermakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Platzieren Sie Ihren Makrocode hier
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Nachdem Sie die ScreenUpdating-Eigenschaft wieder auf True gesetzt haben, löst Excel automatisch eine Neuzeichnung des Bildschirms aus.
Statusleisten-Updates deaktivieren
Die Excel-Statusleiste, die am unteren Rand des Excel-Fensters angezeigt wird, zeigt normalerweise den Fortschritt bestimmter Aktionen in Excel an. Wenn Ihr Makro mit vielen Daten arbeitet, nimmt die Statusleiste einige Ressourcen in Anspruch.
Beachten Sie, dass das Deaktivieren der Bildschirmaktualisierung vom Deaktivieren der Statusleistenanzeige getrennt ist. Die Statusleiste wird auch dann aktualisiert, wenn Sie die Bildschirmaktualisierung deaktivieren. Sie können die Application.DisplayStatusBar-Eigenschaft verwenden, um alle Statusleistenaktualisierungen vorübergehend zu deaktivieren, um die Leistung Ihres Makros weiter zu verbessern:
Untermakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
'Platzieren Sie Ihren Makrocode hier
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub
Excel anweisen, Ereignisse zu ignorieren
Sie können Makros als Ereignisprozeduren implementieren, die Excel anweisen, bestimmten Code auszuführen, wenn sich ein Arbeitsblatt oder eine Arbeitsmappe ändert.
Manchmal nehmen Standardmakros Änderungen vor, die eine Ereignisprozedur auslösen. Wenn Sie beispielsweise über ein Standardmakro verfügen, das mehrere Zellen auf Sheet1 bearbeitet, muss Ihr Makro jedes Mal, wenn eine Zelle auf diesem Blatt geändert wird, angehalten werden, während das Worksheet_Change-Ereignis ausgeführt wird.
Sie können eine weitere Leistungssteigerungsebene hinzufügen, indem Sie die EnableEvents-Eigenschaft verwenden, um Excel anzuweisen, Ereignisse zu ignorieren, während Ihr Makro ausgeführt wird.
Legen Sie die EnableEvents-Eigenschaft auf False fest, bevor Sie Ihr Makro ausführen. Nachdem der Makrocode ausgeführt wurde, können Sie die EnableEvents-Eigenschaft wieder auf True festlegen.
Untermakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
'Platzieren Sie Ihren Makrocode hier
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub
Seitenumbrüche ausblenden
Jedes Mal, wenn Ihr Makro die Anzahl der Zeilen ändert, die Anzahl der Spalten ändert oder die Seiteneinrichtung eines Arbeitsblatts ändert, ist Excel gezwungen, Zeit für die Neuberechnung der auf dem Blatt angezeigten Seitenumbrüche zu benötigen.
Sie können dieses Verhalten vermeiden, indem Sie die Seitenumbrüche einfach ausblenden, bevor Sie Ihr Makro starten.
Legen Sie die Blatteigenschaft DisplayPageBreaks auf False fest, um Seitenumbrüche auszublenden. Wenn Sie nach der Ausführung Ihres Makros weiterhin Seitenumbrüche anzeigen möchten, legen Sie die Blatteigenschaft DisplayPageBreaks wieder auf True fest.
Untermakro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Activesheet.DisplayPageBreaks = False
'Platzieren Sie Ihren Makrocode hier
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Activesheet.DisplayPageBreaks = True
End Sub
Pivot-Tabellen-Updates aussetzen
Wenn Ihr Makro Pivot-Tabellen manipuliert, die große Datenquellen enthalten, kann die Leistung beim dynamischen Hinzufügen oder Verschieben von Pivot-Feldern beeinträchtigt werden.
Sie können die Leistung Ihres Makros verbessern, indem Sie die Neuberechnung der Pivot-Tabelle aussetzen, bis alle Pivot-Feldänderungen vorgenommen wurden. Legen Sie einfach die PivotTable.ManualUpdate-Eigenschaft auf True fest, um die Neuberechnung zu verschieben, führen Sie Ihren Makrocode aus, und legen Sie dann die PivotTable.ManualUpdate-Eigenschaft wieder auf False fest, um die Neuberechnung auszulösen.
Untermakro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True
'Platzieren Sie Ihren Makrocode hier
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
End Sub
Vermeiden Sie Kopieren und Einfügen
Es ist wichtig, sich daran zu erinnern, dass Macro Recorder zwar Zeit spart, indem er VBA-Code für Sie schreibt, aber nicht immer den effizientesten Code schreibt. Ein Paradebeispiel ist, wie Macro Recorder jede Kopier- und Einfügeaktion erfasst, die Sie während der Aufnahme ausführen.
Sie können Ihren Makros einen leichten Schub geben, indem Sie den Zwischenhändler ausschneiden und eine direkte Kopie von einer Zelle in eine Zielzelle durchführen. Dieser alternative Code verwendet das Argument Destination, um die Zwischenablage zu umgehen und den Inhalt von Zelle A1 direkt in Zelle B1 zu kopieren.
Bereich("A1").Ziel kopieren:=Bereich("B1")
Wenn Sie nur Werte kopieren müssen (keine Formatierungen oder Formeln), können Sie die Leistung noch weiter verbessern, indem Sie die Methode Kopieren insgesamt vermeiden. Setzen Sie einfach den Wert der Zielzelle auf den gleichen Wert wie in der Quellzelle. Diese Methode ist etwa 25-mal schneller als die Methode Kopieren:
Range("B1").Value = Range("A1").Value
Wenn Sie nur Formeln von einer Zelle in eine andere kopieren müssen (keine Werte oder Formatierungen), können Sie die Formel der Zielzelle auf dieselbe Formel wie in der Quellzelle setzen:
Bereich("B1").Formel = Bereich("A1").Formel
Verwenden der With-Anweisung
Beim Aufzeichnen von Makros manipulieren Sie häufig dasselbe Objekt mehr als einmal. Sie können Zeit sparen und die Leistung verbessern, indem Sie mit der With-Anweisung mehrere Aktionen für ein bestimmtes Objekt auf einmal ausführen.
Die im folgenden Beispiel verwendete With-Anweisung weist Excel an, alle Formatierungsänderungen gleichzeitig anzuwenden:
Mit Range("A1").Font
.Fett = Wahr
.Kursiv = Wahr
.Unterstreichen = xlUnderlineStyleSingle
Ende mit
Wenn Sie sich angewöhnen, Aktionen in With-Anweisungen zu unterteilen, werden Ihre Makros nicht nur schneller ausgeführt, sondern Sie können auch Ihren Makrocode leichter lesen.
Vermeiden der Select-Methode
Macro Recorder verwendet gerne die Select-Methode, um Objekte explizit auszuwählen, bevor Aktionen an ihnen ausgeführt werden. Im Allgemeinen müssen Sie keine Objekte auswählen, bevor Sie mit ihnen arbeiten. Tatsächlich können Sie die Makroleistung erheblich verbessern, indem Sie die Select-Methode nicht verwenden.
Machen Sie es sich nach dem Aufzeichnen Ihrer Makros zur Gewohnheit, den generierten Code zu ändern, um die Select-Methoden zu entfernen. In diesem Fall würde der optimierte Code wie folgt aussehen:
Sheets("Sheet1").Range("A1").FormulaR1C1 = "1000"
Sheets("Sheet2").Range("A1").FormulaR1C1 = "1000"
Sheets("Sheet3").Range("A1").FormulaR1C1 = "1000"
Beachten Sie, dass das Nichts ausgewählt wird. Der Code verwendet einfach die Objekthierarchie, um die erforderlichen Aktionen anzuwenden.
Einschränken von Fahrten auf das Arbeitsblatt
Eine andere Möglichkeit, Ihre Makros zu beschleunigen, besteht darin, die Anzahl der Verweise auf Arbeitsblattdaten in Ihrem Code zu begrenzen. Es ist immer weniger effizient, Daten aus dem Arbeitsblatt zu holen als aus dem Speicher. Das heißt, Ihre Makros werden viel schneller ausgeführt, wenn sie nicht wiederholt mit dem Arbeitsblatt interagieren müssen.
Der folgende einfache Code zwingt VBA beispielsweise dazu, kontinuierlich zu Sheets("Sheet1").Range("A1") zurückzukehren, um die für den Vergleich benötigte Zahl in der If-Anweisung zu erhalten:
Für ReportMonth = 1 bis 12
If Range("A1").Value = ReportMonth Then
MsgBox 1000000 / ReportMonth
Ende Wenn
Nächster BerichtMonat
Eine viel effizientere Methode besteht darin, den Wert in Sheets("Sheet1").Range("A1") in einer Variablen namens MyMonth zu speichern. Auf diese Weise referenziert der Code die MyMonth-Variable anstelle des Arbeitsblatts:
Dim MyMonth als Integer
MyMonth = Range("A1").Value
Für ReportMonth = 1 bis 12
Wenn MyMonth = ReportMonth Dann
MsgBox 1000000 / ReportMonth
Ende Wenn
Nächster BerichtMonat
Ziehen Sie in Betracht, Variablen zu nutzen, um mit Daten im Speicher zu arbeiten, anstatt direkt auf Arbeitsblätter zu verweisen.
Vermeiden Sie übermäßige Referenzen
Wenn Sie eine Methode oder Eigenschaft eines Objekts aufrufen, muss diese über die IDispatch-Schnittstelle der OLE-Komponente erfolgen. Die Aufrufe dieser OLE-Komponenten nehmen Zeit in Anspruch, daher kann die Reduzierung der Anzahl der Verweise auf OLE-Komponenten die Geschwindigkeit des Makrocodes verbessern.
Für den Aufruf von Objekteigenschaften oder -methoden wird im Allgemeinen die Darstellungsmethode von Object.Method verwendet, also das „.“ Das Symbol wird zum Aufrufen von Eigenschaften und Methoden verwendet.
Daher kann die Anzahl der Methoden- oder Eigenschaftsaufrufe anhand der Anzahl der Symbole „.“ beurteilt werden. Je weniger das „.“ Symbol, desto schneller wird der Code ausgeführt.
Die folgende Anweisung enthält beispielsweise drei Symbole „.“
ThisWorkbook.Sheet1.Range("A1").Value = 100
Die folgende Anweisung hat nur ein Symbol „.“
Activewindow.Top = 100
Hier sind einige Tricks, um die Anzahl der Symbole zu reduzieren. schneller laufen.
Wenn Sie wiederholt auf dasselbe Objekt verweisen müssen, können Sie zunächst das Objekt auf eine Variable setzen, um die Anzahl der Aufrufe zu reduzieren. Der folgende Code erfordert beispielsweise zwei Aufrufe pro Zeile.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300
Da auf das Sheets("Sheet1") -Objekt wiederholt verwiesen werden muss, kann es zunächst auf eine Variable sht gesetzt werden , sodass jeder Code nur einmal aufgerufen werden muss.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300
Zweitens: Wenn Sie keine temporäre Variable sht deklarieren möchten, können Sie auch die zuvor erwähnte With- Anweisung verwenden. Wie im folgenden Beispiel gezeigt:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End With
Drittens: Wenn es viele Schleifen gibt, versuchen Sie, Eigenschaften und Methoden außerhalb der Schleife zu halten. Wenn Sie einen Eigenschaftswert desselben Objekts in einer Schleife wiederverwenden, können Sie den Eigenschaftswert zunächst einer angegebenen Variablen außerhalb der Schleife zuweisen und dann die Variable in der Schleife verwenden, wodurch eine schnellere Geschwindigkeit erreicht werden kann. Wie im folgenden Beispiel gezeigt:
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i
Jede Schleife in diesem Beispiel erhält die Value-Eigenschaft der Zelle Cells(1,2). Wenn Sie die Value-Eigenschaft von Cells(1.2) vor Beginn der Schleife einer Variablen zuweisen, erhalten Sie eine schnellere Ausführung. Wie im folgenden Beispiel gezeigt:
tmp = Cells(1, 2).Value
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i
Der obige Code ruft bei jeder Schleife ThisWorkbook.Sheets("Sheet1") auf. Sie können dies schneller tun, indem Sie die With- Anweisung verwenden, um den Aufruf an ThisWorkbook.Sheets("Sheet1") außerhalb der Schleife zu verschieben. Wie im folgenden Beispiel gezeigt:
tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
For i = 1 To 1000
.Cells(1, 1) = tmp
.Cells(2, 1) = tmp
.Cells(3, 1) = tmp
Next i
End With
Vermeiden Sie die Verwendung von Variantentypen
Anfänger bevorzugen in der Regel die Verwendung von Variablen vom Typ Variant. Dies hat den Vorteil, dass es weniger kompliziert ist, da jeder Datentyp verwendet werden kann, ohne dass das Problem eines Speicherüberlaufs auftritt, wenn die Daten für die Datentypen Integer oder Long zu groß sind. Daten vom Typ Varienmt erfordern jedoch mehr zusätzlichen Speicherplatz als die anderen angegebenen Typen (2 Byte für Integer-Daten, 4 Byte für Long-Daten und 16 Byte für Variant-Daten). VBA benötigt mehr Zeit für die Verarbeitung von Daten vom Typ Variant als andere angegebene Typen von Dateien. Wie das folgende Beispiel zeigt.
Sub VariantTest()
Dim i As Long
Dim ix As Integer, iy As Integer, iz As Integer
Dim vx As Variant, vy As Variant, vz As Variant
Dim tm As Date
vx = 100: vy = 50
tm = Timer
For i = 1 To 1000000
vz = vx * vy
vz = vx + vy
vz = vx - vy
vz = vx / vy
Next i
Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
ix = 100: iy = 50
tm = Timer
For i = 1 To 1000000
iz = ix * iy
iz = ix + iy
iz = ix - iy
iz = ix / iy
Next i
Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub
Im obigen Code führen die Zeilen 8 bis 13 1 Million Additions-, Subtraktions-, Multiplikations- und Divisionsoperationen von Variant-Variablen aus, und die Zeilen 17 bis 22 führen 1 Million Additions-, Subtraktions-, Multiplikations- und Divisionsoperationen von Integer-Variablen durch. Auf meinem Computer dauerte die Operation der Variant-Variablen etwa 0,09375 Sekunden, während die Operation der Integer-Variablen etwa 0,03125 Sekunden dauerte. Die Ergebnisse können von Computer zu Computer unterschiedlich sein, Variant-Variablen sind jedoch deutlich langsamer als Integer-Variablen .
Aus diesem Grund wird empfohlen, die Verwendung von Variant-Variablen zu vermeiden, wenn Sie den angegebenen Datentyp explizit verwenden können .