1 Die Entwicklungsumgebung von Excel

27

1.1 Excel auf die Programmierung vorbereiten

27

1.1.1 Heruntersetzen der Sicherheitsstufe

27

1.1.2 Die Entwicklertools einbinden

28

1.2 Die Bestandteile der Entwicklungsumgebung

30

1.2.1 Der Projekt-Explorer und das Eigenschaften-Fenster

30

1.2.2 Das Direktfenster zum Testen verwenden

38

1.2.3 Das Überwachungsfenster

43

1.2.4 Das »Lokal«-Fenster

45

1.2.5 Die Symbolleiste »Bearbeiten«

46

1.2.6 Automatische Syntaxprüfung

51

1.2.7 Befehle in der nächsten Zeile fortsetzen

52

1.2.8 Automatische Anpassung der einzelnen Befehle

53

1.2.9 Schnelles Arbeiten über Tastenkombinationen

54

1.2.10 Der Objektkatalog

56

1.2.11 Der Makrorekorder

57

1.2.12 Sonstige wichtige Einstellungen in der Entwicklungsumgebung

62

1.2.13 Die Onlinehilfe

64

2 Datentypen, Variablen und Konstanten

65

2.1 Der Einsatz von Variablen

65

2.1.1 Regeln für die Syntax von Variablen

66

2.1.2 Variablen am Beginn vom Makro deklarieren

68

2.1.3 Die wichtigsten Variablentypen

68

2.1.4 Variablendeklarationen erzwingen

70

2.1.5 Noch kürzere Deklaration von Variablen

70

2.1.6 Die unterschiedlichen Variablentypen

71

2.1.7 Typische Beispiele für den Einsatz von Variablen

73

2.1.8 Die Objektvariablen

80

2.2 Die Verwendung von Konstanten

84

2.2.1 Typische Aufgaben für die Verwendung von Konstanten

85

3 Die Sprachelemente von Excel-VBA

99

3.1 Bedingungen

99

3.2 Typische Aufgaben aus der Praxis

100

3.2.1 Wert in einer Spalte suchen

100

3.2.2 Liegt die aktive Zelle in einem vorgegebenen Bereich?

102

3.2.3 Prüfung, ob eine bestimmte Datei verfügbar ist

103

3.2.4 Spalteninhalte direkt nach der Eingabe umsortieren

104

3.2.5 Spalten mit Wochenenden kennzeichnen

106

3.3 Die Kurzform einer Bedingung

108

3.3.1 Den Doppelklick auf eine Zelle abfangen

109

3.4 Die Anweisung »Select Case« einsetzen

110

3.4.1 Excel-Version abfragen

111

3.4.2 Zahlenwerte prüfen

112

3.4.3 Den Wochentag eines bestimmten Datums auslesen

114

3.4.4 Benotungen über einen Autotext durchführen

114

3.5 Die »Enum«-Anweisung

116

3.5.1 Umsatz klassifizieren mit »Enum«

117

3.6 Schleifen erstellen und verstehen

118

3.6.1 Die »For ... Next«-Schleife

119

3.6.2 Die »For Each ... Next«-Schleife

135

3.6.3 Die Schleife »Do Until ... Loop«

146

3.6.4 Die Schleife »Do While ... Loop«

149

3.7 Sonstige Sprachelemente

152

3.7.1 Die Anweisung »With«

152

4 Zellen und Bereiche programmieren

159

4.1 Zahlenformat einstellen und/oder konvertieren

159

4.1.1 Zahlenformate einstellen (Datum und Zahl)

159

4.1.2 Zahlenformate einstellen (Text)

161

4.1.3 Zahlenformate übertragen

163

4.1.4 Zellen mit Nullen auffüllen

163

4.1.5 Einheitliches Datumsformat einstellen

165

4.1.6 Unerwünschte führende und nachgestellte Leerzeichen entfernen

167

4.1.7 Korrektur nach fehlerhaftem Import von Daten

169

4.1.8 Die Position des Minuszeichens umstellen

171

4.1.9 Daten umschlüsseln

174

4.1.10 Einen eindeutigen Schlüssel aus mehreren Spalten basteln

175

4.2 Zellen, Rahmen und Schriften formatieren

178

4.2.1 Schriftart ermitteln

178

4.2.2 Schriftart ändern

178

4.2.3 Zelleninhalte löschen

181

4.2.4 Schriftfarbe teilweise ändern

182

4.2.5 Automatisch runden und formatieren

184

4.2.6 Zwei Bereiche miteinander vergleichen

186

4.2.7 Einen Bereich »mustern«

188

4.2.8 Einen Bereich einrahmen

190

4.2.9 Einen Bereich umrahmen

192

4.3 Die bedingte Formatierung von Excel

194

4.3.1 Eine bedingte Formatierung als Standard einstellen

194

4.3.2 Duplikate mit dem bedingten Format aufspüren

198

4.3.3 Wertgrößen über einen Datenbalken darstellen

199

