Microsoft Excel Programmierung – Das Handbuch

Mourad Louha

Monika Weber

Published by Microsoft Press Deutschland

Vorwort

Microsoft Excel gehört zu den weltweit am meisten genutzten Anwenderprogrammen für die Tabellenkalkulation. Aus dem betrieblichen Alltag und auch aus dem privaten Umfeld ist es kaum mehr wegzudenken.

Mit der aktuellen Version Excel 2013 können Sie komplexe Berechnungen und Auswertungen mithilfe von über 450 Funktionen durchführen.

Dies reicht für sehr viele Anwendungszwecke aus – falls aber nicht, steht Ihnen mit Visual Basic for Applications eine in Excel integrierte Programmiersprache zur Verfügung, um eigene Erweiterungen zu implementieren und sogar völlig neue Anwendungsbereiche zu erschließen.

In meinen Anfängen als Software-Entwickler hatte ich schon einige Windows-Anwendungen in Pascal und C++ implementiert, ein Nachteil war damals jedoch, dass Bibliotheken mit fertigen Komponenten rar waren und vieles selbst entwickelt werden musste. Eine Anwendung zu schreiben, die beispielsweise ein Diagramm aus den Daten erzeugte, erforderte somit schon einen recht großen Aufwand.

Als ich dann vor über 15 Jahren erstmalig mit VBA in Kontakt kam und meinen ersten Code im Rahmen eines betrieblichen Projekts geschrieben hatte, wusste ich, dass ich das richtige Werkzeug gefunden hatte.

Excel bot quasi schon fast alles frei Haus: eine leistungsfähige Engine für die Tabellenkalkulation und Visualisierung von Daten und später sogar Schnittstellen zu weiteren Anwendungen wie Word, Outlook oder PowerPoint. VBA ist im Verhältnis zu anderen Programmiersprachen relativ einfach zu erlernen und ermöglicht es, schnell sichtbare Ergebnisse zu produzieren.

Die Leidenschaft für Excel hat bis heute angehalten. 2006 habe ich den Schritt in die Selbstständigkeit gewagt und entwickle seitdem vornehmlich Excel-VBA-Anwendungen für kleine und große Unternehmen. Die stets neue Herausforderung bei jeder dieser Anwendungen ist, unterschiedliche Aufgabenstellungen zu lösen. Neben der Anwendungsentwicklung führe ich auch Schulungen in Excel, sowohl für Einsteiger als auch für Fortgeschrittene, durch.

In meiner Freizeit engagiere ich mich in deutsch- und englischsprachigen Foren, blogge auf meinem Blog www.excel-ticker.de und betreibe zudem mehrere weitere Community-Projekte rund um Excel, wie das Excel-Wiki unter www.excel-wiki.de oder den Fußball-WM-Planer unter www.excel-soccer.de.

Für mein Community-Engagement wurde ich von Microsoft 2011 erstmalig zum MVP – Most Valuable Professional – ausgezeichnet, eine Auszeichnung, die mich sehr gefreut hat und mit Stolz erfüllt.

Zielgruppe für dieses Buch

Dieses praxisnahe Buch richtet sich an Anwender und Anwenderinnen, die bereits über gute Excel-Kenntnisse (wenn auch noch keine VBA-Kenntnisse) verfügen. Sie sollten mit der Verwendung von Excel-Funktionen vertraut sein und diese auch als Formeln kombinieren können.

Dieses Buch gliedert sich in 7 Teile, die unterschiedliche Themenbereiche ansprechen. Hierbei richten sich die ersten beiden Teile an Anfänger, die ganz neu in das Thema VBA einsteigen. Aber auch der eine oder andere, der bereits Erfahrungen in VBA gesammelt hat, dürfte Neues entdecken, wie beispielweise die Grundlagen der objektorientierten Programmierung. Der dritte und umfangreichste Teil dieses Buchs verbindet Wissen und Praxis und erweitert Ihre VBA-Kenntnisse in vielfältigen Bereichen. Die weiteren Teile behandeln Themen wie die Erstellung von Formularen, die Klassenprogrammierung oder das Verwenden der Windows-API in VBA.

Dieses Buch hat nicht den Anspruch, jedes Thema der VBA-Programmierung in Excel abzudecken. Dafür sind die Möglichkeiten einfach zu vielfältig. Ziel ist es jedoch, Schritt für Schritt Ihre Kenntnisse in der VBA-Programmierung zu erweitern und Ihnen hierbei Werkzeuge an die Hand zu geben, um Ihre eigenen Anwendungen implementieren zu können. VBA lernen heißt aber auch Learning by doing: experimentieren Sie mit dem Erlernten, beispielsweise indem Sie die Beispieldateien verändern und die Effekte beobachten.

