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

39

1.2.3 Das Überwachungsfenster

44

1.2.4 Das »Lokal«-Fenster

46

1.2.5 Die Symbolleiste »Bearbeiten«

47

1.2.6 Automatische Syntaxprüfung

52

1.2.7 Befehle in der nächsten Zeile fortsetzen

53

1.2.8 Automatische Anpassung der einzelnen Befehle

54

1.2.9 Schnelles Arbeiten über Tastenkombinationen

55

1.2.10 Der Objektkatalog

57

1.2.11 Der Makrorekorder

58

1.2.12 Sonstige wichtige Einstellungen in der Entwicklungsumgebung

63

1.2.13 Die Onlinehilfe

65

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

75

2.1.8 Die Objektvariablen

81

2.2 Die Verwendung von Konstanten

86

2.2.1 Typische Aufgaben für die Verwendung von Konstanten

86

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«

149

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

163

4.1 Zahlenformat einstellen und/oder konvertieren

163

4.1.1 Zahlenformate einstellen (Datum und Zahl)

163

4.1.2 Zahlenformate einstellen (Text)

165

4.1.3 Zahlenformate übertragen

167

4.1.4 Zellen mit Nullen auffüllen

167

4.1.5 Einheitliches Datumsformat einstellen

169

4.1.6 Unerwünschte führende und nachgestellte Leerzeichen entfernen

171

4.1.7 Korrektur nach fehlerhaftem Import von Daten

173

4.1.8 Die Position des Minuszeichens umstellen

175

4.1.9 Daten umschlüsseln

177

4.1.10 Einen eindeutigen Schlüssel aus mehreren Spalten basteln

179

4.2 Zellen, Rahmen und Schriften formatieren

182

4.2.1 Schriftart ermitteln

182

4.2.2 Schriftart ändern

182

4.2.3 Zelleninhalte löschen

185

4.2.4 Schriftfarbe teilweise ändern

186

4.2.5 Automatisch runden und formatieren

188

4.2.6 Zwei Bereiche miteinander vergleichen

190

4.2.7 Einen Bereich »mustern«

192

4.2.8 Einen Bereich einrahmen

194

4.2.9 Einen Bereich umrahmen

196

4.3 Die bedingte Formatierung von Excel

198

4.3.1 Eine bedingte Formatierung als Standard einstellen

198

4.3.2 Duplikate mit dem bedingten Format aufspüren

202

4.3.3 Wertgrößen über einen Datenbalken darstellen

203

4.3.4 Eine Farbskala definieren und einsetzen

205

4.3.5 Daten über Pfeilsymbole bewerten

206

4.3.6 Die Top-Werte in einem Bereich hervorheben

207

4.3.7 Die einzugebende Textlänge überwachen

209

4.3.8 Eine bedingte Formatierung mit Wertgrenzen ausstatten

210

4.3.9 Sparklines einsetzen

212

4.4 Bereiche und Zellen benennen

213

4.4.1 Einen Bereich benennen

213

4.4.2 Mehrere Zellen einzeln benennen

215

4.4.3 Konstante als Namen vergeben

216

4.4.4 Verwendete Namen auslesen

217

4.4.5 Versteckte Namen sichtbar machen

218

4.4.6 Einen geheimen Namen anlegen

219

4.4.7 Einen dynamischen Namen anlegen

219

4.4.8 Ein Zellen-Dropdown auf Basis einer benannten Liste anlegen

221

4.4.9 Eine Prüfung auf Namen vornehmen

222

4.4.10 Benannte Bereiche löschen

224

4.4.11 Benutzerdefinierte Listen erstellen

225

4.5 Tabellenfunktionen einsetzen

226

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

227

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

228

4.5.3 Bedingte Summierung von Umsätzen

230

4.5.4 Bedingte Summierung von Umsätzen mit mehreren Kriterien

231

4.5.5 Den SVERWEIS im Makro einsetzen

233

4.5.6 Extremwerte finden und kennzeichnen

235

4.5.7 Prüfen, ob ein bestimmter Bereich leer ist

238

4.5.8 Einen Bereich mit Zahlenwerten mit vorangestellten Nullen auffüllen