4.3.4 Eine Farbskala definieren und einsetzen

201

4.3.5 Daten über Pfeilsymbole bewerten

202

4.3.6 Die Top-Werte in einem Bereich hervorheben

203

4.3.7 Die einzugebende Textlänge überwachen

205

4.3.8 Eine bedingte Formatierung mit Wertgrenzen ausstatten

206

4.3.9 Sparklines einsetzen

208

4.4 Bereiche und Zellen benennen

209

4.4.1 Einen Bereich benennen

209

4.4.2 Mehrere Zellen einzeln benennen

211

4.4.3 Konstante als Namen vergeben

212

4.4.4 Verwendete Namen auslesen

213

4.4.5 Versteckte Namen sichtbar machen

214

4.4.6 Einen geheimen Namen anlegen

215

4.4.7 Einen dynamischen Namen anlegen

215

4.4.8 Ein Zellen-Dropdown auf Basis einer benannten Liste anlegen

217

4.4.9 Eine Prüfung auf Namen vornehmen

218

4.4.10 Benannte Bereiche löschen

220

4.4.11 Benutzerdefinierte Listen erstellen

221

4.5 Tabellenfunktionen einsetzen

222

4.5.1 Bedingtes Zählen von Werten (ZÄHLENWENN)

223

4.5.2 Bedingtes Zählen von Werten bei mehreren Bedingungen (ZÄHLENWENNS)

224

4.5.3 Bedingte Summierung von Umsätzen

226

4.5.4 Bedingte Summierung von Umsätzen mit mehreren Kriterien

227

4.5.5 Den SVERWEIS im Makro einsetzen

229

4.5.6 Extremwerte finden und kennzeichnen

231

4.5.7 Prüfen, ob ein bestimmter Bereich leer ist

234

4.5.8 Einen Bereich mit Zahlenwerten mit vorangestellten Nullen auffüllen

235

4.5.9 Die Top-3-Werte in einem Bereich aufspüren

236

4.5.10 Automatische Prüfung und Überwachung eines Budgets

238

4.5.11 Mussfelder in einer Tabelle überprüfen

240

4.5.12 Mittelwert über eine InputBox ermitteln

241

4.5.13 Werte oberhalb und unterhalb des Durchschnitts ermitteln und kennzeichnen

243

4.6 Matrixformeln in der Programmierung nutzen

244

4.6.1 Werte bedingt zählen

245

4.6.2 Werte bedingt summieren

246

4.6.3 Mittelwert bilden ohne Berücksichtigung von Nullen

247

4.6.4 Extremwerte bedingt bilden

248

4.6.5 Den am meisten genannten Wert ermitteln

248

4.7 Texte und Zahlen manipulieren

249

4.7.1 Texte finden und umstellen

249

4.7.2 Mehrere Spalten anhand von Trennzeichen splitten

252

4.7.3 Daten nach einem Datentransfer bereinigen

254

4.7.4 Zeichenfolge(n) aus Zellen entfernen

255

4.7.5 Buchstaben aus Zellen entfernen

257

4.7.6 Dateinamen aus Pfad extrahieren

259

4.7.7 Alle Formelzellen einer Tabelle schützen

260

4.8 Gültigkeitsprüfung in Excel

261

4.8.1 Gültigkeitskriterien erstellen

262

4.8.2 Zellen mit Gültigkeitsfunktion auswählen

264

4.8.3 Datumsgrenzen festlegen

265

4.8.4 Nur Werktage für die Eingabe zulassen

267

4.8.5 Eine Gültigkeitsliste aus einem Datenfeld befüllen

268

4.8.6 Uhrzeiten mit einer Gültigkeitsüberprüfung regeln

270

4.8.7 Ein Zellen-Dropdown aus einer Konstanten befüllen

272

4.8.8 Einen Standardeintrag für Zellen-Dropdowns setzen

273

4.8.9 Gültigkeitskriterien löschen

274

4.9 Kommentare in Excel einsetzen

274

4.9.1 Kommentare einfügen

275

4.9.2 Kommentare im Direktfenster auslesen

276

4.9.3 Kommentare formatieren

277

4.9.4 Kommentare aus der aktiven Tabelle löschen

280

4.9.5 Alle Kommentare einer Arbeitsmappe löschen

281

4.9.6 Einen Kommentar einem Namen in einer Arbeitsmappe zuweisen

282

4.9.7 Den Autor von Kommentaren anpassen

283

4.9.8 Kommentarkennzeichnung ein- und ausschalten

284

4.9.9 Kommentare einer Arbeitsmappe in eine Textdatei schreiben

284

4.9.10 Kommentare vor Veränderung schützen

285

4.9.11 Ein Bild in einen Kommentar einfügen

286

5 Spalten und Zeilen programmieren

289

5.1 Zeilen und Spalten ansprechen, ansteuern und anpassen

289

5.1.1 Mehrere Zeilen auf dem Tabellenblatt markieren

290

5.1.2 Mehrere Spalten auf dem Tabellenblatt markieren

