10 Wege, um Ihre Makros zu beschleunigen

Wie Sie Ihre Excel-Makros zunehmend robuster und komplexer geworden sind, können Sie feststellen, dass sie die Leistung verlieren. Wenn Makros zu diskutieren, das Wort Performance

ist in der Regel gleichbedeutend mit Geschwindigkeit. Die Geschwindigkeit ist, wie schnell Ihre VBA-Prozeduren ihre beabsichtigten Aufgaben zu erfüllen. Im Folgenden sind zehn Möglichkeiten, um Ihre Excel-Makros bei ihrem optimalen Leistungsniveau am Laufen halten zu helfen.

Halting Blatt Berechnungen

Wussten Sie, dass jedes Mal, wenn eine Zelle, die jede Formel in Ihrer Tabelle wirkt sich verändert oder manipuliert, Excel das gesamte Arbeitsblatt neu berechnet? In Arbeitsblätter, die eine große Menge an Formeln haben, kann dieses Verhalten drastisch verlangsamen Ihre Makros.

Sie können die Application.Calculation Eigenschaft verwenden Excel zu sagen, in den manuellen Berechnungsmodus zu wechseln. Wenn eine Arbeitsmappe in manuellen Berechnungsmodus ist, neu zu berechnen die Arbeitsmappe wird nicht, bis Sie explizit eine Berechnung durch Drücken der Taste F9 auslösen.

Platz Excel in den manuellen Berechnungsmodus, führen Sie den Code, und dann in den automatischen Berechnungsmodus wechseln.

Sub Macro1 () Application.Calculation = xlCalculationManual'Place Ihr Makrocode hereApplication.Calculation = xlCalculationAutomaticEnd Sub

Einstellen des Berechnungsmodus auf xlCalculationAutomatic zurück wird automatisch eine Neuberechnung des Arbeitsblatt auslösen, so gibt es keine Notwendigkeit, die F9-Taste nach dem Makro ausgeführt zu drücken.

Deaktivieren Blatt Bildschirmaktualisierung

Sie können feststellen, dass, wenn Sie Ihre Makros ausführen, Ihren Bildschirm einen fairen Betrag von Flimmern tut. Dieses Flimmern versucht Excel den Bildschirm neu zu zeichnen, den aktuellen Zustand des Arbeitsblatt zu zeigen. Leider jedes Mal Excel den Bildschirm neu gezeichnet, dauert es Speicher-Ressourcen.

Sie können die Application.ScreenUpdating Eigenschaft verwenden, um Bildschirm-Updates deaktivieren, bis Ihr Makro abgeschlossen hat. Deaktivieren der Bildschirmaktualisierung spart Zeit und Ressourcen, so dass Ihr Makro ein wenig schneller zu laufen. Nach dem Makrocode ausgeführt beendet ist, können Sie Bildschirm wieder ein Update.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = False'Place Ihr Makrocode hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueEnd Sub

Nachdem Sie die ScreenUpdating Eigenschaft zurück auf True gesetzt ist, wird Excel automatisch ein erneutes Zeichnen des Bildschirms auslösen.

Ausschalten Statusleiste Updates

Die Excel-Statusleiste, die am unteren Rand des Excel-Fenster angezeigt wird, zeigt normalerweise den Fortschritt bestimmter Aktionen in Excel. Wenn Ihr Makro mit vielen Daten arbeiten, werden in der Statusleiste einige Ressourcen in Anspruch nehmen.

Es ist wichtig, dass die Aktualisierung des Bildschirms von der Statusleiste Anzeige Ausschalten getrennt Ausschalten zu beachten. Die Statusleiste wird auch weiterhin noch aktualisiert werden, wenn Sie die Aktualisierung des Bildschirms deaktivieren. Sie können die Application.DisplayStatusBar Eigenschaft verwenden, um vorübergehend alle Statusleiste Updates zu deaktivieren, die Leistungsfähigkeit Ihres Makros zu verbessern:

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = False'Place Ihr Makrocode hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueEnd Sub

Excel Telling Ereignisse zu ignorieren