239

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

240

4.5.10 Automatische Prüfung und Überwachung eines Budgets

242

4.5.11 Mussfelder in einer Tabelle überprüfen

244

4.5.12 Mittelwert über eine InputBox ermitteln

245

4.5.13 Werte oberhalb und unterhalb des Durchschnitts ermitteln und kennzeichnen

247

4.6 Matrixformeln in der Programmierung nutzen

248

4.6.1 Werte bedingt zählen

249

4.6.2 Werte bedingt summieren

250

4.6.3 Mittelwert bilden ohne Berücksichtigung von Nullen

251

4.6.4 Extremwerte bedingt bilden

252

4.6.5 Den am meisten genannten Wert ermitteln

252

4.7 Texte und Zahlen manipulieren

253

4.7.1 Texte finden und umstellen

253

4.7.2 Mehrere Spalten anhand von Trennzeichen splitten

256

4.7.3 Daten nach einem Datentransfer bereinigen

258

4.7.4 Zeichenfolge(n) aus Zellen entfernen

259

4.7.5 Buchstaben aus Zellen entfernen

261

4.7.6 Dateinamen aus Pfad extrahieren

263

4.7.7 Alle Formelzellen einer Tabelle schützen

264

4.8 Gültigkeitsprüfung in Excel

265

4.8.1 Gültigkeitskriterien erstellen

266

4.8.2 Zellen mit Gültigkeitsfunktion auswählen

268

4.8.3 Datumsgrenzen festlegen

269

4.8.4 Nur Werktage für die Eingabe zulassen

271

4.8.5 Eine Gültigkeitsliste aus einem Datenfeld befüllen

272

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

274

4.8.7 Ein Zellen-Dropdown aus einer Konstanten befüllen

276

4.8.8 Einen Standardeintrag für Zellen-Dropdowns setzen

277

4.8.9 Gültigkeitskriterien löschen

278

4.9 Kommentare in Excel einsetzen

278

4.9.1 Kommentare einfügen

279

4.9.2 Kommentare im Direktfenster auslesen

280

4.9.3 Kommentare formatieren

281

4.9.4 Kommentare aus der aktiven Tabelle löschen

285

4.9.5 Alle Kommentare einer Arbeitsmappe löschen

285

4.9.6 Einen Kommentar einem Namen in einer Arbeitsmappe zuweisen

286

4.9.7 Den Autor von Kommentaren anpassen

287

4.9.8 Kommentarkennzeichnung ein- und ausschalten

288

4.9.9 Kommentare einer Arbeitsmappe in eine Textdatei schreiben

289

4.9.10 Kommentare vor Veränderung schützen

290

4.9.11 Ein Bild in einen Kommentar einfügen

290

5 Spalten und Zeilen programmieren

293

5.1 Zeilen und Spalten ansprechen, ansteuern und anpassen

293

5.1.1 Mehrere Zeilen auf dem Tabellenblatt markieren

294

5.1.2 Mehrere Spalten auf dem Tabellenblatt markieren

294

5.1.3 Zeilen und Spalten markieren

295

5.1.4 Markierte Spalten zählen

296

5.1.5 Letzte freie Zelle in Spalte identifizieren

297

5.1.6 Anzahl der benutzten Spalten und Zeilen ermitteln

297

5.1.7 Zeilen und Spalten im umliegenden Bereich zählen

299

5.1.8 Zeilenhöhe und Spaltenbreite einstellen

300

5.2 Zeilen einfügen und löschen

303

5.2.1 Zeile einfügen

303

5.2.2 Mehrere Zeilen einfügen

303

5.2.3 Leere Zeilen dynamisch einfügen

304

5.2.4 Wirklich leere Zeilen löschen

306

5.2.5 Doppelte Sätze löschen

307

5.3 Spalten einfügen, löschen und bereinigen

308

5.3.1 Spalte einfügen

308

5.3.2 Mehrere Spalten einfügen

309

5.3.3 Spalte löschen

309

5.3.4 Mehrere Spalten löschen

310

5.4 Zeilen ein- und ausblenden

310

5.4.1 Leere Zeilen ausblenden

310

