Vorwort XV

Visual Studio-Tools für Office XV
Danksagung XVI


Einleitung XVII

Die Symbole XVII
Die Schreibweisen XVIII
Die CD-ROM zum Buch XVIII
Die Themen XVIII


1 Der Einstieg in VBA und VBE 1

Die VB-Entwicklungsumgebung (VBE) 1
Der Projekt-Explorer 2
Der VBA-Editor 3
Das Eigenschaftenfenster 4
Der Direktbereich 4
Objekte, Eigenschaften und Methoden 4
Was sind Objekte? 5
Was sind Methoden? 5
Was sind Eigenschaften? 6
Der Objektkatalog 6
Namenskonventionen, 7
Der Makrorekorder 8
Absolute oder relative Aufzeichnung 11
Makros löschen 11
Module löschen, exportieren und importieren 12
Code kommentieren 12
Ein Makro aufrufen 13
Eine Formular-Schaltfläche verwenden 13
Eine Befehlsschaltfläche aus der Steuerelement-Toolbox verwenden 14
Die persönliche Makroarbeitsmappe (Personlxls) 15
Sicherheitseinstellungen 16
Variablen und Konstanten 17
Datentypen 17
Variablen 18
Statische Variablen (Static) 19
Globale Variablen (Public) 20
Konstanten 20
Datentypen konvertieren 21
Datentypen prüfen 23
Präfixe verwenden 24
Mit Fehlern umgehen 24
Fehlermeldungen unterdrücken 25
Fehlerunterdrückung zurücksetzen 26
Fehlermeldungen abfragen 26
Debuggen und Überwachen 27
Das Debuggen 27
Die Verwendung von Haltepunkten 28
Das Überwachungsfenster 28


2 Einfache VBA-Prozeduren und mehr 31

Private und Public für Prozeduren 31
Was ist IntelliSense? 32
Fenster programmieren und aufrufen 32
Meldungsfenster (MsgBox) 33
Eingabefenster (InputBox) 39
Integrierte Dialogfelder 40
Entscheidungen, Verzweigungen und Sprungmarken 41
IfThenElse 41
Ilf-Entscheidung 43
SelectCase 43
GoTo und Exit Sub 44
Schleifen 45
ForNext 45
For Each 47
DoLoop 47
WhileWend 48
Zusammenfassungen mit With 48
Geschwindigkeit von Prozeduren messen 49
Vermeiden von Select und Activate 51
Zugriffe auf Objekte 52
Auf die Excel-Applikation zugreifen 53
Auf Arbeitsmappen zugreifen 53
Auf Tabellenblätter zugreifen 54
Auf Zeilen und Spalten zugreifen 55
Auf Zellen und Bereiche zugreifen 56
Tipps und Tricks zum Ansprechen von Objekten 57
Prozeduren aus Prozeduren aufrufen (Call) 60
Eine Unterprozedur aufrufen 60
Ein rekursiver Prozeduraufruf 60
Parameterangaben bei Prozedurnamen 61
Datum und Uhrzeit verwenden 63
Datums- und Zeitdifferenz ermitteln 67
Eine Nachricht drei Sekunden lang anzeigen lassen 67
Eine Zeitgesteuerte Prozedur 68
Einen Jahreskalender erstellen 69
Arrays 71
Eindimensionale Arrays 72
Mehrdimensionale Arrays 74
LBound und UBound 77
Dynamische Arrays (ReDim und Preserve) 77
Ein dynamisches Array in der Praxis 78


3 Eigene Formeln programmieren 83

Englische Funktionsnamen per Makrorekorder ermitteln 84
Eigene Funktionen programmieren 84
VBA-Funktionen in einer Tabelle verwenden 85
VBA-Funktionen einer anderen Kategorie zuweisen 87
Eine Funktion ohne Übergabewerte 88
Neuberechnung (Application Volatile) 89
Eine Funktion mit mehreren Übergabewerten 89
Bereichsfunktionen 90
Array-Funktionen 90
Unbestimmte Anzahl an Übergabewerten (Parameter-Array) 91
Eine Funktion in einer Prozedur verwenden 92
Eine Tastenkombination zuweisen 94
Funktionen generell zu Verfügung stellen 95
PERSONLXLS 96
Funktionen in Add-Ins bereitstellen 96
Einige hilfreiche Funktionen 98
Englische und deutsche Formelnamen ermitteln 98
Eine erweiterte Wenn-Funktion 99
Farbige Zellen berechnen 100
Benutzernamen ermitteln 102


