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

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

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 Zahlenwerte prüfen

113

3.4.2 Den Wochentag eines bestimmten Datums auslesen

114

3.4.3 Benotungen über einen Autotext durchführen

115

3.5 Die »Enum«-Anweisung

117

3.5.1 Umsatz klassifizieren mit »Enum«

118

3.6 Schleifen erstellen und verstehen

119

3.6.1 Die »For ... Next«-Schleife

120

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

136

3.6.3 Die Schleife »Do Until ... Loop«

147

3.6.4 Die Schleife »Do While ... Loop«

151

3.7 Sonstige Sprachelemente

153

3.7.1 Die Anweisung »With«

154

4 Zellen und Bereiche programmieren

159

4.1 Zahlenformat einstellen und/oder konvertieren

159

4.1.1 Zahlenformate einstellen (Datum und Zahl)

159

4.1.2 Zahlenformate einstellen (Text)

162

4.1.3 Zahlenformate übertragen

163

4.1.4 Zellen mit Nullen auffüllen

163

4.1.5 Einheitliches Datumsformat einstellen

165

4.1.6 Unerwünschte führende und nachgestellte Leerzeichen entfernen

167

4.1.7 Korrektur nach fehlerhaftem Import von Daten

169

4.1.8 Die Position des Minuszeichens umstellen

171

4.1.9 Daten umschlüsseln

173

4.1.10 Einen eindeutigen Schlüssel aus mehreren Spalten basteln

175

4.2 Zellen, Rahmen und Schriften formatieren

177

4.2.1 Schriftart ermitteln

178

4.2.2 Schriftart ändern

178

4.2.3 Zelleninhalte löschen

181

4.2.4 Schriftfarbe teilweise ändern

182

4.2.5 Automatisch runden und formatieren

183

4.2.6 Zwei Bereiche miteinander vergleichen

185

4.2.7 Einen Bereich »mustern«

187

4.2.8 Einen Bereich einrahmen

190

4.2.9 Einen Bereich umrahmen

191

4.3 Die bedingte Formatierung von Excel

193

4.3.1 Eine bedingte Formatierung als Standard einstellen

194

4.3.2 Duplikate mit dem bedingten Format aufspüren

197

4.3.3 Wertgrößen über einen Datenbalken darstellen

199

4.3.4 Eine Farbskala definieren und einsetzen

200

4.3.5 Daten über Pfeilsymbole bewerten

201

4.3.6 Die Top-Werte in einem Bereich hervorheben

203

4.3.7 Die einzugebende Textlänge überwachen

204

4.3.8 Eine bedingte Formatierung mit Wertgrenzen ausstatten

205

4.3.9 Sparklines einsetzen

207

4.4 Bereiche und Zellen benennen

208

4.4.1 Einen Bereich benennen

209

4.4.2 Mehrere Zellen einzeln benennen

210

4.4.3 Konstante als Namen vergeben

211

4.4.4 Verwendete Namen auslesen

212

4.4.5 Versteckte Namen sichtbar machen

213

4.4.6 Einen geheimen Namen anlegen

214

4.4.7 Einen dynamischen Namen anlegen

215

4.4.8 Ein Zellen-Dropdown auf Basis einer benannten Liste anlegen

217

4.4.9 Benannte Bereiche löschen

218

4.4.10 Benutzerdefinierte Listen erstellen

219

4.5 Tabellenfunktionen einsetzen

220

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

227

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

242

4.6 Matrixformeln in der Programmierung nutzen

243

4.6.1 Werte bedingt zählen

244

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

248

4.7 Texte und Zahlen manipulieren

248

4.7.1 Texte finden und umstellen

249

4.7.2 Mehrere Spalten anhand von Trennzeichen splitten

251

4.7.3 Daten nach einem Datentransfer bereinigen

254

4.7.4 Zeichenfolge(n) aus Zellen entfernen

254

4.7.5 Buchstaben aus Zellen entfernen

257

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

261

4.8.1 Gültigkeitskriterien erstellen