5.4.2 Alle Zellen einblenden

311

5.5 Spalten ein- und ausblenden

312

5.5.1 Bestimmte Spalten ausblenden

312

5.6 Spalten und Zeilen formatieren

313

5.7 Daten sortieren

314

5.7.1 Daten sortieren mit der klassischen Variante

314

5.7.2 Daten sortieren mit der modernen Methode

315

5.7.3 Daten sortieren nach Farbe der Zellen

318

5.7.4 Daten nach einer Überschrift spaltenweise sortieren

319

5.8 Spalte(n) vergleichen

320

5.8.1 Zelle mit Spalte vergleichen

320

5.8.2 Spalten über eine bedingte Formatierung miteinander vergleichen

321

5.9 Zeilen filtern

323

5.9.1 AutoFilter aktivieren und deaktivieren

323

5.9.2 Filterkriterien setzen

325

5.9.3 Nur Texte filtern

328

5.9.4 Daten filtern, die eine Zeichenfolge enthalten

329

5.9.5 Der Top-10-Filter

331

5.9.6 Filtern nach Zellenfarbe

332

5.9.7 Gefilterte Zeilen entfernen

334

5.9.8 Filterkriterien als Datenfeld übergeben

335

5.9.9 Alle gesetzten Filter sichtbarer machen

341

5.9.10 Wie lauten die Filterkriterien?

343

5.9.11 Doppelte Werte mit dem Spezialfilter ermitteln

344

5.9.12 Doppelte Werte mit dem »Dictionary«-Objekt entfernen

346

5.9.13 Daten über einen Kriterienbereich filtern

347

5.9.14 Mehrere Spalten über einen Kriterienbereich filtern

349

5.9.15 Wildcards im Spezialfilter einsetzen

351

5.9.16 Filtern von Umsätzen in einem vorgegebenen Zeitraum

353

5.9.17 Gefilterte Daten transferieren

354

5.10 Zeilen über das Teilergebnis gruppieren

356

6 Tabellen und Diagramme programmieren

359

6.1 Tabellen einfügen

359

6.2 Tabellenblätter benennen

360

6.2.1 Eine neue Mappe erstellen, 12 Monatstabellen anlegen und benennen

361

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

362

6.2.3 Tabelle einfügen und gleichzeitig benennen

363

6.3 Tabellen markieren

363

6.4 Tabellenblätter gruppieren

364

6.4.1 Mehrere Tabellen gruppieren

365

6.4.2 Alle Tabellen gruppieren

365

6.4.3 Gruppierte Tabellen übertragen

366

6.4.4 Gruppierte Tabellen ermitteln

366

6.5 Tabellenblätter löschen

367

6.5.1 Eine Tabelle löschen

367

6.5.2 Bestimmte Tabellen aus einer Mappe entfernen

369

6.5.3 Tabellen mit gefärbten Registerlaschen entfernen

370

6.5.4 Leere Tabellen aus Arbeitsmappen entfernen

370

6.6 Tabellenblätter ein- und ausblenden

371

6.6.1 Tabellenblätter sicher ausblenden

372

6.6.2 Tabellen je nach Status ein- oder ausblenden

372

6.6.3 Alle Tabellenblätter anzeigen

373

6.6.4 Alle Tabellen außer der aktiven Tabelle ausblenden

374

6.7 Tabellenblätter schützen

374

6.7.1 Tabellenschutz aufheben

376

6.7.2 Alle Tabellen einer Arbeitsmappe schützen

376

6.7.3 Weitere Schutzfunktionen ab Excel 2002

377

6.7.4 Passwort - Einstellungsdialog mit verschlüsseltem Passwort aufrufen

378

6.8 Tabellen einstellen

379

6.8.1 Registerlaschen ein- und ausblenden

379

6.8.2 Tabellenansicht anpassen

379

6.8.3 Einen einheitliche Zoomeinstellung vornehmen

380

6.8.4 Tabellenblätter sortieren

381

6.8.5 Kopf- und Fußzeilen einrichten

382

6.8.6 Druckbereiche festlegen

391

6.8.7 Das Tabellengitternetz ein- und ausschalten

392