Sie können Makros als Ereignisprozeduren implementieren, Excel sagen bestimmten Code auszuführen, wenn ein Arbeitsblatt oder eine Arbeitsmappe Änderungen.

Manchmal Standardmakros Änderungen vornehmen, die eine Ereignisprozedur auslösen. Wenn Sie ein Standard-Makro haben zum Beispiel die mehrere Zellen auf Sheet1 manipuliert, jedes Mal, wenn eine Zelle auf diesem Blatt geändert wird, hat Ihr Makro zu pausieren, während das Worksheet_Change Ereignis läuft.

Sie können mit Hilfe der EnableEvents Eigenschaft Steigerung eine weitere Ebene der Leistung hinzufügen Excel zu erzählen Ereignisse während Makro ausgeführt zu ignorieren.

Stellen Sie die EnableEvents Eigenschaft auf False, bevor das Makro ausgeführt wird. Nach dem Makrocode ausgeführt beendet ist, können Sie die EnableEvents Eigenschaft zurück auf True gesetzt.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = False'Place Ihr Makrocode hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueEnd Sub

Ausblenden von Seitenumbrüchen

Jedes Mal, wenn Ihr Makro die Anzahl der Zeilen ändert, die Anzahl der Spalten ändert, oder ändert die Seiteneinrichtung von einem Arbeitsblatt, wird Excel gezwungen Zeit zu nehmen, die Seitenumbrüche auf dem Blatt gezeigt neu berechnet wird.

Sie können durch einfaches Ausblenden der Seitenumbrüche dieses Verhalten zu vermeiden, bevor das Makro zu starten.

Stellen Sie die DisplayPageBreaks Blatt-Eigenschaft auf False Seitenumbrüche zu verstecken. Wenn Sie Seitenumbrüche nach dem Makro ausgeführt zu zeigen, fortgesetzt werden soll, stellen Sie die DisplayPageBreaks Blatt-Eigenschaft auf True zurück.

Sub Macro1 () Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseActivesheet.DisplayPageBreaks = False'Place Ihr Makrocode hereApplication.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueActivesheet.DisplayPageBreaks = TrueEnd Sub

Schwebend Pivot-Tabelle Updates

Wenn Ihr Makro manipuliert Tabellen schwenken, die große Datenquellen enthalten, können Sie eine schlechte Leistung, wenn Dinge wie dynamisch Hinzufügen oder Verschieben von Pivot-Feldern zu tun.

Sie können durch Aussetzung der Neuberechnung der Pivot-Tabelle, die Leistung Ihres Makro verbessern, bis alle Drehfeldänderungen vorgenommen wurden. die PivotTable.ManualUpdate Eigenschaft auf True aufzuschieben Neuberechnung, führen Sie Ihre Makrocode, und legen Sie dann die PivotTable.ManualUpdate Eigenschaft zurück auf False die Neuberechnung auszulösen einfach eingestellt.

Sub Macro1 () ActiveSheet.PivotTables ( "PivotTable1"). ManualUpdate = True'Place den Makrocode hereActiveSheet.PivotTables ( "PivotTable1"). ManualUpdate = FalseEnd Sub

Lenkung von Kopieren und Einfügen klar

Es ist wichtig, sich daran zu erinnern, dass, obwohl Macro Recorder Zeit durch das Schreiben von VBA-Code für Sie spart, muss es nicht immer die effizienteste Code schreiben. Ein gutes Beispiel dafür ist, wie Macro Recorder erfasst jede Copy-and-Paste-Aktion, die Sie durchführen, während der Aufnahme.

Sie können Ihre Makros einen leichten Schub geben, indem die Mittelsmann Ausschneiden und eine direkte Kopie von einer Zelle zu einer Zielzelle durchgeführt wird. Diese alternative Code verwendet die Zielargument der Zwischenablage und kopieren Sie den Inhalt der Zelle A1 direkt auf die Zelle B1 zu umgehen.

. Range ( "A1") kopieren Destination: = Range ( "B1")