4 Wo ereignet sich was? - Ereignisprozeduren 105

Ereignisse deaktivieren 109
Welche Ereignisse gibt es? 109
Ereignisse in Mappen 110
Workbook_Activate und Workbook_Deactivate 111
Workbook_Open 112
Workbook_BeforeClose 113
Workbook_BeforeSave 114
Workbook_BeforePrint 115
Workbook_SheetCalculate 115
Ereignisse in Tabellenblättern 116
Worksheet_Activate und Worksheet_Deactivate 116
Worksheet_BeforeDoubleClick 117
Worksheet_BeforeRightClick 117
Worksheet_Change 118
Worksheet_SelectionChange 119
Steuerelemente (ActiveX) 120
Standardsteuerelemente 121
Befehlsschaltfläche (CommandButton) 122
Umschaltfläche (ToggleButton) 123
Bezeichnungsfeld (Label) 125
Textfeld (TextBox) 126
Optionsfeld (OptionButton) 127
Kontrollkästchen (CheckBox) 128
Listenfeld (ListBox) 129
Kombinationsfeld (ComboBox) 131
Drehfeld (SpinButton) 131
Bildlaufleiste (ScrollBar) 132
Bild (Image) 133
Zusätzliche Steuerelemente 134
Steuerelemente aus einem Tabellenblatt entfernen 137


5 Zellen, Tabellen und Mappen 139

Zellen und Bereiche 139
Kopieren von Zellen oder Bereichen 139
Zeilen und Spalten ein- und ausblenden 141
Welche Spalten oder Zeilen sind ausgeblendet? 142
Leere Zeilen ausblenden 143
Leere Zeilen oder Spalten löschen 144
Zellen, die Formeln enthalten, hervorheben 145
Mit Farben arbeiten 146
Arbeiten mit Kommentaren 150
Tastenkombinationen programmieren 159
Transponieren 161
Mehrere Bereiche auf einmal transponieren 163
Tabellen 164
Tabellenblätter löschen 164
Tabellenblätter umbenennen 166
Tabellenblätter sortieren 167
Schutz von Tabellen, Bereichen und Mappen 168
Verknüpfungen löschen und durch Werte ersetzen 170
Mappen 172
Mappen öffnen 172
Eine neue Mappe erzeugen 173
Mappen ansprechen 173
Mappe schließen 174
Kopf- und Fußzeilen 175
Eigenschaften auslesen und erstellen 178
Benannte Bereiche 181


6 Daten auswerten 185

Daten vergleichen 185
Werte vergleichen und farblich hervorheben 185
Datenreihe mit Bereich vergleichen 187
Zwei Bereiche vergleichen 188
Tabellenblätter vergleichen 189
Daten sortieren 193
Vertikal sortieren 193
Horizontal sortieren 194
Blöcke sortieren 195
Formeln ermitteln 199
Gültigkeit 201
AutoFilter 204
Einen einfachen AutoFilter programmieren 206
AutoFilter mit Datum 208
AutoFilter ausblenden 209
Einen einzelnen Filterpfeil ausblenden 209
Leere und nichtleere Einträge filtern 210
Alle Datensätze einblenden 211
Spezialfilter 211
Doppelte Datensätze ausblenden 214
Ereignisorientiert filtern 214
Pivot-Tabellen 216
Eine Pivot-Tabelle per VBA erzeugen 219
Alle Pivot-Tabellen löschen 220
Pivot-Tabelle erstellen 220
Pivot-Tabellen automatisch aktualisieren 222
Eine formatierte Pivot-Tabelle erzeugen 222


