Excel 2016 programmieren (eBook)

Dr. Michael Kofler wurde 1967 in Innsbruck (Österreich) geboren. Im Oktober 1998 hat er an der technischen Universität in Graz seine Dissertation zum Thema R-trees for Visualizing and Organizing Large 3D GIS Databases abgeschlossen. Michael Kofler ist verheiratet, hat zwei Kinder und lebt in Graz. Michael Kofler zählt zu den renommiertesten und bestverkauften Computerbuchautoren im deutschen Sprachraum. Sein erstes Buch wurde 1987 im Sybex-Verlag veröffentlicht ("Das Atari ST Grafikbuch"). Die meisten aktuellen Bücher sind im Addison-Wesley-Verlag erschienen (siehe die Bibliografie). Vom Linux-Buch wurden mehrere 100.000 Stück verkauft. Einige Bücher liegen auch in Übersetzungen vor (englisch, französisch, italienisch, holländisch, polnisch, russisch, tschechisch, chinesisch).Ralf Nebelo ist Office-Entwickler und freier Journalist.

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
Stichwortverzeichnis 856