6.8.8 Zeilen- und Spaltenköpfe ein- und ausblenden

392

6.8.9 Cursor einstellen auf Zelle A1

393

6.9 Tabellenblätter drucken und PDF erstellen

393

6.9.1 Mehrere Kopien drucken

394

6.9.2 Markierte Bereiche drucken

394

6.9.3 Mehrere Tabellenblätter drucken

395

6.9.4 Tabelle als PDF ablegen

395

6.10 Tabelleninhaltsverzeichnis erstellen

396

6.11 Intelligente Tabellen

398

6.11.1 Tabelle umwandeln

399

6.11.2 Tabelle um eine Spalte ergänzen

400

6.11.3 Tabelle um eine Zeile ergänzen

401

6.11.4 Tabelle filtern

402

6.11.5 Tabellen sortieren

404

6.11.6 Tabelle um Ergebniszeile erweitern

405

6.11.7 Tabelle entfernen

406

6.12 Pivot-Tabellen erstellen

406

6.12.1 Pivot-Tabellen aktualisieren

410

6.12.2 Eine einzelne Pivot-Tabelle aktualisieren

410

6.12.3 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

411

6.12.4 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren

411

6.12.5 Pivot-Tabellen dynamisch erweitern

412

6.12.6 Pivot-Tabellen formatieren

413

6.12.7 Slicer einfügen und bedienen

414

6.13 Diagramme programmieren

416

6.13.1 Umsätze in einem Säulendiagramm darstellen

417

6.13.2 Tagesumsätze im Liniendiagramm anzeigen

422

6.13.3 Tagesgenaue Formatierung im Punktdiagramm

424

6.13.4 Diagramme als Grafiken speichern

426

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

428

6.13.6 Linienstärke unabhängig von den Markierungssymbolen formatieren

430

6.13.7 Sparklines automatisch erstellen

430

7 Arbeitsmappen und Dateien programmieren

435

7.1 Arbeitsmappen ansprechen

435

7.2 Arbeitsmappen anlegen

436

7.2.1 Eine Arbeitsmappe auf Basis einer Dokumentvorlage erstellen

436

7.2.2 Arbeitsmappe mit x Tabellen anlegen

437

7.2.3 Mappe mit Wochentabellen anlegen

438

7.3 Arbeitsmappen speichern

439

7.3.1 Arbeitsmappe unter aktuellem Tagesdatum speichern

440

7.3.2 Alle Tabellen einer Mappe als eigenständige Mappen speichern

441

7.3.3 Mappe erstellen und »Speichern unter«-Dialog aufrufen

442

7.3.4 Individuellen Speichern-Dialog aufrufen

443

7.3.5 Kopie der aktuellen Mappe zur Laufzeit erstellen

445

7.4 Arbeitsmappen öffnen

445

7.4.1 Die Argumente der Methode »Open«

446

7.4.2 Mehrere Arbeitsmappen öffnen

446

7.4.3 Die aktuelle Datei in einem Verzeichnis öffnen

448

7.4.4 Regelmäßig die Dateiexistenz prüfen

449

7.4.5 Alle verknüpften Mappen automatisch öffnen

449

7.5 Arbeitsmappen schließen

451

7.5.1 Arbeitsmappe schließen - Änderungen akzeptieren

451

7.5.2 Alle Arbeitsmappen bis auf eine schließen

452

7.6 Arbeitsmappe löschen

453

7.6.1 Arbeitsmappe nach Verfallsdatum löschen

453

7.6.2 Alle Excel-Mappen in einem Verzeichnis löschen

454

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

455

7.7 Arbeitsmappen drucken

456

7.7.1 Nur bestimmte Tabellen drucken

456

7.7.2 Alle Mappen eines Verzeichnisses drucken

457

7.7.3 Nur sichtbare Blätter ausdrucken

458

7.8 Dokumenteigenschaften verarbeiten

459

7.8.1 Dokumenteigenschaftsnamen abfragen

460

7.8.2 Letztes Speicherdatum abfragen

461

7.8.3 Erstelldatum herausfinden und manipulieren

462

7.8.4 Zugriffsdaten einer Arbeitsmappe ermitteln

