Aufzeichnen eines Makros in Excel - TechTV-Artikel

Dies sind meine Shownotizen von meinem Auftritt in Episode 33 von Call for Help bei TechTV Canada.

VBA-Makrorecorder

Jede seit 1995 verkaufte Excel-Kopie enthält das unglaublich leistungsstarke Visual Basic für Applikationen (VBA), das sich hinter den Zellen versteckt. Wenn Sie Excel haben, haben Sie VBA.

Mit VBA können Sie alles automatisieren, was Sie in Excel tun können, und mehr Dinge tun, die in Excel im Allgemeinen nicht möglich sind.

Zum Glück hat Microsoft einen Makrorecorder in Excel integriert. Auf diese Weise können Sie VBA-Code schreiben, ohne Programmierer zu sein. Leider ist der Macro Recorder fehlerhaft. Es wird kein Code erzeugt, der jedes Mal funktioniert. Der Makrorecorder bringt Sie in die Nähe, aber in vielen Fällen müssen Sie den Code ein wenig korrigieren, damit er zuverlässig funktioniert. In anderen Fällen müssen Sie wissen, wann Sie die relative Aufzeichnung verwenden müssen, damit das Makro funktioniert.

Tracy Syrstad und ich haben VBA und Makros Microsoft Excel 2016 geschrieben, um Ihnen zu helfen, die Einschränkungen des aufgezeichneten Codes zu verstehen und Ihnen beizubringen, wie Sie aufgezeichneten Code in etwas korrigieren, das jedes Mal funktioniert.

In der Show habe ich den Prozess der Aufnahme eines Makros demonstriert, das perfekt funktioniert, wenn Sie wissen, wie man die Schaltfläche Relative Referenz verwendet. Jemand hatte mir ein Excel-Arbeitsblatt mit Hunderten von Namen und Adressen gegeben. Sie wollten Versandetiketten. Wenn Sie jemals die Microsoft Word-Seriendruckfunktion verwendet haben, wissen Sie, dass Sie jedes Feld in einer neuen Spalte im Arbeitsblatt benötigen. Stattdessen wurden in diesem speziellen Arbeitsblatt die Daten in Spalte A angezeigt.

Der Vorgang zum Fixieren eines einzelnen Etiketts ist ziemlich langwierig.

Beginnen Sie mit dem Zellenzeiger auf den Namen in Spalte A.
Drücken Sie den Abwärtspfeil, um zur Adresse zu gelangen
Strg + x zum Schneiden
Pfeil nach oben, Pfeil nach rechts, um zur Spalte B neben dem Namen zu gelangen
Strg + v zum Einfügen
Pfeil zweimal nach unten, Pfeil nach links einmal, um in die Stadt zu gelangen
Strg + x zum Schneiden
Pfeil zweimal nach oben, Pfeil nach rechts dreimal
Strg + v zum Einfügen
Pfeil nach links zweimal, Pfeil nach unten einmal, um zur jetzt leeren Zeile zu gelangen.
Halten Sie die Umschalttaste gedrückt, während Sie zweimal auf den Abwärtspfeil drücken
Alt + edr, um die leeren Zeilen zu löschen
Pfeil nach oben und Pfeil nach unten, um nur den Namen erneut auszuwählen.

Hier ist die Animation, die diese Schritte zeigt:

Einrichten von Excel zum Zulassen von Makros

Obwohl jede Kopie von Excel VBA enthält, deaktiviert Microsoft standardmäßig die Möglichkeit, Makros auszuführen. Sie müssen eine einmalige Anpassung vornehmen, damit Makros ausgeführt werden können. Öffnen Sie Excel. Wählen Sie im Menü Extras> Makro> Sicherheit. Wenn Ihre Makrosicherheitsstufe derzeit auf hoch eingestellt ist, ändern Sie sie auf Mittel.

Vorbereiten der Aufnahme des Makros

