1 Die Entwicklungsumgebung von Excel

31

1.1 Excel auf die Programmierung vorbereiten

31

1.1.1 Heruntersetzen der Sicherheitsstufe

31

1.1.2 Die Entwicklertools einbinden

32

1.2 Die Bestandteile der Entwicklungsumgebung

34

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

34

1.2.2 Der Direktbereich zum Testen verwenden

41

1.2.3 Das Überwachungsfenster

46

1.2.4 Das »Lokal«-Fenster

48

1.2.5 Die Symbolleiste »Bearbeiten«

49

1.2.6 Automatische Syntaxprüfung

54

1.2.7 Befehle in der nächsten Zeile fortsetzen

55

1.2.8 Automatische Anpassung der einzelnen Befehle

56

1.2.9 Schnelles Arbeiten über Tastenkombinationen

57

1.2.10 Der Objektkatalog

59

1.2.11 Der Makrorekorder

60

1.2.12 Sonstige wichtige Einstellungen in der Entwicklungsumgebung

65

1.2.13 Die Onlinehilfe

66

2 Datentypen, Variablen und Konstanten

69

2.1 Der Einsatz von Variablen

69

2.1.1 Regeln für die Syntax von Variablen

70

2.1.2 Variablen am Beginn des Makros deklarieren

71

2.1.3 Die wichtigsten Variablentypen

72

2.1.4 Variablendeklarationen erzwingen

73

2.1.5 Noch kürzere Deklaration von Variablen

74

2.1.6 Die unterschiedlichen Variablentypen

75

2.1.7 Typische Beispiele für den Einsatz von Variablen

77

2.1.8 Die Objektvariablen

83

2.2 Die Verwendung von Konstanten

88

2.2.1 Typische Aufgaben für die Verwendung von Konstanten

88

3 Die Sprachelemente von Excel-VBA

103

3.1 Bedingungen

103

3.2 Typische Aufgaben aus der Praxis

104

3.2.1 Wert in einer Spalte suchen

104

3.2.2 Liegt die aktive Zelle in einem vorgegebenen Bereich?

106

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

107

3.2.4 Spalteninhalte direkt nach der Eingabe umsortieren

108

3.2.5 Spalten mit Wochenenden kennzeichnen

110

3.3 Die Kurzform einer Bedingung

112

3.3.1 Den Doppelklick auf eine Zelle abfangen

113

3.4 Die Anweisung »Select Case« einsetzen

114

3.4.1 Zahlenwerte prüfen

115

3.4.2 Den Wochentag eines bestimmten Datums auslesen

117

3.4.3 Benotungen über einen Autotext durchführen

117

3.5 Die »Enum«-Anweisung

119

3.5.1 Umsatz klassifizieren mit »Enum«

120

3.6 Schleifen erstellen und verstehen

121

3.6.1 Die »For ... Next«-Schleife

121

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

138

3.6.3 Die Schleife »Do Until ... Loop«

148

3.6.4 Die Schleife »Do While ... Loop«

152

3.7 Sonstige Sprachelemente

155

3.7.1 Die Anweisung »With«

155

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

175

4.1.10 Einen eindeutigen Schlüssel aus mehreren Spalten basteln

177

4.2 Zellen, Rahmen und Schriften formatieren

179

4.2.1 Schriftart ermitteln

179

4.2.2 Schriftart ändern

180

4.2.3 Zelleninhalte löschen

183

4.2.4 Schriftfarbe teilweise ändern

183

4.2.5 Automatisch runden und formatieren

185

4.2.6 Zwei Bereiche miteinander vergleichen

187

4.2.7 Einen Bereich »mustern«

189

4.2.8 Einen Bereich einrahmen

191

4.2.9 Einen Bereich umrahmen

193

4.3 Die bedingte Formatierung von Excel

195

4.3.1 Eine bedingte Formatierung als Standard einstellen

195

4.3.2 Duplikate mit dem bedingten Format aufspüren

199

4.3.3 Wertgrößen über einen Datenbalken darstellen

200

4.3.4 Eine Farbskala definieren und einsetzen

201

4.3.5 Daten über Pfeilsymbole bewerten

203

4.3.6 Die Top-Werte in einem Bereich hervorheben

204

4.3.7 Die einzugebende Textlänge überwachen

205

4.3.8 Eine bedingte Formatierung mit Wertgrenzen ausstatten

207

4.3.9 Sparklines einsetzen

208

4.4 Bereiche und Zellen benennen

209

4.4.1 Einen Bereich benennen

210

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

216

4.4.8 Ein Zellen-Dropdown auf Basis einer benannten Liste anlegen

217