462

7.8.5 Eigene Dokumenteigenschaften verwenden

463

7.9 Arbeitsmappen und Verknüpfungen

465

7.9.1 Verknüpfungen in Hyperlinks umwandeln

465

7.9.2 Verknüpfungen aus der Arbeitsmappe entfernen

467

7.9.3 Verknüpfungen ändern

469

7.9.4 Verknüpfungen aktualisieren

470

7.10 Arbeitsmappe durchsuchen

470

7.11 Arbeitsmappen miteinander vergleichen

472

7.12 Arbeitsmappenübersicht erstellen

474

7.13 Textdateien importieren

476

7.14 Makros für das Dateimanagement

488

7.14.1 Ein Jahresverzeichnis automatisch anlegen

488

7.14.2 Eine bestimmte Datei nach Rückfrage löschen

489

7.14.3 Einen Ordner archivieren

491

7.14.4 Eine bestimmte Datei kopieren

492

7.14.5 Ordner anlegen und entfernen

493

7.14.6 Verzeichnisstruktur in einer Tabelle anzeigen

495

8 Eigene Funktionen programmieren und reguläre Ausdrücke verwenden

499

8.1 Benutzerdefinierte Funktionen

499

8.1.1 Aktive Arbeitsmappe ermitteln

500

8.1.2 Aktives Tabellenblatt ermitteln

501

8.1.3 Ist eine Tabelle leer?

502

8.1.4 Ist eine Tabelle geschützt?

503

8.1.5 Befinden sich Daten in einer bestimmten Spalte?

503

8.1.6 Den letzten Wert einer Spalte ermitteln

504

8.1.7 Den letzten Wert einer Zeile ermitteln

505

8.1.8 Den aktiven Bearbeiter identifizieren

505

8.1.9 Funktion zum Umsetzen von Schulnoten

505

8.1.10 Rangfolge als Text ausgeben

507

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

507

8.1.12 Enthält eine Zelle einen Kommentar?

508

8.1.13 Ist eine bestimmte Zelle verbunden?

509

8.1.14 Initialen aus Namen erstellen

509

8.1.15 Nur Zellen mit Fettdruck addieren

510

8.1.16 Mit Uhrzeiten rechnen

511

8.1.17 Erweitertes Runden durchführen

513

8.1.18 Schnelles Umrechnen von Geschwindigkeiten

514

8.1.19 Extremwerte berechnen

515

8.1.20 Erste Ziffer in einer Zelle ermitteln

516

8.1.21 Buchstaben aus Zellen entfernen

518

8.1.22 Anzahl der Ziffern einer Zelle ermitteln

519

8.1.23 Römische Zahlen in arabische umwandeln

520

8.1.24 Einen Kommentartext in eine Zelle holen

522

8.1.25 Angabe eines optionalen Parameters bei einer Funktion

523

8.1.26 Leerzeichen in einen String integrieren

523

8.2 Modulare Funktionen schreiben

524

8.2.1 Dateien in einem Verzeichnis zählen

525

8.2.2 Fehlerüberwachung umleiten

525

8.2.3 Prüfen, ob eine bestimmte Datei existiert

527

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

528

8.2.5 Prüfen, ob eine Datei gerade bearbeitet wird

529

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

530

8.2.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln

530

8.2.8 Den letzten Wert einer Spalte ermitteln

532

8.2.9 Grafikelemente in einem definierten Bereich löschen

534

8.2.10 Kalenderwoche nach DIN ermitteln

535

8.2.11 Unerwünschte Zeichen aus Zellen entfernen

536

8.3 Funktionen verfügbar machen

538

8.3.1 Speichern der Funktionen in der persönlichen Arbeitsmappe

538

8.3.2 Speichern der Funktionen in einem Add-In

539

8.3.3 Ein Add-In einbinden

539

8.4 Mit regulären Ausdrücken programmieren

540

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

540

8.4.2 Bestimmte Zeichenfolgen in Texten finden

543

8.4.3 Spezielle Zeichen nutzen

545

8.4.4 Zeichenfolgen aus Texten extrahieren

546

8.4.5 Eine E-Mail-Adresse prüfen

