Richtig einsteigen: Excel VBA-Programmierung (eBook)

Für Microsoft Excel 2007 bis 2016

Bernd Held (Autor)

eBook Download: PDF

2016 | 2. Auflage
294 Seiten
O'Reilly Verlag
978-3-96010-027-0 (ISBN)

Infos

Dieses Buch hat sich als verständlicher Einstieg in Excel VBA außerordentlich bewährt. Auf Grundlage seiner Kurse und langjährigen Praxiserfahrung vermittelt Bernd Held das Wichtigste über Schleifen, Verzweigungen und die relevanten Objekte von Excel. In kurzer Zeit sind Sie in der Lage, Alltagsaufgaben erfolgreich zu lösen und sich das Leben erheblich zu erleichtern. Neben schnellen Erfolgserlebnissen bietet diese Einführung genau die richtige Dosis Know-why.

Bernd Held gehört zu den deutschlandweit erfolgreichsten Autoren und Trainern zum Thema Excel VBA und kann auf mehr als 15 Jahre Schulungserfahrung zurückgreifen. Mehrfach wurde er von Microsoft als MVP (Most Valuable Professional) im Bereich Excel ausgezeichnet. Mit seinem Team realisiert er seit 2008 Programmier-Aufträge, berät Unternehmen und veröffentlicht Fachbücher und Fachartikel.

Inhalt

