Excel-Makros: So optimieren Sie Ihren Arbeitsprozess mit der beliebten Tabellenkalkulation
Wer regelmäßig mit Textverarbeitungsprogrammen, Datenbanksystemen oder Tabellenkalkulations-Anwendungen arbeitet, weiß, wie mühsam es sein kann, die gleichen Anweisungen immer und immer wieder eingeben zu müssen. Vor allem komplexe, mehrstufige Programmabläufe werden so schnell zur nervenaufreibenden Geduldsprobe. Abhilfe schaffen sogenannte Makros (engl. macro (instruction) = „Groß-Befehl“), die eine Folge von Befehlen aufzeichnen, sodass diese vom Nutzer der Software zu einem beliebigen Zeitpunkt automatisch ausgeführt werden können. Obwohl Makros in einer Programmiersprache geschrieben sind, sind Programmierkenntnisse in der Regel nicht notwendig, denn viele Programme – darunter auch Excel – können simple Makros aufzeichnen und so bei der Entwicklung Hilfestellungen geben. Für Microsofts Tabellenkalkulations-Software Excel sind Makros eines der wichtigsten Features.
Domain kaufen
Registrieren Sie Ihre perfekte Domain - Inklusive Wildcard-SSL-Zertifikat
- Inklusive Domain Lock
- Inklusive 2 GB E-Mail-Postfach
Was ist ein Excel-Makro?
Microsoft Excel gehört zu den gefragtesten Lösungen, um Daten zu bearbeiten, zu analysieren und zu präsentieren. Mit der Tabellenkalkulation, die Teil der Microsoft-Office-Suite ist, arbeiten Windows-Nutzer seit Jahren, um beispielsweise den Haushaltsplan zu erstellen oder einen individuellen Kalender zu kreieren. Auch in Unternehmen kommt Excel zum Einsatz: Mit der Software sind Projektpläne, Stundenlisten oder Budgetplanungen ebenso einfach realisiert wie grafische Darstellungen von Umsatzzahlen, Gewinnen oder Verlusten. Wer sich mit der Bedienung des Programms vertraut gemacht hat, lernt die zahlreichen Funktionen zu schätzen – entwickelt aber auch schnell eine nur schwer zu unterdrückende Abneigung gegen sich ständig wiederholende Routineaufgaben oder Aktionen, die sich nicht einfach mithilfe der Standardbenutzeroberfläche ausführen lassen.
Es kommt also nicht von ungefähr, dass die Möglichkeit, Makros in Excel zu erstellen, zu den Kernfeatures des Kalkulationsprogramms zählt. Das zu diesem Zweck integrierte Tool zum Makro-Aufzeichnen bedient sich der Skriptsprache Visual Basic for Applications (VBA), die auch in den Microsoft-Office-Programmen Word, PowerPoint, Access und Outlook zum Einsatz kommt. Dank der Sprache können Sie unkompliziert Ihre eigenen Excel-Makros erstellen, die erwähnten Routinebefehle automatisch ausführen lassen oder der Tabellenkalkulation neue Funktionen (z. B. Algorithmen für die Datenanalyse) hinzufügen.
Es kommt also nicht von ungefähr, dass die Möglichkeit, Makros in Excel zu erstellen, zu den Kernfeatures des Kalkulationsprogramms zählt. Das zu diesem Zweck integrierte Tool zum Makro-Aufzeichnen bedient sich der Skriptsprache Visual Basic for Applications (VBA), die auch in den Microsoft-Office-Programmen Word, PowerPoint, Access und Outlook zum Einsatz kommt. Dank der Sprache können Sie unkompliziert Ihre eigenen Excel-Makros erstellen, die erwähnten Routinebefehle automatisch ausführen lassen oder der Tabellenkalkulation neue Funktionen (z. B. Algorithmen für die Datenanalyse) hinzufügen.
So funktionieren Makros in Excel
Excel stellt die verschiedenen Elemente der Bedienoberfläche in Form von hierarchisch organisierten Programm-Objekten zur Verfügung. Diese besitzen jeweils spezifische Eigenschaften und Methoden. Alle verfügbaren Objekte stehen im hierarchischen Objektmodell miteinander in Beziehung und spiegeln sich ungefähr in der visuellen Benutzeroberfläche wider, die Ihnen Schalt- und Eingabeflächen zur Interaktion mit der Anwendung bereitstellt. Indem Sie die Eigenschaften festlegen und ihre Methoden aufrufen, bearbeiten Sie die verschiedenen Objekte. Für das Objekt „Workbook“ existieren beispielweise die Methode „Close“, mit deren Hilfe Sie die ausgewählte Arbeitsmappe schließen, sowie die Eigenschaft „ActiveSheet“, die das Blatt darstellt, das aktuell in der Arbeitsmappe aktiv ist.
Mithilfe von Auflistungen (Objekte, die durch Plural-S gekennzeichnet werden) können Makros darüber hinaus auch Aktionen auf eine ganze Gruppe von Objekten ausführen. Das Auflistungsobjekt „Worksheets“ bewirkt in einem Makro beispielsweise, dass die Anweisungen für alle Arbeitsblätter gelten. Für die Ausführung eines Makros haben Sie folgende drei Optionen:
Mithilfe von Auflistungen (Objekte, die durch Plural-S gekennzeichnet werden) können Makros darüber hinaus auch Aktionen auf eine ganze Gruppe von Objekten ausführen. Das Auflistungsobjekt „Worksheets“ bewirkt in einem Makro beispielsweise, dass die Anweisungen für alle Arbeitsblätter gelten. Für die Ausführung eines Makros haben Sie folgende drei Optionen:
- Auswahl im Makromenü
- Mausklick auf eine individuell erstellte Schaltfläche
- Individuelle Tastenkombination
Die Vorteile von Excel-Makros
Wenn Sie bisher – bewusst oder unbewusst – darauf verzichtet haben, Makros in Excel zu erstellen, muss sich das nicht zwangsläufig negativ auf das Ergebnis Ihrer Excel-Dateien ausgewirkt haben. Allerdings haben Sie damit den umständlichen Weg für Ihre Arbeiten gewählt. Denn unübersehbar bietet die Makro-Technik einige entscheidende Vorteile, die sie zum Pflichtprogramm für jeden macht, der das Optimum aus der Kalkulations-Software herausholen möchte:
- Makros verringern die Fehlerquote: Mit jeder Anweisung, die Sie in Excel manuell eingeben, steigt auch die Wahrscheinlichkeit, eine falsche Eingabe zu tätigen. Vor allem in komplexen Befehlsfolgen und sich oft wiederholenden Arbeitsschritten kann einem schnell ein Fehler unterlaufen, der die komplette Funktionalität blockiert. Bei einem Makro birgt einzig der Erstellungsprozess Fehlerpotenzial. Haben sich hierbei keine Irrtümer eingeschlichen, funktioniert das Excel-Makro bei jeder Nutzung fehlerfrei.
- Makros reduzieren den Arbeitsaufwand: Makros erstellen Sie nur ein einziges Mal. Anschließend starten Sie die praktischen Befehlszusammenfassungen so oft Sie wollen mit nur einem Klick bzw. mit der passenden Tastenkombination. Das spart Ihnen eine Menge Zeit, die Sie fortan in andere Arbeitsbereiche stecken können.
- Makros erweitern Excel: Mit VBA können Sie nicht nur Makros programmieren, die Befehle zusammenfassen, sondern auch neue Funktionen entwickeln. So erweitern Sie die integrierte Funktionssammlung um Ihre benutzerdefinierten Arbeitsblattfunktionen und vereinfachen Ihre Formeln erheblich. Praktischerweise präsentiert Excel auch eigens erstellte Funktionen so, als würde es sich um native Funktionen handeln. Darüber hinaus haben Sie die Möglichkeit, sämtliche erstellten Makros mit eigenen Schaltflächen in der Symbolleiste zu verknüpfen, über die sich diese starten lassen.
Wie Sie ein eigenes Excel-Makro erstellen
Damit Sie eine eigene Befehlssammlung erstellen können, müssen Sie zunächst den VBA-Editor aktivieren, der Teil der Entwicklertools ist. Diese sind standardmäßig nicht über die Menüleiste (die in Microsoft-Office-Anwendungen auch „Menüband“ heißt) erreichbar, weshalb Sie die entsprechende Registerkarte im ersten Schritt hinzufügen.
Schritt 1: Registerkarte „Entwicklertools“ hinzufügen
Wählen Sie hierfür die Registerkarte „Datei“ aus und klicken auf „Optionen“. Unter dem Punkt „Menüband anpassen“ finden Sie eine Auflistung der Hauptregisterkarten, zu denen auch die „Entwicklertools“ zählen. Setzen Sie im betreffenden Kästchen ein Häkchen und bestätigen Sie die Änderung mit einem Klick auf „OK“. Die Karte erscheint in der Folge im Menüband.
Schritt 2: Eine neue Arbeitsmappe für Makros erstellen
Im zweiten Schritt geht es darum, eine neue Excel-Mappe anzulegen, die aufgezeichneten Makros als Basis dienen soll. Dafür klicken Sie in der soeben freigeschalteten Registerkarte „Entwicklertools“ auf die Schaltfläche „Makros“, geben unter „Makroname“ den Namen der neuen Mappe an (hier beispielhaft „Hallo“) und bestätigen diesen per Klick auf „Erstellen“, woraufhin sich der VBA-Editor inklusive des folgenden Codes öffnet: Sub steht hierbei für Subroutine und somit für ein Makro, das an sich eine Art Unterprogramm von Excel darstellt. Für das jeweilige Makro wird der gesamte Code ausgeführt, der zwischen Sub und End Sub steht. Für einen kleinen Test können Sie den Code des hier verwendeten Beispiel-Makros „Hallo“ folgendermaßen erweitern:
Sub Hallo()
MsgBox ("Hallo Welt!")
End Sub
Speichern Sie das Ergebnis im .xslm-Format, wechseln Sie zurück in die Excel-Programmoberfläche, betätigen Sie erneut die Schaltfläche „Makros“ und wählen Sie das „Hallo“-Makro in der angezeigten Liste aus. Wenn Sie nun auf „Ausführen“ klicken, erscheint ein kleines Informationsfenster mit dem soeben definierten Textinhalt
Per Klick auf die Schaltfläche „OK“ schließen Sie das Fenster und beenden die Ausführung des Makros. Speichern Sie anschließend die Makro-Arbeitsmappe im VBA-Editor.
Schritt 3: Eine Schnellzugriff-Schaltfläche für Makros erzeugen
Sowohl in der Registerkarte „Entwicklertools“ als auch in der Karte „Ansicht“ können Sie verfügbare Makros in Excel anzeigen lassen und ausführen. Wenn Sie die Befehlszusammenfassung allerdings häufiger benötigen, ist es sinnvoll, eine Schaltfläche auf der Symbolleiste für den Schnellzugriff zu erzeugen. Gehen Sie dabei folgendermaßen vor:- Rufen Sie die Registerkarte „Datei“ auf.
- Öffnen Sie die Optionen und klicken auf den Menüeintrag „Symbolleiste für den Schnellzugriff“.
- Wählen Sie in der Liste „Befehle auswählen“ den Eintrag „Makros“ aus und suchen Sie dort das bereits erstellte „Hallo“-Makro.
- In dem Sie dieses markieren und auf „Hinzufügen“ klicken, übertragen Sie es in die Liste der Schaltfächen für den Schnellzugriff.
- Bevor Sie die neue Schaltfläche via „OK“ bestätigen, können Sie zunächst noch das später angezeigte Symbol bestimmen. Wählen Sie dazu einfach das Makro aus und klicken Sie auf „Ändern“.
Hinweis: Alternativ können Sie auch eine Tastenkombination für die Ausführung des Makros festlegen („Entwicklertools“ -> „Makros“ -> „Optionen“).
Schritt 4: Die Makroaufzeichnung nutzen
Der VBA-Editor, den Microsoft in seine Office-Anwendungen integriert hat, bietet Ihnen den Vorteil, dass Sie die Programmiersprache nicht beherrschen müssen, um einfache Excel-Makros zu erstellen. Zu diesem Zweck ist die integrierte Funktion, Makros aufzuzeichnen, nämlich vollkommen ausreichend. Beispielhaft erstellen wir an dieser Stelle ein Makro, das ein Arbeitsblatt automatisch umbenennt.- In den Entwicklertools wählen Sie dieses Mal die Schaltfläche „Makro aufzeichnen“.
- Im folgenden Dialogfenster geben Sie dem Makro den Namen „RenameWorksheets“ und starten im Anschluss mit „OK“ die Aufzeichnung.
- Benennen Sie nun „Tabelle1“ in „Neuer Name“ um und beenden Sie die Makroaufzeichnung durch einen Klick auf „Aufzeichnung beenden“.
Bei den ersten vier Codezeilen unter der Sub-Zeile, die jeweils mit einem Apostroph beginnen, handelt es sich um Kommentarzeilen. Diese haben keinerlei Einfluss auf die allgemeine Funktionalität des Makros und dienen in erster Linie der besseren Verständlichkeit des Codes. Darüber hinaus kann man Codezeilen mithilfe von Kommentaren auch vorübergehend deaktivieren. Für das Beispiel-Makro benötigen Sie die vier automatisch erzeugten Zeilen nicht, weshalb Sie diese bedenkenlos löschen können.
Die nächste Zeile enthält die Select-Methode, das Auswählen von „Tabelle1“, das bei der manuellen Durchführung der Neubenennung zunächst nötig war, bevor Sie dieser einen neuen Namen geben konnten. VBA-Skripte müssen Objekte jedoch nicht auswählen, um sie bearbeiten zu können, weshalb Sie auch diese Codezeile nicht für das Excel-Makro benötigen. Komplett überarbeitet sieht der Code folgerichtig so aus:
Die nächste Zeile enthält die Select-Methode, das Auswählen von „Tabelle1“, das bei der manuellen Durchführung der Neubenennung zunächst nötig war, bevor Sie dieser einen neuen Namen geben konnten. VBA-Skripte müssen Objekte jedoch nicht auswählen, um sie bearbeiten zu können, weshalb Sie auch diese Codezeile nicht für das Excel-Makro benötigen. Komplett überarbeitet sieht der Code folgerichtig so aus:
Sub RenameWorksheets ()
Sheets("Tabelle1").Name = "Neuer Name"
End Sub
Wechseln Sie im Anschluss zurück zu Excel und benennen Sie den Arbeitsbogen wieder in „Tabelle1“ um. Führen Sie nun testweise das RenameWorksheets-Makro aus, sollte sich der Name automatisch ändern. Beachten Sie, dass Sie das Makro nun an den neuen Namen anpassen müssen, um es erneut auszuführen.
Excel-Makros in der Praxis: Diagramme und Eingabemasken
VBA-Skripte ermöglichen die Automatisierung verschiedenster Excel-Aufgaben. Mithilfe der Aufzeichnungsmöglichkeit können Sie nicht nur auf einfache Weise versuchen, den eigenen Arbeitsprozess zu vereinfachen, sondern erwerben nebenbei auch Wissen über die leistungsfähige Programmiersprache. Zögern Sie also nicht, das VBA-Tool zu nutzen, wenn Sie vermuten, dass ein Excel-Makro einen bestimmten Arbeitsschritt erleichtern kann.
Nachdem wir in den vorangegangenen Absätzen die allgemeine Vorgehensweise bei der Makro-Erstellung vorgestellt haben, sollen die folgenden zwei Beispiele Ihnen ganz konkret die Vielseitigkeit der Befehlsautomatisierung vor Augen führen.
Nachdem wir in den vorangegangenen Absätzen die allgemeine Vorgehensweise bei der Makro-Erstellung vorgestellt haben, sollen die folgenden zwei Beispiele Ihnen ganz konkret die Vielseitigkeit der Befehlsautomatisierung vor Augen führen.
Excel-Evergreen: Diagramm auf Basis eines Zellbereichs erstellen
Eine verbreitete Funktion von Excel-Tabellen ist die Visualisierung eingegebener Daten in Form eines Diagramms. Bei dieser oft mühsamen Angelegenheit kann ein Makro wertvolle Hilfestellung leisten. Erstellen Sie zu diesem Zweck zunächst ein Makro mit dem Namen „AssortedTasks“ und deklarieren die Variable für Ihr Diagrammobjekt:Dim meinDiagramm As Chartobject
In der nächsten Zeile erstellen Sie dann ein Diagrammobjekt, dem Sie diese meinDiagramm-Variable zuweisen:
Set meinDiagramm = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
Die Werte, die sich in der Klammer befinden, bestimmen die Position sowie die Größe des Diagramms. Bei den ersten beiden Daten handelt es sich um die Koordinaten der linken oberen Ecke, anschließend kommen die Werte für die Breite und die Höhe. Wenn Sie das Makro nun ausführen, erstellt Excel das Objekt automatisch an der vorgegebenen Position und in der definierten Größe – allerdings ist es natürlich noch leer, da bis zum jetzigen Zeitpunkt die Input-Daten fehlen.
Im letzten Schritt füttern Sie das Diagrammobjekt also mit Daten, die dann entsprechend visualisiert werden können. Dazu ist einerseits die Eingabe der Werte nötig, andererseits eine weitere Anpassung des Excel-Makros, denn bisher fehlt diesem noch die Information darüber, woher es die darzustellenden Informationen nehmen soll. Im Rahmen der Konstruktion With … End With führen Sie aus diesem Grund nun die SetSourceData-Methode ein und spezifizieren diese durch den Wert Selection, der bewirkt, dass beim Ausführen des Makros die Werte aller Zellen berücksichtigt werden, die markiert sind. Der Code des kompletten Makros sieht danach folgendermaßen aus:
Sub AssortedTasks()
Dim meinDiagramm As ChartObject
Set meinDiagramm = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
With meinDiagramm
.Chart.SetSourceData Source := Selection
End With
End Sub
Geben Sie nun zu Testzwecken die Werte 1 bis 5 in die Excel-Zellen A1 bis A5 ein und führen das Makro aus, werden Ihnen die Daten als Balkendiagramm präsentiert. Diesen Diagrammtyp erzeugt Excel standardmäßig, insofern Sie keine zusätzlichen ChartType-Angaben machen.
So erstellen Sie ein Dialogfenster für Nutzereingaben
Mithilfe des Makro-Tools können Sie aber auch Eingabemasken erstellen, über die der Nutzer mit Excel interagieren kann. Um z. B. ein Interaktionsfenster zu erstellen, dessen Eingabewert automatisch in eine definierte Zelle geschrieben wird, beginnen Sie wie gewohnt mit dem Anlegen und Benennen des Makros. In unserem Beispiel geben wir der Befehlssammlung den Namen „Eingabemaske“. Anschließend nennen Sie der Subroutine den Zielort des später vom Nutzer eingegebenen Wertes:Sub Eingabemaske()
ActiveSheet.Range("A1").Value =
End Sub
Das Makro sorgt also dafür, dass die Eingabe des Nutzers in die Zelle A1 der Tabelle1 übertragen wird. Der Wert des Feldes (Value) muss diesem nun noch durch den zweiten Teil der Anweisung zugeteilt werden. Wir haben uns im Beispiel für das Kommando InputBox entschieden, das dem MsgBox-Befehl, der im „Hallo“-Makro zum Einsatz kam, sehr ähnlich ist. Kombiniert mit folgenden drei Argumenten, die im VBA-Code in Anführungszeichen stehen, entsteht ein zweckmäßiges Eingabefenster:
- Prompt: Mithilfe des ersten Arguments definieren Sie den Text des Dialogfensters, der den Nutzer bei der Eingabe unterstützt.
- Title: Das „Title“-Argument stellt die Überschrift der Eingabemaske dar.
- Default: Schließlich können Sie mit „Default“ auch eine Standardeingabe vorgeben.
Sub Eingabemaske()
Tabelle1.Range("A1").Value = InputBox("Bitte geben Sie einen Wert für die Zelle A1 an.", "Überschrift der Eingabemaske", "Wert für Zelle A1")
End Sub
Führen Sie dieses Excel-Makro aus, erscheint das entsprechende Dialogfenster:
Weitergabe von Excel-Makros
Wenn Sie in Ihrer Excel-Arbeitsmappe Makros erstellt haben, können Sie diese problemlos in einem anderen Dokument nutzen oder an andere Nutzer weitergeben. Der VBA-Editor besitzt zu diesem Zweck eine Import/Export-Funktion, mit deren Hilfe Sie Ihre Befehlssammlungen im .bas-Format abspeichern bzw. im .bas-Format gespeicherte Makros in Ihr aktuelles Excel-Dokument einbinden. Voraussetzung ist jedoch, dass Makros im Zieldokument aktiviert sind. Da die praktischen Skripte potenziell sicherheitsgefährdende Codes enthalten können, macht Excel zum Schutz der Nutzer hier standardmäßig Einschränkungen, sodass beispielsweise alle Makros ohne bzw. mit Benachrichtigung oder gar alle Makros ohne digitale Signatur blockiert werden. Die einfache Lösung ist, alle Makros automatisch zuzulassen, wobei Sie vor dem Import unbedingt sichergehen sollten, dass die Codes vertrauenswürdig sind.
Zu finden sind die Optionen zum Aktivieren und Deaktivieren von Makros im „Trust Center“ (bei älteren Versionen „Sicherheitscenter“): Rufen Sie einfach die Registerkarte „Datei“ auf, wählen „Optionen“, „Trust Center“ und klicken auf die Schaltfläche „Einstellungen für das Trust Center“. Dort finden Sie den Menüpunkt „Einstellungen für Makros“ inklusive der erwähnten Einstellungsmöglichkeiten.
Zu finden sind die Optionen zum Aktivieren und Deaktivieren von Makros im „Trust Center“ (bei älteren Versionen „Sicherheitscenter“): Rufen Sie einfach die Registerkarte „Datei“ auf, wählen „Optionen“, „Trust Center“ und klicken auf die Schaltfläche „Einstellungen für das Trust Center“. Dort finden Sie den Menüpunkt „Einstellungen für Makros“ inklusive der erwähnten Einstellungsmöglichkeiten.
Um nun ein Makro zu exportieren, öffnen Sie den Visual-Basic-Editor, klicken in der Menüleiste auf den Reiter „Datei“ und anschließend auf „Datei exportieren“. Geben Sie Ihrer Makro-Sammlung einen aussagekräftigen Namen, wählen Sie das gewünschte Verzeichnis aus und schließen Sie den Export mit „Speichern“ ab. Die exportierte Datei ist nur wenige Bytes groß und lässt sich somit bequem via Mail oder über ein portables Speichermedium auf den gewünschten Zielrechner transportieren. Um eine Makro-Datei zu importieren, gehen Sie zunächst gleich vor, wählen logischerweise aber „Datei importieren“ und geben anschließend den Speicherort der Datei an.
HiDrive Cloud-Speicher
Ihr sicherer Online-Speicher - Daten zentral speichern, teilen und bearbeiten
- Serverstandort Deutschland (ISO 27001-zertifiziert)
- Höchste Datensicherheit im Einklang mit der DSGVO