7 Der Zugang zur Welt: das Internet 225

Hyperlinks 225
Interne und externe Hyperlinks 225
Hyperlinks aus Tabellenblattnamen erzeugen 227
Schaltfläche mit Hyperlink 231
Hyperlinks auf Zellen bestimmten Inhalts 232
Hyperlinks ersetzen 233
Hyperlinks entfernen 234
E-Mail-Adressen einfügen 235
E-Mail-Adressen entfernen 236
E-Mails 237
Eine E-Mail versenden 237
Eine E-Mail mit Anhang versenden 239
Eine E-Mail mit HTML-Tags 240
Einen Bereich versenden 242
Einen Bereich als Anhang versenden 243
Eine Serien-Mail versenden 245
Eine Mappe komprimiert versenden (WinZip) 247
Webabfragen 249
Eine Webabfrage per VBA erzeugen 250
Webabfragen aktualisieren 252
Webabfragen löschen 253
Eine Excel-Datei als Webseite abspeichern 254
Konvertierung nach HTML 256


8 Diagramme per VBA steuern 261

Das Diagramm-Objekt-Modell 262
Diagrammtypen 263
Ein Diagramm erzeugen 264
Bereinigen aufgezeichneter Diagrammcodes 265
Diagramme ausdrucken 266
Diagramme löschen 266
Diagrammelemente formatieren 267
Diagramm- und Zeichnungsfläche formatieren 267
Datenreihen formatieren 270
Markierer formatieren 272
3D-Diagramme 274
3D-Oberflächendiagramme 275
Diagrammbeschriftungen 276
Diagrammtitel und Achsentitel 276
Achsentext 277
Trendlinien 279
Negative Datenpunke hervorheben 280
Bedingte Formatierung von Datenpunkten 282
Schwebebalken ein-/ausblenden 284
Kombinationsdiagramme 285
Halbtransparente Datenpunkte 288
Diagramm-Ereignisse 291
Ereignisse für Diagrammblätter 291
Ereignisse für eingebettete Diagramme 293
Ein Diagramm exportieren (Grafikformat) 294


9 Kreativität in Excel - Grafische Objekte 297

Objekte korrekt ansprechen 297
Welche Objekttypen gibt es? 297
Objekttyp ermitteln 298
Alle Objekte löschen 300
Objekte eines bestimmten Typs löschen 300
Ein Objekt über einem Bereich einfügen 300
Ein Objekt zentrieren 301
Zeichenelemente 303
AutoFormen 305
WordArt-Objekte 308
ClipArts 312
Grafiken 315
Grafiken einfügen 315
Grafiken der Größe eines Bereiches anpassen 316
Grafiken löschen 317
Grafiken bedingt einfügen 318
Grafiken formatieren 319
Bereiche einer Tabelle als Grafik abspeichern 321
Einen Bereich als Grafik exportieren 322
Verknüpfte Bilder 324
Die Sprechblase des Hilfe-Assistenten 326
Einen eigenen Text in der Sprechblase anzeigen 328
Textformatierungen 329
Symbole und Schaltflächen 330
Grafiken 332
Aufzählungspunkte und Nummerierungen 333
Kontrollkästen 334
Hyperlinks 335


10 Menü- und Symbolleisten programmieren 339

Menüleiste 341
Ein Menüelement anfügen/löschen 341
Die Menüleiste zurücksetzen 343
Einen neuen Menübefehl erstellen 344
Menüelemente mit weiteren Verzweigungen 346
Eigene Menübefehle nur in bestimmten Mappen anzeigen 347
Einen bestehenden Menübefehl ausblenden 348
Menüelemente mit Symbolen 350
Menübefehle mit Umschaltflächen 351
Menübefehl mit Hyperlinks 352
Menübefehle über Tastenkombinationen aufrufen 353
Symbolleisten 354
Eine integrierte Symbolleiste ergänzen 355
Symbolleisten zurücksetzen 356
Eine eigene Symbolleiste erzeugen 357
Eine eigene Symbolleiste löschen 360
Welche Symbole gibt es? 360
Eigene Symbole erzeugen 362
Symbole ersetzen 363
Symbolleisten positionieren 364
Symbolleisten mit Text 365
Kontextmenüs 366
Kontextmenüs ergänzen und zurücksetzen 367
Kontextmenübefehle ausblenden 368
Ein Kontextmenü ersetzen 369
Ein verändertes Kontextmenü für einen bestimmten Bereich 370


