Das Excel-VBA Codebook

Monika Weber, Melanie Breden

Das Excel-VBA Codebook eBook Die nicht autorisierte Weitergabe dieses eBooks an Dritte ist eine Verletzung des Urheberrechts!

An imprint of Pearson Education München • Boston • San Francisco • Harlow, England Don Mills, Ontario • Sydney • Mexico City Madrid • Amsterdam

Bibliografische Information Der Deutschen Bibliothek Die Deutsche Bibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über abrufbar.

Die Informationen in diesem Produkt werden ohne Rücksicht auf einen eventuellen Patentschutz veröffentlicht. Warennamen werden ohne Gewährleistung der freien Verwendbarkeit benutzt. Bei der Zusammenstellung von Texten und Abbildungen wurde mit größter Sorgfalt vorgegangen. Trotzdem können Fehler nicht vollständig ausgeschlossen werden. Verlag, Herausgeber und Autoren können für fehlerhafte Angaben und deren Folgen weder eine juristische Verantwortung noch irgendeine Haftung übernehmen. Für Verbesserungsvorschläge und Hinweise auf Fehler sind Verlag und Herausgeber dankbar. Alle Rechte vorbehalten, auch die der fotomechanischen Wiedergabe und der Speicherung in elektronischen Medien. Die gewerbliche Nutzung der in diesem Produkt gezeigten Modelle und Arbeiten ist nicht zulässig. Falls alle Hardware- und Softwarebezeichnungen, die in diesem Buch erwähnt werden, sind gleichzeitig auch eingetragene Warenzeichen oder sollten als solche betrachtet werden. Umwelthinweis: Dieses Buch wurde auf chlorfrei gebleichtem Papier gedruckt. Die Einschrumpffolie – zum Schutz vor Verschmutzung – ist aus umweltverträglichem und recyclingfähigem PE-Material.

10 9 8 7 6 5 4 3 2 1 06 05 04 ISBN 3-8273-2101-8 © 2004 by Addison-Wesley Verlag, ein Imprint der Pearson Education Deutschland GmbH Martin-Kollar-Straße 10–12, D-81829 München/Germany Alle Rechte vorbehalten Korrektorat: G & U Technische Dokumentation GmbH, Flensburg Lektorat: Frank Eller, [email protected] Herstellung: Monika Weiher, [email protected] Satz: reemers publishing services gmbh, Krefeld – gesetzt aus der Minion Umschlaggestaltung: Marco Lindenbeck, [email protected] Druck und Verarbeitung: Kösel, Kempten (www.Koeselbuch.de) Printed in Germany

Inhaltsverzeichnis Teil I Einführung

17

Vorwort

19

Einführung 1 Hinweise zum Buch 2 Die Geschichte von Excel

21 21 22

Teil II Rezepte

27

Grundlagen 1 Sicherheitseinstellungen in Excel 2 Zellen selektieren oder nicht? 3 Tabellenblätter selektieren oder nicht? 4 Codezeilen kommentieren 5 Codezeilen umbrechen 6 Die letzte (nicht) belegte Zelle ermitteln 7 Löschen von Zellen, Zellinhalt, Spalten und Zeilen 8 Ausschneiden, kopieren und einfügen von Zellinhalt 9 Löschen von Tabellenblättern 10 Einfügen von Tabellenblättern 11 Arbeiten mit Kopf- und Fußzeilen 12 Kopf- und Fußzeilen mit Bildern 13 Kopf- und Fußzeilen auf jedem Tabellenblatt 14 Eine Farbtabelle erstellen 15 RGB-Farben 16 Kommentare einfügen 17 Kommentare ergänzen 18 Kommentare auslesen 19 Kommentare formatieren 20 Kommentare löschen 21 Verfügbare Systeminformationen auslesen (Environ) 22 Eigenschaften auslesen 23 Eigenschaften ausfüllen bzw. erstellen 24 Funktionen 25 Excel-Tabellenfunktionen in Visual Basic verwenden 26 VBA-Funktionen 27 Benutzerdefinierte Tabellenfunktionen 28 Funktion einer Kategorie zuweisen 29 Beschreibung einer Funktion hinzufügen 30 Pfad, Dateiname und Blattname ermitteln 31 Flüchtige Tabellenblattfunktionen 32 Alle Blattnamen einer Arbeitsmappe ermitteln 33 Existiert ein bestimmtes Tabellenblatt? 34 Ist eine Arbeitsmappe geöffnet?