4.4.9 Benannte Bereiche löschen

219

4.4.10 Benutzerdefinierte Listen erstellen

220

4.5 Tabellenfunktionen einsetzen

221

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

221

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

223

4.5.3 Bedingte Summierung von Umsätzen

224

4.5.4 Bedingte Summierung von Umsätzen mit mehreren Kriterien

226

4.5.5 Den SVERWEIS im Makro einsetzen

228

4.5.6 Extremwerte finden und kennzeichnen

229

4.5.7 Prüfen, ob ein bestimmter Bereich leer ist

233

4.5.8 Einen Bereich mit Zahlenwerten mit vorangestellten Nullen auffüllen

234

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

235

4.5.10 Automatische Prüfung und Überwachung eines Budgets

237

4.5.11 Mussfelder in einer Tabelle überprüfen

239

4.5.12 Mittelwert über eine InputBox ermitteln

240

4.5.13 Werte oberhalb und unterhalb des Durchschnitts ermitteln und kennzeichnen

241

4.6 Matrixformeln in der Programmierung nutzen

243

4.6.1 Werte bedingt zählen

243

4.6.2 Werte bedingt summieren

245

4.6.3 Mittelwert bilden ohne Berücksichtigung von Nullen

246

4.6.4 Extremwerte bedingt bilden

247

4.6.5 Den am meisten genannten Wert ermitteln

247

4.7 Texte und Zahlen manipulieren

248

4.7.1 Texte finden und umstellen

248

4.7.2 Mehrere Spalten anhand von Trennzeichen splitten

251

4.7.3 Daten nach einem Datentransfer bereinigen

253

4.7.4 Zeichenfolge(n) aus Zellen entfernen

254

4.7.5 Buchstaben aus Zellen entfernen

256

4.7.6 Dateinamen aus Pfad extrahieren

258

4.7.7 Alle Formelzellen einer Tabelle schützen

259

4.8 Gültigkeitsprüfung in Excel

260

4.8.1 Gültigkeitskriterien erstellen

261

4.8.2 Zellen mit Gültigkeitsfunktion auswählen

263

4.8.3 Datumsgrenzen festlegen

264

4.8.4 Nur Werktage für die Eingabe zulassen

265

4.8.5 Eine Gültigkeitsliste aus einem Datenfeld befüllen

267

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

269

4.8.7 Ein Zellen-Dropdown aus einer Konstanten befüllen

270

4.8.8 Einen Standardeintrag für Zellen-Dropdowns setzen

272

4.8.9 Gültigkeitskriterien löschen

273

4.9 Kommentare in Excel einsetzen

273

4.9.1 Kommentare einfügen

274

4.9.2 Kommentare im Direktfenster auslesen

275

4.9.3 Kommentare formatieren

276

4.9.4 Kommentare aus der aktiven Tabelle löschen

280

4.9.5 Alle Kommentare einer Arbeitsmappe löschen

280

4.9.6 Einen Kommentar einem Namen in einer Arbeitsmappe zuweisen

281

4.9.7 Den Autor von Kommentaren anpassen

282

4.9.8 Kommentarkennzeichnung ein- und ausschalten

283

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

285

5 Spalten und Zeilen programmieren

287

5.1 Zeilen und Spalten ansprechen, ansteuern und anpassen

287

5.1.1 Mehrere Zeilen auf dem Tabellenblatt markieren

288

5.1.2 Mehrere Spalten auf dem Tabellenblatt markieren

288

5.1.3 Zeilen und Spalten markieren

289

5.1.4 Markierte Spalten zählen

290

5.1.5 Letzte freie Zelle in Spalte identifizieren

291

5.1.6 Anzahl der benutzten Spalten und Zeilen ermitteln

291

5.1.7 Zeilen und Spalten im umliegenden Bereich zählen

293

5.1.8 Zeilenhöhe und Spaltenbreite einstellen

294

5.2 Zeilen einfügen und löschen

297

5.2.1 Zeile einfügen

297

5.2.2 Mehrere Zeilen einfügen

298

5.2.3 Leere Zeilen dynamisch einfügen

298

5.2.4 Wirklich leere Zeilen löschen

301

5.2.5 Doppelte Sätze löschen

301

5.3 Spalten einfügen, löschen und bereinigen

303

5.3.1 Spalte einfügen

303

5.3.2 Mehrere Spalten einfügen

303

5.3.3 Spalte löschen

304

5.3.4 Mehrere Spalten löschen

304

5.4 Zeilen ein- und ausblenden

305

5.4.1 Leere Zeilen ausblenden

305

5.4.2 Alle Zellen einblenden

306

5.5 Spalten ein- und ausblenden

306

