VBA mit Excel

Vorwort ... 27


Über den Autor ... 29


1. Grundsätzliches zur Programmierung ... 31


1.1 ... Wie gehe ich von Anfang an richtig vor? ... 31



2. Die Arbeits- und Entwicklungsumgebung für die Programmierung in Excel ... 39


2.1 ... Die Scroll Area festlegen ... 40

2.2 ... Tabellenblätter ein- und ausblenden ... 40

2.3 ... Neue Module einfügen ... 41

2.4 ... Die Symbolleiste »Bearbeiten« ... 42

2.5 ... Automatische Syntaxprüfung ... 46

2.6 ... Schnelles Arbeiten über Tastenkombinationen ... 49

2.7 ... Suchen und Ersetzen ... 50

2.8 ... Der Objektkatalog ... 51

2.9 ... Die Testumgebung ... 53



3. Datentypen, Variablen und Konstanten ... 55


3.1 ... Der Einsatz von Variablen ... 55

3.2 ... Die Verwendung von Konstanten ... 63



4. Sprachelemente in VBA in der praktischen Anwendung ... 65


4.1 ... Verzweigungen ... 65

4.2 ... Die Anweisung »Select Case« für mehr Übersicht ... 71

4.3 ... Schleifen in Excel praxisgerecht einsetzen ... 78



5. Zellen und Bereiche programmieren ... 115


5.1 ... Die Navigation ... 115

5.2 ... Bereiche und Zellen benennen ... 134

5.3 ... Die Formatierung von Zellen und Bereichen ... 152

5.4 ... Formeln einsetzen ... 176

5.5 ... Funktionen erfassen ... 179

5.6 ... Texte und Zahlen manipulieren ... 201

5.7 ... Die Programmierung von Tabellen (Listen bis Excel 2003) ... 220

5.8 ... Gültigkeitsprüfung in Excel ... 225

5.9 ... Kommentare in Excel ... 233

5.10 ... Sonstige Lösungen mit Zellen und Bereichen ... 243



6. Die Programmierung von Spalten und Zeilen ... 263


6.1 ... Zeilen und Spalten markieren, zählen und ansteuern ... 263

6.2 ... Zeilenhöhe und Spaltenbreite einstellen ... 276

6.3 ... Zeilen einfügen und löschen ... 279

6.4 ... Spalten einfügen, löschen und bereinigen ... 293

6.5 ... Zeilen ein- und ausblenden ... 296

6.6 ... Spalten ein- und ausblenden ... 302

6.7 ... Spalten und Zeilen formatieren ... 303

6.8 ... Daten sortieren ... 307

6.9 ... Daten ausdünnen ... 308

6.10 ... Spalten vergleichen ... 309

6.11 ... Text auf Spalten verteilen ... 310

6.12 ... Zeilenumbrüche schnell festlegen ... 313

6.13 ... Daten übertragen ... 317

6.14 ... Wiederholungszeilen und -spalten definieren ... 319

6.15 ... Auf der Suche nach der richtigen Zeile ... 319

6.16 ... Direktsuche bei größeren Datenbeständen ... 323

6.17 ... Zeilen filtern ... 325

6.18 ... Prüfungen und Auswertungen von Filtern ... 334

6.19 ... Doppelte Werte mit dem Spezialfilter ermitteln ... 342

6.20 ... Doppelte Werte mit dem Dictionary-Objekt entfernen ... 344

6.21 ... Gefilterte Daten transferieren ... 347

6.22 ... Filtern von Daten auch bei geschützten Tabellen durchführen ... 349

6.23 ... Gliederungen ... 350



7. Tabellenblätter programmieren ... 357


7.1 ... Tabellenblätter einfügen ... 357

7.2 ... Tabellen markieren ... 360

7.3 ... Tabellenblätter benennen ... 361

7.4 ... Tabellenblätter löschen ... 363

7.5 ... Tabellenblätter aktivieren ... 367

7.6 ... Tabellenblätter gruppieren ... 368

7.7 ... Tabellenblätter schützen ... 370

7.8 ... Tabellenblätter ein- und ausblenden ... 375

7.9 ... Kopf- und Fußzeilen einrichten ... 378

7.10 ... Druckbereiche festlegen ... 387

7.11 ... Tabellenblätter drucken ... 388

7.12 ... Tabellenblätter kopieren, verschieben und einfügen ... 391

