Einfügen von Prozedur – Code (Sub)
Definition
Praktisch jede Aktion, die Sie in Excel per Hand durchführen, kann mit einer Prozedur, also einem Makro sprich VBA – Programm (Sub) automatisiert werden. Es gibt aber auch einige Vorgänge, die ausschließlich mit einem solchen Programm ausgeführt werden können. In dieser Anleitung erkennen Sie, wie Sie damit umgehen und wie Sie vorhandenen Code in Ihre Tabellen einfügen und nutzen können.
Hinweis: Große Teile dieser Anleitung gelten auch für frühere Versionen des Excel, insbesondere ab Version 2000. In jedem Fall aber sind die dargestellten Menüpunkte, manche Abbildungen und der Teil mit den Entwicklertools nicht für ältere Versionen gültig. Mit Excel 2007 ist der Menü-Bereich komplett geändert worden.
Kurz und knapp (für Eilige)
Hier wirklich nur in Stichworten, wie Sie ein Makro/VBA-Code, welchen Sie beispielsweise von einer CD, dem Netz, auf dieser Seite oder ähnlichen Quellen kopiert haben, in eine Excel-Datei einfügen, damit Sie ihn ausführen können:
- Achten Sie darauf, dass die entsprechende Datei (das Workbook) geöffnet und auch aktiv ist.
- Tastenkombination AltF11, um die Entwicklungsumgebung für Makros (den VBA-Editor) zu öffnen.
- Bei Bedarf: StrgR, um den Projekt-Explorer zu öffnen und zu aktivieren.
- Das Workbook oder das gewünschte Arbeitsblatt per Doppelklick markieren. Dieses Objekt ist dort nun blau hinterlegt dargestellt. Dadurch wird im rechten Teil des Fensters der eigentliche Editor (weißer Hintergrund) gezeigt.
- Im Editor den Code schreiben oder den vorher kopierten Code aus der Zwischenablage einfügen.
- Fremder Code: Unbedingt inhaltlich prüfen, ob nicht ungewollte Aktionen durchgeführt werden (Schadprogramme).
- Eigener Code: Noch einmal auf Tippfehler und auch Logikfehler prüfen.
- Zur Sicherheit mit StrgS oder über das Menü speichern. Bei Versionen ab Excel 2007 wählen Sie den Dateityp *.xlsm, damit die Makros mit gespeichert werden.
Hinweis: Das gilt prinzipiell nur für Prozeduren (SUB). Selbst definierte Funktionen (UDF), welche in Arbeitsblättern verwendet werden, müssen in ein Projekt-Modul eingefügt werden. Mehr dazu an dieser Stelle.
Ab sofort steht Ihnen der Code zur Ausführung bereit. Sie können den VBA-Explorer auch wieder schließen.
So weit die Kurzform. Eine wesentlich ausführlichere und mit Screenshots versehene Anleitung folgt in den Zeilen hierunter …
Unsere Muster-Dateien
Damit Sie möglichst detailliert die Arbeitsschritte nachvollziehen können, stellen wir Ihnen hier drei Muster-Dateien zum Download zur Verfügung:
- 10ProzentPlus Die Musterdatei ohne Makro
- 10ProzentPlus_2003 Die Musterdatei, als *.zip gepackt, ohne Makro im 2003er Format
- 10ProzentPlus Die Musterdatei bereits mit dem Makro
Der Sourcecode (das Programm)
In Foren des Internets, Büchern, Zeitschriften, Anleitungen, auf dieser Site, … werden mitunter Excel-Lösungen in Form von VBA-Code angeboten. Sie haben dann mehr oder weniger Zeilen mit einem Programm vorliegen und möchten diese in Ihr Excel-Projekt einbinden. Wie Sie bei diesen Gelegenheiten vorgehen sollten, wird hier nun ausführlich beschrieben.
Angenommen, folgende Aufgabe soll durchgeführt werden: In der Tabelle1 sollen alle Zahlen um 10% erhöht werden. Texte oder als Text formatierte Zellen sollen nicht verändert werden, selbst wenn der Inhalt der Zelle nur aus Ziffern, Vorzeichen und Dezimaltrennzeichen besteht, also eigentlich eine Zahl ist. Und kalendarische Daten, die ja eigentlich auch nichts anderes sind als positive Zahlen, sollen auch so bleiben, wie sie sind. Ausnahme: Der logische Wert WAHR, der wird verändert (was sich aber auch noch unterbinden lässt). So etwas per Hand zu erledigen, kann eine echte Strafe sein. 😕
Diese Aufgabe lässt sich mit Sicherheit per Makro viel besser lösen als „zu Fuß”. Nicht nur, weil es schneller ist, sondern auch der Genauigkeit und der geringeren Fehleranfälligkeit wegen. In der Beispieldatei ist in der Zelle A4 Text, auch wenn es auf den ersten Blick nicht danach aussieht. Sie erkennen es eigentlich nur in der Editierzeile, wo vor dem eigentlichen Inhalt ein Auslassungszeichen steht. Aber es geht hier ja nicht in erster Linie um den Sinn von Makros sondern darum, wie diese in eine Tabelle oder Arbeitsmappe eingebunden werden. Der VBA-Code für diesen Zweck würde beispielsweise so aussehen:
Sub Plus10Prozent()
Dim Zelle As Range
For Each Zelle In Tabelle1.UsedRange
If IsNumeric(Zelle) Then
If Not WorksheetFunction.IsText(Zelle.Value) Then
Zelle.Value = Zelle.Value * 1.1
End If
End If
Next Zelle
End Sub
So, die Programmzeilen, der Sourcecode liegt damit vor. Diesen wollen Sie nun in eine Excel-Datei einbringen, damit das Programm auch ausgeführt werden kann. Dazu müssen Sie das Makro in Excel erst einmal erstellen, was durch Abtippen oder per copy and paste passieren kann. Gehen Sie in jedem Fall dazu so vor:
- AltF11, um den VBA-Editor, die Entwicklungsumgebung zu öffnen.
- Eventuell StrgR, damit der Projekt-Explorer (im Fenster links) bei Bedarf geöffnet und aktiviert wird:
Editor-Fenster nach Alt-F11 Klick
Sie sehen erst einmal nicht so viel. Zu diesem Zeitpunkt haben Sie auch noch nicht die Möglichkeit, den Code in Excel, genauer gesagt den VBA-Editor einzufügen. Sie müssen sich zu diesem Zeitpunkt entscheiden, wo die Prozedur gespeichert werden soll. Indirekt entscheiden Sie damit auch, ob das Programm in der gesamten Mappe oder nur in bestimmten Tabellen ausgeführt werden kann. Im linken Teil des Fensters, dem Projekt-Explorer erkennen Sie eine Auflistung verschiedener Excel-Objekte. Zuoberst ein VBA-Projekt, dessen Namen ich unkenntlich gemacht habe. Er ist hier nicht relevant. Das zweite VBA-Projekt hat bei Ihnen vermutlich den Namen Mappe1. Das ist die derzeitige (neue) Datei, die noch nicht unter einem anderen Namen gespeichert worden ist. Darunter finden Sie die einzelnen, verfügbaren ExcelObjekte mit den Blatt-Namen. Hinweis: Wenn Sie mit den von uns in dieser Mappe zur Verfügung gestellten Muster-Mappen arbeiten, werden Sie erkennen. dass bereits die Namen für das Projekt und die Tabellennamen den Gegebenheiten angepasst sind. Die Frage ist nun: Wo soll dieses Modul „beheimatet” sein, von wo aus soll es aufgerufen und ausgeführt werden können? In unserem Fall ist es (nur) die Tabelle1, wo das Programm abgearbeitet werden soll. Führen Sie also bitte auf genau der Zeile im Projekt-Explorer (links) einen Doppelklick aus. Umgehend wird sich etwas ändern:
Der Editor ist nun geöffnet
Im Projekt-Explorer ist die Zeile Tabelle1 (Tabelle1) markiert, farblich hinterlegt. Im rechten Teil des Fensters ist nun die graue Fläche durch einen Editor (jetzt weißer Hintergrund) mit einem blinkenden Schreibcursor ersetzt worden. Hier können Sie nun die Zeilen per Hand eingeben oder einen aus der Zwischenablage kopierten Code einfügen. Damit ist die Hauptarbeit schon getan, denn ab sofort kann das Makro ausgeführt werden. Hinweis: Überprüfen Sie in jedem Fall, ob der Code eventuell etwas tun könnte, was nicht gewollt ist. Oder um es klar zu sagen: Immer wieder stellen fehlgeleitete „Scherzbolde” Programmzeilen in das Netz, welche nicht nur das tun, was in der Beschreibung angegeben ist. Der Schaden kann sich bis zum totalen Datenverlust der Festplatte ausweiten. Und Spionage-Software ist durchaus auch auf diesem Wege einzuschleusen.
Was – Wann – Wo
Im Projekt-Explorer sind ja in Sachen Speicherort verschiedene Möglichkeiten der Auswahl gegeben. Da stellt sich nun die Frage: Was soll ich wann warum auswählen? Grundsätzlich gilt, dass ein Programm nur dort sichtbar ist, wo es abgespeichert wurde. Wenn Sie also eben den Code dem Objekt Tabelle1 zugeordnet haben, dann ist es nach drücken von AltF11 auch nur dort sichtbar. Beim Doppelklick auf Tabelle2 im Projekt-Explorer wird das Editor-Fenster leer bleiben. Wenn Sie allerdings über die Ribbon-Leiste (Menüband) gehen und dort bei den Entwickler-Tools das zweite Icon Makros auswählen oder alternativ mit AltF8 zum gleichen Ziel gelangen, dann werden Sie das Makro dennoch sehen und auch ausführen können. Aber: Der Code wird in jedem Fall an dem Ort ausgeführt, wo das Makro gespeichert ist. (Dennoch gilt: Ausnahmen bestätigen diese Regel.) Um das einmal an einem kleinen Beispiel zu verdeutlichen, hier ein Mini-Programm, welches in A1 den Text „Hugo Hurtig” schreiben soll:
Sub Hugo()
Cells("A1") = "Hugo Hurtig"
End Sub
Fügen Sie diese drei Zeilen bitte unterhalb des vorhandenen Codes in den Editor für Tabelle1 ein. Wechseln Sie nun bitte in Excel (bei den Tabellen) in die Tabelle2, damit dieses die aktuelle, aktive Tabelle ist. AltF8 und führen Sie dann das Makro Tabelle1.Hugo aus. Offensichtlich geschieht nichts. Aber wenn Sie zur Tabelle1 wechseln, sehen Sie, dass der Code korrekt ausgeführt worden ist. In A1 steht der Text Hugo Hurtig.
Hinweis: Falls Sie den Menüpunkt Entwicklertools nicht sehen können, bekommen Sie hier Hilfe.
Aus dem Grunde ist es sinnvoll, alle Makros, die unabhängig von dem derzeit aktiven Tabellenblatt in der (kompletten) aktuellen Arbeitsmappe etwas ausführen sollen, in DieseArbeitsmappe (erste Auswahl innerhalb des Projekts) zu speichern.
Diese Formulierung „schreit” förmlich nach zwei Fragen: Warum haben denn die einzelnen Tabellen denn überhaupt eine Speichermöglichkeit für VBA-Code und wo speichere ich Makros, die ich in allen möglichen Arbeitsmappen verwenden will und nicht nur der aktuellen?
Eine Antwort ist oben schon gegeben. Wenn sich der Code immer nur auf eine bestimmte Tabelle beziehen soll, dann bietet sich diese Tabelle auch als Speicherort an. Dazu kommt noch der Vorteil, das bei etwas „lockerer” (genauer gesagt: ungenauer) Programmierung ein Codea dann auch mal im aktuellen Fenster ausgeführt wird und dabei vielleicht wichtige Daten unwiederbringlich überschreibt. Und behaupten Sie nicht, dass Sie in jedem Fall solche „Schlampigkeiten” unterlassen. Es wird gewiss kein Vorsatz sein … 😉
Weiterhin gilt: UserForms, also Eingabeformulare, Bildschirmmasken werden in einem eigenen Bereich des Projekts gespeichert. VBA-Programme, welche in der gesamten Mappe gültig sein sollen, gehören in den Bereich mit dem Namen DieseArbeitsmappe. Später, wenn Sie sich in Richtung „Profi” entwickelt haben, dann werden Sie gezielter mit diesen Möglichkeiten arbeiten.
Und warum sollten Sie nicht gleich alles in DieseArbeitsmappe speichern? Nun ja, theoretisch ist das durchaus möglich. Aber wenn der Programmcode nicht sehr gut „abgesichert” ist, dann wird manch eine Anweisung ungewollter Weise im aktuellen Tabellenblatt ausgeführt. Oder aber, ein Tabellenblatt wird umbenannt, dann kann von der zentralen Stelle nur darauf zugegriffen werden, wenn der Code auch angepasst wird.
Wenn Sie eine selbst definierte Funktion (UDF, User Defined Function) speichern wollen, dann brauchen Sie einen neuen Speicherort für das Projekt. Die Function muss in einem Modul gespeichert werden. Um solch ein Modul, einen neuen Container für den Code, zu schaffen, Klicken Sie auf die in der folgenden Abbildung gezeigte Auswahl:
Modul-Speicherplatz für selbst definierte Funktionen
Alternativ können Sie im VBA-Fenster auch über das Menü Einfügen gehen und dort das Modul wählen. Ihr Editor-Fenster zeigt sich nun etwas anders:
Hier können Sie sogar auch den Namen des Moduls ändern
Im linken Teil des Fensters (Projekt-Explorer) hat sich nun etwas getan. Der Ordner Module sowie der Eintrag Modul1 ist hinzu gekommen. Der Bereich des Editors hat sich nicht verändert.
Da die zuerst vorgestellte Prozedur, welche die Zahlen um die 10% erhöht, eigentlich sinnvoller in DieseArbeitsmappe aufgehoben sein könnte, sollten Sie den kompletten Sourcecode in Tabelle1 ausschneiden und in DieseArbeitsmappe wieder einfügen. Das dient auch ein wenig der Übung zum Umgang mit dem Editor.
Ausführen des Programms (ab 2007)
Bislang liegt der Code ja nur so da. Sie könnten ihn direkt im VBA-Editor ausführen, aber das ist nicht der Sinn der Sache. Sie können ein Makro auch als User sehr einfach aufrufen, indem Sie beim Menü Entwicklertools auf Makros Klicken und dann in der Auswahl das gewünschte Makro doppelklicken. Dadurch wird das Makro ohne Rückfrage ausgeführt.
Aufruf eines Makros über das Menüband
Hinweis: In älteren Excel-Versionen finden Sie den Punkt bei Extras | Makro | Makros… und gelangen dann zum gleichen Fenster, wie in der folgenden Abbildung gezeigt.
Es können auch mehrere Prozeduren zur Auswahl stehen
Natürlich können Sie in diesem Fenster auch auf Ausführen Klicken, um das Makro zu starten. Wahrscheinlich sind Sie oben beim Test der Sub Hugo schon diesen Weg gegangen. Und bei Schritt wird das Makro Schritt für Schritt ausgeführt, um eventuelle Fehler zu erkennen.
Hinweis: Wenn Sie eine Excel-Datei mit einem integrierten Makro in einer Programm-Version ab 2007 laden, dann ist es durchaus möglich, dass vorhandene Makros nicht ausgeführt werden können. Die standardmäßigen Sicherheitseinstellungen verhindern dieses. In dem Fall müssen Sie nach dem Öffnen des Arbeitsblattes die entsprechende Schaltfläche anklicken. Mehr dazu lesen Sie hier in einem neuen Fenster.
Versionen 2000 bis 2003
Wie bereits erwähnt, vieles ist sehr ähnlich. Darum werde ich an dieser Stelle viele Texte von oben übernehmen jedoch nicht alle Punkte ausführlich beschreiben. Die Abbildungen sind weitgehend mit integriert. Im Zweifel schauen Sie bitte im oberen Teil noch einmal nach.
Der Sourcecode (das Programm)
Den eigentlichen Programmcode finden Sie im oberen Bereich. Das Vorgehen ist hier wie bei der Version 2007. Der Weg über das Menü ist möglich, aber anders als bei der 2007er Version. Gehen Sie also wie oben schon gezeigt so vor:
- AltF11, um den VBA-Bereich zu öffnen:
Editor-Fenster nach Alt-F11 Klick
Sie sehen erst einmal nicht so viel. Zu diesem Zeitpunkt haben Sie auch nicht die Möglichkeit, den Code in Excel einzufügen. Sie müssen sich an dieser Stelle entscheiden, wo die Prozedur gespeichert werden soll und indirekt entscheiden Sie damit auch, wo das Programm aufgerufen werden kann und wo nicht.
Im linken Teil des Fensters erkennen Sie eine Auflistung verschiedener Excel-Objekte. Zuoberst ein VBA-Projekt, dessen Namen ich unkenntlich gemacht habe. Er ist hier nicht relevant. Das zweite VBA-Projekt hat den Namen Mappe1. Das ist die derzeitige Datei, die noch nicht unter einem anderen Namen gespeichert worden ist. Darunter finden Sie die einzelnen, verfügbaren Excel-Objekte .
Die Frage war ja nun: Wo soll dieses Modul „beheimatet” sein, von wo aus soll es ausgeführt werden können? In unserem Fall ist es die Tabelle1, wo das Programm abgearbeitet werden soll. Führen Sie also bitte auf dieser Zeile im Projekt-Explorer (so nennt sich dieser Teil) einen Doppelklick aus. Umgehend wird sich etwas ändern:
Der geöffnete VBA-Editor
Im rechten Teil des Fensters ist die graue Fläche durch einen Editor mit einem blinkenden Schreibcursor ersetzt worden. Hier können Sie den Code per Hand eingeben oder aus der Zwischenablage einfügen. Damit ist die Hauptarbeit schon getan, denn ab sofort kann das Makro ausgeführt werden.
Was – Wann – Wo
Im Projekt-Explorer sind ja nun verschiedene Möglichkeiten der Auswahl gegeben. Da stellt sich die Frage: Was soll ich wann wo auswählen? Grundsätzlich gilt, dass ein Programm nur dort sichtbar ist, wo es abgespeichert wurde. Wenn Sie also eben den Code dem Objekt Tabelle1 zugeordnet haben, dann ist es nach drücken von AltF11 auch nur dort sichtbar. Wenn Sie allerdings über das Menü gehen …:
Aufruf über das Menü
… haben Sie natürlich keine Ribbons sondern das klassische Menü. Sie erkennen auch, dass die Tastenkombinationen nicht gewechselt haben. Wählen Sie den obersten Punkt rechts, was AltF8 entspricht, um zum gleichen Ziel gelangen, dann werden Sie das Makro dennoch sehen und ausführen können.
Auswahl über Alt + F8
Darum ist es sinnvoll, reine Makros, die nur in der aktuellen Arbeitsmappe etwas ausführen, in DieseArbeitsmappe (erste Auswahl innerhalb des Projekts) zu speichern.
Diese Formulierung „schreit” förmlich nach zwei Fragen: Warum haben denn die einzelnen Tabellen denn eine Speichermöglichkeit für VBA-Code und wo speichere ich Makros, die ich in allen möglichen Arbeitsmappen verwenden will und nicht nur der aktuellen?
In den Tabellen werden vorwiegend UserForms gespeichert, also Eingabeformulare, Bildschirmmasken. Allerdings können Sie diese auch in der Arbeitsmappe speichern. Später, wenn Sie sich in Richtung „Profi” entwickelt haben, dann werden Sie gezielter mit diesen Möglichkeiten arbeiten.
Wenn Sie eine Sub-Prozedur nicht nur im aktuellen Projekt ausführen wollen sondern es global nutzen möchten, dann brauchen Sie einen neuen Speicherort für das Projekt. Die Sub-Prozedur muss in einem Modul gespeichert werden. Das gilt übrigens auch für alle Funktionen, welche Sie in Excel einfügen wollen. Um solch ein Modul, einen neuen Container für den Code, zu schaffen, Klicken Sie auf die in der folgenden Abbildung gezeigte Auswahl:
Ein neues allgemeines Modul
Alternativ können Sie im VBA-Fenster auch über das Menü Einfügen gehen und dort das Modul wählen. Ihr Editor-Fenster zeigt sich nun etwas anders:
Das Modul – Fenster, wo Sie auch den Namen des Moduls ändern können
Im linken Teil des Fensters hat sich nun etwas getan. Der Bereich des Editors hat sich nicht verändert. Wie gesagt, prinzipiell können Sie auch hier Ihr VBA-Programm speichern, aber es ist nur dann sinnvoll, wenn es entweder eine Funktion (Function) ist oder diese Prozedur universell einsetzbar sein soll.
Da die Prozedur eigentlich sinnvoller in DieseArbeitsmappe aufgehoben ist, sollten Sie den kompletten Sourcecode in Tabelle1 ausschneiden und in DieseArbeitsmappe wieder einfügen.
Ausführen des Programms
Bislang liegt der Code ja nur so da. Sie könnten ihn direkt im VBA-Editor ausführen, aber das ist nicht der Sinn der Sache. Sie können ein Makro auch als User sehr einfach aufrufen, indem Sie beim Menü Extras auf Makro und dann auf Makros… Klicken
Programm-Aufruf über das Menü
… und dann in der Auswahl das gewünschte Makro doppelklicken.
Per Doppelklick das Makro auswählen
Dadurch wird das Makro ohne Rückfrage ausgeführt.
Natürlich können Sie in diesem Fenster auch auf Ausführen Klicken, um das Makro zu starten. Und bei Schritt wird das Makro Schritt für Schritt ausgeführt, um eventuelle Fehler aufzuspüren und diese zu erkennen.
Im Gegensatz zu der 2007er Version ist der entsprechende Menüpunkt für die Makros immer vorhanden und sichtbar. Die Vorgehensweise für die neue Version ist oben beschrieben.
Sie haben zusätzlich auch die Möglichkeit, ein Makro bei jedem Start der Mappe automatisch ausführen zu lassen oder solch ein Modul an eine selbst erstellte Schaltfläche zu binden oder auch einen eigenen Menüpunkt dafür zu schaffen. Anregungen dazu finden Sie im Internet. Und natürlich bei unseren Seminaren.
[NachObenLetzte Verweis=„T&T: Prozedur einfügen”]