Excel VBA-Makros bearbeiten

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 erfahren Sie, wie Sie Makros anzeigen und bearbeiten können, wie Sie Makros debuggen können und einige gängige Bearbeitungsbeispiele finden.

Makros anzeigen

Eine Liste der Makros kann im Dialogfenster Makros angezeigt werden. Um dieses Dialogfenster anzuzeigen, wählen Sie im Menüband die Hauptregisterkarte Entwicklertools und klicken Sie auf die Schaltfläche Makros.

Wenn mehrere Arbeitsmappen geöffnet sind, werden die Makros aus allen Arbeitsmappen in der Liste angezeigt. Makros in der aktiven Arbeitsmappe werden nur mit ihrem Namen angezeigt, während den Makros in anderen Arbeitsmappen der Name der Arbeitsmappe und ein Ausrufezeichen vorangestellt werden (z. B. „Mappe2!AnderesMakro“).

Öffnen eines Makros zur Bearbeitung

Sie können das Dialogfenster Makro verwenden, um den Code eines Makros zu öffnen, indem Sie den Makronamen 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 Entwickler auf die Schaltfläche Visual Basic klicken oder die Tastenkombination ALT+F11 drücken.

Mit dieser Methode müssen Sie zu Ihrem gewünschten Makro (auch „Prozedur“ genannt) navigieren. Wir werden uns das Layout des VBA-Editors ansehen:

Überblick über den VB-Editor

Der VB-Editor hat mehrere Fenster; in diesem Tutorial werden wir das Projektfenster, das Eigenschaftsfenster und das Codefenster behandeln.

Projektfenster

Im Projektfenster wird jede Excel-Datei als eigenes Projekt angezeigt, wobei alle Objekte in diesem Projekt nach Typ kategorisiert sind. Aufgezeichnete Makros erscheinen in der Kategorie „Module“, normalerweise im Objekt „Modul1“. (Wenn Ihr Projekt mehrere Module hat und Sie nicht sicher sind, wo Ihr Makro gespeichert ist, öffnen Sie es einfach über das oben erwähnte Dialogfenster „Makros“)

Eigenschaftsfenster

Das Eigenschaftsfenster zeigt die Eigenschaften und zugehörigen Werte eines Objekts an – wenn Sie beispielsweise im Projektfenster auf ein Arbeitsblattobjekt klicken, wird eine Liste der Eigenschaften für das Arbeitsblatt angezeigt. Die Eigenschaftsnamen stehen auf der linken Seite und die Eigenschaftswerte auf der rechten.

Wenn Sie ein Modul im Projektfenster auswählen, wird nur eine Eigenschaft angezeigt, nämlich „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 Modul1 sehen möchten, doppelklicken Sie im Projektfenster auf „Modul1“.

Ausführen von Makros im VB-Editor

Makros können direkt aus dem VB-Editor ausgeführt werden. Was für Tests und Debugging nützlich ist.

Ausführen eines Makros

• Doppelklicken Sie im Projektfenster auf das Modul, das das zu testende Makro enthält, um das Codefenster zu öffnen
• Platzieren Sie den Mauszeiger im Codefenster an einer beliebigen Stelle im Makro-Code zwischen „Sub“ und „End Sub“
• Klicken Sie auf die Schaltfläche Ausführen in der Symbolleiste, oder drücken Sie die Taste F5

Ein Makro „schrittweise“ ausführen

Anstatt das Makro auf einmal auszuführen, können Sie das Makro auch zeilenweise ausführen, indem Sie eine Tastenkombination verwenden, um den Code „durchzugehen“. Das Makro macht bei jeder Zeile eine Pause, so dass 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 abbrechen.

So gehen Sie ein Makro „schrittweise“ durch:

• Doppelklicken Sie im Projektfenster auf das Modul, das das zu testende Makro enthält, um dessen Codefenster zu öffnen
• Platzieren Sie den Mauszeiger im Codefenster an einer beliebigen Stelle im Code des Makros
• Drücken Sie die Taste F8, um den „schrittweisen“ Prozess zu starten
• Drücken Sie wiederholt F8, um die Ausführung des Codes voranzutreiben, was durch die gelbe Markierung im Codefenster angezeigt wird
• Um die Ausführung eines Makros zu beenden, drücken Sie die Schaltfläche Zurücksetzen