290

5.1.3 Zeilen und Spalten markieren

291

5.1.4 Markierte Spalten zählen

291

5.1.5 Letzte freie Zelle in Spalte anspringen

293

5.1.6 Anzahl der benutzten Spalten und Zeilen ermitteln

293

5.1.7 Zeilen und Spalten im umliegenden Bereich zählen

295

5.1.8 Zeilenhöhe und Spaltenbreite einstellen

296

5.2 Zeilen einfügen und löschen

299

5.2.1 Zeile einfügen

299

5.2.2 Mehrere Zeilen einfügen

299

5.2.3 Leere Zeilen dynamisch einfügen

300

5.2.4 Wirklich leere Zeilen löschen

302

5.2.5 Doppelte Sätze löschen

303

5.3 Spalten einfügen, löschen und bereinigen

304

5.3.1 Spalte einfügen

304

5.3.2 Mehrere Spalten einfügen

304

5.3.3 Spalte löschen

305

5.3.4 Mehrere Spalten löschen

305

5.4 Zeilen ein- und ausblenden

306

5.4.1 Leere Zeilen ausblenden

306

5.4.2 Alle Zellen einblenden

307

5.5 Spalten ein- und ausblenden

307

5.5.1 Bestimmte Spalten ausblenden

308

5.6 Spalten und Zeilen formatieren

308

5.7 Daten sortieren

310

5.7.1 Daten sortieren mit der klassischen Variante

310

5.7.2 Daten sortieren mit der modernen Methode

311

5.7.3 Daten sortieren nach Farbe der Zellen

314

5.7.4 Daten nach einer Überschrift spaltenweise sortieren

315

5.8 Spalte(n) vergleichen

315

5.8.1 Zelle mit Spalte vergleichen

316

5.8.2 Spalten über eine bedingte Formatierung miteinander vergleichen

317

5.9 Zeilen filtern

318

5.9.1 AutoFilter aktivieren und deaktivieren

319

5.9.2 Filterkriterien setzen

321

5.9.3 Nur Texte filtern

324

5.9.4 Daten filtern, die eine Zeichenfolge enthalten

325

5.9.5 Der Top-10-Filter

326

5.9.6 Filtern nach Zellenfarbe

328

5.9.7 Gefilterte Zeilen entfernen

329

5.9.8 Filterkriterien als Datenfeld übergeben

331

5.9.9 Alle gesetzten Filter sichtbarer machen

336

5.9.10 Wie lauten die Filterkriterien?

338

5.9.11 Doppelte Werte mit dem Spezialfilter ermitteln

339

5.9.12 Doppelte Werte mit dem »Dictionary«-Objekt entfernen

341

5.9.13 Daten über einen Kriterienbereich filtern

342

5.9.14 Mehrere Spalten über einen Kriterienbereich filtern

344

5.9.15 Wildcards im Spezialfilter einsetzen

345

5.9.16 Filtern von Umsätzen in einem vorgegebenen Zeitraum

347

5.9.17 Gefilterte Daten transferieren

349

5.10 Zeilen über das Teilergebnis gruppieren

351

6 Tabellen und Diagramme programmieren

355

6.1 Tabellen einfügen

355

6.2 Tabellenblätter benennen

356

6.2.1 Eine neue Mappe mit 12 Monatstabellen anlegen und benennen

356

6.2.2 Eine neue Mappe mit den nächste 14 Tagen anlegen

358

6.2.3 Tabelle einfügen und benennen kombinieren

359

6.3 Tabellen markieren

359

6.4 Tabellenblätter gruppieren

360

6.4.1 Mehrere Tabellen gruppieren

360

6.4.2 Alle Tabellen gruppieren

361

6.4.3 Gruppierte Tabellen übertragen

362

6.4.4 Gruppierte Tabellen ermitteln

362

6.5 Tabellenblätter löschen

362

6.5.1 Eine Tabelle löschen

363

6.5.2 Bestimmte Tabellen aus einer Mappe entfernen

364

6.5.3 Tabellen mit gefärbten Registerlaschen entfernen

365

6.5.4 Leere Tabellen aus Arbeitsmappen entfernen

366

6.6 Tabellenblätter ein- und ausblenden

367

6.6.1 Tabellenblätter sicher ausblenden

368

6.6.2 Tabellen je nach Status ein- oder ausblenden

368

6.6.3 Alle Tabellenblätter anzeigen

369

6.6.4 Alle Tabellen außer der aktiven Tabelle ausblenden

369

6.7 Tabellenblätter schützen

370

6.7.1 Tabellenschutz aufheben

371

6.7.2 Alle Tabellen einer Arbeitsmappe schützen

371

6.7.3 Weitere Schutzfunktionen ab Excel 2002

372

6.7.4 Passwort – Einstellungsdialog mit verschlüsseltem Passwort aufrufen

373

6.8 Tabellen einstellen

374

6.8.1 Registerlaschen ein- und ausblenden

374