7.13 ... Tabellenblätter sortieren ... 396

7.14 ... Tabellen suchen ... 397

7.15 ... Tabellen sichern ... 398

7.16 ... Tabellenblätter konsolidieren ... 403

7.17 ... Sonstige Lösungen mit Tabellen ... 407



8. Die Programmierung von Arbeitsmappen ... 417


8.1 ... Arbeitsmappen speichern und sichern ... 417

8.2 ... Arbeitsmappen öffnen ... 425

8.3 ... Arbeitsmappen schließen ... 434

8.4 ... Arbeitsmappe löschen ... 438

8.5 ... Arbeitsmappe anlegen ... 439

8.6 ... Arbeitsmappen drucken ... 441

8.7 ... Arbeitsmappen und Dokumenteigenschaften ... 443

8.8 ... Arbeitsmappen und Verknüpfungen ... 452

8.9 ... Arbeitsmappe durchsuchen ... 462

8.10 ... Arbeitsmappen miteinander vergleichen ... 464

8.11 ... Arbeitsmappenübersicht erstellen ... 466

8.12 ... Arbeitsmappen säubern ... 468



9. Diagramme und Pivot-Tabellenberichte programmieren ... 477


9.1 ... Diagramme erstellen ... 478

9.2 ... Diagramme löschen ... 490

9.3 ... Diagramme identifizieren ... 492

9.4 ... Datenbasis und Diagramm im Wechsel ... 493

9.5 ... Datenbeschriftungen hinzufügen und auslesen ... 494

9.6 ... Diagramme positionieren und skalieren ... 501

9.7 ... Diagramme als Grafiken speichern ... 507

9.8 ... Diagramme formatieren ... 509

9.9 ... Dynamische Diagramme erzeugen ... 515

9.10 ... Pivot-Tabellenberichte erstellen ... 517

9.11 ... Pivot-Tabellen aktualisieren ... 520

9.12 ... Pivot-Tabellen dynamisch erweitern ... 522

9.13 ... Pivot-Tabellen sortieren ... 524

9.14 ... Pivot-Tabellen formatieren ... 525

9.15 ... Seitenfeld der Pivot-Tabelle bestimmen ... 526

9.16 ... Slicer einfügen ... 529



10. Benutzerdefinierte Funktionen und Funktionsmakros ... 531


10.1 ... Benutzerdefinierte Funktionen ... 531

10.2 ... Modulare Funktionen schreiben ... 563



11. Programmierung mit Windows-API-Aufrufen ... 589


11.1 ... Ermittlung der Laufwerke am PC ... 590

11.2 ... Bedienung des CD-ROM-Laufwerks ... 592

11.3 ... Bildschirmauflösung ermitteln ... 593

11.4 ... Ist ein externes Programm gestartet? ... 594

11.5 ... Wie lange läuft ein externes Programm? ... 594

11.6 ... Excel schlafen schicken ... 597

11.7 ... Verzeichnisse über eine API erstellen bzw. löschen ... 597

11.8 ... Netzlaufwerk verbinden ... 598

11.9 ... Verzeichnisbaum anzeigen und auswerten ... 599

11.10 ... Windows-Info-Bildschirm anzeigen ... 601

11.11 ... Windows-Verzeichnis ermitteln ... 602

11.12 ... Standardverzeichnis temporär ändern ... 603

11.13 ... Anwendernamen ausgeben ... 604

11.14 ... Computernamen ermitteln ... 604

11.15 ... Texte mit API-Funktionen konvertieren ... 605

11.16 ... Cursorposition in Pixel angeben ... 605

11.17 ... Zwischenablage löschen ... 607

11.18 ... Sounds per API-Funktion ausgeben ... 607

11.19 ... PC piepsen lassen ... 608

11.20 ... Dateien suchen ... 608

11.21 ... Tasten abfangen ... 609



12. Ereignisse in Excel einsetzen ... 611


12.1 ... Ereignisse für die Arbeitsmappe ... 612

12.2 ... Ereignisse für das Tabellenblatt ... 634

12.3 ... Reaktion auf Tastendruck ... 670

12.4 ... Zeitsteuerung in Excel ... 678



13. VBE-Programmierung ... 685


13.1 ... Die VBE-Bibliothek einbinden ... 686

13.2 ... Die VBE-Bibliothek deaktivieren ... 687

13.3 ... Weitere Bibliotheken einbinden ... 688