5.5.1 Bestimmte Spalten ausblenden

306

5.6 Spalten und Zeilen formatieren

307

5.7 Daten sortieren

309

5.7.1 Daten sortieren mit der klassischen Variante

309

5.7.2 Daten sortieren mit der modernen Methode

310

5.7.3 Daten sortieren nach Farbe der Zellen

313

5.7.4 Daten nach einer Überschrift spaltenweise sortieren

314

5.8 Spalte(n) vergleichen

315

5.8.1 Zelle mit Spalte vergleichen

315

5.8.2 Spalten über eine bedingte Formatierung miteinander vergleichen

316

5.9 Zeilen filtern

318

5.9.1 AutoFilter aktivieren und deaktivieren

318

5.9.2 Filterkriterien setzen

320

5.9.3 Nur Texte filtern

323

5.9.4 Daten filtern, die eine Zeichenfolge enthalten

324

5.9.5 Der Top-10-Filter

325

5.9.6 Filtern nach Zellenfarbe

327

5.9.7 Gefilterte Zeilen entfernen

328

5.9.8 Filterkriterien als Datenfeld übergeben

329

5.9.9 Alle gesetzten Filter sichtbarer machen

335

5.9.10 Wie lauten die Filterkriterien?

337

5.9.11 Doppelte Werte mit dem Spezialfilter ermitteln

338

5.9.12 Doppelte Werte mit dem »Dictionary«-Objekt entfernen

340

5.9.13 Daten über einen Kriterienbereich filtern

341

5.9.14 Mehrere Spalten über einen Kriterienbereich filtern

343

5.9.15 Wildcards im Spezialfilter einsetzen

344

5.9.16 Filtern von Umsätzen in einem vorgegebenen Zeitraum

346

5.9.17 Gefilterte Daten transferieren

348

5.10 Zeilen über das Teilergebnis gruppieren

350

6 Tabellen und Diagramme programmieren

353

6.1 Tabellen einfügen

353

6.2 Tabellenblätter benennen

354

6.2.1 Eine neue Mappe erstellen, zwölf Monatstabellen anlegen und benennen

355

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

356

6.2.3 Tabelle einfügen und gleichzeitig benennen

357

6.3 Tabellen markieren

357

6.4 Tabellenblätter gruppieren

359

6.4.1 Mehrere Tabellen gruppieren

359

6.4.2 Alle Tabellen gruppieren

359

6.4.3 Gruppierte Tabellen übertragen

360

6.4.4 Gruppierte Tabellen ermitteln

360

6.5 Tabellenblätter löschen

361

6.5.1 Eine Tabelle löschen

361

6.5.2 Bestimmte Tabellen aus einer Mappe entfernen

363

6.5.3 Tabellen mit gefärbten Registerlaschen entfernen

364

6.5.4 Leere Tabellen aus Arbeitsmappen entfernen

365

6.6 Tabellenblätter ein- und ausblenden

365

6.6.1 Tabellenblätter sicher ausblenden

366

6.6.2 Tabellen je nach Status ein- oder ausblenden

367

6.6.3 Alle Tabellenblätter anzeigen

367

6.6.4 Alle Tabellen außer der aktiven Tabelle ausblenden

368

6.7 Tabellenblätter schützen

369

6.7.1 Tabellenschutz aufheben

370

6.7.2 Alle Tabellen einer Arbeitsmappe schützen

370

6.7.3 Weitere Schutzfunktionen ab Excel 2002

371

6.7.4 Passwort – Einstellungsdialog mit verschlüsseltem Passwort aufrufen

372

6.8 Tabellen einstellen

373

6.8.1 Registerlaschen ein- und ausblenden

373

6.8.2 Tabellenansicht anpassen

374

6.8.3 Eine einheitliche Zoomeinstellung vornehmen

374

6.8.4 Tabellenblätter sortieren

375

6.8.5 Kopf- und Fußzeilen einrichten

376

6.8.6 Druckbereiche festlegen

385

6.8.7 Das Tabellengitternetz ein- und ausschalten

386

6.8.8 Zeilen- und Spaltenköpfe ein- und ausblenden

386

6.8.9 Cursor einstellen auf Zelle A1

387

6.9 Tabellenblätter drucken und PDF erstellen

387

6.9.1 Mehrere Kopien drucken

388

6.9.2 Markierte Bereiche drucken

388

6.9.3 Mehrere Tabellenblätter drucken

389

6.9.4 Tabelle als PDF ablegen

389

6.10 Tabelleninhaltsverzeichnis erstellen

390

6.11 Intelligente Tabellen

392

6.11.1 Tabelle umwandeln

393

