Excel 2016 programmieren E-Book

Michael Kofler

• Herausgeber: Hanser, Carl
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Deutsch
• Veröffentlichungsjahr: 2016

Seitenzahl: 1124

Beschreibung

Die Möglichkeiten der Excel-Programmierung sind nahezu unbegrenzt.

Das Buch hat sich aufgrund seiner praxisnahen Beispiele und der klaren Strukturierung zu einem Standardwerk für professionelle Excel-Programmierer entwickelt.

In der komplexen Objektwelt von Excel zurechtfinden

- Mit VBA können Sie tägliche Arbeitsvorgänge automatisieren, neue Tabellenfunktionen programmieren, externe Daten einlesen und mit Pivottabellen analysieren, Diagramme erstellen und vieles mehr.
- Dazu müssen Sie sich in der komplexen Objektwelt von Excel zurechtfinden, was Ihnen mit Hilfe dieses Buchs und seiner zahlreichen Schritt-für-Schritt-Beispiele aus der Programmierpraxis mühelos gelingt.
- Extra: E-Book inside

Systemvoraussetzungen für E-Book inside: Internet-Verbindung und Adobe-Reader oder Ebook-Reader bzw. Adobe Digital Editions.

Leseprobe

Michael Kofler Ralf Nebelo

Excel 2016 programmieren

Abläufe automatisieren, (Office-)Add-ins und Anwendungen entwickeln

Die Autoren:

Michael Kofler, GrazRalf Nebelo, Bocholt

Alle in diesem Buch enthaltenen Informationen, Verfahren und Darstellungen wurden nach bestem Wissen zusammengestellt und mit Sorgfalt getestet. Dennoch sind Fehler nicht ganz auszuschließen. Aus diesem Grund sind die im vorliegenden Buch enthaltenen Informationen mit keiner Verpflichtung oder Garantie irgendeiner Art verbunden. Autoren und Verlag übernehmen infolgedessen keine juristische Verantwortung und werden keine daraus folgende oder sonstige Haftung übernehmen, die auf irgendeine Art aus der Benutzung dieser Informationen – oder Teilen davon – entsteht.

Ebenso übernehmen Autoren und Verlag keine Gewähr dafür, dass beschriebene Verfahren usw. frei von Schutzrechten Dritter sind. Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Warenbezeichnungen usw. in diesem Buch berechtigt deshalb auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen­ und Markenschutz­Gesetzgebung als frei zu betrachten wären und daher von jedermann benutzt werden dürften.

Bibliografische Information der Deutschen Nationalbibliothek: Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.d-nb.de abrufbar.

Dieses Werk ist urheberrechtlich geschützt. Alle Rechte, auch die der Übersetzung, des Nachdruckes und der Vervielfältigung des Buches, oder Teilen daraus, vorbehalten. Kein Teil des Werkes darf ohne schriftliche Genehmigung des Verlages in irgendeiner Form (Fotokopie, Mikrofilm oder ein anderes Verfahren) – auch nicht für Zwecke der Unterrichtsgestaltung – reproduziert oder unter Verwendung elektronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden.

© 2016 Carl Hanser Verlag Münchenwww.hanser-fachbuch.de

Lektorat: Brigitte Bauer-Schiewek Copyediting: Petra Kienle, Fürstenfeldbruck Herstellung: Irene Weilhart Umschlagdesign: Marc Müller-Bremer, www.rebranding.de, München Umschlagrealisation: Stephan Rönigk

Print-ISBN: 978-3-446-43866-8 E-Pub-ISBN: 978-3-446-45081-3

Verwendete Schriften: SourceSansPro und SourceCodePro (Lizenz) CSS-Version: 1.0

Inhalt

Titelei

Impressum

Inhalt

Vorwort

Konzeption des Buchs

TEIL I Intuitiver Einstieg

1 Das erste Makro

1.1 Begriffsdefinition

1.2 Was ist Visual Basic für Applikationen?

1.3 Beispiel: Eine Formatvorlage mit einem Symbol verbinden

1.4 Beispiel: Makro zur Eingabeerleichterung

1.5 Beispiel: Einfache Literaturdatenbank

1.6 Beispiel: Formular zur Berechnung der Verzinsung von Spareinlagen

1.7 Beispiel: Benutzerdefinierte Funktionen

1.8 Beispiel: Analyse komplexer Tabellen

1.9 Beispiel: Vokabeltrainer

1.10 Weitere Beispiele zum Ausprobieren

2 Neuerungen in Excel 2007 bis 2016

2.1 Die Benutzeroberfläche RibbonX

2.2 Neue Programmfunktionen

2.3 Office-Add-ins