262

4.8.2 Zellen mit Gültigkeitsfunktion auswählen

263

4.8.3 Datumsgrenzen festlegen

265

4.8.4 Nur Werktage für die Eingabe zulassen

266

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

271

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

274

4.9.1 Kommentare einfügen

274

4.9.2 Kommentare im Direktfenster auslesen

276

4.9.3 Kommentare formatieren

277

4.9.4 Kommentare aus der aktiven Tabelle löschen

280

4.9.5 Alle Kommentare einer Arbeitsmappe löschen

281

4.9.6 Einen Kommentar einem Namen in einer Arbeitsmappe zuweisen

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

286

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

289

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

297

5.2.3 Leere Zeilen dynamisch einfügen

298

5.2.4 Wirklich leere Zeilen löschen

300

5.2.5 Doppelte Sätze löschen

301

5.3 Spalten einfügen, löschen und bereinigen

302

5.3.1 Spalte einfügen

302

5.3.2 Mehrere Spalten einfügen

303

5.3.3 Spalte löschen

303

5.3.4 Mehrere Spalten löschen

304

5.4 Zeilen ein- und ausblenden

304

5.4.1 Leere Zeilen ausblenden

304

5.4.2 Alle Zellen einblenden

305

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

308

5.7.1 Daten sortieren mit der klassischen Variante

309

5.7.2 Daten sortieren mit der modernen Methode

309

5.7.3 Daten sortieren nach Farbe der Zellen

312

5.7.4 Daten nach einer Überschrift spaltenweise sortieren

313

5.8 Spalte(n) vergleichen

314

5.8.1 Zelle mit Spalte vergleichen

314

5.8.2 Spalten über eine bedingte Formatierung miteinander vergleichen

315

5.9 Zeilen filtern

317

5.9.1 AutoFilter aktivieren und deaktivieren

318

5.9.2 Filterkriterien setzen

319

5.9.3 Nur Texte filtern

322

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

330

5.9.9 Alle gesetzten Filter sichtbarer machen

336

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

345

5.9.16 Filtern von Umsätzen in einem vorgegebenen Zeitraum

347

5.9.17 Gefilterte Daten transferieren

349

5.10 Zeilen über das Teilergebnis gruppieren

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, 12 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

366

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

368

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

373

6.8.3 Einen 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

398

6.11.6 Tabelle um Ergebniszeile erweitern

399

6.11.7 Tabelle entfernen

400

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

405

6.12.4 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren

405

6.12.5 Pivot-Tabellen dynamisch erweitern

406

6.12.6 Pivot-Tabellen formatieren

407

6.12.7 Slicer einfügen und bedienen

408

6.13 Diagramme programmieren

410

6.13.1 Umsätze in einem Säulendiagramm darstellen

411

6.13.2 Tagesumsätze im Liniendiagramm anzeigen

416

6.13.3 Tagesgenaue Formatierung im Punktdiagramm

418

6.13.4 Diagramme als Grafiken speichern

420

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

422

6.13.6 Linienstärke unabhängig von den Markierungssymbolen formatieren

423

6.13.7 Sparklines automatisch erstellen

424

6.14 Tabellen blitzschnell vergleichen und Unterschiede dokumentieren

428

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

440

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

444

7.4 Arbeitsmappen öffnen

445

7.4.1 Die Argumente der Methode »Open«

445

7.4.2 Mehrere Arbeitsmappen öffnen

446

7.4.3 Die aktuelle Datei in einem Verzeichnis öffnen

447

7.4.4 Regelmäßig die Dateiexistenz prüfen

448

7.4.5 Alle verknüpften Mappen automatisch öffnen

449

7.5 Arbeitsmappen schließen

450

7.5.1 Arbeitsmappe schließen - Änderungen akzeptieren

450

7.5.2 Alle Arbeitsmappen bis auf eine schließen

451

7.6 Arbeitsmappe löschen

452

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

454

7.7 Arbeitsmappen drucken

455

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

