24128.book Seite 1 Mittwoch, 7. Februar 2007 12:06 12 Excel-VBA in 14 Tagen 24128.book Seite 2 Mittwoch, 7. Februar 2007 12:06 12 Unser Online-Tipp für noch mehr Wissen ... ... aktuelles Fachwissen rund um die Uhr — zum Probelesen, Downloaden oder auch auf Papier. www.InformIT.de 24128.book Seite 3 Freitag, 9. Februar 2007 10:08 10 Excel-VBA AKTUELL ZU EXCEL 2007 BERND HELD 24128.book Seite 4 Freitag, 9. Februar 2007 10:08 10 Bibliografische Information Der Deutschen Bibliothek Die Deutsche Bibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über <http://dnb.ddb.de> 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. Fast alle Hardware- und Softwarebezeichnungen und weitere Stichworte und sonstige Angaben, die in diesem Buch verwendet werden, sind als eingetragene Marken geschützt. Da es nicht möglich ist, in allen Fällen zeitnah zu ermitteln, ob ein Markenschutz besteht, wird das ® -Symbol in diesem Buch nicht verwendet. Umwelthinweis: Dieses Buch wurde auf chlorfrei gebleichtem Papier gedruckt. 10 9 8 7 6 5 4 3 2 1 10 09 08 07 ISBN 978-3-8272-4128-3 © 2007 by Markt+Technik Verlag, ein Imprint der Pearson Education Deutschland GmbH, Martin-Kollar-Straße 10–12, D–81829 München/Germany Alle Rechte vorbehalten Lektorat: Brigitte Bauer-Schiewek, [email protected] Herstellung: Philipp Burkart, [email protected] Korrektur: Simone Meißner Satz: reemers publishing services gmbh, Krefeld, (www.reemers.de) Druck und Verarbeitung: Bercker, Kevelaer Printed in Germany 24128.book Seite 5 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis Inhaltsverzeichnis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Liebe Leserin, lieber Leser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Wochenvorschau . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Tag 1 Die Entwicklungsplattform, Variablen und Konstanten kennen lernen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.1 Der Projekt-Explorer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 1.2 Das Eigenschaften-Fenster . . . . . . . . . . . . . . . . . . . . . . . . . 24 Tabellen ein- und ausblenden . . . . . . . . . . . . . . . . . . . . . . 25 Tabellen spiegeln . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Den zu bearbeitenden Bereich festlegen. . . . . . . . . . . . . . 26 1.3 Das Code-Fenster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Makros erfassen und starten . . . . . . . . . . . . . . . . . . . . . . . . 29 1.4 Der Makrorekorder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Ein- und Ausschalten der Gitternetzlinien . . . . . . . . . . . . 33 Einheitliches Gestalten der Kopf- und Fußzeile. . . . . . . . 35 1.5 Der Objektkatalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Letztes Änderungsdatum einer Datei ermitteln. . . . . . . . . 42 Monatsnamen ermitteln. . . . . . . . . . . . . . . . . . . . . . . . . . . 43 1.6 Die Online-Hilfe. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Verwendete Befehle nachlesen . . . . . . . . . . . . . . . . . . . . . 44 Verfügbare Objekte ansehen . . . . . . . . . . . . . . . . . . . . . . . 45 1.7 Weitere praktische Helfer in der Entwicklungsumgebung 46 Die Symbolleiste Bearbeiten . . . . . . . . . . . . . . . . . . . . . . . 46 Suchen & Ersetzen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Schnelles Arbeiten über Tastenkombinationen. . . . . . . . . 54 1.8 Entwicklungsumgebung einstellen . . . . . . . . . . . . . . . . . . 56 Editoreinstellungen anpassen. . . . . . . . . . . . . . . . . . . . . . . 57 Editierformat festlegen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Allgemeine Einstellungen vornehmen. . . . . . . . . . . . . . . . 59 Fenster verankern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 5 24128.book Seite 6 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis 1.9 Datentypen, Variablen und Konstanten. . . . . . . . . . . . . . . 61 Was sind Variablen? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Variablen deklarieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Variablendeklaration erzwingen. . . . . . . . . . . . . . . . . . . . . 65 Vorsicht, Falle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Statische Variablen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Öffentliche Variablen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Private Variablen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Objektvariablen einsetzen . . . . . . . . . . . . . . . . . . . . . . . . . 70 Konstanten einsetzen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Konstanten für die Datumsfomatierung. . . . . . . . . . . . . . . 89 1.10 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 1.11 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 1.12 Übung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Tag 2 Die Sprachelemente von VBA. . . . . . . . . . . . . . . . . . . . . . . . . . 99 2.1 Arbeiten mit Verzweigungen. . . . . . . . . . . . . . . . . . . . . . . . 100 Verzweigungen im Leben. . . . . . . . . . . . . . . . . . . . . . . . . . 100 Verzweigungen in Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Verzweigungen in Excel-VBA . . . . . . . . . . . . . . . . . . . . . . 102 Zelleninhalte prüfen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Auch eine Art von Verzweigung. . . . . . . . . . . . . . . . . . . . . 106 2.2 Übersichtlichere Form mit Select Case. . . . . . . . . . . . . . . 108 Excel-Version feststellen. . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Zahlenwerte prüfen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Fensterstatus ermitteln . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 2.3 Schleifen programmieren. . . . . . . . . . . . . . . . . . . . . . . . . . 112 For...Next-Schleifen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 For Each...Next-Schleifen . . . . . . . . . . . . . . . . . . . . . . . . . 118 Die Schleife Do Until...Loop. . . . . . . . . . . . . . . . . . . . . . . 123 Die Schleife Do While...Loop . . . . . . . . . . . . . . . . . . . . . . 126 2.4 Auf Fehlersuche mit VBA. . . . . . . . . . . . . . . . . . . . . . . . . . 130 Fehler finden und beseitigen . . . . . . . . . . . . . . . . . . . . . . . 131 Typische Fehlerquellen in Excel-VBA. . . . . . . . . . . . . . . . 139 2.5 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 2.6 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 2.7 Übung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 6 24128.book Seite 7 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis Tag 3 Einstellen und Anpassen der Excel-Anwendung. . . . . . . . . . . . 147 3.1 Vollbildansicht einstellen . . . . . . . . . . . . . . . . . . . . . . . . . . 148 3.2 Weitere Einstellungen der Ansicht. . . . . . . . . . . . . . . . . . . 150 3.3 Lang laufende Makros schneller machen . . . . . . . . . . . . . 152 Bildschirmaktualisierung ein- und ausschalten . . . . . . . . . 153 Die Berechnung ein- und ausschalten. . . . . . . . . . . . . . . . 154 Statuszeile einsetzen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Standardmeldungen abschalten. . . . . . . . . . . . . . . . . . . . . 157 3.4 Automatische Listen generieren. . . . . . . . . . . . . . . . . . . . . 158 Benutzerdefinierte Liste aus Makro erzeugen . . . . . . . . . . 159 Benutzerdefinierte Liste aus Zelleninhalten erzeugen . . . 160 3.5 Drag&Drop ein- und ausschalten. . . . . . . . . . . . . . . . . . . . 161 3.6 Add-Ins überprüfen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 3.7 Wiedervorlageliste bearbeiten. . . . . . . . . . . . . . . . . . . . . . . 163 3.8 Sonstige Einstellmöglichkeiten in Excel . . . . . . . . . . . . . . 165 Allgemeine Einstellungen . . . . . . . . . . . . . . . . . . . . . . . . . 165 Zoom einstellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Aufenthaltsbereich festlegen. . . . . . . . . . . . . . . . . . . . . . . . 168 3.9 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 3.10 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 3.11 Übung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Tag 4 Auf Arbeitsmappen zugreifen – Datei-Operationen . . . . . . . . . 173 4.1 Arbeitsmappen öffnen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Arbeitsmappe öffnen (ohne Aktualisierung). . . . . . . . . . . . 176 Arbeitsmappe(n) öffnen über einen Dialog. . . . . . . . . . . . 177 Geöffnete Arbeitsmappen identifizieren . . . . . . . . . . . . . . 180 4.2 Arbeitsmappen speichern . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Arbeitsmappe speichern über Dialog. . . . . . . . . . . . . . . . . 182 Alle geöffneten Arbeitsmappen speichern . . . . . . . . . . . . . 184 Arbeitsmappe speichern unter Datum/Uhrzeit . . . . . . . . . 185 Arbeitsmappe bedingt speichern . . . . . . . . . . . . . . . . . . . . 186 4.3 Arbeitsmappen schließen . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Arbeitsmappe schließen – Änderungen speichern. . . . . . . 187 Arbeitsmappe schließen – Änderungen verwerfen. . . . . . . 187 Mehrere Arbeitsmappen schießen . . . . . . . . . . . . . . . . . . . 187 4.4 Arbeitsmappen anlegen. . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 7 24128.book Seite 8 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis 4.5 Arbeitsmappen zusammenführen. . . . . . . . . . . . . . . . . . . . 189 4.6 Arbeitsmappen entfernen . . . . . . . . . . . . . . . . . . . . . . . . . . 191 4.7 Die Dokumenteigenschaften . . . . . . . . . . . . . . . . . . . . . . . 191 Dokumenteigenschaften auslesen . . . . . . . . . . . . . . . . . . . 192 Dokumenteigenschaften setzen . . . . . . . . . . . . . . . . . . . . . 193 4.8 Verknüpfungen in Arbeitsmappen . . . . . . . . . . . . . . . . . . . 196 Verknüpfungen dokumentieren. . . . . . . . . . . . . . . . . . . . . 197 Verknüpfungen ändern. . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 4.9 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 4.10 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 4.11 Übung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Tag 5 Tabellenblätter programmieren . . . . . . . . . . . . . . . . . . . . . . . . . 203 5.1 Tabellen anlegen und benennen . . . . . . . . . . . . . . . . . . . . 204 5.2 Blatt-Typ bestimmen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 Tabellenblätter füllen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208 Blattprüfung vor Füllung vornehmen . . . . . . . . . . . . . . . . 208 5.3 Tabellen entfernen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Tabelle nach Rückfrage löschen . . . . . . . . . . . . . . . . . . . . 209 Rückfrage bei Tabellenlöschung unterdrücken. . . . . . . . . 210 Eigene Rückfrage programmieren . . . . . . . . . . . . . . . . . . . 210 Vor dem Löschen prüfen . . . . . . . . . . . . . . . . . . . . . . . . . . 211 5.4 Tabellen exportieren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 Tabelle als Textdatei exportieren . . . . . . . . . . . . . . . . . . . . 213 Tabelle in eine neue Arbeitsmappe kopieren . . . . . . . . . . 215 Tabelle in neue Arbeitsmappe übertragen. . . . . . . . . . . . . 216 Tabelle in andere Arbeitsmappe kopieren/übertragen. . . . 217 5.5 Tabellen importieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Textdatei-Import mit festen Datenfeldern . . . . . . . . . . . . . 217 Textdatei-Import mit variablen Datenfeldern . . . . . . . . . . 221 5.6 Tabellen ein- und ausblenden . . . . . . . . . . . . . . . . . . . . . . 223 Einfaches Aus- und Einblenden einer Tabelle . . . . . . . . . 224 Mehrere Tabellen ausblenden. . . . . . . . . . . . . . . . . . . . . . 225 Das sichere Ausblenden von Blättern. . . . . . . . . . . . . . . . . 226 Blätter wieder einblenden. . . . . . . . . . . . . . . . . . . . . . . . . . 227 5.7 Tabellen gruppieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228 Einfaches Gruppieren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 Komplexeres Gruppieren . . . . . . . . . . . . . . . . . . . . . . . . . . 230 8 24128.book Seite 9 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis 5.8 Tabellen schützen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232 Nur bestimmte Zellen vor Veränderung schützen . . . . . . 234 Temporären Schutz einstellen. . . . . . . . . . . . . . . . . . . . . . 236 Bestimmte Bereiche vom Schutz ausnehmen. . . . . . . . . . 236 5.9 Tabellen sortieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238 5.10 Kopf- und Fußzeilen programmieren. . . . . . . . . . . . . . . . . 239 Kopf- und Fußzeilen füllen . . . . . . . . . . . . . . . . . . . . . . . . 239 5.11 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 5.12 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 5.13 Übungen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Tag 6 Zellenbearbeitung mit VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 6.1 Zellen markieren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Zelle markieren und Koordinaten ausgeben . . . . . . . . . . . 247 Zellenbereich markieren und Koordinaten ausgeben. . . . 247 Mehrere Zellenbereiche markieren. . . . . . . . . . . . . . . . . . 248 Relative Markierungsformen . . . . . . . . . . . . . . . . . . . . . . . 249 Zellen über einen Index ansprechen . . . . . . . . . . . . . . . . . 252 Den verwendeten Bereich ermitteln . . . . . . . . . . . . . . . . . 253 Den umliegenden Bereich ermitteln. . . . . . . . . . . . . . . . . 254 6.2 Zellen füllen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 Zellen initialisieren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 Zellen abfragen und ändern. . . . . . . . . . . . . . . . . . . . . . . . 257 6.3 Zellen formatieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Hintergrundfarbe von Zellen festlegen . . . . . . . . . . . . . . . 259 Schriftart festlegen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Die Schriftschnitte angeben. . . . . . . . . . . . . . . . . . . . . . . . 263 Zellen rahmen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 Zahlenformate festlegen. . . . . . . . . . . . . . . . . . . . . . . . . . . 268 Datumsformate festlegen . . . . . . . . . . . . . . . . . . . . . . . . . . 269 6.4 Zellen benennen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 6.5 Zellen kommentieren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274 Kommentarzellen auskundschaften. . . . . . . . . . . . . . . . . . 274 Kommentare aus Zelleninhalten erstellen. . . . . . . . . . . . . 276 Zellen füllen aus Kommentaren. . . . . . . . . . . . . . . . . . . . . 277 Kommentare löschen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 9 24128.book Seite 10 Mittwoch, 7. Februar 2007 12:06 12 Inhaltsverzeichnis 6.6 Arbeiten mit Formeln . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Addition von bestimmten Sätzen . . . . . . . . . . . . . . . . . . . . 281 Subtraktion von Datumsangaben. . . . . . . . . . . . . . . . . . . . 283 Preise erhöhen durch Multiplikation. . . . . . . . . . . . . . . . . 283 6.7 Tabellenfunktionen einsetzen . . . . . . . . . . . . . . . . . . . . . . 285 Die Funktionsliste. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285 Die Tabellenfunktion Sum. . . . . . . . . . . . . . . . . . . . . . . . . 286 Die Tabellenfunktionen DATEDIF und Rank. . . . . . . . . 288 Die Tabellenfunktion SumIf . . . . . . . . . . . . . . . . . . . . . . . 292 Die Tabellenfunktion CountIf. . . . . . . . . . . . . . . . . . . . . . 293 Die Tabellenfunktion Count . . . . . . . . . . . . . . . . . . . . . . . 295 Die Tabellenfunktion CountA. . . . . . . . . . . . . . . . . . . . . . 296 Die Tabellenfunktion CountBlank . . . . . . . . . . . . . . . . . . 298 6.8 Formeln finden und dokumentieren . . . . . . . . . . . . . . . . . 300 Formelzellen finden. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300 Formelzellen dokumentieren. . . . . . . . . . . . . . . . . . . . . . . 302 6.9 Gültigkeitsprüfungen vornehmen . . . . . . . . . . . . . . . . . . . 305 Nur ganze Zahlen eingeben. . . . . . . . . . . . . . . . . . . . . . . . 307 Datumsgrenzen einstellen . . . . . . . . . . . . . . . . . . . . . . . . . 309 Gültigkeitsliste erstellen . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 6.10 Fragen & Antworten. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 6.11 Quiz. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 6.12 Übungen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Tag 7 Aktionen und Benutzereingaben in Excel überwachen . . . . . . 315 7.1 Was sind Ereignisse?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 7.2 Ereignisse auf Arbeitsmappenebene. . . . . . . . . . . . . . . . . . 317 Das Ereignis Workbook_Open. . . . . . . . . . . . . . . . . . . . . . 317 Das Ereignis Workbook_BeforeClose . . . . . . . . . . . . . . . . 319 Das Ereignis Workbook_BeforeSave . . . . . . . . . . . . . . . . . 320 Das Ereignis Workbook_NewSheets . . . . . . . . . . . . . . . . . 321 Weitere Arbeitsmappenereignisse im Überblick . . . . . . . . 322 7.3 Ereignisse auf Tabellenblattebene . . . . . . . . . . . . . . . . . . . 324 Das Ereignis Activate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 Das Ereignis Change . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325 Das Ereignis Selection_Change. . . . . . . . . . . . . . . . . . . . . 328 Das Ereignis Calculate. . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 Weitere Tabellenereignisse im Überblick . . . . . . . . . . . . . 331 10