Danksagung

Dieses Buch ist auf der Grundlage des Handbuchs zur Excel 2010-Programmierung von Monika Weber entstanden. Das Buch folgt einer neuen Struktur, wobei übernommenes Material aktualisiert und die Kapitel über weite Strecken auch neu geschrieben wurden. Manche Themen sind ganz neu hinzugekommen, anderes ist aus heutiger Sicht nicht mehr so relevant wie beim Schreiben des Vorgängers.

Das Buch ist in der knappen Freizeit neben meiner Selbstständigkeit entstanden. Vieles, was ich mir gewünscht hätte zu schreiben, musste ich mir versagen, das Buch wäre schlicht zu umfangreich geworden. Meine Familie hat in dankenswerter Weise diese Zeit mit viel Geduld hingenommen. Ein besonderer Dank geht an meine Frau, die trotz eines gepflegten Desinteresses für Excel immer wieder mit mir diskutiert, mich ermutigt und gelegentlich auf den Boden der Realität zurückgeholt hat.

Sehr dankbar bin ich meinem Lektor René Majer bei Microsoft Press, der mich souverän und vorausschauend um die Klippen meines ersten Buches gelotst hat. Meinem Fachlektor Uwe Thiemann möchte ich für das konstruktive Lektorat danken. Mein Dank gilt zudem allen Beteiligten von Microsoft Press, die dieses Buch erst möglich gemacht haben.

Und ein besonderer Dank gilt natürlich Ihnen, liebe Leserinnen und Leser, für Ihre Entscheidung, dieses Buch zu erwerben. Ich wünsche Ihnen viel Erfolg bei der Umsetzung Ihrer Projekte in VBA.

Mourad Louha

Einleitung

In diesem Kapitel:

„Die Hervorhebungen“

„Die Schreibweisen“

„Die Beispieldateien“

„Die Themen“

Bevor Sie in die VBA-Welt eintauchen, empfehlen wir Ihnen, sich mit der Organisation und dem Aufbau des Buchs vertraut zu machen.

Diese Einleitung stellt Ihnen die in diesem Buch verwendeten Hervorhebungen und Schreibweisen vor, erläutert Ihnen den Aufbau der Beispieldateien und fasst die einzelnen Themen in einer Übersicht zusammen.

Die Hervorhebungen

Wenn in diesem Buch einem Abschnitt besondere Beachtung geschenkt werden soll, wird dieser je nach Wichtigkeit und Art der Information wie folgt hervorgehoben:

Achtung

Es wird auf besondere Umstände hingewiesen, die unerwünschte Auswirkungen haben können und die einer besonderen Beachtung bedürfen.

Wichtig

Es wird auf wichtige Zusatzinformationen aufmerksam gemacht, die Sie berücksichtigen sollten.

Hinweis

Es werden Informationen vermittelt, deren Kenntnis zum besseren Verständnis des Themas sinnvoll ist. Hinweise werden zudem benutzt, um auf weiterführende oder ergänzende Themen in anderen Kapiteln hinzuweisen.

Tipp

Es werden alternative Vorgehensweisen oder zusätzliche Tipps und Tricks im Umgang mit der beschriebenen Software vermittelt.

Online

Es wird beschrieben, in welchen Beispieldateien die abgedruckten Codebeispiele zu finden sind.

Die Schreibweisen

In diesem Buch werden einheitliche Formatierungen für verschiedene Elemente verwendet, die in der nachfolgenden Übersicht aufgeführt sind.

Beschreibung

Beispiel

Formatierung

Menübefehle

Datei/Öffnen

Kursiv

Grafische Elemente

Die Abbrechen-Schaltfläche

Kursiv

Pfad- und Dateiangaben

C:|MeinPfad|Mappe1.xlsm

Kursiv

Dateierweiterungen

*.xlsm

Kursiv

Abkürzungen mit Erläuterungen

VBA (Visual Basic for Applications)

Kursiv und Normal

VBA-Code

Sub Makro1()
...
End Sub

Listingschrift

Tastenkombinationen

Tastenkappen

Die Beispieldateien

Die meisten im Buch abgedruckten Codebeispiele sind auch in den Beispieldateien zu finden. Ausnahmen bilden kleinere Codeabschnitte im Buch, die entweder schnell von Hand abgetippt werden können oder nur zur Veranschaulichung dienen.

Die Beispieldateien finden Sie unter:

www.microsoft-press.de/support/9783866454705 und unter msp.oreilly.de/support/2362/866.