Warum VBA-Makros bearbeiten?

Der Makrorekorder ist zwar effektiv, aber auch sehr begrenzt. In einigen Fällen erzeugt er langsame Makros, zeichnet Aktionen auf, die Sie nicht wiederholen wollten, oder Dinge, von denen Sie nicht wussten, dass Sie sie tun. Wenn Sie lernen, Ihre Makros zu bearbeiten, können Sie sie schneller, effizienter und vorhersehbarer ausführen.

Neben der Behebung dieser Probleme können Sie auch Ihre Produktivität erheblich steigern, wenn Sie sich die volle Leistungsfähigkeit von Makros zunutze machen. Makros müssen nicht nur Aufzeichnungen von Aufgaben sein. Makros können auch 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 abschnitt finden Sie einige praktische Tipps zur Optimierung Ihres Makrocodes sowie Tools, mit denen Ihre Makros effizienter und intelligenter arbeiten.

Allgemeine Beispiele für die Makrobearbeitung

Makros beschleunigen

Wenn Sie ein Makro haben, das sehr lange für die Ausführung braucht, kann das mehrere Gründe haben.

Erstens: Wenn ein Makro ausgeführt wird, zeigt Excel alles in Echtzeit an. Das sieht zwar schnell aus, aber die tatsächliche Anzeige der Arbeit stellt einen erheblichen Leistungsverlust dar. Eine Möglichkeit, Excel deutlich schneller laufen zu lassen, besteht darin, die Bildschirmaktualisierung zu stoppen:

' Bildschirmaktualisierung deaktivieren
Application.ScreenUpdating = False

' Bildschirmaktualisierung einschalten
Application.ScreenUpdating = True

Die Zeile „Application.ScreenUpdating = False“ bedeutet, dass Sie nicht sehen, dass das Makro arbeitet, aber es läuft viel schneller. Beachten Sie, dass Sie ScreenUpdating immer am Ende Ihres Makros auf True setzen sollten, da Excel sonst später möglicherweise nicht so reagiert, wie Sie es erwarten!

Eine weitere Möglichkeit, Makros zu beschleunigen: Schalten Sie die automatische Berechnung im Makro aus. Wenn Sie schon einmal mit komplexen Tabellenkalkulationen gearbeitet haben, wissen Sie, dass kleine Änderungen Tausende von Berechnungen auslösen können, die viel Zeit in Anspruch nehmen. Deshalb schalten viele Benutzer die automatische Berechnung in den Excel-Optionen aus. Sie können dies auch mit VBA-Code umschalten, so dass Ihr Makro auch auf anderen Computern schnell funktioniert. Dies ist hilfreich, wenn Sie viele Formelzellen kopieren 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 all Ihre Aktionen als Code in einer Sprache namens VBA. VBA ist mehr als nur eine Methode zur Aufzeichnung von Aktionen in Excel – es ist eine Programmiersprache, d. h. sie kann Code enthalten, der Entscheidungen darüber trifft, welche Aktionen ausgeführt werden sollen, oder die Aktionen wiederholt, bis eine Bedingung erfüllt ist.

Schleife

Angenommen, Sie möchten ein Makro erstellen, das einen Bericht vorbereitet und als Teil dieses Makros müssen Sie der Arbeitsmappe neunzehn Blätter hinzufügen, also insgesamt zwanzig. Sie könnten sich selbst aufzeichnen, indem Sie immer wieder auf die Schaltfläche (+) klicken oder Sie könnten eine Schleife schreiben, die die Aktion für Sie wiederholt, etwa so:

Sub ReportPrep()
    Dim i As Long
    
    For i = 1 To 19
        Sheets.Add
    Next i
End Sub

In diesem Beispiel verwenden wir eine For-Schleife, d. h. eine Schleifenart, die einen Bereich von Elementen durchläuft. Hier sind es die Zahlen 1 bis 19, wobei eine Variable namens ‚i‘ verwendet wird, damit die Schleife den Überblick behält. In unserer Schleife wird zwischen der For- und der Next-Zeile nur eine Aktion wiederholt (das Hinzufügen von Blättern), aber Sie können so viel Code in die Schleife einfügen, wie Sie möchten, um z. B. das Blatt zu formatieren oder Daten zu kopieren und in jedes Blatt einzufügen, was immer Sie wiederholen möchten.

