1 Die Entwicklungsumgebung von Excel

29

1.1 Excel auf die Programmierung vorbereiten

29

1.1.1 Heruntersetzen der Sicherheitsstufe

29

1.1.2 Die Entwicklertools einbinden

30

1.2 Die Bestandteile der Entwicklungsumgebung

32

1.2.1 Der Projekt-Explorer und das »Eigenschaften«-Fenster

32

1.2.2 Das Direktfenster zum Testen verwenden

40

1.2.3 Das Überwachungsfenster

45

1.2.4 Das »Lokal«-Fenster

47

1.2.5 Die Symbolleiste »Bearbeiten«

48

1.2.6 Automatische Syntaxprüfung

53

1.2.7 Befehle in der nächsten Zeile fortsetzen

54

1.2.8 Automatische Anpassung der einzelnen Befehle

55

1.2.9 Schnelles Arbeiten über Tastenkombinationen

56

1.2.10 Der Objektkatalog

58

1.2.11 Der Makrorekorder

59

1.2.12 Sonstige wichtige Einstellungen in der Entwicklungsumgebung

64

1.2.13 Die Onlinehilfe

66

2 Datentypen, Variablen und Konstanten

67

2.1 Der Einsatz von Variablen

67

2.1.1 Regeln für die Syntax von Variablen

68

2.1.2 Variablen am Beginn des Makros deklarieren

70

2.1.3 Die wichtigsten Variablentypen

70

2.1.4 Variablendeklarationen erzwingen

72

2.1.5 Noch kürzere Deklaration von Variablen

72

2.1.6 Die unterschiedlichen Variablentypen

73

2.1.7 Typische Beispiele für den Einsatz von Variablen

76

2.1.8 Die Objektvariablen

82

2.2 Die Verwendung von Konstanten

86

2.2.1 Typische Aufgaben für die Verwendung von Konstanten

87

3 Die Sprachelemente von Excel-VBA

101

3.1 Bedingungen

101

3.2 Typische Aufgaben aus der Praxis

102

3.2.1 Wert in einer Spalte suchen

102

3.2.2 Liegt die aktive Zelle in einem vorgegebenen Bereich?

104

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

105

3.2.4 Spalteninhalte direkt nach der Eingabe umsortieren

106

3.2.5 Spalten mit Wochenenden kennzeichnen

108

3.3 Die Kurzform einer Bedingung

110

3.3.1 Den Doppelklick auf eine Zelle abfangen

111

3.4 Die Anweisung »Select Case« einsetzen

112

3.4.1 Excel-Version abfragen

113

3.4.2 Zahlenwerte prüfen

114

3.4.3 Den Wochentag eines bestimmten Datums auslesen

116

3.4.4 Benotungen über einen Autotext durchführen

116

3.5 Die »Enum«-Anweisung

118

3.5.1 Umsatz klassifizieren mit »Enum«

119

3.6 Schleifen erstellen und verstehen

120

3.6.1 Die »For ... Next«-Schleife

121

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

137

3.6.3 Die Schleife »Do Until ... Loop«

148

3.6.4 Die Schleife »Do While ... Loop«

151

3.7 Sonstige Sprachelemente

154

3.7.1 Die Anweisung »With«

154

4 Zellen und Bereiche programmieren

161

4.1 Zahlenformat einstellen und/oder konvertieren

161

4.1.1 Zahlenformate einstellen (Datum und Zahl)

161

4.1.2 Zahlenformate einstellen (Text)

163

4.1.3 Zahlenformate übertragen

165

4.1.4 Zellen mit Nullen auffüllen

165

4.1.5 Einheitliches Datumsformat einstellen

167

4.1.6 Unerwünschte führende und nachgestellte Leerzeichen entfernen

169

4.1.7 Korrektur nach fehlerhaftem Import von Daten

171

4.1.8 Die Position des Minuszeichens umstellen

173

4.1.9 Daten umschlüsseln

176

4.1.10 Einen eindeutigen Schlüssel aus mehreren Spalten basteln

177

4.2 Zellen, Rahmen und Schriften formatieren

180

4.2.1 Schriftart ermitteln

180

4.2.2 Schriftart ändern

180

4.2.3 Zelleninhalte löschen

183

4.2.4 Schriftfarbe teilweise ändern

184

4.2.5 Automatisch runden und formatieren

186

4.2.6 Zwei Bereiche miteinander vergleichen

188

4.2.7 Einen Bereich »mustern«

190

4.2.8 Einen Bereich einrahmen

192

4.2.9 Einen Bereich umrahmen

194

4.3 Die bedingte Formatierung von Excel

196

4.3.1 Eine bedingte Formatierung als Standard einstellen

196

4.3.2 Duplikate mit dem bedingten Format aufspüren

200

4.3.3 Wertgrößen über einen Datenbalken darstellen

201

4.3.4 Eine Farbskala definieren und einsetzen

203