29 29 30 33 34 35 35 37 38 38 39 40 41 42 43 44 46 48 49 51 52 53 54 56 57 58 60 61 62 65 67 69 70 71 72

6

35 36 37 38

Inhaltsverzeichnis

Aufrufende Formelzelle ermitteln Fehlerwerte in Funktionen Spaltenbuchstabe ermitteln Funktion Seitenzahl

Allgemein 39 Formeln mit Zirkelbezügen berechnen 40 VBA-Addition mit eigenem Zellenwert 41 Zellenwert kumulieren 42 Welche Zellen sind gesperrt? 43 Gesperrte Zellen einfärben 44 Formelzellen schützen 45 Autofilter trotz Blattschutz 46 Gliederung trotz Blattschutz 47 Sortierung trotz Blattschutz 48 Schutzoptionen auslesen 49 Schutzoptionen einstellen 50 Nicht gesperrte Zellen markieren und formatieren 51 Nicht gesperrte Zellen formatieren (ab Excel 2002) 52 Nicht gesperrte Zellen formatieren (alle Excel-Versionen) 53 Schutzoptionen ändern 54 Benutzerberechtigte Bereiche hinzufügen 55 Benutzerberechtigte Bereiche löschen 56 Schutz der benutzerberechtigten Bereiche aufheben 57 Benutzerberechtigte Bereiche schützen 58 Benutzerberechtigte Bereiche – Passwort ändern 59 Benutzerberechtigte Bereiche – Titel ändern 60 Benutzerberechtigte Bereiche – Bereich ändern 61 Ausgewählte Bereiche freigeben 62 Löschen von Blättern verhindern 63 Schreibgeschützte Datei ohne Speichern-Rückfrage schließen 64 Entfernen des Schreibschutzes ignorieren 65 Benutzerdefinierte Ansichten schützen 66 Informationen des Betriebssystems auslesen 67 Zwei Benutzernamen auslesen Datum/Zeit 68 Datum und Zeitformat anpassen 69 Wochentag des Geburtstages ermitteln 70 Anzeigen des Systemdatums 71 Schaltjahre bestimmen 72 Dateizugriffe dokumentieren 73 Bestandveränderungen dokumentieren 74 Vereinfachte Datumseingabe 75 Uhrzeiten mit Autokorrektur 76 Uhrzeiten ohne Doppelpunkt eingeben 77 Aktuelles Datum finden 78 Beliebiges Datum finden 79 Arbeitsstunden in Meldungsfenster anzeigen 80 Arbeitszeiten summieren 81 Kalenderwoche nach DIN 1355 berechnen

73 73 76 77 81 81 83 84 86 88 90 91 92 92 94 98 99 100 101 101 104 105 106 107 108 108 111 113 117 119 120 121 125 127 129 129 133 133 134 135 137 139 140 141 144 144 146 147 149

Inhaltsverzeichnis

82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106

Anzahl Kalenderwochen bestimmen Feiertage mit Formeln berechnen Formeln in Tabelle anzeigen Funktion zur Formelanzeige Feiertage mit VBA berechnen Tageskalender erstellen 100-jähriger Kalender und Arbeitszeittabelle Lohn berechnen Minuszeiten berechnen Negative Zeitberechnung mit Funktion 1904-Datumswerte für Minuszeiten Anzeige des eingestellten Datumssystems in Statuszeile Eingabe von Minuszeiten 1904-Datumswerte mit VBA kopieren Datumswerte aus anderem Datumssystem importieren Datums-Funktionen bei 1904-Datumswerten Berechnung von Datumsdifferenzen Zeitdifferenzen mit VBA DateDiff VBA-Ersatz-Prozedur für die DateDif-Tabellenfunktion Funktion, um Lebensalter zu berechnen Stoppuhr mit Excel Excel kann warten Signaltöne erzeugen Töne bei aktuellem Datum ausgeben Meldung zeitbegrenzt anzeigen