If-Anweisungen

Eine If-Anweisung wird verwendet, um zu entscheiden, ob ein Code ausgeführt wird oder nicht, wobei ein logischer Test die Entscheidung trifft. 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 wahr ergibt, wird der Code darin ausgeführt.

Ein Manko dieses Codes ist die Tatsache, dass er jeweils nur eine Zelle prüft und bei der Auswahl mehrerer Zellen fehlschlagen würde. Dies wäre nützlicher, wenn Sie… jede ausgewählte Zelle in einer Schleife 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 Schleife durchläuft nicht einen Zahlenbereich, sondern 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 der Inhalt der 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 setzen oder eine If-Anweisung verwenden, um zu entscheiden, ob eine Schleife überhaupt ausgeführt werden soll.

<<<Steve: fügen Sie hier AutoMacro-Aufrufe zum Handeln ein!>>>

BildScrolleffekte entfernen

Ein häufiger Grund für die Bearbeitung von Makrocode ist das Entfernen von Bildschirmscrollen. Wenn Sie ein Makro aufzeichnen, müssen Sie möglicherweise andere Bereiche eines Arbeitsblatts durch Scrollen erreichen, aber Makros brauchen nicht zu scrollen, um auf Daten zuzugreifen.

Scrollen kann Ihren Code mit Hunderten oder gar Tausenden von Zeilen unnötigen Codes überfrachten. 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 überflüssig und könnte ohne Beeinträchtigung anderer Funktionen gelöscht werden. Selbst wenn Sie den Bildlauf beibehalten wollten, könnte dieser Code in einer Schleife zusammengefasst werden.

Redundanten Code entfernen

Aufgezeichnete Makros neigen dazu, eine Menge überflüssigen Code hinzuzufügen, der nicht unbedingt das widerspiegelt, was das Makro tun soll. Nehmen Sie zum Beispiel den folgenden aufgezeichneten Code, der die Änderung eines Schriftnamens in einer Zelle aufzeichnet:

Obwohl nur der Schriftname geändert wurde, wurden elf (11) Schriftänderungen aufgezeichnet, wie z. B. die Schriftgröße, die Texteffekte usw. Wenn die Absicht des Makros darin bestünde, nur den Namen der Schriftart zu ändern (und alle anderen Eigenschaften unverändert zu lassen), würde dieses aufgezeichnete Makro nicht funktionieren!

Es ist möglich, dieses Makro so zu ändern, dass es nur den Namen der Schriftart ändert:

Dieses Makro funktioniert jetzt nicht nur wie vorgesehen, sondern ist auch viel leichter zu lesen.

Mauszeigerbewegungen entfernen

Eine weitere Sache, die in Makros aufgezeichnet wird, ist das Auswählen von Arbeitsblättern und Zellen. Dies ist ein Problem, da ein Benutzer leicht den Überblick darüber verlieren kann, woran er gerade gearbeitet hat, wenn sich der Mauszeiger nach der Ausführung eines Makros an eine andere Position bewegt.

Ähnlich wie beim Blättern kann es erforderlich sein, den Mauszeiger zu bewegen und verschiedene Zellen auszuwählen, um eine Aufgabe auszuführen, aber Makros müssen den Mauszeiger 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 einige Probleme mit diesem Code:

• Der Benutzer verliert seine vorherige Position in der Arbeitsmappe
• Das Makro gibt nicht an, von welchem Blatt kopiert wird, was ein Problem darstellen könnte, 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 behoben werden:

In diesem Code ist klar zu erkennen, dass wir von Sheet1 kopieren, und weder das aktive Arbeitsblatt noch der ausgewählte Bereich müssen geändert werden, um die Daten einzufügen. Eine wichtige Änderung ist die Verwendung von „PasteSpecial“ anstelle von „Paste“. Bereichsobjekte, wie „Range(„C4″)“, haben nur Zugriff auf den Befehl PasteSpecial.

Immer wenn der Code voller Verweise auf „.Select“ und „Selection“ ist, ist dies ein Hinweis darauf, dass der Code optimiert und effizienter gestaltet werden kann.