6.8.2 Tabellenansicht anpassen

375

6.8.3 Einen einheitliche Zoomeinstellung vornehmen

376

6.8.4 Tabellenblätter sortieren

376

6.8.5 Kopf- und Fußzeilen einrichten

377

6.8.6 Druckbereiche festlegen

386

6.8.7 Das Tabellen Gitternetz ein- und ausschalten

387

6.8.8 Zeilen- und Spaltenköpfe ein- und ausblenden

387

6.8.9 Cursor einstellen auf Zelle A1

388

6.9 Tabellenblätter drucken und PDF erstellen

388

6.9.1 Mehrere Kopien drucken

389

6.9.2 Markierte Bereiche drucken

389

6.9.3 Mehrere Tabellenblätter drucken

389

6.9.4 Tabelle als PDF ablegen

390

6.10 Tabelleninhaltsverzeichnis erstellen

391

6.11 Intelligente Tabellen

393

6.11.1 Tabelle umwandeln

393

6.11.2 Tabelle um eine Spalte ergänzen

395

6.11.3 Tabelle um eine Zeile ergänzen

396

6.11.4 Tabelle filtern

397

6.11.5 Tabellen sortieren

399

6.11.6 Tabelle um Ergebniszeile erweitern

400

6.11.7 Tabelle entfernen

401

6.12 Pivot-Tabellen erstellen

401

6.12.1 Pivot-Tabellen aktualisieren

405

6.12.2 Eine einzelne Pivot-Tabelle aktualisieren

405

6.12.3 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

406

6.12.4 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren

406

6.12.5 Pivot-Tabellen dynamisch erweitern

407

6.12.6 Pivot-Tabellen formatieren

408

6.12.7 Slicer einfügen und bedienen

409

6.13 Diagramme programmieren

411

6.13.1 Umsätze in einem Säulendiagramm darstellen

412

6.13.2 Tagesumsätze im Liniendiagramm anzeigen

417

6.13.3 Tagesgenaue Formatierung im Punktdiagramm

419

6.13.4 Diagramme als Grafiken speichern

421

6.13.5 Gewinn und Verlust in einem Säulendiagramm präsentieren

423

6.13.6 Linienstärke unabhängig von den Markierungssymbolen formatieren

424

6.13.7 Sparklines automatisch erstellen

426

7 Arbeitsmappen und Dateien programmieren

429

7.1 Arbeitsmappen ansprechen

429

7.2 Arbeitsmappen anlegen

430

7.2.1 Eine Arbeitsmappe auf Basis einer Dokumentvorlage erstellen

430

7.2.2 Arbeitsmappe mit x Tabellen anlegen

431

7.2.3 Mappe mit Wochentabellen anlegen

432

7.3 Arbeitsmappen speichern

433

7.3.1 Arbeitsmappe unter aktuellen Tagesdatum speichern

434

7.3.2 Alle Tabellen einer Mappe als eigenständige Mappen speichern

434

7.3.3 Mappe erstellen und »Speichern unter«-Dialog aufrufen

436

7.3.4 Individuellen Speichern-Dialog aufrufen

437

7.3.5 Kopie der aktuellen Mappe zur Laufzeit erstellen

438

7.4 Arbeitsmappen öffnen

439

7.4.1 Mehrere Arbeitsmappen öffnen

439

7.4.2 Die aktuelle Datei in einem Verzeichnis öffnen

441

7.4.3 Regelmäßig die Dateiexistenz prüfen

442

7.4.4 Alle verknüpften Mappen automatisch öffnen

442

7.5 Arbeitsmappen schließen

444

7.5.1 Arbeitsmappe schließen – Änderungen akzeptieren

444

7.5.2 Alle Arbeitsmappen bis auf eine schließen

445

7.6 Arbeitsmappe löschen

446

7.6.1 Arbeitsmappe nach Verfallsdatum löschen

446

7.6.2 Alle Excel-Mappen in einem Verzeichnis löschen

447

7.6.3 Mappe löschen, die älter als 14 Tage ist

448

7.7 Arbeitsmappen drucken

449

7.7.1 Nur bestimmte Tabellen drucken

449

7.7.2 Alle Mappen eines Verzeichnisses drucken

450

7.7.3 Nur sichtbare Blätter ausdrucken

451

7.8 Dokumenteigenschaften verarbeiten

452

7.8.1 Dokumenteigenschaftsnamen abfragen

452

7.8.2 Letztes Speicherdatum abfragen

454

7.8.3 Erstelldatum herausfinden und manipulieren

454

7.8.4 Zugriffsdaten einer Arbeitsmappe ermitteln

455

7.8.5 Eigene Dokumenteigenschaften verwenden

456

7.9 Arbeitsmappen und Verknüpfungen

457

7.9.1 Verknüpfungen in Hyperlinks umwandeln

458

7.9.2 Verknüpfungen aus der Arbeitsmappe entfernen

459

7.9.3 Verknüpfungen ändern

461

7.9.4 Verknüpfungen aktualisieren