Auswertungen 107 Bezüge archivieren 108 Bezug-Fehler reparieren 109 Tabellenblätter vergleichen (Fehlerausgabe in einem separaten Tabellenblatt) 110 Tabellenblätter vergleichen (Fehlerausgabe in einer separaten Mappe) 111 Formeln in einem Tabellenblatt ermitteln 112 Einfacher Autofilter 113 Autofiltern nach Datum 114 Autofilter deaktivieren 115 Spezialfilter mit mehr als zwei Kriterien 116 Doppelte Einträge ausblenden 117 Spezialfilter: Alle Datensätze wieder einblenden 118 Spezialfilter als Ereignisprozedur 119 Gültigkeit 120 Gültigkeit auf ein anderes Tabellenblatt 121 Gültigkeit als Ereignis-Prozedur 122 Eine Pivot-Tabelle erzeugen 123 Zwei Pivot-Tabellen auf einem Blatt erzeugen 124 Eine bestehende Pivot-Tabelle formatieren 125 Datenpunkte ausblenden 126 Pivot-Tabellen berechnen 127 Sortieren nach Farben 128 Sortieren nach selbst festgelegter Farbfolge 129 Tabellenblätter alphabetisch sortieren 130 Tabellenblätter nach Codenamen sortieren

7

151 152 152 154 155 156 160 163 164 165 166 168 170 170 172 174 175 177 179 182 183 186 186 187 188 191 191 194 196 200 204 206 209 211 211 213 214 214 216 218 219 221 225 227 228 230 231 234 236 239

8

131 132

Inhaltsverzeichnis

Tabellenblätter numerisch sortieren Tabellenblätter nach Farben sortieren

241 242

Steuerelemente 133 Steuerelemente der Formular-Symbolleiste 134 Formular-Schaltfläche erstellen 135 Formular-Schaltfläche löschen 136 Makro an Formular-Schaltfläche zuweisen 137 Steuerelement-Toolbox 138 Steuerelemente erstellen 139 Steuerelemente löschen 140 Alle Steuerelemente löschen 141 Steuerelemente eines Typs löschen 142 Fokus der Steuerelemente verwalten 143 Wert in Kontrollkästchen (CheckBox) ändern 144 Kontrollkästchen (CheckBox)-Wert auslesen 145 Wahrheitswert eines Kontrollkästchens übertragen 146 Steuerelement-Eigenschaften ändern 147 Mit Umschaltfläche Steuerelement steuern 148 Gruppen von Steuerelementen erstellen 149 Optionsfelder auswerten 150 Optionsfelder und Kontrollkästchen zurücksetzen 151 Arbeitsblätter über Listenfeld auswählen 152 Listenfeld durch Mausbewegung füllen 153 Monate in Listenfeld auflisten 154 Listenfeldeinträge bei Auswahl ändern 155 Mehrspaltiges Listenfeld füllen 156 Kombinationsfeld nach Auswahl füllen 157 Passworteingabe durch Sternchen ersetzen 158 Textfeld-Werte umwandeln 159 Zeilenumbruch in Textfeld (TextBox) per Code einfügen 160 Mit Drehfeld (SpinButton) Monatswerte verändern 161 Mit Bildlaufleiste (ScrollBar) zu Datum wechseln 162 Steuerelemente mit Tastatur aktivieren 163 Tabellenblatt-Zugriffe mit Steuerelementen verwalten

245 246 248 249 250 250 254 256 256 257 257 258 260 261 261 262 264 266 267 271 272 273 274 277 279 281 283 284 285 287 290 292

Befehlsleisten 164 Befehlsleistendatei finden 165 Befehlsleistendatei sichern 166 Symbolleistenkonfigurationen speichern 167 Befehlsleistendatei laden 168 Originaldatei *.xlb laden 169 Befehlsleisten ermitteln 170 Menüs und Steuerelemente ermitteln 171 Menü-Auflistung im Direktfenster 172 Ganzes Menü deaktivieren oder aktivieren 173 Ganzes Menü über ID aus- und einblenden 174 Untermenü-Auflistung im Direktfenster 175 Untermenüs deaktivieren und aktivieren 176 Untermenü des Untermenüs deaktivieren und aktivieren 177 Befehlsleisten zurücksetzen

301 302 303 305 307 308 309 311 312 313 314 315 317 317 318

Inhaltsverzeichnis

178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214