6.11.2 Tabelle um eine Spalte ergänzen

394

6.11.3 Tabelle um eine Zeile ergänzen

395

6.11.4 Tabelle filtern

396

6.11.5 Tabellen sortieren

397

6.11.6 Tabelle um Ergebniszeile erweitern

399

6.11.7 Tabelle entfernen

399

6.12 Pivot-Tabellen erstellen

400

6.12.1 Pivot-Tabellen aktualisieren

404

6.12.2 Eine einzelne Pivot-Tabelle aktualisieren

404

6.12.3 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

404

6.12.4 Alle Pivot-Tabellen in einer Arbeitsmappe aktualisieren

405

6.12.5 Pivot-Tabellen dynamisch erweitern

405

6.12.6 Pivot-Tabellen formatieren

406

6.12.7 Slicer einfügen und bedienen

407

6.13 Diagramme programmieren

409

6.13.1 Umsätze in einem Säulendiagramm darstellen

410

6.13.2 Tagesumsätze im Liniendiagramm anzeigen

415

6.13.3 Tagesgenaue Formatierung im Punktdiagramm

417

6.13.4 Diagramme als Grafiken speichern

419

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

421

6.13.6 Linienstärke unabhängig von den Markierungssymbolen formatieren

422

6.13.7 Sparklines automatisch erstellen

424

6.14 Tabellen blitzschnell vergleichen und Unterschiede dokumentieren

427

7 Arbeitsmappen und Dateien programmieren

433

7.1 Arbeitsmappen ansprechen

433

7.2 Arbeitsmappen anlegen

434

7.2.1 Eine Arbeitsmappe auf Basis einer Dokumentvorlage erstellen

434

7.2.2 Arbeitsmappe mit x Tabellen anlegen

435

7.2.3 Mappe mit Wochentabellen anlegen

436

7.3 Arbeitsmappen speichern

437

7.3.1 Arbeitsmappe unter aktuellem Tagesdatum speichern

438

7.3.2 Alle Tabellen einer Mappe als eigenständige Mappen speichern

438

7.3.3 Mappe erstellen und »Speichern unter«-Dialog aufrufen

440

7.3.4 Individuellen Speichern-Dialog aufrufen

441

7.3.5 Kopie der aktuellen Mappe zur Laufzeit erstellen

442

7.4 Arbeitsmappen öffnen

443

7.4.1 Die Argumente der Methode »Open«

443

7.4.2 Mehrere Arbeitsmappen öffnen

444

7.4.3 Die aktuellste Datei in einem Verzeichnis öffnen

445

7.4.4 Regelmäßig das Vorhandensein der Datei prüfen

446

7.4.5 Alle verknüpften Mappen automatisch öffnen

447

7.5 Arbeitsmappen schließen

448

7.5.1 Arbeitsmappe schließen – Änderungen akzeptieren

448

7.5.2 Alle Arbeitsmappen bis auf eine schließen

449

7.6 Arbeitsmappe löschen

450

7.6.1 Arbeitsmappe nach Verfallsdatum löschen

451

7.6.2 Alle Excel-Mappen in einem Verzeichnis löschen

451

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

452

7.7 Arbeitsmappen drucken

453

7.7.1 Nur bestimmte Tabellen drucken

453

7.7.2 Alle Mappen eines Verzeichnisses drucken

454

7.7.3 Nur sichtbare Blätter ausdrucken

455

7.8 Dokumenteigenschaften verarbeiten

456

7.8.1 Dokumenteigenschaftsnamen abfragen

457

7.8.2 Letztes Speicherdatum abfragen

458

7.8.3 Erstelldatum herausfinden und manipulieren

458

7.8.4 Zugriffsdaten einer Arbeitsmappe ermitteln

459

7.8.5 Eigene Dokumenteigenschaften verwenden

460

7.9 Arbeitsmappen und Verknüpfungen

461

7.9.1 Verknüpfungen in Hyperlinks umwandeln

462

7.9.2 Verknüpfungen aus der Arbeitsmappe entfernen

463

7.9.3 Verknüpfungen ändern

465

7.9.4 Verknüpfungen aktualisieren

466

7.10 Arbeitsmappe durchsuchen

467

7.11 Arbeitsmappen miteinander vergleichen

468

7.12 Arbeitsmappenübersicht erstellen

471

7.13 Textdateien importieren

473

7.13.1 Eine durch Semikolon getrennte CSV-Datei öffnen

473

7.13.2 Eine durch Tabstopps getrennte Textdatei öffnen

474

7.13.3 Eine Textdatei mit festen Spaltenbreiten öffnen

476

7.13.4 Eine Datenverbindung zu einer Textdatei mit Trennzeichen herstellen