549

8.4.6 Konten prüfen

550

8.4.7 Zahlen aus Texten extrahieren

552

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«)

566

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«)

570

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«)

580

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

580

9.3 Reaktion auf Tastendruck

581

9.3.1 Texte einfügen

583

9.3.2 Blattsperre ohne Blattschutz erstellen

584

9.3.3 Nur Werte einfügen

585

9.4 Zeitsteuerung in Excel

586

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 einer 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

596

10.1.5 VBE-Editor aufrufen

597

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

600

10.4 Alle Makros aus einer Arbeitsmappe entfernen

601

10.4.1 Module mit Makros bestücken

602

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

608

10.9 Makros und Ereignisse dokumentieren

609

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

615

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

624

11.4.1 UserForm einfügen

624

11.4.2 UserForm beschriften

625

11.4.3 UserForm aufrufen

626

11.4.4 Die verfügbaren Steuerelemente

626

11.4.5 Steuerelemente einfügen

628

11.4.6 Die wichtigsten Eigenschaften

629

11.4.7 Ereignisse einstellen

632

11.5 Programmierung von Textfeldern

633

11.5.1 Passwort über einen Dialog abfragen

633

11.5.2 Textfelder leeren

637

11.5.3 Textfelder kennzeichnen

639

11.5.4 Prüfung auf numerischen Inhalt

642

11.5.5 Länge eines Textfeldes prüfen

644

11.5.6 Prüfen von und Rechnen mit Textfeldern

645

11.5.7 Prüfen und widerrufen

650

11.5.8 Eine AutoAusfüllen-Funktion programmieren

652

11.5.9 Rechtschreibprüfung vornehmen

655

11.5.10 Daten über ein Textfeld suchen

657

11.6 Programmierung von Listenfeldern

661

11.6.1 Listenfeld mit Tabellen füllen

661

11.6.2 Listenfeld mit Monaten füllen

664

11.6.3 Mehrspaltiges Listenfeld mit Daten aus Tabelle füllen

666

11.6.4 Listenfeld transponiert füllen

671

11.6.5 Listenfelder im Duett

674

11.6.6 Listenfeld und Textfelder im Zusammenspiel

678

11.7 Programmierung von Kombinationsfeldlisten

680

11.7.1 Dropdown mit Tagen füllen

680

11.7.2 Eindeutige Einträge im Dropdown anzeigen

683

11.7.3 Dropdowns synchronisieren

686

11.7.4 Dropdown und Listenfeld im Duett

690

11.8 Die Programmierung von Optionsschaltflächen

693

11.8.1 Mehrwertsteuersatz als Option anwenden

693

11.8.2 Optionsfelder und Listenfeld im Zusammenspiel

697

11.9 Die Programmierung von Kontrollkästchen

700

11.9.1 Kontrollkästchen über eine Tabelle speisen

700

11.9.2 Ansichtseinstellungen über Kontrollkästchen vornehmen

703

11.10 Die Programmierung von Bildelementen

707

11.10.1 Der eigene Bildbetrachter

707

11.11 Die Programmierung sonstiger Steuerelemente

711

11.11.1 Bilder in MultiPage laden

711

11.11.2 Umschaltfläche programmieren

713

11.11.3 Drehfeld programmieren

717

11.11.4 Die Programmierung des »ListView«-Steuerelements

720

11.11.5 Die Programmierung des »TreeView«-Steuerelements

726

11.11.6 Die Programmierung des »ProgressBar«-Steuerelements

730

11.12 Das Verwaltungstool

732

11.12.1 Die hinterlegte Datentabelle

733

11.12.2 Die beteiligten Steuerelemente

733

11.12.3 Vorbereitende Aufgaben

734

11.12.4 Daten suchen

735

11.12.5 Mit Klick auf das Listenfeld die Textfelder ausfüllen

737

11.12.6 Den Dialog initialisieren

739

11.12.7 Datensatz löschen

739

11.12.8 Datensatz ändern

740

11.12.9 Neue Kundennummer ermitteln

741

11.12.10 Datensatz anlegen

741

12 Excel im Umfeld von Office programmieren