4.3.5 Daten über Pfeilsymbole bewerten

204

4.3.6 Die Top-Werte in einem Bereich hervorheben

205

4.3.7 Die einzugebende Textlänge überwachen

207

4.3.8 Eine bedingte Formatierung mit Wertgrenzen ausstatten

208

4.3.9 Sparklines einsetzen

210

4.4 Bereiche und Zellen benennen

211

4.4.1 Einen Bereich benennen

211

4.4.2 Mehrere Zellen einzeln benennen

213

4.4.3 Konstante als Namen vergeben

214

4.4.4 Verwendete Namen auslesen

215

4.4.5 Versteckte Namen sichtbar machen

216

4.4.6 Einen geheimen Namen anlegen

217

4.4.7 Einen dynamischen Namen anlegen

217

4.4.8 Ein Zellen-Dropdown auf Basis einer benannten Liste anlegen

219

4.4.9 Eine Prüfung auf Namen vornehmen

220

4.4.10 Benannte Bereiche löschen

222

4.4.11 Benutzerdefinierte Listen erstellen

223

4.5 Tabellenfunktionen einsetzen

224

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

225

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

226

4.5.3 Bedingte Summierung von Umsätzen

228

4.5.4 Bedingte Summierung von Umsätzen mit mehreren Kriterien

229

4.5.5 Den SVERWEIS im Makro einsetzen

231

4.5.6 Extremwerte finden und kennzeichnen

233

4.5.7 Prüfen, ob ein bestimmter Bereich leer ist

236

4.5.8 Einen Bereich mit Zahlenwerten mit vorangestellten Nullen auffüllen

237

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

238

4.5.10 Automatische Prüfung und Überwachung eines Budgets

240

4.5.11 Mussfelder in einer Tabelle überprüfen

242

4.5.12 Mittelwert über eine InputBox ermitteln

243

4.5.13 Werte oberhalb und unterhalb des Durchschnitts ermitteln und kennzeichnen

245

4.6 Matrixformeln in der Programmierung nutzen

246

4.6.1 Werte bedingt zählen

247

4.6.2 Werte bedingt summieren

248

4.6.3 Mittelwert bilden ohne Berücksichtigung von Nullen

249

4.6.4 Extremwerte bedingt bilden

250

4.6.5 Den am meisten genannten Wert ermitteln

250

4.7 Texte und Zahlen manipulieren

251

4.7.1 Texte finden und umstellen

251

4.7.2 Mehrere Spalten anhand von Trennzeichen splitten

254

4.7.3 Daten nach einem Datentransfer bereinigen

256

4.7.4 Zeichenfolge(n) aus Zellen entfernen

257

4.7.5 Buchstaben aus Zellen entfernen

259

4.7.6 Dateinamen aus Pfad extrahieren

261

4.7.7 Alle Formelzellen einer Tabelle schützen

262

4.8 Gültigkeitsprüfung in Excel

263

4.8.1 Gültigkeitskriterien erstellen

264

4.8.2 Zellen mit Gültigkeitsfunktion auswählen

266

4.8.3 Datumsgrenzen festlegen

267

4.8.4 Nur Werktage für die Eingabe zulassen

269

4.8.5 Eine Gültigkeitsliste aus einem Datenfeld befüllen

270

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

272

4.8.7 Ein Zellen-Dropdown aus einer Konstanten befüllen

274

4.8.8 Einen Standardeintrag für Zellen-Dropdowns setzen

275

4.8.9 Gültigkeitskriterien löschen

276

4.9 Kommentare in Excel einsetzen

276

4.9.1 Kommentare einfügen

277

4.9.2 Kommentare im Direktfenster auslesen

278

4.9.3 Kommentare formatieren

279

4.9.4 Kommentare aus der aktiven Tabelle löschen

282

4.9.5 Alle Kommentare einer Arbeitsmappe löschen

283

4.9.6 Einen Kommentar einem Namen in einer Arbeitsmappe zuweisen

284

4.9.7 Den Autor von Kommentaren anpassen

285

4.9.8 Kommentarkennzeichnung ein- und ausschalten

286

4.9.9 Kommentare einer Arbeitsmappe in eine Textdatei schreiben

286

4.9.10 Kommentare vor Veränderung schützen

287

4.9.11 Ein Bild in einen Kommentar einfügen

288

5 Spalten und Zeilen programmieren

291

5.1 Zeilen und Spalten ansprechen, ansteuern und anpassen

291

5.1.1 Mehrere Zeilen auf dem Tabellenblatt markieren

292

5.1.2 Mehrere Spalten auf dem Tabellenblatt markieren

292

5.1.3 Zeilen und Spalten markieren

293

5.1.4 Markierte Spalten zählen

293

5.1.5 Letzte freie Zelle in Spalte identifizieren

295

5.1.6 Anzahl der benutzten Spalten und Zeilen ermitteln

295