Menüleisten ein- und ausblenden Symbolleisten ein- und ausblenden Kontextmenüs einblenden Zelle-Kontextmenü deaktivieren Alle Befehlsleisten ein- und ausblenden Einzelne Symbolleistentypen ein- und ausblenden Einzelne Symbolleisten vor Änderungen schützen Befehlsleisten vor Änderungen schützen in XL97 und XL2000 Befehlsleisten-Schutz aufheben Befehlsleisten-Schutz wechseln Befehlsleisten vor Änderungen schützen ab XL2002 Fehlerroutinen vor Erstellung von Befehlsleisten Löschen von Befehlsleisten Erstellen einer Menüleiste Wechseln und Löschen von Menüleisten Symbolleisten erstellen und verankern Symbolleisten schwebend positionieren Symbolleiste neben Standard-Symbolleiste andocken Kontextmenü mit Datum und Zeit erstellen Neues Farben-Menü erzeugen Menü suchen und aufklappen Arbeiten mit personalisierten Menüs Steuerelemente kopieren Integrierte Steuerelemente in Symbolleiste einfügen Schaltflächen-ID ermitteln Schaltflächen mit Quickinfo erstellen Symbolleiste mit zwei Ebenen erstellen Steuerelemente mit ID erstellen und anzeigen Schaltflächen über ID löschen Menüpunkte mit Hyperlinks Schaltfläche einem Menü hinzufügen Tabellenreiter über Dropdownfeld färben Zellenschutz im Kontextmenü anzeigen und ändern Kontextmenü durch Kaskaden-Menü ersetzen Welches Kontextmenü wurde aufgerufen? Symbole über Kontextmenü einfügen Alle FaceIDs auf einen Blick

Objekte 215 Einen Bereich in eine Grafik umwandeln 216 Einen Bereich als Grafik exportieren 217 Eine verknüpfte Grafik 218 Eine Textbox, die den Cursor verfolgt 219 Eine Grafik in eine Zelle einfügen 220 Bei Klick auf Zelle A1 eine Grafik in Zelle B1 einfügen 221 Eine Grafik einfügen und der Zellengröße anpassen 222 Eine Grafik exakt in einen Bereich einfügen 223 Eine Grafik in einer Zelle zentrieren 224 Grafik(en) benennen 225 Grafik(en) kopieren 226 Grafik(en) löschen

9

320 321 323 324 325 326 328 329 331 332 332 334 335 335 337 338 339 340 341 342 346 347 349 351 352 353 354 356 357 358 361 363 370 373 376 377 381 383 383 385 388 389 390 392 393 394 396 397 398 399

10

227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252

Inhaltsverzeichnis

Grafik formatieren Bedingtes Einfügen einer Grafik Eine animierte Grafik einbinden Ein WordArt-Objekt erzeugen Ein WordArt-Objekt formatieren AutoFormen einfügen AutoFormen formatieren Objekte lokalisieren Ein ClipArt in der Farbe verändern Eine schematische Darstellung oder ein Organigramm erzeugen Diagrammknoten löschen Objekte löschen Nur Objekte von einem bestimmten Typ löschen Objekt-Typ ermitteln Nur bestimmte Objekte gruppieren (Array) Der Office-Assistent Einen eigenen Titel und Text in der Sprechblase ausgeben Den Pfad des Assistenten ausgeben Schaltflächen in einer Sprechblase Eine Sprechblase mit Aufzählungspunkten Eine Sprechblase mit Nummerierung Eine Sprechblase mit Kontrollkästchen Den Text in einer Sprechblase formatieren Grafiken in einer Sprechblase anzeigen Eine Sprechblase mit Hyperlinks Den Assistenten animieren

Diagramme 253 Diagrammtypen und -index 254 Hintergrund Diagramm- und Zeichnungsfläche 255 Wände und Böden von 3-D-Diagrammen 256 Diagrammtitel hinzufügen 257 Datenreihen formatieren 258 Datenpunkte formatieren 259 Markierer und Linien formatieren 260 Trendlinien hinzufügen 261 Trendlinien entfernen 262 Trendlinien-Typen 263 Gleichungen und Bestimmtheitsmaß ein- und ausblenden 264 Trendperiode vor- oder rückwärts 265 Achsen formatieren 266 Achseneinstellungen speichern 267 Achseneinstellungen rückgängig machen 268 Primär- oder Sekundärachse 269 Datentabelle formatieren 270 Legende verwalten 271 3-D-Oberflächen-Diagramm rotieren lassen 272 Ein benutzerdefiniertes Diagramm erstellen 273 Einen benutzerdefinierten Diagrammtypen löschen 274 Einen benutzerdefinierten Diagrammtypen anwenden 275 Pivot-Diagramm