478

7.13.5 Eine Datenverbindung zu einer Textdatei mit festen Spaltenbreiten herstellen

480

7.13.6 Textdateien zeilenweise einlesen

482

7.14 Makros für das Dateimanagement

483

7.14.1 Ein Jahresverzeichnis automatisch anlegen

484

7.14.2 Eine bestimmte Datei nach Rückfrage löschen

485

7.14.3 Einen Ordner archivieren

486

7.14.4 Eine bestimmte Datei kopieren

488

7.14.5 Ordner anlegen und entfernen

489

7.14.6 Verzeichnisstruktur in einer Tabelle anzeigen

490

8 Eigene Funktionen und reguläre Ausdrücke

495

8.1 Benutzerdefinierte Funktionen

495

8.1.1 Aktive Arbeitsmappe ermitteln

496

8.1.2 Aktives Tabellenblatt ermitteln

497

8.1.3 Ist eine Tabelle leer?

498

8.1.4 Ist eine Tabelle geschützt?

498

8.1.5 Befinden sich Daten in einer bestimmten Spalte?

499

8.1.6 Den letzten Wert einer Spalte ermitteln

500

8.1.7 Den letzten Wert einer Zeile ermitteln

500

8.1.8 Den aktiven Bearbeiter identifizieren

501

8.1.9 Funktion zum Umsetzen von Schulnoten

501

8.1.10 Rangfolge als Text ausgeben

502

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

503

8.1.12 Enthält eine Zelle einen Kommentar?

504

8.1.13 Ist eine bestimmte Zelle verbunden?

504

8.1.14 Initialen aus Namen erstellen

505

8.1.15 Nur Zellen mit Fettdruck addieren

506

8.1.16 Mit Uhrzeiten rechnen

507

8.1.17 Erweitertes Runden durchführen

509

8.1.18 Schnelles Umrechnen von Geschwindigkeiten

510

8.1.19 Extremwerte berechnen

510

8.1.20 Erste Ziffer in einer Zelle ermitteln

512

8.1.21 Buchstaben aus Zellen entfernen

514

8.1.22 Anzahl der Ziffern einer Zelle ermitteln

515

8.1.23 Römische Zahlen in arabische umwandeln

515

8.1.24 Einen Kommentartext in eine Zelle holen

517

8.1.25 Angabe eines optionalen Parameters bei einer Funktion

518

8.1.26 Leerzeichen in einen String integrieren

519

8.2 Jahresbericht mit nur einer Funktion blitzschnell erstellen

520

8.3 Modulare Funktionen schreiben

526

8.3.1 Dateien in einem Verzeichnis zählen

526

8.3.2 Fehlerüberwachung umleiten

527

8.3.3 Prüfen, ob eine bestimmte Datei vorhanden ist

528

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

529

8.3.5 Prüfen, ob eine Datei gerade bearbeitet wird

530

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

531

8.3.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln

531

8.3.8 Den letzten Wert einer Spalte ermitteln

534

8.3.9 Grafikelemente in einem definierten Bereich löschen

535

8.3.10 Kalenderwoche nach DIN ermitteln

537

8.3.11 Unerwünschte Zeichen aus Zellen entfernen

537

8.4 Funktionen verfügbar machen

539

8.4.1 Speichern der Funktionen in der persönlichen Arbeitsmappe

539

8.4.2 Speichern der Funktionen in einem Add-in

540

8.4.3 Ein Add-in einbinden

541

8.5 Mit regulären Ausdrücken programmieren

541

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

542

8.5.2 Bestimmte Zeichenfolgen in Texten finden

545

8.5.3 Spezielle Zeichen nutzen

546

8.5.4 Zeichenfolgen aus Texten extrahieren

548

8.5.5 Eine E-Mail-Adresse prüfen

550

8.5.6 Konten prüfen

552

8.5.7 Zahlen aus Texten extrahieren

553

9 Ereignisse programmieren

555

9.1 Ereignisse für die Arbeitsmappe

555

9.1.1 Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-Ereignissen

556

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

557

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

559

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

564

9.1.5 Letztes Bearbeitungsdatum festhalten (»Workbook_BeforeSave«)

565

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

565

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

566

9.2 Ereignisse für das Tabellenblatt

567

9.2.1 Allgemeine Vorgehensweise bei der Einstellung von Tabellenereignissen

567

9.2.2 Die wichtigsten Ereignisse für Tabellen im Überblick

568

9.2.3 Passworteingabe beim Aktivieren einer Tabelle (»Worksheet_Activate«)

569

9.2.4 Vergleich von zwei Spalten (»Worksheet_Change«)

569

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