Häufig wurden aus Platzgründen in den abgedruckten Codebeispielen einzelne Leerzeilen aus dem Originalcode entfernt. In den Beispieldateien sind diese Leerzeilen noch enthalten, lockern damit den Code etwas auf und erhöhen die Übersichtlichkeit.

Jede Beispieldatei zu diesem Buch beinhaltet ein Inhaltsverzeichnis, welches kurz die Inhalte, die Lernziele, die enthaltenen Tabellen und Code-Module beschreibt. Bis auf einige Ausnahmen, wie die Beispieldateien zur Anpassung des Menübands, finden Sie in den Beispieldateien eine oder mehrere Übungstabellen. Komplexere Beispieldateien beinhalten zudem ein Backup der Übungstabellen, sodass Sie den Originalzustand mit dem nach Ausführung des Codes vergleichen können.

Sind bestimmte Voraussetzungen – wie beispielsweise die Aktivierung der Übungstabelle – an das Verwenden der Beispieldatei geknüpft, finden Sie einen entsprechenden Hinweis im Inhaltsverzeichnis.

Die Beispieldateien sind entsprechend der Kapitel in einzelne Verzeichnisse unterteilt. Wenn in einem Beispiel Zusatzdateien, wie beispielsweise Bilder, verwendet werden, sind diese ebenfalls im entsprechenden Ordner oder in einem Unterordner zu finden.

Die Themen

Beginnend mit den Grundkenntnissen in VBA über das Verbinden von Wissen und Praxis bis hin zu Themen für Fortgeschrittene – dieses Buch deckt vielfältige Themenbereiche ab, die sich in den einzelnen Teilen des Buchs widerspiegeln. Die einzelnen Teile sind wiederum in Kapitel aufgeteilt, die je einen Schwerpunkt innerhalb eines Themenbereichs bilden. Nachfolgend finden Sie eine Beschreibung der Inhalte zu den einzelnen Teilen.

Teil I

Der erste Teil dieses Buchs umfasst einen generellen Einstieg in die VBA-Programmierung. Sie erstellen Ihr erstes Makro mithilfe des Makro-Rekorders und lernen den aufgezeichneten Code zu verstehen und in der Entwicklungsumgebung zu modifizieren. Sie lernen Variablen in VBA kennen und erfahren, wie Funktionen und Prozeduren in VBA funktionieren und was diese unterscheidet. Sie erfahren zudem, welche Grundkonzepte der Programmierung in VBA Verwendung finden und warum es wichtig ist, diese zu kennen. Abschließend erforschen Sie das Excel-Objektmodell und lernen unterstützende Hilfsmittel der Entwicklungsumgebung kennen.

Teil II

Im zweiten Teil des Buchs steigen Sie Schritt für Schritt in die Praxis ein. Sie beginnen mit der Programmierung von Meldungs- und Eingabefeldern, lernen anschließend Kontrollstrukturen – wie Verzweigungen oder Schleifen – in VBA kennen und erfahren, wie Sie Datenfelder in VBA einsetzen können. Schließlich erlernen Sie, wie Sie Fehler in VBA vermeiden und welche Hilfsmittel hierzu zur Verfügung stehen.

Teil III

Der dritte Teil des Buchs ist mit sieben Kapiteln der umfangreichste Teil des Buchs. Hier erfahren Sie, wie Sie in VBA Arbeitsmappen, Arbeitsblätter, Zellen und Bereiche ansprechen und verwalten können, oder wie Sie in VBA Formatierungen durchführen, mit Datum und Zeit umgehen, Datenüberprüfungen einsetzen, grafische Objekte verwenden, Excel-Formeln nutzen, eigene Funktionen programmieren und vieles mehr. Praxisbeispiele und unzählige Codebeispiele dienen zur Veranschaulichung und zur Verwendung im Alltag. Einen wichtigen Aspekt in VBA bildet die ereignisorientierte Programmierung, weshalb Sie ein Kapitel umfassend in das Thema einführt.

Teil IV

Der vierte Teil widmet sich der Auswertung von Daten anhand von VBA. Sie erlernen, wie Sie in VBA Daten filtern, sortieren, vergleichen und anhand von Diagrammen visualisieren können.

Teil V

Der fünfte Teil dieses Buchs befasst sich mit der Entwicklung von Formularen in VBA. Sie erfahren, welche Steuerelemente in Excel zur Verfügung stehen, welchen Zweck diese erfüllen und wie diese in VBA verwendet werden können. Beginnend mit der Verwendung von Steuerelementen in Arbeitsblättern erweitern Sie Ihr Wissen Schritt für Schritt um die Programmierung von benutzerdefinierten Formularen in VBA – UserForms. Den Abschluss dieses Teils bildet ein Kapitel zur Anpassung des Menübands.