458

7.8.1 Dokumenteigenschaftsnamen abfragen

459

7.8.2 Letztes Speicherdatum abfragen

461

7.8.3 Erstelldatum herausfinden und manipulieren

461

7.8.4 Zugriffsdaten einer Arbeitsmappe ermitteln

462

7.8.5 Eigene Dokumenteigenschaften verwenden

463

7.9 Arbeitsmappen und Verknüpfungen

464

7.9.1 Verknüpfungen in Hyperlinks umwandeln

464

7.9.2 Verknüpfungen aus der Arbeitsmappe entfernen

466

7.9.3 Verknüpfungen ändern

468

7.9.4 Verknüpfungen aktualisieren

469

7.10 Arbeitsmappe durchsuchen

469

7.11 Arbeitsmappen miteinander vergleichen

471

7.12 Arbeitsmappenübersicht erstellen

473

7.13 Textdateien importieren

475

7.14 Makros für das Dateimanagement

486

7.14.1 Ein Jahresverzeichnis automatisch anlegen

486

7.14.2 Eine bestimmte Datei nach Rückfrage löschen

488

7.14.3 Einen Ordner archivieren

489

7.14.4 Eine bestimmte Datei kopieren

490

7.14.5 Ordner anlegen und entfernen

492

7.14.6 Verzeichnisstruktur in einer Tabelle anzeigen

493

8 Eigene Funktionen und reguläre Ausdrücke

497

8.1 Benutzerdefinierte Funktionen

497

8.1.1 Aktive Arbeitsmappe ermitteln

498

8.1.2 Aktives Tabellenblatt ermitteln

499

8.1.3 Ist eine Tabelle leer?

500

8.1.4 Ist eine Tabelle geschützt?

501

8.1.5 Befinden sich Daten in einer bestimmten Spalte?

501

8.1.6 Den letzten Wert einer Spalte ermitteln

502

8.1.7 Den letzten Wert einer Zeile ermitteln

502

8.1.8 Den aktiven Bearbeiter identifizieren

503

8.1.9 Funktion zum Umsetzen von Schulnoten

503

8.1.10 Rangfolge als Text ausgeben

504

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

505

8.1.12 Enthält eine Zelle einen Kommentar?

506

8.1.13 Ist eine bestimmte Zelle verbunden?

507

8.1.14 Initialen aus Namen erstellen

507

8.1.15 Nur Zellen mit Fettdruck addieren

508

8.1.16 Mit Uhrzeiten rechnen

509

8.1.17 Erweitertes Runden durchführen

511

8.1.18 Schnelles Umrechnen von Geschwindigkeiten

512

8.1.19 Extremwerte berechnen

512

8.1.20 Erste Ziffer in einer Zelle ermitteln

514

8.1.21 Buchstaben aus Zellen entfernen

516

8.1.22 Anzahl der Ziffern einer Zelle ermitteln

517

8.1.23 Römische Zahlen in arabische umwandeln

518

8.1.24 Einen Kommentartext in eine Zelle holen

519

8.1.25 Angabe eines optionalen Parameters bei einer Funktion

520

8.1.26 Leerzeichen in einen String integrieren

521

8.2 Jahresbericht mit nur einer Funktion blitzschnell erstellen

522

8.3 Modulare Funktionen schreiben

528

8.3.1 Dateien in einem Verzeichnis zählen

529

8.3.2 Fehlerüberwachung umleiten

529

8.3.3 Prüfen, ob eine bestimmte Datei existiert

531

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

532

8.3.5 Prüfen, ob eine Datei gerade bearbeitet wird

533

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

534

8.3.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln

534

8.3.8 Den letzten Wert einer Spalte ermitteln

536

8.3.9 Grafikelemente in einem definierten Bereich löschen

538

8.3.10 Kalenderwoche nach DIN ermitteln

539

8.3.11 Unerwünschte Zeichen aus Zellen entfernen

540

8.4 Funktionen verfügbar machen

542