571

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

573

9.2.7 Die Spaltensumme überwachen (»Worksheet_Change«)

574

9.2.8 Nur einmalige Eingabe zulassen (»Worksheet_Change«)

575

9.2.9 Die Eingabe von Dubletten verhindern (»Worksheet_Change«)

576

9.2.10 Eingabe verhindern (»Worksheet_SelectionChange«)

577

9.2.11 Markierung überwachen (»Worksheet_SelectionChange«)

578

9.2.12 Mausklicks überwachen (»Worksheet_BeforeRightClick«)

579

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

580

9.3 Reaktion auf Tastendruck

580

9.3.1 Texte einfügen

582

9.3.2 Blattsperre ohne Blattschutz erstellen

584

9.3.3 Nur Werte einfügen

585

9.4 Zeitsteuerung in Excel

585

9.4.1 Regelmäßig die Uhrzeit anzeigen

586

9.4.2 Die Zeit läuft ...

587

9.4.3 Zellen blinken lassen

588

9.4.4 Eingaben nach Ablauf von 1 Minute löschen

589

10 Die VBE-Programmierung

591

10.1 Die VBE-Bibliothek einbinden

592

10.1.1 Die VBE-Bibliothek deaktivieren

593

10.1.2 Weitere Bibliotheken einbinden

594

10.1.3 Objektbibliotheken deaktivieren

595

10.1.4 Informationen zu Objektbibliotheken ausgeben

595

10.1.5 VBE-Editor aufrufen

596

10.1.6 Das Direktfenster aufrufen

597

10.2 Die VBE ein- und ausschalten

597

10.2.1 Neue Module einfügen

598

10.2.2 Einzelne Module löschen

599

10.3 Einzelnes Makro löschen

599

10.4 Alle Makros aus einer Arbeitsmappe entfernen

600

10.4.1 Module mit Makros bestücken

601

10.4.2 Makro zeilenweise in ein Modul übertragen

602

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

603

10.4.4 Export von VBA-Modulen in Textdateien

604

10.5 Identifikation von Komponenten

605

10.6 Ein bestimmtes Makro auskommentieren

606

10.7 Das Direktfenster löschen

607

10.8 Den Status des VBA-Projekts abfragen

607

10.9 Makros und Ereignisse dokumentieren

608

11 Dialoge, Meldungen und UserForms programmieren

611

11.1 »MsgBox«-Meldung

612

11.1.1 Welche Schaltfläche wurde angeklickt?

614

11.1.2 Löschrückfrage

614

11.2 Die »InputBox«-Eingabemaske

615

11.2.1 Einen Suchbegriff über eine InputBox abfragen

616

11.2.2 Abfrage des Spaltenbuchstabens

618

11.3 Integrierte Dialoge einsetzen

619

11.3.1 Den »Öffnen«-Dialog aufrufen

620

11.3.2 Den Dialog »Optionen« aufrufen

623

11.4 Eigene UserForms entwerfen

623

11.4.1 UserForm einfügen

623

11.4.2 UserForm beschriften

624

11.4.3 UserForm aufrufen

625

11.4.4 Die verfügbaren Steuerelemente

625

11.4.5 Steuerelemente einfügen

627

11.4.6 Die wichtigsten Eigenschaften

628

11.4.7 Ereignisse einstellen

631

11.5 Programmierung von Textfeldern

631

11.5.1 Passwort über einen Dialog abfragen

632

11.5.2 Textfelder leeren

636

11.5.3 Textfelder kennzeichnen

638

11.5.4 Prüfung auf numerischen Inhalt

640

11.5.5 Länge eines Textfeldes prüfen

642

11.5.6 Prüfen von und Rechnen mit Textfeldern

643

11.5.7 Prüfen und widerrufen

648

11.5.8 Eine AutoAusfüllen-Funktion programmieren

650

11.5.9 Rechtschreibprüfung vornehmen

653

11.5.10 Daten über ein Textfeld suchen

655

11.6 Programmierung von Listenfeldern

659

11.6.1 Listenfeld mit Tabellen füllen

659

11.6.2 Listenfeld mit Monaten füllen

662

11.6.3 Mehrspaltiges Listenfeld mit Daten aus Tabelle füllen

664

11.6.4 Listenfeld transponiert füllen

669

11.6.5 Listenfelder im Duett

671

11.6.6 Listenfeld und Textfelder im Zusammenspiel

675

11.7 Programmierung von Kombinationsfeldlisten

678

11.7.1 Dropdown mit Tagen füllen

678

11.7.2 Eindeutige Einträge im Dropdown anzeigen

680

11.7.3 Dropdowns synchronisieren

684