745

12.1 Excel im Zusammenspiel mit PowerPoint

745

12.1.1 Excel-Bereich nach PowerPoint exportieren

745

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

748

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

750

12.1.4 PowerPoint-Folie als Objekt in Excel einbinden

752

12.1.5 Diagrammobjekte in eine Präsentation exportieren

754

12.2 Excel im Zusammenspiel mit Word

756

12.2.1 Excel-Bereich in Dokument exportieren

757

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

759

12.2.3 Markierten Bereich einer Excel-Tabelle in ein Dokument exportieren

763

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

765

12.3 Excel im Zusammenspiel mit Outlook

767

12.3.1 Kontaktdaten aus Excel nach Outlook exportieren

768

12.3.2 Termine aus Excel in den Outlook-Kalender schieben

773

12.3.3 Aktive Tabelle aus Excel heraus versenden

777

12.3.4 Aktive Tabelle als Anhang aus Excel heraus versenden

778

12.3.5 Aktive Arbeitsmappe per E-Mail versenden

780

12.3.6 Alle Dokumente aus einem Verzeichnis per E-Mail versenden

783

12.4 Excel im Zusammenspiel mit Access

785

12.4.1 Toolfrage und Randbedingungen

785

12.4.2 Anforderungen an das Tool

786

12.4.3 Die Umsetzung der Kernfunktionen

786

12.4.4 Befüllung der UserForm mit den wichtigsten Daten

788

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

789

12.4.6 Suche nach Datum

795

12.4.7 Termine erfassen

797

12.4.8 Änderung von Terminen

799

12.4.9 Termine löschen

801

12.4.10 Felder löschen

803

12.5 Excel im Zusammenspiel mit dem Internet Explorer

803

12.5.1 Eine Internetseite aus Excel aufrufen

804

12.5.2 Texte übersetzen mit Google

804

12.5.3 Bild aus Internet laden und in UserForm anzeigen

806

12.5.4 Ein PDF aus dem Internet laden

808

13 Datenfelder, ADO, Dictionaries und Collections programmieren

811

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

811

13.1.1 Daten filtern und in einer anderen Tabelle ausgeben

812

13.1.2 Umsätze nach Datum verdichten

815

13.1.3 Umsätze nach Datum und Warengruppe verdichten

818

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

822

13.1.5 Daten aus einer Tabelle löschen

824

13.1.6 Top-Werte ermitteln

826

13.1.7 Mehrere Tabellen zusammenfassen

828

13.1.8 Eine Unikatsliste bilden

831

13.1.9 Excel-Daten per ADO verändern

834

13.2 Arbeiten mit Arrays

837

13.2.1 Aktionen im Arbeitsspeicher ausführen lassen

838

13.2.2 Bestimmte Daten aus einer Tabelle löschen

841

13.2.3 Daten konvertieren

844

13.3 Arbeiten mit dem »Dictionary«-Objekt

849

13.3.1 Daten verdichten

849

13.3.2 Bedingte Summierung mit mehreren Kriterien

852

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

866

13.4.1 Eindeutige Einträge über eine Collection bilden

866

13.4.2 Eine Collection aus einer Tabelle befüllen

869

14 Die Programmierung der Excel-Oberfläche

873

14.1 Die Programmierung von Kontextmenüs

873

14.1.1 Kontextmenüs deaktivieren

874

14.1.2 Das Zellen-Kontextmenü erweitern

875

14.1.3 Kontextmenü aufbauen (dreistufig)

877

14.1.4 Kontextmenü zurücksetzen

879

14.2 Die Ribbon-Programmierung

879

14.2.1 Der Custom UI Editor

879

14.2.2 Weitere wichtige Quellen und Hilfen

882

14.2.3 Ribbon mit Schaltflächen erstellen

883

14.2.4 Ribbon mit »ComboBox« erstellen

885

14.2.5 Ribbon mit bereits verfügbaren Funktionen bestücken

887

14.2.6 Den Backstage-Bereich programmieren

888

14.2.7 Eine Galerie mit Fotos erstellen

890

15 Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten

891

15.1 Kleinere Fehler beheben

891