400 403 404 406 407 410 411 413 413 416 420 421 422 423 424 426 428 429 431 432 434 435 437 438 439 441 445 447 452 455 457 460 462 462 463 466 467 469 470 473 477 479 481 482 484 486 488 490 491 491

Inhaltsverzeichnis

276 277 278 279 280 281 282 283 284 285 286 287 288

Alle Diagramme drucken und löschen Diagramme drucken Diagramm-Säulen mit Grafiken Diagramm als Grafik exportieren Objektnamen in Diagrammen ermitteln Zellfarben an Datenreihe übergeben Zellfarben an Datenpunkte übergeben Minuswerte invertieren Diagramm mit bedingter Formatierung (Säulenfarbe) Diagramm mit bedingter Formatierung (Werte) Halbtransparente Diagramme Verbund-Diagramm Blasendiagramm-Blume

Ereignisse 289 Allgemeine Prozeduren 290 Ereignis-Prozeduren 291 Workbook-Ereignisse 292 Aktuelles Monatsblatt anzeigen 293 Ausschneiden und Löschen verhindern 294 Ereignisse ignorieren 295 Ereignisse aus- und einschalten 296 Excel-Datei ohne Makros unbrauchbar 297 Datei nur mit Makrounterstützung anzeigen 298 Registerreiter neu nummerieren 299 Neues Blatt beschriften 300 Diagramm nach Ansicht löschen 301 Tabellenblatt-Ereignisse (Worksheet) 302 Zellenkontextmenü verhindern 303 Auf den Spuren von Target 304 Bereichsauswahl verhindern 305 Cursorbewegungen protokollieren 306 Lupe vergrößert markierten Zellenbereich 307 Cursor in eine Eieruhr verwandeln 308 Formeln in Kommentaren anzeigen 309 Bei Blattwechsel Monatsspalten anzeigen 310 Blattschutz ohne Blattschutz 311 Ereignisbedingte Formatierung 312 Ereignisbedingte Formatierung durch Formelbezüge 313 Makro aus Hyperlink aufrufen 314 Adresse zu Hyperlink ermitteln 315 Autoprozeduren 316 Datei öffnen und Auto_Open-Prozedur aufrufen 317 Datei schließen und Auto_Close-Prozedur aufrufen 318 Begrüßung nach der Tageszeit 319 Zoomfaktor über Bildschirmauflösung 320 Zoomfaktor mit Workbook_Open 321 Zeitgesteuerte Makros 322 Uhrzeit in Statuszeile zeitbegrenzt anzeigen 323 Uhrzeit in Statuszeile steuern 324 Uhrzeitmeldung

11

493 495 496 497 499 499 501 502 503 505 507 510 513 519 519 520 521 525 527 530 531 532 540 542 544 548 549 551 552 554 554 555 556 557 558 559 561 563 565 567 568 569 569 570 571 572 574 575 578 579

12

325 326 327 328

Inhaltsverzeichnis

Tastenkombinationen erstellen Aufgabenbereich ein-/ausblenden Navigation mit Fadenkreuz Makro rückgängig machen

579 583 585 588

UserForm 329 Bezeichnungsfelder (Label) 330 Textfelder (TextBox) 331 Schaltflächen (CommandButton) 332 Rahmen (Frame) 333 Listenfelder (ListBox) 334 Mehrspaltige Listenfelder 335 Kombinationsfelder (ComboBox) 336 Werte von Kombinationsfeld nach Listenfeld übertragen 337 Optionsfelder (OptionButton) 338 Kontrollkästchen (CheckBox) 339 Umschaltfelder (ToggleButton) 340 Register (TabStrip) 341 Multiseiten (Multipage) 342 Bildlaufleisten (ScrollBar) 343 Drehfelder (SpinButton) 344 Anzeige (Image) 345 RefEdit 346 Position und Größe eines UserForm 347 UserForm dynamisch in der Größe verändern 348 UserForm aus UserForm aufrufen 349 Landkarte mit Quickinfo 350 Passworteingabe 351 Automatisches Ausrichten von Elementen 352 Tabellenblatt in UserForm einbinden 353 Diagramm in UserForm 354 Diagramm in UserForm mit Daten aus Tabelle 355 Mappenbaum in UserForm (TreeView) 356 Animierte Grafik in UserForm 357 Hyperlink in UserForm 358 Eine eigene Werkzeugsammlung zusammenstellen