5.1.7 Zeilen und Spalten im umliegenden Bereich zählen

297

5.1.8 Zeilenhöhe und Spaltenbreite einstellen

298

5.2 Zeilen einfügen und löschen

301

5.2.1 Zeile einfügen

301

5.2.2 Mehrere Zeilen einfügen

301

5.2.3 Leere Zeilen dynamisch einfügen

302

5.2.4 Wirklich leere Zeilen löschen

304

5.2.5 Doppelte Sätze löschen

305

5.3 Spalten einfügen, löschen und bereinigen

306

5.3.1 Spalte einfügen

306

5.3.2 Mehrere Spalten einfügen

306

5.3.3 Spalte löschen

307

5.3.4 Mehrere Spalten löschen

307

5.4 Zeilen ein- und ausblenden

308

5.4.1 Leere Zeilen ausblenden

308

5.4.2 Alle Zellen einblenden

309

5.5 Spalten ein- und ausblenden

309

5.5.1 Bestimmte Spalten ausblenden

310

5.6 Spalten und Zeilen formatieren

310

5.7 Daten sortieren

312

5.7.1 Daten sortieren mit der klassischen Variante

312

5.7.2 Daten sortieren mit der modernen Methode

313

5.7.3 Daten sortieren nach Farbe der Zellen

316

5.7.4 Daten nach einer Überschrift spaltenweise sortieren

317

5.8 Spalte(n) vergleichen

317

5.8.1 Zelle mit Spalte vergleichen

318

5.8.2 Spalten über eine bedingte Formatierung miteinander vergleichen

319

5.9 Zeilen filtern

320

5.9.1 AutoFilter aktivieren und deaktivieren

321

5.9.2 Filterkriterien setzen

323

5.9.3 Nur Texte filtern

326

5.9.4 Daten filtern, die eine Zeichenfolge enthalten

327

5.9.5 Der Top-10-Filter

328

5.9.6 Filtern nach Zellenfarbe

330

5.9.7 Gefilterte Zeilen entfernen

331

5.9.8 Filterkriterien als Datenfeld übergeben

333

5.9.9 Alle gesetzten Filter sichtbarer machen

338

5.9.10 Wie lauten die Filterkriterien?

340

5.9.11 Doppelte Werte mit dem Spezialfilter ermitteln

341

5.9.12 Doppelte Werte mit dem »Dictionary«-Objekt entfernen

343

5.9.13 Daten über einen Kriterienbereich filtern

344

5.9.14 Mehrere Spalten über einen Kriterienbereich filtern

346

5.9.15 Wildcards im Spezialfilter einsetzen

347

5.9.16 Filtern von Umsätzen in einem vorgegebenen Zeitraum

349

5.9.17 Gefilterte Daten transferieren

351

5.10 Zeilen über das Teilergebnis gruppieren

353

6 Tabellen und Diagramme programmieren

357

6.1 Tabellen einfügen

357

6.2 Tabellenblätter benennen

358

6.2.1 Eine neue Mappe erstellen, 12 Monatstabellen anlegen und benennen

358

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

360

6.2.3 Tabelle einfügen und gleichzeitig benennen

361

6.3 Tabellen markieren

361

6.4 Tabellenblätter gruppieren

362

6.4.1 Mehrere Tabellen gruppieren

362

6.4.2 Alle Tabellen gruppieren

363

6.4.3 Gruppierte Tabellen übertragen

364

6.4.4 Gruppierte Tabellen ermitteln

364

6.5 Tabellenblätter löschen

364

6.5.1 Eine Tabelle löschen

365

6.5.2 Bestimmte Tabellen aus einer Mappe entfernen

366

6.5.3 Tabellen mit gefärbten Registerlaschen entfernen

367

6.5.4 Leere Tabellen aus Arbeitsmappen entfernen

368

6.6 Tabellenblätter ein- und ausblenden

369

6.6.1 Tabellenblätter sicher ausblenden

370

6.6.2 Tabellen je nach Status ein- oder ausblenden

370

6.6.3 Alle Tabellenblätter anzeigen

371

6.6.4 Alle Tabellen außer der aktiven Tabelle ausblenden

371

6.7 Tabellenblätter schützen

372

6.7.1 Tabellenschutz aufheben

373

6.7.2 Alle Tabellen einer Arbeitsmappe schützen

373

6.7.3 Weitere Schutzfunktionen ab Excel 2002

374

6.7.4 Passwort – Einstellungsdialog mit verschlüsseltem Passwort aufrufen

375

6.8 Tabellen einstellen

376

6.8.1 Registerlaschen ein- und ausblenden

376

6.8.2 Tabellenansicht anpassen

377

6.8.3 Einen einheitliche Zoomeinstellung vornehmen

378

6.8.4 Tabellenblätter sortieren

378

6.8.5 Kopf- und Fußzeilen einrichten

379

6.8.6 Druckbereiche festlegen

388