8.4.1 Speichern der Funktionen in der persönlichen Arbeitsmappe

542

8.4.2 Speichern der Funktionen in einem Add-In

543

8.4.3 Ein Add-In einbinden

544

8.5 Mit regulären Ausdrücken programmieren

544

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

545

8.5.2 Bestimmte Zeichenfolgen in Texten finden

548

8.5.3 Spezielle Zeichen nutzen

549

8.5.4 Zeichenfolgen aus Texten extrahieren

551

8.5.5 Eine E-Mail-Adresse prüfen

553

8.5.6 Konten prüfen

554

8.5.7 Zahlen aus Texten extrahieren

556

9 Ereignisse programmieren

559

9.1 Ereignisse für die Arbeitsmappe

559

9.1.1 Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-Ereignissen

560

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

561

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

563

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

568

9.1.5 Letztes Bearbeitungsdatum festhalten (»Workbook_BeforeSave«)

568

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

569

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

570

9.2 Ereignisse für das Tabellenblatt

571

9.2.1 Allgemeine Vorgehensweise bei der Einstellung von Tabellenereignissen

571

9.2.2 Die wichtigsten Ereignisse für Tabellen im Überblick

572

9.2.3 Passworteingabe beim Aktivieren einer Tabelle (»Worksheet_Activate«)

572

9.2.4 Vergleich von zwei Spalten (»Worksheet_Change«)

573

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

575

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

576

9.2.7 Die Spaltensumme überwachen (»Worksheet_Change«)

577

9.2.8 Nur einmalige Eingabe zulassen (»Worksheet_Change«)

579

9.2.9 Die Eingabe von Dubletten verhindern (»Worksheet_Change«)

579

9.2.10 Eingabe verhindern (»Worksheet_SelectionChange«)

580

9.2.11 Markierung überwachen (»Worksheet_SelectionChange«)

582

9.2.12 Mausklicks überwachen (»Worksheet_BeforeRightClick«)

583

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

583

9.3 Reaktion auf Tastendruck

584

9.3.1 Texte einfügen

586

9.3.2 Blattsperre ohne Blattschutz erstellen

587

9.3.3 Nur Werte einfügen

588

9.4 Zeitsteuerung in Excel

589

9.4.1 Regelmäßig die Uhrzeit anzeigen

589

9.4.2 Die Zeit läuft ...

590

9.4.3 Zellen blinken lassen

591

9.4.4 Eingaben nach Ablauf einer Minute löschen

592

10 Die VBE-Programmierung

595

10.1 Die VBE-Bibliothek einbinden

596

10.1.1 Die VBE-Bibliothek deaktivieren

597

10.1.2 Weitere Bibliotheken einbinden

598

10.1.3 Objektbibliotheken deaktivieren

599

10.1.4 Informationen zu Objektbibliotheken ausgeben

599

10.1.5 VBE-Editor aufrufen

600

10.1.6 Das Direktfenster aufrufen

601

10.2 Die VBE ein- und ausschalten

601

10.2.1 Neue Module einfügen

602

10.2.2 Einzelne Module löschen

603

10.3 Einzelnes Makro löschen

603

10.4 Alle Makros aus einer Arbeitsmappe entfernen

604

10.4.1 Module mit Makros bestücken

605

10.4.2 Makro zeilenweise in ein Modul übertragen

606

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

607

10.4.4 Export von VBA-Modulen in Textdateien

608

10.5 Identifikation von Komponenten

609

10.6 Ein bestimmtes Makro auskommentieren

610

10.7 Das Direktfenster löschen

611

10.8 Den Status des VBA-Projekts abfragen

611

10.9 Makros und Ereignisse dokumentieren

612

11 Dialoge, Meldungen und UserForms programmieren

615

11.1 »MsgBox«-Meldung

616

11.1.1 Welche Schaltfläche wurde angeklickt?

618

11.1.2 Löschrückfrage

619

11.2 Die »InputBox«-Eingabemaske

619

11.2.1 Einen Suchbegriff über eine InputBox abfragen

