Arbeiten mit Excel VBA-Makros
Makros in Excel werden als VBA-Code gespeichert, und manchmal möchten Sie diesen Code direkt bearbeiten. In diesem Tutorial wird beschrieben, wie Sie Makros anzeigen und bearbeiten, einige Techniken zum Debuggen von Makros beschreiben und einige allgemeine Bearbeitungsbeispiele geben.
Makros anzeigen
Eine Liste von Makros kann im Dialog Makros angezeigt werden. Um dieses Dialogfeld anzuzeigen, wählen Sie die Registerkarte Entwickler im Menüband und klicken Sie auf die Schaltfläche Makros.
Wenn mehrere Arbeitsmappen geöffnet sind, werden Makros aus allen Arbeitsmappen in der Liste angezeigt. Makros in der aktiven Arbeitsmappe werden nur nach Namen angezeigt, während Makros in anderen Arbeitsmappen den Arbeitsmappennamen und ein Ausrufezeichen voranstellen (z. B. „Book2!OtherMacro“).
Öffnen Sie ein Makro zum Bearbeiten
Sie können den Makro-Dialog verwenden, um den Code für ein Makro zu öffnen, indem Sie den Namen des Makros auswählen und auf die Schaltfläche Bearbeiten klicken. Dadurch wird das Makro im VB-Editor geöffnet.
Alternativ können Sie den VB-Editor auch direkt öffnen, indem Sie auf der Registerkarte Entwicklertools auf die Schaltfläche Visual Basic klicken oder die Tastenkombination ALT+F11 drücken.
Bei dieser Methode müssen Sie zu Ihrem gewünschten Makro navigieren (auch "Prozedur" genannt). Wir werden das Layout des VBA-Editors durchgehen:
Überblick über den VB-Editor
Der VB-Editor hat mehrere Fenster; In diesem Tutorial behandeln wir das Projektfenster, das Eigenschaftenfenster und das Codefenster.
Projektfenster
Das Projektfenster zeigt jede Excel-Datei als eigenes Projekt an, wobei alle Objekte in diesem Projekt nach Typ kategorisiert sind. Aufgezeichnete Makros erscheinen in der Kategorie „Module“, in der Regel im Objekt „Modul1“. (Wenn Ihr Projekt mehrere Module hat und Sie nicht sicher sind, wo Ihr Makro gespeichert ist, öffnen Sie es einfach über den oben genannten Makros-Dialog.)
Eigenschaftenfenster
Das Eigenschaftenfenster zeigt die Eigenschaften und die zugehörigen Werte eines Objekts an - zum Beispiel wird durch Klicken auf ein Arbeitsblattobjekt im Projektfenster eine Liste der Eigenschaften für das Arbeitsblatt angezeigt. Eigenschaftsnamen befinden sich auf der linken Seite und Eigenschaftswerte befinden sich auf der rechten Seite.
Wenn Sie ein Modul im Projektfenster auswählen, wird angezeigt, dass es nur eine Eigenschaft hat, „(Name)“. Sie können den Namen eines Moduls ändern, indem Sie auf den Wert der Eigenschaft doppelklicken, einen neuen Namen eingeben und die Eingabetaste drücken. Wenn Sie den Namen eines Moduls ändern, wird es im Projektfenster umbenannt, was nützlich ist, wenn Sie viele Module haben.
Codefenster
Codefenster sind spezielle Texteditoren, in denen Sie den VBA-Code Ihres Makros bearbeiten können. Wenn Sie den Code für ein Makro in Module1 sehen möchten, doppelklicken Sie im Projektfenster auf „Module1“.
Ausführen von Makros im VB-Editor
Makros können direkt aus dem VB-Editor ausgeführt werden, was zum Testen und Debuggen nützlich ist.
Ausführen eines Makros
- Doppelklicken Sie im Projektfenster auf das Modul, das das zu testende Makro enthält (um sein Codefenster zu öffnen).
- Platzieren Sie im Code-Fenster den Cursor an einer beliebigen Stelle im Code des Makros zwischen „Sub“ und „End Sub“
- Drücke den Lauf Schaltfläche in der Symbolleiste oder drücken Sie die Tastenkombination F5
„Schritt durch“ ein Makro
Anstatt das Makro auf einmal auszuführen, können Sie das Makro Zeile für Zeile ausführen, indem Sie eine Tastenkombination verwenden, um den Code „durchzugehen“. Das Makro wird in jeder Zeile angehalten, sodass Sie sicherstellen können, dass jede Codezeile das tut, was Sie in Excel erwarten. Sie können mit dieser Methode auch jederzeit die Fortsetzung eines Makros stoppen.
So „durchlaufen“ Sie ein Makro:
- Doppelklicken Sie im Projektfenster auf das Modul, das das zu testende Makro enthält (um sein Codefenster zu öffnen).
- Platzieren Sie den Cursor im Codefenster an einer beliebigen Stelle auf dem Code des Makros
- Drücken Sie die Tastenkombination F8, um den „Durchgangsvorgang“ zu starten
- Drücken Sie wiederholt F8, um die Codeausführung fortzusetzen, was durch die gelbe Markierung im Codefenster angezeigt wird
- Um die Fortsetzung eines Makros zu stoppen, drücken Sie die Zurücksetzen Taste
Warum VBA-Makros bearbeiten?
Der Makrorekorder ist zwar effektiv, aber auch sehr begrenzt. In einigen Fällen erzeugt es langsame Makros, zeichnet Aktionen auf, die Sie nicht wiederholen wollten, oder zeichnet Dinge auf, von denen Sie dachten, dass Sie sie nicht tun. Wenn Sie lernen, Ihre Makros zu bearbeiten, werden sie schneller, effizienter und vorhersehbarer ausgeführt.
Neben der Behebung dieser Probleme werden Sie auch eine massive Produktivitätssteigerung erzielen, wenn Sie die volle Leistung von Makros nutzen. Makros müssen nicht nur Aufzeichnungen von Aufgaben sein - Makros können Logik enthalten, damit sie Aufgaben nur unter bestimmten Bedingungen ausführen. In nur wenigen Minuten können Sie Schleifen programmieren, die eine Aufgabe Hunderte oder Tausende Male auf einmal wiederholen!
Im Folgenden finden Sie einige praktische Tipps zur Optimierung Ihres Makrocodes sowie Tools, mit denen Ihre Makros härter und intelligenter arbeiten.
Allgemeine Beispiele für die Makrobearbeitung
Makros beschleunigen
Wenn Sie ein Makro haben, dessen Ausführung lange dauert, kann es mehrere Gründe geben, warum es langsam ausgeführt wird.
Zum einen: Wenn ein Makro ausgeführt wird, zeigt Excel alles so, wie es in Echtzeit passiert - obwohl es vielleicht so ist aussehen schnell zu dir, eigentlichzeigen das werk ist ein deutlicher leistungshit. Eine Möglichkeit, Excel deutlich schneller laufen zu lassen, besteht darin, es anzuweisen Stoppen Sie die Aktualisierung des Bildschirms:
' Bildschirmaktualisierung deaktivieren Application.ScreenUpdating = False ' Bildschirmaktualisierung aktivieren Application.ScreenUpdating = True
Die Zeile „Application.ScreenUpdating = False“ bedeutet, dass das Makro nicht funktioniert, aber viel schneller ausgeführt wird. Beachten Sie, dass Sie ScreenUpdating am Ende Ihres Makros immer auf True setzen sollten, sonst verhält sich Excel später möglicherweise nicht so, wie Sie es erwarten!
Eine andere Möglichkeit, Makros zu beschleunigen:Automatische Berechnung im Makro deaktivieren. Wenn Sie mit komplexen Tabellenkalkulationen gearbeitet haben, wissen Sie, dass kleine Änderungen Tausende von Berechnungen auslösen können, deren Ausführung Zeit in Anspruch nimmt, weshalb viele Leute die automatische Berechnung in den Excel-Optionen deaktivieren. Sie können dies auch mit VBA-Code umschalten, damit Ihr Makro auf anderen Computern weiterhin schnell funktioniert. Dies hilft in Fällen, in denen Sie viele Formelzellen kopieren und einfügen oder viele Berechnungen auslösen, wenn Sie Daten in einen Bereich einfügen:
' Automatische Berechnung deaktivieren Application.Calculation = xlCalculationManual ' Automatische Berechnung aktivieren Application.Calculation = xlCalculationAutomatic
Schleifen und Logik hinzufügen (If-Anweisungen)
Der Makrorekorder speichert alle Ihre Aktionen als Code in einer Sprache namens VBA. VBA ist mehr als nur eine Möglichkeit, Aktionen in Excel aufzuzeichnen – es ist eine Programmiersprache, was bedeutet, dass es Code enthalten kann, um Entscheidungen darüber zu treffen, welche Aktionen ausgeführt werden sollen, oder Aktionen zu wiederholen, bis eine Bedingung erfüllt ist.
Looping
Angenommen, Sie wollten ein Makro erstellen, das einen Bericht erstellt, und als Teil dieses Makros mussten Sie der Arbeitsmappe neunzehn Blätter hinzufügen, also insgesamt zwanzig. Sie könnten sich selbst aufnehmen, wenn Sie immer wieder auf die Schaltfläche (+) klicken, oder Sie könnten eine Schleife schreiben, die die Aktion für Sie wiederholt, wie folgt:
Sub ReportPrep() Dim i As Long For i = 1 To 19 Sheets.Add Next i End Sub
In diesem Beispiel verwenden wir a Für Schleife, eine Art Schleife, die eine Reihe von Elementen durchläuft. Hier ist unser Bereich die Zahlen 1 bis 19, wobei eine Variable namens „i“ verwendet wird, damit die Schleife den Überblick behalten kann. In unserer Schleife wird zwischen den nur eine Aktion wiederholt zum undnächste Zeilen (das Hinzufügen des Blatts), aber Sie können so viel Code in die Schleife einfügen, wie Sie möchten, z. B. das Blatt formatieren oder Daten in jedes Blatt kopieren und einfügen - was immer Sie wiederholen möchten.
Wenn-Anweisungen
Ein Wenn Anweisung wird verwendet, um zu entscheiden, ob ein Code ausgeführt wird oder nicht, wobei ein logischer Test verwendet wird, um die Entscheidung zu treffen. Hier ist ein einfaches Beispiel:
Sub ClearIfSmall() If Selection.Value < 100 Then Selection.Clear End If End Sub
Dieses einfache Beispiel zeigt, wie die If-Anweisung funktioniert - Sie testen eine Bedingung, die entweder wahr oder falsch ist (ist der Wert der ausgewählten Zelle kleiner als 100?), und wenn der Test True zurückgibt, wird der darin enthaltene Code ausgeführt.
Ein Nachteil dieses Codes besteht darin, dass er jeweils nur eine Zelle testet (und fehlschlagen würde, wenn Sie mehrere Zellen auswählen). Dies wäre nützlicher, wenn Sie … jede ausgewählte Zelle durchlaufen und jede einzelne testen könnten …
Sub ClearIfSmall() Dim c As Range For Each c In Selection.Cells If c.Value < 100 Then c.Clear End If Next c End Sub
In diesem Beispiel gibt es eine etwas andere For-Schleife – diese durchläuft nicht einen Zahlenbereich, sondern durchläuft stattdessen alle Zellen in der Auswahl, wobei eine Variable namens „c“ verwendet wird, um den Überblick zu behalten. Innerhalb der Schleife wird der Wert von ‚c‘ verwendet, um zu bestimmen, ob die Zelle gelöscht werden soll oder nicht.
Schleifen und If-Anweisungen können beliebig kombiniert werden - Sie können Schleifen in Schleifen oder eine If-Anweisung in eine andere einfügen oder mit einer If-Anweisung entscheiden, ob eine Schleife überhaupt ausgeführt werden soll.
<<>>
Scroll-Effekte entfernen
Ein häufiger Grund zum Bearbeiten von Makrocode besteht darin, das Scrollen des Bildschirms zu entfernen. Beim Aufzeichnen eines Makros müssen Sie möglicherweise durch Scrollen auf andere Bereiche eines Arbeitsblatts zugreifen, aber Makros müssen nicht scrollen, um auf Daten zuzugreifen.
Scrollen kann Ihren Code mit Hunderten oder sogar Tausenden von Zeilen unnötigen Codes überladen. Hier ist ein Beispiel für den Code, der aufgezeichnet wird, wenn Sie auf die Bildlaufleiste klicken und ziehen:
Diese Art von Code ist völlig unnötig und könnte gelöscht werden, ohne dass andere Funktionen beeinträchtigt werden. Selbst wenn Sie das Scrollen beibehalten wollten, könnte dieser Code immer noch zu einer Schleife komprimiert werden.
Überflüssigen Code entfernen
Aufgezeichnete Makros neigen dazu, viel redundanten Code hinzuzufügen, der nicht unbedingt widerspiegelt, was das Makro tun soll. Nehmen Sie zum Beispiel den folgenden aufgezeichneten Code, der das Ändern eines Schriftartnamens in einer Zelle aufzeichnet:
Obwohl nur der Schriftname geändert wurde, wurden elf (11) Schriftänderungen wie Schriftgröße, Texteffekte usw. aufgezeichnet. Wenn die Absicht des Makros darin bestand, nur den Schriftnamen zu ändern (unter Beibehaltung aller anderen Eigenschaften), dies Aufgezeichnetes Makro würde nicht funktionieren!
Es ist möglich, dieses Makro so zu ändern, dass es nur den Schriftartnamen ändert:
Dieses Makro funktioniert jetzt nicht nur wie vorgesehen, sondern ist auch viel einfacher zu lesen.
Cursorbewegungen entfernen
Eine andere Sache, die in Makros aufgezeichnet wird, sind Arbeitsblatt- und Zellenauswahlen. Dies ist ein Problem, da ein Benutzer leicht den Überblick verlieren kann, woran er gerade gearbeitet hat, wenn sich der Cursor nach der Ausführung eines Makros an eine andere Position bewegt.
Wie beim Scrollen Sie Möglicherweise müssen Sie den Cursor bewegen und verschiedene Zellen auswählen, um eine Aufgabe auszuführen, aber Makros müssen den Cursor nicht verwenden, um auf Daten zuzugreifen. Betrachten Sie den folgenden Code, der einen Bereich kopiert und dann in drei andere Blätter einfügt:
Es gibt ein paar Probleme mit diesem Code:
- Der Benutzer verliert seinen vorherigen Platz in der Arbeitsmappe
- Das Makro gibt nicht an, welches Blatt wir kopierenvon - Dies könnte ein Problem sein, wenn das Makro auf dem falschen Blatt ausgeführt wurde
Außerdem ist der Code schwer zu lesen und verschwenderisch. Diese Probleme können leicht genug gelöst werden:
In diesem Code ist deutlich zu erkennen, dass wir aus Sheet1 kopieren und weder das aktive Arbeitsblatt noch der ausgewählte Bereich geändert werden müssen, um die Daten einzufügen. (Eine wesentliche Änderung ist die Verwendung von „PasteSpecial“ anstelle von „Paste“ – Range-Objekte wie „Range(“C4″)“ haben nur Zugriff auf den PasteSpecial-Befehl.)
Immer wenn Code voller Verweise auf „.Select“ und „Selection“ ist, ist dies ein Hinweis darauf, dass dieser Code optimiert und effizienter gestaltet werden kann.