591 594 594 597 599 600 603 604 607 608 610 612 613 614 618 620 622 624 626 627 628 631 633 634 636 643 645 647 649 650 651

Web/Mail 359 Eine einfache E-Mail versenden 360 Eine E-Mail mit HTML-Formatierungen 361 Ein Tabellenblatt als E-Mail versenden 362 Zellbereich per E-Mail versenden 363 Serien-Mail mit einheitlichem Inhalt 364 Serien-Mail mit unterschiedlichem Inhalt 365 Interne Hyperlinks 366 Hyperlink mit Quickinfo 367 Mittels Hyperlinks ein Inhaltsverzeichnis erstellen 368 Hyperlinks und Zelladressen (Suchen/Weitersuchen) 369 Ein neues Dokument erstellen 370 Externe Hyperlinks 371 Hyperlink auf lokale Datei

653 653 656 657 659 660 661 663 664 665 667 670 671 672

Inhaltsverzeichnis

372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393

Hyperlinks zu einer Textmarke Hyperlinks zu Zellen/Tabellen einer anderen Mappe E-Mail-Adressen Eine Schaltfläche mit Hyperlink URL in Nebenzelle ausgeben Hyperlinks löschen Nur E-Mail-Links löschen E-Mail-Links nicht löschen Den HTML-Code einer Excel-Datei ansehen Eine Webabfrage erstellen Webabfragen aktualisieren Eine Webabfrage bereinigen Webabfrage aktualisieren und bereinigen (kombiniert) Webabfragen löschen Einen Webbrowser einbinden Eine XML-Datei erzeugen Import und Export von XML-Dateien XML-SS-Schemas XSL(T) Stylesheets für XML Smart Tags Ein eigenes Smart-Tag-Menü erstellen (XML) Smart Tags löschen

Extern 394 Dateien löschen 395 Dateien umbenennen 396 Mit ActiveX ein Objekt erzeugen 397 Dateien verschieben 398 Dateien kopieren 399 Datei-Eigenschaften auslesen 400 Dateiattribute setzen 401 Datei- und Verzeichnisnamen auslesen 402 Dateien suchen 403 Dateien alphabethisch sortiert ausgeben 404 Dateien aus Unterverzeichnissen auslesen 405 Verzeichnis erstellen 406 Verzeichnis löschen 407 Verzeichnis wechseln 408 Größe eines Verzeichnisses ermitteln 409 Laufwerk oder Ordner? 410 Laufwerkbuchstaben ermitteln 411 Laufwerk-Eigenschaften 412 System-Informationen auslesen (Environ) 413 Das File System Object (FSO) 414 Eine Textdatei erzeugen 415 Eine Textdatei ergänzen 416 Eine Textdatei auslesen 417 Import aus Textdatei 418 Export nach Textdatei 419 Import aus Word 420 Export von Excel nach Word

13

672 673 674 675 676 676 678 678 679 681 684 685 686 687 688 689 692 696 698 702 703 706 709 710 711 714 715 717 718 721 723 724 725 726 727 728 728 728 730 731 733 733 734 737 739 740 743 748 751 754

14

421 422 423 424 425 426 427 428 429 430 431 432 433 434

Inhaltsverzeichnis

Export von Zwischenablage nach Word Import aus PowerPoint Export nach PowerPoint Outlook-Kontakte auslesen Outlook-Kalender auslesen Outlook-Posteingang auslesen Outlook-Aufgaben auslesen Outlook-Aufgaben-Fenster anzeigen Outlook-Notiz erstellen Access: Ein Query erstellen Import aus einer Access-Tabelle Export in eine Access-Tabelle Export nach Zwischenablage Import aus Zwischenablage