6.8.7 Das Tabellengitternetz ein- und ausschalten

389

6.8.8 Zeilen- und Spaltenköpfe ein- und ausblenden

389

6.8.9 Cursor einstellen auf Zelle A1

390

6.9 Tabellenblätter drucken und PDF erstellen

390

6.9.1 Mehrere Kopien drucken

391

6.9.2 Markierte Bereiche drucken

391

6.9.3 Mehrere Tabellenblätter drucken

391

6.9.4 Tabelle als PDF ablegen

392

6.10 Tabelleninhaltsverzeichnis erstellen

393

6.11 Intelligente Tabellen

395

6.11.1 Tabelle umwandeln

395

6.11.2 Tabelle um eine Spalte ergänzen

397

6.11.3 Tabelle um eine Zeile ergänzen

398

6.11.4 Tabelle filtern

399

6.11.5 Tabellen sortieren

401

6.11.6 Tabelle um Ergebniszeile erweitern

402

6.11.7 Tabelle entfernen

403

6.12 Pivot-Tabellen erstellen

403

6.12.1 Pivot-Tabellen aktualisieren

407

6.12.2 Eine einzelne Pivot-Tabelle aktualisieren

407

6.12.3 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

408

6.12.4 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren

408

6.12.5 Pivot-Tabellen dynamisch erweitern

409

6.12.6 Pivot-Tabellen formatieren

410

6.12.7 Slicer einfügen und bedienen

411

6.13 Diagramme programmieren

413

6.13.1 Umsätze in einem Säulendiagramm darstellen

414

6.13.2 Tagesumsätze im Liniendiagramm anzeigen

419

6.13.3 Tagesgenaue Formatierung im Punktdiagramm

421

6.13.4 Diagramme als Grafiken speichern

423

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

425

6.13.6 Linienstärke unabhängig von den Markierungssymbolen formatieren

426

6.13.7 Sparklines automatisch erstellen

428

7 Arbeitsmappen und Dateien programmieren

431

7.1 Arbeitsmappen ansprechen

431

7.2 Arbeitsmappen anlegen

432

7.2.1 Eine Arbeitsmappe auf Basis einer Dokumentvorlage erstellen

432

7.2.2 Arbeitsmappe mit x Tabellen anlegen

433

7.2.3 Mappe mit Wochentabellen anlegen

434

7.3 Arbeitsmappen speichern

435

7.3.1 Arbeitsmappe unter aktuellem Tagesdatum speichern

436

7.3.2 Alle Tabellen einer Mappe als eigenständige Mappen speichern

436

7.3.3 Mappe erstellen und »Speichern unter«-Dialog aufrufen

438

7.3.4 Individuellen Speichern-Dialog aufrufen

439

7.3.5 Kopie der aktuellen Mappe zur Laufzeit erstellen

440

7.4 Arbeitsmappen öffnen

441

7.4.1 Die Argumente der Methode »Open«

441

7.4.2 Mehrere Arbeitsmappen öffnen

441

7.4.3 Die aktuelle Datei in einem Verzeichnis öffnen

443

7.4.4 Regelmäßig die Dateiexistenz prüfen

444

7.4.5 Alle verknüpften Mappen automatisch öffnen

444

7.5 Arbeitsmappen schließen

446

7.5.1 Arbeitsmappe schließen – Änderungen akzeptieren

446

7.5.2 Alle Arbeitsmappen bis auf eine schließen

447

7.6 Arbeitsmappe löschen

448

7.6.1 Arbeitsmappe nach Verfallsdatum löschen

448

7.6.2 Alle Excel-Mappen in einem Verzeichnis löschen

449

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

450

7.7 Arbeitsmappen drucken

451

7.7.1 Nur bestimmte Tabellen drucken

451

7.7.2 Alle Mappen eines Verzeichnisses drucken

452

7.7.3 Nur sichtbare Blätter ausdrucken

453

7.8 Dokumenteigenschaften verarbeiten

454

7.8.1 Dokumenteigenschaftsnamen abfragen

454

7.8.2 Letztes Speicherdatum abfragen

456

7.8.3 Erstelldatum herausfinden und manipulieren

456

7.8.4 Zugriffsdaten einer Arbeitsmappe ermitteln

457

7.8.5 Eigene Dokumenteigenschaften verwenden

458

7.9 Arbeitsmappen und Verknüpfungen

459

7.9.1 Verknüpfungen in Hyperlinks umwandeln

460

7.9.2 Verknüpfungen aus der Arbeitsmappe entfernen

461

7.9.3 Verknüpfungen ändern

463

7.9.4 Verknüpfungen aktualisieren

464

7.10 Arbeitsmappe durchsuchen

465

7.11 Arbeitsmappen miteinander vergleichen

466

7.12 Arbeitsmappenübersicht erstellen

469

7.13 Textdateien importieren

470

7.14 Makros für das Dateimanagement

482