Denken Sie über die Schritte nach, die zur Erreichung der Aufgabe erforderlich sind. Sie möchten sicherstellen, dass Sie mit dem Zellenzeiger auf einen Namen beginnen und mit diesem beim nächsten Namen enden. Auf diese Weise können Sie das Makro viele Male wiederholt ausführen. Wenn Sie die Schritte fertig haben, schalten Sie den Makrorecorder ein. Wählen Sie im Menü Extras> Makro> Neues Makro aufnehmen.

Wenn Sie dieses Makro viele Tage lang ausführen, sollten Sie dem Makro einen nützlichen Namen geben. Wenn es sich um ein einmaliges Makro handelt, um eine einmalige Aufgabe zu automatisieren, ist es wahrscheinlich in Ordnung, den Makronamen als Makro1 zu belassen. Das wichtige Feld hier ist das Tastenkombinationsfeld. Wenn Sie ein Makro für die einmalige Verwendung erstellen, weisen Sie das Makro einer Tastenkombination wie Strg + a zu. Es ist ziemlich einfach, Strg + a wiederholt zu drücken, da die Tasten nahe beieinander liegen. Wenn Sie ein Makro erstellen möchten, das Sie jeden Tag verwenden, möchten Sie das Makro einer Taste zuweisen, die noch keine wichtige Tastenkombination ist. Strg + j oder Strg + k sind eine gute Wahl.

Makros zur einmaligen Verwendung sollten in ThisWorkbook gespeichert werden. Wenn Sie das Makro wiederholt in vielen verschiedenen Arbeitsmappen verwenden müssen, können Sie das Makro in der persönlichen Makro-Arbeitsmappe speichern.

Sie erhalten jetzt die kleinste Symbolleiste in ganz Excel. die Symbolleiste Stop Recording. Es hat nur zwei Symbole und sieht folgendermaßen aus:

Wenn Ihnen diese Symbolleiste im Weg steht, drücken Sie nicht das X, um sie zu schließen. Nehmen Sie stattdessen die blaue Leiste und ziehen Sie die Symbolleiste an eine neue Position. Das Verschieben der Symbolleiste wird nicht im Makro aufgezeichnet. Wenn Sie die Symbolleiste versehentlich schließen, rufen Sie sie mit Ansicht> Symbolleisten> Aufzeichnung beenden zurück. Diese Aktion wird jedoch aufgezeichnet.

Die erste Schaltfläche in der Symbolleiste ist die Schaltfläche "Aufnahme beenden". Dies ist selbsterklärend. Wenn Sie mit dem Aufzeichnen des Makros fertig sind, drücken Sie die Symbolleiste zum Beenden der Aufzeichnung.

Die wichtigere (und selten verstandene) Schaltfläche ist die Schaltfläche "Relative Referenz".

In unserem aktuellen Beispiel müssen Sie die Schaltfläche Relative Referenz drücken, bevor Sie beginnen. Wenn Sie ein Makro mit aktivierten relativen Referenzen aufzeichnen, zeichnet Excel folgende Schritte auf:

Bewegen Sie eine Zelle nach unten
Schneiden Sie die aktuelle Zelle
Bewegen Sie eine Zelle nach oben
Bewegen Sie eine Zelle nach rechts
Einfügen
usw.

Wenn Sie das Makro stattdessen ohne relative Referenzen aufzeichnen würden, würde das Makro die folgenden Schritte aufzeichnen:

Gehen Sie zu Zelle A4
Zelle A4 abschneiden
Gehen Sie zu Zelle A3
Gehen Sie zu Zelle B3
In Zelle B3 einfügen
usw.

Dies wäre schrecklich falsch - wir brauchen das Makro, um an Zellen zu arbeiten, die 1 und 2 Zellen vom Startpunkt des Makros entfernt sind. Wenn Sie das Makro immer wieder ausführen, ist der Startpunkt Zeile 4, Zeile 5, Zeile 6 usw. Wenn Sie das Makro ohne aktivierte relative Referenzen aufnehmen, wird das Makro immer in Zeile 3 als Startpunkt ausgeführt.