2.4 Neues in Sachen Programmierung

2.4.1 Kompatibilitätskrücke Add-ins-Register

2.4.2 Zu- und Abgänge im Objektmodell

2.4.3 Anpassen der Benutzeroberfläche

2.4.4 Die Grenzen von VBA

2.5 Probleme und Inkompatibilitäten

TEIL II Grundlagen

3 Entwicklungsumgebung

3.1 Komponenten von VBA-Programmen

3.2 Komponenten der Entwicklungsumgebung

3.3 Codeeingabe in Modulen

3.4 Makros ausführen

3.5 Makroaufzeichnung

3.6 Tastenkürzel

4 VBA-Konzepte

4.1 Variablen und Felder

4.1.1 Variablenverwaltung

4.1.2 Felder

4.1.3 Syntaxzusammenfassung

4.2 Prozedurale Programmierung

4.2.1 Prozeduren und Parameter

4.2.2 Gültigkeitsbereich von Variablen und Prozeduren

4.2.3 Verzweigungen (Abfragen)

4.2.4 Schleifen

4.2.5 Syntaxzusammenfassung

4.3 Objekte

4.3.1 Der Umgang mit Objekten, Methoden und Eigenschaften

4.3.2 Der Objektkatalog (Verweise)

4.3.3 Übersichtlicher Objektzugriff durch das Schlüsselwort With

4.3.4 Objektvariablen

4.3.5 Syntaxzusammenfassung

4.4 Ereignisse

4.4.1 Ereignisprozeduren

4.4.2 Ereignisprozeduren deaktivieren

4.4.3 Überblick über wichtige Excel-Ereignisse

4.4.4 Ereignisse beliebiger Objekte empfangen

4.4.5 Ereignisprozeduren per Programmcode erzeugen

4.4.6 Syntaxzusammenfassung

4.5 Programmierung eigener Klassen

4.5.1 Eigene Methoden, Eigenschaften und Ereignisse

4.5.2 Collection-Objekt

4.5.3 Beispiel für ein Klassenmodul

4.5.4 Beispiel für abgeleitete Klassen (Implements)

4.5.5 Eine Klasse als FileSearch-Ersatz

4.5.6 Syntaxzusammenfassung

4.6 Operatoren in VBA

4.7 Virenschutz

4.7.1 Vorhandene Schutzmaßnahmen nutzen

4.7.2 Viren selbst entdecken

4.7.3 Vertrauenswürdige Makros ohne Einschränkungen ausführen

5 Programmiertechniken

5.1 Zellen und Zellbereiche

5.1.1 Objekte, Methoden, Eigenschaften

5.1.2 Anwendungsbeispiele

5.1.3 Syntaxzusammenfassung

5.2 Arbeitsmappen, Fenster und Arbeitsblätter

5.2.1 Objekte, Methoden und Eigenschaften

5.2.2 Anwendungsbeispiele

5.2.3 Syntaxzusammenfassung

5.3 Datentransfer über die Zwischenablage

5.3.1 Zellbereiche kopieren, ausschneiden und einfügen

5.3.2 Zugriff auf die Zwischenablage mit dem DataObject

5.3.3 Syntaxzusammenfassung

5.4 Umgang mit Zahlen und Zeichenketten

5.4.1 Numerische Funktionen, Zufallszahlen

5.4.2 Zeichenketten

5.4.3 Umwandlungsfunktionen

5.4.4 Syntaxzusammenfassung

5.5 Rechnen mit Datum und Uhrzeit

5.5.1 VBA-Funktionen

5.5.2 Tabellenfunktionen

5.5.3 Anwendungs- und Programmiertechniken

5.5.4 Feiertage

5.5.5 Syntaxzusammenfassung

5.6 Umgang mit Dateien, Textimport/-export

5.6.1 File System Objects ‒ Überblick

5.6.2 Laufwerke, Verzeichnisse und Dateien

5.6.3 Textdateien (TextStream)

5.6.4 Binärdateien (Open)

5.6.5 Excel-spezifische Methoden und Eigenschaften

5.6.6 Textdateien importieren und exportieren

5.6.7 Textexport für Mathematica-Listen

5.6.8 Syntaxzusammenfassung

5.7 Benutzerdefinierte Tabellenfunktionen

5.7.1 Grundlagen

5.7.2 Beispiele

5.8 Schutzmechanismen

5.8.1 Bewegungsradius einschränken

5.8.2 Zellen, Tabellenblätter und Arbeitsmappen schützen

5.8.3 Schutzmechanismen für den gemeinsamen Zugriff

5.8.4 Programmcode und Symbolleiste schützen