7.14.1 Ein Jahresverzeichnis automatisch anlegen

482

7.14.2 Eine bestimmte Datei nach Rückfrage löschen

483

7.14.3 Einen Ordner archivieren

485

7.14.4 Eine bestimmte Datei kopieren

486

7.14.5 Ordner anlegen und entfernen

487

7.14.6 Verzeichnisstruktur in einer Tabelle anzeigen

489

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

493

8.1 Benutzerdefinierte Funktionen

493

8.1.1 Aktive Arbeitsmappe ermitteln

494

8.1.2 Aktives Tabellenblatt ermitteln

495

8.1.3 Ist eine Tabelle leer?

496

8.1.4 Ist eine Tabelle geschützt?

497

8.1.5 Befinden sich Daten in einer bestimmten Spalte?

497

8.1.6 Den letzten Wert einer Spalte ermitteln

498

8.1.7 Den letzten Wert einer Zeile ermitteln

498

8.1.8 Den aktiven Bearbeiter identifizieren

499

8.1.9 Funktion zum Umsetzen von Schulnoten

499

8.1.10 Rangfolge als Text ausgeben

501

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

501

8.1.12 Enthält eine Zelle einen Kommentar?

502

8.1.13 Ist eine bestimmte Zelle verbunden?

503

8.1.14 Initialen aus Namen erstellen

503

8.1.15 Nur Zellen mit Fettdruck addieren

504

8.1.16 Mit Uhrzeiten rechnen

505

8.1.17 Erweitertes Runden durchführen

507

8.1.18 Schnelles Umrechnen von Geschwindigkeiten

508

8.1.19 Extremwerte berechnen

509

8.1.20 Erste Ziffer in einer Zelle ermitteln

510

8.1.21 Buchstaben aus Zellen entfernen

512

8.1.22 Anzahl der Ziffern einer Zelle ermitteln

513

8.1.23 Römische Zahlen in arabische umwandeln

514

8.1.24 Einen Kommentartext in eine Zelle holen

516

8.1.25 Angabe eines optionalen Parameters bei einer Funktion

516

8.1.26 Leerzeichen in einen String integrieren

517

8.2 Modulare Funktionen schreiben

518

8.2.1 Dateien in einem Verzeichnis zählen

518

8.2.2 Fehlerüberwachung umleiten

519

8.2.3 Prüfen, ob eine bestimmte Datei existiert

521

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

521

8.2.5 Prüfen, ob eine Datei gerade bearbeitet wird

522

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

523

8.2.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln

524

8.2.8 Den letzten Wert einer Spalte ermitteln

526

8.2.9 Grafikelemente in einem definierten Bereich löschen

527

8.2.10 Kalenderwoche nach DIN ermitteln

529

8.2.11 Unerwünschte Zeichen aus Zellen entfernen

530

8.3 Funktionen verfügbar machen

532

8.3.1 Speichern der Funktionen in der persönlichen Arbeitsmappe

532

8.3.2 Speichern der Funktionen in einem Add-In

533

8.3.3 Ein Add-In einbinden

534

8.4 Mit regulären Ausdrücken programmieren

534

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

535

8.4.2 Bestimmte Zeichenfolgen in Texten finden

538

8.4.3 Spezielle Zeichen nutzen

539

8.4.4 Zeichenfolgen aus Texten extrahieren

541

8.4.5 Eine E-Mail-Adresse prüfen

543

8.4.6 Konten prüfen

545

8.4.7 Zahlen aus Texten extrahieren

546

8.5 API-Funktionen einsetzen

548

8.5.1 Ermittlung der Laufwerke am PC

548

8.5.2 Bedienung des CD-ROM-Laufwerks

551

8.5.3 Bildschirmauflösung ermitteln

552

8.5.4 Ist ein externes Programm gestartet?

552

8.5.5 Excel schlafen schicken

553

8.5.6 Verzeichnisbaum anzeigen und auswerten

553

8.5.7 Cursorposition in Pixel angeben

556

8.5.8 Sounds per API-Funktion ausgeben

556

8.5.9 PC piepsen lassen

557

8.5.10 Eine E-Mail erstellen

557

8.5.11 Eine UserForm bildschirmfüllend anzeigen

558

8.5.12 Eine UserForm unverrückbar machen

559

8.5.13 Ein Label einer UserForm mit einem Hyperlink ausstatten

560

8.5.14 Den Standarddrucker per API abfragen

561

8.5.15 Texte über MD5 verschlüsseln

562

9 Ereignisse programmieren

565

9.1 Ereignisse für die Arbeitsmappe

565

9.1.1 Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-Ereignissen

566

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

567

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

569

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

574

9.1.5 Letztes Bearbeitungsdatum festhalten (»Workbook_BeforeSave«)

574

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

575

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

576

9.2 Ereignisse für das Tabellenblatt

577

9.2.1 Allgemeine Vorgehensweise bei der Einstellung von Tabellenereignissen