Folge diesen Schritten:

Wählen Sie die Schaltfläche Relative Referenz in der Symbolleiste Stop Recording
Der Zellenzeiger sollte sich bereits auf einem Namen in Spalte A befinden.
Drücken Sie den Abwärtspfeil, um zur Adresse zu gelangen
Strg + x zum Schneiden
Pfeil nach oben, Pfeil nach rechts, um zur Spalte B neben dem Namen zu gelangen
Strg + v zum Einfügen
Pfeil zweimal nach unten, Pfeil nach links einmal, um in die Stadt zu gelangen
Strg + x zum Schneiden
Pfeil zweimal nach oben, Pfeil nach rechts dreimal
Strg + v zum Einfügen
Pfeil nach links zweimal, Pfeil nach unten einmal, um zur jetzt leeren Zeile zu gelangen.
Halten Sie die Umschalttaste gedrückt, während Sie zweimal auf den Abwärtspfeil drücken
Alt + edr, um die leeren Zeilen zu löschen
Aufwärts- und Abwärtspfeil, um den nächsten Namen in der Liste auszuwählen.
Klicken Sie auf die Symbolleiste Stop Recording
Speichern Sie die Arbeitsmappe
Testen Sie das Makro, indem Sie die oben zugewiesene Tastenkombination drücken. Der nächste Name in der Liste sollte perfekt festgelegt sein

Sobald Sie sehen, dass das Makro wie gewünscht funktioniert, können Sie Strg + a gedrückt halten, um schnell einige Namen pro Sekunde zu korrigieren. Die gesamte Liste von 500 Namen kann in ein oder zwei Minuten festgelegt werden.

Bonus-Tipp - Nicht in der Show

Sie können das Makro einfach in eine einfache Schleife einschließen, um alle 500 Namen zu korrigieren, ohne dass Sie mehrere hundert Mal Strg + drücken müssen.

Drücken Sie die Alt + F11-Taste, um den Makro-Editor zu öffnen.

Wenn der Bereich Projekt - VBAProject nicht angezeigt wird, drücken Sie Strg + r.

Klicken Sie mit der rechten Maustaste auf Modul1 und wählen Sie Code anzeigen. Sie sehen Code, der so aussieht:

Jetzt müssen Sie nicht verstehen, was dieser Code tut, aber Sie wissen, dass wir alles in diesem Makro einmal für jeden Namen ausführen möchten, den wir haben. Wenn Sie wissen, dass es 462 Namen gibt, können Sie 2 Zeilen hinzufügen, um den Code 462 Mal auszuführen. Fügen Sie oben im Makro eine neue Zeile mit den Worten " For i = 1 to 462" ein. Fügen Sie am unteren Rand des Makros eine Zeile mit der Aufschrift " Next i" ein. Dies weist VBA an, den Code innerhalb von 462 Mal auszuführen.

Fazit

Nach diesem einfachen Makro habe ich ein Beispiel in der Show eines sehr komplexen Makros gezeigt. Mit diesem Makro wurden Pivot-Tabellen und -Diagramme für 46 Abteilungen eines Unternehmens erstellt. Dieser Bericht dauerte jeden Monat 40 Stunden. Das VBA-Makro wird jetzt ausgeführt und erstellt alle 46 Berichte in weniger als 2 Minuten.

Das Buch VBA und Makros Microsoft Excel 2016 führt Sie durch die Lernkurve, sodass Sie von der Aufzeichnung einfacher Makros wie diesem bis zur Ausführung sehr komplexer Berichte gelangen, mit denen Sie Hunderte von Stunden pro Jahr sparen können. Wenn Sie VBA nicht lernen möchten, sollten Sie einen Excel-Berater beauftragen, einen Bericht für Sie zu erstellen.