E-Book, Deutsch, 1037 Seiten
Reihe: Rheinwerk Computing
Held VBA mit Excel
6. Auflage 2025
ISBN: 978-3-367-10575-5
Verlag: Rheinwerk
Format: EPUB
Kopierschutz: 0 - No protection
Das umfassende Handbuch
E-Book, Deutsch, 1037 Seiten
Reihe: Rheinwerk Computing
ISBN: 978-3-367-10575-5
Verlag: Rheinwerk
Format: EPUB
Kopierschutz: 0 - No protection
Ob Einsteiger oder fortgeschrittener Anwender : Mit diesem Buch werden Sie unter Anleitung des Erfolgsautors Bernd Held zum Profi in VBA mit Excel. Von den Grundlagen und der Objektprogrammierung über Funktionen und Ereignisse bis hin zu UserForms oder Symbolleisten : Hier ist alles drin, mit zahlreichen Beispielen illustriert. Dank der Best Practices erstellen Sie leicht Ihren eigenen Code und haben im Fehlerfall immer die passende Lösung parat . Sie erfahren zudem, wie Sie von KI-Tools bei der VBA-Programmierung profitieren. Das Buch ist Einführung, Arbeitsbuch und Nachschlagewerk in einem und unverzichtbar für die Arbeit mit Excel-VBA. Mit über 650 sofort einsetzbaren Makros zum Download. Geeignet für Excel 2016 bis 2024 und das Office-Abo Microsoft 365.
Aus dem Inhalt:
- Grundsätzliches zur Programmierung
- Die Arbeitsumgebung für die Programmierung
- Datentypen, Variablen und Konstanten
- Sprachelemente in VBA in der praktischen Anwendung
- Zellen und Bereiche programmieren
- Die Programmierung von Spalten und Zeilen
- Tabellenblätter programmieren
- Die Programmierung von Arbeitsmappen
- Diagramme und Pivot-Tabellenberichte programmieren
- Benutzerdefinierte Funktionen und Funktionsmakros
- Programmierung durch Windows-API-Aufrufe
- Ereignisse in Excel einsetzen
- VBE-Programmierung
- Dialoge, Meldungen und UserForms programmieren
- Steuerelemente in Tabellen programmieren
- Eigene Menüs und Symbolleisten erstellen
- Excel und sein Umfeld
- Fehlerbehandlung, Tuning und Schutz von VBA-Projekten
- KI-Tools als nützliche Helfer
- FAQ zur Programmierung mit Excel
- Aufstellung aller wichtigen VBA-Befehle
- Sofort einsetzbare Makros
Autoren/Hrsg.
Weitere Infos & Material
Materialien zum Buch ... 25
Vorwort ... 27
1. Die Entwicklungsumgebung von Excel ... 31
1.1 ... Excel auf die Programmierung vorbereiten ... 31
1.2 ... Die Bestandteile der Entwicklungsumgebung ... 34
2. Datentypen, Variablen und Konstanten ... 69
2.1 ... Der Einsatz von Variablen ... 69
2.2 ... Die Verwendung von Konstanten ... 88
3. Die Sprachelemente von Excel-VBA ... 103
3.1 ... Bedingungen ... 103
3.2 ... Typische Aufgaben aus der Praxis ... 104
3.3 ... Die Kurzform einer Bedingung ... 112
3.4 ... Die Anweisung »Select Case« einsetzen ... 114
3.5 ... Die »Enum«-Anweisung ... 119
3.6 ... Schleifen erstellen und verstehen ... 121
3.7 ... Sonstige Sprachelemente ... 155
4. Zellen und Bereiche programmieren ... 161
4.1 ... Zahlenformat einstellen und/oder konvertieren ... 161
4.2 ... Zellen, Rahmen und Schriften formatieren ... 179
4.3 ... Die bedingte Formatierung von Excel ... 195
4.4 ... Bereiche und Zellen benennen ... 209
4.5 ... Tabellenfunktionen einsetzen ... 221
4.6 ... Matrixformeln in der Programmierung nutzen ... 243
4.7 ... Texte und Zahlen manipulieren ... 248
4.8 ... Gültigkeitsprüfung in Excel ... 260
4.9 ... Kommentare in Excel einsetzen ... 273
5. Spalten und Zeilen programmieren ... 287
5.1 ... Zeilen und Spalten ansprechen, ansteuern und anpassen ... 287
5.2 ... Zeilen einfügen und löschen ... 297
5.3 ... Spalten einfügen, löschen und bereinigen ... 303
5.4 ... Zeilen ein- und ausblenden ... 305
5.5 ... Spalten ein- und ausblenden ... 306
5.6 ... Spalten und Zeilen formatieren ... 307
5.7 ... Daten sortieren ... 309
5.8 ... Spalte(n) vergleichen ... 315
5.9 ... Zeilen filtern ... 318
5.10 ... Zeilen über das Teilergebnis gruppieren ... 350
6. Tabellen und Diagramme programmieren ... 353
6.1 ... Tabellen einfügen ... 353
6.2 ... Tabellenblätter benennen ... 354
6.3 ... Tabellen markieren ... 357
6.4 ... Tabellenblätter gruppieren ... 359
6.5 ... Tabellenblätter löschen ... 361
6.6 ... Tabellenblätter ein- und ausblenden ... 365
6.7 ... Tabellenblätter schützen ... 369
6.8 ... Tabellen einstellen ... 373
6.9 ... Tabellenblätter drucken und PDF erstellen ... 387
6.10 ... Tabelleninhaltsverzeichnis erstellen ... 390
6.11 ... Intelligente Tabellen ... 392
6.12 ... Pivot-Tabellen erstellen ... 400
6.13 ... Diagramme programmieren ... 409
6.14 ... Tabellen blitzschnell vergleichen und Unterschiede dokumentieren ... 427
7. Arbeitsmappen und Dateien programmieren ... 433
7.1 ... Arbeitsmappen ansprechen ... 433
7.2 ... Arbeitsmappen anlegen ... 434
7.3 ... Arbeitsmappen speichern ... 437
7.4 ... Arbeitsmappen öffnen ... 443
7.5 ... Arbeitsmappen schließen ... 448
7.6 ... Arbeitsmappe löschen ... 450
7.7 ... Arbeitsmappen drucken ... 453
7.8 ... Dokumenteigenschaften verarbeiten ... 456
7.9 ... Arbeitsmappen und Verknüpfungen ... 461
7.10 ... Arbeitsmappe durchsuchen ... 467
7.11 ... Arbeitsmappen miteinander vergleichen ... 468
7.12 ... Arbeitsmappenübersicht erstellen ... 471
7.13 ... Textdateien importieren ... 473
7.14 ... Makros für das Dateimanagement ... 483
8. Eigene Funktionen und reguläre Ausdrücke ... 495
8.1 ... Benutzerdefinierte Funktionen ... 495
8.2 ... Jahresbericht mit nur einer Funktion blitzschnell erstellen ... 520
8.3 ... Modulare Funktionen schreiben ... 526
8.4 ... Funktionen verfügbar machen ... 539
8.5 ... Mit regulären Ausdrücken programmieren ... 541
9. Ereignisse programmieren ... 555
9.1 ... Ereignisse für die Arbeitsmappe ... 555
9.2 ... Ereignisse für das Tabellenblatt ... 567
9.3 ... Reaktion auf Tastendruck ... 580
9.4 ... Zeitsteuerung in Excel ... 585
10. Die VBE-Programmierung ... 591
10.1 ... Die VBE-Bibliothek einbinden ... 592
10.2 ... Die VBE ein- und ausschalten ... 597
10.3 ... Einzelnes Makro löschen ... 599
10.4 ... Alle Makros aus einer Arbeitsmappe entfernen ... 600
10.5 ... Identifikation von Komponenten ... 605
10.6 ... Ein bestimmtes Makro auskommentieren ... 606
10.7 ... Das Direktfenster löschen ... 607
10.8 ... Den Status des VBA-Projekts abfragen ... 607
10.9 ... Makros und Ereignisse dokumentieren ... 608
11. Dialoge, Meldungen und UserForms programmieren ... 611
11.1 ... »MsgBox«-Meldung ... 612
11.2 ... Die »InputBox«-Eingabemaske ... 615
11.3 ... Integrierte Dialoge einsetzen ... 619
11.4 ... Eigene UserForms entwerfen ... 623
11.5 ... Programmierung von Textfeldern ... 631
11.6 ... Programmierung von Listenfeldern ... 659
11.7 ... Programmierung von Kombinationsfeldlisten ... 678
11.8 ... Die Programmierung von Optionsschaltflächen ... 690
11.9 ... Die Programmierung von Kontrollkästchen ... 696
11.10 ... Die Programmierung von Bildelementen ... 703
11.11 ... Die Programmierung sonstiger Steuerelemente ... 708
11.12 ... Das Verwaltungstool ... 729
11.13 ... Ist das ListView-Steuerelement die bessere Listbox? ... 741
12. Excel im Umfeld von Office programmieren ... 751
12.1 ... Excel im Zusammenspiel mit PowerPoint ... 751
12.2 ... Excel im Zusammenspiel mit Word ... 762
12.3 ... Excel im Zusammenspiel mit Outlook ... 773
12.4 ... Excel im Zusammenspiel mit Access ... 790
12.5 ... Excel im Zusammenspiel mit dem Internet Explorer ... 809
13. Datenfelder, ADO, Dictionaries und Collections programmieren ... 813
13.1 ... Aufgaben mithilfe von ADO und SQL-Statements lösen ... 813
13.2 ... Arbeiten mit Arrays ... 838
13.3 ... Arbeiten mit dem »Dictionary«-Objekt ... 849
13.4 ... Arbeiten mit Collections ... 865
13.5 ... Intelligente Arrays mit Zusatzfunktionen ... 871
14. Die Programmierung der Excel-Oberfläche ... 877
14.1 ... Die Programmierung von Kontextmenüs ... 877
14.2 ... Die Menübandprogrammierung ... 883
15. Fehlerbehandlung, Tuning und Schutz von VBA-Projekten sowie Support durch KI ... 895
15.1 ... Kleinere Fehler beheben ... 895
15.2 ... Schwerwiegendere Fehler ... 897
15.3 ... Das Add-in MZ-Tools ... 900
15.4 ... Laufzeiten verkürzen ... 903
15.5 ... VBA-Projekte schützen ... 905
15.6 ... KI und Excel-VBA ... 906
15.7 ... Mein Fazit ... 914
16. Typische Verarbeitungsaufgaben aus der Praxis ... 915
16.1 ... Daten übertragen ... 915
16.2 ... Daten im Batch verarbeiten ... 918
16.3 ... Daten verteilen ... 920
16.4 ... Berichtsfilterseiten erstellen ... 928
16.5 ... Daten löschen ... 931
16.6 ... Daten kennzeichnen ... 934
16.7 ... Diagramme automatisch formatieren ... 941
16.8 ... Daten mithilfe von Wildcards finden ... 945
16.9 ... Zwei identische Bereiche miteinander vergleichen ... 946
16.10 ... Suche nach einem Begriff unter Berücksichtigung der Formatierung ... 948
16.11 ... Automatische Sicherung von E-Mails in einer Access-Datenbank ... 949
16.12 ... Einen Durchschnitt aus den Top-5-Werten ermitteln ... 952
16.13 ... Arbeitsmappen auf Knopfdruck automatisch erstellen ... 954
16.14 ... Alle Formeln einer Tabelle schützen und verstecken ... 955
16.15 ... Eine Unikatsliste über den Einsatz von SQL erstellen ... 956
16.16 ... Erstellung eines Kalenders mit VBA -- Schritt für Schritt ... 959
16.17 ... Kriterien für eine Mehrfachfilterung aus Zellen beziehen ... 961
16.18 ... Bestimmte Zeichenfolge in einem Bereich entfernen ... 962
16.19 ... Automatisch eine Kopie der Mappe erstellen ... 963
16.20 ... Einen Excel-Bereich als Objekt nach PowerPoint übertragen ... 964
16.21 ... Einen formatierten Text in eine PowerPoint-Folie übertragen ... 966
16.22 ... Top-Werte ermitteln und kennzeichnen ... 968
16.23 ... Daten aus einer geschlossenen Mappe ziehen ... 970
16.24 ... Ein PDF in Excel erstellen und direkt versenden ... 973
16.25 ... Daten verdichten ... 974
16.26 ... Daten in Mappen aktualisieren, ohne diese zu öffnen ... 980
16.27 ... Eine Suchfunktion für eine ListBox erstellen ... 983
16.28 ... Mehrere Bilder dynamisch in eine Tabelle einfügen (Bildergalerie) ... 986
16.29 ... Bedingte Summierung direkt im Arbeitsspeicher vornehmen ... 989
16.30 ... Über zwei Arrays Jahresumsätze pro Monat auswerten ... 994
16.31 ... Bedingte Summierung mit mehreren Kriterien im Speicher durchführen ... 997
16.32 ... Pfeilsymbol oberhalb einer bestimmten Säule in einem Diagramm einfügen ... 1001
16.33 ... Excel erweitern -- doppelte Werte finden und unterschiedlich kennzeichnen ... 1004
16.34 ... Bestimmte Zeilen übertragen -- Kriterien dynamisch zusammenstellen ... 1007
16.35 ... Der Vollautomat -- alle Dateien eines Verzeichnisses schnell verarbeiten ... 1010
16.36 ... Eine Mehrfachsuche in einer Tabelle mit einem Array beschleunigen ... 1013
Index ... 1017
1.2 Die Bestandteile der Entwicklungsumgebung
Auf den nächsten Seiten werde ich die einzelnen Werkzeuge der Entwicklungsumgebung besprechen. Dabei werde ich Ihnen die wichtigsten Bestandteile der Entwicklungsumgebung sowie typische tägliche Arbeiten beschreiben, die ich bei der Programmierung meiner Projekte nutze und die sich als nützlich und zweckdienlich erwiesen haben.
1.2.1 Der Projekt-Explorer und das »Eigenschaften«-Fenster
Drücken Sie die Tastenkombination (Alt) + (F11), um am schnellsten in die Entwicklungsumgebung von Microsoft Excel zu gelangen.
Abbildung 1.4 Die Entwicklungsumgebung von Excel
Standardmäßig sind die beiden Fenster Projekt-Explorer und Eigenschaften eingeblendet. Sollten sie noch nicht sichtbar sein, dann blenden Sie sie über das Menü Ansicht ein. Diese beiden Fenster arbeiten zusammen und bedingen einander.
Tabellen ein- oder ausblenden
Im Projekt-Explorer (links oben) werden alle momentan geöffneten Arbeitsmappen und die darin enthaltenen Tabellen angezeigt. Im Zusammenspiel mit dem darunterliegenden Eigenschaften-Fenster erkennen Sie beispielsweise, welche Tabellen einer Arbeitsmappe angezeigt bzw. ausgeblendet sind. Markieren Sie dazu exemplarisch Tabelle1 im Projekt-Explorer, und wählen Sie im Eigenschaften-Fenster die Eigenschaft Visible aus.
Abbildung 1.5 Den Status der Tabelle abfragen
Im Eigenschaften-Fenster können Sie über das Dropdown bei der Eigenschaft Visible festlegen, ob Sie eine Tabelle ein- oder ausblenden möchten. Dabei fällt auf, dass es drei Ausprägungen dafür gibt:
-
1 – xlSheetVisible: Die Tabelle ist sichtbar.
-
0 – xlSheetHidden: Die Tabelle ist ausgeblendet.
-
2 – xlSheetVeryHidden: Die Tabelle ist sicher ausgeblendet, das heißt, »normale« Anwenderinnen und Anwender können diese Tabelle nicht mehr einblenden. Das Einblenden von solch versteckten Tabellen kann nur über die Entwicklungsumgebung oder ein Makro erfolgen.
Eigenschaften einstellen oder codieren?
Eigenschaften können Sie entweder direkt im Eigenschaften-Fenster festlegen oder – besser noch – über ein Makro setzen. Das Einstellen von Eigenschaften über ein Makro hat den Vorteil, dass Sie die Einstellung beispielsweise von einer Bedingung abhängig machen können. Stellen Sie sich einmal vor, Sie möchten Tabelle1 nur dann sicher ausblenden, wenn ein nicht »bekannter« Anwender Ihre Excel-Arbeitsmappe öffnet.
Ein weiterer Vorteil dieser Vorgehensweise liegt darin, dass Sie das Setzen von Eigenschaften per Makro zusätzlich mit Kommentaren im Quellcode versehen können, um den Quellcode sprechend und nachvollziehbar für andere zu gestalten.
Bei dem folgenden Ereignismakro aus der Arbeitsmappe Zugang.xlsm wird exemplarisch Tabelle1 ausgeblendet. Gehen Sie dazu wie folgt vor:
-
Führen Sie im Projekt-Explorer einen Doppelklick auf die Rubrik DieseArbeitsmappe durch.
-
Wählen Sie im Codefenster auf der rechten Seite im links stehenden Dropdown den Befehl Workbook aus.
Abbildung 1.6 Ein Ereignis einstellen
-
Das jetzt eingestellte Ereignis Workbook_Open wird immer automatisch beim Öffnen der Arbeitsmappe durchlaufen. Die darin enthaltenen Befehle werden dabei nacheinander abgearbeitet.
-
Ergänzen Sie den noch leeren Ereignisrahmen wie folgt:
Private Sub Workbook_Open()
'Dieses Ereignis wird beim Öffnen der Mappe ausgeführt
Tabelle1.Visible = xlSheetVeryHidden
End SubListing 1.1 Eine Tabelle beim Öffnen der Mappe sicher ausblenden
Im Ereignismakro aus Listing 1.1 wird die Eigenschaft Visible auf den Wert xlSheetVeryHidden gesetzt. Damit wird diese Tabelle sicher ausgeblendet.
Um das Ereignismakro Workbook_Open zu starten, müssten Sie die Mappe speichern und schließen. Beim erneuten Öffnen der Mappe würde dieses Ereignis dann automatisch angesteuert. Für dieses Ereignis können Sie aber auch den Mauszeiger in das Ereignis setzen und die Taste (F5) drücken. Dadurch wird das Ereignis ebenso angesteuert und abgearbeitet. Diese Vorgehensweise funktioniert aber nur für dieses eine Ereignis Workbook_Open.
Was sind Ereignisse?
Unter Ereignissen versteht man automatisch ablaufende Makros, die dann gestartet werden, wenn der Anwender eine Aktion in Excel durchführt. Das bekannteste Ereignis Workbook_Open wird beim Öffnen der Mappe ausgeführt. Weitere Ereignisse, wie beispielsweise solche, die beim Drucken (BeforePrint), Speichern (BeforeSave) und Schließen (BeforeClose) einer Arbeitsmappe angelaufen werden, lernen Sie im weiteren Verlauf des Buches kennen. Über den Einsatz von Ereignissen können Sie sich an bestimmte Aktionen des Anwenders anhängen und dabei weitere Aktionen zusätzlich ausführen. Exemplarisch haben Sie gerade beim Öffnen der Mappe Tabelle1 sicher ausgeblendet.
Eine ScrollArea einstellen
Beim folgenden Beispiel wird für Tabelle2 eine sogenannte ScrollArea eingestellt. Dabei handelt es sich um einen Bereich in der Tabelle, in dem sich die Anwenderinnen und Anwender aufhalten dürfen. Das bedeutet, dass Sie über diese Eigenschaft genau festlegen können, welchen Bereich der Tabelle eine Anwenderin betreten darf.
Die ScrollArea muss standardmäßig ein zusammenhängender Bereich in der Tabelle sein. Es ist nicht möglich, mehrere einzelne Bereiche oder Zellen zusammenzufassen.
Diese »Schutzfunktion« ist, sofern Sie sie direkt über dem Eigenschaften-Fenster einstellen, nicht dauerhaft gesetzt. Das bedeutet, dass diese Eigenschaft wieder ungesetzt ist, wenn Sie nach der Einstellung der ScrollArea die Mappe speichern und verlassen und danach wieder öffnen. Daher gehen Sie auch in diesem Fall dazu über, die ScrollArea über ein Makro bzw. ein Ereignismakro zu setzen. Erweitern Sie dazu das vorher eingestellte Workbook_Open-Ereignis aus Listing 1.1, wie in Listing 1.2 gezeigt:
'Dieses Ereignis wird beim Öffnen der Mappe ausgeführt
Tabelle1.Visible = xlSheetVeryHidden
'Die ScrollArea für Tabelle2 setzen
Tabelle2.ScrollArea = "A1:D10"
End Sub
Listing 1.2 Den Aufenthaltsbereich in »Tabelle2« festlegen
Nach Abarbeiten des Ereignisses Workbook_Open kann der Anwender in Tabelle2 den Bereich A1:D10 nicht verlassen.
Kommentare hinterlassen
Wenn Sie Listing 1.2 noch einmal betrachten, dann sehen Sie, dass oberhalb der Befehle noch Kommentare verfasst wurden. Diese Kommentare werden mit einem Apostroph eingeleitet und werden dann von Excel nicht als Befehl interpretiert.
Tabellennamen ansprechen
Ein sehr wichtiges Thema in der Excel-Programmierung ist, wie Sie Tabellen in Excel ansprechen. Wenn Sie einen Blick in den Projekt-Explorer von Excel werfen, stellen Sie fest, dass jeder Tabellenname gleich zweimal genannt wird. Standardmäßig sind beide Tabellennamen gleich. Das muss aber nicht so bleiben.
Abbildung 1.7 Eine Tabelle benennen und ansprechen
Geben Sie exemplarisch Tabelle3...