11.7.4 Dropdown und Listenfeld im Duett

687

11.8 Die Programmierung von Optionsschaltflächen

690

11.8.1 Mehrwertsteuersatz als Option anwenden

690

11.8.2 Optionsfelder und Listenfeld im Zusammenspiel

694

11.9 Die Programmierung von Kontrollkästchen

696

11.9.1 Kontrollkästchen über eine Tabelle speisen

697

11.9.2 Ansichtseinstellungen über Kontrollkästchen vornehmen

700

11.10 Die Programmierung von Bildelementen

703

11.10.1 Der eigene Bildbetrachter

704

11.11 Die Programmierung sonstiger Steuerelemente

708

11.11.1 Bilder in MultiPage laden

708

11.11.2 Umschaltfläche programmieren

710

11.11.3 Drehfeld programmieren

714

11.11.4 Die Programmierung des »ListView«-Steuerelements

717

11.11.5 Die Programmierung des »TreeView«-Steuerelements

723

11.11.6 Die Programmierung des »ProgressBar«-Steuerelements

727

11.12 Das Verwaltungstool

729

11.12.1 Die hinterlegte Datentabelle

730

11.12.2 Die beteiligten Steuerelemente

731

11.12.3 Vorbereitende Aufgaben

731

11.12.4 Daten suchen

733

11.12.5 Mit Klick auf das Listenfeld die Textfelder ausfüllen

735

11.12.6 Den Dialog initialisieren

736

11.12.7 Datensatz löschen

737

11.12.8 Datensatz ändern

738

11.12.9 Neue Kundennummer ermitteln

739

11.12.10 Datensatz anlegen

739

11.13 Ist das ListView-Steuerelement die bessere Listbox?

741

11.13.1 Die Befüllung des ListView-Elements

742

11.13.2 Das Sortieren von Spalten in einem ListView-Element

746

11.13.3 Markierte Elemente in Tabelle zurückschreiben

747

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

753

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

755

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 einer bestimmten Stelle eines Dokuments einfügen

771

12.3 Excel im Zusammenspiel mit Outlook

773

12.3.1 Kontaktdaten aus Excel nach Outlook exportieren

773

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

784

12.3.6 Alle Dokumente aus einem Verzeichnis per E-Mail versenden

787

12.4 Excel im Zusammenspiel mit Access

790

12.4.1 Toolfrage und Randbedingungen

790

12.4.2 Anforderungen an das Tool

790

12.4.3 Die Umsetzung der Kernfunktionen

791

12.4.4 Befüllung der UserForm mit den wichtigsten Daten

792

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

794

12.4.6 Suche nach Datum

800

12.4.7 Termine erfassen

802

12.4.8 Änderung von Terminen

804

12.4.9 Termine löschen

806

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

809

13 Datenfelder, ADO, Dictionaries und Collections programmieren

813

13.1 Aufgaben mithilfe von ADO und SQL-Statements lösen

813

13.1.1 Daten filtern und in einer anderen Tabelle ausgeben

814

13.1.2 Umsätze nach Datum verdichten

817

13.1.3 Umsätze nach Datum und Warengruppe verdichten

820

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

823

13.1.5 Daten aus einer Tabelle löschen

825

13.1.6 Top-Werte ermitteln

828

13.1.7 Mehrere Tabellen zusammenfassen

830

13.1.8 Eine Unikatsliste bilden

832

13.1.9 Excel-Daten per ADO verändern

835

13.2 Arbeiten mit Arrays

838

13.2.1 Aktionen im Arbeitsspeicher ausführen lassen

839

13.2.2 Bestimmte Daten aus einer Tabelle löschen

842

13.2.3 Daten konvertieren

845

13.3 Arbeiten mit dem »Dictionary«-Objekt

849

13.3.1 Daten verdichten

850

13.3.2 Bedingte Summierung mit mehreren Kriterien

853

13.3.3 Eine Unikatsliste erstellen

857

13.3.4 Anzahl von Bestellungen ermitteln

859

13.3.5 Doppelte Daten in einem Bereich ermitteln

864

13.4 Arbeiten mit Collections

865

13.4.1 Eindeutige Einträge über eine Collection bilden

866

13.4.2 Eine Collection aus einer Tabelle befüllen

869

13.5 Intelligente Arrays mit Zusatzfunktionen

871

13.5.1 Stack füllen und prüfen

871

13.5.2 Eine SortedList füllen und übergeben

873

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 Zellenkontextmenü erweitern

879

14.1.3 Kontextmenü aufbauen (dreistufig)

881

14.1.4 Kontextmenü zurücksetzen

882

14.2 Die Menübandprogrammierung

883