577

9.2.2 Die wichtigsten Ereignisse für Tabellen im Überblick

578

9.2.3 Passworteingabe beim Aktivieren einer Tabelle (»Worksheet_Activate«)

578

9.2.4 Vergleich von zwei Spalten (»Worksheet_Change«)

579

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

581

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

582

9.2.7 Die Spaltensumme überwachen (»Worksheet_Change«)

584

9.2.8 Nur einmalige Eingabe zulassen (»Worksheet_Change«)

585

9.2.9 Die Eingabe von Dubletten verhindern (»Worksheet_Change«)

585

9.2.10 Eingabe verhindern (»Worksheet_SelectionChange«)

587

9.2.11 Markierung überwachen (»Worksheet_SelectionChange«)

588

9.2.12 Mausklicks überwachen (»Worksheet_BeforeRightClick«)

589

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

589

9.3 Reaktion auf Tastendruck

590

9.3.1 Texte einfügen

592

9.3.2 Blattsperre ohne Blattschutz erstellen

593

9.3.3 Nur Werte einfügen

594

9.4 Zeitsteuerung in Excel

595

9.4.1 Regelmäßig die Uhrzeit anzeigen

595

9.4.2 Die Zeit läuft ...

596

9.4.3 Zellen blinken lassen

597

9.4.4 Eingaben nach Ablauf einer Minute löschen

598

10 Die VBE-Programmierung

601

10.1 Die VBE-Bibliothek einbinden

602

10.1.1 Die VBE-Bibliothek deaktivieren

603

10.1.2 Weitere Bibliotheken einbinden

604

10.1.3 Objektbibliotheken deaktivieren

605

10.1.4 Informationen zu Objektbibliotheken ausgeben

606

10.1.5 VBE-Editor aufrufen

607

10.1.6 Das Direktfenster aufrufen

607

10.2 Die VBE ein- und ausschalten

607

10.2.1 Neue Module einfügen

608

10.2.2 Einzelne Module löschen

609

10.3 Einzelnes Makro löschen

609

10.4 Alle Makros aus einer Arbeitsmappe entfernen

610

10.4.1 Module mit Makros bestücken

611

10.4.2 Makro zeilenweise in ein Modul übertragen

612

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

613

10.4.4 Export von VBA-Modulen in Textdateien

614

10.5 Identifikation von Komponenten

615

10.6 Ein bestimmtes Makro auskommentieren

616

10.7 Das Direktfenster löschen

617

10.8 Den Status des VBA-Projekts abfragen

617

10.9 Makros und Ereignisse dokumentieren

618

11 Dialoge, Meldungen und UserForms programmieren

621

11.1 »MsgBox«-Meldung

622

11.1.1 Welche Schaltfläche wurde angeklickt?

624

11.1.2 Löschrückfrage

625

11.2 Die »InputBox«-Eingabemaske

625

11.2.1 Einen Suchbegriff über eine InputBox abfragen

626

11.2.2 Abfrage des Spaltenbuchstabens

628

11.3 Integrierte Dialoge einsetzen

629

11.3.1 Den »Öffnen«-Dialog aufrufen

630

11.3.2 Den Dialog »Optionen« aufrufen

633

11.4 Eigene UserForms entwerfen

633

11.4.1 UserForm einfügen

634

11.4.2 UserForm beschriften

635

11.4.3 UserForm aufrufen

636

11.4.4 Die verfügbaren Steuerelemente

636

11.4.5 Steuerelemente einfügen

637

11.4.6 Die wichtigsten Eigenschaften

638

11.4.7 Ereignisse einstellen

642

11.5 Programmierung von Textfeldern

642

11.5.1 Passwort über einen Dialog abfragen

643

11.5.2 Textfelder leeren

646

11.5.3 Textfelder kennzeichnen

649

11.5.4 Prüfung auf numerischen Inhalt

651

11.5.5 Länge eines Textfeldes prüfen

653

11.5.6 Prüfen von und Rechnen mit Textfeldern

654

11.5.7 Prüfen und widerrufen

659

11.5.8 Eine AutoAusfüllen-Funktion programmieren

661

11.5.9 Rechtschreibprüfung vornehmen

664

11.5.10 Daten über ein Textfeld suchen

666

11.6 Programmierung von Listenfeldern

670

11.6.1 Listenfeld mit Tabellen füllen

670

11.6.2 Listenfeld mit Monaten füllen

673

11.6.3 Mehrspaltiges Listenfeld mit Daten aus Tabelle füllen

675

11.6.4 Listenfeld transponiert füllen

680

11.6.5 Listenfelder im Duett

683

11.6.6 Listenfeld und Textfelder im Zusammenspiel

687

11.7 Programmierung von Kombinationsfeldlisten

689

11.7.1 Dropdown mit Tagen füllen

689

11.7.2 Eindeutige Einträge im Dropdown anzeigen