620

11.2.2 Abfrage des Spaltenbuchstabens

622

11.3 Integrierte Dialoge einsetzen

623

11.3.1 Den »Öffnen«-Dialog aufrufen

624

11.3.2 Den Dialog »Optionen« aufrufen

627

11.4 Eigene UserForms entwerfen

628

11.4.1 UserForm einfügen

628

11.4.2 UserForm beschriften

629

11.4.3 UserForm aufrufen

630

11.4.4 Die verfügbaren Steuerelemente

630

11.4.5 Steuerelemente einfügen

632

11.4.6 Die wichtigsten Eigenschaften

633

11.4.7 Ereignisse einstellen

636

11.5 Programmierung von Textfeldern

637

11.5.1 Passwort über einen Dialog abfragen

637

11.5.2 Textfelder leeren

641

11.5.3 Textfelder kennzeichnen

643

11.5.4 Prüfung auf numerischen Inhalt

646

11.5.5 Länge eines Textfeldes prüfen

647

11.5.6 Prüfen von und Rechnen mit Textfeldern

649

11.5.7 Prüfen und widerrufen

654

11.5.8 Eine AutoAusfüllen-Funktion programmieren

655

11.5.9 Rechtschreibprüfung vornehmen

659

11.5.10 Daten über ein Textfeld suchen

661

11.6 Programmierung von Listenfeldern

665

11.6.1 Listenfeld mit Tabellen füllen

665

11.6.2 Listenfeld mit Monaten füllen

668

11.6.3 Mehrspaltiges Listenfeld mit Daten aus Tabelle füllen

670

11.6.4 Listenfeld transponiert füllen

675

11.6.5 Listenfelder im Duett

678

11.6.6 Listenfeld und Textfelder im Zusammenspiel

681

11.7 Programmierung von Kombinationsfeldlisten

684

11.7.1 Dropdown mit Tagen füllen

684

11.7.2 Eindeutige Einträge im Dropdown anzeigen

686

11.7.3 Dropdowns synchronisieren

690

11.7.4 Dropdown und Listenfeld im Duett

693

11.8 Die Programmierung von Optionsschaltflächen

696

11.8.1 Mehrwertsteuersatz als Option anwenden

696

11.8.2 Optionsfelder und Listenfeld im Zusammenspiel

700

11.9 Die Programmierung von Kontrollkästchen

702

11.9.1 Kontrollkästchen über eine Tabelle speisen

703

11.9.2 Ansichtseinstellungen über Kontrollkästchen vornehmen

706

11.10 Die Programmierung von Bildelementen

709

11.10.1 Der eigene Bildbetrachter

710

11.11 Die Programmierung sonstiger Steuerelemente

714

11.11.1 Bilder in MultiPage laden

714

11.11.2 Umschaltfläche programmieren

716

11.11.3 Drehfeld programmieren

720

11.11.4 Die Programmierung des »ListView«-Steuerelements

723

11.11.5 Die Programmierung des »TreeView«-Steuerelements

729

11.11.6 Die Programmierung des »ProgressBar«-Steuerelements

732

11.12 Das Verwaltungstool

735

11.12.1 Die hinterlegte Datentabelle

735

11.12.2 Die beteiligten Steuerelemente

736

11.12.3 Vorbereitende Aufgaben

737

11.12.4 Daten suchen

738

11.12.5 Mit Klick auf das Listenfeld die Textfelder ausfüllen

740

11.12.6 Den Dialog initialisieren

742

11.12.7 Datensatz löschen

742

11.12.8 Datensatz ändern

743

11.12.9 Neue Kundennummer ermitteln

744

11.12.10 Datensatz anlegen

745

11.13 Ist das ListView-Steuerelement die bessere Listbox?

746

11.13.1 Die Befüllung des ListView-Elements

747

11.13.2 Das Sortieren von Spalten in einem ListView-Element

752

11.13.3 Markierte Elemente in Tabelle zurückschreiben

753