Richtig einsteigen: Excel VBA-Programmierung 1
Impressum 4
Inhalt 5
Einleitung 9
Wie dieses Buch aufgebaut ist 9
Download der Beispieldateien 10
Die Icons 10
Unterstützung für dieses Buch 10
Über den Autor 11
Kapitel 1 - Die Entwicklungsumgebung von Excel 13
Excel für die Programmierung vorbereiten 13
Die Sicherheitsstufe heruntersetzen 13
Das Werkzeug Entwicklertools einblenden 14
Die Entwicklungsumgebung kennenlernen 16
Die Entwicklungsumgebung aufrufen 16
Der Projekt-Explorer 16
Das Eigenschaftenfenster 17
Tabellen ein- und ausblenden 18
Standardspaltenbreite festlegen 19
Die ScrollArea setzen 19
Den Namen einer Tabelle festlegen 20
Das Codefenster 22
Das Direktfenster – die Testhilfe 24
Angemeldeten Nutzer ermitteln 25
Tabelle ein- und ausblenden 26
Der Objektkatalog – das Nachschlagewerk 28
Der Makrorekorder – zu Beginn eine gute Hilfe 30
Bereich kopieren und eins zu eins einfügen 31
Bereich kopieren und nur Werte einfügen 33
Eine Kopfzeile einrichten 36
Einen Bereich einrahmen 38
Die ersten Makros und deren Handhabung 40
Wert in eine Zelle einer Tabelle schreiben 40
Variante 1: Das Ansprechen einer Tabelle über den Codenamen 40
Variante 2: Das Ansprechen einer Tabelle über den Registernamen 41
Variante 3: Das Ansprechen einer Tabelle über einen Index 42
Eine Meldung am Bildschirm ausgeben 44
Mehrzeilige Meldung am Bildschirm ausgeben 47
Eine Eingabe vom Anwender verlangen 47
Einen individuellen Tabellenkopf erstellen 48
Ein Makro im Einzelschrittmodus abarbeiten 51
Mit einem Haltepunkt arbeiten 53
Die wichtigsten Tastenkombinationen 54
Variablen und Konstanten 55
Variablen deklarieren 56
Regeln für Variablen 56
Die verfügbaren Datentypen 56
Eine Systemeinstellung temporär ändern und wieder zurücksetzen 57
Konstanten einsetzen 59
Einen alten DM-Wert in Euro umrechnen 59
Ein Verzeichnis prüfen und ein Unterverzeichnis anlegen 60
Zusammenfassung 61
Die Lernkontrolle 61
Kapitel 2 - Die wichtigsten Sprachelemente von Excel-VBA 63
Bedingungen erstellen und üben 63
Die Anweisungen If/Then/Else einsetzen 64
Eine Löschrückfrage erstellen 64
Eine Eingabeprüfung vornehmen 66
Vor dem Rechnen den Datentyp prüfen 67
Wert in eine Wertgruppe einordnen 68
Ein K.-o.-Kriterium formulieren 69
Prüfen, ob eine Datei existiert 70
Die Anweisung Select Case einsetzen 71
Excel-Version ermitteln 71
Zahlenwerte prüfen 72
Den Wochentag eines bestimmten Datums auslesen 73
Schleifen erstellen und verstehen 74
Die For...Next-Schleifen 75
Die Farbpalette von Excel auslesen 75
Der Rahmen für eine Verarbeitung von Zeilen von oben nach unten 77
Topwerte kennzeichnen 78
Wochenenden in einer Datumsreihe hervorheben 79
Duplikate ermitteln und hervorheben 81
Der Rahmen für eine Verarbeitung von Zeilen von unten nach oben 83
Bestimmte Zeilen aus einer Tabelle entfernen 85
Null-Artikel aus einer Tabelle entfernen 86
Der Rahmen für eine Verarbeitung von Spalten von links nach rechts 88
Wochenenden und Werktage kennzeichnen 89
Bestimmte Spalten in einer Tabelle ausblenden 90
Der Rahmen für eine Verarbeitung von Spalten von rechts nach links 91
Bestimmte Spalten einer Tabelle entfernen 91
Die For Each...Next-Schleifen 93
Alle Zellen in der Markierung nacheinander verarbeiten 94
Eine Preiserhöhung durchführen 94
Die Schleife Do Until...Loop 96
Textdatei Zeile für Zeile verarbeiten 96
Die Schleife Do While...Loop 98
Anzahl von Dateien eines Verzeichnisses ermitteln 98
Sonstige Sprachelemente 99
Die Struktur With 99
Eine Zelle formatieren 99
Eine Kopfzeile befüllen 100
Zusammenfassung 101
Lernkontrolle 101
Kapitel 3 - Das Objekt Range (Zellen und Bereiche programmieren) 103
Zellen und Bereiche formatieren 104
Zahlenformat und Schriftschnitt festlegen 105
Zellenfarbe und Schriftfarbe festlegen 106
Das Gitternetz und den Gesamtrahmen formatieren 108
Daten in Zellen konvertieren 110
Korrektur nach fehlerhaftem Datenimport 110
Unerwünschte führende und nachgestellte Leerzeichen entfernen 112
Bestimmte Zeichen in Zellen ersetzen/entfernen 114
Die Position des Minuszeichens umstellen 117
Verwendete Datumsformate vereinheitlichen 119
Daten in Zellen und Bereichen suchen 121
Suche nach exakter Übereinstimmung 121
Suche nach exakter Übereinstimmung (Schreibweise spielt keine Rolle) 123
Suche auch in Teilen der Zelle (Schreibweise spielt keine Rolle) 124
Daten anhand eines eindeutigen Schlüssels suchen 125
Bereiche Zelle für Zelle verarbeiten 128
Daten aus einem Bereich löschen 128
Extremwerte in einem Bereich ermitteln und kennzeichnen 129
Mehrere nicht zusammenhängende Bereiche verarbeiten 132
Zusammenfassung 133
Die Lernkontrolle 134
Kapitel 4 - Das Objekt Worksheet (Tabellen programmieren) 135
Tabellen dokumentieren, filtern und durchsuchen 136
Tabelleninhaltsverzeichnis erstellen und verlinken 137
Tabellen durchsuchen und dokumentieren 138
Tabellen filtern mit einem Kriterium 139
Tabellen filtern mit mehreren Kriterien 141
Tabellen einrichten und schützen 142
Bildlaufbereiche für Tabellen festlegen 142
Bereiche in Tabellen sperren 143
Tabellenschutz für eine Tabelle einstellen und zurücksetzen 144
Alle Tabellen einer Mappe schützen 145
Tabellenblätter anlegen, drucken und exportieren 147
Tabellen anlegen und benennen 147
Eine Tabelle drucken 149
Alle sichtbaren Tabellen einer Mappe ausdrucken 149
Tabelle als PDF ausgeben 150
Eine Tabelle exportieren 150
Individuelle Kopf- und Fußzeilen erstellen 151
Tabellen verstecken oder löschen 153
Tabellen ein- und ausblenden 153
Alle Tabellen bis auf eine ausblenden 153
Tabellen löschen 155
Zusammenfassung 156
Die Lernkontrolle 156
Kapitel 5 - Das Objekt Workbook (Arbeitsmappen programmieren) 157
Arbeitsmappen abarbeiten und schließen 158
Arbeitsmappe anlegen, verarbeiten, speichern und schließen 159
Dokumenteigenschaften abfragen und auswerten 161
Externe Verknüpfungen verarbeiten 163
Externe Verknüpfungen ermitteln 163
Verknüpfte Arbeitsmappen automatisch öffnen 165
Externe Verknüpfungen entfernen 166
Arbeitsmappe löschen 167
Sicherheitskopie einer Arbeitsmappe erstellen 167
Daten aus einer anderen Mappe synchronisieren 168
Zusammenfassung 172
Die Lernkontrolle 172
Kapitel 6 - Standardfunktionen nutzen, eigene Funktionen schreiben 173
Die integrierten Tabellenfunktionen von Excel anzapfen 174
Einen Bereich summieren 175
Eine bedingte Summierung durchführen 176
Extremwerte ermitteln 177
Leere Tabellen aus einer Arbeitsmappe entfernen 178
Min- und Max-Wert in einem Bereich finden und einfärben 179
Leere Zeilen aus einer Tabelle entfernen 181
Eigene Funktionen schreiben 182
Der Aufbau einer Funktion 182
Aktuelle Arbeitsmappe ermitteln 183
Funktionen testen 184
Bestimmte Zeichen aus einer Zelle entfernen 185
Kalenderwoche nach DIN ermitteln 188
Die Existenz einer Tabelle prüfen 188
Die Existenz einer Datei prüfen 189
Die Existenz eines Verzeichnisses prüfen 190
Funktionen im Funktionsassistenten einsehen 190
Funktionen in eine andere Funktionskategorie hängen 191
Zusammenfassung 193
Die Lernkontrolle 193
Kapitel 7 - Die Ereignisprogrammierung in Excel 195
Die Arbeitsmappenereignisse 196
Das Ereignis Workbook_Open 196
Das Ereignis Workbook_BeforeClose 197
Das Ereignis Workbook_BeforeSave 198
Das Ereignis Workbook_NewSheet 199
Das Ereignis WorkBook_BeforePrint 200
Die wichtigsten Ereignisse auf Arbeitsmappenebene 201
Die Tabellenereignisse 201
Das Ereignis Worksheet_Change 202
Alle Zellen einer Tabelle überwachen 202
Einige Zellen in einer Tabelle überwachen 203
Einen Bereich in einer Tabelle überwachen 204
Doppelte Nummern bereits bei der Eingabe erkennen können 205
Automatisch die Mehrwertsteuer berechnen – ganz ohne Formeln 206
Änderungen in einem bestimmten Bereich in einer Textdatei festhalten 207
Das Ereignis Worksheet_SelectionChange 208
Zugang zu einem bestimmten Bereich gewähren 208
Eine Mehrfachauswahl von Zellen verhindern 209
Das Ereignis Worksheet_BeforeDoubleClick 210
Eine Zelle per Doppelklick automatisch hochzählen 210
Automatische Sortierung bei Doppelklick ausführen 211
Per Doppelklick eine Zelle mit einem Kreuz versehen 213
Die wichtigsten Tabellenereignisse im Überblick 214
Excel über Tastenkombinationen steuern 214
Formelzellen in Festwertzellen wandeln 214
Makros zeitgesteuert starten 217
Excel nach einer bestimmten Zeit beenden 217
Makro zu einer bestimmten Uhrzeit starten 218
Zusammenfassung 219
Die Lernkontrolle 219
Kapitel 8 - Die Dialogprogrammierung mit Excel 221
UserForms einfügen, beschreiben und anzeigen 222
Die Eigenschaften einer UserForm festlegen 222
Einen variablen Titel als Überschrift für die UserForm festlegen 223
Sonstige Einstellungen für eine UserForm vornehmen 224
Eine UserForm aufrufen 225
Die wichtigsten Steuerelemente 225
Das Steuerelement TextBox 227
Daten über Textfelder erfassen und in eine Tabelle schreiben 227
Eingaben in Textfelder prüfen 231
Ein Dialogfeld für eine Passworteingabe aufrufen und auswerten 234
Das Steuerelement ComboBox 238
Ein Kombinationsfeld mit Werten füllen 238
Zwei voneinander abhängige Kombinationsfelder programmieren 242
Das Steuerelement ListBox 245
Ein Listenfeld mit Werten füllen 245
Eine mehrspaltige ListBox programmieren 248
Das Steuerelement CheckBox 251
Kontrollkästchen schnell aktivieren bzw. deaktivieren 251
Das Steuerelement OptionButton 255
Einen Multiple-Choice-Test mit Optionsschaltflächen durchführen 255
Das Steuerelement Image 259
Ein Bild per Klick in eine UserForm laden 259
Ein Bild aus einer UserForm in eine Tabelle exportieren 262
Ein Kombinationsfeld mit einem Bildsteuerelement kombinieren 263
Zusammenfassung 267
Die Lernkontrolle 267
Kapitel 9 - Das Fehler-Handling 269
Die Laufzeitfehler von Excel 270
Typische Stolperfallen bei der Programmierung 270
Einen Laufzeitfehler abfangen 271
Laufzeitfehler bereits im Voraus verhindern 273
Tabelle einfügen und benennen 273
Alle Zellen mit Formeln markieren 274
Bestimmte Zellen automatisch einfärben 276
Laufzeitfehler in UserForms abfangen 278
Zusammenfassung 280
Die Lernkontrolle 280
Kapitel 10 - Fragen & Antworten
Kapitel 1 281
Kapitel 2 283
Kapitel 3 283
Kapitel 4 284
Kapitel 5 285
Kapitel 6 285
Kapitel 7 286
Kapitel 8 286
Kapitel 9 287
Index 289
A 289
B 289
C 289
D 289
E 290
F 290
G 291
H 291
I 291
K 291
L 291
M 292
N 292
O 292
P 292
Q 292
R 292
S 292
T 293
U 293
V 293
W 294
Z 294
www.oreilly.de 0