15.1.1 Stimmt die Syntax?

891

15.1.2 Ist die Variablendefinition gegeben?

892

15.1.3 Objekt vorhanden?

892

15.1.4 Methode, Eigenschaft verfügbar?

893

15.2 Schwerwiegendere Fehler

893

15.2.1 Fehler im Vorfeld erkennen und reagieren

893

15.2.2 Fehler ignorieren

894

15.2.3 Fehlerursache ermitteln

894

15.2.4 Die Funktion »IsError«

895

15.3 Das Add-In MZ-Tools

896

15.3.1 Zeilennummern automatisch einfügen

897

15.3.2 Eine Fehlerbehandlung mit den MZ-Tools hinzufügen

898

15.4 Laufzeiten verkürzen

899

15.4.1 Variablen und Konstanten einsetzen

899

15.4.2 Berechnung und Bildschirmaktualisierung ausschalten

900

15.4.3 Integrierte Tabellenfunktionen anwenden

901

15.5 VBA-Projekte schützen

902

16 Typische Verarbeitungsaufgaben aus der Praxis

903

16.1 Daten übertragen

903

16.2 Daten im Batch verarbeiten

906

16.3 Daten verteilen

909

16.3.1 Die Tabellen entfernen

909

16.3.2 Die Verteilung der Zeilen auf die Tabellen

910

16.3.3 Die Plausibilität prüfen

914

16.3.4 Der Export der Tabellen

915

16.4 Berichtsfilterseiten erstellen

917

16.5 Daten löschen

919

16.5.1 Daten entfernen - Variante 1

920

16.5.2 Daten entfernen - Variante 2

920

16.5.3 Daten entfernen - Variante 3

921

16.6 Daten kennzeichnen

922

16.6.1 Doppelte Daten kennzeichnen (der Standard)

923

16.6.2 Doppelte Daten kennzeichnen (die Erweiterung)

925

16.6.3 Top-10-Werte aus einem Bereich ermitteln

927

16.7 Diagramme automatisch formatieren

930

16.8 Daten über den Einsatz von Wildcards suchen

935

16.9 Zwei identische Bereiche miteinander vergleichen

936

16.10 Suche nach einem Begriff unter Berücksichtigung der Formatierung

937

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

939

16.12 Einen Durchschnitt aus den Top-5-Werten ermitteln

941

16.13 Arbeitsmappen auf Knopfdruck automatisch erstellen

943

16.14 Alle Formeln einer Tabelle schützen und verstecken

945

16.15 Eine Unikatsliste über den Einsatz von SQL erstellen

946

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

948

16.17 Kriterien für eine Mehrfachfilterung aus Zellen beziehen

951

16.18 Bestimmte Zeichenfolge in einem Bereich entfernen

952

16.19 Automatisch eine Kopie der Mappe erstellen

953

16.20 Einen Excel-Bereich als Objekt nach PowerPoint übertragen

954

16.21 Einen formatierten Text in eine PowerPoint-Folie übertragen

957

16.22 Top-Werte ermitteln und kennzeichnen

959

16.23 Daten aus einer geschlossenen Mappe ziehen

961

16.24 Ein PDF in Excel erstellen und direkt versenden

963

16.25 Daten verdichten

965

16.25.1 Daten verdichten mithilfe von SUMMEWENNS

966

16.25.2 Daten mithilfe einer SQL-Anweisung verdichten

967

16.25.3 Daten verdichten mithilfe des Datenfilters

969

16.26 Automatisierung: automatischer E-Mail-Versand für das Controlling

970

16.27 Zufällige Zahlen in einem bestimmten Zahlenbereich generieren

974

16.28 Jahresdaten auf 12 Tabellen verteilen

975

16.29 Automatisierung: Zeilen miteinander vergleichen

978

16.30 Doppelte und fehlende Rechnungsnummern aufspüren

980

16.31 Suche nach Wert und Format

983

16.32 Bereiche miteinander vergleichen

985

16.33 Dynamischer Jahresbericht auf Knopfdruck

988

16.34 Bestimmte Zeilen in einer Tabelle entfernen

990

16.35 Zwei Spalten miteinander vergleichen

992