Wenn Sie nur Werte kopieren müssen (nicht die Formatierung oder Formeln), können Sie alle zusammen die Leistung noch mehr durch die Copy-Methode zu vermeiden verbessern. Setzen Sie einfach in der Ursprungszelle gefunden den Wert der Zielzelle auf den gleichen Wert. Diese Methode ist etwa etwa 25-mal schneller als die Copy-Methode:

Range ( "B1"). Value = Range ( "A1"). Value

Wenn Sie nur Formeln von einer Zelle zur anderen (nicht Werte oder Formatierung) kopieren müssen, können Sie die Formel der Zielzelle auf die gleiche Formel in der Ursprungszelle enthalten sind gesetzt:

Range ( "B1"). Formel = Range ( "A1"). Formel

Unter Verwendung der With-Anweisung

Wenn Makros aufzeichnen, werden Sie oft das gleiche Objekt mehr manipulieren als einmal. Sie können Zeit und verbessern die Leistung durch Verwendung der With-Anweisung ausführen mehrere Aktionen an einem bestimmten Objekt in einem Schuss zu speichern.

Die With-Anweisung im folgenden Beispiel verwendet sagt Excel alle Formatierungsänderungen auf einmal anwenden:

Mit Range ( "A1"). Font.Bold = True.Italic = True.Underline = xlUnderlineStyleSingleEnd Mit

Der Einstieg in die Gewohnheit, Aktionen in Mit Aussagen Chunking wird nicht nur Ihre Makros halten schneller läuft, aber es auch einfacher machen Sie Ihren Makrocode zu lesen.

Die Vermeidung der Select-Methode

Macro Recorder ist gern die Select-Methode der Verwendung explizit Objekte auswählen, bevor Aktionen auf ihnen. Es ist in der Regel nicht erforderlich, Objekte auswählen, bevor Sie mit ihnen zu arbeiten. In der Tat können Sie dramatisch Makro-Leistung verbessern, indem sie nicht die Select-Methode verwendet wird.

Nach der Aufnahme von Makros, machen Sie es sich zur Gewohnheit, den generierten Code zu ändern, um die Select-Methoden zu entfernen. In diesem Fall würde der optimierten Code wie folgt aussehen:

Sheets ( "Sheet1"). Range ( "A1"). FormulaR1C1 = "1000" Sheets ( "Tabelle2"). Range ( "A1"). FormulaR1C1 = "1000" Sheets ( "Sheet3"). Range ( "A1" ) .FormulaR1C1 = "1000"

Beachten Sie, dass das nichts ausgewählt wird. Der Code verwendet einfach die Objekthierarchie, die erforderlichen Maßnahmen zu beantragen.

Die Begrenzung Ausflüge in das Arbeitsblatt

Eine andere Möglichkeit, Makros zu beschleunigen ist es, die Höhe der Zeit Sie Arbeitsblatt-Daten in Ihrem Code verweisen zu begrenzen. Es ist immer weniger effizient Daten aus dem Arbeitsblatt zu greifen, als aus dem Speicher. Das heißt, Ihre Makros läuft viel schneller, wenn sie wiederholt nicht mit dem Arbeitsblatt zusammenwirken müssen.

Zum Beispiel zwingt die folgenden einfachen Code VBA kontinuierlich Sheets zurückzukehren ( "Sheet1") Range ( "A1") die Nummer für den Vergleich durchgeführt wird, in der If-Anweisung zu erhalten benötigt.:

Für ReportMonth = 1 To 12Wenn Range ( "A1"). Value = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Eine viel effizientere Methode ist es, den Wert in Sheets ( "Sheet1") zu speichern. Range ( "A1") auf eine Variable namens MyMonth. Auf diese Weise verweist der Code die MyMonth Variable anstelle des Arbeitsblatt:

Dim MyMonth als IntegerMyMonth = Range ( "A1"). Wertfür ReportMonth = 1 To 12Wenn MyMonth = ReportMonth ThenMsgBox 1000000 / ReportMonthEnd IfNext ReportMonth

Betrachten Sie Variablen nutzen mit Daten im Speicher zu arbeiten, im Gegensatz zu direkt Arbeitsblatt verweisen.