5.8.5 Syntaxzusammenfassung

5.9 Konfigurationsdateien, individuelle Konfiguration

5.9.1 Optionen

5.9.2 Optionseinstellungen per Programmcode

5.9.3 Konfigurationsdateien

5.10 Tipps und Tricks

5.10.1 Geschwindigkeitsoptimierung

5.10.2 Zeitaufwendige Berechnungen

5.10.3 Effizienter Umgang mit Tabellen

5.10.4 Zusammenspiel mit Excel-4-Makros

5.10.5 Excel-Version feststellen

5.10.6 Hilfe zur Selbsthilfe

5.10.7 Syntaxzusammenfassung

6 Fehlersuche und Fehlerabsicherung

6.1 Hilfsmittel zur Fehlersuche (Debugging)

6.1.1 Syntaxkontrolle

6.1.2 Reaktion auf Fehler

6.1.3 Kontrollierte Programmausführung

6.2 Fehlertolerantes Verhalten von Programmen

6.3 Reaktion auf Programmunterbrechungen

6.4 Syntaxzusammenfassung

7 Dialoge

7.1 Vordefinierte Dialoge

7.1.1 Excel-Standarddialoge

7.1.2 Die Funktionen MsgBox und InputBox

7.1.3 Die Methode Application.InputBox

7.2 Selbst definierte Dialoge

7.2.1 Veränderungen gegenüber Excel 5/7

7.2.2 Einführungsbeispiel

7.3 Der Dialogeditor

7.4 Die MS-Forms-Steuerelemente

7.4.1 Beschriftungsfeld (Label)

7.4.2 Textfeld (TextBox)

7.4.3 Listenfeld (ListBox) und Kombinationslistenfeld (ComboBox)

7.4.4 Kontrollkästchen (CheckBox) und Optionsfelder (OptionButton)

7.4.5 Buttons (CommandButton) und Umschaltbuttons (ToggleButton)

7.4.6 Rahmenfeld (Frame)

7.4.7 Multiseiten (MultiPage), Register (TabStrip)

7.4.8 Bildlaufleiste (ScrollBar) und Drehfeld (SpinButton)

7.4.9 Anzeige (Image)

7.4.10 Formelfeld (RefEdit)

7.4.11 Das UserForm-Objekt

7.5 Steuerelemente direkt in Tabellen verwenden

7.6 Programmiertechniken

7.6.1 Zahleneingabe

7.6.2 Dialoge gegenseitig aufrufen

7.6.3 Dialoge dynamisch verändern

7.6.4 Umgang mit Drehfeldern

8 Die Benutzeroberfläche von Excel 2016

8.1 Menüs und Symbolleisten

8.1.1 Manuelle Bearbeitung von Menüs und Symbolleisten

8.1.2 Programmierte Veränderung von Menüs und Symbolleisten

8.1.3 Programmiertechniken

8.1.4 Blattwechsel über die Symbolleiste

8.1.5 Excel-Anwendungen in Befehlsleisten integrieren

8.1.6 Syntaxzusammenfassung

8.2 Das Menüband

8.2.1 Manuelle Anpassung des Menübands

8.2.2 Programmierte Anpassung des Menübands

8.2.3 RibbonX-Controls

8.2.4 Erweiterte Programmiertechniken

8.2.5 Klassische Menüs und Symbolleisten nachbilden

8.2.6 Anpassungen permanent verfügbar machen

8.2.7 Syntaxzusammenfassung

8.3 Die Symbolleiste für den Schnellzugriff

8.3.1 Symbolleiste für den Schnellzugriff manuell anpassen

8.3.2 Symbolleiste für den Schnellzugriff programmiert anpassen

8.3.3 Syntaxzusammenfassung

8.4 Kontextmenüs

8.4.1 Kontextmenüs programmiert anpassen

8.4.2 Syntaxzusammenfassung

8.5 Die Backstage-Ansicht

8.5.1 Grundlagen der Programmierung

8.5.2 Backstage-spezifische Steuerelemente

8.5.3 Befehle in den FastCommand-Bereich einfügen

8.5.4 Eigene Backstage-Tabs anlegen

8.5.5 Excel-eigene Backstage-Tabs anpassen

8.5.6 Syntaxzusammenfassung

TEIL III Anwendung

9 Mustervorlagen und „intelligente“ Formulare

9.1 Grundlagen

9.1.1 Gestaltungselemente für „intelligente“ Formulare

9.1.2 Mustervorlagen mit Datenbankanbindung

9.2 Beispiel: Das „Speedy“-Rechnungsformular

9.3 Beispiel: Abrechnungsformular für einen Car-Sharing-Verein

