Embed Size (px)
Citation preview
Excel-VBAMit über 1000 Makros für Excel
2000 bis 2010
1 *• "" • • ~j " •"• i " •» '- • ' " •" •" ' - ' s ! " '
Markt+Technik
KOMPENDI UM J
Überblick
Vorwort 33
Kapitel 1 Grundsätzliches zur Programmierung 37
Kapitel 2 Die Arbeitsumgebung für die Programmierung 45
Kapitel 3 Datentypen, Variablen und Konstanten 61
Kapitel k Sprachelemente in VBA in der praktischen Anwendung 69
Kapitel 5 Zellen und Bereiche programmieren 113
Kapitel 6 Die Programmierung von Spalten und Zeilen 233
Kapitel 7 Tabellenblätter programmieren 309
Kapitel 8 Die Programmierung von Arbeitsmappen 361
Kapitel 9 Diagramme und Pivot-Tabellenberichte programmieren 413
Kapitel 10 Benutzerdefinierte Funktionen und Funktionsmakros 461
Kapitel 11 Programmierung mit Windows-API-Aufrufen 511
Kapitel 12 Ereignisse in Excel einsetzen 531
Kapitel 13 VBE-Programmierung 597
Kapitel 1^ Dialoge, Meldungen und UserForms programmieren 615
Kapitel 15 Steuerelemente in Tabellen programmieren 687
Kapitel 16 Eigene Menüs und Symbolleisten erstellen 715
Teil 1 - Programmierung von Menüs, Symbolleisten und Kontextmenüs. . . 715
Teil II - Ribbon-Programmierung 762
Kapitel 17 Excel und sein Umfeld 773
Kapitel 18 Fehlerbehandlung, Tuning und Schutz von VBA-Projekten 799
Kapitel 19 FAQ zur Programmierung mit Excel 815
Anhang A Nützliche und interessante. Makros 833
Anhang B Das Wichtigste in einem Add-In 859
Anhang C Listings 875
Anhang D Excel-Dateien sicher schützen und verteilen 897
Anhang E Der VBA-Code-Viewer 911
Das VBA-Repository 912
Index 915
Inhalt
Vorwort 33
Über den Autor 35
Kapitel 1 Grundsätzliches zur Programmierung 37
1.1 Wie gehe ich von Anfang an richtig vor? 38
Den Makrorekorder einsetzen 38
Die Aufzeichnung des Makrorekorders ergänzen 40
Makro ausführen 41
Wie erfahre ich mehr über die einzelnen Befehle? 43
Kapitel 2 Die Arbeitsumgebung für die Programmierung 45
2.1 Die Scroll Area festlegen 46
2.2 Tabellenblätter ein- und ausblenden 46
2.3 Neue Module einfügen 47
2.k Die Symbolleiste Bearbeiten 48
Eigenschaften/Methoden anzeigen 48
Konstanten anzeigen 49
Quickinfo oder Paramterinfo 49
Ganzes Wort 50
Einzüge vergrößern bzw. verkleinern 50
Haltepunkte setzen 50
Kommentare im Code hinterlegen 51
Lesezeichen setzen 51
2.5 Automatische Syntaxprüfung 52
Befehle in der nächsten Zeile fortsetzen 52
Mehrere Befehle in einer Zeile 53
Automatische Anpassung der einzelnen Befehle 53
Inhalt
2.6 Schnelles Arbeiten über Tastenkombinationen 54
2.7 Suchen & Ersetzen 56
2.8 Der Objektkatalog 56
2.9 Die Testumgebung 58
Hilfe im Direktfenster 58
Code Schritt für Schritt durchlaufen lassen 59
Kapitel 3 Datentypen, Variablen und Konstanten 61
3.1 Der Einsatz von Variablen 61
Regeln für die Syntax von Variablen 62
Variablen am Beginn vom Makro deklarieren 62
Variablendeklarationen erzwingen 63
Die wichtigsten Variablentypen 63
Noch kürzere Deklaration von Variablen 64
Die unterschiedlichen Variablentypen 64
Die Objektvariablen 66
3.2 Die Verwendung von Konstanten 68
Kapitel 4 Sprachelemente in VBA in der praktischen Anwendung 69
I1..1 Verzweigungen 69
Normale Verzweigungen 70
Verzweigungen in Blöcke einteilen 73
Die Kurzform einer Verzweigung 74
, k-2 Die Anweisung Select Case für mehr Übersicht 75
Indizes in Farben umsetzen 75
Zahlen in Texte umwandeln 76
Formate prüfen mit VBA 77
Fensterstatus ermitteln 78
Makro bei ungültigem Wert beenden 78
Excel-Version feststellen 79
k3 Schleifen in Excel praxisgerecht einsetzen 80
For...Next-Schleifen 80
Jede zweite Zeile ausblenden 82
For Each...Next-Schleifen 84
8
Inhalt
Die Schleife Do UntiL.Loop 94
Kopien entfernen 98
Listen sortieren und doppelte Sätze löschen 100
Die Schleife Do While...Loop 105
Kapitel 5 Zellen und Bereiche programmieren 113
5.1 Die Navigation 113
Die übersichtlichere Mehrfachauswahl 114
Relative Markierungsformen 115
Mischformen von Markierungen 118
Bestimmte Zellen ansteuern 118
Markierung und Zellen auslesen 121
Verwendete Zellen zählen 122
Zellen im Verbund 122
Alle Zellen markieren 126
Die umliegenden Zellen markieren 126
Auf Zellen zugreifen, ohne darauf zu sitzen 127
Aktive Zelle in definiertem Bereich? 128
5.2 Bereiche und Zellen benennen 129
Bereich benennen 129
Mehrere Zellen benennen 130
Konstante als Namen vergeben 132
Bezüge von benannten Bereichen ermitteln 133
Namenprüfung einer Zelle 134
Benannte Bereiche markieren 134
Namenprüfung eines Bereichs 135
Alle Namen protokollieren 135
Namen ändern 136
Bezugsadressen von Namen ändern 137
Dynamische Datenbezüge herstellen 139
Wo steht der Zellcursor? 141
Namen verbergen 142
Namen löschen 143
Benutzerdefinierte Listen erstellen 143
Inhalt
5.3 Die Formatierung von Zellen und Bereichen 145
Schriftart ermitteln 145
Schriftart ändern 145
Zelleninhalte löschen 146
Schriftfarbe teilweise ändern 147
Grenzwerte sofort sichtbar 148
Sonderzeichen einfügen 149
Chemische Formeln formatieren 151
Rahmenart und -färbe bestimmen 152
Rahmen übertragen 153
Zahlenformate festlegen 154
Datumsformate festlegen 154
Formate übertragen 155
Formate löschen 155
Nur Zahlenformate übertragen 155
Die bedingte Formatierung 157
Formate suchen und ersetzen 161
5./j Formeln einsetzen 162
Werte addieren 162
Werte subtrahieren 162
Werte multiplizieren 163
Werte dividieren 164
Werte potenzieren 164
Komplexere Berechnungen 164
5.5 Funktionen erfassen 165
Bedingtes Zählen von Werten (ZÄHLENWENN) 165
Bedingte Summierung (SUMMEWENN) 166
Auskunftssystem erstellen (SVERWEIS) 167
Offene Posten-Verwaltung (WENN) 168
Zahlen summieren (SUMME) 169
Rechnungsfälligkeit ermitteln (EDATUM) 170
Minimalwert ermitteln 170
Auswertungen durchführen 171
Bereiche summieren 175
10
Inhalt
Mittelwert über Input-Box ermitteln 176
Eurokonvertierung leicht gemacht 177
Obergrenzen festlegen bzw. runden 179
Formeln und Verknüpfungen schnell finden 180
Bezüge umsetzen 182
5.6 Texte und Zahlen manipulieren 184
Texte finden 184
Daten bereinigen nach Datentransfer 186
Zellen bereinigen oder manipulieren 187
Telefonliste bearbeiten 189
Warennummer bilden 190
Formeln tauschen 191
Nullen reinpumpen 193
Nullen entfernen 193
Konstante Werte löschen und Formeln erhalten 194
Bereiche säubern 195
Leere Zellen löschen 196
Formeln entfernen 198
Buchstaben aus Zellen entfernen 198
Dateinamen aus Pfad extrahieren 199
Namen drehen 199
5.7 Gültigkeitsprüfung in Excel 200
Zellen mit Gültigkeitsfunktion erkennen 201
Gültigkeitskriterien erstellen 202
Datumsgrenzen festlegen 203
Keine Arbeit am Sonntag 204
Gültigkeitstypen ermitteln 206
Gültigkeitskriterien löschen 207
5.8 Kommentare in Excel 207
Kommentare einfügen 207
Tabelle mit Kommentaren füllen 210
Kommentare schnell finden 211
Kommentare löschen 211
Kommentare protokollieren 212
11
Inhalt
Kommentare aus Zellentexten bilden 214
Kommentare formatieren 214
Kommentarfester anpassen 215
5.9 Sonstige Lösungen mit Zellen und Bereichen 216
Die Anzahl der Wörter im markierten Bereich ermitteln 216
Das Auftauchen eines Zeichens im markierten Bereich ermitteln 217
Werte in Bereichen zählen 218
Die Eckdaten eines Bereiches ermitteln 219
Erste freie Zelle in Bereich ermitteln 220
Zellen mit gleichem Inhalt füllen 221
Markierter Bereich wird zum Druckbereich 222
Bereiche kopieren 222
Grafiken in Bereiche integrieren 224
Zellen sperren 228
Nicht gesperrte Zellen markieren 229
Mehrzellige Eingabe in Zellen 229
Daten suchen 230
Kapitel 6 Die Programmierung von Spalten und Zeilen 233
6.1 Zeilen und Spalten markieren, zählen und ansteuern 234
Mehrere Zeilen auf dem Tabellenblatt markieren 234
Mehrere Spalten auf dem Tabellenblatt markieren 235
Zeilen und Spalten markieren 236
Markierte Spalten in Markierung zählen 236
Letzte freie Zelle in Spalte anspringen 237
Bestimmte Zelle in Spalte ansteuern 237
Anzahl der benutzten Spalten und Zeilen ermitteln 237
Zeilen und Spalten im umliegenden Bereich zählen 238
Erste und letzte Zeile im verwendeten Bereich ermitteln 239
Adresse der letzten verwendeten Zelle einer Zeile ausgeben 241
Markierte Spalten einer Zeile zählen 241
Mehrfachsprünge durchführen 242
Markieren von Zeilen ab bestimmter Position 243
Letzte Zeile in Tabelle einfärben 244
12
Inhalt
6.2 Zeilenhöhe und Spaltenbreite einstellen 245
6.3 Zeilen einfügen und löschen 248
Zeile einfügen 248
Mehrere Zeilen einfügen 248
Zeile löschen 250
Mehrere Zeilen löschen 250
Löschen von Zeilen mit bestimmtem Text 251
Datumszeilen löschen 253
Wochenend-Zeilen löschen 254
Zeilen vor Startdatum löschen 255
Leerzeilen löschen 256
Zeilen innerhalb einer Markierung löschen 257
Zeilen bis auf Überschrift löschen 258
Doppelte Sätze löschen 258
64 Spalten einfügen, löschen und bereinigen 259
Spalte einfügen 260
Mehrere Spalten einfügen 260
Spalte löschen 260
Mehrere Spalten löschen 260
Spalten bereinigen 261
6.5 Zeilen ein- und ausblenden 262
Leere Zeilen ausblenden 264
Alle Zellen einblenden 265
Versteckte Zeilen löschen 266
6.6 Spalten ein- und ausblenden 267
6.7 Spalten und Zeilen formatieren 268
6.8 Daten sortieren 271
6.9 Daten ausdünnen 272
6.10 Spalten vergleichen 273
6.11 Text auf Spalten verteilen 274
6.12 Zeilenumbrüche schnell festlegen 276
6.13 Daten übertragen 280
6.14 Wiederholungszeilen und -spalten definieren 281
13
Inhalt
6.15 Auf der Suche nach der richtigen Zeile 282
Die Suche über die Schleife 282
6.16 Direktsuche bei größeren Datenbeständen 285
6.17 Zeilen filtern 286
6.18 AutoFilter aktivieren bzw. deaktivieren 287
6.19 Filterkriterien setzen 289
Unterbestände ermitteln 289
Bestands- und Preiskontrolle durchführen 290
Zeilen in einem bestimmten Wertebereich einblenden 291
Extremwerte ermitteln 292
Filtern mit Datumswerten 293
6.20 Prüfungen und Auswertungen von Filtern 294
In welchen Spalten ist ein Filterkriterium gesetzt? 294
Wie viele Zeilen sind gefiltert? 295
Wie viele Zellen sind gefiltert? 296
Den größten Wert ermitteln 298
Die erste bzw. letzte gefilterte Zeile ermitteln 299
Wie lauten die Filterkriterien? 300
6.21 Doppelte Werte mit dem Spezialfilter ermitteln 301
6.22 Gefilterte Daten transferieren 302
Gefilterte Zeilen in anderes Tabellenblatt übertragen 302
Gefilterte Zeilen in eine Textdatei übertragen 303
6.23 Filtern von Daten auch bei geschützten Tabellen durchführen 304
6.21» Gliederungen 305
Gliederung einfügen 305
Gliederung entfernen 307
Gruppierung anzeigen und ausblenden 307
Gliederungssymbole bedienen 308
Kapitel 7 Tabellenblätter programmieren 309
7.1 Tabellenblätter einfügen 310
7.2 Tabellen markieren 311
Inhalt
7.3 Tabellenblätter benennen 312
Tabelle nach Tagesdatum benennen 312
Tabelle nach formatiertem Datum benennen 313
Tabelle nach Zelleninhalt benennen 314
Tabelle nach Anwender und Tagesdatum benennen 314
Tabelle einfügen und benennen kombinieren 314
7.ij Tabellenblätter löschen 314
Tabelle löschen ohne Rückfrage 315
Alle Tabellen löschen, nur die aktive Tabelle nicht 316
Alle leeren Tabellen in Arbeitsmappe löschen 317
7.5 Tabellenblätter aktivieren 318
7.6 Tabellenblätter gruppieren 318
Mehrere Tabellen gruppieren 318
Alle Tabellen gruppieren 319
Gruppierte Tabellen übertragen 319
Gruppierte Tabellen ermitteln 320
7.7 Tabellenblätter schützen 320
Tabellenschutz aufheben 321
Alle Tabellen einer Arbeitsmappe schützen 321
Aufgaben trotz Blattschutz ausführen 322
Weitere Schutzfunktionen ab Excel 2002 323
7.8 Tabellenblätter ein- und ausblenden 324
Tabellenblätter sicher ausblenden 325
Tabellen je nach Status ein- oder ausblenden 325
Alle Tabellenblätter anzeigen 325
Alle Tabellen außer der aktiven Tabelle ausblenden 326
7.9 Kopf- und Fußzeilen einrichten 326
Fußzeile mit Anwendernamen 327
Fußzeile mit Pfad 327
Kopfzeile mit Datums- und Zeitangabe 327
Fußzeile mit Dokumenteigenschaften füllen 328
Kopfzeile mit Logo einrichten 329
Mehrzellige Fußzeile anlegen 331
15
Inhalt
7.10 Druckbereiche festlegen 334
7.11 Tabellenblätter drucken 335
Mehrere Kopien drucken 335
Markierte Bereiche drucken 335
Mehrere Tabellenblätter drucken 336
Den integrierten Drucken-Dialog aufrufen 336
Wie viele Druckseiten enthält die Tabelle? 337
7.12 Tabellenblätter kopieren, verschieben und einfügen 338
Tabellenblatt kopieren 338
Tabellenblatt verschieben 339
Tabellenblatt ohne Formeln und Verknüpfungen übertragen 339
Tabelle in andere Arbeitsmappe kopieren 340
Tabellenblatt als E-Mail versenden 341
7.13 Tabellenblätter sortieren 342
7.11» Tabellen suchen 342
7.15 Tabellen sichern 344
Verwendete Zeilen in andere Tabelle transferieren 344
Tabelle kopieren und umbenennen 345
Texte übertragen 345
Tabelle in Textdatei schreiben 346
7.16 Tabellenblätter konsolidieren 347
Tabellenblätter summieren (Festwert) 348
Tabellenblätter summieren (Verknüpfung) 349
Tabellen in einer Tabelle zusammenführen 350
7.17 Sonstige Lösungen mit Tabellen 352
Registerlaschen ein- und ausblenden 352
Registerlaschen einfärben 353
Diashow starten 354
Tabellenblätter verlinken 354
Bilder in Tabellen einfügen 355
Hintergrundbild einfügen 358
Tabellenansicht anpassen 358
16
Inhalt
Kapitel 8 Die Programmierung von Arbeitsmappen 361
8.1 Arbeitsmappen speichern und sichern 361
Arbeitsmappe doppelt sichern 363
Bei Bedarf verzeichnis anlegen 363
Integrierten Speichern unter-Dialog aufrufen 364
Die Ermittlung des Pfades der geladenen Arbeitsmappe 364
Zustand der Arbeitsmappe ermitteln 365
Arbeitsmappe ohne Makros speichern 365
Arbeitsmappe als Textdatei speichern 366
Arbeitsmappe aktuell speichern 367
8.2 Arbeitsmappen öffnen 368
Den integrierten Öffnen-Dialog aufrufen 370
Liste der geöffneten Arbeitsmappen beeinflussen 370
Mehrere Arbeitsmappen öffnen 372
Alle geöffneten Arbeitsmappen ermitteln 373
Die aktuellste Arbeitsmappe öffnen 374
Arbeitsmappe ohne Makrodialog starten 375
8.3 Arbeitsmappen schließen 376
Arbeitsmappe schließen - Änderungen akzeptieren 376
Arbeitsmappe schließen - Änderungen verwerfen 376
Arbeitsmappe schließen bei Vernachlässigung 377
Alle Arbeitsmappen bis auf eine schließen 378
&.k Arbeitsmappe löschen 379
Arbeitsmappe nach Verfallsdatum löschen 379
8.5 Arbeitsmappen einfügen 380
Mappe mit Wochen-Tabellen anlegen 381
Arbeitsmappen aus Zellen erstellen 381
8.6 Arbeitsmappen drucken 382
Nur bestimmte Tabellen drucken 382
8.7 Arbeitsmappen und Dokumenteigenschaften 384
Dokumenteigenschaftsnamen abfragen 384
Dokumenteigenschaftsinhalte ermitteln 385
Letztes Speicherdatum abfragen 386
Erstelldatum herausfinden und manipulieren 387
17
Inhalt
Autor einer Arbeitsmappe ermitteln 388
Zusätzliche Dokumenteigenschaft anlegen 388
Zugriffsdaten einer Arbeitsmappe ermitteln 390
8.8 Arbeitsmappen und Verknüpfungen 391
Verknüpfungen in Hyperlinks umwandeln 391
Verknüpfungen in Arbeitsmappe ermitteln 393
Verknüpfungen aus der Arbeitsmappe entfernen 395
Verknüpfungen aktualisieren 397
Alle Dateien aus einem Ordner dokumentieren 397
8.9 Arbeitsmappe durchsuchen 400
8.10 Arbeitsmappen zusammenführen 402
8.11 Arbeitsmappen miteinander vergleichen 405
8.12 Arbeitsmappen säubern 406
Alle Grafikobjekte aus Arbeitsmappe entfernen 406
Alle Kommentare einer Arbeitsmappe löschen 407
Benutzerdefinierte Listen löschen und erstellen 408
Formatvorlagen löschen und erstellen 409
Alle benutzerdefinierten Namen einer Mappe löschen 411
Kapitel 9 Diagramme und Pivot-Tabellenberichte programmieren 413
9.1 Diagramme erstellen 414
Kosten im Säulendiagramm darstellen , 415
Das Zylinderdiagramm auf Knopfdruck 417
„ Das Balkendiagramm aus Daten in der Umgebung 418
Tagesumsätze im Liniendiagramm anzeigen 419
Tagesgenaue Formatierung im Punktdiagramm 421
Mittelwert in Diagramm bilden 423
9.2 Diagramme löschen 425
Diagrammblätter aus Arbeitsmappe entfernen 425
Eingebettete Diagramme aus Arbeitsmappe löschen 426
9.3 Diagramme identifizieren 426
Diagramme umbenennen 427
9.«t Datenbasis und Diagramm im Wechsel 427
18
Inhalt
9.5 Datenbeschriftungen hinzufügen und auslesen 428
Datenbeschriftung aus Zellen verwenden 430
Daten aus Diagrammen auslesen 432
9.6 Diagramme positionieren und skalieren 434
Die Höhe und Breite eines Diagramms festlegen 434
Diagramme platzieren 435
Diagramme skalieren 438
9.7 Diagramme als Grafiken speichern 440
9.8 Diagramme formatieren 441
Gewinn und Verlust in einem Säulendiagramm präsentieren 441
Eine Ist-Darstellung in einem Säulendiagramm darstellen 444
Legenden formatieren 445
Zusätzliche Labels hinzufügen 445
9.9 Dynamische Diagramme erzeugen 447
9.10 Pivot-Tabellenberichte erstellen 449
9.11 Pivot-Tabellen aktualisieren 451
Einzelne Pivot-Tabelle aktualisieren 451
Mehrere Pivot-Tabellen auf einem Tabellen blatt aktualisieren 452
Alle Pivot-Tabellen in Arbeitsmappe aktualisieren 452
9.12 Pivot-Tabellen dynamisch erweitern 453
9.13 Pivot-Tabellen sortieren 455
9.11» Pivot-Tabellen formatieren 456
9.15 Seitenfeld der Pivot-Tabelle bestimmen 457
Seitenfeldeinstellungauslesen 457
Seitenfelder initialisieren 458
Seitenfelder in Abhängigkeit von Zellen setzen 458
Kapitel 10 Benutzerdefinierte Funktionen und Funktionsmakros 461
10.1 Benutzerdefinierte Funktionen 461
Aktive Arbeitsmappe ermitteln 462
Aktives Tabellenblatt ermitteln 463
Ist eine Tabelle leer? 465
Ist eine Tabelle geschützt? 465
19
Inhalt
Befinden sich Daten in einer bestimmten Spalte? 465
Letzten Wert einer Spalte ermitteln 466
Letzten Wert einer Zeile ermitteln 467
Aktiven Bearbeiter identifizieren 468
Funktion zum Umsetzen von Noten 468
Rangfolge als Text ausgeben 469
Enthält eine bestimmte Zelle eine Formel? 470
Enthält eine bestimmte Zelle ein Gültigkeitskriterium? 471
Enthält eine Zelle einen Kommentar? 472
Ist eine bestimmte Zelle verbunden? 472
Störende Zeichen eliminieren 473
Initialen aus Namen erstellen 473
Formeln und Verknüpfungen in Festwerte umwandeln 474
Nur Zellen mit Fettdruck addieren 475
Nur Zellen mit roter Schriftfarbe addieren 476
Mit Uhrzeiten rechnen 476
Erweitertes Runden durchführen 478
Hintergrundfarben auslesen 478
Zahlenformate ermitteln 479
Schnelles Umrechnen von Geschwindigkeiten 480
Extremwerte berechnen 481
Erste Ziffer in einer Zelle ermitteln 482
Buchstaben aus Zellen entfernen 484
Anzahl der Ziffern einer Zelle ermitteln 485
Römische Zahlen in arabische wandeln 485
Befindet sich eine Grafik in einer Tabelle? 487
Nur Zahlenwerte zulassen 487
10.2 Modulare Funktionen schreiben 489
Dateien in einem Verzeichnis zählen 489
Prüfung, ob eine bestimmte Datei existiert 490
Prüfung, ob eine bestimmte Datei geöffnet ist 491
Prüfung, ob eine Datei gerade bearbeitet wird 492
Prüfung, ob ein bestimmter Name in der Arbeitsmappe verwendet wird 492
Prüfung, ob ein Add-In eingebunden ist 493
20
Inhalt
Dokumenteigenschaften einer Arbeitsmappe ermitteln 495
Bedingte Formatierung mit mehr als drei Farben 497
Wochenende ermitteln 499
Letzten Tag im Monat ermitteln 500
Das Osterdatum für ein bestimmtes Jahr ermitteln 501
Den letzten Wert einer Spalte ermitteln 503
Grafikelemente in einem definierten Bereich löschen 504
10.3 Funktionen verfügbar machen 506
Speichern der Funktionen in der persönlichen Arbeitsmappe 506
Speichern der Funktionen in einem Add-In 507
Ein Add-In einbinden 508
Kapitel 11 Programmierung mit Windows-API-Aufrufen 511
11.1 Ermittlung der Laufwerke am PC 512
n.2 Bedienung des CD-ROM-Laufwerks 514
11.3 Bildschirmauflösung ermitteln 515
ii.it Ist ein externes Programm gestartet? 515
11.5 Wie lange läuft ein externes Programm? 516
n.6 Excel schlafen schicken 518
11.7 Verzeichnisse erstellen/löschen über API 518
11.8 Netzlaufwerk verbinden 519
11.9 Verzeichnisbaum anzeigen und auswerten 520
11.10 Windows-Info-Bildschirm anzeigen 522
11.11 Windows-Verzeichnis ermitteln 522
11.12 Excel-Verzeichnis ermitteln 524
11.13 Standardverzeichnis temporär ändern 524
n.iit Anwendernamen ausgeben 525
11.15 Computernamen ermitteln 525
11.16 Texte mit API-Funktionen konvertieren 526
11.17 Cursorposition in Pixel angeben 526
11.18 Zwischenablage löschen 528
11.19 Sounds per API-Funktion ausgeben 528
21
Inhalt
11.20 PC piepsen lassen 528
11.21 Dateien suchen 529
11.22 Tasten abfangen 530
Kapitel 12 Ereignisse in Excel einsetzen 531
12.1 Ereignisse für die Arbeitsmappe 532
Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-Ereignissen 533
Aktivieren des richtigen Tabellenblatts 534
Einen Bereich begrenzen 536
Alle ausgeblendeten Tabellen bei Bedarf einblenden 537
Letzten Bearbeiter einer Arbeitsmappe ermitteln 538
Makro am Monatsende starten 538
Symbolleisten ein- und ausblenden 539
Datumszelle in Tabelle finden 541
Arbeitsmappe schließen bedingt verhindern 541
Arbeitsmappe ohne Rückfrage schließen 542
Letztes Bearbeitungsdatum festhalten 542
Datenbestand vor dem Speichern sortieren 543
Dauer der Bearbeitung einer Mappe festhalten 544
Schnell Tabellen gleicher Struktur vergleichen 545
Die Lösung für das sparsame Drucken 548
Einfügen von Blättern verhindern 549
Druckstatistik führen 549
Alle Ereignisse für die Arbeitsmappe im Überblick 549
12.2 Ereignisse für das Tabellenblatt 550
Allgemeine Vorgehensweise bei der Einstellung von Tabellenereignissen 551
Passworteingabe beim Aktivieren einer Tabelle 552
Starten einer UserForm 552
Beim Verlassen eines Tabellenblatts Daten neu berechnen 553
»Schiffe versenken« spielen 554
Vergleich von zwei Spalten 555
Änderungen in einer Tabelle sichtbar machen 557
Unterschiedliche Makros je nach Zellenwert starten 558
Automatisch die Spaltenbreite einstellen 560
22
Inhalt
Mehrere Bereiche überwachen 561
Eingabe auf mehreren Tabellen gleichzeitig durchführen 562
Änderungen dokumentieren 563
Wareneingänge dokumentieren 563
Artikelliste bereinigen 564
Nur einmalige Eingabe zulassen 565
Tabellenname aus Zelle herleiten 566
Automatische Suche von Datensätzen 567
Alternative Formularsteuerung 568
Automatisches Runden nach Eingabe 569
Eine Blaupause erzeugen 570
Formelcheck nach Eingabe 571
Nettowert automatisch ausweisen 571
Eine selbst rechnende Zelle 572
Eingabe von Dubletten verhindern 573
Die Lesbarkeit der aktuellen Auswahl erhöhen 574
Mehr Übersichtlichkeit in Tabellen herstellen 576
Aktuelle Zelle übertragen 577
Eingabe verhindern 578
Markierung überwachen 579
Mausklicks überwachen 580
Die Aktualisierung von Pivot-Tabellen überwachen 582
Direkte Verbindung zu einer anderen Tabelle erstellen 582
Alle Ereignisse für Tabellen im Überblick 583
12.3 Reaktion auf Tastendruck 584
Tastenkombinationen ein- und ausschalten 586
Zeilenumbrüche zurücksetzen 587
Texte einfügen 588
Speichern per Tastenkombination verhindern 589
Blattsperre ohne Blattschutz erstellen 589
12.it Zeitsteuerung in Excel 591
Regelmäßig die Uhrzeit anzeigen 591
Die Zeit läuft 592
Regelmäßige Kontrolle, ob Arbeitsmappe verfügbar ist 594
Zellen blinken lassen 595
23
Inhalt
Kapitel 13 VBE-Programmierung 597
13.1 Die VBE-Bibliothek einbinden 598
13.2 Die VBE-Bibliothek deaktivieren 599
13.3 Weitere Bibliotheken einbinden 600
Objektbibliotheken deaktivieren 600
Informationen zu Objektbibliotheken ausgeben 601
13.4 VBE-Editor aufrufen 602
13.5 Die VBE ein- und ausschalten 602
13.6 Neue Module einfügen 603
13.7 Einzelne Module löschen 604
13.8 Makros löschen 604
Einzelnes Makro löschen 604
Alle Makros aus einer Arbeitsmappe entfernen 605
13.9 Module mit Makros bestücken 606
Makro zeilenweise in ein Modul übertragen 606
Makros aus einer Textdatei in ein Modul überführen 607
Alle Mappen aus einem Verzeichnis mit Code versorgen 609
13.10 Export von VBA-Modulen in Textdateien 611
13.11 Identifikation von Komponenten 612
Kapitel ii* Dialoge, Meldungen und UserForms programmieren 615
iit.i Der Meldungen-Dialog Msgbox 616
Welche Schaltfläche wurde angeklickt? 617
Löschrückfrage 618
Informationen anzeigen 619
Ist eine bestimmte Arbeitsmappe vorhanden? 619
14.2 Textfelder für Meldungen einsetzen 620
i/t-3 Die Eingabemaske InputBox 621
Mehrwertsteuer errechnen 623
Mehrere Eingaben erfassen 623
Bereiche über eine Input-Box markieren 624
Funktionen eingeben über Input-Box 625
Inhalt
Kt.it Integrierte Dialoge einsetzen 626
Den Öffnen-Dialog aufrufen 626
Der Dialog Öffnen mit automatischer Passworteingabe 627
Die Farbpalette aufrufen 627
Den Dialog Drucken aufrufen 628
Den Dialog für den Blattschutz anzeigen 629
Den Suchen-Dialog aufrufen 629
Den Dialog Optionen aufrufen 629
Weitere integrierte Dialoge in Excel 630
11t.5 Eigene Dialoge entwerfen und programmieren 631
Eine UserForm zeichnen 631
Der Entwurf einer automatischen dialoggesteuerten Rechnungserfassung 633
Der erste Aufruf der neuen UserForm 636
Reihenfolge der Elemente festlegen 636
UserForms mit Makros bestücken 637
Die UserForm beenden 639
Alle Textfelder initialisieren 640
Inhalte von Textfeldern markieren 640
UserForm mit Tabellendaten füllen 641
Textfelder mit Farben belegen 644
Textfelder prüfen 645
Bezeichnungsfelder dynamisch halten 647
Textfelder ein- und ausblenden 648
Passwortabfrage über UserForm 649
Die Ausmaße von UserForms ändern 651
Die Hintergrundfarbe der UserForm festlegen 653
Listenfelder in UserForms programmieren 655
Listenfelder und Textfelder kombinieren 656
Listenfeld löschen 657
Listeneinträge markieren 658
Listenfeld ohne Leerzeilen füllen 659
Mehrspaltige Listenfelder programmieren 660
Drop-down-Felder einsetzen 662
Drop-downs füllen ohne Duplikate 664
25
Inhalt
Drop-down dynamisch füllen 665
Auswahl aus Drop-down-Feld erzwingen 665
Der Datums-BUG in Drop-down-Felder 666
Abwesenheitszeiten über UserForm erfassen 667
Multiseiten erstellen 670
Multiseiten im Praxiseinsatz 672
Fortschrittsbalken für UserForms programmieren 675
Bilder in UserForms anzeigen 676
iit.6 Zusätzliche Steuerelemente für UserForms 679
Das Kalendersteuerelement 679
Der LevelSlider 682
Das Spreadsheet-Steuerelement 683
Das TreeView-Steuerelement 684
Kapitel 15 Steuerelemente in Tabellen programmieren 687
15.1 Schaltflächen einsetzen 688
Eine Schaltfläche automatisch in eine Tabelle einfügen 688
Schaltfläche mit Bild einfügen 689
Makro automatisch starten 690
Welche Schaltfläche wurde angeklickt? 691
Die mobile Schaltfläche 691
Umschaltflächen programmieren 692
15.2 Textboxen programmieren 695
Textfelder auslesen 695
Textfelder mit Zelleninhalten füllen 696
Textfelder je nach Eingabe sperren 697
15.3 Drop-downs programmieren 698
Drop-down-Felder füllen 698
Drop-down-Felder sortieren 700
Drop-down-Felder leeren 701
Drop-down-Felder automatisch erstellen 701
15.it Listenfelder programmieren 702
Listenfelder füllen 702
Listenfelder bereinigen 703
26
Inhalt
15.5 Drehfelder programmieren 704
Preiskalkulation schrittweise durchführen 704
Objekte schrittweise drehen 705
15.6 Bildlaufleisten einsetzen 706
15.7 Schnelles Umschalten über Optionsfelder 707
15.8 Kontrollkästchen programmieren 708
Mehrfachauswahl mit Kontrollkästchen 708
Kontrollkästchen synchronisieren 709
Kontrollkästchen initialisieren 710
15.9 Steuerelemente identifizieren 711
Kapitel 16 Eigene Menüs und Symbolleisten erstellen 715
Teil 1 - Programmierung von Menüs, Symbolleisten und Kontextmenüs 715
16.1 Die Programmierung von Menüs 717
Ein- und Ausblenden der Menüleiste 718
Ein eigenes Menü erstellen 719
Menübefehle im neuen Menü einfügen 720
Erstellung der Menüleiste automatisieren 723
Die nützlichen Makros aus dem neuen Menü 724
Menüs identifizieren und ansprechen 727
Ganze Menüs deaktivieren 728
Bestimmte Menübefehle suchen und deaktivieren 729
Kaskaden-Menüs erstellen 733
Menübefehle mit Häkchen programmieren 736
16.2 Die Programmierung von Kontextmenüs 743
Kontextmenüs deaktivieren 743
Das Zellen-Kontextmenü erweitern 744
Kontextmenü aufbauen (dreistufig) 747
Kontextmenü zurücksetzen 748
16.3 Die Programmierung von Symbolleisten . . . 748
Neue Symbolleiste erstellen 748
Symbole in neue Symbolleiste integrieren 749
Symbole identifizieren 752
27
Inhalt
Symbolleisten schützen 754
Symbolleiste löschen 755
Grafiken in Symbolleisten integrieren 755
Symbolschaltflächen deaktivieren 757
Drop-downs in Symbolleisten einfügen 758
Teil II - Ribbon-Programmierung 762
i6.it Der »Custom Ul Editor« 762
16.5 Weitere wichtige Quellen und Hilfen 764
16.6 Eigene Ribbons programmieren 766
Ribbon mit Schaltflächen erstellen 766
Ribbon mit ComboBox erstellen 768
Ribbon mit bereits verfügbaren Funktionen bestücken 770
Kapitel 17 Excel und sein Umfeld 773
17.1 Textdateien im Zugriff von Excel 773
Textdateien speichern 774
Textdateien mit Trennzeichen einlesen 775
Textdateien fixer Länge öffnen 776
Mehrere Textdateien in eine Tabelle einfügen 777
17.2 Excel und Word im Duett 779
Excel-Tabelle unverknüpft nach Word übertragen 779
Excel-Tabelle verknüpft nach Word übertragen 783
17.3 Excel im Zusammenspiel mit Access 784
* Datenbankabfrage durchführen 785
Datenbank-Update durchführen ; 787
17.it Excel und Outlook 789
Tabelle als E-Mail versenden 789
Excel-Bereich als E-Mail versenden 791
Mehrere Arbeitsmappen per E-Mail versenden 792
E-Mail bei Änderung senden 794
Kontakte austauschen 795
28
Inhalt
Kapitel 18 Fehlerbehandlung, Tuning und Schutz von VBA-Projekten 799
18.1 Kleinere Fehler beheben 799
18.2 Schwerwiegendere Fehler 802
Fehler im Vorfeld erkennen und reagieren 802
18.3 Laufzeiten verkürzen 804
Befehle zusammenfassen 805
Variablen und Konstanten einsetzen 808
Berechnung und Bildschirmaktualisierung ausschalten 809
Integrierte Tabellenfunktionen anwenden 810
Weitere Faktoren zur Geschwindigkeitssteigerung 810
18.it VBA-Projekte schützen 811
Kapitel 19 FAQ zur Programmierung mit Excel 815
19.1 Bildschirmaktualisierung abschalten 815
19.2 Kopfzeile mit komplettem Pfad 816
19.3 Letzte Zelle in Tabelle finden 817
19.ii Texte suchen und ersetzen 817
19.5 Symbol in Symbolleiste deaktivieren 818
19.6 Nicht benötigte Zeilen löschen 819
19.7 Seitenwechsel festlegen 820
19.8 Druckbereich festlegen bzw. aufheben 820
19.9 Rechnen mit bedingten Formaten 821
19.10 Doppelte Sätze ermitteln 822
19.11 Zeilenumbrüche und Zellenverbunde aufheben 823
19.12 Zellenverknüpfungen finden 823
19.13 Datumsdifferenzen errechnen 825
19.11t Warnmeldungen deaktivieren 825
19.15 Datentransport nach Word 826
19.16 Laufwerk und Pfad der aktiven Arbeitsmappe anzeigen 828
19.17 Drag&Drop ein- und ausschalten 828
19.18 Bilder umbenennen 829
19.19 Dateiinformationen auslesen 830
29
Inhalt
Anhang A Nützliche und interessante Makros 833
A.i Arbeitsmappe als Verknüpfung auf den Desktop legen 833
A.2 Verknüpfungen in Festwerte umwandeln 834
A.3 Automatischer Bildleistenlauf programmieren 835
A.it Tasten deaktivieren 836
A.5 Doppelklicks deaktivieren 836
A.6 Zelleninhalte zusammenführen 838
A.7 Zahlenwerte richtig erkennen 838
A.8 Zellen blinken lassen 839
A.9 Makro aus Word starten 839
A.io Zusammenspiel von Zellen und Textboxen 840
A.11 Dateien als Objekte in Tabelle einfügen 841
A.12 Pfad als Fenstertitel ausgeben 842
A.13 Add-In einbinden 842
A.iit Runden mit Excel 843
A.15 Gefilterte Daten in einer Textdatei ausgeben 844
A.16 Steuerelemente mehrfach einfügen 846
A.17 Daten verschlüsseln 847
A.18 Eingaben widerrufen 848
A.19 Farbige Schaltflächen in Abhängigkeit programmieren 849
A.2O Gitternetzlinien ein- und ausblenden 849
A.21 Diagramm auf Tabelle positionieren 850
A.22 Gleiche Zellen suchen und formatieren 850
A.23 Datumsabhängige Formatierung 851
A.2it Arbeitsmappe kopieren 852
A.25 Ganzzahlige Werte extrahieren 852
A.26 Arbeiten mit Arrays 854
Anhang B Das Wichtigste in einem Add-In 859
B.1 Die Funktionen des MacAddlns 859
B.2 Die Ereignisse 861
30
Inhalt
B.3 Der Quellcode 862
B.it Menüerstellung 862
B.5 Die Makros 866
B.6 Formeln in Festwerte umwandeln 866
B.7 Aktive Tabelle als E-Mail verschicken 866
B.8 Aktive Tabelle in neuer Arbeitsmappe speichern 867
B.9 Informationen zu Datum und Zeit 867
B.io Informationen zur aktiven Arbeitsmappe 868
B.ii Öffnen aller verknüpften Arbeitsmappen 868
B.12 Verknüpfungen zu anderen Arbeitsmappen entfernen 869
B.13 Suchen von Texten in Arbeitsmappen 869
B.iit Zellen bearbeiten 870
B.15 Zellen markieren 871
B.i6 Zellen löschen 872
Anhang C Listings 875
Anhang D Excel-Dateien sicher schützen und verteilen 897
D.i LockXLS 898
Formel- und VBA-Schutz 900
Rechnergebundener Aktivierungscode 901
Einschränkung der Funktionalität mittels VBA 902
D.2 DoneEx 903
Formel- und VBA-Schutz 904
Rechnergebundener Aktivierungscode 906
D.3 Matrixlock 907
Matrix-Crypt 907
VBA-Code-Einbindung 909
Anhang E Der VBA-Code-Viewer 911
Das VBA-Repository 912
Index 915
31