Gemischtes 435 VBE – Entwicklungsumgebung 436 Verweise auslesen 437 Verweise aktivieren 438 Verweise deaktivieren 439 VBA-Projekteigenschaften auslesen 440 Projektname auslesen und ändern 441 Ist Projekt-Ansicht gesperrt? 442 Mit VBA Projektschutz aufheben 443 Entwicklungsumgebung ein- und ausblenden 444 Codemodul-Fenster auflisten 445 Codemodul-Fenster anzeigen 446 Text in Direktbereich löschen 447 Alle Projekte auslesen 448 Module und Prozeduren des aktiven VBA-Projektes auslesen 449 Code einer Prozedur auslesen 450 Blatt- und Codenamen einer Tabelle auslesen 451 Codenamen einer Tabelle ändern 452 Codenamen nach Tabellennamen benennen 453 Modul erstellen 454 Modul mit Namensvergabe erstellen 455 Modul löschen 456 Vorhandene Module überprüfen 457 Vorhandene Prozeduren überprüfen 458 Prozedur in ein Modul schreiben und aufrufen 459 Prozedur in eigenes Modul schreiben und aufrufen 460 Ereignis-Prozedur erstellen 461 Prozeduren löschen 462 Code eines Moduls löschen 463 Menü in VBE-Menüleiste erstellen 464 Ereignis-Prozeduren für integrierte Ereignisse erstellen 465 Meldungen über alle Zellpositions-Änderungen 466 Add-Ins 467 Add-Ins hinzufügen und installieren 468 Add-In von Diskette auf Festplatte kopieren 469 Add-Ins auflisten

757 758 760 762 765 767 768 769 770 771 776 779 781 783 785 785 786 788 789 790 791 792 792 794 795 796 797 798 800 802 803 805 808 808 809 810 811 812 813 814 814 816 817 818 822 827 829 831 833 834

Inhaltsverzeichnis

470 471 472 473 474 475 476

Add-Ins über Explorer installieren Add-In deinstallieren und aus Liste entfernen Titel und Beschreibung einem Add-In hinzufügen Code-Änderungen in Add-In speichern Funktionen in Add-In einbinden Makros aus Add-In aufrufen Makros aus Add-In über Schaltfläche aufrufen

15

835 837 839 840 841 843 843

Specials 477 Ein kleines Puzzle 478 Die Farbpalette von Excel verändern 479 Einen Lottoschein ausfüllen (Array) 480 Formeln, Werte und Text eines Tabellenblattes ermitteln 481 Sprachtrainer 482 Tabellenblatt-Übersicht in Menüleiste 483 Präsentation in Excel 484 Auto-Berechnung aus Statuszeile in Zwischenablage einfügen 485 Menüeinträge mit Tastenkombinationen aufrufen

849 849 853 857 861 864 868 874 876 880

Teil III Anhang

887

Anhang 1 Die englischen Funktionsnamen für die deutschen Tabellenblattfunktionen 2 VBA-Schlüsselwörter 3 MsgBox-Konstanten 4 Application.InputBox-Methode 5 Vergleichs-Operatoren 6 Arithmetische Operatoren 7 Verkettungs-Operatoren 8 SpecialCells-Methode 9 RGB-Funktion 10 ColorIndex-Eigenschaft 11 ColorFormat-Objekt (Fülleffekte) 12 FormatCondition-Objekt (Bedingte Formatierung) 13 Formatcodes für Kopf- und Fußzeilen 14 Datentypen 15 Typ-Umwandlungsfunktionen 16 Benutzerdefinierte Datums- und Zeitformate (Format-Funktion) 17 DateDiff-Funktion 18 OnKey-Methode 19 Attributes-Eigenschaft 20 Verschiedene Konstanten 21 ClipboardFormats-Eigenschaft 22 Spezifikationen und Einschränkungen von Excel 23 Liste der integrierten Dialogfeldargumente 24 Liste der integrierten Dialogfeldargumente für Diagramme 25 Liste der integrierten Dialogfeldargumente für Pivottabellen 26 Liste der integrierten Dialogfeldargumente für Solver 27 Liste der integrierten Dialogfeldargumente für Steuerelemente

889 889 892 897 898 899 900 902 903 904 905 905 906 907 909 910 911 913 914 916 916 917 918 922 930 933 934 934

Stichwortverzeichnis

937

TEIL I Einführung