13.4 ... VBE-Editor aufrufen ... 691

13.5 ... Die VBE ein- und ausschalten ... 691

13.6 ... Neue Module einfügen ... 692

13.7 ... Einzelne Module löschen ... 693

13.8 ... Makros löschen ... 693

13.9 ... Module mit Makros bestücken ... 695

13.10 ... Export von VBA-Modulen in Textdateien ... 700

13.11 ... Identifikation von Komponenten ... 702

13.12 ... Ein bestimmtes Makro auskommentieren ... 703

13.13 ... Das Direktfenster löschen ... 703



14. Dialoge, Meldungen und UserForms programmieren ... 705


14.1 ... Die »Msgbox«-Meldung ... 705

14.2 ... Textfelder für Meldungen einsetzen ... 711

14.3 ... Die »InputBox«-Eingabemaske ... 712

14.4 ... Integrierte Dialoge einsetzen ... 717

14.5 ... Eigene Dialoge entwerfen und programmieren ... 727



15. Steuerelemente in Tabellen programmieren ... 779


15.1 ... Schaltflächen einsetzen ... 780

15.2 ... Textboxen programmieren ... 788

15.3 ... Dropdown-Felder programmieren ... 791

15.4 ... Listenfelder programmieren ... 796

15.5 ... Drehfelder programmieren ... 798

15.6 ... Bildlaufleisten einsetzen ... 801

15.7 ... Schnelles Umschalten über Optionsfelder ... 802

15.8 ... Kontrollkästchen programmieren ... 803

15.9 ... Steuerelemente identifizieren ... 806



16. Kontextmenüs und Ribbon-Programmierung ... 809


16.1 ... Die Programmierung von Kontextmenüs ... 809

16.2 ... Die Ribbon-Programmierung ... 815



17. Excel und sein Umfeld ... 827


17.1 ... Textdateien im Zugriff von Excel ... 827

17.2 ... Excel und Word im Duett ... 833

17.3 ... Excel im Zusammenspiel mit Access ... 839

17.4 ... Excel und Outlook ... 844



18. Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten ... 855


18.1 ... Kleinere Fehler beheben ... 855

18.2 ... Schwerwiegendere Fehler ... 857

18.3 ... Das Add-In »MZ-Tools« ... 860

18.4 ... Laufzeiten verkürzen ... 863

18.5 ... VBA-Projekte schützen ... 869



19. FAQ zur Programmierung mit Excel ... 873


19.1 ... Bildschirmaktualisierung abschalten ... 873

19.2 ... Kopfzeile mit komplettem Pfad ... 874

19.3 ... Die letzte Zelle in einer Tabelle finden ... 875

19.4 ... Texte suchen und ersetzen ... 876

19.5 ... Nicht benötigte Zeilen löschen ... 877

19.6 ... Seitenwechsel festlegen ... 878

19.7 ... Druckbereich festlegen bzw. aufheben ... 879

19.8 ... Rechnen mit bedingten Formaten ... 880

19.9 ... Doppelte Sätze ermitteln ... 881

19.10 ... Zeilenumbrüche und Zellenverbunde aufheben ... 882

19.11 ... Zellenverknüpfungen finden ... 883

19.12 ... Datumsdifferenzen errechnen ... 884

19.13 ... Warnmeldungen deaktivieren ... 885

19.14 ... Datentransport nach Word ... 886

19.15 ... Laufwerk und Pfad der aktiven Arbeitsmappe anzeigen ... 889

19.16 ... Drag & Drop ein- und ausschalten ... 889

19.17 ... Bilder umbenennen ... 890

19.18 ... Dateiinformationen auslesen ... 891



20. Nützliche und interessante Makros ... 895


Tabellenblätter alphabetisch sortieren ... 895

Alle Tabellen einer Arbeitsmappe schützen ... 896

Alle Diagramme als Bilder exportieren ... 897

Alle ausgeblendeten Tabellen einblenden ... 899

Buchstaben und Sonderzeichen aus dem Zellinhalt löschen ... 900

In allen Blättern Formeln mit Werten überschreiben ... 901

Alle Formeln schützen und verstecken ... 902

Alle Tabellenblätter in einzelnen Arbeitsmappen speichern ... 903

Alle Zellen mit Formeln oder Verweisen einfärben ... 905

Alle versteckten oder gefilterten Zeilen löschen ... 905



Index ... 907