462

7.10 Arbeitsmappe durchsuchen

463

7.11 Arbeitsmappen miteinander vergleichen

464

7.12 Arbeitsmappenübersicht erstellen

467

7.13 Textdateien importieren

468

7.14 Makros für das Dateimanagement

480

7.14.1 Ein Jahresverzeichnis automatisch anlegen

480

7.14.2 Eine bestimmte Datei nach Rückfrage löschen

481

7.14.3 Einen Ordner archivieren

483

7.14.4 Eine bestimmte Datei kopieren

484

7.14.5 Ordner anlegen und entfernen

485

7.14.6 Verzeichnisstruktur in einer Tabelle anzeigen

487

8 Eigene Funktionen, reguläre Ausdrücke und API-Funktionen

491

8.1 Benutzerdefinierte Funktionen

491

8.1.1 Aktive Arbeitsmappe ermitteln

492

8.1.2 Aktives Tabellenblatt ermitteln

493

8.1.3 Ist eine Tabelle leer?

494

8.1.4 Ist eine Tabelle geschützt?

495

8.1.5 Befinden sich Daten in einer bestimmten Spalte?

495

8.1.6 Den letzten Wert einer Spalte ermitteln

496

8.1.7 Den letzten Wert einer Zeile ermitteln

496

8.1.8 Den aktiven Bearbeiter identifizieren

497

8.1.9 Funktion zum Umsetzen von Noten

497

8.1.10 Rangfolge als Text ausgeben

499

8.1.11 Enthält eine bestimmte Zelle ein Gültigkeitskriterium?

499

8.1.12 Enthält eine Zelle einen Kommentar?

500

8.1.13 Ist eine bestimmte Zelle verbunden?

501

8.1.14 Initialen aus Namen erstellen

501

8.1.15 Nur Zellen mit Fettdruck addieren

502

8.1.16 Mit Uhrzeiten rechnen

503

8.1.17 Erweitertes Runden durchführen

505

8.1.18 Schnelles Umrechnen von Geschwindigkeiten

506

8.1.19 Extremwerte berechnen

507

8.1.20 Erste Ziffer in einer Zelle ermitteln

508

8.1.21 Buchstaben aus Zellen entfernen

510

8.1.22 Anzahl der Ziffern einer Zelle ermitteln

511

8.1.23 Römische Zahlen in arabische umwandeln

512

8.1.24 Einen Kommentartext in eine Zelle holen

514

8.1.25 Angabe eines optionalen Parameters bei einer Funktion

514

8.1.26 Leerzeichen in einen String integrieren

515

8.2 Modulare Funktionen schreiben

516

8.2.1 Dateien in einem Verzeichnis zählen

516

8.2.2 Fehlerüberwachung umleiten

517

8.2.3 Prüfen, ob eine bestimmte Datei existiert

519

8.2.4 Prüfen, ob eine bestimmte Datei geöffnet ist

519

8.2.5 Prüfen, ob eine Datei gerade bearbeitet wird

520

8.2.6 Prüfen, ob ein bestimmter Name in der Arbeitsmappe verwendet wird

521

8.2.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln

522

8.2.8 Den letzten Wert einer Spalte ermitteln

524

8.2.9 Grafikelemente in einem definierten Bereich löschen

525

8.2.10 Kalenderwoche nach DIN ermitteln

527

8.2.11 Unerwünschte Zeichen aus Zellen entfernen

528

8.3 Funktionen verfügbar machen

530

8.3.1 Speichern der Funktionen in der persönlichen Arbeitsmappe

530

8.3.2 Speichern der Funktionen in einem Add-In

531

8.3.3 Ein Add-In einbinden

532

8.4 Mit regulären Ausdrücken programmieren

532

8.4.1 Funktionen für die Arbeit mit regulären Ausdrücken erstellen

533

8.4.2 Bestimmte Zeichenfolgen in Texten finden

536

8.4.3 Spezielle Zeichen nutzen

537

8.4.4 Zeichenfolgen aus Texten extrahieren

539

8.4.5 Eine E-Mail-Adresse prüfen

541

8.4.6 Konten prüfen

543

8.4.7 Zahlen aus Texten extrahieren

544

8.5 API-Funktionen einsetzen

546

8.5.1 Ermittlung der Laufwerke am PC

546

8.5.2 Bedienung des CD-ROM-Laufwerks

549

8.5.3 Bildschirmauflösung ermitteln

550

8.5.4 Ist ein externes Programm gestartet?

550

8.5.5 Excel schlafen schicken

551

8.5.6 Verzeichnisbaum anzeigen und auswerten

551

8.5.7 Cursorposition in Pixel angeben

554

8.5.8 Sounds per API-Funktion ausgeben

554

8.5.9 PC piepsen lassen

555

8.5.10 Eine E-Mail erstellen

555

8.5.11 Eine UserForm bildschirmfüllend anzeigen

556

8.5.12 Eine UserForm unverrückbar machen

557