Teil VI

Der sechste Teil beginnt mit einer Zusammenstellung verschiedener Tipps & Tricks, wie beispielsweise das Löschen von Verknüpfungen, das Verwenden der Zwischenablage oder der Zugriff auf VBA-fremde Objekte. Ein Kapitel in Teil VI befasst sich mit der Verwendung der Windows-API in VBA und demonstriert dies anhand von drei Beispielen. Abschließend behandelt ein Kapitel die Programmierung von Klassen in VBA und führt Sie Schritt für Schritt an die Entwicklung eigener Klassen heran.

Teil VII

Der siebte Teil zeigt Ihnen auf, wie Sie über Excel-VBA mit weiteren Office-Produkten wie Word, PowerPoint, Outlook oder mit Datenbanken kommunizieren können und beispielsweise Daten austauschen können. Sie erfahren in diesem Teil zudem, wie Sie Mails per VBA versenden können, Webabfragen durchführen oder auf das Dateisystem zugreifen können.

Anhang A

Im Anhang A finden Sie eine Zusammenfassung der wichtigsten Tabellen zu diesem Buch. Zudem ergänzt der Anhang die Inhalte des Buchs um weitere Tabellen, wie z.B. zu den Tastenkombinationen in Excel oder RibbonX.

Teil I. Grundkenntnisse in Excel-VBA aufbauen

Kapitel 1

Kapitel 2

Kapitel 3

Kapitel 1. Makrorekorder und Arbeitsumgebung kennenlernen

In diesem Kapitel:

„Einführung in VBA“

„Die Sicherheitseinstellungen für Makros“

„Der Umgang mit dem Makrorekorder“

„Die Entwicklungsumgebung“

„Mit Modulen arbeiten“

In diesem Kapitel lernen Sie einige Grundbegriffe der Programmierung kennen und erfahren, wie Sie Makros aufzeichnen, ausführen und speichern. Zudem zeigen wir Ihnen, welche Effekte die Sicherheitseinstellungen auf Ihre eigenen oder andere Makros haben und wie Sie diese beeinflussen.

Danach erforschen Sie das aufgezeichnete Makro im Code und lernen grundlegende Funktionen der Entwicklungsumgebung kennen.

Einführung in VBA

Seit Mitte der 1990er-Jahre bieten die verschiedenen Microsoft Office-Anwendungen die Möglichkeit, Prozesse und Abläufe via einheitliche Programmiersprache zu automatisieren. Diese Programmiersprache – Visual Basic für Applikationen bzw. in Englisch Visual Basic for Applications oder kurz VBA – lehnt sich an Visual Basic 6 an, eine von Microsoft entwickelte Programmiersprache zur Entwicklung von Windows-Anwendungen.

So entspricht die Syntax von VBA weitestgehend der von Visual Basic 6, und auch dessen Zugriffsmodell auf Komponenten und Schnittstellen des Betriebssystems sowie auf Anwendungen wurde in VBA übernommen. Dies ermöglicht beispielsweise aus Microsoft Excel heraus, über VBA weitere Microsoft Office-Anwendungen anzusprechen und fernzusteuern.

Hinweis

Unter Syntax ist ein Regelsatz zu verstehen, der es einem Interpreter erlaubt, die im Quellcode enthaltenen Anweisungen zu verstehen und auszuführen. Die Einhaltung dieser Regeln ist verpflichtend. Anweisungen, die nicht der Syntax folgen, führen zu Fehlern.

Andere Fähigkeiten von Visual Basic 6, wie das Erstellen eigenständig ausführbarer Anwendungen, sind in VBA jedoch nicht verfügbar. Zur Ausführung von VBA-Makros benötigen Sie das Dokument, das den Code enthält, und die entsprechende Anwendung zum Öffnen der Dokumente muss installiert sein. VBA-Code wird zudem zur Laufzeit interpretiert, was VBA-Anwendungen generell langsamer als native Anwendungen macht.

Anweisungen

Eine Anweisung stellt in der Programmierung eine einzelne Aktion bzw. einen Verarbeitungsschritt dar, den das Programm durchführen soll. Es gibt verschiedene Formen von Anweisungen, die in den Folgekapiteln detaillierter besprochen werden. Gemeinsam ist aber allen Anweisungen, dass diese der Syntax der Programmiersprache folgen müssen.