12 Excel im Umfeld von Office programmieren

755

12.1 Excel im Zusammenspiel mit PowerPoint

755

12.1.1 Excel-Bereich nach PowerPoint exportieren

755

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

758

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

759

12.1.4 PowerPoint-Folie als Objekt in Excel einbinden

762

12.1.5 Diagrammobjekte in eine Präsentation exportieren

764

12.2 Excel im Zusammenspiel mit Word

766

12.2.1 Excel-Bereich in Dokument exportieren

767

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

769

12.2.3 Markierten Bereich einer Excel-Tabelle in ein Dokument exportieren

773

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

775

12.3 Excel im Zusammenspiel mit Outlook

777

12.3.1 Kontaktdaten aus Excel nach Outlook exportieren

778

12.3.2 Termine aus Excel in den Outlook-Kalender schieben

782

12.3.3 Aktive Tabelle aus Excel heraus versenden

786

12.3.4 Aktive Tabelle als Anhang aus Excel heraus versenden

787

12.3.5 Aktive Arbeitsmappe per E-Mail versenden

789

12.3.6 Alle Dokumente aus einem Verzeichnis per E-Mail versenden

792

12.4 Excel im Zusammenspiel mit Access

794

12.4.1 Toolfrage und Randbedingungen

794

12.4.2 Anforderungen an das Tool

795

12.4.3 Die Umsetzung der Kernfunktionen

795

12.4.4 Befüllung der UserForm mit den wichtigsten Daten

797

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

798

12.4.6 Suche nach Datum

804

12.4.7 Termine erfassen

806

12.4.8 Änderung von Terminen

808

12.4.9 Termine löschen

811

12.4.10 Felder löschen

812

12.5 Excel im Zusammenspiel mit dem Internet Explorer

813

12.5.1 Eine Internetseite aus Excel aufrufen

813

12.5.2 Texte übersetzen mit Google

813

13 Datenfelder, ADO, Dictionaries und Collections programmieren

817

13.1 Aufgaben mithilfe 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

829

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

854

13.3.1 Daten verdichten

854

13.3.2 Bedingte Summierung mit mehreren Kriterien

857

13.3.3 Eine Unikatsliste erstellen

862

13.3.4 Anzahl von Bestellungen ermitteln

864

13.3.5 Doppelte Daten in einem Bereich ermitteln

869

13.4 Arbeiten mit Collections

871

13.4.1 Eindeutige Einträge über eine Collection bilden

871

13.4.2 Eine Collection aus einer Tabelle befüllen

874

13.5 Intelligente Arrays mit Zusatzfunktionen

876

13.5.1 Stack füllen und prüfen

876

13.5.2 Eine SortedList füllen und übergeben

878

14 Die Programmierung der Excel-Oberfläche

881

14.1 Die Programmierung von Kontextmenüs

881

14.1.1 Kontextmenüs deaktivieren

882

14.1.2 Das Zellen-Kontextmenü erweitern

883

14.1.3 Kontextmenü aufbauen (dreistufig)

885

14.1.4 Kontextmenü zurücksetzen

886

14.2 Die Ribbon-Programmierung

887

14.2.1 Der Custom UI Editor

887

14.2.2 Weitere wichtige Quellen und Hilfen

889

14.2.3 Ribbon mit Schaltflächen erstellen

891

14.2.4 Ribbon mit »ComboBox« erstellen

892

14.2.5 Ribbon mit bereits verfügbaren Funktionen bestücken

894

14.2.6 Den Backstage-Bereich programmieren

896

14.2.7 Eine Galerie mit Fotos erstellen

897

15 Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten

899

15.1 Kleinere Fehler beheben

899

15.1.1 Stimmt die Syntax?

899

15.1.2 Ist die Variablendefinition gegeben?

900

15.1.3 Objekt vorhanden?

900

15.1.4 Methode, Eigenschaft verfügbar?

901

15.2 Schwerwiegendere Fehler

901

15.2.1 Fehler im Vorfeld erkennen und reagieren