Textauszug

Kapitel 2
Die wichtigsten Sprachelemente von Excel-VBA

In diesem Kapitel:

Bedingungen erstellen und üben

Schleifen erstellen und verstehen

Sonstige Sprachelemente

Zusammenfassung

Lernkontrolle

Das Wesentliche einer Programmiersprache sind ihre Sprachelemente. In diesem Kapitel erfahren Sie, wie mithilfe von Verzweigungen, Schleifen und anderen Anweisungen Programme flexibel gestaltet werden können. Solche Sprachelemente lassen sich leider nicht mit dem Makrorekorder aufzeichnen und müssen selbst erstellt werden. Der richtige Einsatz der Sprachelemente in Verbindung mit dem entsprechenden Vokabular (der zur Verfügung stehenden Befehle) macht letztendlich die Kunst der Programmierung aus.

Bedingungen erstellen und üben

Bedingungen werden in Excel standardmäßig über die Anweisung If umgesetzt. Diese Anweisung kann bei Bedarf auch mehrfach geschachtelt werden. Des Weiteren haben wir eine zweite Möglichkeit, in Excel-VBA eine Abfrage zu erstellen. Diese Möglichkeit ist durch die Anweisung Select Case gegeben, über die Sie auf sehr übersichtliche Weise auch komplexere Bedingungen umsetzen können. Lernen Sie auf den folgenden Seiten Schritt für Schritt, wie Sie Bedingungen anwenden und erweitern können