8.5.13 Ein Label einer UserForm mit einem Hyperlink ausstatten

558

8.5.14 Den Standarddrucker per API abfragen

559

8.5.15 Texte über MD5 verschlüsseln

560

9 Ereignisse programmieren

563

9.1 Ereignisse für die Arbeitsmappe

563

9.1.1 Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-Ereignissen

564

9.1.2 Die wichtigsten Ereignisse für die Arbeitsmappe im Überblick

565

9.1.3 Zugriff beim Öffnen der Mappe festhalten (»Workbook_Open«)

567

9.1.4 Das Schließen der Arbeitsmappe bedingt verhindern (»Workbook_BeforeClose«)

572

9.1.5 Letztes Bearbeitungsdatum festhalten (»Workbook_BeforeSave«)

572

9.1.6 Die Lösung für das sparsame Drucken (»Workbook_BeforePrint«)

573

9.1.7 Einfügen von Blättern verhindern (»Workbook_NewSheet«)

574

9.2 Ereignisse für das Tabellenblatt

575

9.2.1 Allgemeine Vorgehensweise bei der Einstellung von Tabellenereignissen

575

9.2.2 Die wichtigsten Ereignisse für Tabellen im Überblick

576

9.2.3 Passworteingabe beim Aktivieren einer Tabelle (»Worksheet_Activate«)

576

9.2.4 Vergleich von zwei Spalten (»Worksheet_Change«)

577

9.2.5 AutoTexte über Kürzel abrufen (»Worksheet_Change«)

579

9.2.6 Symbole nach der Eingabe verändern (»Worksheet_Change«)

580

9.2.7 Die Spaltensumme überwachen (»Worksheet_Change«)

582

9.2.8 Nur einmalige Eingabe zulassen (»Worksheet_Change«)

583

9.2.9 Die Eingabe von Dubletten verhindern (»Worksheet_Change«)

583

9.2.10 Eingabe verhindern (»Worksheet_SelectionChange«)

585

9.2.11 Markierung überwachen (»Worksheet_SelectionChange«)

586

9.2.12 Mausklicks überwachen (»Worksheet_BeforeRightClick«)

587

9.2.13 Die Aktualisierung von Pivot-Tabellen überwachen (»Worksheet_PivotTableUpdate«)

587

9.3 Reaktion auf Tastendruck

588

9.3.1 Texte einfügen

590

9.3.2 Blattsperre ohne Blattschutz erstellen

591

9.3.3 Nur Werte einfügen

592

9.4 Zeitsteuerung in Excel

593

9.4.1 Regelmäßig die Uhrzeit anzeigen

593

9.4.2 Die Zeit läuft ...

594

9.4.3 Zellen blinken lassen

595

9.4.4 Eingaben nach Ablauf einer Minute löschen

596

10 Die VBE-Programmierung

599

10.1 Die VBE-Bibliothek einbinden

600

10.1.1 Die VBE-Bibliothek deaktivieren

601

10.1.2 Weitere Bibliotheken einbinden

602

10.1.3 Objektbibliotheken deaktivieren

603

10.1.4 Informationen zu Objektbibliotheken ausgeben

604

10.1.5 VBE-Editor aufrufen

604

10.1.6 Das Direktfenster aufrufen

605

10.2 Die VBE ein- und ausschalten

605

10.2.1 Neue Module einfügen

606

10.2.2 Einzelne Module löschen

607

10.3 Einzelnes Makro löschen

607

10.4 Alle Makros aus einer Arbeitsmappe entfernen

608

10.4.1 Module mit Makros bestücken

609

10.4.2 Makro zeilenweise in ein Modul übertragen

610

10.4.3 Makros aus einer Textdatei in ein Modul überführen

611

10.4.4 Export von VBA-Modulen in Textdateien

612

10.5 Identifikation von Komponenten

613

10.6 Ein bestimmtes Makro auskommentieren

614

10.7 Das Direktfenster löschen

615

10.8 Den Status des VBA-Projekt abfragen

615

10.9 Makros und Ereignisse dokumentieren

616

11 Dialoge, Meldungen und UserForms programmieren

619

11.1 »MsgBox«-Meldung

620

11.1.1 Welche Schaltfläche wurde angeklickt?

622

11.1.2 Löschrückfrage

623

11.2 Die »InputBox«-Eingabemaske

623

11.2.1 Einen Suchbegriff über eine InputBox abfragen

624

11.2.2 Abfrage des Spaltenbuchstabens

626

11.3 Integrierte Dialoge einsetzen

627

11.3.1 Den »Öffnen«-Dialog aufrufen

628

11.3.2 Den Dialog »Optionen« aufrufen

631

11.4 Eigene UserForms entwerfen

631

11.4.1 UserForm einfügen

632

11.4.2 UserForm beschriften

633

11.4.3 UserForm aufrufen

634

11.4.4 Die verfügbaren Steuerelemente

634

11.4.5 Steuerelemente einfügen

635

11.4.6 Die wichtigsten Eigenschaften