691

11.7.3 Dropdowns synchronisieren

695

11.7.4 Dropdown und Listenfeld im Duett

698

11.8 Die Programmierung von Optionsschaltflächen

701

11.8.1 Mehrwertsteuersatz als Option anwenden

701

11.8.2 Optionsfelder und Listenfeld im Zusammenspiel

705

11.9 Die Programmierung von Kontrollkästchen

707

11.9.1 Kontrollkästchen über eine Tabelle speisen

708

11.9.2 Ansichtseinstellungen über Kontrollkästchen vornehmen

711

11.10 Die Programmierung von Bildelementen

715

11.10.1 Der eigene Bildbetrachter

715

11.11 Die Programmierung sonstiger Steuerelemente

719

11.11.1 Bilder in MultiPage laden

719

11.11.2 Umschaltfläche programmieren

721

11.11.3 Drehfeld programmieren

726

11.11.4 Die Programmierung des »ListView«-Steuerelements

728

11.11.5 Die Programmierung des »TreeView«-Steuerelements

734

11.11.6 Die Programmierung des »ProgressBar«-Steuerelements

737

11.12 Das Verwaltungstool

740

11.12.1 Die hinterlegte Datentabelle

740

11.12.2 Die beteiligten Steuerelemente

741

11.12.3 Vorbereitende Aufgaben

742

11.12.4 Daten suchen

743

11.12.5 Mit Klick auf das Listenfeld die Textfelder ausfüllen

745

11.12.6 Den Dialog initialisieren

747

11.12.7 Datensatz löschen

747

11.12.8 Datensatz ändern

748

11.12.9 Neue Kundennummer ermitteln

749

11.12.10 Datensatz anlegen

749

12 Excel im Umfeld von Office programmieren

753

12.1 Excel im Zusammenspiel mit PowerPoint

753

12.1.1 Excel-Bereich nach PowerPoint exportieren

753

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

756

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

758

12.1.4 PowerPoint-Folie als Objekt in Excel einbinden

760

12.1.5 Diagrammobjekte in eine Präsentation exportieren

762

12.2 Excel im Zusammenspiel mit Word

764

12.2.1 Excel-Bereich in Dokument exportieren

765

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

767

12.2.3 Markierten Bereich einer Excel-Tabelle in ein Dokument exportieren

771

12.2.4 Bereich als Grafik an einer bestimmten Stelle eines Dokuments einfügen

773

12.3 Excel im Zusammenspiel mit Outlook

775

12.3.1 Kontaktdaten aus Excel nach Outlook exportieren

776

12.3.2 Termine aus Excel in den Outlook-Kalender schieben

780

12.3.3 Aktive Tabelle aus Excel heraus versenden

784

12.3.4 Aktive Tabelle als Anhang aus Excel heraus versenden

785

12.3.5 Aktive Arbeitsmappe per E-Mail versenden

787

12.3.6 Alle Dokumente aus einem Verzeichnis per E-Mail versenden

790

12.4 Excel im Zusammenspiel mit Access

792

12.4.1 Toolfrage und Randbedingungen

793

12.4.2 Anforderungen an das Tool

793

12.4.3 Die Umsetzung der Kernfunktionen

793

12.4.4 Befüllung der UserForm mit den wichtigsten Daten

795

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

797

12.4.6 Suche nach Datum

803

12.4.7 Termine erfassen

805

12.4.8 Änderung von Terminen

807

12.4.9 Termine löschen

809

12.4.10 Felder löschen

810

12.5 Excel im Zusammenspiel mit dem Internet Explorer

811

12.5.1 Eine Internetseite aus Excel aufrufen

811

12.5.2 Texte übersetzen mit Google

812

12.5.3 Bild aus Internet laden und in UserForm anzeigen

814

12.5.4 Ein PDF aus dem Internet laden

816

13 Datenfelder, ADO, Dictionaries und Collections programmieren

817

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

817

13.1.1 Daten filtern und in einer anderen Tabelle ausgeben

818

13.1.2 Umsätze nach Datum verdichten

821

13.1.3 Umsätze nach Datum und Warengruppe verdichten

824

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

827

13.1.5 Daten aus einer Tabelle löschen

830

13.1.6 Top-Werte ermitteln

832

13.1.7 Mehrere Tabellen zusammenfassen

834

13.1.8 Eine Unikatsliste bilden

836

13.1.9 Excel-Daten per ADO verändern

839

13.2 Arbeiten mit Arrays

842

13.2.1 Aktionen im Arbeitsspeicher ausführen lassen

843

13.2.2 Bestimmte Daten aus einer Tabelle löschen

846

13.2.3 Daten konvertieren

849

13.3 Arbeiten mit dem »Dictionary«-Objekt

853

13.3.1 Daten verdichten

854

13.3.2 Bedingte Summierung mit mehreren Kriterien

857

13.3.3 Eine Unikatsliste erstellen