Die Anweisungen If/Then/Else einsetzen

Sicher kennen Sie alle die Standardtabellenfunktion WENN. Genau diese Funktion gibt es auch als Sprachelement in VBA. Die Syntax entspricht dabei genau der Syntax der Tabellenfunktion. Die VBA-Funktion mit dem Namen If ist aber nicht an Limitationen gebunden und kann nahezu unendlich eingesetzt werden.

Die If-Anweisung ist in den meisten Fällen eine Prüffunktion, mit der Sie sicherstellen, ob beispielsweise eine Zelle einen gewünschten Wert enthält, ob ein Datentyp passt, ob eine Tabelle oder Datei existiert, ob eine Formatierung so weit korrekt ist oder vieles mehr.

Die Syntax der Funktion If lautet:

If Bedingung Then [Anweisung] [Else elseAnweisung]

Alternativ können Sie die Blocksyntax verwenden:

If Bedingung Then
  Anweisung


  Else


  elseAnweisung
End If

Für das Argument Bedingung geben Sie die Bedingung an, die erfüllt sein muss. Anweisung repräsentiert die Anweisungen direkt im Anschluss an Then, die ausgeführt werden, wenn die Bedingung zutrifft. elseAnweisung repräsentiert die Anweisungen, die ausgeführt werden, wenn die Bedingung nicht erfüllt ist.

