Michael Kofler, Ralf Nebelo
Excel 2016 programmieren
Excel Programmierung für Anfänger - Lernen Sie wie Sie ein Diagramm auf Basis einer Pivot Tabelle erstellen und automatisieren Sie Ihre Arbeit mit VBA
Inhalt
6
Vorwort
15
Konzeption des Buchs
19
TEIL I: Intuitiver Einstieg
22
1 Das erste Makro
24
1.1 Begriffsdefinition
24
1.2 Was ist Visual Basic für Applikationen?
27
1.3 Beispiel: eine Formatvorlage mit einem Symbol verbinden
28
1.4 Beispiel: Makro zur Eingabeerleichterung
34
1.5 Beispiel: einfache Literaturdatenbank
36
1.6 Beispiel: Formular zur Berechnung der Verzinsung von Spareinlagen
42
1.7 Beispiel: benutzerdefinierte Funktionen
47
1.8 Beispiel: Analyse komplexer Tabellen
48
1.9 Beispiel: Vokabeltrainer
49
1.10 Weitere Beispiele zum Ausprobieren
55
2 Neuerungen in Excel 2007 bis 2016
62
2.1 Die Benutzeroberfläche RibbonX
63
2.2 Neue Programmfunktionen
66
2.3 Office-Add-ins
70
2.4 Neues in Sachen Programmierung
72
2.4.1 Kompatibilitätskrücke Add-ins-Register
73
2.4.2 Zu- und Abgänge im Objektmodell
74
2.4.3 Anpassen der Benutzeroberfläche
75
2.4.4 Die Grenzen von VBA
76
2.5 Probleme und Inkompatibilitäten
77
TEIL II: Grundlagen
80
3 Entwicklungsumgebung
82
3.1 Komponenten von VBA-Programmen
82
3.2 Komponenten der Entwicklungsumgebung
83
3.3 Codeeingabe in Modulen
90
3.4 Makros ausführen
94
3.5 Makroaufzeichnung
95
3.6 Tastenkürzel
97
4 VBA-Konzepte
100
4.1 Variablen und Felder
100
4.1.1 Variablenverwaltung
100
4.1.2 Felder
105
4.1.3 Syntaxzusammenfassung
108
4.2 Prozedurale Programmierung
110
4.2.1 Prozeduren und Parameter
110
4.2.2 Gültigkeitsbereich von Variablen und Prozeduren
119
4.2.3 Verzweigungen (Abfragen)
123
4.2.4 Schleifen
126
4.2.5 Syntaxzusammenfassung
129
4.3 Objekte
132
4.3.1 Der Umgang mit Objekten, Methoden und Eigenschaften
132
4.3.2 Der Objektkatalog (Verweise)
138
4.3.3 Übersichtlicher Objektzugriff durch das Schlüsselwort With
141
4.3.4 Objektvariablen
142
4.3.5 Syntaxzusammenfassung
144
4.4 Ereignisse
145
4.4.1 Ereignisprozeduren
146
4.4.2 Ereignisprozeduren deaktivieren
149
4.4.3 Überblick über wichtige Excel-Ereignisse
150
4.4.4 Ereignisse beliebiger Objekte empfangen
155
4.4.5 Ereignisprozeduren per Programmcode erzeugen
157
4.4.6 Syntaxzusammenfassung
159
4.5 Programmierung eigener Klassen
162
4.5.1 Eigene Methoden, Eigenschaften und Ereignisse
164
4.5.2 Collection-Objekt
167
4.5.3 Beispiel für ein Klassenmodul
168
4.5.4 Beispiel für abgeleitete Klassen (Implements)
170
4.5.5 Eine Klasse als FileSearch-Ersatz
174
4.5.6 Syntaxzusammenfassung
181
4.6 Operatoren in VBA
182
4.7 Virenschutz
185
4.7.1 Vorhandene Schutzmaßnahmen nutzen
186
4.7.2 Viren selbst entdecken
189
4.7.3 Vertrauenswürdige Makros ohne Einschränkungen ausführen
189
5 Programmiertechniken
192
5.1 Zellen und Zellbereiche
192
5.1.1 Objekte, Methoden, Eigenschaften
192
5.1.2 Anwendungsbeispiele
207
5.1.3 Syntaxzusammenfassung
216
5.2 Arbeitsmappen, Fenster und Arbeitsblätter
218
5.1.4 Objekte, Methoden und Eigenschaften
219
5.1.5 Anwendungsbeispiele
225
5.1.6 Syntaxzusammenfassung
229
5.3 Datentransfer über die Zwischenablage
231
5.3.1 Zellbereiche kopieren, ausschneiden und einfügen
231
5.3.2 Zugriff auf die Zwischenablage mit dem DataObject
233
5.3.3 Syntaxzusammenfassung
234
5.4 Umgang mit Zahlen und Zeichenketten
235
5.4.1 Numerische Funktionen, Zufallszahlen
235
5.4.2 Zeichenketten
237
5.4.3 Umwandlungsfunktionen
242
5.4.4 Syntaxzusammenfassung
244
5.5 Rechnen mit Datum und Uhrzeit
246
5.5.1 VBA-Funktionen
250
5.5.2 Tabellenfunktionen
252
5.5.3 Anwendungs- und Programmiertechniken
253
5.5.4 Feiertage
256
5.5.5 Syntaxzusammenfassung
262
5.6 Umgang mit Dateien, Textimport/-export
263
5.6.1 File System Objects – Überblick
264
5.6.2 Laufwerke, Verzeichnisse und Dateien
266
5.6.3 Textdateien (TextStream)
272
5.6.4 Binärdateien (Open)
274
5.6.5 Excel-spezifische Methoden und Eigenschaften
278
5.6.6 Textdateien importieren und exportieren
281
5.6.7 Textexport für Mathematica-Listen
289
5.6.8 Syntaxzusammenfassung
294
5.7 Benutzerdefinierte Tabellenfunktionen
298
5.7.1 Grundlagen
298
5.7.2 Beispiele
305
5.8 Schutzmechanismen
307
5.8.1 Bewegungsradius einschränken
308
5.8.2 Zellen, Tabellenblätter und Arbeitsmappen schützen
309
5.8.3 Schutzmechanismen für den gemeinsamen Zugriff
313
5.8.4 Programmcode und Symbolleiste schützen
314
5.8.5 Syntaxzusammenfassung
315
5.9 Konfigurationsdateien, individuelle Konfiguration
316
5.9.1 Optionen
316
5.9.2 Optionseinstellungen per Programmcode
317
5.9.3 Konfigurationsdateien
320
5.10 Tipps und Tricks
328
5.10.1 Geschwindigkeitsoptimierung
328
5.10.2 Zeitaufwendige Berechnungen
329
5.10.3 Effizienter Umgang mit Tabellen
333
5.10.4 Zusammenspiel mit Excel-4-Makros
335
5.10.5 Excel-Version feststellen
336
5.10.6 Hilfe zur Selbsthilfe
336
5.10.7 Syntaxzusammenfassung
338
6 Fehlersuche und Fehlerabsicherung
340
6.1 Hilfsmittel zur Fehlersuche (Debugging)
340
6.1.1 Syntaxkontrolle
340
6.1.2 Reaktion auf Fehler
341
6.1.3 Kontrollierte Programmausführung
344
6.2 Fehlertolerantes Verhalten von Programmen
346
6.3 Reaktion auf Programmunterbrechungen
351
6.4 Syntaxzusammenfassung
352
7 Dialoge
354
7.1 Vordefinierte Dialoge
354
7.1.1 Excel-Standarddialoge
354
7.1.2 Die Funktionen MsgBox und InputBox
358
7.1.3 Die Methode Application.InputBox
358
7.2 Selbst definierte Dialoge
360
7.2.1 Veränderungen gegenüber Excel 5/7
361
7.2.2 Einführungsbeispiel
363
7.3 Der Dialogeditor
367
7.4 Die MS-Forms-Steuerelemente
371
7.4.1 Beschriftungsfeld (Label)
372
7.4.2 Textfeld (TextBox)
373
7.4.3 Listenfeld (ListBox) und Kombinationslistenfeld (ComboBox)
376
7.4.4 Kontrollkästchen (CheckBox) und Optionsfelder (OptionButton)
382
7.4.5 Buttons (CommandButton) und Umschaltbuttons (ToggleButton)
383
7.4.6 Rahmenfeld (Frame)
384
7.4.7 Multiseiten (MultiPage), Register (TabStrip)
386
7.4.8 Bildlaufleiste (ScrollBar) und Drehfeld (SpinButton)
390
7.4.9 Anzeige (Image)
392
7.4.10 Formelfeld (RefEdit)
393
7.4.11 Das UserForm-Objekt
395
7.5 Steuerelemente direkt in Tabellen verwenden
398
7.6 Programmiertechniken
405
7.6.1 Zahleneingabe
405
7.6.2 Dialoge gegenseitig aufrufen
407
7.6.3 Dialoge dynamisch verändern
409
7.6.4 Umgang mit Drehfeldern
411
8 Die Benutzeroberfläche von Excel 2016
414
8.1 Menüs und Symbolleisten
414
8.1.1 Manuelle Bearbeitung von Menüs und Symbolleisten
416
8.1.2 Programmierte Veränderung von Menüs und Symbolleisten
422
8.1.3 Programmiertechniken
427
8.1.4 Blattwechsel über die Symbolleiste
430
8.1.5 Excel-Anwendungen in Befehlsleisten integrieren
433
8.1.6 Syntaxzusammenfassung
438
8.2 Das Menüband
439
8.2.1 Manuelle Anpassung des Menübands
440
8.2.2 Programmierte Anpassung des Menübands
444
8.2.3 RibbonX-Controls
451
8.2.4 Erweiterte Programmiertechniken
464
8.2.5 Klassische Menüs und Symbolleisten nachbilden
470
8.2.6 Anpassungen permanent verfügbar machen
473
8.2.7 Syntaxzusammenfassung
474
8.3 Die Symbolleiste für den Schnellzugriff
476
8.3.1 Symbolleiste für den Schnellzugriff manuell anpassen
476
8.3.2 Symbolleiste für den Schnellzugriff programmiert anpassen
478
8.3.3 Syntaxzusammenfassung
479
8.4 Kontextmenüs
479
8.4.1 Kontextmenüs programmiert anpassen
480
8.4.2 Syntaxzusammenfassung
482
8.5 Die Backstage-Ansicht
483
8.5.1 Grundlagen der Programmierung
483
8.5.2 Backstage-spezifische Steuerelemente
484
8.5.3 Befehle in den FastCommand-Bereich einfügen
485
8.5.4 Eigene Backstage-Tabs anlegen
486
8.5.5 Excel-eigene Backstage-Tabs anpassen
491
8.5.6 Syntaxzusammenfassung
494
TEIL III: Anwendung
496
9 Mustervorlagen und „intelligente“ Formulare
498
9.1 Grundlagen
498
9.1.1 Gestaltungselemente für „intelligente“ Formulare
500
9.1.2 Mustervorlagen mit Datenbankanbindung
506
9.2 Beispiel: Das „Speedy“-Rechnungsformular
509
9.3 Beispiel: Abrechnungsformular für einen Car-Sharing-Verein
517
9.4 Grenzen „intelligenter“ Formulare
524
10 Diagramme und Zeichnungsobjekte
526
10.1 Umgang mit Diagrammen
526
10.1.1 Grundlagen
526
10.1.2 Diagrammtypen
527
10.1.3 Diagrammelemente (Diagrammobjekte) und Formatierungsmöglichkeiten
528
10.1.4 Ausdruck
532
10.2 Programmierung von Diagrammen
532
10.2.1 Objekthierarchie
533
10.2.2 Programmiertechniken
537
10.3 Beispiel: Automatische Datenprotokollierung
542
10.3.1 Die Bedienung des Beispielprogramms
543
10.3.2 Programmcode
544
10.4 Syntaxzusammenfassung
555
10.5 Die Zelldiagramme der Bedingten Formatierung
556
10.5.1 Programmierung von Datenbalkendiagrammen
558
10.5.2 Programmierung von Farbskalendiagrammen
559
10.5.3 Programmierung von Symbolsatzdiagrammen
561
10.5.4 Syntaxzusammenfassung
563
10.6 Sparklines-Diagramme
564
10.6.1 Programmierung von Sparklines-Diagrammen
565
10.6.2 Syntaxzusammenfassung
569
10.7 SmartArt-Diagramme
569
10.7.1 Programmierung von SmartArt-Diagrammen
570
10.7.2 Benutzerdefinierte SmartArt-Diagramme
575
10.7.3 Syntaxzusammenfassung
576
10.8 Neue Diagrammtypen in Excel 2016
577
10.8.1 Programmierung von Wasserfall-Diagrammen
577
10.8.2 Programmierung von Histogrammen
579
10.8.3 Programmierung von Pareto-Diagrammen
581
10.8.4 Programmierung von Kastengrafik-Diagrammen
582
10.8.5 Programmierung von Treemap-Diagrammen
584
10.8.6 DirectoryMap – Inhaltsverzeichnisse visualisieren
585
10.8.7 Programmierung von Sunburst-Diagrammen
589
10.9 Zeichnungsobjekte (Shapes)
591
11 Datenverwaltung in Excel
596
11.1 Grundlagen
596
11.1.1 Einleitung
597
11.1.2 Kleines Datenbankglossar
598
11.1.3 Excel versus Datenbanksysteme
599
11.2 Datenverwaltung innerhalb von Excel
601
11.2.1 Eine Datenbank in Excel erstellen
601
11.2.2 Daten über die Datenbankmaske eingeben, ändern und löschen
604
11.2.3 Daten sortieren, suchen, filtern
606
11.3 Datenverwaltung per VBA-Code
613
11.3.1 Programmiertechniken
613
11.3.2 Syntaxzusammenfassung
616
11.4 Datenbank-Tabellenfunktionen
617
11.5 Tabellen konsolidieren
620
11.5.1 Grundlagen
620
11.5.2 Konsolidieren per VBA-Code
623
11.6 Beispiel: Abrechnung eines Car-Sharing-Vereins
624
11.6.1 Bedienung
624
11.6.2 Überblick über die Komponenten der Anwendung
627
11.6.3 Programmcode
629
12 Zugriff auf externe Daten
638
12.1 Grundkonzepte relationaler Datenbanken
638
12.2 Import externer Daten
644
12.2.1 Datenimport mit Power Query
645
12.2.2 Datenimport mit MS Query
653
12.2.3 Das QueryTable-Objekt
664
12.2.4 Excel-Daten exportieren
667
12.3 Datenbankzugriff mit der ADO-Bibliothek
668
12.3.1 Einführung
668
12.3.2 Verbindungsaufbau (Connection)
673
12.3.3 Datensatzlisten (Recordset)
676
12.3.4 SQL-Kommandos (Command)
683
12.3.5 SQL-Grundlagen
684
12.3.6 Syntaxzusammenfassung
687
12.4 Beispiel: Fragebogenauswertung
689
12.4.1 Überblick
689
12.4.2 Aufbau des Fragebogens
692
12.4.3 Aufbau der Datenbank
694
12.4.4 Programmcode
696
13 Datenanalyse in Excel
706
13.1 Daten gruppieren (Teilergebnisse)
706
13.1.1 Einführung
706
13.1.2 Programmierung
708
13.2 Pivot-Tabellen (Kreuztabellen)
710
13.2.1 Einführung
710
13.2.2 Gestaltungsmöglichkeiten
714
13.2.3 Pivot-Tabellen für externe Daten
719
13.2.4 Pivot-Tabellenoptionen
723
13.2.5 Pivot-Diagramme
724
13.3 Programmiertechniken
724
13.3.1 Pivot-Tabellen erzeugen und löschen
725
13.3.2 Aufbau und Bearbeitung vorhandener Pivot-Tabellen
729
13.3.3 Interne Verwaltung (PivotCache)
733
13.3.4 Syntaxzusammenfassung
739
14 XML- und Listenfunktionen
742
14.1 Bearbeitung von Listen
742
14.2 XML-Grundlagen
744
14.3 XML-Funktionen interaktiv nutzen
747
14.4 XML-Programmierung
751
15 Excel-Programmierung für Fortgeschrittene
758
15.1 Excel-Add-ins
758
15.2 Excel und das Internet
763
15.2.1 Excel-Dateien als E-Mail versenden
763
15.2.2 HTML-Import
765
15.2.3 HTML-Export
766
15.3 Smart Tags
768
15.4 Web Services nutzen
771
15.5 Dynamic Link Libraries (DLLs) verwenden
777
15.6 ActiveX-Automation (COM)
782
15.6.1 Excel als Client (Steuerung fremder Programme)
783
15.6.2 Excel als Server (Steuerung durch fremde Programme)
789
15.6.3 Neue Objekte für Excel (ClipBoard-Beispiel)
793
15.6.4 Object Linking and Embedding (OLE)
795
15.6.5 Automation und Visual Basic .NET
799
15.6.6 Programme ohne ActiveX starten und steuern
807
15.6.7 Syntaxzusammenfassung
809
15.7 64-Bit-Programmierung
810
15.7.1 Kompatibilitätsprobleme
810
15.7.2 Ein problematisches (32-Bit-)Beispiel
811
15.7.3 Syntaxzusammenfassung
816
15.8 Visual Studio Tools for Office
817
15.8.1 Bestandsaufnahme: die Grenzen von VBA
817
15.8.2 VSTO: Profi-Werkzeug für Profi-Entwickler
818
15.8.3 Grundlagen des VSTO-Einsatzes
820
15.8.4 Beispielprojekte
824
15.8.4.1 Individuelle Aufgabenbereiche anlegen
824
15.8.4.2 Anpassen des Menübands
826
15.8.4.3 Abfragen von Web Services
829
15.9 Office-Add-ins
832
15.9.1 Bestandteile eines Office-Add-ins
833
15.9.2 Typen von Office-Add-ins
834
15.9.3 Werkzeuge für die Entwicklung von Office-Add-ins
836
15.9.4 Beispiel 1: SimpleApp
836
15.9.5 Das JavaScript-API für Office
843
15.9.6 Beispiel 2: ComplexApp
847
Anhang
852
A Inhalte der Download-Dateien zum Buch
852
A.1 Objektreferenz
852
A.2 Hyperlinks
852
A.3 Beispieldateien
853
B Verwendete Literatur
853
C Nachweis der Grafiken & Icons
854
Stichwortverzeichnis
856
© 2009-2024 ciando GmbH