9.4 Grenzen „intelligenter“ Formulare

10 Diagramme und Zeichnungsobjekte

10.1 Umgang mit Diagrammen

10.1.1 Grundlagen

10.1.2 Diagrammtypen

10.1.3 Diagrammelemente (Diagrammobjekte) und Formatierungsmöglichkeiten

10.1.4 Ausdruck

10.2 Programmierung von Diagrammen

10.2.1 Objekthierarchie

10.2.2 Programmiertechniken

10.3 Beispiel: Automatische Datenprotokollierung

10.3.1 Die Bedienung des Beispielprogramms

10.3.2 Programmcode

10.4 Syntaxzusammenfassung

10.5 Die Zelldiagramme der Bedingten Formatierung

10.5.1 Programmierung von Datenbalkendiagrammen

10.5.2 Programmierung von Farbskalendiagrammen

10.5.3 Programmierung von Symbolsatzdiagrammen

10.5.4 Syntaxzusammenfassung

10.6 Sparklines-Diagramme

10.6.1 Programmierung von Sparklines-Diagrammen

10.6.2 Syntaxzusammenfassung

10.7 SmartArt-Diagramme

10.7.1 Programmierung von SmartArt-Diagrammen

10.7.2 Benutzerdefinierte SmartArt-Diagramme

10.7.3 Syntaxzusammenfassung

10.8 Neue Diagrammtypen in Excel 2016

10.8.1 Programmierung von Wasserfall-Diagrammen

10.8.2 Programmierung von Histogrammen

10.8.3 Programmierung von Pareto-Diagrammen

10.8.4 Programmierung von Kastengrafik-Diagrammen

10.8.5 Programmierung von Treemap-Diagrammen

10.8.6 DirectoryMap ‒ Inhaltsverzeichnisse visualisieren

10.8.7 Programmierung von Sunburst-Diagrammen

10.9 Zeichnungsobjekte (Shapes)

11 Datenverwaltung in Excel

11.1 Grundlagen

11.1.1 Einleitung

11.1.2 Kleines Datenbankglossar

11.1.3 Excel versus Datenbanksysteme

11.2 Datenverwaltung innerhalb von Excel

11.2.1 Eine Datenbank in Excel erstellen

11.2.2 Daten über die Datenbankmaske eingeben, ändern und löschen

11.2.3 Daten sortieren, suchen, filtern

11.3 Datenverwaltung per VBA-Code

11.3.1 Programmiertechniken

11.3.2 Syntaxzusammenfassung

11.4 Datenbank-Tabellenfunktionen

11.5 Tabellen konsolidieren

11.5.1 Grundlagen

11.5.2 Konsolidieren per VBA-Code

11.6 Beispiel: Abrechnung eines Car-Sharing-Vereins

11.6.1 Bedienung

11.6.2 Überblick über die Komponenten der Anwendung

11.6.3 Programmcode

12 Zugriff auf externe Daten

12.1 Grundkonzepte relationaler Datenbanken

12.2 Import externer Daten

12.2.1 Datenimport mit Power Query

12.2.2 Datenimport mit MS Query

12.2.3 Das QueryTable-Objekt

12.2.4 Excel-Daten exportieren

12.3 Datenbankzugriff mit der ADO-Bibliothek

12.3.1 Einführung

12.3.2 Verbindungsaufbau (Connection)

12.3.3 Datensatzlisten (Recordset)

12.3.4 SQL-Kommandos (Command)

12.3.5 SQL-Grundlagen

12.3.6 Syntaxzusammenfassung

12.4 Beispiel: Fragebogenauswertung

12.4.1 Überblick

12.4.2 Aufbau des Fragebogens

12.4.3 Aufbau der Datenbank

12.4.4 Programmcode

13 Datenanalyse in Excel

13.1 Daten gruppieren (Teilergebnisse)

13.1.1 Einführung

13.1.2 Programmierung

13.2 Pivot-Tabellen (Kreuztabellen)

13.2.1 Einführung

13.2.2 Gestaltungsmöglichkeiten

13.2.3 Pivot-Tabellen für externe Daten

13.2.4 Pivot-Tabellenoptionen

13.2.5 Pivot-Diagramme

13.3 Programmiertechniken

13.3.1 Pivot-Tabellen erzeugen und löschen

13.3.2 Aufbau und Bearbeitung vorhandener Pivot-Tabellen

13.3.3 Interne Verwaltung (PivotCache)

13.3.4 Syntaxzusammenfassung

14 XML- und Listenfunktionen

14.1 Bearbeitung von Listen

14.2 XML-Grundlagen