Es ist nicht immer notwendig, für eine If-Anweisung einen Else-Zweig zu erfassen, wenn dieser nicht benötigt wird. Daher kann eine If-Anweisung auch nur aus einem Then-Zweig bestehen und mit End If abgeschlossen werden.

Bei der ersten Syntaxform brauchen Sie übrigens keinen Abschluss mit End If anzugeben, sofern der Befehl in eine Zeile passt. Das bedeutet aber auch, dass, sobald Sie einen Zeilenumbruch ohne Zeilenfortsetzungszeichen haben, das End If dringend benötigt wird, da der VBA-Editor sonst einen Fehler moniert.

Aufgrund der besseren Übersichtlichkeit sowie der später besseren Erweiterbarkeit empfehle ich Ihnen, die Blocksyntax standardmäßig zu verwenden. Dabei muss die If-Anweisung am Ende durch End If abgeschlossen werden.

Eine Löschrückfrage erstellen

Bei der ersten Aufgabe in diesem Kapitel wird eine Rückfrage programmiert, die am Bildschirm angezeigt werden soll, bevor ein bestimmter Bereich in einer Tabelle gelöscht wird. Dazu verwenden wir zum einen die Anweisung If und zum anderen die Funktion MsgBox, die Sie bereits im vorherigen Kapitel kennengelernt haben.

Erfassen Sie jetzt das Makro aus Listing 2.1.

Sub RückfrageVorLöschungEinholen()


  If MsgBox("Wollen Sie den Bereich A1:D10 löschen?", vbYesNo + _
  vbQuestion, "Löschenabfrage") = vbYes Then
     tbl_IF.Range("A1:D10").ClearContents
  End If


End Sub

Listing 2.1: Eine Löschrückfrage einholen.

Achten Sie darauf, dass die Anweisung If und das Schlüsselwort Then immer in einer Zeile erfasst werden. Nachdem Sie mit der ersten Zeile fertig sind, drücken Sie zweimal die Taste und schreiben dann sofort das End If.

Warum?

Weil das immer gern vergessen wird und später riesige Probleme bereitet, wenn Sie mehrere If-Then-Else-Verzweigungen haben und nicht mehr genau erkennen können, wo denn nun das End If hingehört.

Also behalten Sie als Kernregel bitte gleich Folgendes im Kopf: »Jede Struktur in Excel-VBA hat einen Beginn und ein Ende. Wenn Sie den Beginn erfassen, schreiben Sie immer sofort auch die End-Anweisung!«

Wird auf die Schaltfläche Ja geklickt, wird der Löschbefehl ausgeführt. Achten Sie dringend darauf, dass Sie beim Löschen den Tabellennamen des Bereichs mit angeben, den Sie löschen möchten. Die Methode ClearContents löscht alle Werte aus einem Bereich, behält die Formatierung jedoch bei.

Abbildung 2.1: Bei der Rückfrage ist die Schaltfläche Ja standardmäßig vorbelegt.

Um noch einmal auf der Referenzierung der Tabelle herumzureiten ...

tbl_IF.Range("A1:D10").ClearContents

kontra:

Range("A1:D10").ClearContents

Wird der Bezug zur Tabelle beim Löschen weggelassen, nimmt Excel automatisch an, dass die aktive Tabelle gemeint ist – ein gefährliches Missverständnis.

Eine Eingabeprüfung vornehmen