636

11.4.7 Ereignisse einstellen

640

11.5 Programmierung von Textfeldern

640

11.5.1 Passwort über einen Dialog abfragen

641

11.5.2 Textfelder leeren

644

11.5.3 Textfelder kennzeichnen

647

11.5.4 Prüfung auf numerischen Inhalt

649

11.5.5 Länge eines Textfeldes prüfen

651

11.5.6 Prüfen und rechnen mit Textfeldern

652

11.5.7 Prüfen und widerrufen

657

11.5.8 Eine AutoAusfüllen-Funktion programmieren

659

11.5.9 Rechtschreibprüfung vornehmen

662

11.5.10 Daten über ein Textfeld suchen

664

11.6 Programmierung von Listenfeldern

668

11.6.1 Listenfeld mit Tabellen füllen

668

11.6.2 Listenfeld mit Monaten füllen

671

11.6.3 Mehrspaltiges Listenfeld mit Daten aus Tabelle füllen

673

11.6.4 Listenfeld transponiert füllen

678

11.6.5 Listenfelder im Duett

681

11.6.6 Listenfeld und Textfelder im Zusammenspiel

685

11.7 Programmierung von Kombinationsfeldlisten

687

11.7.1 Dropdown mit Tagen füllen

687

11.7.2 Unikate Einträge im Dropdown anzeigen

689

11.7.3 Dropdowns synchronisieren

693

11.7.4 Dropdown und Listenfeld im Duett

696

11.8 Die Programmierung von Optionsschaltflächen

699

11.8.1 Mehrwertsteuersatz als Option anwenden

699

11.8.2 Optionsfelder und Listenfeld im Zusammenspiel

703

11.9 Die Programmierung von Kontrollkästchen

705

11.9.1 Kontrollkästchen über eine Tabelle speisen

706

11.9.2 Ansichtseinstellungen über Kontrollkästchen vornehmen

709

11.10 Die Programmierung von Bildelementen

713

11.10.1 Der eigene Bildbetrachter

713

11.11 Die Programmierung sonstiger Steuerelemente

717

11.11.1 Bilder in MultiPage laden

717

11.11.2 Umschaltfläche programmieren

719

11.11.3 Drehfeld programmieren

724

11.11.4 Die Programmierung des »ListView«-Steuerelements

727

11.11.5 Die Programmierung des »TreeView«-Steuerelements

732

11.11.6 Die Programmierung des »ProgressBar«-Steuerelements

736

11.12 Das Verwaltungstool

738

11.12.1 Die hinterlegte Datentabelle

739

11.12.2 Die beteiligten Steuerelemente

740

11.12.3 Vorbereitende Aufgaben

740

11.12.4 Daten suchen

742

11.12.5 Mit Klick auf das Listenfeld die Textfelder ausfüllen

744

11.12.6 Den Dialog initialisieren

745

11.12.7 Datensatz löschen

746

11.12.8 Datensatz ändern

747

11.12.9 Neue Kundennummer ermitteln

748

11.12.10 Datensatz anlegen

748

12 Excel im Umfeld von Office programmieren

751

12.1 Excel im Zusammenspiel mit PowerPoint

751

12.1.1 Excel-Bereich nach PowerPoint exportieren

751

12.1.2 Bereich aus Excel in eine bestehende Präsentation einfügen

754

12.1.3 Excel-Bereich verknüpft in eine neue Präsentation integrieren

756

12.1.4 PowerPoint-Folie als Objekt in Excel einbinden

758

12.1.5 Diagrammobjekte in eine Präsentation exportieren

760

12.2 Excel im Zusammenspiel mit Word

762

12.2.1 Excel-Bereich in Dokument exportieren

763

12.2.2 Excel-Tabelle in ein leeres Dokument überführen

765

12.2.3 Markierten Bereich einer Excel-Tabelle in ein Dokument exportieren

769

12.2.4 Bereich als Grafik an eine bestimmte Stelle eines Dokuments einfügen

771

12.3 Excel im Zusammenspiel mit Outlook

773

12.3.1 Kontaktdaten aus Excel nach Outlook exportieren

774

12.3.2 Termine aus Excel in den Outlook-Kalender schieben

778

12.3.3 Aktive Tabelle aus Excel heraus versenden

782

12.3.4 Aktive Tabelle als Anhang aus Excel heraus versenden

783

12.3.5 Aktive Arbeitsmappe per E-Mail versenden

785

12.3.6 Alle Dokumente aus einem Verzeichnis per E-Mail versenden

788

12.4 Excel im Zusammenspiel mit Access

790

12.4.1 Toolfrage und Randbedingungen

791

12.4.2 Anforderungen an das Tool

791

12.4.3 Die Umsetzung der Kernfunktionen

791

12.4.4 Befüllung der UserForm mit den wichtigsten Daten

793

12.4.5 Suche nach Therapeut über das Kürzel/den Patientennamen

795

12.4.6 Suche nach Datum

801