Eine Anweisung kann quasi als kleinste Einheit angesehen werden, um einen Befehl oder eine Aktion auszuführen. Sie sehen im Folgenden, vereinfacht dargestellt, ein paar Beispiele:

Setze die Hintergrundfarbe der Zelle A1 auf Rot

Addiere 10 Einheiten auf einen bestimmten Wert

Merke einen Wert für eine spätere Verwendung

Erstelle eine neue Tabelle in der Arbeitsmappe

Wiederhole 10 mal eine weitere Anweisung

Rufe ein weiteres Makro auf

Makros

Einzelne Anweisungen können nicht direkt ausgelöst bzw. ausgeführt werden, sondern benötigen einen Container, innerhalb dessen sie agieren können. Dieser Container, den wir Makro nennen, muss dem Interpreter vorgegeben werden. Ein Interpreter prüft die Syntax und führt die Befehle beim Aufruf des Makros aus. Die Anweisungen werden der Reihe nach ausgeführt, und zwar genau so, wie diese im Makro abgelegt wurden.

Abbildung 1.1 Makros als Container für Anweisungen

Abbildung 1.1 veranschaulicht drei Makros mit jeweils unterschiedlichen Aufgaben. Jedes Makro ist in sich geschlossen und beinhaltet eine oder mehrere Anweisungen, die in den Beispielen jeweils als Satz zusammengefasst sind.

Tipp

Analysieren Sie die Sätze und stellen Sie sich die Schritte in Microsoft Excel vor, die Sie durchführen würden, um die Aufgaben der Makros zu erfüllen. Spielen Sie die Aufgaben der Makros in einer Tabelle nach.

Beachten Sie auch die Namensgebung der einzelnen Makros, die sich aus einem Verb sowie Substantiven und Bindewörtern zusammensetzen, und wo die jeweiligen Wörter mit einem Großbuchstaben beginnen. Diese Systematik der Benennung wird Ihnen an vielen weiteren Stellen des Buchs begegnen und dient im Wesentlichen dazu, die Lesbarkeit der Makros und das Verständnis der mit den Makros assoziierten Aktion zu erhöhen.

Die Makros selbst werden übrigens auch in Containern abgelegt und organisiert, den sogenannten Modulen. Hierbei ist es auch möglich, mehrere Module zu verwenden. So könnten Sie beispielsweise alle Makros zur Formatierung von Zellen in einem Modul Zellformatierungen ablegen. Weitere Makros, die zum Kopieren und Einfügen von Daten dienen, könnten in einem Modul Kopierer liegen.

Die Sicherheitseinstellungen für Makros

Durch die Verwandtschaft von VBA zu Visual Basic 6 bestehen in VBA auch Möglichkeiten des Zugriffs auf Betriebssystemfunktionen, das Dateisystem oder auf die Windows-Registrierung im Kontext des angemeldeten Benutzers.

Leider haben einige Zeitgenossen die Funktionalität zu unschönen Zwecken verwendet und beispielsweise Makroviren statt sinnvoller Anwendungen in Umlauf gebracht. Microsoft reagierte daraufhin mit der Integration von Sicherheitsmechanismen in den einzelnen Office-Anwendungen. Diese unterbinden die Ausführung von Makros oder lassen diese nur unter bestimmten Rahmenbedingungen zu.

Bei einer Erstinstallation von Microsoft Office ist in der Regel die Ausführung von Makros deaktiviert, weshalb Sie diese erst aktivieren müssen, um Ihre Makros verwenden zu können.

Hinweis

Nachfolgend ist die Makroaktivierung für Excel 2013 beschrieben. Während die Unterschiede zwischen Excel 2013 und Excel 2010 recht minimal sind, ist die Registerkarte DATEI in Excel 2007 nicht vorhanden, sondern wird durch die runde Startschaltfläche in der linken oberen Ecke des Anwendungsfensters repräsentiert.

In Excel 2013 aktivieren Sie Makros wie folgt:

Starten Sie Microsoft Excel.

Falls Sie Excel 2013 verwenden, wählen Sie als Vorlage die leere Arbeitsmappe aus. In früheren Excel-Versionen wird in der Regel automatisch eine leere Arbeitsmappe angelegt.

Klicken Sie anschließend auf die Registerkarte DATEI. Sie gelangen zum sogenannten Backstage-Bereich von Excel.

Abbildung 1.2 Der Backstage-Bereich in Excel 2013 mit Zugang zu den Optionen

Klicken Sie im linken Bereich des Fensters auf den Eintrag Optionen, um zu den Excel-Optionen zu gelangen. Wählen Sie im folgenden Dialogfeld die Kategorie .

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!