Bei der nächsten Aufgabenstellung soll der Anwender aufgefordert werden, eine Zahl zwischen 1 und 100 über eine InputBox einzugeben. Diese Eingabe schreiben Sie in die Tabelle tbl_IF in Zelle A1. Danach führen Sie eine Prüfung durch, die feststellt, ob die Zahl auch wirklich im vorgegebenen Wertebereich liegt.

InputBox haben Sie bereits im vorherigen Kapitel kennengelernt, nun erfolgt die praktische Anwendung im Makro aus Listing 2.2.

Sub EingabeEinerZahl()


  With tbl_IF
   .Range("A1").Value =
   InputBox("Erfassen Sie einen Wert zwischen 1 und 100!", "Eingabe", 100)


   If .Range("A1").Value >= 1 And .Range("A1").Value <= 100 Then
       MsgBox "Eingabe im geforderten Wertebereich!", vbInformation
     Else
       MsgBox "Eingabe nicht im geforderten Wertebereich!", vbCritical
   End If


  End With


End Sub

Listing 2.2: Bei der Prüfung müssen zwei Bedingungen erfüllt sein.

Gleich zu Beginn des Makros wird über die Anweisung With festgelegt, mit welcher Tabelle gearbeitet wird. Danach müssen gleich zwei Bedingungen erfüllt sein. Diese werden mit dem Operator And miteinander verknüpft. Ist die Bedingung erfüllt, wird der Then-Zweig angesteuert. In diesem Fall wird eine Meldung am Bildschirm ausgegeben, die die eingegebene Zahl bestätigt.

Im anderen Fall wird ebenso eine Meldung am Bildschirm ausgegeben. Beachten Sie bei beiden Meldungen die unterschiedliche Symbolkonstanten vbInformation und vbCritical.

Denken Sie bitte immer an die Einrückungen im Quellcode. Die If-Anweisung und die End If-Anweisung sollten links untereinander ausgerichtet sein. Nur so können Sie später auch größere Makros noch gut lesen und verstehen.

Abbildung 2.2: Beim Aufruf von InputBox wurde als Standardwert vorab der Wert 100 eingetragen.

Vor dem Rechnen den Datentyp prüfen

Im folgenden Beispiel werden Sie sehen, wie wichtig es sein kann, einen Zellenwert zu prüfen, bevor Sie beginnen, mit dem Wert zu rechnen. Stellen Sie sich vor, in einer Zelle steht keine Zahl, sondern ein Text! In dem Fall würde unser Versuch, zu rechnen, mit einem Laufzeitfehler quittiert.

Um diese Aufgabe lösen zu können, lernen Sie eine weitere immens wichtige VBA-Funktion kennen. Diese Funktion heißt IsNumeric. Mit ihrer Hilfe können Sie prüfen, ob es sich bei einem Wert um einen Text oder um eine Zahl handelt. Zu dieser Funktion ist eigentlich nur zu sagen, dass ihr ein Wert, beispielsweise aus einer Zelle, übergeben wird. Sie gibt uns dann entweder True oder False zurück. Nur wenn die Funktion die Rückgabe True liefert, dürfen wir mit dem Wert aus der Zelle rechnen.

Die Aufgabe lautet: »Prüfe, ob in Zelle A1 der Tabelle...

Produkt-Details

• Erscheint lt. Verlag: 31.3.2016
• Verlagsort: Heidelberg
• Sprache: deutsch
• Themenwelt: Informatik ► Office Programme ► Excel
• Schlagworte: Analyse • Arbeitsmappen • Berechnungen • Daten • Diagramme • Einstieg in Excel • Einstieg in Excel 2016 • Excel • Excel 2007 • Excel 2010 • Excel 2013 • Excel 2016 • Excel Anleitungen • Excel-Automatisierung • Excel Funktionen • Excel-Funktionen • Excel ganz einfach • Excel lernen • Excel nutzen leicht gemacht • Excel-Programmierung • Excel Vorlagen • Formeln in Excel • Lösungen • Makroprogrammierung • Makros in Excel • Microsoft • Office • Office 2016 • Organisation • Pivot • Produktivität • schritt für schritt • Software • Tabellen • VBA • VBA-Programmierung • Visualisierung • Zellen
• ISBN-10: 3-96010-027-2 / 3960100272
• ISBN-13: 978-3-96010-027-0 / 9783960100270