12.4.7 Termine erfassen

803

12.4.8 Änderung von Terminen

805

12.4.9 Termine löschen

807

12.4.10 Felder löschen

808

12.5 Excel im Zusammenspiel mit dem Internet Explorer

809

12.5.1 Eine Internetseite aus Excel aufrufen

809

12.5.2 Texte übersetzen mit Google

810

12.5.3 Bild aus Internet laden und in UserForm anzeigen

812

12.5.4 Ein PDF aus dem Internet laden

814

13 Datenfelder, ADO, Dictionaries und Collections programmieren

815

13.1 Aufgaben mit Hilfe von ADO und SQL-Statements lösen

815

13.1.1 Daten filtern und in einer anderen Tabelle ausgeben

816

13.1.2 Umsätze nach Datum verdichten

819

13.1.3 Umsätze nach Datum und Warengruppe verdichten

822

13.1.4 Daten aus einer Arbeitsmappe beziehen, ohne diese zu öffnen

825

13.1.5 Daten aus einer Tabelle löschen

828

13.1.6 Top-Werte ermitteln

830

13.1.7 Mehrere Tabellen zusammenfassen

832

13.1.8 Eine Unikatsliste bilden

834

13.1.9 Excel-Daten per ADO verändern

837

13.2 Arbeiten mit Arrays

840

13.2.1 Aktionen im Arbeitsspeicher ausführen lassen

841

13.2.2 Bestimmte Daten aus einer Tabelle löschen

844

13.2.3 Daten konvertieren

847

13.3 Arbeiten mit dem »Dictionary«-Objekt

851

13.3.1 Daten verdichten

852

13.3.2 Bedingte Summierung mit mehreren Kriterien

855

13.3.3 Eine Unikatsliste erstellen

859

13.3.4 Anzahl von Bestellungen ermitteln

861

13.3.5 Doppelte Daten in einem Bereich ermitteln

866

13.4 Arbeiten mit Collections

868

13.4.1 Unikate Einträge über eine Collection bilden

869

13.4.2 Eine Collection aus einer Tabelle befüllen

872

14 Die Programmierung der Excel-Oberfläche

875

14.1 Die Programmierung von Kontextmenüs

875

14.1.1 Kontextmenüs deaktivieren

876

14.1.2 Das Zellen-Kontextmenü erweitern

877

14.1.3 Kontextmenü aufbauen (dreistufig)

879

14.1.4 Kontextmenü zurücksetzen

881

14.2 Die Ribbon-Programmierung

881

14.2.1 Der Custom UI Editor

881

14.2.2 Weitere wichtige Quellen und Hilfen

884

14.2.3 Ribbon mit Schaltflächen erstellen

885

14.2.4 Ribbon mit ComboBox erstellen

887

14.2.5 Ribbon mit bereits verfügbaren Funktionen bestücken

889

14.2.6 Den Backstage-Bereich programmieren

890

14.2.7 Eine Galerie mit Fotos erstellen

892

15 Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten

893

15.1 Kleinere Fehler beheben

893

15.1.1 Stimmt die Syntax?

893

15.1.2 Ist die Variablendefinition gegeben?

894

15.1.3 Objekt vorhanden?

894

15.1.4 Methode, Eigenschaft verfügbar?

895

15.2 Schwerwiegendere Fehler

895

15.2.1 Fehler im Vorfeld erkennen und reagieren

895

15.2.2 Fehler ignorieren

896

15.2.3 Fehlerursache ermitteln

896

15.2.4 Die Funktion »IsError«

897

15.3 Das Add-In MZ-Tools

898

15.3.1 Zeilennummern automatisch einfügen

899

15.3.2 Eine Fehlerbehandlung mit den MZ-Tools hinzufügen

900

15.4 Laufzeiten verkürzen

901

15.4.1 Variablen und Konstanten einsetzen

901

15.4.2 Berechnung und Bildschirmaktualisierung ausschalten

902

15.4.3 Integrierte Tabellenfunktionen anwenden

903

15.5 VBA-Projekte schützen

903

16 Typische Verarbeitungsaufgaben aus der Praxis

905

16.1 Daten übertragen

905

16.2 Daten im Batch verarbeiten

908

16.3 Daten verteilen

910

16.3.1 Die Tabellen entfernen

911

16.3.2 Die Verteilung der Zeilen auf die Tabellen

912

16.3.3 Die Plausibilität prüfen

915

16.3.4 Der Export der Tabellen

917

16.4 Berichtsfilterseiten erstellen

918

16.5 Daten löschen

921

16.5.1 Daten entfernen – Variante 1

922

16.5.2 Daten entfernen – Variante 2

922

16.5.3 Daten entfernen – Variante 3

923

16.6 Daten kennzeichnen

924

16.6.1 Doppelte Daten kennzeichnen (der Standard)

925

16.6.2 Doppelte Daten kennzeichnen (die Erweiterung)

927

16.6.3 Top-10-Werte aus einem Bereich ermitteln

929