901

15.2.2 Fehler ignorieren

902

15.2.3 Fehlerursache ermitteln

902

15.2.4 Die Funktion »IsError«

903

15.3 Das Add-In MZ-Tools

904

15.3.1 Zeilennummern automatisch einfügen

905

15.3.2 Eine Fehlerbehandlung mit den MZ-Tools hinzufügen

906

15.4 Laufzeiten verkürzen

907

15.4.1 Variablen und Konstanten einsetzen

907

15.4.2 Berechnung und Bildschirmaktualisierung ausschalten

908

15.4.3 Integrierte Tabellenfunktionen anwenden

909

15.5 VBA-Projekte schützen

910

16 Typische Verarbeitungsaufgaben aus der Praxis

911

16.1 Daten übertragen

911

16.2 Daten im Batch verarbeiten

914

16.3 Daten verteilen

917

16.3.1 Die Tabellen entfernen

917

16.3.2 Die Verteilung der Zeilen auf die Tabellen

918

16.3.3 Die Plausibilität prüfen

921

16.3.4 Der Export der Tabellen

923

16.4 Berichtsfilterseiten erstellen

924

16.5 Daten löschen

927

16.5.1 Daten entfernen - Variante 1

927

16.5.2 Daten entfernen - Variante 2

928

16.5.3 Daten entfernen - Variante 3

929

16.6 Daten kennzeichnen

930

16.6.1 Doppelte Daten kennzeichnen (der Standard)

931

16.6.2 Doppelte Daten kennzeichnen (die Erweiterung)

932

16.6.3 Top-10-Werte aus einem Bereich ermitteln

935

16.7 Diagramme automatisch formatieren

938

16.8 Daten über den Einsatz von Wildcards suchen

942

16.9 Zwei identische Bereiche miteinander vergleichen

943

16.10 Suche nach einem Begriff unter Berücksichtigung der Formatierung

944

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

945

16.12 Einen Durchschnitt aus den Top-5-Werten ermitteln

948

16.13 Arbeitsmappen auf Knopfdruck automatisch erstellen

950

16.14 Alle Formeln einer Tabelle schützen und verstecken

951

16.15 Eine Unikatsliste über den Einsatz von SQL erstellen

952

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

955

16.17 Kriterien für eine Mehrfachfilterung aus Zellen beziehen

957

16.18 Bestimmte Zeichenfolge in einem Bereich entfernen

958

16.19 Automatisch eine Kopie der Mappe erstellen

959

16.20 Einen Excel-Bereich als Objekt nach PowerPoint übertragen

960

16.21 Einen formatierten Text in eine PowerPoint-Folie übertragen

962

16.22 Top-Werte ermitteln und kennzeichnen

965

16.23 Daten aus einer geschlossenen Mappe ziehen

967

16.24 Ein PDF in Excel erstellen und direkt versenden

969

16.25 Daten verdichten

970

16.25.1 Daten verdichten mithilfe von SUMMEWENNS

972

16.25.2 Daten mithilfe einer SQL-Anweisung verdichten

973

16.25.3 Daten verdichten mithilfe des Datenfilters

975

16.26 Daten in Mappen aktualisieren, ohne diese zu öffnen

976

16.27 Eine Suchfunktion für eine Listbox erstellen

979

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

982

16.29 Bedingte Summierung direkt im Arbeitsspeicher vornehmen

985

16.30 Über zwei Arrays Jahresumsätze pro Monat auswerten

990

16.31 Bedingte Summierung mit mehreren Kriterien im Speicher durchführen

993

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

997

16.33 Excel erweitern - doppelte Werte finden und unterschiedlich kennzeichnen

1000

16.34 Bestimmte Zeilen übertragen - Kriterien dynamisch zusammenstellen

1003

16.35 Der Vollautomat - alle Dateien eines Verzeichnisses schnell verarbeiten

1006

16.36 Eine Mehrfachsuche in einer Tabelle mit einem Array beschleunigen

1009