14.3 XML-Funktionen interaktiv nutzen

14.4 XML-Programmierung

15 Excel-Programmierung für Fortgeschrittene

15.1 Excel-Add-ins

15.2 Excel und das Internet

15.2.1 Excel-Dateien als E-Mail versenden

15.2.2 HTML-Import

15.2.3 HTML-Export

15.3 Smart Tags

15.4 Web Services nutzen

15.5 Dynamic Link Libraries (DLLs) verwenden

15.6 ActiveX-Automation (COM)

15.6.1 Excel als Client (Steuerung fremder Programme)

15.6.2 Excel als Server (Steuerung durch fremde Programme)

15.6.3 Neue Objekte für Excel (ClipBoard-Beispiel)

15.6.4 Object Linking and Embedding (OLE)

15.6.5 Automation und Visual Basic .NET

15.6.6 Programme ohne ActiveX starten und steuern

15.6.7 Syntaxzusammenfassung

15.7 64-Bit-Programmierung

15.7.1 Kompatibilitätsprobleme

15.7.2 Ein problematisches (32-Bit-)Beispiel

15.7.3 Syntaxzusammenfassung

15.8 Visual Studio Tools for Office

15.8.1 Bestandsaufnahme: die Grenzen von VBA

15.8.2 VSTO: Profi-Werkzeug für Profi-Entwickler

15.8.3 Grundlagen des VSTO-Einsatzes

15.8.4 Beispielprojekte

15.9 Office-Add-ins

15.9.1 Bestandteile eines Office-Add-ins

15.9.2 Typen von Office-Add-ins

15.9.3 Werkzeuge für die Entwicklung von Office-Add-ins

15.9.4 Beispiel 1: SimpleApp

15.9.5 Das JavaScript-API für Office

15.9.6 Beispiel 2: ComplexApp

Anhang

A Inhalte der Download-Dateien zum Buch

A.1 Objektreferenz

A.2 Hyperlinks

A.3 Beispieldateien

B Verwendete Literatur

C Nachweis der Grafiken & Icons

Vorwort

TEIL I: Intuitiver Einstieg

1Das erste Makro

Im Verlauf des Kapitels lernen Sie Begriffe wie Makro oder Visual Basic für Applikationen kennen, zeichnen selbst Makros auf, integrieren diese in die Symbolleiste für den Schnellzugriff, stellen eine einfache Datenbankanwendung zusammen etc. Das Kapitel gibt Ihnen einen ersten Einblick in Themen, die im Buch später viel ausführlicher aufgegriffen werden.

1.1 Begriffsdefinition

Makro

Dieses Kapitel steht unter dem Motto „Das erste Makro“. Daher soll zunächst einmal der Begriff „Makro“ erklärt werden:

Ein Makro bezeichnet eine Reihe von Anweisungen an den Computer, die dieser ausführt, sobald er dazu aufgefordert wird.

Welchen Zweck haben Makros? Hier ein paar Antworten:

Sie können Arbeitsschritte, die sich häufig wiederholen, automatisieren und vereinfachen.

Sie können Excel und dessen Benutzeroberfläche ganz an Ihre Bedürfnisse anpassen.

Sie können die Bedienung von Excel für andere Anwender vereinfachen, sodass diese praktisch ohne Schulung konkrete Excel-Anwendungen bedienen können.

Und schließlich können Sie echte „Programme“ (Anwendungen) schreiben, denen man kaum mehr anmerkt, dass sie in Excel entstanden sind.

Da der Computer natürlichsprachliche Anweisungen wie „Speichere diese Datei!“ oder „Stelle die drei markierten Zellen in einer größeren Schrift dar!“ leider nicht versteht, müssen Makroanweisungen in einer speziellen Sprache formuliert werden. Aus Kompatibilitätsgründen stellt Excel zwei Sprachen zur Auswahl:

Die herkömmliche Makroprogrammiersprache hat sich im Verlauf der ersten Excel-Versionen gebildet. Makros in dieser Sprache heißen herkömmliche Makros oder Excel-4-Makros, weil die Grundkonzepte dieser Sprache seit der Programmversion 4 nicht mehr verändert oder erweitert wurden. Die Schlüsselwörter der herkömmlichen Makroprogrammiersprache werden in deutscher Sprache angegeben.

Mit Excel 5 wurde die Sprache Visual Basic für Applikationen (kurz VBA) eingeführt. Sie bietet mehr und ausgefeiltere Möglichkeiten der Programmsteuerung, wirkt aber vielleicht auf den ersten Blick etwas umständlich (insbesondere dann, wenn Sie schon einmal ein herkömmliches Makro geschrieben haben).