Vorwort Seit Oktober 2003 ist der Nachfolger von Office XP, das Office System 2003, auf dem Markt erhältlich. Der Verlag Addison-Wesley hat deshalb beschlossen, dass es an der Zeit ist, die erste Auflage des Excel VBA Codebooks entsprechend anzupassen. Aufgrund eines Autorenwechsels war eine zweite Auflage im herkömmlichen Sinne nicht möglich und deshalb wurde das Buch von Grund auf neu geschrieben. Für alle Leser, die bereits im Besitz der ersten Auflage sind, bedeutet das, dass mit dem Erscheinen dieses Buchs quasi Band 2 vorliegt. Ein Blick in das neue Buch wird sich somit durchaus lohnen. Änderungen sind bereits am Aufbau der Kategorien zu erkennen, denn diese wurden teils komplett ersetzt. Sie werden erneut unzählige Codebeispiele und Highlights in diesem Buch finden, die Ihnen den Alltag in der Programmierung erleichtern werden. Sämtliche Beispiele in diesem Buch wurden auf die Excel-Versionen 2000, 2002 und 2003 abgestimmt. Nahezu alle hier enthaltenen Codes lassen sich problemlos in all diesen drei Versionen ausführen. Falls Versions-Unterschiede bestehen sollten, werden Sie an entsprechender Stelle darauf hingewiesen. Als Zielgruppe für dieses Buch wurde hauptsächlich der fortgeschrittene Anwender, bis hin zum Profi gewählt. Das Buch ist kein Lehrbuch, sondern eine Art Lexikon. Es soll Ihnen als Nachschlagewerk dienen. Sie finden hier eine sehr umfangreiche Auswahl an Code-Elementen, die sich nach belieben zusammenfügen lassen. Wir haben zudem Wert darauf gelegt, neben umfangreichen Codes auch kurze Beispiele zub erstellen, so dass es auch einem Einsteiger möglich ist, sich besser mit dieser Programmiersprache vertraut zu machen. Für die erfahrenen VBA-Anwender sind viele komplexe Beispiele enthalten. Da VBA durchaus nicht immer der einzige Weg ist, der zum Ziel führt, haben wir uns nach Möglichkeit darum bemüht, neben dem VBA-Code auch Lösungswege aufzuzeigen, die ohne Programmierung auskommen.

Danksagung von Monika Weber Es war für mich eine große Herausforderung, das Codebook komplett neu zu schreiben. Mein erster Dank gilt meiner neuen Buchpartnerin Melanie Breden, die ich für dieses Projekt gewinnen konnte. Sie hat sich als kompetente Partnerin beim Erstellen des Buches erwiesen. Ein weiteres Dankeschön möchte ich an unseren Lektor Frank Eller richten. Er hat mir mit diesem Werk bereits das dritte Projekt bei Addison-Wesley ermöglicht. Selbstverständlich gilt der Dank auch unserer Korrektorin Anja Schmitz, die dafür gesorgt hat, dass unsere Tippfehler aus den Buchseiten entfernt wurden. Meinen US-MVP Kollegen Bob Umlas und John Walkenbach möchte ich für die Inspiration und Unterstützung in zwei sehr interessanten Beispielen (Array und Diagramme) danken. Ein letztes und besonders herzliches Dankeschön geht an meine Liebsten und Familienangehörigen, die erneut das Verständnis dafür aufgebracht haben, dass meine Freizeit während der Dauer des Schreibens sehr eingeschränkt war. Monika Weber, Schweiz, http://www.jumper.ch Microsoft MVP für Excel

20

Vorwort

Danksagung von Melanie Breden Mein Dank geht an unseren Lektor Frank Eller und meine Buchpartnerin Monika Weber. Das große Vertrauen, welches durch dieses herausfordernde Projekt an mich übertragen wurde, hat mich sehr geehrt. Dieser Aufgabe habe ich mich mit Freude gestellt und auch beim Erarbeiten der Kategorien und Beispiele neue Möglichkeiten und Lösungen der großen Excel-Welt ergründen können. Ich bedanke mich vor allem bei meinem Mann Michael und meinen Kindern Juliane und Lukas. Sie haben sehr viel Verständnis für mich aufgebracht, wenn ich mal wieder sehr vertieft bei meiner Arbeit war und sie nicht die sonst gewohnte Aufmerksamkeit erhalten haben. Ein weiterer Dank geht an meinen Schweizer MVP Kollegen Thomas Ramel. Ich danke ihm herzlich für den innovativen Gedankenaustausch und für das Testen umfangreicherer Programmierbeispiele. Ein letzter Dank geht an die Benutzer der microsoft.public.de.excel-Newsgroup. Durch ihre Fragestellungen bin ich auf die alltäglichen Probleme mit VBA aufmerksam geworden. In der Hoffnung, auch Ihnen liebe Leser, das Leben mit VBA zu vereinfachen, sind einige meiner dort aufgezeigten Lösungswege in dieses Buch eingeflossen. Melanie Breden, Deutschland Microsoft MVP für Excel