14.2.1 Der Custom UI Editor

883

14.2.2 Weitere wichtige Quellen und Hilfen

885

14.2.3 Menüband mit Schaltflächen erstellen

887

14.2.4 Menüband mit »ComboBox« erstellen

888

14.2.5 Menüband mit bereits verfügbaren Funktionen bestücken

890

14.2.6 Den Backstage-Bereich programmieren

892

14.2.7 Eine Galerie mit Fotos erstellen

893

15 Fehlerbehandlung, Tuning und Schutz von VBA-Projekten sowie Support durch KI

895

15.1 Kleinere Fehler beheben

895

15.1.1 Stimmt die Syntax?

896

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

15.6 KI und Excel-VBA

906

15.6.1 Codevorschläge und Generierung von vollständigem Code

906

15.6.2 Fehlererkennung und Debugging

908

15.6.3 Codeübersetzung

910

15.6.4 Codeanalyse und Optimierung, Fehlerbehandlung integrieren, Codehygiene

911

15.6.5 Dokumentation und Kommentierung

912

15.6.6 Codeverständnis

913

15.6.7 Recherche und Weiterbildung

913

15.7 Mein Fazit

914

16 Typische Verarbeitungsaufgaben aus der Praxis

915

16.1 Daten übertragen

915

16.2 Daten im Batch verarbeiten

918

16.3 Daten verteilen

920

16.3.1 Die Tabellen entfernen

921

16.3.2 Die Verteilung der Zeilen auf die Tabellen

922

16.3.3 Die Plausibilität prüfen

925

16.3.4 Der Export der Tabellen

927

16.4 Berichtsfilterseiten erstellen

928

16.5 Daten löschen

931

16.5.1 Daten entfernen – Variante 1

931

16.5.2 Daten entfernen – Variante 2

932

16.5.3 Daten entfernen – Variante 3

933

16.6 Daten kennzeichnen

934

16.6.1 Doppelte Daten kennzeichnen (der Standard)

935

16.6.2 Doppelte Daten kennzeichnen (die Erweiterung)

936

16.6.3 Top-10-Werte aus einem Bereich ermitteln

938

16.7 Diagramme automatisch formatieren

941

16.8 Daten mithilfe von Wildcards finden

945

16.9 Zwei identische Bereiche miteinander vergleichen

946

16.10 Suche nach einem Begriff unter Berücksichtigung der Formatierung

948

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

949

16.12 Einen Durchschnitt aus den Top-5-Werten ermitteln

952

16.13 Arbeitsmappen auf Knopfdruck automatisch erstellen

954

16.14 Alle Formeln einer Tabelle schützen und verstecken

955

16.15 Eine Unikatsliste über den Einsatz von SQL erstellen

956

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

959

16.17 Kriterien für eine Mehrfachfilterung aus Zellen beziehen

961

16.18 Bestimmte Zeichenfolge in einem Bereich entfernen

962

16.19 Automatisch eine Kopie der Mappe erstellen

963

16.20 Einen Excel-Bereich als Objekt nach PowerPoint übertragen

964

16.21 Einen formatierten Text in eine PowerPoint-Folie übertragen

966

16.22 Top-Werte ermitteln und kennzeichnen

968

16.23 Daten aus einer geschlossenen Mappe ziehen

970

16.24 Ein PDF in Excel erstellen und direkt versenden

973

16.25 Daten verdichten

974

16.25.1 Daten verdichten mithilfe von SUMMEWENNS

976

16.25.2 Daten mithilfe einer SQL-Anweisung verdichten

977

16.25.3 Daten verdichten mithilfe des Datenfilters

979

16.26 Daten in Mappen aktualisieren, ohne diese zu öffnen

980

16.27 Eine Suchfunktion für eine ListBox erstellen

983

16.28 Mehrere Bilder dynamisch in eine Tabelle einfügen (Bildergalerie)

986

16.29 Bedingte Summierung direkt im Arbeitsspeicher vornehmen

989

16.30 Über zwei Arrays Jahresumsätze pro Monat auswerten

994

16.31 Bedingte Summierung mit mehreren Kriterien im Speicher durchführen

997

16.32 Pfeilsymbol oberhalb einer bestimmten Säule in einem Diagramm einfügen

1001

16.33 Excel erweitern – doppelte Werte finden und unterschiedlich kennzeichnen

1004

16.34 Bestimmte Zeilen übertragen – Kriterien dynamisch zusammenstellen

1007

16.35 Der Vollautomat – alle Dateien eines Verzeichnisses schnell verarbeiten

1010

16.36 Eine Mehrfachsuche in einer Tabelle mit einem Array beschleunigen

1013