11 Formulare entwerfen (UserForm) 371

Ein UserForm erstellen 372
Farbpaletten 372
Ein UserForm aufrufen und schließen 373
Die Werkzeugsammlung 374
Ausrichtung von Steuerelementen 375
Benennung von Steuerelementen und Präfixen 376
Aktivierreihenfolge 377
Rahmen (Frame) 378
Multiseiten (MultiPage) 380
Register (TabStrip) 383
Inaktive und unsichtbare Steuerelemente 386
RefEdit (RefEdit) 387
Listenfelder mit Mehrfachauswahl 388
Mehrspaltige Listenfelder 391
Größe und Position von UserForms 392
Weitere Steuerelemente 395
UserForm mit Hyperlink 395
Kalender 398
Fortschrittsbalken 399
Ein UserForm mit Symbolleiste 401


12 XML, XSL(T) und Smarttags 405

XML 405
Wann wird XML eingesetzt? 406
Eine Excel-Datei im * xml-Format abspeichern 406
Eine XML-Datei manuell erzeugen 408
Listen 411
Listen importieren 412
Listen exportieren 412
XML-Quelle 413
Exportmechanismus per VBA programmieren 414
XSL(T) - Externe Formatvorlage 418
XML-SS-Schemas - Integrierte Formatierungen 422
Smarttags 424
Eine eigene Smarttag-Liste erzeugen 425
Smarttags löschen 429


13 Zugriff auf externe Applikationen 431

Word 433
Daten nach Word exportieren 434
Word-Daten importieren 435
Ein Word-Dokument mit Formatierungen 436
PowerPoint 437
Daten nach PowerPoint exportieren 438
PowerPoint-Daten importieren 439
Access 441
Daten nach Access exportieren 441
Daten aus Access importieren 443
Outlook 445
Den Posteingang auslesen 446
Kalender auslesen 448
Kontakte auslesen 449
Der Windows-Explorer (FSO) 450
Windows-Befehle ohne FSO 450
Textdateien 451
Daten nach Textdatei exportieren 451
Daten aus einer Textdatei importieren 453


14 Visual Studio-Tools für Office 459

Was ist Visual Studio-Tools für Office? 459
Die Installationsreihenfolge 460
Die ersten Schritte 462
Hallo Welt 464
Standard-Ereignisse 465
Wo befindet sich der Code? 468
Bereitstellung von Office-Lösungen 469
Den Speicherort der Excel-Datei ändern 469
Sicherheit und Berechtigungen 471
Beispiele 473
Bestehende Schaltflächen verwenden 473
Schaltflächen erstellen 476
Arbeiten mit Tabellenblättern 478
Arbeiten mit Bereichen 479
Ein Diagramm erstellen 480
Mit Fehlern umgehen 482
Die Menüleiste ergänzen 483
Eine eigene Symbolleiste erstellen 485


Anhang A Wichtige Präfixe, Funktionen und Konstanten 489

Präfixe 489
Präfixe zu Modulen 489
Präfixe zu Datentypen 490
Präfixe zu Elementen der Steuerelement-Toolbox 490
Präfixe zu UserForm-Objekten 491
Datentypen prüfen 491
Umwandlungsfunktionen 492
Datum und Zeit 492
Weekday 492
Format-Funktion 493
DateDiff 493
Funktionskategorien 494
SpecialCells 494
Farbkonstanten 495
Tastenkombinationen (OnKey) 495
Operatoren und Kriterien für Autofilter 496
Gültigkeit 497
Objekttypen (Shapes) 498


Anhang B Die CD-ROM zum Buch 501


Stichwortverzeichnis 505