861

13.3.4 Anzahl von Bestellungen ermitteln

863

13.3.5 Doppelte Daten in einem Bereich ermitteln

868

13.4 Arbeiten mit Collections

870

13.4.1 Eindeutige Einträge über eine Collection bilden

871

13.4.2 Eine Collection aus einer Tabelle befüllen

874

14 Die Programmierung der Excel-Oberfläche

877

14.1 Die Programmierung von Kontextmenüs

877

14.1.1 Kontextmenüs deaktivieren

878

14.1.2 Das Zellen-Kontextmenü erweitern

879

14.1.3 Kontextmenü aufbauen (dreistufig)

881

14.1.4 Kontextmenü zurücksetzen

883

14.2 Die Ribbon-Programmierung

883

14.2.1 Der Custom UI Editor

883

14.2.2 Weitere wichtige Quellen und Hilfen

886

14.2.3 Ribbon mit Schaltflächen erstellen

887

14.2.4 Ribbon mit »ComboBox« erstellen

889

14.2.5 Ribbon mit bereits verfügbaren Funktionen bestücken

891

14.2.6 Den Backstage-Bereich programmieren

892

14.2.7 Eine Galerie mit Fotos erstellen

894

15 Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten

895

15.1 Kleinere Fehler beheben

895

15.1.1 Stimmt die Syntax?

895

15.1.2 Ist die Variablendefinition gegeben?

896

15.1.3 Objekt vorhanden?

896

15.1.4 Methode, Eigenschaft verfügbar?

897

15.2 Schwerwiegendere Fehler

897

15.2.1 Fehler im Vorfeld erkennen und reagieren

897

15.2.2 Fehler ignorieren

898

15.2.3 Fehlerursache ermitteln

898

15.2.4 Die Funktion »IsError«

899

15.3 Das Add-In MZ-Tools

900

15.3.1 Zeilennummern automatisch einfügen

901

15.3.2 Eine Fehlerbehandlung mit den MZ-Tools hinzufügen

902

15.4 Laufzeiten verkürzen

903

15.4.1 Variablen und Konstanten einsetzen

903

15.4.2 Berechnung und Bildschirmaktualisierung ausschalten

904

15.4.3 Integrierte Tabellenfunktionen anwenden

905

15.5 VBA-Projekte schützen

905

16 Typische Verarbeitungsaufgaben aus der Praxis

907

16.1 Daten übertragen

907

16.2 Daten im Batch verarbeiten

910

16.3 Daten verteilen

912

16.3.1 Die Tabellen entfernen

913

16.3.2 Die Verteilung der Zeilen auf die Tabellen

914

16.3.3 Die Plausibilität prüfen

917

16.3.4 Der Export der Tabellen

919

16.4 Berichtsfilterseiten erstellen

920

16.5 Daten löschen

923

16.5.1 Daten entfernen – Variante 1

924

16.5.2 Daten entfernen – Variante 2

924

16.5.3 Daten entfernen – Variante 3

925

16.6 Daten kennzeichnen

926

16.6.1 Doppelte Daten kennzeichnen (der Standard)

927

16.6.2 Doppelte Daten kennzeichnen (die Erweiterung)

929

16.6.3 Top-10-Werte aus einem Bereich ermitteln

931

16.7 Diagramme automatisch formatieren

934

16.8 Daten über den Einsatz von Wildcards suchen

938

16.9 Zwei identische Bereiche miteinander vergleichen

939

16.10 Suche nach einem Begriff unter Berücksichtigung der Formatierung

941

16.11 Automatische Sicherung von E-Mails in einer Access-Datenbank

942

16.12 Den Durchschnitt aus den Top-5-Werten berechnen

945

16.13 Automatische Erstellung von Arbeitsmappen auf Knopfdruck

947

16.14 Alle Formeln einer Tabelle schützen und verstecken

948

16.15 Eine Unikatsliste über den Einsatz von SQL erstellen

949

16.16 Erstellung eines Kalenders mit VBA – Schritt für Schritt

952

16.17 Kriterien für eine Mehrfachfilterung aus Zellen beziehen

954

16.18 Bestimmte Zeichenfolge in einem Bereich entfernen

955

16.19 Automatisch eine Kopie der Mappe erstellen

956

16.20 Einen Excel-Bereich als Objekt nach PowerPoint übertragen

957

16.21 Einen formatierten Text in eine PowerPoint-Folie übertragen

960

16.22 Top-Werte ermitteln und kennzeichnen

962

16.23 Daten aus einer geschlossenen Mappe ziehen

964

16.24 Ein PDF in Excel erstellen und direkt versenden

966

16.25 Daten verdichten

968

16.25.1 Daten verdichten mit Hilfe von SUMMEWENNS

969

16.25.2 Daten verdichten mit Hilfe einer SQL-Anweisung

970

16.25.3 Daten verdichten mit Hilfe des Datenfilters

972