VBA mit Excel - ReadingSample - beck-shop.de · PDF file65 4 Kapitel 4 Sprachelemente in VBA...

Galileo Computing

VBA mit Excel

Das umfassende Handbuch

Bearbeitet vonBernd Held

1. Auflage 2013. Buch. ca. 918 S.ISBN 978 3 8362 2579 3

Format (B x L): 16 x 24 cm

Weitere Fachgebiete > EDV, Informatik > Programmiersprachen: Methoden >Microsoft Programmierung

schnell und portofrei erhältlich bei

Die Online-Fachbuchhandlung beck-shop.de ist spezialisiert auf Fachbücher, insbesondere Recht, Steuern und Wirtschaft.Im Sortiment finden Sie alle Medien (Bücher, Zeitschriften, CDs, eBooks, etc.) aller Verlage. Ergänzt wird das Programmdurch Services wie Neuerscheinungsdienst oder Zusammenstellungen von Büchern zu Sonderpreisen. Der Shop führt mehr

als 8 Millionen Produkte.

http://www.beck-shop.de/Held-VBA-Excel/productview.aspx?product=13059312&utm_source=pdf&utm_medium=clickthru_lp&utm_campaign=pdf_13059312&campaign=pdf/13059312

http://www.beck-shop.de/trefferliste.aspx?toc=8299

http://www.beck-shop.de/trefferliste.aspx?toc=8299

LeseprobeIn diesem Auszug erfahren Sie von Bernd Held alles über die prak-tische Anwendung der VBA-Sprachelemente. Außerdem lernen Sie, wie Sie Diagramme programmieren. Außerdem erhalten Sie das vollständige Inhalts- und Stichwortverzeichnis aus dem Buch.

Bernd Held

VBA mit Excel – Das umfassende Handbuch918 Seiten, 2013, mit DVD, 49,90 € ISBN 978-3-8362-2579-3

www.galileocomputing.de/3426

»Sprachelemente in VBA in der praktischen Anwendung« »Diagramme programmieren«

Inhaltsverzeichnis

Index

Der Autor

Wissen, wie’s geht.

65

4

Kapitel 4

Sprachelemente in VBA in der praktischen Anwendung

Das Wesentliche, was eine Programmiersprache ausmacht, sind ihre

Sprachelemente. In diesem Kapitel erfahren Sie, wie Sie mithilfe von

Abfragen, Schleifen und anderen Anweisungen Ihre Programme flexi-

bel gestalten können.

Die Sprachelemente lassen sich nicht mit dem Makrorekorder aufzeichnen und müs-

sen von Ihnen selbst erstellt werden. Der richtige Einsatz der Sprachelemente macht

letztendlich die Kunst der Programmierung aus.

Sie finden alle Beispiele auf der mitgelieferten CD-ROM in der Datei SPRACHELE-

MENTE.XLS.

4.1 Verzweigungen

Mit Verzweigungen können Sie in Excel bestimmte Zustände abfragen und je nach

Zustand anders reagieren. Dieses Sprachelement kann nicht mit dem Makrorekorder

aufgezeichnet werden und stellt mit das wichtigste Element in der Programmierung

dar. Jede Verzweigung hat eine oder mehrere Bedingungen, dann einen Zweig, der

eintritt, wenn die Bedingung zutrifft, sowie einen Zweig, der abgearbeitet wird, wenn

die Bedingung nicht zutrifft.

Bei den Verzweigungen unterscheidet man zwei Formen:

� Normalform:

IF Bedingung Then Aktion1 Else Aktion2

� Blockform:

If Bedingung ThenAktion1aAktion1bAktion1c

Else

4 Sprachelemente in VBA in der praktischen Anwendung

66

Aktion2aAktion2b

End if

Wird die Blockform verwendet, können mehrere Schritte nacheinander durchge-

führt werden. Beachten Sie aber, dass die Anweisung mit einem End If abgeschlossen

werden muss.

Es folgen nun einige typische Beispiele für den Einsatz von Verzweigungen.

4.1.1 Normale Verzweigungen

Schriftschnitte abfragen

Fragen Sie im nächsten Beispiel nach, ob eine Zelle den Schriftschnitt Fett enthält,

und geben Sie je nach Ergebnis eine Meldung auf dem Bildschirm aus.

Sub SchriftschnittFett()Sheets("Verzweigungen").Activate

If Range("A1").Font.Bold = True ThenMsgBox "Schriftschnitt Fett"

ElseMsgBox "Schriftschnitt nicht Fett"

End If

End Sub

Listing 4.1 Schriftschnitte ermitteln

Selbstverständlich können Sie in der Abfrage auch mehrere Zustände abfragen. Dazu

verwenden Sie zwischen den beiden Abfragen den Operator And, wenn beide Bedin-

gungen erfüllt sein müssen, bzw. den Operator Or, wenn nur eine von beiden Bedin-

gungen erfüllt sein muss. So fragen Sie im nächsten Beispiel aus Listing 4.2 ab, ob eine

Zelle mit dem Schriftschnitt Fett und der Schriftfarbe Rot formatiert ist:

Sub SchriftschnittFettUndFarbeRot()

Sheets("Verzweigungen").Activate

If Range("A1").Font.Bold = True And _Range("A1").Font.ColorIndex = 3 ThenMsgBox "Schriftschnitt Fett und Schriftfarbe Rot"

ElseMsgBox "Schrift nicht Fett oder Schrift nicht rot"

4.1 Verzweigungen

67

4

End If

End Sub

Listing 4.2 Schriftschnitt und Schriftfarbe abfragen

Im zweiten Fall erstellen Sie eine Abfrage, in der die Zelle B1 entweder die Schriftfarbe

Rot oder den Schriftschnitt Kursiv erhalten muss. Sie sehen das Makro für diese

Aufgabe in Listing 4.3:

Sub SchriftschnittKursivOderFarbeRot()

Sheets("Verzweigungen").Activate

If Range("A2").Font.Italic = True Or _Range("A2").Font.ColorIndex = 3 ThenMsgBox "Eine der beiden Bedingungen trifft zu!"

ElseMsgBox "Keine der beiden Bedingungen trifft zu!"

End If

End Sub

Listing 4.3 Schriftschnitt und Schriftfarbe abfragen (Variante 2)

Zellen auf Werte prüfen

Wenn Sie gezielt nach Werten in den Zellen fragen möchten, prüfen Sie vorher, ob die

Zelle einen numerischen Wert aufweist. Dazu setzen Sie die Funktion IsNumeric aus

Listing 4.4 ein.

Sub WerteÜberprüfen()

Sheets("Verzweigungen").ActivateRange("A3").Select

If IsNumeric(ActiveCell.Value) _Then MsgBox "In Zelle " & ActiveCell.Address & _" steht ein numerischer Wert!" _

Else MsgBox "Zelle " & ActiveCell.Address & _" steht ein Textwert!"

End Sub

Listing 4.4 Prüfung von Zellen auf numerischen Inhalt


68

Tipp

Neben der Funktion IsNumeric gibt es weitere Funktionen, mit denen Sie Ihre Daten

prüfen können. Eine davon ist die Funktion IsDate. Die Funktion IsDate gibt den

Wert True zurück, wenn der Ausdruck ein Datum ist oder in ein gültiges Datum

umgewandelt werden kann. Andernfalls wird der Wert False zurückgegeben. In

Windows liegen gültige Datumswerte im Bereich zwischen dem 1. Januar 100 n. Chr.

und dem 31. Dezember 9999 n. Chr. vor. Auf anderen Betriebssystemen können

andere Bereiche gelten.

Zellen auf Datumswerte prüfen

Im Beispiel aus Listing 4.5 wird der Eintrag in Zelle A4 überprüft:

Sub ZelleAufDatumswertPrüfen()


If IsDate(ActiveCell.Value) ThenMsgBox "Gültiges Datum in Zelle " & ActiveCell.Address

ElseMsgBox "Es handelt sich um kein gültiges Datum!"

End If

End Sub

Listing 4.5 Zellen auf Datumswert prüfen

Hin und wieder müssen Sie über ein Makro prüfen, ob eine Zelle bereits einen Wert

enthält. Hierzu setzen Sie die Funktion IsEmpty ein oder geben folgende Zeile ein:

If ActiveCell.Value = "" then

Die elegantere Variante sieht wie folgt aus:

Sub ZelleLeer()


If IsEmpty(ActiveCell) ThenMsgBox "Die Zelle " & ActiveCell.Address & " ist leer"

ElseMsgBox "Zelle enthält den Wert: " & ActiveCell.Value

4.1 Verzweigungen

69

4

End If

End Sub

Listing 4.6 Zelle auf Inhalt prüfen

Neben den Funktionen IsEmpty und IsDate gibt es weitere Prüffunktionen, die Sie

Tabelle 4.1 entnehmen können.

4.1.2 Verzweigungen in Blöcke einteilen

Bei den bisherigen Beispielen haben Sie jeweils eine Aktion im Then- bzw. Else-Zweig

ausgeführt. Wenn Sie mehrere Anweisungen innerhalb einer Bedingung ausführen

möchten, dann bietet sich – wie auch bei den letzten Beispielen durchgeführt – die

Blockform an.

Mehrzeiliger Zweig

Im nächsten Beispiel soll auf dem Tabellenblatt Verzweigungen der Name des in

Windows angemeldeten Anwenders ausgelesen werden. Im Fall, dass der richtige

Anwender »vor dem« PC sitzt (hier Held), soll das Wort OK in die Zelle A1 geschrieben

Funktion Beschreibung

IsEmpty Gibt einen Wert vom Typ Boolean zurück, der angibt, ob eine Vari-

able initialisiert wurde.

IsArray Gibt einen Wert vom Typ Boolean zurück, der angibt, ob eine Vari-

able ein Datenfeld ist.

IsDate Gibt einen Wert vom Typ Boolean zurück, der angibt, ob ein Aus-

druck in ein Datum umgewandelt werden kann.

IsError Gibt einen Wert vom Typ Boolean zurück, der angibt, ob ein Aus-

druck ein Fehlerwert ist.

IsNull Gibt einen Wert vom Typ Boolean zurück, der angibt, ob ein Aus-

druck keine gültigen Daten (Null) enthält.

IsNumeric Gibt einen Wert vom Typ Boolean zurück, der angibt, ob ein Aus-

druck als Zahl ausgewertet werden kann.

IsObject Gibt einen Wert vom Typ Boolean zurück, der angibt, ob ein Bezeich-

ner eine Objekt-Variable darstellt.

Tabelle 4.1 Die Prüffunktionen von Excel


70

werden. Im anderen Fall soll das Wort Falsch in die Zelle A1 und der Name des

Anwenders in die Zelle B1 geschrieben werden.

Sub AnwenderOK()

If Environ("username") = "Held" ThenSheets("Verzweigungen").Range("A1").Value = "OK"

ElseSheets("Verzweigungen").Range("A1").Value = "Falsch"Sheets("Verzweigungen").Range("B1").Value = _Environ("username")

End If

End Sub

Listing 4.7 Anwender auslesen

Mehrere Verzweigungen verschachteln

Selbstverständlich können Sie mehrere Bedingungen ineinander verschachteln. Zur

Verdeutlichung der Funktionsweise soll die Zelle A10 im Tabellenblatt Verzweigun-

gen überwacht werden. Je nach Wert der Zelle soll in der Nebenzelle ein Eintrag vor-

genommen werden.

Sub VerschachteltesIF()


If ActiveCell.Value < 10 ThenActiveCell.Offset(0, 1).Value = "In Ordnung"ActiveCell.Interior.ColorIndex = xlColorIndexNone

ElseIf ActiveCell.Value <= 15 ThenActiveCell.Offset(0, 1).Value = "langsam kritisch"ActiveCell.Interior.ColorIndex = xlColorIndexNone

ElseIf ActiveCell.Value >= 16 ThenActiveCell.Offset(0, 1).Value = "zu hoch"ActiveCell.Interior.ColorIndex = 3

End If

End Sub

Listing 4.8 Mehr als zwei Möglichkeiten in einer IF-Anweisung abfragen

Bei einem Wert über 16 wird der Zellenhintergrund automatisch mit der Farbe Rot

gefüllt.

4.2 Die Anweisung »Select Case« für mehr Übersicht

71

4

4.1.3 Die Kurzform einer Verzweigung

Neben der Verzweigung If Then Else gibt es eine weitere Möglichkeit, um Werte zu

überprüfen. Die Funktion heißt IIF.

Die Funktion IIF hat folgende Syntax:

IIf(expr, truepart, falsepart)

� Mit dem Argument expr geben Sie den Ausdruck an, der ausgewertet werden soll.

� Das Argument truepart liefert den zurückgegebenen Wert oder Ausdruck, wenn

expr den Wert True ergibt.

� Das Argument falsepart stellt den zurückgegebenen Wert oder Ausdruck dar,

wenn expr den Wert False liefert.

Diese Funktion wertet immer sowohl den Teil truepart als auch den Teil falsepart

aus, auch dann, wenn nur einer von beiden Teilen zurückgegeben wird.

In einem Beispiel angewendet, überprüfen Sie den Inhalt einer Zelle. Sofern der

Inhalt numerisch ist, schreiben Sie in die Nebenzelle den Text »Numerisch«. Sollte es

sich um einen Text handeln, dann schreiben Sie in die Nebenzelle den Text »Alpha-

numerisch«.

Sub AlternativVerzweigung()

ActiveCell.Offset(0, 1).Value = _IIF(IsNumeric(ActiveCell), "Numerisch","Alphanumerisch")

End Sub

Listing 4.9 Kurzprüfung auf numerischen Inhalt

Das Ergebnis dieser Auswertung schreiben Sie über die Anweisung ActiveCell.Off-

set(0, 1).Value in die Nebenzelle. Dabei weist die Eigenschaft Offset zwei Argu-

mente auf. Das erste Argument gibt die Zeilenverschiebung bekannt – von der

aktiven Zelle aus betrachtet. Da Sie in derselben Zeile bleiben möchten, ist demnach

die Verschiebung gleich 0. Das zweite Argument gibt die Spaltenverschiebung, von

der aktiven Zelle aus gesehen, bekannt. Da das Ergebnis in die Nebenspalte geschrie-

ben werden soll, wird dieses Argument mit dem Wert 1 bestückt.


Das Makro in Listing 4.8 war doch recht unübersichtlich. Eine bessere Lösung, um

diese Aufgabe umzusetzen, ist die Verwendung eines anderen Sprachelements, näm-

lich der Select Case-Anweisung.


72

Sub SelectCase()


Select Case ActiveCell.ValueCase Is < 10ActiveCell.Offset(0, 1).Value = "In Ordnung"ActiveCell.Interior.ColorIndex = xlColorIndexNone

Case Is <= 15ActiveCell.Offset(0, 1).Value = "langsam kritisch"ActiveCell.Interior.ColorIndex = xlColorIndexNone

Case Is >= 16ActiveCell.Offset(0, 1).Value = "zu hoch"ActiveCell.Interior.ColorIndex = 3

End Select

End Sub

Listing 4.10 Mehrere Farben in einer übersichtlichen CASE-Anweisung abfragen

Tabelle 4.2 enthält eine Liste der Vergleichsoperatoren und die Bedingungen, unter

denen das Ergebnis True, False oder 0 wird:

4.2.1 Indizes in Farben umsetzen

Im nächsten Beispiel (Listing 4.11) werden mithilfe der Select Case-Anweisung Farben

anhand des Farbindex umgesetzt. In Excel hat jede Farbe eine eindeutige Nummer.

Vergleichsoperator Erklärung

< Kleiner als

<= Kleiner oder gleich

> Größer als

>= Größer oder gleich

= Gleich

<> Ungleich

Tabelle 4.2 Die Vergleichsoperatoren in Excel


73

4

Ausgehend von der Nummer, die in Zelle A15 steht, wird die dazugehörige Farbe in

der Nebenzelle B15 angezeigt.

Sub FarbenIndexUmsetzen()


Select Case ActiveCell.ValueCase 1 'schwarzActiveCell.Offset(0, 1).Interior.ColorIndex = 1

Case 2 'weißActiveCell.Offset(0, 1).Interior.ColorIndex = 2

Case 3 'rotActiveCell.Offset(0, 1).Interior.ColorIndex = 3

Case 4 'hellgrünActiveCell.Offset(0, 1).Interior.ColorIndex = 4

Case 5 'blauActiveCell.Offset(0, 1).Interior.ColorIndex = 5

Case 6 'gelbActiveCell.Offset(0, 1).Interior.ColorIndex = 6

Case 7 'violettActiveCell.Offset(0, 1).Interior.ColorIndex = 7

Case 8 'hellblauActiveCell.Offset(0, 1).Interior.ColorIndex = 8

Case 9 'braunActiveCell.Offset(0, 1).Interior.ColorIndex = 9

Case 10 'dunkelgrünActiveCell.Offset(0, 1).Interior.ColorIndex = 10

Case ElseMsgBox "Dieser Index ist im Makro nicht zugeordnet!"ActiveCell.Offset(0, 1).Interior.ColorIndex = _

xlColorIndexNoneEnd Select

End Sub

Listing 4.11 Farbindizes ermitteln

Wie Sie dieses Makro noch besser automatisieren können, sodass die Farbe sich

sofort nach der Eingabe des Index automatisch anpasst, erfahren Sie in Kapitel 12.


74

4.2.2 Zahlen in Texte umwandeln

In der nächsten Aufgabe aus Listing 4.12 werden Zahlen in Texte umgesetzt. Ausge-

wertet wird die Zelle A20. Die Zahlen 1 bis 10 sollen in Form der Texte Eins bis Zehn in

der Nebenzelle B21 ausgegeben werden. In dem Fall, dass eine Zahl außerhalb des

Gültigkeitsbereiches in der Zelle steht, soll der Buchstabe X mit der Formatierungs-

option xLFill in die Nebenzelle eingefügt werden. Diese Option bewirkt, dass der

Buchstabe so oft wiederholt wird, bis die ganze Zelle damit ausgefüllt ist. Wahr-

scheinlich haben Sie diese Art von Formatierung schon einmal auf bestimmten

Bankformularen gesehen.

Sub ZahlenInTextUmsetzen()

Sheets("Verzweigungen").ActivateRange("A20").SelectActiveCell.Offset(0, 1).HorizontalAlignment = xlGeneral

Select Case ActiveCell.ValueCase 1: ActiveCell.Offset(0, 1).Value = "Eins"Case 2: ActiveCell.Offset(0, 1).Value = "Zwei"Case 3: ActiveCell.Offset(0, 1).Value = "Drei"Case 4: ActiveCell.Offset(0, 1).Value = "Vier"Case 5: ActiveCell.Offset(0, 1).Value = "Fünf"Case 6: ActiveCell.Offset(0, 1).Value = "Sechs"Case 7: ActiveCell.Offset(0, 1).Value = "Sieben"Case 8: ActiveCell.Offset(0, 1).Value = "Acht"Case 9: ActiveCell.Offset(0, 1).Value = "Neun"Case 10: ActiveCell.Offset(0, 1).Value = "Zehn"Case Else

MsgBox "Diese Zahl kennt das Makro nicht!"ActiveCell.Offset(0, 1).Value = "X"ActiveCell.Offset(0, 1).HorizontalAlignment= xlFill

End Select

End Sub

Listing 4.12 Zahleneingaben in ausgeschriebene Texte umwandeln

4.2.3 Mit VBA Formate prüfen

Die Select Case-Anweisung können Sie auch verwenden, um zu prüfen, welches For-

mat eine bestimmte Zelle hat. Sehen Sie sich dazu das Beispiel aus Listing 4.13 an:


75

4

Sub FormatErkennen()


Select Case ActiveCell.NumberFormatCase "General"

MsgBox "Das Standardformat"Case "0.00"

MsgBox "Einfache Zahl mit zwei Kommastellen"Case "#,##0.00"

MsgBox "Zahl mit Trennzeichen und 2 Kommastellen"Case "@"

MsgBox "Textformat"Case Else

MsgBox "Format wurde nicht erkannt"End Select

End Sub

Listing 4.13 Zahlenformate prüfen

4.2.4 Fensterstatus ermitteln

Wenn Sie in die obere rechte Ecke Ihres Fensters sehen, werden Sie drei Symbole er-

kennen. Diese drei Symbole stellen den Status Ihres Arbeitsfensters dar. Welchen

Status das Fenster gerade hat, können Sie über die Select Case-Anweisung heraus-

finden.

Sub FensterStatusErmitteln()Dim Str As String

Select Case Application.WindowStateCase xlMaximizedStr = "xlMaximized"

Case xlMinimizedStr = "xlMinimized"

Case xlNormalStr = "xlNormal"

End Select

MsgBox "Das Fenster ist gerade im Status: " & StrEnd Sub

Listing 4.14 Fensterstatus ermitteln


76

4.2.5 Makro bei ungültigem Wert beenden

Im letzten Beispiel mit der Anweisung Select Case geht es darum, den Wert in Zelle

A30 zu überprüfen. Steht in dieser Zelle der Buchstabe A, B oder C, wird das Makro in

Listing 4.15 fortgesetzt. Weist die Zelle jedoch einen anderen Wert auf, wird das Makro

sofort abgebrochen.

Sub GültigkeitenAbfragen()


Select Case ActiveCell.ValueCase Is = "A", "B", "C"Case ElseMsgBox "Ende des Makros"Exit Sub

End Select

MsgBox "Das Makro geht weiter ..."'weitere Befehle ...

End Sub

Listing 4.15 Gültigkeiten abfragen mit der CASE-Anweisung

bei Unterscheidung von Groß- und Kleinschreibung

Tipp

Übrigens wird im letzten Makro die Groß- und Kleinschreibung unterschieden. Nur

die korrekte Schreibweise liefert das gewünschte Ergebnis.

Möchten Sie erreichen, dass Excel zwischen Groß- und Kleinschreibung nicht unter-

scheidet, ergänzen Sie obiges Makro noch um die Kleinschreibweise der Buchstaben,

oder setzen Sie die Funktion UCase ein.

Sub GültigkeitenAbfragenII()


Select Case UCase(ActiveCell.Value)Case Is = "A", "B", "C"Case ElseMsgBox "Ende des Makros"


77

4

Exit SubEnd SelectMsgBox "Das Makro geht weiter..."'weitere Befehle...

End Sub

Listing 4.16 Gültigkeiten abfragen bei Ignorierung

von Groß- und Kleinschreibung

Die Funktion UCase wandelt Kleinbuchstaben automatisch in Großbuchstaben um.

Analog dazu ändert die Funktion LCase Großbuchstaben in Kleinbuchstaben um.

4.2.6 Excel-Version feststellen

In der nächsten Aufgabe für dieses Sprachelement sollen Sie feststellen, mit welcher

Excel-Version Sie arbeiten. Für diese Aufgabe können Sie die Eigenschaft Version aus-

werten, die über einen numerischen Wert Ihre Excel-Installation identifiziert.

Sub ExcelVersionFeststellen()

MsgBox Application.Version

Select Case Val(Application.Version)Case 5MsgBox "Excel 5"

Case 7MsgBox "Excel 7/95"

Case 8MsgBox "Excel 8/97"

Case 9MsgBox "Excel 2000"






Case ElseMsgBox "Unbekannte Version von Excel"


78

End Select

End Sub

Listing 4.17 Excel-Version ermitteln

Werten Sie über die Funktion Val die Versionsnummer aus, die Ihnen die Eigenschaft

Version meldet. Innerhalb der Select Case-Anweisung überprüfen Sie die Versions-

nummern. Trifft eine Bedingung zu, wird eine Meldung am Bildschirm ausgegeben,

die Ihnen die Excel-Version meldet. Danach wird die Select Case-Anweisung direkt

beendet.

4.3 Schleifen in Excel praxisgerecht einsetzen

Schleifen werden in Excel dazu verwendet, Abläufe mehrmals hintereinander durch-

zuführen. Die Schleifen werden so lange durchlaufen, bis eine oder mehrere Bedin-

gungen zutreffen, die dann einen Abbruch der Schleife bewirken. Je nach verwende-

ter Schleife findet die Abbruchprüfung am Anfang oder am Ende der Schleife statt.

Lernen Sie auf den nächsten Seiten klassische Beispiele für den sinnvollen Einsatz

von Schleifen kennen.

4.3.1 »For Next«-Schleifen

Sie können die Schleife For Next verwenden, um einen Block von Anweisungen eine

bestimmte Anzahl von Wiederholungen mal ausführen zu lassen. For Next-Schleifen

verwenden eine Zählervariable, deren Wert mit jedem Schleifendurchlauf erhöht

oder verringert wird. Sie brauchen daher nicht daran zu denken, den Zähler selbst

hoch- oder herunterzusetzen.

Die Syntax dieser Schleife lautet:

For Zähler = Anfang To Ende [Step Schritt][Anweisungen][Exit For][Anweisungen]Next [Zähler]

� Das Argument Zähler ist erforderlich und ist eine numerischen Variable, die als

Schleifenzähler dient.

� Das Argument Anfang repräsentiert den Startwert von Zähler.


79

4

� Mit dem Argument Ende legen Sie den Endwert von Zähler fest. Das Argument

Schritt ist optional. Hier können Sie den Betrag bestimmen, um den Zähler bei

jedem Schleifendurchlauf verändert wird. Falls kein Wert angegeben wird, ist die

Voreinstellung eins.

� Unter Anweisungen steht eine oder mehrere Anweisungen zwischen For und Next,

die so oft wie angegeben ausgeführt werden.

Innerhalb einer Schleife kann eine beliebige Anzahl von Exit For-Anweisungen an

beliebiger Stelle als alternative Möglichkeit zum Verlassen der Schleife verwendet

werden.

Üben Sie diese Art von Schleife anhand der nächsten Aufgaben.

Tabellenblätter einfügen

Im ersten Beispiel (Listing 4.18) sollen in eine neue Arbeitsmappe genau 50 Tabellen-

blätter eingefügt werden. Manuell wäre das ein wenig mühselig. Leichter geht es mit-

hilfe einer For Next-Schleife.

Sub TabellenblätterAnlegen()Dim intTab As Integer

Workbooks.Add

For intTab = 1 To 50Worksheets.Add

Next ibtTab

End Sub

Listing 4.18 Neue Tabellenblätter einfügen

Info

Die Tabellenblätter haben den Namen Tabelle1 bis Tabelle(N), je nachdem, wie

hoch Sie den Wert für die Tabellenblätter standardmäßig unter Extras • Optionen •

Allgemein (Excel 97–Excel 2003) bzw. bei den Excel-Optionen auf dem Register

Häufig verwendet (Excel 2007) eingestellt haben.

In Excel 2010 und Excel 2013 können Sie diese Einstellung unter Datei • Optionen •

Allgemein • Beim Erstellen neuer Arbeitsmappen • So viele Arbeitsblätter einfü-

gen vornehmen.


80

Tabellenblätter benennen

Jetzt möchten Sie den neu eingefügten Tabellenblättern andere Namen geben. Dabei

soll als Tabellenblattname Ihr eigener Name mit dem Zusatz einer fortlaufenden

Nummer gewählt werden.

Sub TabellenNamenFestlegen()Dim intTab As Integer

For intTab = 1 To 50Worksheets(intTab).Name = _Environ("UserName") & intTab

Next intTab

End Sub

Listing 4.19 Mehrere Tabellenblätter mit

fortlaufender Nummerierung benennen

Jede zweite Zeile ausblenden

Im nächsten Beispiel soll auf einem Tabellenblatt jede zweite Zeile ausgeblendet wer-

den. Dabei sollen lediglich die ersten 20 Zeilen betroffen sein. Realisieren können Sie

diese Aufgabe mit folgendem Makro aus Listing 4.20:

Sub JedeZweiteZeileAusblenden()Dim intZ As Integer

Sheets("Schleifen").ActivateFor intZ = 1 To 20 Step 2Rows(intZ).Hidden = True

Next intZ

End Sub

Listing 4.20 Jede zweite Zeile ausblenden

Setzen Sie die Eigenschaft Hidden auf den Wert True, wenn Sie Zeilen ausblenden

möchten. Wenn Sie dasselbe Makro mit der Anweisung Rows(intZ).Hidden = False

starten, werden Ihre ausgeblendeten Zeilen wieder eingeblendet.

Tipp

Wenn Sie die Anweisung Rows.Hidden = False einsetzen, werden alle Zeilen wieder

eingeblendet.


81

4

Tabellenblätter pro Arbeitsmappe ermitteln

Wie schon vorher erwähnt wurde, müssen Sie bei dieser Schleife einen Zähler mitlau-

fen lassen. Wenn Sie die vorherigen Beispiele betrachten, werden Sie feststellen, dass

die Zahl nach dem Argument To jeweils fix ist. Diesen Wert können Sie aber auch, je

nach Aufgabe, variabel halten. So bietet sich der variable Zähler an, wenn Sie in einer

Arbeitsmappe auf jedem Tabellenblatt einen bestimmten Eintrag vornehmen müs-

sen. Das Problem ist jetzt nur, dass Sie nicht davon ausgehen können, dass sich in

jeder Mappe gleich viele Tabellenblätter befinden. Also müssen Sie zuerst einmal

herausfinden, wie viele Tabellenblätter die Arbeitsmappe enthält.

Sub EintragVornehmen()Dim intTab As Integer

For intTab = 1 To ActiveWorkbook.WorkSheets.CountWorksheets(intab).Range("E1").Value = Date

Next intTab

End Sub

Listing 4.21 Auf allen Tabellenblättern einen Eintrag vornehmen

Mehrere Tabellen mit Datumsfunktion ausstatten

Über die Eigenschaft Count ermitteln Sie die Anzahl der Tabellenblätter (Worksheets)

in der vorliegenden Arbeitsmappe. Genauso oft soll die Schleife durchlaufen werden,

die dafür sorgt, dass das aktuelle Datum auf jedem Tabellenblatt in Zelle E1 eingetra-

gen wird.

Passwortvergabe auf allen Tabellenblättern in einer Arbeitsmappe

In Excel können Sie sowohl die Arbeitsmappe als auch einzelne Tabellenblätter vor

Veränderungen schützen. Wenn Sie alle Tabellenblätter schützen und dabei dasselbe

Passwort verwenden möchten, können Sie ebenfalls die For Next-Schleife wie in Lis-

ting 4.22 einsetzen:

Sub PasswortAufAllenTabellenblätternSetzen()Dim intTab As Integer

For intTab = 1 To Worksheets.CountSheets(intTab).Protect "Passwort"

Next intTab

End Sub

Listing 4.22 Einheitliche Passwortvergabe auf allen

Tabellenblättern einer Arbeitsmappe


82

Mithilfe der Methode Protect können Sie eine Tabelle schützen. Das Passwort geben

Sie in doppelten Anführungszeichen ein.

Schutz auf allen Tabellenblättern aufheben

Um alle Tabellenblätter der Arbeitsmappe wieder ändern zu können, heben Sie den

Blattschutz mit dem Makro aus Listing 4.23 wieder auf:

Sub PasswortLöschenAufAllenTabellenblättern()Dim intTab As Integer

For intTab = 1 To Worksheets.CountSheets(intTab).Unprotect "Passwort"Next intTab

End Sub

Listing 4.23 Passwort auf allen Tabellenblättern einer Arbeitsmappe löschen

Mithilfe der Methode Unprotect heben Sie den Blattschutz einer Tabelle auf. Je nach-

dem, ob Sie überhaupt ein Passwort beim Schützen der Tabelle vergeben haben, müs-

sen Sie das Passwort der Methode mitgeben oder eben weglassen.

Tipp

Wenn Sie für jedes Tabellenblatt ein anderes Passwort verwenden möchten, kön-

nen Sie beispielsweise den jeweils aktuellen Zählerstand wie in Listing 4.24 verwen-

den und diesen an das Passwort anhängen.

Sub PasswortAufAllenTabellenblätternDynamisch()Dim intTab As Integer

For intTab = 1 To Worksheets.CountSheets(intTab).Protect "Passwort" & intTab

Next intTab

End Sub

Listing 4.24 Dynamisches Passwort in Tabellen setzen

Hier lautet das Passwort des ersten Blattes Passwort1, das Passwort des zweiten Blat-

tes Passwort2 usw. Beim Aufheben dieser Passwörter müssen Sie genauso vorgehen

wie beim Setzen der Passwörter.


83

4

Sub PasswortEntfernenAufAllenTabellenblättern()Dim intTab As Integer

For intTab = 1 To Worksheets.CountSheets(intTab).Unprotect "Passwort" & intTab

Next intTab

End Sub

Listing 4.25 Dynamisches Passwort in Tabellen entfernen

Arbeitsmappen ausdünnen

Auch zum Ausdünnen von Tabellenblättern aus Arbeitsmappen eignet sich diese

Schleife sehr gut. Nehmen wir an, Sie wollen alle Tabellenblätter bis auf das erste Blatt

aus einer Arbeitsmappe löschen:

Sub LöscheAlleTabellenBisAufDieErste()Dim intTab As Integer

Application.DisplayAlerts = False

For intTab = ActiveWorkbook.Sheets.Count To 1 Step –1Sheets(intTab).Delete

Next intTab

Application.DisplayAlerts = True

End Sub

Listing 4.26 Löschen von Tabellenblättern

Die Eigenschaft DisplayAlerts setzen Sie auf den Wert False. Damit brauchen Sie das

Löschen der einzelnen Tabellenblätter nicht jeweils zu bestätigen. Vergessen Sie

nicht, am Ende des Makros die Eigenschaft DisplayAlerts wieder auf den Wert True

zu setzen, damit zukünftig Meldungen wieder angezeigt werden. Um eine Tabelle zu

löschen, setzen Sie die Methode Delete ein.

Achten Sie darauf, dass Sie die Schleife beim Löschen von Tabellen rückwärts laufen

lassen müssen!

4.3.2 »For Each Next«-Schleifen

Die Schleife For Each Next wiederholt eine Gruppe von Anweisungen für jedes Ele-

ment in einem Datenfeld oder einer Auflistung.


84

Die Syntax dieser Schleife lautet:

For Each Element In Gruppe[Anweisungen][Exit For][Anweisungen]Next [Element]

Das Argument Element stellt die Variable zum Durchlaufen der Elemente unserer

Auflistung oder des Datenfeldes dar. Bei Auflistungen ist für Element nur eine Vari-

able vom Typ Variant, eine allgemeine Objektvariable oder eine beliebige spezielle

Objektvariable zulässig. Bei Datenfeldern ist für Element nur eine Variable vom Typ

Variant zulässig. Das nächste Argument Gruppe steht für den Namen einer Objekt-

auflistung oder eines Datenfeldes. Das letzte Argument, Anweisungen, ist optional

und führt eine oder mehrere Anweisungen durch, die für jedes Element in Gruppe

ausgeführt werden sollen.

Auch diese wichtige Schleife üben wir nun anhand praxisnaher Aufgaben.

Verdopplung der Werte innerhalb einer Markierung

Im ersten Beispiel für eine For Each Next-Schleife (Listing 4.27) sollen innerhalb einer

Markierung alle Werte verdoppelt werden.

Sub ZellenInMarkierungMultiplizieren()Const Multi = 2Dim Zelle As Range

For Each Zelle In SelectionIf IsNumeric(Zelle.Value) = True ThenZelle.Value = Zelle.Value * Multi

End IfNext Zelle

End Sub

Listing 4.27 Werte innerhalb eines markierten Bereichs verdoppeln

Die Variable Zelle wird als Range deklariert. Damit haben Sie Zugriff auf alle Befehle,

die für Zellen angeboten werden. In einer Schleife arbeiten Sie alle Zellen der Markie-

rung ab. Prüfen Sie innerhalb der Schleife über die Funktion IsNumeric, ob die Zelle

auch einen numerischen Wert enthält.


85

4

Tabellenblatt durchsuchen

Auch bei der Suche nach Daten leistet Ihnen diese Schleife gute Dienste. So sucht das

folgende Makro auf dem aktiven Tabellenblatt nach einem Suchbegriff, den Sie vor-

her in einer Input-Box eingegeben haben. Das Makro klappert nun jede Zelle inner-

halb der Markierung ab und hält sofort an, wenn es bei der Suche fündig wird. Die

gefundene Zelle wird dann markiert. Falls das Makro zu keinem Ergebnis kommt,

wird eine Meldung auf dem Bildschirm ausgegeben. Vor dem Start des Makros mar-

kieren Sie bitte noch den Bereich, in dem Excel nach dem Begriff suchen soll.

Sub DatenSuchen()Dim Zelle As RangeDim strText As String

strText = InputBox _("Bitte geben Sie den Suchbegriff ein!")

If strText = "" Then Exit Sub

For Each Zelle In ActiveSheet.UsedRangeIf Zelle = strText ThenZelle.SelectExit Sub

End IfNext Zelle

MsgBox "Suchbegriff nicht gefunden!"End Sub

Listing 4.28 Tabellenblatt durchsuchen

Im obigen Makro wird eine Eingabe vom Benutzer gefordert. Drückt der Anwender in

der Input-Box auf die Schaltfläche Abbrechen, wird das durch die Zeile If strText =

"" Then Exit Sub abgefangen. Die Suche wird dann erst gar nicht begonnen. Im Nor-

malfall, also wenn der Anwender einen Suchbegriff eingibt und mit OK bestätigt,

wird innerhalb der Markierung auf dem Tabellenblatt gesucht.

Ganze Arbeitsmappe durchsuchen

Das Beispiel ist auf eine Markierung innerhalb eines Tabellenblattes beschränkt.

Möchten Sie die Suche auf die ganze Arbeitsmappe ausdehnen, verwenden Sie das

Makro aus Listing 4.29:

Sub DatenSuchenInGanzerArbeitsmappe()Dim Zelle As RangeDim Blatt As Worksheet


86

Dim strText As String

strText = InputBox("Bitte geben Sie den Suchbegriff ein!")If strText = "" Then Exit SubFor Each Blatt In ActiveWorkbook.WorksheetsFor Each Zelle In Blatt.UsedRangeIf Zelle = strText ThenDebug.Print Blatt.Name & vbTab & Zelle.AddressEnd If

Next ZelleNext Blatt

End Sub

Listing 4.29 Ganze Arbeitsmappe durchsuchen

In der Variablendeklaration machen Sie bekannt, dass Sie mit dem Worksheet-Objekt

arbeiten möchten. Diesem geben Sie im Makro den Namen Blatt. Danach arbeiten

Sie alle Tabellen der Arbeitsmappe ab und durchlaufen in einer zweiten For Each

Next-Schleife alle Zellen des jeweils benutzten Bereichs (UsedRange) der Tabelle.

Entspricht der Zellenwert dem vorher eingegebenen Wert, dann geben Sie die Zellen-

adresse sowie den Namen der Tabelle im Direktfenster der Entwicklungsumgebung

aus.

Auf Formelsuche im Tabellenblatt

Besonders mühselig ist es, auf einem Tabellenblatt nach Formelzellen zu suchen.

Dazu können Sie durch das Drücken der Taste (F5) den Gehe zu-Dialog aufrufen,

dann auf die Schaltfläche Inhalte klicken und anschließend die Option Formeln

aktivieren. Nach der Bestätigung mit OK werden dann alle Zellen der Tabelle mar-

kiert, die Formeln oder auch Verknüpfungen enthalten. Das folgende Beispiel aus Lis-

ting 4.30 ermittelt zunächst die Anzahl der Formeln auf einem Tabellenblatt:

Sub FormelnZählen()Dim intz As IntegerDim Zelle As Range

On Error GoTo fehlerFor Each Zelle In ActiveSheet.Cells.SpecialCells(xlFormulas)intz = intz + 1

Next Zelle

MsgBox "Anzahl der Formeln in der Tabelle: " & intzExit Sub


87

4

fehler:MsgBox "Es konnten auf dem Tabellenblatt <" & _ActiveSheet.Name & "> keine Formeln gefunden werden!"

End Sub

Listing 4.30 Formeln auf Tabellenblatt zählen

Die Methode SpecialCells eignet sich hervorragend, um bestimmte Zellen wieder-

zufinden. Neben Formelzellen können Sie so auch Zellen mit Kommentaren

(xlCellTypeNotes) aufstöbern, leere Zellen (xlCellTypeBlanks) oder auch Zellen mit

Konstanten (xlCellTypeConstants) ermitteln.

Umlaute im Tabellenblatt umsetzen

Wenn Sie z. B. in Excel einen Text vorliegen haben, der Umlaute aufweist, können Sie

elegant die Schleife For Each Next dazu einsetzen, um diese Zeichen zu ersetzen. Mar-

kieren Sie vorher alle Zellen, in denen Sie Umlaute umsetzen möchten, und starten

Sie das Makro aus Listing 4.31.

Sub UmlauteErsetzen()Dim Zelle As Range

For Each Zelle In SelectionWith Selection.Replace What:="ä", _Replacement:="ae", LookAt:=xlPartReplace What:="ö", _Replacement:="oe", LookAt:=xlPartReplace What:="ß", _Replacement:="ss", LookAt:=xlPartReplace What:="ü", _Replacement:="ue", LookAt:=xlPart

End WithNext Zelle

End Sub

Listing 4.31 Umlaute in Tabellenblatt austauschen

Wochenenden farblich hervorheben

Im nächsten Beispiel sollen im Bereich A1:K1, wo jeweils Datumsangaben stehen, alle

Samstage bzw. Sonntage farbig hervorgehoben werden. Dazu setzen Sie die Funktion


88

Weekday ein. Diese Funktion meldet für den Sonntag den Index 1 und für den Samstag

den Index 7 – ein wenig ungewöhnlich zwar, aber es klappt hervorragend.

Sub WochenendeFormatieren()Dim zelle As Range

For Each Zelle In ActiveSheet.Range("A1:K1")If WeekDay(Zelle, 2) > 5 ThenZelle.Interior.ColorIndex = 15

ElseZelle.Interior.ColorIndex = xlColorIndexNone

End IfNext Zelle

End Sub

Listing 4.32 Wochenenden farbig hervorheben

Im obigen Beispiel haben Sie konkret schon einen ganz bestimmten Bereich A1:K1

vorgegeben, für den die Wochenendprüfung durchgeführt werden soll. Als Zellen-

hintergrund verwenden Sie einen leichten Grauton. Sicherheitshalber kann auch

hier dafür gesorgt werden, dass normale Wochentage keinen Hintergrund erhalten,

indem Sie der Eigenschaft ColorIndex die Konstante xlColorIndexNone zuweisen.

Nullen auffüllen

Im nächsten Praxisbeispiel soll ein Bereich mit Nullen aufgefüllt werden. Stellen Sie

sich vor, Sie haben Ihre monatlichen Ausgaben in einer Tabelle erfasst und möchten

nun die Zellen in einem vorher festgelegten Bereich durchsuchen. Alle Zellen, die in

diesem Bereich keinen Eintrag aufweisen, sollen mit der Zahl 0 gefüllt werden.

Legen Sie mit der Anweisung Set zunächst einen Bereich fest, für den Sie diese Aktion

durchführen möchten.

Abbildung 4.1 Sie können die Wochenenden farbig hervorheben.


89

4

Sub NullenAuffüllen()Dim Zelle As RangeDim Bereich As Range

Set Bereich = Sheets("Nullen").Range("B2:H10")For Each Zelle In BereichIf Zelle.Value = "" ThenZelle.Value = 0

End IfNext Zelle

End Sub

Listing 4.33 Bereich mit Nullen initialisieren

Abbildung 4.2 Die Ausgangstabelle

Abbildung 4.3 Die leeren Zellen wurden mit dem Wert 0 aufgefüllt.


90

Extremwerte ermitteln und kenntlich machen

Die Ermittlung von Spitzen- bzw. Niedrigstwerten ist in Excel leicht möglich. In VBA

setzen Sie für diese Aufgabe die Funktionen Max bzw. Min ein. So wird im folgenden

Beispiel aus Listing 4.34 innerhalb eines vorher definierten Bereichs sowohl der

Maximalwert als auch der niedrigste Wert ermittelt und farbig gekennzeichnet.

Sub AusreisserInBereichErmitteln()Dim Zelle As RangeDim Bereich As Range

Set Bereich = Sheets("Ausreißer").Range("A3:F14")

For Each Zelle In BereichIf Zelle = Application.Min(Bereich) ThenWith Zelle.Font.ColorIndex = 10 'grün.Font.Bold = TrueEnd With

End IfIf Zelle = Application.Max(Bereich) ThenWith Zelle.Font.ColorIndex = 3 'rot.Font.Bold = True

End With

Abbildung 4.4 Die höchsten und die niedrigsten Kosten des letzten Halbjahrs

(Zur besseren Übersicht wurden beide Zellen in der Abbildung markiert.)


91

4

End IfNext Zelle

End Sub

Listing 4.34 Extremwerte farbig hervorheben

Durchschnittliche Kosten ermitteln

Wenn Sie in der Tabelle aus der vorherigen Aufgabe die durchschnittlichen Kosten

ermitteln möchten, müssen Sie zuerst feststellen, wie viele Zellen in der Markierung

enthalten sind. Dies gelingt Ihnen über die Methode Count. Danach summieren Sie

zunächst einmal alle Werte aus den markierten Zellen und speichern diese in der

Variablen Avg. Wählen Sie für diese Variable nicht den Datentyp Integer, der bei die-

sen großen Zahlen nicht mehr ausreicht, sondern definieren Sie dazu eine Variable

vom Typ Long. Um den Durchschnittswert zu bekommen, dividieren Sie die Gesamt-

summe aus der Variablen Avg durch die Anzahl der markierten Zellen, die Sie in der

Variablen i zwischengespeichert haben. Bringen Sie die Zahl am Ende mit der Funk-

tion Format noch in das gewünschte Format.

Sub DurchschnittskostenErmitteln()Dim zelle As RangeDim Avg As LongDim i As Integer

Sheets("Ausreißer").ActivateAvg = 0'Anzahl markierter Zellen ermitteln

i = Selection.Cells.Count

For Each zelle In SelectionAvg = Avg + zelle.Value

Next zelle

Avg = Avg / iMsgBox "Durchschnittliche Kosten: " _& Format(Avg, "#,##0 €")

End Sub

Listing 4.35 Durchschnittliche Kosten ermitteln


92

Zeilen einblenden

Erinnern Sie sich an ein vorangegangenes Beispiel, in dem Sie Zeilen ausgeblendet

haben? Blenden Sie diese jetzt wieder ein, indem Sie folgendes Makro aus Listing 4.36

verwenden:

Sub AusgeblendeteZeilenEinblenden()Dim Zeile As Range

For Each Zeile In Sheets("Verzweigungen").UsedRange.RowsZeile.Hidden = False

Next Zeile

End Sub

Listing 4.36 Zeilen einblenden

Das Makro überprüft, wie viele Zeilen auf dem aktuellen Tabellenblatt mit Daten

belegt sind. In einer Schleife werden dann alle Zeilen durchlaufen und die ausgeblen-

deten Zeilen wieder eingeblendet.

Abbildung 4.5 Die durchschnittlichen Kosten im Juni betragen 452 Euro.


93

4

Tipp

Wenn Sie genau hinsehen, werden Sie merken, dass Ihr Bildschirm bei diesem

Makro mehr oder weniger stark flackert. Verantwortlich dafür ist die Bildschirm-

aktualisierung, die Sie aber mit der Anweisung

Application.Screenupdating = False

ausschalten können. Ihren Augen zuliebe sollten Sie sich angewöhnen, diese Anwei-

sung zu Beginn jedes Makros einzusetzen. Am Ende eines jeden Makros können Sie

die Bildschirmaktualisierung mit der Anweisung

Application.Screenupdating = True

wieder einschalten. Bei längeren Makros empfiehlt es sich, hin und wieder die Bild-

schirmaktualisierung einzuschalten, damit der Anwender nicht fälschlicherweise

meint, Ihr Makro sei abgestürzt.

Bereichsnamen ermitteln

Sicher wissen Sie, dass Sie in Excel mit benannten Bereichen arbeiten können. For-

meln werden so sprechender und insgesamt leichter verständlich. Wie aber ermit-

teln Sie die Namen und deren Zellenadressen in einer Arbeitsmappe?

Sub NamenInArbeitsmappeAusgeben()Dim BerName As Name

For Each BerName In ActiveWorkbook.NamesMsgBox "Name: " & BerName.Name & vbLf _& "Adresse: " & BerName.RefersToLocal

Next BerName

End Sub

Listing 4.37 Alle verwendeten Namen der Arbeitsmappe ausgeben

Geben Sie zuerst an, dass Sie mit dem Objekt Name arbeiten möchten. Danach suchen

Sie die Arbeitsmappe nach verwendeten Namen ab und geben diese in einem Mel-

dungsfenster aus. Interessant sind der Name selbst sowie seine Adresse, also die Zell-

position innerhalb der Arbeitsmappe.

Einheitliche Kopf- und Fußzeilen

Im nächsten Beispiel werden Sie für eine einheitliche Gestaltung der Kopf- und Fuß-

zeilen sorgen. Dabei arbeiten Sie alle Tabellen einer Arbeitsmappe ab, rufen die Sei-

tenansicht auf und stellen die gewünschten Kopf- und Fußzeilen ein. Folgende

Informationen sollen enthalten sein:


94

� Kopfzeile links: der Firmenname

� Kopfzeile Mitte: der Name des Tabellenblatts

� Kopfzeile rechts: das aktuelle Datum

� Fußzeile links: der komplette Speicherpfad inklusive Dateiname

� Fußzeile Mitte: bleibt leer

� Fußzeile rechts: Seitennummerierung

Sub KopfUndFuß()Dim Tabelle As Worksheet

Application.ScreenUpdating = FalseFor Each Tabelle In ActiveWorkbook.WorksheetsWith Tabelle.PageSetup.LeftHeader = "Firmenname".CenterHeader = "Tabellenname &A".RightHeader = "&D".LeftFooter = "Pfad : " & ActiveWorkbook.FullName.CenterFooter = "".RightFooter = "Seite &P von &N"

End WithNext Tabelle

Set Tabelle = NothingApplication.ScreenUpdating = True

End Sub

Listing 4.38 Alle Tabellen mit einer einheitlichen Kopf- und Fußzeile ausstatten

Definieren Sie im ersten Schritt eine Objektvariable vom Typ Worksheet. Schalten Sie

danach die Bildschirmaktualisierung aus, indem Sie die Anweisung Application.

ScreenUpdating = False einsetzen. Setzen Sie danach eine Schleife auf, die alle Tabel-

len der aktiven Arbeitsmappe durchläuft und über das Objekt PageSetup einheitliche

Kopf- und Fußzeilen einstellt. Den Namen der jeweiligen Tabelle bekommen Sie über

die Eigenschaft Name. Die Seitennummerierung für die Kopf- und Fußzeile kann über

sogenannte Steuerzeichen (z. B. &P oder &N) eingefügt werden. Den kompletten

Namen der gespeicherten Arbeitsmappe können Sie über die Eigenschaft FullName

abfragen. Heben Sie am Ende des Makros den Objektverweis wieder auf, um Speicher

freizugeben, und schalten Sie die Bildschirmaktualisierung wieder ein.


95

4

4.3.3 Die Schleife »Do Until Loop«

Die Do Until Loop-Schleife wiederholt einen Block mit Anweisungen, bis eine Bedin-

gung den Wert True erhält. Die Bedingung wird jeweils am Ende der Schleife geprüft.

Als Abbruchbedingung können Sie alles Mögliche abfragen; so können Sie z. B. eine

Abbruchbedingung festlegen, wenn ein bestimmter Wert erreicht ist oder eine Zelle

einen bestimmten Text aufweist.

Die Syntax dieser Schleife sieht wie folgt aus:

Do [{Until} Bedingung][Anweisungen][Exit Do][Anweisungen]Loop

Die Bedingung stellt einen numerischen Ausdruck oder Zeichenfolgenausdruck dar,

der entweder True oder False ergibt. Liefert die Bedingung den Wert 0, so wird die

Bedingung als False interpretiert. Hinter den Anweisungen verbergen sich eine oder

mehrere Anweisungen, die so lange wiederholt werden, bis die Bedingung durch True

erfüllt ist.

Innerhalb einer Do Until Loop-Anweisung kann eine beliebige Anzahl von Exit Do-

Anweisungen an beliebiger Stelle als Alternative zum Verlassen einer Do Loop-

Anweisung verwendet werden. Exit Do wird oft in Zusammenhang mit der Auswer-

tung einer Bedingung (zum Beispiel If Then) eingesetzt und hat zur Folge, dass die

Ausführung mit der ersten Anweisung im Anschluss an Loop fortgesetzt wird.

Üben Sie den Einsatz dieser Schleife an ein paar ausgesuchten Beispielen.

In der folgenden Aufgabe soll eine Liste in Excel verwaltet werden. Anhand eines Zel-

lenkennzeichens sollen Aktionen abgeleitet werden können.

Funktionsumfang

Die Aktionen sind:

� Zeilen ausblenden (Zellenkennzeichen = H)

� Zeilen einfügen (Zellenkennzeichen = I)

� Zeilen kopieren (Zellenkennzeichen = C)

� Zeilen löschen (Zellenkennzeichen = D)

� Zeilen mit Kopien löschen (Zelleninhalt = Kopie)

� Keine Aktion (Zellenkennzeichen = O)


96

Zeilen ausblenden

Um Zeilen auszublenden, wird das Zellenkennzeichen H verwendet. Die Do Until

Loop-Schleife arbeitet in Spalte A so lange Zeilen ab, bis sie auf die Abbruchbedingung

stößt. Die Abbruchbedingung ist gegeben, wenn die erste leere Zelle in Spalte A

erreicht wird.

Sub BestimmteZeilenAusblenden()

Sheets("Until").ActivateRange("A1").SelectDo Until IsEmpty(ActiveCell.Value)If ActiveCell.Value = "H" ThenSelection.EntireRow.Hidden = True

End IfActiveCell.Offset(1, 0).SelectLoop

End Sub

Listing 4.39 Zeilen ausblenden bei Bedingung

Abbildung 4.6 Die Ausgangstabelle


97

4

Indem Sie die Eigenschaft Hidden auf den Wert True setzen, wird die aktuelle Zeile aus-

geblendet. Danach wird der Mauszeiger mithilfe der Offset-Eigenschaft auf die

nächste Zeile gesetzt.

Zeilen einfügen

Für das Einfügen von Zeilen wurde das Zellenkennzeichen I vereinbart.

Sub BestimmteZeilenEinfügen()

Sheets("Until").ActivateRange("A1").Select

Do Until IsEmpty(ActiveCell.Value)If ActiveCell.Value = "I" ThenSelection.EntireRow.InsertActiveCell.Offset(2, 0).Select

ElseActiveCell.Offset(1, 0).Select

End IfLoop

End Sub

Listing 4.40 Zeilen einfügen bei Bedingung

Verwenden Sie die Methode Insert, um Zeilen einzufügen, und setzen Sie danach

den Mauszeiger zwei Zellen nach unten.

Zeilen löschen

Das Zellenkennzeichen für den Vorgang »Zeilen löschen« lautet D.

Sub BestimmteZeilenLöschen()

Sheets("Until").ActivateRange("A1").SelectDo Until ActiveCell.Value = ""If ActiveCell.Value = "D" ThenSelection.EntireRow.Delete


End If


98

Loop

End Sub

Listing 4.41 Zeilen löschen bei Bedingung

Die Methode Delete löscht im obigen Makro jeweils eine gesamte Zeile. Beim Lösch-

vorgang muss der Mauszeiger nicht per Befehl verschoben werden, er setzt den

Mauszeiger automatisch in die nächste Zelle.

Zeilen kopieren

Beim Zellenkennzeichen C soll die aktuelle Zeile kopiert und mit dem Zusatz Kopie

bei der Nr. versehen werden.

Sub BestimmteZeilenkopieren()

Sheets("Until").ActivateRange("A1").Select

Do Until IsEmpty(ActiveCell.Value)If ActiveCell.Value = "C" ThenSelection.EntireRow.CopySelection.EntireRow.InsertActiveCell.Offset(0, 5).Value = _ActiveCell.Offset(0, 5).Value & " Kopie"ActiveCell.Offset(2, 0).SelectApplication.CutCopyMode = False


End IfLoop

End Sub

Listing 4.42 Zeilen kopieren bei Bedingung

Setzen Sie die Methoden Copy und Insert ein, um eine Zeile zu kopieren bzw. einzu-

fügen. Über die Eigenschaft Offset greifen Sie auf die Spalte F zu und ergänzen in der

aktiven Zelle den Text Kopie.


99

4

Kopien entfernen

Wenn Sie die Kopien wieder entfernen möchten, müssen Sie die Spalte F durchlau-

fen. Immer wenn dort in einer Zelle das Wort Kopie auftaucht, muss die ganze Zeile

entfernt werden.

Sub KopienEntfernen()

Sheets("Until").ActivateRange("F1").Select

Do Until IsEmpty(ActiveCell.Value)If Right(ActiveCell.Value, 5) = "Kopie" ThenSelection.EntireRow.Delete


End ifLoop

End Sub

Listing 4.43 Doppelte Zeilen entfernen

Mit der Funktion Right prüfen Sie, ob der Text Kopie in der Zelle vorhanden ist. Dabei

legen Sie im ersten Argument fest, welche Zelle untersucht werden soll (aktive Zelle),

Abbildung 4.7 Zeilen kopieren und kennzeichnen


100

und im zweiten Parameter bestimmen Sie, wie viele Zeichen der Zelle von rechts

gesehen mit einbezogen werden sollen. Entspricht der Zellenteil dem Text Kopie,

dann wird die Methode Delete angewendet, um die ganze Zeile zu löschen.

Tipp

Neben der Funktion Right, die jeweils ein oder mehrere Zeichen von rechts über-

prüft, gibt es selbstverständlich auch eine Funktion Left, die die Überprüfung eines

Textes von der linken Seite beginnt. Die Argumente entsprechen dabei den Argu-

menten der Right-Funktion.

Info

Neben diesen beiden Funktionen gibt es die Funktion Mid, die einen Teil eines Tex-

tes prüfen kann. Dabei sind jedoch drei Argumente notwendig. Das erste Argument

bestimmt den Text oder die Zelle, der bzw. die untersucht werden soll. Das zweite

Argument bestimmt, ab welcher Position gesucht werden soll. Im letzten Argument

geben Sie an, für wie viele Zeichen diese Funktion bestimmt ist.

So meldet die Funktion Mid("Excel 2013 VBA", 7, 4) den Wert 2013.

Datumstest durchführen

Stellen Sie sich ein Tabellenblatt vor, bei dem in Zelle A1 das heutige Datum steht. In

der zweiten Zeile steht eine Datumsreihe, die in Zelle A2 beginnt und in Zelle I2

endet. Wie müsste jetzt ein Makro lauten, das so lange in Zeile 2 nach rechts läuft, bis

das aktuelle Datum erreicht ist?

Sub AufHeutigemDatumStoppen()

Sheets("Until2").ActivateRange("A2").SelectDo Until Date = ActiveCell.ValueActiveCell.Offset(0, 1).Select

Loop

End Sub

Listing 4.44 Datumstest durchführen (Variante 1)

Setzen Sie die Do Until Loop-Schleife beginnend in Zelle A2 auf. Versetzen Sie mit der

Offset-Eigenschaft so lange den Mauszeiger nach rechts, bis der Wert der Funktion

Date mit einer Zelle übereinstimmt.


101

4

Gleich an dieser Stelle gibt es eine alternative Möglichkeit, diese Aufgabe zu lösen.

Dabei kommt die folgende Lösung ohne den Select-Befehl innerhalb der Schleife

aus.

Sub AufHeutigemDatumStoppen2()

Sheets("Until2").Activatei = 1Do Until Date = Cells(2, i).Valuei = i + 1

LoopCells(2, i).Select

End Sub

Listing 4.45 Datumstest durchführen (Variante 2)

Mithilfe der Cells-Auflistung kann jede Zelle der Tabelle angesprochen werden.

Dabei liefert das erste Argument die jeweilige Zeile, und das zweite Argument gibt

Auskunft über die Spalte. Der Schnittpunkt aus beiden Angaben liefert die Koordi-

nate der angesprochenen Zelle. Da in der zweiten Variante auf den Select-Befehl ver-

zichtet wurde, muss stattdessen eine Variable in der Schleife hochgezählt werden.

Beide Makros liefern dasselbe Ergebnis, wobei das zweite Makro einen kleinen

Geschwindigkeitsvorteil gegenüber der Variante 1 hat, da auf die Methode Select

innerhalb der Schleife verzichtet werden kann.

Abbildung 4.8 Genaues Positionieren von Zellen


102

Listen sortieren und doppelte Sätze löschen

Gegeben sei eine Liste, die aus einer Spalte (Spalte A) besteht. In dieser unsortierten

Liste sind Einträge mehrfach vorhanden. Außerdem liegt die Liste in unsortiertem

Zustand vor. Ihre Aufgabe besteht nun darin, alle doppelten Sätze zu ermitteln.

Sortieren Sie im ersten Schritt die Liste, und löschen Sie danach die doppelten Sätze.

Sub DoppelteSätzeEliminieren()Dim i As Long

Sheets("Until3").Activate

'zuerst sortierenColumns("A:A").SelectSelection.Sort Key1:=Range("A1"), Order1:=xlAscending, _Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _Orientation:=xlTopToBottomi = 1'jetzt doppelte Sätze rausschmeißenDo Until IsEmpty(Cells(i, 1).Value)If Cells(i, 1).Value = Cells(i + 1, 1).Value ThenCells(i, 1).EntireRow.Delete

Elsei = i + 1

End IfLoop

End Sub

Listing 4.46 Doppelte Sätze löschen

Markieren Sie die komplette Spalte A, und wenden Sie die Methode Sort an. Schauen

Sie sich die Syntax der Methode Sort einmal etwas genauer an:

� Die Syntax:

Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom,MatchCase, Orientation, SortMethod)

� Die Argumente der Methode Sort:

– Die ersten beiden Argumente müssen Sie immer gemeinsam betrachten. Das

erste Argument, Key, bestimmt das Sortierfeld, das zweite Argument die Sortier-

reihenfolge. Möglich dabei ist, entweder aufsteigend (xlAscending) oder abstei-

gend (xlDescending) zu sortieren. Insgesamt können Sie drei verschiedene

Sortierfelder bestimmen.


103

4

– Das Argument Type ist nur bei Pivot-Tabellenberichten interessant und wird

hier nicht näher behandelt.

– Das Argument Header legt fest, ob die erste Zeile Überschriften enthält oder

nicht. Dabei weisen Sie die Konstante xlGuess zu, wenn Sie die Entscheidung

darüber Excel selbst überlassen möchten. Setzen Sie die Konstante xlYes, wenn

der Sortierbereich eine Überschriftenzeile enthält, die natürlich nicht mitsor-

tiert werden darf. Wenn Sie die Konstante xlNo zuweisen, enthält der Sortier-

bereich wie im obigen Beispiel keine Überschriften.

– Das Argument OrderCustom wird bei benutzerdefinierten Sortierreihenfolgen

verwendet und ist in diesem Zusammenhang eher uninteressant.

– Das Argument MatchCase nimmt den Wert True an, wenn beim Sortieren Groß-

und Kleinschreibung berücksichtigt werden soll. Setzen Sie dieses Argument

auf den Wert False, wenn Groß- und Kleinschreibung nicht berücksichtigt wer-

den soll.

– Beim Argument Orientation wird die Sortierweise festgelegt. Hat das Argu-

ment den Wert xlSortRows, so wird von oben nach unten, also zeilenweise sor-

tiert. Wird das Argument auf xlSortColumns gesetzt, so wird von links nach

rechts, also spaltenweise sortiert.

– Das letzte Sortierkriterium legt den Sortieralgorithmus fest.

Anschließend werden die doppelten Sätze aus der Liste entfernt. Auch bei dieser Auf-

gabe kann auf den Befehl Select verzichtet werden, indem man mit der Auflistung

Cells arbeitet. Sollte ein Satz doppelt vorkommen, dann stehen beide Sätze nach

dem Sortiervorgang genau untereinander. Von daher kann man nun die aktuelle

Zeile (i) und die darunter liegende (i+1) miteinander vergleichen. Bei doppeltem Vor-

kommen wird die aktuelle Zeile über die Methode Delete gelöscht. Über die Eigen-

schaft EntireRow wird dabei auf die komplette Zeile zurückgegriffen.

Zeilen auf ein anderes Tabellenblatt übertragen

Oft kommt es vor, dass Sie aus einer Liste bestimmte Zeilen filtern und auf ein ande-

res Tabellenblatt kopieren müssen. Dazu legen Sie zuerst fest, welches die Ausgangs-

tabelle und welches die Zieltabelle sein soll. Die Namen der Tabellen definieren Sie

gleich zu Beginn des Makros als Blatt1 und Blatt2.

Das Ziel

Was soll konkret gemacht werden?

� Alle Zeilen im Tabellenblatt Until, die als Kürzel den Buchstaben C haben, sollen

auf das Tabellenblatt Ziel übertragen werden.

� Ermitteln Sie die Anzahl aller Tabellensätze der Liste.


104

� Setzen Sie die ermittelte Anzahl als Abbruchbedingung für Ihre Do Until Loop-

Schleife ein.

� Ermitteln Sie, wie viele Sätze übertragen wurden.

� Geben Sie eine Meldung am Ende des Makros aus, die die Anzahl der übertragenen

Sätze enthält.

Das Makro für diese Aufgabe sehen Sie in Listing 4.47:

Sub SätzeAufAnderesTabellenblattÜbertragen1()Const Blatt1 = "Until"Const Blatt2 = "Ziel"Dim i As IntegerDim iAnz As Integer

Application.ScreenUpdating = FalseSheets(Blatt1).ActivateRange("A1").SelectiAnz = 0i = 0

Do Until i = ActiveSheet.UsedRange.Rows.CountIf ActiveCell.Value = "C" ThenSelection.EntireRow.Copy

Abbildung 4.9 Die Ausgangstabelle vor der Übertragungsaktion


105

4

Sheets(Blatt2).ActivateActiveSheet.PasteActiveCell.Offset(1, 0).SelectSheets(Blatt1).SelectActiveCell.Offset(1, 0).SelectiAnz = iAnz + 1


End Ifi = i + 1LoopApplication.CutCopyMode = FalseApplication.ScreenUpdating = TrueMsgBox "Es wurden " & iAnz & " Sätze übertragen"

End Sub

Listing 4.47 Datensätze auf anderes Tabellenblatt übertragen (Variante 1)

Schalten Sie zu Beginn des Makros die Bildschirmaktualisierung aus. Gerade bei häu-

figem Blattwechsel flimmert das Bild sonst zu stark. Schonen Sie Ihre Augen und

Ihren Bildschirm, und setzen Sie die Eigenschaft Screenupdating auf den Wert False.

Im nächsten Schritt setzen Sie Ihre Zählvariablen auf den Wert 0, um auf Nummer

sicher zu gehen, dass diese beiden Variablen richtig initialisiert sind. Ermitteln Sie

nun mit der Methode Count die verwendeten Zeilen auf Ihrem Tabellenblatt. Das

Ergebnis dieser Abfrage verwenden Sie gleich als Abbruchbedingung für Ihre

Schleife. Setzen Sie die If-Anweisung ein, um zu ermitteln, ob die aktive Zelle das

richtige Kennzeichen C aufweist. Wenn nicht, können Sie mit der Eigenschaft Offset

gleich eine Zelle weiter nach unten gehen.

Stimmt das Kennzeichen überein, kopieren Sie mithilfe der Methode Copy die ganze

Zeile, wechseln auf das Tabellenblatt Ziel und fügen den Inhalt aus der Zwischen-

ablage mit der Methode Paste ein. Jetzt positionieren Sie den Zellenzeiger auf die

nächste freie Zelle der Spalte und addieren den Wert 1 zu der Variablen iAnz, die die

übertragenen Sätze zählt. Wechseln Sie im Anschluss auf Ihr Ausgangstabellenblatt

Blatt1, positionieren Sie den Zellenzeiger eine Zelle weiter nach unten, und addie-

ren Sie auch hier zur Zählvariable i den Wert 1. Sind alle Sätze untersucht worden,

wird der Ausschneide- bzw. Kopiermodus deaktiviert und der Laufrahmen entfernt,

indem Sie die Eigenschaft CutCopyMode auf den Wert False setzen. Schalten Sie nun

die Bildschirmaktualisierung wieder ein, und geben Sie mit der Funktion Msgbox die

Anzahl der übertragenen Sätze aus.


106

Bei der zweiten Variante kann die Aufgabe noch schneller erledigt werden, da auf die

Tabellenwechsel und die Zellenpositionierung verzichtet wurde. Außerdem kann bei

dieser Lösung auf die Methode Copy sowie die Methode Paste verzichtet werden.

Sub SätzeAufAnderesTabellenblattÜbertragen2()Dim Tab1 As WorksheetDim Tab2 As WorksheetDim L As LongDim i As Long

Set Tab1 = ActiveWorkbook.Worksheets("Until")Set Tab2 = ActiveWorkbook.Worksheets("Ziel")

For L = 1 To Tab1.UsedRange.Rows.CountIf Tab1.Cells(L, 1).Value = "C" Theni = 1Do Until Tab2.Cells(i, 1) = ""i = i + 1

LoopTab2.Cells(i, 1).Value = Tab1.Cells(L, 1).ValueTab2.Cells(i, 2).Value = Tab1.Cells(L, 2).ValueTab2.Cells(i, 3).Value = Tab1.Cells(L, 3).ValueTab2.Cells(i, 4).Value = Tab1.Cells(L, 4).Value

End IfNext LMsgBox "Es wurden " & i & " Sätze übertragen"

End Sub

Listing 4.48 Datensätze auf anderes Tabellenblatt übertragen (Variante 2)

Abbildung 4.10 Alle Daten wurden übertragen.


107

4

Geben Sie zu Beginn des Makros bekannt, welches das Ziel und welches das Aus-

gangsblatt ist. Definieren Sie dazu zwei Objektvariablen vom Typ Worksheet. Die zu

übertragenden Informationen werden dabei von einem Blatt direkt in das Zielblatt

übertragen, indem die Auflistung Cells mit den jeweiligen Variablen für die Zeilen

bestückt wird.

4.3.4 Die Schleife »Do While Loop«

Die Do While Loop-Schleife wiederholt einen Block mit Anweisungen, solange eine

Bedingung den Wert True erhält. Die Prüfung der angegebenen Bedingung erfolgt

immer zu Beginn der Schleife. Als Abbruchbedingung können Sie alles Mögliche

abfragen; beispielsweise könnten Sie eine Schleife so oft wiederholen lassen, so lange

die Zellenformatierung der Zellen sich nicht ändert.

Die Syntax dieser Schleife sieht wie folgt aus:

Do [{While} Bedingung][Anweisungen][Exit Do][Anweisungen]Loop

Die Bedingung stellt einen numerischen Ausdruck oder Zeichenfolgenausdruck dar,

der entweder True oder False ergibt. Liefert die Bedingung den Wert 0, so wird die

Bedingung als False interpretiert. Hinter den Anweisungen verbergen sich eine oder

mehrere Anweisungen, die wiederholt werden, so lange die Bedingung True erfüllt

ist.

Innerhalb einer Do While Loop-Anweisung kann eine beliebige Anzahl von Exit Do-

Anweisungen an beliebiger Stelle als Alternative zum Verlassen einer Do While Loop-

Anweisung verwendet werden. Exit Do wird oft in Zusammenhang mit der Auswer-

tung einer Bedingung (zum Beispiel If Then) eingesetzt und hat zur Folge, dass die

Ausführung mit der ersten Anweisung im Anschluss an Loop fortgesetzt wird.

Nur bestimmte Zellen addieren mit »Do While«

Gegeben sei eine Liste, aus der Sie alle Werte addieren müssen, die über dem Wert

100 liegen. Das Ergebnis soll unterhalb der Liste im Schriftschnitt Fett und mit einfa-

cher Unterstreichung ausgegeben werden.

Erfassen Sie nun folgendes Makro:

Sub AlleGroßenZahlenAddieren()Dim summe As LongDim i As Long


108

Const Grenzwert = 100

Sheets("While").Activatesumme = 0i = 1

Do While Cells(i, 1).Value <> ""If Cells(i, 1).Value > Grenzwert _Then summe = summe + Cells(i, 1).Valuei = i + 1

Loopi = i + 2Cells(i, 1).Value = summeWith Cells(i, 1).Font.Bold = True.Underline = True

End With

End Sub

Listing 4.49 Bedingte Zahlenaddition durchführen

Abbildung 4.11 Das Endergebnis: die Summe der Liste mit Zahlen > 100


109

4

Bestimmen Sie in der Variablen Grenzwert selbst, wie niedrig Sie die Schwelle legen

möchten. Deklarieren Sie die Variable summe als Datentyp Long, um ganz sicherzuge-

hen, dass der Datentyp auch für die Summierung ausreicht. Prüfen Sie mit der

Anweisung If, ob die Zellen über Ihrem Grenzwert liegen. Nur dann addieren Sie den

aktuellen Zellenwert, den Sie jeweils über die Auflistung Cells ansprechen, zur Vari-

able summe. Sobald die Schleifenbedingung nicht mehr erfüllt ist, wird über die Zellen-

variable i zwei Zellen nach unten verwiesen und der Inhalt der Variablen summe in die

Zelle geschrieben. Danach erfolgt die Formatierung der Zelle über die Eigenschaften

Bold und Underline. Beachten Sie die Anweisung With, die auch hier ein wenig für

Übersichtlichkeit und weniger Schreibarbeit sorgt.

Text in Spalten aufteilen

Ein häufiger Fall in der Praxis ist folgender: Sie bekommen eine Liste und möchten

diese weiterverarbeiten. In der Liste befinden sich in manchen Spalten jeweils zwei

Informationen, die Sie lieber getrennt in einzelnen Spalten haben möchten. Denken

Sie beispielsweise an eine Kundenliste, in der der Vor- und der Nachname in einer

einzigen Spalte stehen. Genauso verhält es sich mit der Postleitzahl und dem Wohn-

ort des Kunden. Auch hier wird oft nur eine Spalte verwendet. Die Aufgabe besteht

jetzt darin, eine solche Liste umzustellen. Dazu müssen Sie zum einen neue Spalten

einfügen und zum anderen die Einträge voneinander trennen.

Das Makro für diese Aufgabe können Sie in Listing 4.50 sehen:

Sub TextInSpaltenAufteilen()Dim Nachname As StringDim Vorname As String

Abbildung 4.12 Die Ausgangssituation


110

Dim PLZ As StringDim Ort As StringDim i As IntegerDim i2 As IntegerDim i3 As Integer

Application.ScreenUpdating = FalseSheets("While2").ActivateColumns("B:B").Insert Shift:=xlToRightRange("B1").Value = "Vorname"Columns("D:D").Insert Shift:=xlToRightRange("D1").Value = "PLZ"Range("A2").Select

Do While ActiveCell.Value <> ""i = InStr(ActiveCell.Value, ",")i2 = Len(ActiveCell.Value)Nachname = Left(ActiveCell.Value, i – 1)Vorname = Mid(ActiveCell.Value, i + 2, i2 – i)ActiveCell.Value = NachnameActiveCell.Offset(0, 1).Value = VornameActiveCell.Offset(0, 4).SelectPLZ = Left(ActiveCell.Value, 5)i3 = Len(ActiveCell.Value)Ort = Mid(ActiveCell.Value, 7, i3 – 6)ActiveCell.Offset(0, –1).Value = PLZActiveCell.Value = OrtActiveCell.Offset(1, –4).Select

LoopApplication.ScreenUpdating = True

End Sub

Listing 4.50 Text in Spalten aufteilen

Zu Beginn des Makros definieren Sie Variablen für die jeweiligen Informationen, die

Sie auf die einzelnen Spalten verteilen möchten. Zusätzlich brauchen Sie noch drei

Variablen vom Typ Integer. Fügen Sie danach zwei neue Spalten für den Vornamen

und die Postleitzahl ein, und vergeben Sie die Spaltenüberschriften. Schreiben Sie

jetzt eine Do While Loop-Schleife, die durchlaufen wird, solange Excel auf keine Leer-

zeile stößt.


111

4

Trennen von Zellen

Jetzt geht’s ans Auseinanderreißen der einzelnen Spalteninhalte. Beginnen Sie mit

der Namensspalte. Zuerst müssen Sie ermitteln, an welcher Position das Komma in

der Namenszelle steht, da Sie anhand dieses Trennzeichens den Nachnamen vom

Vornamen sauber trennen können. Für diese Aufgabe setzen Sie die Funktion InStr

ein. Die Funktion InStr ermittelt die Position des ersten Auftretens einer Zeichen-

folge (,) innerhalb einer anderen Zeichenfolge (aktiven Zelle). Speichern Sie die ermit-

telte Position des Kommas in der Variablen i.

Länge von Zellen ermitteln

Jetzt müssen Sie noch wissen, aus wie vielen Zeichen der Name überhaupt besteht.

Dazu verwenden Sie die Funktion Len. Bauen Sie sich nun den Nachnamen mit dem

Befehl

Nachname = Left(ActiveCell.Value, i – 1).

zusammen.

Die Funktion Left erwartet zwei Argumente: Das erste Argument steht für die Zelle,

auf die die Funktion angewendet werden soll. Das zweite Argument ist die Anzahl der

Zeichen, die von links übertragen werden sollen. Die Anzahl der Zeichen ermitteln

Sie, indem Sie von der Variablen i den Wert 1 abziehen, da Sie ja schließlich das

Komma nicht mit übertragen möchten. Separieren Sie jetzt den Vornamen:

Vorname = Mid(ActiveCell.Value, i + 2, i2 – i)

Position des Kommas ermitteln

Dazu setzen Sie die Funktion Mid ein. Die Funktion Mid gibt einen Wert zurück, der

eine bestimmte Anzahl von Zeichen aus einer Zeichenfolge enthält. Dazu müssen Sie

der Funktion drei Argumente mitgeben. Zum einen ist das wieder die Zelle, auf die

diese Funktion angewendet werden soll, dann ist es die Startposition, ab der Zeichen

übertragen werden sollen, und zu guter Letzt ist es das Argument, das die Anzahl der

Zeichen angibt, die übertragen werden sollen. Die Startposition beginnt in unserem

Beispiel zwei Stellen hinter dem Kommatrennzeichen. Die Anzahl der Zeichen, die

übertragen werden sollen, ergibt sich aus der Gesamtlänge der Zelle abzüglich der

Zeichen bis zum Komma.

Übertragen Sie nun die Variableninhalte in die dafür vorgesehenen Zellen. Danach

springen Sie zu der Zelle, die die Postleitzahl und den Ort enthält.

Da die Postleitzahl genau aus fünf Zeichen besteht, können Sie diese Information mit

der Funktion Left direkt abgreifen und in der Variablen PLZ speichern. Anders ver-

hält es sich beim Ort. Dazu setzen Sie die Funktion Mid ein:

Ort = Mid(ActiveCell.Value, 7, i3 – 6)


112

Position des Leerzeichens herausfinden

Das zweite Argument, die Startposition, ab der Zeichen übertragen werden sollen,

ergibt sich aus der Anzahl Zeichen der Postleitzahl und einem Leerzeichen. Die

Anzahl der zu übertragenden Zeichen ergibt sich aus der Gesamtlänge der Zelle

abzüglich des Wertes 6 (Postleitzahl inklusive eines Leerzeichens). Füllen Sie jetzt die

Zellen mit den Variablen für die Postleitzahl und den Ort. Danach positionieren Sie

den Mauszeiger in die nächste Zeile der Spalte A. Die Schleife beginnt dann wieder

von vorne und läuft so lange, bis alle Sätze verarbeitet worden sind.

Artikelnummern prüfen

In der nächsten Aufgabe sollen auf einem Tabellenblatt Artikelnummern geprüft

und falsche Artikelnummern ausgegeben werden. Dazu müssen Sie zuerst die Gül-

tigkeit Ihrer Artikelnummern festlegen:

Gültige Nummern sind:

� alle Artikelnummern, die genau sechs Zeichen lang sind und

� als letztes Zeichen den Buchstaben T enthalten.

Sub FalscheArtikelNummernErmitteln()Dim i As LongDim z As Integer

Sheets("ArtikelNummer").Activatei = 2z = 0

Do While Cells(i, 1).Value <> ""If Len(Cells(i, 1).Value) <> 6 _Or Right(Cells(i, 1).Value, 1) <> "T" Then

Abbildung 4.13 Das Endergebnis: Alle Informationen sind auf einzelne Spalten verteilt.


113

4

MsgBox "Falsche Artikelnummer in " & _Cells(i, 1).Addressz = z + 1

End Ifi = i + 1LoopMsgBox z & " falsche Artikelnummern gefunden!"End Sub

Listing 4.51 Artikelnummern prüfen

Haben Sie es bemerkt? In diesem Beispiel wurde die Funktion Right eingesetzt. Die

Funktion Right gibt einen Wert zurück, der eine bestimmte Anzahl von Zeichen von

der rechten Seite (dem Ende) einer Zeichenfolge enthält. Da Sie lediglich das letzte

Zeichen prüfen wollen, geben Sie als zweites Argument den Wert 1 ein. Die Gesamt-

länge der Zeichen einer Zeile können Sie über die Funktion Len ermitteln.

Abbildung 4.14 Prüfen der Artikelnummern

477

9

Kapitel 9

Diagramme und Pivot-Tabellen-berichte programmieren

In diesem Kapitel werden zwei Themen behandelt. Im ersten Teil ler-

nen Sie, wie Sie Diagramme erstellen, bearbeiten und löschen können.

Im zweiten Teil wird die Programmierung mit Pivot-Tabellenberichten

gezeigt.

Im ersten Teil werden unter anderem folgende Fragen beantwortet:

� Wie erstelle ich verschiedene Diagramme auf einem separaten Blatt?

� Wie erzeuge ich ein eingebettetes Diagramm?

� Wie kann ich Diagramme aus einer Arbeitsmappe entfernen?

� Wie kann ich Diagramme ansprechen?

� Wie kann ich Daten und ein Diagramm im Wechsel ein- und ausblenden?

� Wie kann ich Diagrammen Datenbeschriftungen hinzufügen?

� Wie kann ich Höhe und Breite eines Diagramms genau festlegen?

� Wie kann ich Diagramme selbst skalieren?

� Wie kann ich Diagramme als Grafik speichern?

� Wie kann ich Datenreihen formatieren?

� Wie erstelle ich ein dynamisches Diagramm?

Die Makros zur Programmierung von Diagrammen finden Sie auf der CD-ROM im

Verzeichnis KAP09 in der Datei DIAGRAMME.XLS.

Hinweis

Seit der Version von Excel 2000 ist es möglich, Diagramme direkt aus Pivot-Tabel-

lenberichten zu generieren.

Im Pivot-Teil des Kapitels finden Sie u. a. Antworten auf folgende Fragen:

� Wie kann ich einen Pivot-Tabellenbericht erstellen?

� Wie aktualisiere ich Pivot-Tabellenberichte?

� Wie kann ich Pivot-Tabellenberichte dynamisch erweitern?

9 Diagramme und Pivot-Tabellenberichte programmieren

478

� Wie kann ich Pivot-Tabellen sortieren?

� Welche Möglichkeiten bietet mir das AutoFormat bei Pivot-Tabellen?

� Wie kann ich das Seitenfeld einer Pivot-Tabelle bedienen?

� Wie kann ich einen Slicer einfügen? (ab Excel 2010)

Die Makros zur Programmierung von Pivot-Tabellenberichten finden Sie auf der CD-

ROM im Verzeichnis KAP09 in der Datei PIVOT.XLS bzw. PIVOT.XLSM.

9.1 Diagramme erstellen

Bei der Erstellung von Diagrammen haben Sie die Auswahl aus mehreren Diagramm-

typen. Für den richtigen Diagrammtyp ist die Eigenschaft ChartType verantwortlich.

In Tabelle 9.1 sehen Sie exemplarisch eine kleine Auswahl an möglichen Diagramm-

typen, die über eine xlChartType-Konstante identifiziert werden.

Diagrammtyp Konstante

Säulendiagramm (gruppiert) xlColumnClustered

Säulendiagramm (gestapelt) xlColumnStacked

Säulendiagramm 3D-Darstellung xl3DColumn

Balkendiagramm (gruppiert) xlBarClustered

Balkendiagramm (gestapelt) xlBarStacked

Liniendiagramm xlLine

Kreisdiagramm xlPie

Punktdiagramm xlXYScatter

Blasendiagramm xlBubble

Flächendiagramm xlArea

Ringdiagramm xlDoughnut

Netzdiagramm xlRadar

Oberflächendiagramm xlSurface

Kursdiagramm xlStockHLC

Tabelle 9.1 Die wichtigsten Diagrammtypen


479

9

Neben den in der Tabelle abgebildeten Diagrammtypen gibt es noch Zylinder-, Kegel-

und Pyramidendiagramme sowie zu jedem Diagramm zahlreiche Untertypen. Eine

komplette Liste der vorhandenen Diagramme in Excel bekommen Sie in der Online-

Hilfe unter dem Stichwort ChartType.

9.1.1 Kosten im Säulendiagramm darstellen

Das Säulendiagramm ist das Standarddiagramm in Excel. Sie können ein Säulendia-

gramm ganz schnell in Excel erstellen, indem Sie Ihren Datenbereich markieren und

einfach die Taste (F11) drücken. An diesem Standarddiagramm fehlen eigentlich nur

noch ein Titel und eventuell noch ein paar Datenbeschriftungen.

Erzeugen Sie ein Säulendiagramm, dessen Titel sich vom Tabellennamen ableitet.

Das Makro für diese Aufgabe lautet:

Sub DiagrammErstellen01()Dim Bereich As RangeDim s As String

Set Bereich = Range("A2:D6")s = ActiveSheet.NameCharts.AddWith ActiveChart.ChartType = xlColumnClustered.SetSourceData _Source:=Bereich, _PlotBy:=xlRows.HasTitle = True.ChartTitle.Text = s

End With

End Sub

Listing 9.1 Monatliche Kosten in einem Säulendiagramm darstellen

Im ersten Schritt legen Sie den Bereich fest, der die Datenbasis für das Diagramm ent-

hält. Dazu speichern Sie die Adresse des Bereichs in der Range-Variablen Bereich.

Danach ermitteln Sie den Namen des aktiven Tabellenblattes über die Eigenschaften

ActiveSheet und Name und speichern ihn in der Variablen s. Mit der Methode Add

erstellen Sie zunächst ein neues Diagrammblatt. Danach legen Sie das Aussehen des

Diagramms fest. Mit der Eigenschaft ChartType bestimmen Sie den Diagrammtyp.


480

Syntax und Argumente von »SetSourceData«

Über die Methode SetSourceData geben Sie an, woher Excel die Daten für das Dia-

gramm bekommt. Diese Methode benötigt zwei Argumente. Dem ersten Argument,

Source, übergeben Sie die Variable Bereich. Mit dem zweiten Argument, PlotBy, legen

Sie fest, wie die Daten angezeigt werden sollen. Wählen Sie entweder die Konstante

xlColumns oder xlRows.

Abbildung 9.1 Die Ausgangstabelle mit den monatlichen Kosten

Abbildung 9.2 Das Ergebnis: das erste Säulendiagramm per Makro


481

9

Die Eigenschaft HasTitle müssen Sie auf den Wert True setzen, damit eine Über-

schrift im Diagramm angezeigt werden kann. Die Überschrift des Diagramms erstel-

len Sie mithilfe der Eigenschaft ChartTitle und der Eigenschaft Text, die Sie aus der

Variablen s herausholen.

9.1.2 Das Zylinderdiagramm auf Knopfdruck

Eine Variante, die der Standarderstellung von Diagrammen entspricht – also Daten

zu markieren und danach die Taste (F11) zu drücken –, liefert das Makro aus Listing

9.2. Dieses Makro erstellt anhand der markierten Daten ein Zylinderdiagramm

(Abbildung 9.3) auf einem separaten Diagrammblatt.

Sub DiagrammblattEinfügen()

ActiveWorkbook.Names.Add _Name:="ST_Diagramm", RefersToR1C1:=SelectionCharts.AddActiveChart.ApplyCustomType _

Abbildung 9.3 Auch andere Diagrammtypen sind jederzeit einsetzbar.


482

ChartType:=xlCylinderColClustered

End Sub

Listing 9.2 Ein Diagramm anhand markierter Zellen einfügen

Mit dieser Lösung sind Sie nicht mehr auf den Standarddiagrammtyp fixiert, son-

dern können ganz flexibel selbst entscheiden, welchen Diagrammtyp Sie einsetzen

möchten.

9.1.3 Das Balkendiagramm aus Daten in der Umgebung

Bei der letzten Aufgabe haben Sie auf dem Tabellenblatt einen Datenbereich mar-

kiert und anschließend ein Makro gestartet, das Ihnen automatisch das Zylinder-

diagramm erstellt hat. Im Makro aus Listing 9.3 entfällt sogar noch der Schritt, bei

dem Sie Ihre Datenbasis über eine Markierung festlegen. Hier ermittelt Excel von

ganz allein die zur Darstellung benötigten Daten:

Sub DiagrammAusUmliegendemBereich()

Dim s As String

s = ActiveSheet.NameCharts.AddActiveChart.ChartType = xl3DBarClusteredActiveChart.SetSourceData _Source:=Sheets(s).Range("A2").CurrentRegion, _PlotBy:=xlColumns

End Sub

Listing 9.3 Diagramm aus umliegendem Datenbereich bilden

Das neue Diagrammblatt wird noch leer eingefügt. Über die Eigenschaft ChartType

können Sie ein Diagramm Ihrer Wahl (siehe Tabelle 9.1) festlegen. Die Datenbasis

für das Diagramm ermitteln Sie mit der Methode SetSourceData, der Sie im Ar-

gument Source das aktive Tabellenblatt, die Startzelle A2 sowie die Eigenschaft

CurrentRegion mitgeben. Die Eigenschaft CurrentRegion ermittelt automatisch für

Sie den umliegenden Bereich um Zelle A2, der von Leerzeilen bzw. Leerspalten um-

schlossen wird.


483

9

9.1.4 Tagesumsätze im Liniendiagramm anzeigen

Um Schwankungen in den Tagesumsätzen besser zu analysieren, können Sie ein Li-

niendiagramm generieren. Dazu erstellen Sie dieses Mal kein extra Diagrammblatt,

sondern ordnen das Diagramm neben den Umsatzdaten auf demselben Tabellen-

blatt an.

Das eingebettete Diagramm erstellen Sie mit dem Makro aus Listing 9.4:

Sub DiagrammErstellen02()Dim Dia As ChartObjectDim s As StringDim i As Integer

s = ActiveSheet.NameActiveSheet.ChartObjects.DeleteSet Dia = ActiveSheet.ChartObjects.Add _(175, 15, 300, 250)Dia.Name = "Umsätze April"i = ActiveSheet.Range("B1").End(xlDown).Row

Abbildung 9.4 Das Balkendiagramm mit nur einer einzigen Aktion erstellen


484

Range("A2:B" & i).CopyActiveSheet.ChartObjects("Umsätze April").ActivateActiveChart.SeriesCollection.Paste _Rowcol:=xlColumns, SeriesLabels:=False, _CategoryLabels:=True, Replace:=True, NewSeries:=TrueApplication.CutCopyMode = False

With ActiveChart.ChartType = xlLineMarkers.HasLegend = False.HasTitle = True.ChartTitle.Text = s

End WithRange("A1").Select

End Sub

Listing 9.4 Tägliche Umsätze in einem Liniendiagramm anzeigen

Im ersten Schritt ermitteln Sie den Tabellennamen der aktiven Tabelle. Dieser Tabel-

lenname soll später die Überschrift des Diagramms werden. Da Sie ein eingebettetes

Diagramm einfügen möchten, müssen Sie dafür Sorge tragen, dass bei mehrmaligem

Starten des Makros nicht mehrere Diagramme übereinander eingefügt werden.

Löschen Sie daher zu Beginn alle integrierten Diagramme auf dem Arbeitsblatt über

die Methode Delete. Danach fügen Sie mit der Methode Add ein noch leeres Chart-

Objekt in Ihre Tabelle ein. Dabei können Sie die genaue Position des ChartObjekts

exakt festlegen. Die vier Zahlenwerte entsprechen den Angaben zum linken Rand,

oberen Rand, Breite und Höhe. Die Angaben werden in Punkt ausgegeben und bezie-

hen sich relativ auf die obere linke Ecke der Zelle A1 oder auf die obere linke Ecke des

Diagramms.

Geben Sie als Nächstes dem eingebetteten Diagramm einen Namen. Als Nächstes

müssen Sie herausbekommen, wie viele Tagesumsätze im Diagramm angezeigt wer-

den sollen. Dazu ermitteln Sie die letzte belegte Zelle in Spalte B und speichern die

Zeilennummer in der Variablen i. Markieren Sie den Bereich, und kopieren Sie diesen

mit der Methode Copy. Markieren Sie danach das gerade eingefügte Diagramm. Um

die kopierten Daten einzufügen, verwenden Sie die Methode Paste der SeriesCollec-

tion-Auflistung.

� Die Syntax: Die Methode Paste verwendet mehrere Argumente und hat folgende

Syntax:

Paste(Rowcol, SeriesLabels, CategoryLabels, Replace, NewSeries)


485

9

� Die Argumente der Methode Paste:

– Im ersten Argument, RowCol, geben Sie Auskunft darüber, ob sich die Daten in

Zeilen oder Spalten befinden. Zulässig sind hierbei die Konstanten xlColumns

oder xlRows.

– Das Argument SeriesLabels bestimmt, woher Excel die Beschriftung für die

Datenreihen nehmen soll. Wird der Wert auf True gesetzt, wird der Inhalt der

Zelle in der ersten Spalte jeder Zeile (bzw. der ersten Zeile jeder Spalte) als Name

für die Datenreihe in der betreffenden Zeile (bzw. Spalte) verwendet. Setzen Sie

dieses Argument auf den Wert False, wenn der Inhalt der Zelle in der ersten

Spalte jeder Zeile (bzw. der ersten Zeile jeder Spalte) als erster Datenpunkt der

Datenreihe verwendet werden soll.

– Das Argument CategoryLabels weist den Wert True auf, wenn der Inhalt der ers-

ten Zeile (bzw. Spalte) als Rubrik des Diagramms verwendet wird. Setzen Sie das

Argument auf den Wert False, wenn der Inhalt der ersten Zeile (bzw. Spalte) als

erste Datenreihe im Diagramm verwendet werden soll.

– Das Argument Replace ist standardmäßig auf den Wert True gesetzt. Mit True

werden beim Ersetzen vorhandener Kategorien durch Informationen aus dem

kopierten Bereich Kategorien angewendet. Mit False werden neue Kategorien

eingefügt, ohne die alten zu ersetzen.

– Das Argument NewSeries hat dann den Wert True, wenn die Daten als eine neue

Datenreihe eingefügt werden sollen. Setzen Sie das Argument auf den Wert

False, wenn Sie die Daten als neue Datenpunkte in eine vorhandene Datenreihe

einfügen möchten.

Nach der Kopier- und Einfügeaktion ist nach wie vor der Kopierrahmen aktiv. Setzen

Sie die Eigenschaft CutCopyMode auf den Wert False, um den Ausschneide- bzw.

Kopiermodus zu deaktivieren und den Laufrahmen zu entfernen. Als letzte Aufgabe

weisen Sie dem Diagramm den richtigen Diagrammtyp zu, verzichten auf eine

Legende, indem Sie die Eigenschaft HasLegend auf den Wert False setzen, und zeigen

die Überschrift im Diagramm an.

Tipp

Ist Ihnen während der Diagrammerstellung zu viel Bewegung auf dem Bildschirm,

dann schalten Sie die Bildschirmaktualisierung zu Beginn des Makros mit der

Anweisung Application.Screenupdating = False ab. Am Ende des Makros schalten

Sie die Aktualisierung des Bildschirms wieder ein, indem Sie diese Eigenschaft auf

den Wert True setzen.


486

9.1.5 Tagesgenaue Formatierung im Punktdiagramm

Fügen Sie jetzt ein Punktdiagramm ein, aus dem Sie Ihre täglichen Ausgaben ablesen

können. Als Zusatzfunktion sollen alle Punkte aus der Vergangenheit mit der stan-

dardmäßig ausgewählten Farbe Blau belegt und alle in der Zukunft liegenden Plan-

werte mit einer anderen Farbe versehen werden. Des Weiteren soll die Überschrift

etwas größer erscheinen.

Das Makro für diese Aufgabe sehen Sie in Listing 9.5:

Sub PunktediagrammTagesgenau()Dim Dia As ChartObjectDim s As StringDim DArray As VariantDim Punkt As Point

s = ActiveSheet.Range("A4").ValueActiveSheet.ChartObjects.DeleteSet Dia = ActiveSheet.ChartObjects.Add _(25, 60, 450, 180)Dia.Name = "Ausgaben"Range("A3:H4").Copy

Abbildung 9.5 Das Ergebnis: Tagesumsätze im Liniendiagramm


487

9

ActiveSheet.ChartObjects("Ausgaben").ActivateActiveChart.SeriesCollection.Paste _Rowcol:=xlRows, SeriesLabels:=True, _CategoryLabels:=True, Replace:=True, NewSeries:=TrueApplication.CutCopyMode = False

With ActiveChart.ChartType = xlXYScatter.HasLegend = False.HasTitle = True.ChartTitle.Text = s.ChartTitle.Font.Name = "Arial".ChartTitle.Font.Size = 14.PlotBy = xlRowsEnd With

'Zweiter Teil des MakrosActiveSheet.ChartObjects("Ausgaben").ActivateWith ActiveChart.SeriesCollection(1)DArray = .XValuesFor Each Punkt In .Pointsi = i + 1If DArray(i) > Now ThenPunkt.MarkerBackgroundColorIndex = 2Punkt.MarkerForegroundColorIndex = 1

End IfNext

End WithRange("A1").Select

End Sub

Listing 9.5 Punktdiagramm einfügen und formatieren

Da auf die Erstellung eines Diagramms schon auf den vorigen Seiten ausgiebig einge-

gangen wurde, sehen wir uns direkt den zweiten Teil von Listing 9.5 an. Dort wird das

Diagramm aktiviert und die Datenreihe markiert. Jetzt lesen Sie die einzelnen Werte

der Datenreihe in das Datenfeld DArray ein. Im Anschluss daran wird eine For Each

Next-Schleife durchlaufen, in der geprüft wird, ob die einzelnen Punkte in der Ver-

gangenheit bzw. in der Zukunft liegen. Liegen die Punkte in der Zukunft, wird über

die MarkerBackgroundColorIndex-Eigenschaft die Punkt-Innenfläche mit der Farbe

Weiß versehen. Die Punktumrandung erhält die Farbe Schwarz und wird durch die

Eigenschaft MarkerForegroundColorIndex festgelegt.


488

9.1.6 Mittelwert in Diagramm bilden

In der folgenden Aufgabe wird aus einem Datenbereich ein Diagramm erstellt. Die

Ausgangstabelle sehen Sie in Abbildung 9.7.

Ihre Aufgabe besteht nun darin, ein Säulendiagramm zu erstellen. Dabei soll im Dia-

gramm eine Linie eingezeichnet werden, die den Mittelwert der Kosten darstellen

soll. Das Makro für diese Aufgabe finden Sie in Listing 9.6:

Abbildung 9.6 Das Endergebnis: punktgenaue Formatierung in Abhängigkeit vom

aktuellen Datum

Abbildung 9.7 Die Ausgangsbasis für Ihr Diagramm


489

9

Sub DiagrammMitMittelwertEinfügen()Dim Datenreihe As SeriesDim Punkt As PointDim i As IntegerDim e As Integer

Sheets("Tabelle10").ActivateCharts.AddActiveChart.ChartType = xlColumnClusteredActiveChart.SetSourceData _Source:=Sheets("Tabelle10").Range("A1:G2")ActiveChart.Location _Where:=xlLocationAsObject, Name:="Tabelle10"Set Datenreihe = _ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

With DatenreiheFor Each Punkt In .PointsPunkt.ApplyDataLabelse = e + 1i = i + Punkt.DataLabel.Text

NextEnd WithRange("A3").Value = "Mittelwert"Range("B3:G3").Value = i / eActiveChart.SeriesCollection.Add _Source:=Sheets("Tabelle10").Range("B3:G3")ActiveChart.SeriesCollection(2).ChartType = _xlLineMarkersActiveChart.SeriesCollection(2).Name = Range("A3")

End Sub

Listing 9.6 Diagramm mit Mittelwert einfügen

Fügen Sie im ersten Schritt mithilfe der Methode Add ein noch leeres Diagramm ein.

Danach bestimmen Sie über die Eigenschaft ChartType den Diagrammtyp. Um ein

Säulendiagramm zu erzeugen, geben Sie dieser Eigenschaft die Konstante xlColumn-

Clustered. Bestimmen Sie nun, welche Daten im Diagramm angezeigt werden sollen.

Dazu geben Sie in der Methode SetSourceData die genaue Position Ihrer Daten auf

der Tabelle an. Verwenden Sie die Methode Location, um das neu eingefügte Dia-

gramm zu verschieben. Indem Sie dieser Methode die Konstante xlLocationAsObject


490

übergeben, bestimmen Sie, dass das Diagramm als Objekt in Ihrer Tabelle angeord-

net wird. Im Argument Name müssen Sie dann noch bestimmen, auf welcher Tabelle

das Diagramm eingebettet werden soll. Im nächsten Schritt legen Sie die Beschrif-

tung der Säulen fest. Dabei sollen die Kosten direkt über den einzelnen Säulen ange-

zeigt werden. Für diesen Zweck markieren Sie die komplette Datenreihe im

Diagramm mithilfe der Anweisung ActiveSheet.ChartObjects(1).Chart.SeriesCol-

lection(1).

Erstellen Sie danach eine Schleife, die jeden einzelnen Datenpunkt im Diagramm

durchläuft und mit der Methode ApplyDataLabels die Beschriftung der Säulen vor-

nimmt. Was genau angezeigt werden soll, geben Sie mit der Anweisung DataLabel.

Text an. Damit zeigen Sie die Kosten im Diagramm an. Wurden diese Schritte durch-

geführt, dann übertragen Sie die durchschnittlichen Kosten in die Tabelle sowie in

das Diagramm.

9.2 Diagramme löschen

Möchten Sie Diagramme aus Ihrer Arbeitsmappe entfernen, so wenden Sie die

Methode Delete an. Allerdings müssen Sie dabei unterscheiden, ob Sie Diagramm-

blätter oder eingebettete Diagramme aus Ihrer Arbeitsmappe löschen möchten.

Abbildung 9.8 Das Ergebnis – ein Diagramm mit Mittelwert

9.2 Diagramme löschen

491

9

9.2.1 Diagrammblätter aus Arbeitsmappe entfernen

Um alle Diagrammblätter aus einer Arbeitsmappe zu löschen, setzen Sie das Makro

aus Listing 9.7 ein:

Sub DiagrammeEntfernen()

Application.DisplayAlerts = FalseOn Error Resume NextActiveWorkbook.Charts.DeleteApplication.DisplayAlerts = False

End Sub

Listing 9.7 Alle Diagrammblätter aus einer Arbeitsmappe entfernen

Indem Sie die Eigenschaft DisplayAlerts auf den Wert False setzen, brauchen Sie die

einzelnen Löschungen nicht zu bestätigen. Die On Error-Anweisung sorgt dafür, dass

die Verarbeitung sauber abgefangen wird, wenn überhaupt kein Diagrammblatt in

der Arbeitsmappe enthalten ist. Die Eigenschaft Charts gibt eine Auflistung aller Dia-

grammblätter der aktiven Arbeitsmappe zurück, die Sie mithilfe der Methode Delete

löschen.

Vergessen Sie nicht, die Eigenschaft DisplayAlerts wieder auf den Wert True zu set-

zen, um die Anzeige von Warnungen und Meldungen zu ermöglichen.

9.2.2 Eingebettete Diagramme aus Arbeitsmappe löschen

Um alle eingebetteten Diagramme aus einer Arbeitsmappe zu entfernen, schreiben

Sie ein Makro mit zwei Schleifen. Dies könnte so aussehen wie in Listing 9.8:

Sub AlleEingebettetenDiagrammeLöschen()Dim i As IntegerDim e As Integer

For i = 1 To Sheets.CountSheets(i).ActivateFor e = ActiveSheet.ChartObjects.Count to 1 Step – 1ActiveSheet.ChartObjects(e).delete

Next eNext i

End Sub

Listing 9.8 Alle eingebetteten Diagramme einer Arbeitsmappe löschen


492

Das Makro aus Listing 9.8 besteht aus einer äußeren Schleife, die dafür sorgt, dass

Tabellenblatt für Tabellenblatt abgearbeitet wird. Die zweite Schleife kontrolliert, ob

es auf einzelnen Tabellenblättern ein oder auch mehrere eingebettete Diagramme

gibt, und löscht diese über die Methode Delete.

9.3 Diagramme identifizieren

Wie Sie schon in den vorherigen Beispielen gesehen haben, muss der Name eines

Diagramms bekannt sein, um gezielt darauf zugreifen zu können. Die folgende

Lösung schreibt alle Namen der in der Arbeitsmappe verwendeten Diagramme in

den Direktbereich Ihrer Entwicklungsumgebung.

Sub DiagrammNamenErmitteln()Dim Blatt As WorksheetDim i As IntegerDim Dia As ChartObject

For i = 1 To Sheets.CountFor Each Dia In Sheets(i).ChartObjectsDebug.Print "Tabellennamen: " & Sheets(i).Name & _vbLf & "Diagrammnamen: " & Dia.Name & _vbLf & vbLf

NextNext i

End Sub

Listing 9.9 Diagrammnamen und Position in den Direktbereich schreiben

Definieren Sie zuerst eine Variable vom Typ ChartObject. Danach bilden Sie eine

äußere Schleife, die alle Tabellenblätter der Arbeitsblätter ansteuert, sowie eine

innere Schleife, die die Namen der einzelnen Diagramme und deren Position mit der

Anweisung Debug.Print in den Direktbereich schreibt.

9.3.1 Diagramme umbenennen

Wenn ein neues Diagramm eingefügt wird, bekommt es den Namen Chart mit

einer fortlaufenden Nummer. Sie können diesen Namen ändern und durch einen

eigenen Namen ersetzen. Dazu starten Sie das folgende Makro aus Listing 9.10.

9.4 Datenbasis und Diagramm im Wechsel

493

9

Sub DiagrammUmbenennen()

On Error Resume NextSheets("Tabelle2").ChartObjects(1).Name = "NeuerName"MsgBox Sheets("Tabelle2").ChartObjects(1).Name

End Sub

Listing 9.10 Eingebettete Diagramme umbenennen

Über die Eigenschaft Name geben Sie dem Diagrammobjekt einen neuen Namen und

geben den Namen anschließend in einer Meldung auf dem Bildschirm aus.

9.4 Datenbasis und Diagramm im Wechsel

Um Platz auf Ihrer Tabelle zu sparen, können Sie Diagramm und Datenquelle auch

übereinander anordnen und diese im Wechsel ein- und ausblenden. Die beiden

Makros für diese Aufgabe entnehmen Sie Listing 9.11 und Listing 9.12:

Sub DiagrammAusblenden()

Sheets("Tabelle1").ChartObjects(1).Visible = False

End Sub

Listing 9.11 Diagramm ausblenden

Sub DiagrammEinblenden()

Sheets("Tabelle1").ChartObjects(1).Visible = True

End Sub

Listing 9.12 Diagramm einblenden

Nachdem Sie Ihr Diagramm direkt auf Ihre Datenbasis gelegt haben, starten Sie das

Makro DiagrammAusblenden, um die Datenbasis anzuzeigen, bzw. das Makro Diagramm-

Einblenden, um Ihr Diagramm wieder sichtbar zu machen. Diesen tollen Effekt errei-

chen Sie, indem die Eigenschaft Visible auf den Wert False bzw. auf den Wert True

gesetzt wird.


494

9.5 Datenbeschriftungen hinzufügen und auslesen

Stellen Sie sich vor, Sie haben ein Punktdiagramm, das Ihnen Ihre täglichen Kosten

anzeigt. Jetzt möchten Sie ganz gezielt einzelne Punkte dokumentieren, d. h., den

Punkten Bemerkungen hinzufügen. Diese Aufgabe können Sie über den Einsatz von

zwei Input-Boxen lösen. In der ersten Input-Box geben Sie den Punkt an, zu dem Sie

einen Kommentar hinzufügen möchten, und in der zweiten Input-Box erfassen Sie

den eigentlichen Kommentar zum Datenpunkt.

Das Makro zum Hinzufügen von Kommentaren zu einzelnen Datenpunkten sehen

Sie in Listing 9.13:

Sub DatenbeschriftungHinzufügen()Dim i As Integer

i = InputBox("Welcher Punkt soll kommentiert werden?", _"Kommentierung des Diagramms")If i = 0 Then Exit SubSheets("Punktediagramm").ChartObjects(1).SelectActiveChart.SeriesCollection(1) _.Points(i).ApplyDataLabels _Type:=xlDataLabelsShowLabel, AutoText:=TrueActiveChart.SeriesCollection(1). _Points(i).DataLabel.Text _= InputBox("Bitte Text der Kommentierung eingeben")

End Sub

Listing 9.13 Kommentare zu Datenpunkten in Diagrammen erfassen

Abbildung 9.9 Das Diagramm liegt über den Daten.


495

9

Mithilfe der Methode ApplyDataLabels weisen Sie einem Datenpunkt, der Datenreihe

oder allen Datenreihen im Diagrammblatt Datenbeschriftungen zu.

� Die Syntax: Die Methode hat folgende Syntax:

ApplyDataLabels(Type, LegendKey, AutoText, HasLeaderLines)

� Die Argumente der Methode ApplyDataLabels:

– Das Argument Type stellt den Typ der Datenbeschriftung dar. Zulässig ist eine

der XlDataLabelsType-Konstanten aus Tabelle 9.2.

– Das Argument LegendKey hat den Wert True, wenn Excel die Legende neben dem

Datenpunkt anzeigen soll. Dies ist aber in den meisten Fällen unerwünscht. Aus

diesem Grund ist der Standardwert für dieses Argument False.

– Beim Argument AutoText:=True erzeugt das Objekt, basierend auf dem Inhalt,

automatisch einen angemessenen Text.

– Dem Argument HasLeaderLines weisen Sie den Wert True zu, wenn die Reihe

Führungslinien besitzen soll.

In der zweiten Input-Box geben Sie den Text Ihrer Kommentierung ein, der über das

Objekt DataLabel als Text für den Datenpunkt eingefügt wird.

Konstante Beschreibung

xlDataLabelsShowNone keine Datenbeschriftung

xlDataLabelsShowValue Wert für den Datenpunkt (Wenn dieses

Argument nicht angegeben wird, wird ein

Wert angenommen.)

XlDataLabelsShowPercent Prozentsatz der Summe (nur für Kreis- und

Ringdiagramme verfügbar)

XlDataLabelsShowLabel Rubrikenbeschriftung für den Datenpunkt

(Dies ist der Standardwert.)

XlDataLabelsShowLabelAndPercent Prozentsatz der Summe und Rubriken-

beschriftung für den Datenpunkt (nur für

Kreis- und Ringdiagramme verfügbar)

Tabelle 9.2 Die Konstanten für die Datenbeschriftung bei Diagrammen


496

9.5.1 Datenbeschriftung aus Zellen verwenden

Wenn Sie möchten, kann die Beschriftung Ihrer Diagramme auch direkt aus Zellen

übernommen werden. Das können Sie sowohl für den Titel des Diagramms als auch

für die einzelne Punktbeschriftung vorsehen.

Im Makro aus Listing 9.14 werden sowohl der Diagrammtitel als auch die Beschrif-

tung der Datenpunkte aus Zellen bezogen:

Sub DatenbeschriftungAusZellen()Dim DatenReihe As SeriesDim Punkte As PointsDim Punkt As PointDim i As Integer

Range("A5").SelectSet DatenReihe = _ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)DatenReihe.HasDataLabels = TrueSet Punkte = DatenReihe.Points

For Each Punkt In Punktei = i + 1

Abbildung 9.10 Kommentar zum Datenpunkt erfassen


497

9

Punkt.DataLabel.Text = ActiveCell.Offset(0, i).ValuePunkt.DataLabel.Font.Bold = True

Next PunktActiveSheet.ChartObjects(1).Select

With ActiveChart.HasTitle = True.ChartTitle.Text = Range("A2").Value

End With

End Sub

Listing 9.14 Die Beschriftung eines Diagramms wird aus Zelleninhalten gebildet.

Erstellen Sie zuerst eine Variable vom Typ Series. Damit können Sie eine ganze

Datenreihe eines Diagramms darstellen. In der Variablen DatenReihe speichern Sie

die Datenreihe in Ihrem eingebetteten Diagramm. Indem Sie die Eigenschaft Has-

DataLabels auf den Wert True setzen, geben Sie bekannt, dass das eingebettete Dia-

gramm Datenbeschriftungen erhalten soll. Möchten Sie als Datenbeschriftung die

Bezeichnung des Datenpunkts anzeigen, dann verwenden Sie die Anweisung

Range("A4").Select; möchten Sie hingegen den Wert des Datenpunkts anzeigen, ver-

wenden Sie die Anweisung Range("A5").Select. Diese Angabe bestimmt, wo der

Startpunkt für die Beschriftung des Diagramms ist. Sie benötigen jetzt noch weitere

Variablen: eine Variable vom Typ Points, die alle Punkte in einer Datenreihe symbo-

lisiert, sowie eine Variable vom Typ Point, die genau einen Punkt in einer Datenreihe

darstellt. Mit der Anweisung Set weisen Sie der Variablen Punkte einen Verweis auf

das Objekt Points zu.

Nun können Sie mithilfe einer For Each Next-Schleife den einzelnen Diagrammpunk-

ten einen Text zuweisen. Den Text entnehmen Sie den Zelleneinträgen der Zeile 4

bzw. der Zeile 5. Dazu wenden Sie die Eigenschaft Text auf das Objekt DataLabels an.

Als Zellenbezug für die Beschriftung verwenden Sie die jeweils dazugehörige Zelle,

auf die Sie mit jedem Punkt ein Stück weiter nach rechts verweisen. Für das Versetzen

der Zelle ist die Eigenschaft Offset verantwortlich. Das erste Argument der Verschie-

bung stellt die Zeile dar und beinhaltet im Beispiel den Wert 0, was bedeutet, dass der

Mauszeiger in der aktuellen Zeile verbleibt. Das zweite Argument stellt die Spalten-

verschiebung dar und muss je Punkt um den Wert 1 erhöht werden. Um die einzelnen

Datenbeschriftungen deutlich erkennen zu können, formatieren Sie die Datenbe-

schriftung mit dem Schriftschnitt Fett. Dazu setzen Sie die Eigenschaft Bold ein. Im

Anschluss daran kümmern Sie sich um den Titel, den Sie aus der Zelle A2 beziehen.

Dazu markieren Sie das Diagramm und geben über die Eigenschaft HasTitle an, dass

ein Diagrammtitel angezeigt werden soll. Mit der Eigenschaft Text weisen Sie dem


498

Objekt ChartTitel einen Text zu, den Sie in diesem Fall aber aus einer Zelle entneh-

men.

9.5.2 Daten aus Diagrammen auslesen

Vielleicht ist es Ihnen auch schon einmal passiert: Sie haben zwar ein Diagramm in

einer Excel-Tabelle, die dazugehörige Datenbasis ist aber nicht mehr auffindbar. Sie

müssen jetzt zusehen, wie Sie wieder an die Datenbasis herankommen. Dazu können

Sie ein Makro verfassen, das Ihnen alle Werte aus dem Diagramm in eine Tabelle

schreibt.

Um aus einem eingebetteten Diagramm die einzelnen Werte herauszulesen, setzen

Sie das Makro aus Listing 9.15 ein:

Sub DiagrammWerteAuslesen()Dim s As StringDim i As VariantDim Element As ObjectDim iz As Integer

s = ActiveSheet.NameActiveSheet.ChartObjects(1).Selecti = UBound(ActiveChart.SeriesCollection(1).Values)

Abbildung 9.11 Die Beschriftungen des Diagramms aus Zellen


499

9

Worksheets(s).Cells(1, 1) = "Monate"

With Worksheets(s).Range(.Cells(2, 1), _.Cells(i + 1, 1)) = _Application.Transpose _(ActiveChart.SeriesCollection(1).XValues)

End Withiz = 2

For Each Element In ActiveChart.SeriesCollectionWorksheets(s).Cells(1, iz) = Element.NameWith Worksheets(s).Range(.Cells(2, iz), _.Cells(i + 1, iz)) = _Application.Transpose(Element.Values)

End Withiz = iz + 1

Next

End Sub

Listing 9.15 Datenwerte aus einem Diagramm auslesen

Abbildung 9.12 Die Ausgangsbasis: ein Diagramm, jedoch ohne Datenbasis


500

Ermitteln Sie zuerst einmal den Namen des Tabellenblatts, und speichern Sie ihn in

der Variablen s. Im nächsten Schritt aktivieren Sie das eingebettete Diagramm und

ermitteln über die Funktion UBound den höchsten Index der Datenreihe im Dia-

gramm. Für das Beispiel bedeutet dieser Index die Anzahl der Säulen. Über die Eigen-

schaft Cells positionieren Sie dann genau auf die erste Zelle des Tabellenblattes und

schreiben dort die Überschrift der Tabelle hinein. Im Anschluss daran markieren Sie

den Bereich (Zellen in Spalte A), in dem die X-Achsenbeschriftungen eingefügt wer-

den sollen. Setzen Sie die Funktion Transpose ein, um die Überschriften der X-Achse

einzufügen. Dabei dreht die Funktion Transpose die Richtung für das Einfügen der

Werte um. Da die X-Achsenbeschriftungen von links nach rechts vorliegen, sorgt

diese Funktion dafür, dass die Achsenbeschriftungen von oben nach unten eingefügt

werden. Damit haben Sie die erste Spalte mit den Überschriften der X-Achse gefüllt.

Positionieren Sie danach gleich in die zweite Spalte B, indem Sie die Variable iz auf

den Wert 2 setzen. Setzen Sie nun eine For Each Next-Schleife auf, bei der Sie alle

Datenwerte aus dem Diagramm lesen. In die erste Zeile schreiben Sie dabei jeweils

die Bezeichnung der Datenreihe. Die Werte transferieren Sie wieder mithilfe der

Funktion Transpose in die Spalten B und C.

Abbildung 9.13 Das Endergebnis: die restaurierte Datenbasis

9.6 Diagramme positionieren und skalieren

501

9


Wenn Sie mit eingebetteten Diagrammen arbeiten, müssen Sie vorher genau festle-

gen, wo diese auf dem Tabellenblatt angeordnet werden sollen. Auf den vorigen Sei-

ten haben Sie dazu die Methode Add eingesetzt, der Sie die vier Argumente linker

Rand, oberer Rand, Breite und Höhe mitgegeben haben. Dabei wurde die Positionie-

rung jeweils in Punkt angegeben. Haben Sie ein eingebettetes Diagramm aber schon

erstellt und möchten Sie nachträglich die Position des Diagramms ändern, dann

müssen Sie anders vorgehen. Greifen Sie direkt auf ein eingebettetes Diagramm zu,

und verwenden Sie die beiden Eigenschaften Left und Top, um die obere linke Ecke

des Diagramms zu bestimmen:

Sub DiagrammPositionieren()

With ActiveSheet.Shapes("Diagramm 1").Left = Range("A5").Left.Top = Range("A5").Top

End With

End Sub

Listing 9.16 Eingebettetes Diagramm auf Tabellenblatt positionieren

Über die Eigenschaft ActiveSheet greifen Sie direkt auf das aktive Tabellenblatt zu.

Auf diesem Tabellenblatt befindet sich ein eingebettetes Diagramm, das Sie als

Shape-Objekt identifizieren können. Ein Shape-Objekt kann aber auch eine Auto-

Form, eine ClipArt oder ein anderes OLE-Objekt sein, d. h., Sie können damit auch die

Position von anderen Grafikelementen festlegen. Mit den Eigenschaften Left und

Top geben Sie die Position des angegebenen Diagramms auf dem Tabellenblatt an.

Dazu referieren Sie auf die Zelle A5. Das heißt, die linke obere Ecke soll in Zelle A5

beginnen.

9.6.1 Die Höhe und Breite eines Diagramms festlegen

Möchten Sie nachträglich die Höhe bzw. die Breite eines eingebetteten Diagramms

anpassen, verwenden Sie die Eigenschaften Height und Width:

Sub DiagrammHöheFestlegen()

ActiveSheet.ChartObjects("Diagramm 1").Height = 350

End Sub

Listing 9.17 Die Höhe eines eingebetteten Diagramms festlegen


502

Mit der Eigenschaft ActiveSheet greifen Sie abermals direkt auf das aktive Tabellen-

blatt zu. Dieses Mal sprechen Sie das eingebettete Diagramm aber direkt über das

Auflistungsobjekt ChartObjects an, dessen Höhe Sie durch die Eigenschaft Height

festlegen.

Wollen Sie lediglich die Breite eines Diagramms anpassen, setzen Sie die Eigenschaft

Width ein:

Sub DiagrammBreiteFestlegen()

ActiveSheet.ChartObjects("Diagramm 1").Width = 400

End Sub

Listing 9.18 Die Breite eines eingebetteten Diagramms festlegen

9.6.2 Diagramme platzieren

Sind Ihre eingebetteten Diagramme zu klein und möchten Sie diese als Vollbild in

Ihrer Arbeitsmappe umwandeln, dann starten Sie das Makro aus Listing 9.19:

Sub EingebettetesDiagrammZuDiagrammblatt()

ActiveSheet.ChartObjects(1).ActivateActiveChart.Location Where:=xlLocationAsNewSheet, _Name:="Diagramm in Groß"

End Sub

Listing 9.19 Eingebettetes Diagramm als Diagrammblatt ausgeben

Greifen Sie auf das erste eingebettete Diagramm auf Ihrer Tabelle zu, und wenden Sie

die Methode Location an, um das eingebettete Diagramm zu verschieben. Bei der

Methode Location können Sie im Argument Where entweder die Konstanten xlLoca-

tionAsNewSheet, xlLo-cationAsObject oder xlLocationAutomatic verwenden. In Lis-

ting 9.19 wird das eingebettete Diagramm in ein neues Diagrammblatt verschoben.

Aus diesem Grund verwenden Sie das Argument xlLocationAsNewSheet. Im Argu-

ment Name geben Sie an, wie das neue Diagrammblatt heißen soll.

Aber auch der umgekehrte Fall ist von Interesse. So können Sie ein Diagramm auf

einem Diagrammblatt auch als Objekt in eine Tabelle transferieren. So fügen Sie ein

neues Tabellenblatt ein und positionieren alle Diagramme aus der Arbeitsmappe, die

sich auf Diagrammblättern befinden, auf das neu eingefügte Tabellenblatt. Danach

positionieren Sie die transferierten Diagramme, die dann noch alle übereinander lie-

gen, untereinander und verkleinern die eingebetteten Diagramme ein wenig.


503

9

Sub AlleDiagrammblätterInEineTabelleTransferieren()Dim Blatt As ObjectDim s As StringDim i As IntegerDim Ecke As Integer

Application.ScreenUpdating = FalseSheets.Add Before:=Worksheets(1)Range("A1").Selects = ActiveSheet.Name

For Each Blatt In ActiveWorkbook.SheetsBlatt.ActivateIf TypeName(ActiveSheet) = "Chart" Then _ActiveChart.Location Where:=xlLocationAsObject, _Name:=s

Next BlattSheets(s).ActivateEcke = 10

For i = 1 To ActiveSheet.ChartObjects.CountWith ActiveSheet.ChartObjects(i).Top = Ecke.Left = 10.Height = 150.Width = 350

End WithEcke = Ecke + 160

Next iApplication.ScreenUpdating = True

End Sub

Listing 9.20 Diagrammblätter als eingebettete Diagramme darstellen

Schalten Sie im ersten Schritt die Bildschirmaktualisierung ab. Danach fügen Sie ein

neues Tabellenblatt gleich zu Beginn der Arbeitsmappe ein. Dazu verwenden Sie die

Methode Add, der Sie als Argument Before den Index 1 übergeben. Speichern Sie den

Namen des neuen Tabellenblattes in der Variablen s, um später leichter auf dieses

Tabellenblatt zurückkehren zu können. In einer For Each Next-Schleife arbeiten Sie

sich Blatt für Blatt durch die Arbeitsmappe hindurch. Da nur Diagrammblätter verar-

beitet werden dürfen, fragen Sie mit der Funktion TypeName ab, ob es sich um ein Dia-

grammblatt handelt. Bei einem Diagrammblatt liefert die Funktion TypeName den


504

Wert Chart zurück, bei einer normalen Tabelle den Wert Worksheet. Wird ein Dia-

grammblatt ermittelt, so transferieren Sie mithilfe der Methode Location das Dia-

gramm auf das vorher neu eingefügte Tabellenblatt.

Haben Sie alle Tabellen der Arbeitsmappe überprüft, dann sorgen Sie für die richtige

Anordnung der eingebetteten Diagramme auf dem neuen Tabellenblatt. Excel hat

alle Diagramme aufeinander kopiert. Sie müssen nun diese übereinander liegenden

Diagramme auseinanderbringen und auf dem Tabellenblatt neu anordnen. Dazu set-

zen Sie eine For Next-Schleife auf, in der Sie alle eingefügten Diagramme verteilen.

Über die Eigenschaft Count ermitteln Sie die Anzahl der eingebetteten Diagramme

auf dem Tabellenblatt. In der Variablen Ecke definieren Sie jeweils den oberen Rand

des eingebetteten Diagramms. Zu Beginn setzen Sie die Variable auf den Wert 10 und

erhöhen diese Variable jeweils um mindestens die definierte Höhe des eingefügten

Diagramms.

Möchten Sie alle eingebetteten Diagramme in einer Tabelle markieren, dann setzen

Sie das Makro aus Listing 9.21 ein:

Abbildung 9.14 Alle Diagramme der Arbeitsmappe auf einem Tabellenblatt


505

9

Sub AlleDiagrammeAufTabelleMarkieren()

With Worksheets("Tabelle20")If .Shapes.Count > 0 Then.Shapes.SelectAllEnd If

End With

End Sub

Listing 9.21 Alle eingebetteten Diagramme markieren

Fragen Sie zu Beginn des Makros ab, ob sich überhaupt Diagramme oder auch andere

Grafikobjekte wie Schaltflächen, ClipArts oder Ähnliches in der Tabelle befinden.

Setzen Sie für diese Aufgabe die Anweisung Shapes.Count ein. Liefert diese Abfrage ei-

nen Wert > 0, dann befinden sich »Shapes« in Ihrer Tabelle, die Sie über die Methode

SelectAll markieren können.

9.6.3 Diagramme skalieren

Mit VBA haben Sie die Möglichkeit, auf alle Elemente eines Diagramms zuzugreifen.

So können Sie beispielsweise auch die Skalierung eines oder mehrerer Diagramme

ändern.

Abbildung 9.15 Der Ausgangszustand: zwei Diagramme mit unterschiedlicher Skalierung


506

Wenn Sie Diagramme erstellen, orientiert sich Excel jeweils am höchsten Eintrag in

der Tabelle. Aus diesem Grund sehen die beiden Diagramme in Abbildung 9.15 auch

von der Skalierung her gesehen unterschiedlich aus. Die Dimensionen sind hierbei

verzerrt. Besser wäre hier eine einheitliche Skalierung zum Beispiel auf Basis des

gemeinsamen Maximalwertes.

Gerade wenn Sie noch mehr Diagramme auf einem Tabellenblatt integriert haben

und für alle Diagramme zwecks Vergleichbarkeit dieselbe Skalierung verwenden

möchten, dann können Sie das Makro aus Listing 9.22 anwenden:

Sub DiagrammSkalierungEinstellen()Dim i As IntegerDim maxWert As LongDim Schritt As Integer

maxWert = WorksheetFunction.Max(ActiveSheet.UsedRange)maxWert = maxWert * 1.1Schritt = maxWert / 5For i = 1 To ActiveSheet.ChartObjects.CountActiveSheet.ChartObjects(i).SelectWith ActiveChart.Axes(xlValue).MinimumScale = 0.MaximumScale = maxWert.MajorUnit = Schritt

End WithNext i

End Sub

Listing 9.22 Die Skalierung von mehreren Diagrammen auf einmal anpassen

Im ersten Schritt ermitteln Sie den höchsten Wert auf dem Tabellenblatt. Dieser Wert

soll mit einem kleinen Aufschlag später den obersten Wert der Skalierung darstellen.

Dazu setzen Sie die Funktion Max ein, die Sie auf die Eigenschaft UsedRange anwenden,

und speichern das Ergebnis daraus in der Variablen maxwert. Zum so ermittelten Wert

fügen Sie noch 10 Prozent hinzu, damit die größte Säule nicht direkt unter dem obe-

ren Rand hängt und noch ein wenig Luft hat. Dazu multiplizieren Sie die Variable

maxwert mit dem Faktor 1.1, was zusätzlichen 10 Prozent entspricht. Nun bestimmen

Sie die Schrittweite, bei der horizontale Trennlinien gezogen werden sollen. So könn-

ten Sie sich z. B. entschließen, genau fünf horizontale Trennlinien zuzulassen. Divi-

dieren Sie dazu die Variable maxwert durch den Wert 5, und speichern Sie das Ergebnis

daraus in der Variablen Schritt. Mit einer For Next-Schleife werden alle eingebette-

ten Diagramme des Tabellenblattes durchlaufen und wird die einheitliche Skalierung

eingestellt. Über die Anweisung ChartObjects.Count ermitteln Sie die Anzahl der ein-

9.7 Diagramme als Grafiken speichern

507

9

gebetteten Diagramme auf dem Tabellenblatt. Mithilfe der Methode Select aktivie-

ren Sie innerhalb der Schleife ein Diagramm nach dem anderen und wenden

folgende Eigenschaften auf die Achse an:

� Die Eigenschaft MinimumScale stellt den Minimalwert einer Größenachse dar und

wird im Beispiel auf den Wert 0 gesetzt.

� Mit der Eigenschaft MaximumScale geben Sie den höchsten Wert der Größenachse

bekannt, den Sie aus der Variablen maxwert nehmen.

� Über die Eigenschaft MajorUnit legen Sie die Schrittweite der Größenachse fest.

Den Wert dazu entnehmen Sie der Variablen Schritt.

9.7 Diagramme als Grafiken speichern

In Excel können Sie Diagramme über einen Grafikfilter umwandeln. Das bringt Vor-

teile, wenn Sie Diagramme an Personen ausliefern möchten, die kein Excel installiert

haben. Erstellen Sie aus den Excel-Diagrammen speicherschonende Grafikdateien im

GIF-Format. Auch wenn Sie Diagramme in Ihrer Textverarbeitung verwenden und

dabei keine Excel-Diagramme verknüpfen möchten, ist das Einbinden von Dia-

grammgrafiken die bessere Methode. Allerdings können diese umgewandelten Dia-

gramme dann nicht mehr aktualisiert werden. Das Makro aus Listing 9.23 wandelt

alle eingebetteten Diagramme einer Arbeitsmappe in GIF-Dateien um und legt diese

im Verzeichnis C:\ ab.

Abbildung 9.16 Das Endergebnis: Alle Diagramme sind einheitlich skaliert.


508

Sub DiagrammeAlsGrafikSpeichern()Dim Dia As ChartDim i As IntegerDim e As Integer

For i = 1 To Sheets.CountSheets(i).ActivateFor e = 1 To ActiveSheet.ChartObjects.CountActiveSheet.ChartObjects(e).SelectSet Dia = ActiveSheet.ChartObjects(e).ChartDia.Export Filename:="c:\" & _ActiveSheet.ChartObjects(e).Name & _".gif", FilterName:="GIF"

Next eNext i

End Sub

Listing 9.23 Alle eingebetteten Diagramme einer Arbeitsmappe

werden in Grafikdateien exportiert.

Das Makro aus Listing 9.23 besteht aus zwei For Next-Schleifen: Die äußere Schleife

arbeitet alle Tabellenblätter der aktiven Arbeitsmappe ab, die innere Schleife expor-

tiert alle eingebetteten Diagramme auf dem jeweiligen Tabellenblatt. Über die

Methode Filter exportieren Sie die Diagramme in ein Grafikformat. Dabei verwen-

det die Methode Filter die Argumente FileName und FilterName. Im Argument File-

Name geben Sie den Pfad sowie den Namen der Grafikdatei an. Dazu wenden Sie die

Eigenschaft Name auf das Chart-Objekt an. Im Argument FilterName wird der Name

des Grafikfilters angegeben, der verwendet werden soll.

Abbildung 9.17 Alle Diagramme wurden als Grafiken ins Verzeichnis C:\ exportiert.

9.8 Diagramme formatieren

509

9

Mit einem Doppelklick auf die jeweilige Grafikdatei im Windows-Explorer können

Sie die Diagrammgrafik öffnen.


In Excel haben Sie die Möglichkeit, Datenreihen in Diagrammen zu formatieren. Sie

können beispielsweise den Abstand von Säulen oder Balken beeinflussen, verschie-

dene Farben für die Diagrammobjekte einsetzen, bei der Beschriftung der Dia-

gramme aus einer breiten Auswahl von Schrifteffekten und Schriftschnitten wählen

und vieles mehr.

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

In der folgenden Aufgabe werden in einem Säulendiagramm Monatsergebnisse dar-

gestellt. Damit auf einen Blick sichtbar wird, wenn ein Ergebnis in einem Monat

unter den Wert 0 fällt, also ein Verlust eingefahren wird, sollen solche Datenpunkte

mit der Hintergrundfarbe Rot belegt werden. Das Makro für diese Aufgabe sehen Sie

in Listing 9.24:

Sub SäulenVerschiedenfarbig()Dim Datenreihe As SeriesDim Punkt As PointDim i As IntegerDim DArray As Variant

Set Datenreihe = _ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)With DatenreiheDArray = .ValuesFor Each Punkt In .Pointsi = i + 1If DArray(i) < 0 ThenPunkt.Interior.ColorIndex = 3

ElsePunkt.Interior.ColorIndex = 33

End IfNext

End WithEnd Sub

Listing 9.24 Verschiedenfarbige Säulen, je nach Ergebnissituation


510

Definieren Sie zuerst eine Objektvariable vom Typ Series. Anschließend lesen Sie alle

Y-Werte in ein Datenfeld ein und durchlaufen danach eine For Each Next-Schleife, in

der die einzelnen Datenpunkte überprüft werden. Die Werte entnehmen Sie dem

Datenfeld DArray. Weist ein Datenpunkt einen Wert < 0 auf, so wird der Innenbereich

der Säule mit der Farbe Rot formatiert. Bei allen positiven Werten wird ein blauer

Farbton gewählt.

Das Makro aus Listing 9.24 hat noch einen kleinen Schönheitsfehler. Sie müssen das

Makro immer manuell starten, um das Diagramm neu zu formatieren. Wenn also

Daten in der Tabelle geändert wurden, ändert sich die Formatierung des Diagramms

nicht. Dem kann aber abgeholfen werden, indem Sie eine ereignisabhängige Funk-

tion programmieren.

Das Ereignis heißt Worksheet_Change und tritt ein, wenn sich irgendeine Zelle auf dem

aktiven Tabellenblatt ändert. Genau dieses Ereignis kann für die Aufgabenstellung

verwendet werden. Um das Ereignis einzustellen, gehen Sie wie folgt vor:

1. Wechseln Sie in die Entwicklungsumgebung.

2. Im Projekt-Explorer klicken Sie die Tabelle doppelt an, auf der sich Ihr Diagramm

befindet.

3. Geben Sie im Codebereich folgendes Ereignis ein:

Abbildung 9.18 Negative Monatsergebnisse werden rot formatiert.


511

9

Private Sub Worksheet_Change(ByVal Target As Range)

SäulenVerschiedenfarbig

End Sub

Listing 9.25 Makro automatisch bei Zellenänderung starten

Immer wenn Sie eine Zelle auf Ihrem Tabellenblatt verändern, sei es nun durch

direkte Eingabe oder auch durch eine Formel, wird das Makro SäulenVerschiedenfar-

big automatisch ausgeführt.

Lernen Sie noch mehr über den praktischen Einsatz von Ereignissen in Kapitel 12,

»Ereignisse in Excel einsetzen«.

Befindet sich das Diagramm auf einem eigenen Diagrammblatt, dann setzen Sie das

Makro aus Listing 9.26 ein, um die Säulen zu formatieren:

Sub SäulenVerschiedenfarbig()Dim Datenreihe As SeriesDim Punkt As PointDim i As IntegerDim DArray As Variant

On Error Resume NextSheets("Diagramm1").ActivateSet Datenreihe = _ActiveChart.SeriesCollection(1)With DatenreiheDArray = .ValuesFor Each Punkt In .Pointsi = i + 1If DArray(i) < 0 ThenPunkt.Interior.ColorIndex = 3

ElsePunkt.Interior.ColorIndex = 33

End IfNext

End With

End Sub

Listing 9.26 Diagrammblatt ansprechen und formatieren


512

9.8.2 Eine Ist-Darstellung in einem Säulendiagramm darstellen

Im nächsten Beispiel geht es darum, in einem Diagramm, in dem alle zwölf Monate

eines Jahres abgebildet sind, in Abhängigkeit vom aktuellen Datum die Säulen ver-

schieden zu formatieren. Stellen Sie sich also vor, heute wäre der 15. März 2013. Ihre

Aufgabe besteht nun darin, die Säulen von Januar bis zum aktuellen Monat März mit

der Farbe Gelb zu formatieren. Die restlichen Monate April bis Dezember sollen die

Farbe Blau bekommen. Das Makro für diese Aufgabe sehen Sie in Listing 9.27:

Sub DiagrammFärben()Dim i As IntegerDim j As IntegerDim datum1 As DateDim Datindex As Integer

Sheets("Tabelle5").Activatedatum1 = Range("A1").ValueDatindex = Month(datum1)

For i = 1 To DatindexActiveWindow.Visible = TrueActiveSheet.ChartObjects(1).ActivateActiveChart.ChartArea.SelectActiveChart.SeriesCollection(1).SelectActiveChart.SeriesCollection(1).Points(i).SelectWith Selection.Border.Weight = xlThin.LineStyle = xlAutomatic

End WithSelection.Shadow = FalseSelection.InvertIfNegative = FalseWith Selection.Interior.ColorIndex = 44.Pattern = xlSolid

End WithNext i

End Sub

Listing 9.27 Zweifarbiges Säulendiagramm erstellen

In Zelle A1 haben Sie die Tabellenfunktion =HEUTE() erfasst. Diese Funktion liefert

Ihnen das aktuelle Datum. Dieses Datum speichern Sie in der Variablen datum1. Im

Anschluss daran ermitteln Sie den Monat des Datums mithilfe der Funktion Month.

Diese Funktion gibt Ihnen einen Wert zwischen 1 und 12 zurück. Damit wissen Sie,


513

9

wie viele Säulen Sie färben müssen. In einer Schleife weisen Sie dann die gewünschte

Farbe zu.

9.8.3 Legenden formatieren

Wenn Sie möchten, können Sie direkt auf eine Legende zugreifen und ihre Beschrif-

tung formatieren. Dabei haben Sie die Möglichkeit, Schriftgröße, Schriftfarbe sowie

den Schriftschnitt festzulegen. Im folgenden Beispiel aus Listing 9.28 wird eine

Legende mit dem Schriftschnitt Fett und der Schriftfarbe Blau formatiert.

Sub LegendeFormatieren()ActiveSheet.ChartObjects(1).SelectActiveChart.HasLegend = True

With ActiveChart.Legend.Font.Bold = True.ColorIndex = 5

End With

End Sub

Listing 9.28 Legende eines eingebetteten Diagramms formatieren

Nachdem Sie das eingebettete Diagramm auf dem aktiven Tabellenblatt markiert

haben, blenden Sie mithilfe der Eigenschaft HasLegend, die Sie auf den Wert True set-

zen, die Legende ein. Im Anschluss daran wenden Sie die Eigenschaften Bold und

ColorIndex auf die Schrift der Legende an.

Abbildung 9.19 Monatsgenau formatieren


514

9.8.4 Zusätzliche Labels hinzufügen

Sollten Ihnen die Überschrift sowie die Achsenbeschriftungen nicht ausreichen, kön-

nen Sie zusätzliche Textfelder in Ihr eingebettetes Diagramm einfügen. Sehen Sie

sich dazu das Makro aus Listing 9.29 an.

Sub ZusätzlichesTextfeld()

ActiveSheet.ChartObjects(1).SelectActiveChart.HasTitle = TrueActiveChart.ChartTitle.Text = "Kosten und Leistung"ActiveChart.Shapes.AddLabel _(msoTextOrientationHorizontal, _10, 15, 0, 0).TextFrame.Characters.Text = "Zusatzinfo"

End Sub

Listing 9.29 Zusätzliches Feld in ein eingebettetes Diagramm einfügen

Mit der Methode AddLabel können Sie eine zusätzliche Beschriftung in Ihrem Dia-

gramm hinzufügen. Dabei geben Sie die Konstante msoTextOrientationHorizontal an

und legen die linke obere Ecke über die ersten beiden Argumente in der Klammer

fest. Das Objekt TextFrame stellt den Textrahmen dar und enthält sowohl den Text als

auch die Eigenschaften und Methoden zur Steuerung von Ausrichtung und Veranke-

rung des Textrahmens. Über die Eigenschaft Text schreiben Sie Ihren zusätzlichen

Text in das Textfeld.

Abbildung 9.20 Zusätzliche Labels sind immer möglich.

9.9 Dynamische Diagramme erzeugen

515

9

9.9 Dynamische Diagramme erzeugen

Viele Diagramme werden über Monate, ja sogar Jahre hinweg gepflegt und immer

wieder erweitert. Die Erweiterung von Diagrammen ist eine lästige Aufgabe, da Sie

erstens die Datenreihen einzeln erweitern und dabei zweitens genau aufpassen müs-

sen, dass Sie keine falschen Zellenbezüge angeben. Für diese Aufgabe ist eine Excel-

VBA-Lösung geradezu ideal. Stellen Sie sich vor, Sie markieren den Datenbereich, der

in einem Diagramm angezeigt werden soll, und starten ein Makro, das Ihnen das

bereits bestehende Diagramm dynamisch erweitert. Diese Funktionalität erinnert

stark an das Standarddiagramm, bei dem Sie ebenso den Datenbereich markieren

und anschließend die Taste (F11) drücken. Bei dieser Standardlösung ist der Nachteil,

dass Sie das Diagramm immer wieder neu erstellen und danach alle weiteren Forma-

tierungen am Diagramm manuell ausführen müssen. Außerdem wird bei dieser

Standardlösung ein neues Diagrammblatt eingefügt. Wenn Sie also ein bereits beste-

hendes und eingebettetes Diagramm flexibel erweitern möchten, setzen Sie das

Makro aus Listing 9.30 ein.

Das Erweitern von Diagrammen können Sie am besten vornehmen, wenn Sie den

Datenbereich benennen. Damit entfällt erstens viel Schreibarbeit, und zweitens

macht es das Makro leichter lesbar.

Sub DatenreihenErweitern()Dim Blatt As WorksheetDim s As String

s = ActiveSheet.NameSet Blatt = ActiveSheetOn Error Resume Next

Abbildung 9.21 Die Ausgangsbasis: Das Diagramm muss erweitert werden.


516

ActiveWorkbook.Names("DiaBereich").DeleteActiveWorkbook.Names.Add _Name:="DiaBereich", RefersToR1C1:=SelectionBlatt.ChartObjects(1).SelectActiveChart.SetSourceData _Source:=Sheets(s).Range("DiaBereich")

End Sub

Listing 9.30 Ein eingebettetes Diagramm dynamisch erweitern

Definieren Sie zuerst eine Objektvariable vom Typ Worksheet. Danach speichern Sie

den Namen der Tabelle, auf der sich das eingebettete Diagramm befindet, in der Vari-

ablen s. Da Sie den Datenbereich benennen möchten, der in Ihr eingebettetes Dia-

gramm eingeht, löschen Sie vorher einen eventuell schon bestehenden Namen über

die Methode Delete. Die On Error-Anweisung fügen Sie zur Sicherheit vor die Zeile

mit dem Befehl Delete ein. Diese Anweisung sorgt hier dafür, dass das Makro nicht

abstürzt, wenn noch kein Name für den Datenbereich vergeben wurde.

Im nächsten Schritt fügen Sie mithilfe der Methode Add einen neuen Namen ein, der

den Datenbereich für das Diagramm beinhalten soll. Als Argument übergeben Sie an

die Methode Add den Namen sowie den Zellenbezug, auf den sich der Name beziehen

soll. Im Argument RefersToR1C1 verweisen Sie auf die aktuell ausgewählten Zellen,

d. h., Sie müssen vor dem Start des Makros den Datenbereich markieren, der in das

Diagramm eingehen soll. Nach der Namensvergabe markieren Sie das eingebettete

Abbildung 9.22 Das Endergebnis: das erweiterte Diagramm

9.10 Pivot-Tabellenberichte erstellen

517

9

Diagramm und wenden die Methode SetSourceData an. Sie benötigt das Argument

Source. Es beinhaltet den Bereich, der die Quelldaten für das Diagramm enthält. Die-

ses Argument füllen Sie mit dem Inhalt der Variablen s, die den Namen der Tabelle

enthält, und mit dem vorher vergebenen Namen.


In Excel haben Sie die Möglichkeit, sehr schnell aussagekräftige Berichte zu erstellen.

Die wohl beste und sicherste Methode, in Excel Daten auszuwerten und aufzuberei-

ten, ist die Anwendung von Pivot-Tabellen. Der Begriff »Pivot« kommt aus dem Fran-

zösischen und bedeutet »Dreh- und Angelpunkt«. Damit wird schon einiges über die

Funktion ausgesagt: Sie können es drehen und wenden, wie Sie wollen – Sie werden

immer die richtigen Ergebnisse bekommen.

Bei der Arbeit mit Pivot-Tabellen gibt es zwei Punkte, die Sie beachten müssen:

� Ist der Pivot-Tabellenbericht aktualisiert?

� Greift die Pivot-Tabelle wirklich auf den gewünschten Datenbereich zu? (Das

heißt: Ist der Datenbereich nachträglich vergrößert worden?)

Beide Fragen werden noch in diesem Kapitel beantwortet.

Zuerst werden Sie über ein VBA-Makro einen Pivot-Tabellenbericht erstellen. Die

Ausgangstabelle sehen Sie in Abbildung 9.23.

Abbildung 9.23 Die Ausgangstabelle – eine Mitarbeiterliste


518

In einem Pivot-Tabellenbericht soll nun dargestellt werden, welche Vorgesetzten

welche Mitarbeiter an welchem Standort haben. Das Makro für diese Aufgabe sehen

Sie in Listing 9.31:

Sub PivotTabelleErstellen()Dim Bereich As Range

Sheets("Personal").ActivateSet Bereich = ActiveSheet.UsedRangeRange(Bereich.Address).SelectActiveSheet.PivotTableWizard _SourceType:=xlDatabase, SourceData:= _Bereich, TableDestination:="", TableName:="Pivot"With ActiveSheet.PivotTables("Pivot").PivotFields("Vorgesetzter").Orientation = xlPageField.PivotFields("Name").Orientation = xlRowField.PivotFields("Standort").Orientation = xlColumnField.PivotFields("Name").Orientation = xlDataField

End With

End Sub

Listing 9.31 Einen Pivot-Tabellenbericht erstellen

Im ersten Schritt aktivieren Sie das Tabellenblatt, das die Quelldaten für die Auswer-

tung enthält, und legen den verwendeten Bereich in der Variablen Bereich fest.

Danach markieren Sie den Quellbereich und wenden die Methode PivotTableWizard

an, die die Pivot-Tabelle erstellt.

� Die Syntax: Die Methode PivotTableWizard hat folgende Syntax:

PivotTableWizard(SourceType, SourceData, _TableDestination, TableName, RowGrand, _ColumnGrand, SaveData, HasAutoFormat, AutoPage, _Reserved, BackgroundQuery, OptimizeCache, _PageFieldOrder, PageFieldWrapCount, ReadData, _Connection)

� Die Argumente der Methode Pivot-TableWizard: Auf die wichtigsten Argumente

wird im Folgenden näher eingegangen.

– Im Argument SourceType geben Sie die Quelle der Daten in dem Bericht an.

Zulässig ist eine der in Tabelle 9.3 aufgeführten Konstanten.


519

9

– Beim Argument SourceData legen Sie den Datenbereich für den neuen Bericht

fest. Hierfür haben Sie vorher den verwendeten Bereich auf dem Tabellenblatt

in der Variablen Bereich definiert, die Sie hier nun als Argument angeben.

– Im Argument TableDestination können Sie eine Zieltabelle inklusive der Posi-

tion der ersten Zelle für den Pivot-Tabellenbericht angeben. Lassen Sie dieses

Argument leer, wenn der Pivot-Tabellenbericht auf einer neuen Tabelle, begin-

nend ab Zelle A1, eingefügt werden soll.

– Im Argument TableName geben Sie dem Pivot-Tabellenbericht einen Namen.

Dies ist sehr wichtig, um später bei der Anordnung der Pivot-Felder auf die rich-

tige Pivot-Tabelle zuzugreifen. Wird dieses Argument nicht gesetzt, vergibt

Excel eigene Namen für die Pivot-Tabellen, die die Bezeichnung Pivottable

und eine fortlaufende Nummer enthalten. Gerade diese fortlaufende Nummer

macht dann bei der weiteren Programmierung der Pivot-Tabelle Schwierigkei-

ten. Aus diesem Grund füllen Sie dieses Argument mit einem Namen, der kon-

stant bleibt.

Beginnen Sie jetzt mit der Anordnung Ihrer Datenfelder, und nutzen Sie dazu das

Auflistungsobjekt PivotFields. In diesem Auflistungsobjekt stehen alle verfügbaren

Feldnamen der Pivot-Tabelle, die den Spaltenüberschriften des Quellbereiches ent-

sprechen.

Hinweis

Wenn Sie nicht sicherstellen können, dass die Spaltenüberschriften Ihres Datenbe-

reiches konstant bleiben, können Sie die einzelnen Feldnamen auch über einen

Index ansprechen. Wenn die Spaltenüberschriften jedoch konstant bleiben, ver-

wenden Sie besser die Feldnamen für die bessere Lesbarkeit des Makros.

Mit der Eigenschaft Orientation legen Sie die Position des Feldnamens in der Pivot-

Tabelle fest. Tabelle 9.4 führt die Möglichkeiten auf, die Sie dabei haben.


xlConsolidation mehrere Konsolidierungsbereiche

xlDatabase Microsoft Excel-Datenbank oder -Liste (Standard)

xlExternal externe Datenquelle

xlPivotTable gleiche Quelle wie ein anderer PivotTable-Bericht

Tabelle 9.3 Die möglichen Datenquellen-Konstanten bei Pivot-Tabellenberichten


520

9.11 Pivot-Tabellen aktualisieren

Bei Pivot-Tabellenberichten müssen Sie daran denken, dass Sie nach einer Änderung

der Quelldaten nicht vergessen, die Pivot-Tabelle auf den neuesten Stand zu bringen.

Dieser Vorgang geschieht in Excel nicht automatisch; dafür sind Sie verantwortlich.

9.11.1 Eine einzelne Pivot-Tabelle aktualisieren

Wenn Sie ganz gezielt eine einzelne Pivot-Tabelle in einer Arbeitsmappe aktualisie-

ren möchten, setzen Sie das Makro aus Listing 9.32 ein:

Sub EinzelnePivotTabelleAufBlattAktualisieren()

On Error GoTo fehlerSheets(1).PivotTables("Pivot").RefreshTableExit Sub

fehler:MsgBox "Es konnte keine Pivot-Tabelle gefunden werden!"

End Sub

Listing 9.32 Einzelne Pivot-Tabelle auf Tabellenblatt aktualisieren

Auf dem ersten Tabellenblatt in der aktiven Arbeitsmappe wird die Pivot-Tabelle

Pivot mithilfe der Methode RefreshTable auf den aktuellen Stand gebracht. Die

Anweisung On Error ist eine reine Sicherheitsmaßnahme, die verhindern soll, dass

das Makro abstürzt, wenn die richtige Pivot-Tabelle nicht gefunden werden kann.


xlColumnField Im Spaltenbereich der Pivot-Tabelle wird der Standort ausgegeben.

xlDataField Im Datenbereich der Pivot-Tabelle wird die Anzahl der Mitarbeiter

gezählt.

xlHidden Mit dieser Konstante können Sie einzelne Datenfelder ausblenden.

xlPageField Im Seitenfeld der Pivot-Tabelle wird der gewünschte Vorgesetzte

eingestellt.

xlRowField Im Zeilenbereich werden die Namen der Mitarbeiter aufgelistet.

Tabelle 9.4 Die möglichen Ausrichtungskonstanten bei Pivot-Tabellenberichten

9.11 Pivot-Tabellen aktualisieren

521

9

9.11.2 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

Oft werden auch mehrere Pivot-Tabellen auf einem einzigen Tabellenblatt geführt.

Dabei können Sie alle Pivot-Tabellen auf einmal updaten, indem Sie das Makro aus

Listing 9.33 einsetzen:

Sub MehrerePivotTabellenAufBlattAktualisieren()Dim pt As PivotTable

On Error Resume NextFor Each pt In Sheets(1).PivotTablespt.RefreshTable

Next pt

End Sub

Listing 9.33 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren

Definieren Sie eine Objektvariable vom Typ PivotTable, und setzen Sie eine For Each

Next-Schleife auf. Innerhalb der Schleife wenden Sie die Methode RefreshTable an,

um die einzelnen Pivot-Tabellenberichte zu aktualisieren.

9.11.3 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren

Haben Sie in einer Arbeitsmappe mehrere Pivot-Tabellenberichte auf unterschiedli-

chen Tabellenblättern erstellt und möchten Sie diese nun automatisch aktualisieren,

dann setzen Sie das Makro aus Listing 9.34 ein:

Sub AllePivotTabellenInArbeitsmappeAktualisieren()Dim Blatt As WorksheetDim pt As PivotTable

For Each Blatt In ActiveWorkbook.WorkSheetsFor Each pt In Sheets(1).PivotTablespt.RefreshTable

Next ptNext Blatt

End Sub

Listing 9.34 Alle Pivot-Tabellen in der aktiven Arbeitsmappe aktualisieren

Die Aktualisierung aller Pivot-Tabellenberichte Ihrer aktiven Arbeitsmappe errei-

chen Sie über zwei Schleifen. In der ersten Schleife werden alle Tabellenblätter durch-


522

laufen. Mit der zweiten Schleife werden innerhalb eines durchlaufenen Blattes alle

Pivot-Tabellenberichte aktualisiert.

Möchten Sie diese Aktualisierung automatisch nach dem Öffnen der Arbeitsmappe

durchführen, dann wenden Sie das Ereignis Workbook_Open an. Dazu verfahren Sie wie

folgt:


2. Klicken Sie im Projekt-Explorer den Eintrag DieseArbeitsmappe doppelt an.

3. Erfassen Sie das Ereignis Workbook_Open.

Private Sub Workbook_Open()

AllePivotTabellenInArbeitsmappeAktualisieren

End Sub

Listing 9.35 Nach dem Öffnen einer Arbeitsmappe werden alle Pivot-Tabellen aktualisiert.

In Kapitel 12, »Ereignisse in Excel einsetzen«, lernen Sie mehr über die Möglichkeiten,

die Ereignisse bieten.

9.12 Pivot-Tabellen dynamisch erweitern

Die zweite Gefahr neben der, die Aktualisierung der Pivot-Tabelle zu vergessen, ist,

dass der Anwender den Datenbereich erweitert und versäumt, diese Erweiterung sei-

ner Pivot-Tabelle mitzuteilen. Das manuelle Einstellen der Pivot-Tabelle auf den

neuen Bereich ist recht mühselig, wenn Sie mehrere Pivot-Tabellen nacheinander

auf den neuesten Stand bringen müssen. In beiden Fällen (fehlende Aktualisierung

bzw. falscher Quellbereich) ist die Auswirkung gleich: Die Pivot-Tabelle liefert nicht

die richtigen Ergebnisse.

Die Änderung des Quellbereichs, der die Daten für die Pivot-Tabelle enthält, hat zur

Folge, dass auch der Pivot-Tabelle diese Änderung mitgeteilt werden muss. Um diese

Aufgabe zu erledigen, benennen Sie Ihren Datenbereich vorher und erweitern die

Pivot-Tabelle mithilfe des Makros aus Listing 9.36:

Sub PivotTabellenDatenbereichErweitern()Dim pt As PivotTableDim Bereich As Range

Set Bereich = Sheets("Personal").UsedRangeActiveWorkbook.Names.Add _Name:="PivotBereich", _RefersTo:=Bereich, Visible:=True

9.12 Pivot-Tabellen dynamisch erweitern

523

9

Bereich.Select

For Each pt In Sheets(1).PivotTablesWith pt.PivotTableWizard SourceType:=xlDatabase, _SourceData:="PivotBereich"

End Withpt.RefreshTable

Next pt

End Sub

Listing 9.36 Dynamische Erweiterung einer Pivot-Tabelle

Ermitteln Sie zuerst einmal den verwendeten Bereich auf Ihrer Tabelle Personal,

und speichern Sie diesen Bereich in der Objektvariablen Bereich. Benennen Sie

danach den ermittelten Bereich, geben Sie diesem den Namen PivotBereich, und

markieren Sie ihn im Anschluss.

In Kapitel 5, »Zellen und Bereiche programmieren«, können Sie noch einmal nach-

schlagen, wie Sie Zellen bzw. Bereiche benennen können.

Abbildung 9.24 Der Datenbereich wurde nach unten erweitert.


524

Mithilfe einer For Each Next-Schleife greifen Sie auf alle Pivot-Tabellenberichte auf

dem ersten Blatt Ihrer Arbeitsmappe zu, erweitern den Datenbereich und aktualisie-

ren die Pivot-Tabellenberichte. Der neue Datensatz wird am Ende der Pivot-Tabelle

angehängt. Die Pivot-Tabelle muss jetzt neu sortiert werden.

9.13 Pivot-Tabellen sortieren

Bei der vorhergehenden Aufgabe haben Sie gesehen, dass die Pivot-Tabelle zwar

erweitert wurde, jedoch wurden die neuen Einträge am Ende der Pivot-Tabelle ange-

hängt. Pivot-Tabellenberichte lassen sich in Excel genauso sortieren wie ganz nor-

male Listen. Dazu setzen Sie den Zeiger einfach auf das Feld im Spaltenbereich der

Pivot-Tabelle (im Beispiel Name) und wählen danach aus dem Menü Daten den Befehl

Sortieren. Den Sortiervorgang können Sie aber auch mit dem nächsten Makro aus

Listing 9.37 automatisch erledigen lassen:

Sub PivotTabelleSortieren()Dim Pivot1 As PivotTable

ActiveSheet.PivotTables("Pivot").PivotSelect _"'Name'[All]", xlLabelOnlySelection.Sort Order1:=xlAscending, _Type:=xlSortLabels, OrderCustom:=1, _Orientation:=xlTopToBottom

End Sub

Listing 9.37 Pivot-Tabellen sortieren

Mithilfe der Methode PivotSelect können Sie einen Teil einer Pivot-Tabelle markie-

ren. Dabei müssen Sie der Methode den Namen des Datenfeldes übergeben, das Sie

markieren möchten. Im zweiten Argument legen Sie den Markierungsmodus über

eine Konstante fest. In Tabelle 9.5 sehen Sie die Konstanten, die Ihnen zur Verfügung

stehen.


xlBlanks Es werden nur leere Felder innerhalb der Pivot-Tabelle markiert.

xlButton Markiert eine bzw. mehrere Schaltflächen in einem Pivot-

Tabellenbericht.

Tabelle 9.5 Möglichkeiten der Markierung bei Pivot-Tabellen

9.14 Pivot-Tabellen formatieren

525

9

Im Anschluss an die Markierung der Daten in der Pivot-Tabelle wenden Sie die

Methode Sort an, deren Argument Sie bereits in Kapitel 6, »Die Programmierung von

Spalten und Zeilen«, kennengelernt haben.

9.14 Pivot-Tabellen formatieren

Seit der Excel-Version 2000 haben Sie die Möglichkeit, die AutoFormate auch für

Pivot-Tabellenberichte einzusetzen. Dabei haben Sie die Auswahl aus über vierzig

verschiedenen AutoFormaten.

Eine komplette Liste der AutoFormate in Excel finden Sie in der VBA-Online-Hilfe

unter dem Stichwort »AutoFormat«.

Um z. B. die Pivot-Tabelle mit der Mitarbeiterauswertung zu formatieren, können Sie

das Makro aus Listing 9.38 einsetzen:

Sub PivotFormatEinstellen()Dim Pivot1 As PivotTable

Set Pivot1 = Worksheets(1).PivotTables(1)With Pivot1.TableRange1.AutoFormat Format:=xlClassic3

End With

End Sub

Listing 9.38 AutoFormate auf Pivot-Tabellen anwenden

Über die Eigenschaft TableRange1 können Sie einen Pivot-Tabellenbericht auswählen.

Dabei sind allerdings keine Seitenfelder (z. B. Vorgesetzter) inbegriffen. Möchten

xlDataAndLabel Hierbei werden sowohl der Zeilenbezeichner als auch der Daten-

bereich markiert (Standard-Einstellung).

xlDataOnly Es wird nur der Datenbereich der Pivot-Tabelle markiert.

xlFirstRow Die erste Zeile einer Pivot-Tabelle wird markiert.

xlLabelOnly Es erfolgt eine Markierung des angegebenen Datenfeldes.

xlOrigin Markiert das Feld in der Pivot-Tabelle, das im Schnittpunkt

zwischen Zeilen- und Spaltenbereich steht (Anzahl-Name).


Tabelle 9.5 Möglichkeiten der Markierung bei Pivot-Tabellen (Forts.)


526

Sie die Formatierung jedoch für die komplette Pivot-Tabelle, also inklusive der Sei-

tenfelder, anwenden, verwenden Sie die Eigenschaft TableRange2. Auf den so bekannt

gemachten Bereich wenden Sie die Methode AutoFormat an, die den markierten

Bereich mit einem vordefinierten Format belegt.

9.15 Seitenfeld der Pivot-Tabelle bestimmen

Wenn Sie sich die bisherigen Beispiele zu den Pivot-Tabellenberichten angesehen

haben, wird es Sie eventuell noch interessieren, wie Sie das Seitenfeld (Vorgesetz-

ter) über VBA ermitteln bzw. setzen können. Mit dem Seitenfeld können Sie die

Anzeige der Daten einer Pivot-Tabelle einschränken. Standardmäßig werden alle

Daten einer Pivot-Tabelle angezeigt. In diesem Fall ist der Eintrag Alle im Seitenfeld

aktiv. Mit einem Mausklick auf dieses Seitenfeld können Sie bestimmen, welche

Daten Sie in der Pivot-Tabelle anzeigen möchten. Das Seitenfeld fungiert also als eine

Art Datenfilter.

9.15.1 Seitenfeldeinstellung auslesen

Um herauszufinden, welcher Eintrag gerade in einem Seitenfeld aktiviert ist, setzen

Sie das Makro aus Listing 9.39 ein.

Sub PivotSeitenfeldAuslesen()Dim Pivot1 As PivotTable

Set Pivot1 = Worksheets(1).PivotTables(1)With Pivot1MsgBox "Die Einstellung ist: " & _

Abbildung 9.25 Das AutoFormat »xlClassic3« in der Anwendung

9.15 Seitenfeld der Pivot-Tabelle bestimmen

527

9

.PageFields(1).CurrentPageEnd With

End Sub

Listing 9.39 Den Eintrag im Seitenfeld einer Pivot-Tabelle ermitteln

Über die Eigenschaft PageFields können Sie das Seitenfeld einer Pivot-Tabelle be-

stimmen. Mithilfe der Eigenschaft CurrentPage lesen Sie danach den aktuell einge-

stellten Wert des Seitenfeldes aus.

9.15.2 Seitenfelder initialisieren

Um alle Daten in einer Pivot-Tabelle anzuzeigen, setzen Sie die Eigenschaft Current-

Page auf den Wert "(All)". Sehen Sie sich dazu das Makro aus Listing 9.40 an:

Sub PivotSeitenfeldAlleAnzeigen()Dim Pivot1 As PivotTable

Set Pivot1 = Worksheets(1).PivotTables(1)With Pivot1.PageFields(1).CurrentPage = "(All)"End With

End Sub

Listing 9.40 Seitenfelder in Pivot-Tabellen initialisieren

Abbildung 9.26 Das eingestellte Seitenfeld auslesen


528

9.15.3 Seitenfelder in Abhängigkeit von Zellen setzen

Möchten Sie die Einstellung des Seitenfeldes einer Pivot-Tabelle in Abhängigkeit von

einer bestimmten Zelle auf Ihrem Tabellenblatt festlegen, dann setzen Sie das Makro

aus Listing 9.41 ein:

Sub SeitenfeldSetzen()

Dim Pivot1 As PivotTable

Set Pivot1 = Worksheets(1).PivotTables(1)With Pivot1.PageFields(1).CurrentPage = _ActiveSheet.Range("D1").Value

End With

End Sub

Listing 9.41 Seitenfeld einer Pivot-Tabelle in Abhängigkeit von einer Zelle setzen

Wenn Sie in die eben vorgestellte Lösung noch ein wenig mehr Automatismus ein-

bauen möchten, überwachen Sie im Hintergrund die Zelle D1 und aktualisieren die

Pivot-Tabelle automatisch, wenn sich die Zelle D1 ändert. Dazu gehen Sie wie folgt

vor:


2. Klicken Sie die Tabelle, auf der sich Ihr Pivot-Tabellenbericht befindet, im Projekt-

Explorer doppelt an.

3. Erfassen Sie das Ereignis Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$1" Then SeitenfeldSetzen

End Sub

Listing 9.42 Pivot-Tabellenaktualisierung automatisch starten

Sobald sich die Zelle D1 ändert, wird die Pivot-Tabelle entsprechend aktualisiert und

angezeigt.

In Kapitel 12, »Ereignisse in Excel einsetzen«, lernen Sie mehr über die Möglichkeiten,

die Ereignisse Ihnen bieten.

9.16 Slicer einfügen

529

9

9.16 Slicer einfügen

Ab der Version Excel 2010 gibt es bei Pivot-Tabellen die Möglichkeit, einen sogenann-

ten Slicer hinzuzufügen. Damit lassen sich Pivot-Tabellen besser steuern und flexib-

ler filtern.

Sub SlicerObjectEinfügen()

ActiveWorkbook.SlicerCaches(1).DeleteActiveWorkbook.SlicerCaches.Add(Tabelle3.PivotTables(1), _"Standort").Slicers.Add _Tabelle3, , "Standort", "Regionsfilter", 50, 220, 150, 200

End Sub

Listing 9.43 Pivot-Tabellenaktualisierung automatisch starten

Löschen Sie zunächst einen eventuell bereits bestehenden Slicer, indem Sie die

Methode Delete einsetzen. Wenden Sie die Methode Add an, um der Pivot-Tabelle

einen Slicer hinzuzufügen. Dabei geben Sie bekannt, auf welches Datenfeld sich der

Slicer beziehen soll. Des Weiteren können Sie den Namen des Slicers und seine

Beschriftung festlegen. Die Positionsangaben stehen für oben, links sowie für die

Breite und Höhe des Slicers.

Abbildung 9.27 Der Slicer hilft bei der Bedienung der Pivot-Tabelle.

Inhalt

5

Inhalt

Vorwort .................................................................................................................................................. 27

1 Grundsätzliches zur Programmierung 31

1.1 Wie gehe ich von Anfang an richtig vor? ......................................................... 31

1.1.1 Den Makrorekorder einsetzen ................................................................ 32

1.1.2 Die Aufzeichnung des Makrorekorders ergänzen ................................ 34

1.1.3 Makro ausführen ...................................................................................... 35

1.1.4 Wie erfahre ich mehr über die einzelnen Befehle? .............................. 37

2 Die Arbeits- und Entwicklungsumgebung für die Programmierung in Excel 39

2.1 Die Scroll Area festlegen ...................................................................................... 40

2.2 Tabellenblätter ein- und ausblenden ................................................................ 40

2.3 Neue Module einfügen ........................................................................................ 41

2.4 Die Symbolleiste »Bearbeiten« .......................................................................... 42

2.4.1 Eigenschaften/Methoden anzeigen ...................................................... 43

2.4.2 Konstanten anzeigen ............................................................................... 43

2.4.3 QuickInfo oder Parameterinfo ................................................................ 44

2.4.4 Ganzes Wort ............................................................................................. 44

2.4.5 Einzüge vergrößern bzw. verkleinern .................................................... 44

2.4.6 Haltepunkte setzen .................................................................................. 45

2.4.7 Kommentare im Code hinterlegen ........................................................ 45

2.4.8 Lesezeichen setzen ................................................................................... 46

2.5 Automatische Syntaxprüfung ............................................................................ 46

2.5.1 Befehle in der nächsten Zeile fortsetzen ............................................... 47

2.5.2 Mehrere Befehle in einer Zeile ................................................................ 47

2.5.3 Automatische Anpassung der einzelnen Befehle ................................ 48

2.6 Schnelles Arbeiten über Tastenkombinationen ............................................. 49

2.7 Suchen und Ersetzen ............................................................................................. 50

2.8 Der Objektkatalog ................................................................................................. 51

Inhalt

6

2.9 Die Testumgebung ................................................................................................ 53

2.9.1 Hilfe im Direktfenster .............................................................................. 53

2.9.2 Code Schritt für Schritt durchlaufen lassen .......................................... 54

3 Datentypen, Variablen und Konstanten 55

3.1 Der Einsatz von Variablen ................................................................................... 55

3.1.1 Regeln für die Syntax von Variablen ...................................................... 56

3.1.2 Variablen am Beginn vom Makro deklarieren ...................................... 56

3.1.3 Variablendeklarationen erzwingen ....................................................... 57

3.1.4 Die wichtigsten Variablentypen ............................................................. 57

3.1.5 Noch kürzere Deklaration von Variablen .............................................. 58

3.1.6 Die unterschiedlichen Variablentypen .................................................. 59

3.1.7 Die Objektvariablen ................................................................................. 61

3.2 Die Verwendung von Konstanten ..................................................................... 63

4 Sprachelemente in VBA in der praktischen Anwendung 65

4.1 Verzweigungen ...................................................................................................... 65

4.1.1 Normale Verzweigungen ........................................................................ 66

4.1.2 Verzweigungen in Blöcke einteilen ........................................................ 69

4.1.3 Die Kurzform einer Verzweigung ........................................................... 71

4.2 Die Anweisung »Select Case« für mehr Übersicht ......................................... 71

4.2.1 Indizes in Farben umsetzen .................................................................... 72

4.2.2 Zahlen in Texte umwandeln ................................................................... 74

4.2.3 Mit VBA Formate prüfen ......................................................................... 74

4.2.4 Fensterstatus ermitteln ........................................................................... 75

4.2.5 Makro bei ungültigem Wert beenden ................................................... 76

4.2.6 Excel-Version feststellen ......................................................................... 77

4.3 Schleifen in Excel praxisgerecht einsetzen ...................................................... 78

4.3.1 »For Next«-Schleifen ............................................................................... 78

4.3.2 »For Each Next«-Schleifen ...................................................................... 83

4.3.3 Die Schleife »Do Until Loop« ................................................................... 95

4.3.4 Die Schleife »Do While Loop« ................................................................. 107

Inhalt

7

5 Zellen und Bereiche programmieren 115

5.1 Die Navigation ....................................................................................................... 115

5.1.1 Die übersichtlichere Mehrfachauswahl ................................................ 116

5.1.2 Relative Markierungsformen .................................................................. 117

5.1.3 Mischformen von Markierungen ........................................................... 120

5.1.4 Bestimmte Zellen ansteuern .................................................................. 121

5.1.5 Markierung und Zellen auslesen ............................................................ 124

5.1.6 Verwendete Zellen zählen ...................................................................... 125

5.1.7 Zellen im Verbund .................................................................................... 126

5.1.8 Alle Zellen markieren ............................................................................... 130

5.1.9 Die umliegenden Zellen markieren ........................................................ 130

5.1.10 Auf Zellen zugreifen, ohne darauf zu sitzen ......................................... 131

5.1.11 Aktive Zelle in definiertem Bereich? ...................................................... 132

5.2 Bereiche und Zellen benennen ........................................................................... 134

5.2.1 Bereich benennen .................................................................................... 134

5.2.2 Mehrere Zellen benennen ....................................................................... 135

5.2.3 Konstante als Namen vergeben ............................................................. 137

5.2.4 Bezüge von benannten Bereichen ermitteln ........................................ 138

5.2.5 Namensprüfung einer Zelle .................................................................... 139

5.2.6 Benannte Bereiche markieren ................................................................ 139

5.2.7 Namensprüfung eines Bereichs ............................................................. 140

5.2.8 Alle Namen protokollieren ...................................................................... 141

5.2.9 Namen ändern ......................................................................................... 142

5.2.10 Bezugsadressen von Namen ändern ..................................................... 143

5.2.11 Dynamische Datenbezüge herstellen .................................................... 144

5.2.12 Wo steht der Zellcursor? ......................................................................... 147

5.2.13 Namen verbergen .................................................................................... 148

5.2.14 Namen löschen ........................................................................................ 149

5.2.15 Benutzerdefinierte Listen erstellen ........................................................ 150

5.3 Die Formatierung von Zellen und Bereichen ................................................... 152

5.3.1 Schriftart ermitteln .................................................................................. 152

5.3.2 Schriftart ändern ...................................................................................... 152

5.3.3 Zelleninhalte löschen .............................................................................. 154

5.3.4 Schriftfarbe teilweise ändern ................................................................. 155

5.3.5 Grenzwerte sofort sichtbar ..................................................................... 156

5.3.6 Sonderzeichen einfügen .......................................................................... 156

5.3.7 Chemische Formeln formatieren ........................................................... 159

5.3.8 Rahmenart und -farbe bestimmen ........................................................ 160

5.3.9 Rahmen übertragen ................................................................................. 161

Inhalt

8

5.3.10 Zahlenformate festlegen ........................................................................ 162

5.3.11 Datumsformate festlegen ...................................................................... 163

5.3.12 Formate übertragen ................................................................................. 163

5.3.13 Formate löschen ....................................................................................... 164

5.3.14 Nur Zahlenformate übertragen .............................................................. 164

5.3.15 Die bedingte Formatierung ..................................................................... 166

5.3.16 Sparklines einsetzen ................................................................................ 174

5.3.17 Formate suchen und ersetzen ................................................................ 175

5.4 Formeln einsetzen ................................................................................................. 176

5.4.1 Werte addieren ......................................................................................... 176

5.4.2 Werte subtrahieren ................................................................................. 176

5.4.3 Werte multiplizieren ................................................................................ 177

5.4.4 Werte dividieren ....................................................................................... 178

5.4.5 Werte potenzieren ................................................................................... 178

5.4.6 Komplexere Berechnungen .................................................................... 179

5.5 Funktionen erfassen ............................................................................................. 179

5.5.1 Bedingtes Zählen von Werten (ZÄHLENWENN) ................................... 179

5.5.2 Bedingte Summierung (SUMMEWENN) ............................................... 180

5.5.3 Auskunftssystem erstellen (SVERWEIS) ................................................. 181

5.5.4 Offene-Posten-Verwaltung (WENN) ...................................................... 183

5.5.5 Zahlen summieren (SUMME) ................................................................. 184

5.5.6 Rechnungsfälligkeit ermitteln (EDATUM) ............................................. 185

5.5.7 Minimalwert ermitteln ............................................................................ 185

5.5.8 Auswertungen durchführen ................................................................... 186

5.5.9 Bereiche summieren ................................................................................ 191

5.5.10 Mittelwert über InputBox ermitteln ...................................................... 192

5.5.11 Eurokonvertierung leicht gemacht ........................................................ 193

5.5.12 Obergrenzen festlegen bzw. runden ..................................................... 195

5.5.13 Formeln und Verknüpfungen schnell finden ........................................ 196

5.5.14 Bezüge umsetzen ..................................................................................... 199

5.6 Texte und Zahlen manipulieren ......................................................................... 201

5.6.1 Texte finden .............................................................................................. 201

5.6.2 Daten nach einem Datentransfer bereinigen ....................................... 203

5.6.3 Zahlenwerte richtig interpretieren ........................................................ 204

5.6.4 Zellen bereinigen oder manipulieren .................................................... 204

5.6.5 Telefonliste bearbeiten ........................................................................... 206

5.6.6 Warennummer bilden ............................................................................. 207

5.6.7 Formeln tauschen .................................................................................... 210

5.6.8 Nullen reinpumpen .................................................................................. 211

5.6.9 Nullen entfernen ...................................................................................... 212

Inhalt

9

5.6.10 Konstante Werte löschen und Formeln erhalten ................................. 212

5.6.11 Bereiche säubern ...................................................................................... 214

5.6.12 Leere Zellen löschen ................................................................................. 215

5.6.13 Formeln entfernen ................................................................................... 217

5.6.14 Buchstaben aus Zellen entfernen .......................................................... 217

5.6.15 Dateinamen aus Pfad extrahieren ......................................................... 218

5.6.16 Namen drehen .......................................................................................... 219

5.7 Die Programmierung von Tabellen (Listen bis Excel 2003) .......................... 220

5.7.1 Tabelle einrichten .................................................................................... 220

5.7.2 Eine Tabelle um eine Spalte ergänzen ................................................... 221

5.7.3 Tabelle um eine Zeile ergänzen .............................................................. 222

5.7.4 Tabelle filtern ........................................................................................... 223

5.7.5 Tabellen sortieren .................................................................................... 224

5.7.6 Ergebniszeile hinzufügen ........................................................................ 224

5.7.7 Tabelle entfernen ..................................................................................... 225

5.8 Gültigkeitsprüfung in Excel ................................................................................ 225

5.8.1 Zellen mit Gültigkeitsfunktion erkennen .............................................. 226

5.8.2 Gültigkeitskriterien erstellen .................................................................. 227

5.8.3 Datumsgrenzen festlegen ...................................................................... 229

5.8.4 Keine Arbeit am Sonntag ........................................................................ 230

5.8.5 Gültigkeitstypen ermitteln ..................................................................... 232

5.8.6 Gültigkeitskriterien löschen .................................................................... 233

5.9 Kommentare in Excel ........................................................................................... 233

5.9.1 Kommentare einfügen ............................................................................ 234

5.9.2 Tabelle mit Kommentaren füllen ........................................................... 237

5.9.3 Kommentare schnell finden ................................................................... 237

5.9.4 Kommentare löschen .............................................................................. 238

5.9.5 Kommentare protokollieren ................................................................... 239

5.9.6 Kommentare aus Zellentexten bilden ................................................... 241

5.9.7 Kommentare formatieren ....................................................................... 242

5.9.8 Kommentarfenster anpassen ................................................................. 243

5.10 Sonstige Lösungen mit Zellen und Bereichen ................................................. 243

5.10.1 Die Anzahl der Wörter im markierten Bereich ermitteln .................... 244

5.10.2 Das Auftauchen eines Zeichens im markierten Bereich ermitteln ..... 245

5.10.3 Werte in Bereichen zählen ...................................................................... 246

5.10.4 Die Eckdaten eines Bereichs ermitteln .................................................. 247

5.10.5 Erste freie Zelle in Bereich ermitteln ...................................................... 248

5.10.6 Zellen mit gleichem Inhalt füllen ........................................................... 249

5.10.7 Markierter Bereich wird zum Druckbereich .......................................... 250

5.10.8 Bereiche kopieren ..................................................................................... 251

Inhalt

10

5.10.9 Grafiken in Bereiche integrieren ............................................................ 253

5.10.10 Zellen sperren ........................................................................................... 257

5.10.11 Nicht gesperrte Zellen markieren .......................................................... 258

5.10.12 Mehrzeilige Eingabe in Zellen ................................................................. 258

5.10.13 Daten suchen ............................................................................................ 259

6 Die Programmierung von Spalten und Zeilen 263

6.1 Zeilen und Spalten markieren, zählen und ansteuern .................................. 263

6.1.1 Mehrere Zeilen auf dem Tabellenblatt markieren ............................... 264

6.1.2 Mehrere Spalten auf dem Tabellenblatt markieren ............................ 265

6.1.3 Zeilen und Spalten markieren ................................................................ 265

6.1.4 Markierte Spalten in Markierung zählen .............................................. 266

6.1.5 Letzte freie Zelle in Spalte anspringen .................................................. 267

6.1.6 Eine bestimmte Zelle in der Spalte ansteuern ...................................... 267

6.1.7 Anzahl der benutzten Spalten und Zeilen ermitteln ........................... 267

6.1.8 Zeilen und Spalten im umliegenden Bereich zählen ........................... 269

6.1.9 Erste und letzte Zeile im verwendeten Bereich ermitteln ................... 269

6.1.10 Adresse der letzten verwendeten Zelle einer Zeile ausgeben ............ 271

6.1.11 Markierte Spalten einer Zeile zählen ..................................................... 272

6.1.12 Mehrfachsprünge durchführen .............................................................. 272

6.1.13 Zeilen ab einer bestimmten Position markieren .................................. 274

6.1.14 Letzte Zeile in Tabelle einfärben ............................................................ 275

6.2 Zeilenhöhe und Spaltenbreite einstellen ......................................................... 276

6.3 Zeilen einfügen und löschen ............................................................................... 279

6.3.1 Zeile einfügen ........................................................................................... 280

6.3.2 Mehrere Zeilen einfügen ......................................................................... 280

6.3.3 Zeile löschen ............................................................................................. 282

6.3.4 Mehrere Zeilen löschen ........................................................................... 282

6.3.5 Löschen von Zeilen mit bestimmtem Text ............................................ 283

6.3.6 Wertabhängiges Zeilenlöschen .............................................................. 285

6.3.7 Datumszeilen löschen ............................................................................. 286

6.3.8 Wochenend-Zeilen löschen .................................................................... 287

6.3.9 Zeilen vor dem Startdatum löschen ...................................................... 288

6.3.10 Leerzeilen löschen .................................................................................... 289

6.3.11 Zeilen innerhalb einer Markierung löschen .......................................... 290

6.3.12 Zeilen bis auf Überschrift löschen .......................................................... 292

6.3.13 Doppelte Sätze löschen ........................................................................... 292

Inhalt

11

6.4 Spalten einfügen, löschen und bereinigen ...................................................... 293

6.4.1 Spalte einfügen ........................................................................................ 293

6.4.2 Mehrere Spalten einfügen ...................................................................... 294

6.4.3 Spalte löschen .......................................................................................... 294

6.4.4 Mehrere Spalten löschen ........................................................................ 294

6.4.5 Spalten bereinigen ................................................................................... 294

6.4.6 Spalten prüfen und Zellen bereinigen ................................................... 295

6.5 Zeilen ein- und ausblenden ................................................................................. 296

6.5.1 Leere Zeilen ausblenden .......................................................................... 299

6.5.2 Alle Zellen einblenden ............................................................................. 300

6.5.3 Versteckte Zeilen löschen ....................................................................... 300

6.6 Spalten ein- und ausblenden .............................................................................. 302

6.7 Spalten und Zeilen formatieren ......................................................................... 303

6.8 Daten sortieren ...................................................................................................... 307

6.9 Daten ausdünnen .................................................................................................. 308

6.10 Spalten vergleichen .............................................................................................. 309

6.11 Text auf Spalten verteilen ................................................................................... 310

6.12 Zeilenumbrüche schnell festlegen ..................................................................... 313

6.13 Daten übertragen .................................................................................................. 317

6.14 Wiederholungszeilen und -spalten definieren ............................................... 319

6.15 Auf der Suche nach der richtigen Zeile ............................................................. 319

6.15.1 Die Suche über die Schleife ..................................................................... 320

6.16 Direktsuche bei größeren Datenbeständen .................................................... 323

6.17 Zeilen filtern ........................................................................................................... 325

6.17.1 AutoFilter aktivieren bzw. deaktivieren ................................................. 326

6.17.2 Filterkriterien setzen ................................................................................ 328

6.18 Prüfungen und Auswertungen von Filtern ...................................................... 334

6.18.1 In welchen Spalten ist ein Filterkriterium gesetzt? .............................. 334

6.18.2 Wie viele Zeilen sind gefiltert? ............................................................... 335

6.18.3 Wie viele Zellen sind gefiltert? ............................................................... 337

6.18.4 Den größten Wert ermitteln ................................................................... 340

6.18.5 Die erste bzw. letzte gefilterte Zeile ermitteln ..................................... 340

6.18.6 Wie lauten die Filterkriterien? ................................................................ 341

6.19 Doppelte Werte mit dem Spezialfilter ermitteln ........................................... 342

6.20 Doppelte Werte mit dem Dictionary-Objekt entfernen ............................... 344

Inhalt

12

6.21 Gefilterte Daten transferieren ............................................................................ 347

6.21.1 Gefilterte Zeilen in anderes Tabellenblatt übertragen ........................ 348

6.21.2 Gefilterte Zeilen in eine Textdatei übertragen ..................................... 348

6.22 Filtern von Daten auch bei geschützten Tabellen durchführen .................. 349

6.23 Gliederungen .......................................................................................................... 350

6.23.1 Gliederung einfügen ................................................................................ 350

6.23.2 Gliederung entfernen .............................................................................. 353

6.23.3 Gruppierung anzeigen und ausblenden ................................................ 353

6.23.4 Gliederungssymbole bedienen ............................................................... 354

7 Tabellenblätter programmieren 357

7.1 Tabellenblätter einfügen ..................................................................................... 357

7.2 Tabellen markieren ............................................................................................... 360

7.3 Tabellenblätter benennen ................................................................................... 361

7.3.1 Tabelle nach dem Tagesdatum benennen ............................................ 361

7.3.2 Tabelle nach einem formatierten Datum benennen ........................... 361

7.3.3 Tabelle nach Zelleninhalt benennen ..................................................... 362

7.3.4 Tabelle nach Anwender und Tagesdatum benennen .......................... 363

7.3.5 Tabelle einfügen und benennen kombinieren ..................................... 363

7.4 Tabellenblätter löschen ....................................................................................... 363

7.4.1 Tabelle ohne Rückfrage löschen ............................................................. 364

7.4.2 Alle Tabellen löschen, nur die aktive Tabelle nicht .............................. 365

7.4.3 Alle leeren Tabellen in der Arbeitsmappe löschen ............................... 366

7.5 Tabellenblätter aktivieren ................................................................................... 367

7.6 Tabellenblätter gruppieren ................................................................................. 368

7.6.1 Mehrere Tabellen gruppieren ................................................................. 368

7.6.2 Alle Tabellen gruppieren ......................................................................... 369

7.6.3 Gruppierte Tabellen übertragen ............................................................ 370

7.6.4 Gruppierte Tabellen ermitteln ................................................................ 370

7.7 Tabellenblätter schützen ..................................................................................... 370

7.7.1 Tabellenschutz aufheben ........................................................................ 371

7.7.2 Alle Tabellen einer Arbeitsmappe schützen ......................................... 372

7.7.3 Aufgaben trotz Blattschutz ausführen .................................................. 373

7.7.4 Weitere Schutzfunktionen ab Excel 2002 ............................................. 374

Inhalt

13

7.8 Tabellenblätter ein- und ausblenden ................................................................ 375

7.8.1 Tabellenblätter sicher ausblenden ........................................................ 376

7.8.2 Tabellen je nach Status ein- oder ausblenden ...................................... 376

7.8.3 Alle Tabellenblätter anzeigen ................................................................. 377

7.8.4 Alle Tabellen außer der aktiven Tabelle ausblenden ........................... 377

7.9 Kopf- und Fußzeilen einrichten .......................................................................... 378

7.9.1 Fußzeile mit Anwendernamen ............................................................... 378

7.9.2 Fußzeile mit Pfad ..................................................................................... 378

7.9.3 Kopfzeile mit Datums- und Zeitangabe ................................................ 379

7.9.4 Fußzeile mit Dokumenteigenschaften füllen ....................................... 380

7.9.5 Kopfzeile mit Logo einrichten ................................................................. 381

7.9.6 Mehrzeilige Fußzeile anlegen ................................................................. 384

7.10 Druckbereiche festlegen ...................................................................................... 387

7.11 Tabellenblätter drucken ...................................................................................... 388

7.11.1 Mehrere Kopien drucken ......................................................................... 388

7.11.2 Markierte Bereiche drucken .................................................................... 389

7.11.3 Mehrere Tabellenblätter drucken .......................................................... 389

7.11.4 Den integrierten Drucken-Dialog aufrufen ........................................... 389

7.11.5 Wie viele Druckseiten enthält die Tabelle? ........................................... 390

7.12 Tabellenblätter kopieren, verschieben und einfügen ................................... 391

7.12.1 Tabellenblatt kopieren ............................................................................ 391

7.12.2 Tabellenblatt verschieben ....................................................................... 393

7.12.3 Tabellenblatt ohne Formeln und Verknüpfungen übertragen ........... 394

7.12.4 Tabelle in andere Arbeitsmappe kopieren ............................................ 394

7.12.5 Tabellenblatt als E-Mail versenden ........................................................ 395

7.13 Tabellenblätter sortieren ..................................................................................... 396

7.14 Tabellen suchen ..................................................................................................... 397

7.15 Tabellen sichern ..................................................................................................... 398

7.15.1 Verwendete Zeilen in andere Tabelle transferieren ............................ 399

7.15.2 Tabelle kopieren und umbenennen ....................................................... 400

7.15.3 Texte übertragen ..................................................................................... 400

7.15.4 Tabelle in Textdatei schreiben ................................................................ 402

7.16 Tabellenblätter konsolidieren ............................................................................ 403

7.16.1 Tabellenblätter summieren (Festwert) ................................................. 404

7.16.2 Tabellenblätter summieren (Verknüpfung) .......................................... 405

7.16.3 Tabellen in einer Tabelle zusammenführen ......................................... 406

7.17 Sonstige Lösungen mit Tabellen ........................................................................ 407

7.17.1 Registerlaschen ein- und ausblenden .................................................... 408

Inhalt

14

7.17.2 Registerlaschen einfärben ...................................................................... 409

7.17.3 Diashow starten ....................................................................................... 410

7.17.4 Tabellenblätter verlinken ........................................................................ 411

7.17.5 Bilder in Tabellen einfügen ..................................................................... 412

7.17.6 Hintergrundbild einfügen ....................................................................... 415

7.17.7 Tabellenansicht anpassen ....................................................................... 415

8 Die Programmierung von Arbeitsmappen 417

8.1 Arbeitsmappen speichern und sichern ............................................................. 417

8.1.1 Arbeitsmappe doppelt sichern ............................................................... 419

8.1.2 Bei Bedarf Verzeichnis anlegen .............................................................. 420

8.1.3 Den integrierten »Speichern unter«-Dialog aufrufen ......................... 420

8.1.4 Den Pfad der geladenen Arbeitsmappe ermitteln ............................... 421

8.1.5 Den Zustand der Arbeitsmappe ermitteln ............................................ 421

8.1.6 Arbeitsmappe ohne Makros speichern .................................................. 422

8.1.7 Arbeitsmappe als Textdatei speichern .................................................. 423

8.1.8 Arbeitsmappe aktuell speichern ............................................................ 424

8.2 Arbeitsmappen öffnen ......................................................................................... 425

8.2.1 Den integrierten »Öffnen«-Dialog aufrufen ......................................... 427

8.2.2 Die Liste der geöffneten Arbeitsmappen beeinflussen ....................... 428

8.2.3 Mehrere Arbeitsmappen öffnen ............................................................ 429

8.2.4 Alle geöffneten Arbeitsmappen ermitteln ............................................ 430

8.2.5 Die aktuellste Arbeitsmappe öffnen ...................................................... 431

8.2.6 Arbeitsmappe ohne Makrodialog starten ............................................. 432

8.3 Arbeitsmappen schließen .................................................................................... 434

8.3.1 Arbeitsmappe schließen – Änderungen akzeptieren ........................... 434

8.3.2 Arbeitsmappe schließen – Änderungen verwerfen ............................. 435

8.3.3 Arbeitsmappe bei Vernachlässigung schließen ................................... 436

8.3.4 Alle Arbeitsmappen bis auf eine schließen ........................................... 437

8.4 Arbeitsmappe löschen .......................................................................................... 438

8.4.1 Arbeitsmappe nach Verfallsdatum löschen .......................................... 438

8.5 Arbeitsmappe anlegen ......................................................................................... 439

8.5.1 Mappe mit Wochen-Tabellen anlegen .................................................. 440

8.5.2 Arbeitsmappen aus Zellen erstellen ...................................................... 441

Inhalt

15

8.6 Arbeitsmappen drucken ...................................................................................... 441

8.6.1 Nur bestimmte Tabellen drucken .......................................................... 442

8.7 Arbeitsmappen und Dokumenteigenschaften ............................................... 443

8.7.1 Dokumenteigenschaftsnamen abfragen .............................................. 444

8.7.2 Dokumenteigenschaftsinhalte ermitteln ............................................. 444

8.7.3 Letztes Speicherdatum abfragen ........................................................... 446

8.7.4 Erstelldatum herausfinden und manipulieren ..................................... 447

8.7.5 Den Autor einer Arbeitsmappe ermitteln ............................................. 448

8.7.6 Zusätzliche Dokumenteigenschaft anlegen ......................................... 449

8.7.7 Zugriffsdaten einer Arbeitsmappe ermitteln ....................................... 450

8.8 Arbeitsmappen und Verknüpfungen ................................................................ 452

8.8.1 Verknüpfungen in Hyperlinks umwandeln ........................................... 452

8.8.2 Verknüpfungen in Arbeitsmappe ermitteln ......................................... 454

8.8.3 Verknüpfungen aus der Arbeitsmappe entfernen ............................... 456

8.8.4 Verknüpfungen aktualisieren ................................................................. 458

8.8.5 Alle Dateien aus einem Ordner dokumentieren ................................... 458

8.9 Arbeitsmappe durchsuchen ................................................................................ 462

8.10 Arbeitsmappen miteinander vergleichen ........................................................ 464

8.11 Arbeitsmappenübersicht erstellen .................................................................... 466

8.12 Arbeitsmappen säubern ...................................................................................... 468

8.12.1 Alle Grafikobjekte aus einer Arbeitsmappe entfernen ........................ 468

8.12.2 Alle Kommentare einer Arbeitsmappe löschen .................................... 469

8.12.3 Benutzerdefinierte Listen löschen und erstellen .................................. 470

8.12.4 Formatvorlagen löschen und erstellen .................................................. 472

8.12.5 Alle benutzerdefinierten Namen einer Mappe löschen ...................... 474

9 Diagramme und Pivot-Tabellenberichte programmieren 477

9.1 Diagramme erstellen ............................................................................................ 478

9.1.1 Kosten im Säulendiagramm darstellen ................................................. 479

9.1.2 Das Zylinderdiagramm auf Knopfdruck ................................................ 481

9.1.3 Das Balkendiagramm aus Daten in der Umgebung ............................ 482

9.1.4 Tagesumsätze im Liniendiagramm anzeigen ....................................... 483

9.1.5 Tagesgenaue Formatierung im Punktdiagramm ................................. 486

9.1.6 Mittelwert in Diagramm bilden ............................................................. 488

Inhalt

16

9.2 Diagramme löschen .............................................................................................. 490

9.2.1 Diagrammblätter aus Arbeitsmappe entfernen .................................. 491

9.2.2 Eingebettete Diagramme aus Arbeitsmappe löschen ......................... 491

9.3 Diagramme identifizieren ................................................................................... 492

9.3.1 Diagramme umbenennen ....................................................................... 492

9.4 Datenbasis und Diagramm im Wechsel ........................................................... 493

9.5 Datenbeschriftungen hinzufügen und auslesen ............................................ 494

9.5.1 Datenbeschriftung aus Zellen verwenden ............................................ 496

9.5.2 Daten aus Diagrammen auslesen .......................................................... 498

9.6 Diagramme positionieren und skalieren .......................................................... 501

9.6.1 Die Höhe und Breite eines Diagramms festlegen ................................ 501

9.6.2 Diagramme platzieren ............................................................................. 502

9.6.3 Diagramme skalieren .............................................................................. 505

9.7 Diagramme als Grafiken speichern ................................................................... 507

9.8 Diagramme formatieren ...................................................................................... 509

9.8.1 Gewinn und Verlust in einem Säulendiagramm präsentieren ........... 509

9.8.2 Eine Ist-Darstellung in einem Säulendiagramm darstellen ................ 512

9.8.3 Legenden formatieren ............................................................................. 513

9.8.4 Zusätzliche Labels hinzufügen ............................................................... 514

9.9 Dynamische Diagramme erzeugen ................................................................... 515

9.10 Pivot-Tabellenberichte erstellen ........................................................................ 517

9.11 Pivot-Tabellen aktualisieren ............................................................................... 520

9.11.1 Eine einzelne Pivot-Tabelle aktualisieren .............................................. 520

9.11.2 Mehrere Pivot-Tabellen auf einem Tabellenblatt aktualisieren ......... 521

9.11.3 Alle Pivot-Tabellen in Arbeitsmappe aktualisieren .............................. 521

9.12 Pivot-Tabellen dynamisch erweitern ................................................................ 522

9.13 Pivot-Tabellen sortieren ...................................................................................... 524

9.14 Pivot-Tabellen formatieren ................................................................................. 525

9.15 Seitenfeld der Pivot-Tabelle bestimmen .......................................................... 526

9.15.1 Seitenfeldeinstellung auslesen .............................................................. 526

9.15.2 Seitenfelder initialisieren ........................................................................ 527

9.15.3 Seitenfelder in Abhängigkeit von Zellen setzen ................................... 528

9.16 Slicer einfügen ....................................................................................................... 529

Inhalt

17

10 Benutzerdefinierte Funktionen und Funktionsmakros 531

10.1 Benutzerdefinierte Funktionen .......................................................................... 531

10.1.1 Aktive Arbeitsmappe ermitteln .............................................................. 532

10.1.2 Aktives Tabellenblatt ermitteln .............................................................. 533

10.1.3 Ist eine Tabelle leer? ................................................................................ 535

10.1.4 Ist eine Tabelle geschützt? ...................................................................... 535

10.1.5 Befinden sich Daten in einer bestimmten Spalte? ............................... 536

10.1.6 Den letzten Wert einer Spalte ermitteln ............................................... 536

10.1.7 Den letzten Wert einer Zeile ermitteln .................................................. 537

10.1.8 Den aktiven Bearbeiter identifizieren .................................................... 538

10.1.9 Funktion zum Umsetzen von Noten ...................................................... 539

10.1.10 Rangfolge als Text ausgeben .................................................................. 540

10.1.11 Enthält eine bestimmte Zelle eine Formel? .......................................... 541

10.1.12 Enthält eine bestimmte Zelle ein Gültigkeitskriterium? ..................... 542

10.1.13 Enthält eine Zelle einen Kommentar? ................................................... 543

10.1.14 Ist eine bestimmte Zelle verbunden? .................................................... 544

10.1.15 Störende Zeichen eliminieren ................................................................. 544

10.1.16 Initialen aus Namen erstellen ................................................................ 545

10.1.17 Formeln und Verknüpfungen in Festwerte umwandeln ..................... 546

10.1.18 Nur Zellen mit Fettdruck addieren ......................................................... 546

10.1.19 Nur Zellen mit roter Schriftfarbe addieren ........................................... 547

10.1.20 Mit Uhrzeiten rechnen ............................................................................ 548

10.1.21 Erweitertes Runden durchführen ........................................................... 550

10.1.22 Hintergrundfarben auslesen .................................................................. 551

10.1.23 Zahlenformate ermitteln ........................................................................ 552

10.1.24 Schnelles Umrechnen von Geschwindigkeiten .................................... 553

10.1.25 Extremwerte berechnen ......................................................................... 554

10.1.26 Erste Ziffer in einer Zelle ermitteln ........................................................ 555

10.1.27 Buchstaben aus Zellen entfernen .......................................................... 557

10.1.28 Anzahl der Ziffern einer Zelle ermitteln ................................................ 558

10.1.29 Römische Zahlen in arabische umwandeln .......................................... 559

10.1.30 Befindet sich eine Grafik in einer Tabelle? ............................................ 560

10.1.31 Nur Zahlenwerte zulassen ...................................................................... 561

10.2 Modulare Funktionen schreiben ........................................................................ 563

10.2.1 Dateien in einem Verzeichnis zählen .................................................... 563

10.2.2 Prüfen, ob eine bestimmte Datei existiert ............................................ 564

10.2.3 Prüfen, ob eine bestimmte Datei geöffnet ist ...................................... 565

10.2.4 Prüfen, ob eine Datei gerade bearbeitet wird ....................................... 566

Inhalt

18

10.2.5 Prüfen, ob ein bestimmter Name in der Arbeitsmappe

verwendet wird ........................................................................................ 567

10.2.6 Prüfen, ob ein Add-In eingebunden ist .................................................. 568

10.2.7 Dokumenteigenschaften einer Arbeitsmappe ermitteln .................... 571

10.2.8 Bedingte Formatierung mit mehr als drei Farben ................................ 573

10.2.9 Wochenende ermitteln ........................................................................... 575

10.2.10 Den letzten Tag im Monat ermitteln ..................................................... 576

10.2.11 Das Osterdatum für ein bestimmtes Jahr ermitteln ............................ 577

10.2.12 Ermittlung der Kalenderwoche nach DIN EN 28601/ISO 8601 ............ 580

10.2.13 Den letzten Wert einer Spalte ermitteln ............................................... 581

10.2.14 Grafikelemente in einem definierten Bereich löschen ........................ 582

10.2.15 Funktionen verfügbar machen ............................................................... 584

10.2.16 Speichern der Funktionen in der persönlichen Arbeitsmappe ........... 584

10.2.17 Speichern der Funktionen in einem Add-In .......................................... 586

10.2.18 Ein Add-In einbinden ............................................................................... 587

11 Programmierung mit Windows-API-Aufrufen 589

11.1 Ermittlung der Laufwerke am PC ....................................................................... 590

11.2 Bedienung des CD-ROM-Laufwerks .................................................................. 592

11.3 Bildschirmauflösung ermitteln .......................................................................... 593

11.4 Ist ein externes Programm gestartet? .............................................................. 594

11.5 Wie lange läuft ein externes Programm? ........................................................ 594

11.6 Excel schlafen schicken ........................................................................................ 597

11.7 Verzeichnisse über eine API erstellen bzw. löschen ....................................... 597

11.8 Netzlaufwerk verbinden ...................................................................................... 598

11.9 Verzeichnisbaum anzeigen und auswerten .................................................... 599

11.10 Windows-Info-Bildschirm anzeigen .................................................................. 601

11.11 Windows-Verzeichnis ermitteln ........................................................................ 602

11.12 Standardverzeichnis temporär ändern ............................................................. 603

11.13 Anwendernamen ausgeben ................................................................................ 604

11.14 Computernamen ermitteln ................................................................................. 604

11.15 Texte mit API-Funktionen konvertieren ........................................................... 605

11.16 Cursorposition in Pixel angeben ........................................................................ 605

Inhalt

19

11.17 Zwischenablage löschen ...................................................................................... 607

11.18 Sounds per API-Funktion ausgeben .................................................................. 607

11.19 PC piepsen lassen .................................................................................................. 608

11.20 Dateien suchen ...................................................................................................... 608

11.21 Tasten abfangen .................................................................................................... 609

12 Ereignisse in Excel einsetzen 611

12.1 Ereignisse für die Arbeitsmappe ........................................................................ 612

12.1.1 Allgemeine Vorgehensweise beim Erstellen von Arbeitsmappen-

Ereignissen ................................................................................................ 612

12.1.2 Aktivieren des richtigen Tabellenblatts ................................................. 614

12.1.3 Einen Bereich begrenzen ......................................................................... 617

12.1.4 Alle ausgeblendeten Tabellen bei Bedarf einblenden ......................... 618

12.1.5 Den letzten Bearbeiter einer Arbeitsmappe ermitteln ........................ 618

12.1.6 Makro am Monatsende starten .............................................................. 619

12.1.7 Symbolleisten ein- und ausblenden ...................................................... 620

12.1.8 Die Datumszelle in einer Tabelle finden ............................................... 622

12.1.9 Das Schließen der Arbeitsmappe bedingt verhindern ......................... 622

12.1.10 Arbeitsmappe ohne Rückfrage schließen .............................................. 623

12.1.11 Letztes Bearbeitungsdatum festhalten ................................................. 624

12.1.12 Datenbestand vor dem Speichern sortieren ......................................... 624

12.1.13 Die Dauer der Bearbeitung einer Mappe festhalten ............................ 625

12.1.14 Schnell Tabellen gleicher Struktur vergleichen .................................... 627

12.1.15 Die Lösung für das sparsame Drucken .................................................. 630

12.1.16 Einfügen von Blättern verhindern .......................................................... 631

12.1.17 Druckstatistik führen ............................................................................... 631

12.1.18 Die wichtigsten Ereignisse für die Arbeitsmappe im Überblick ......... 632

12.2 Ereignisse für das Tabellenblatt ......................................................................... 634

12.2.1 Allgemeine Vorgehensweise bei der Einstellung von

Tabellenereignissen ................................................................................. 634

12.2.2 Passworteingabe beim Aktivieren einer Tabelle .................................. 635

12.2.3 Starten einer UserForm ........................................................................... 636

12.2.4 Beim Verlassen eines Tabellenblatts Daten neu berechnen ............... 637

12.2.5 Schiffe versenken spielen ........................................................................ 637

12.2.6 Vergleich von zwei Spalten ..................................................................... 639

12.2.7 Änderungen in einer Tabelle sichtbar machen ..................................... 641

Inhalt

20

12.2.8 Unterschiedliche Makros je nach Zellenwert starten .......................... 642

12.2.9 Automatisch die Spaltenbreite einstellen ............................................. 644

12.2.10 Mehrere Bereiche überwachen .............................................................. 645

12.2.11 Eingabe auf mehreren Tabellen gleichzeitig durchführen .................. 646

12.2.12 Änderungen dokumentieren .................................................................. 647

12.2.13 Wareneingänge dokumentieren ............................................................ 647

12.2.14 Artikelliste bereinigen ............................................................................. 648

12.2.15 Nur einmalige Eingabe zulassen ............................................................ 650

12.2.16 Tabellenname aus Zelle herleiten .......................................................... 651

12.2.17 Automatische Suche von Datensätzen ................................................. 652

12.2.18 Alternative Formularsteuerung .............................................................. 653

12.2.19 Automatisches Runden nach Eingabe ................................................... 654

12.2.20 Eine Blaupause erzeugen ........................................................................ 655

12.2.21 Formelcheck nach Eingabe ..................................................................... 656

12.2.22 Nettowert automatisch ausweisen ....................................................... 656

12.2.23 Eine selbst rechnende Zelle .................................................................... 657

12.2.24 Die Eingabe von Dubletten verhindern ................................................. 658

12.2.25 Die Lesbarkeit der aktuellen Auswahl erhöhen .................................... 659

12.2.26 Mehr Übersichtlichkeit in Tabellen herstellen ...................................... 661

12.2.27 Aktuelle Zelle übertragen ........................................................................ 663

12.2.28 Eingabe verhindern .................................................................................. 664

12.2.29 Markierung überwachen ......................................................................... 665

12.2.30 Mausklicks überwachen .......................................................................... 667

12.2.31 Die Aktualisierung von Pivot-Tabellen überwachen ............................ 668

12.2.32 Direkte Verbindung zu einer anderen Tabelle erstellen ...................... 669

12.2.33 Alle Ereignisse für Tabellen im Überblick .............................................. 670

12.3 Reaktion auf Tastendruck ................................................................................... 670

12.3.1 Tastenkombinationen ein- und ausschalten ........................................ 672

12.3.2 Zeilenumbrüche zurücksetzen ............................................................... 674

12.3.3 Texte einfügen ......................................................................................... 675

12.3.4 Speichern per Tastenkombination verhindern ..................................... 676

12.3.5 Blattsperre ohne Blattschutz erstellen .................................................. 677

12.4 Zeitsteuerung in Excel .......................................................................................... 678

12.4.1 Regelmäßig die Uhrzeit anzeigen .......................................................... 679

12.4.2 Die Zeit läuft ... ......................................................................................... 680

12.4.3 Regelmäßige Kontrolle, ob eine Arbeitsmappe verfügbar ist ............. 681

12.4.4 Zellen blinken lassen ............................................................................... 683

Inhalt

21

13 VBE-Programmierung 685

13.1 Die VBE-Bibliothek einbinden ............................................................................ 686

13.2 Die VBE-Bibliothek deaktivieren ........................................................................ 687

13.3 Weitere Bibliotheken einbinden ........................................................................ 688

13.3.1 Objektbibliotheken deaktivieren ............................................................ 689

13.3.2 Informationen zu Objektbibliotheken ausgeben ................................. 690

13.4 VBE-Editor aufrufen .............................................................................................. 691

13.5 Die VBE ein- und ausschalten ............................................................................. 691

13.6 Neue Module einfügen ........................................................................................ 692

13.7 Einzelne Module löschen ..................................................................................... 693

13.8 Makros löschen ...................................................................................................... 693

13.8.1 Einzelnes Makro löschen ......................................................................... 693

13.8.2 Alle Makros aus einer Arbeitsmappe entfernen ................................... 694

13.9 Module mit Makros bestücken ........................................................................... 695

13.9.1 Makro zeilenweise in ein Modul übertragen ........................................ 696

13.9.2 Makros aus einer Textdatei in ein Modul überführen ......................... 697

13.9.3 Alle Mappen aus einem Verzeichnis mit Code versorgen ................... 699

13.10 Export von VBA-Modulen in Textdateien ........................................................ 700

13.11 Identifikation von Komponenten ...................................................................... 702

13.12 Ein bestimmtes Makro auskommentieren ...................................................... 703

13.13 Das Direktfenster löschen ................................................................................... 703

14 Dialoge, Meldungen und UserForms programmieren 705

14.1 Die »Msgbox«-Meldung ...................................................................................... 705

14.1.1 Welche Schaltfläche wurde angeklickt? ................................................ 708

14.1.2 Löschrückfrage ......................................................................................... 708

14.1.3 Informationen anzeigen .......................................................................... 709

14.1.4 Ist eine bestimmte Arbeitsmappe vorhanden? .................................... 710

14.2 Textfelder für Meldungen einsetzen ................................................................ 711

14.3 Die »InputBox«-Eingabemaske .......................................................................... 712

14.3.1 Mehrwertsteuer errechnen ..................................................................... 714

Inhalt

22

14.3.2 Mehrere Eingaben erfassen .................................................................... 715

14.3.3 Bereiche über eine Input-Box markieren ............................................... 715

14.3.4 Funktionen über eine Input-Box eingeben ........................................... 716

14.4 Integrierte Dialoge einsetzen ............................................................................. 717

14.4.1 Den »Öffnen«-Dialog aufrufen ............................................................... 718

14.4.2 Mehrere Dateien zum Öffnen heranziehen .......................................... 719

14.4.3 Der Dialog »Öffnen« mit automatischer Passworteingabe ................ 720

14.4.4 Die Farbpalette aufrufen ......................................................................... 721

14.4.5 Den Dialog »Drucken« aufrufen ............................................................. 721

14.4.6 Den Dialog für den Blattschutz anzeigen ............................................. 722

14.4.7 Den »Suchen«-Dialog aufrufen .............................................................. 723

14.4.8 Den Dialog »Optionen« aufrufen ........................................................... 723

14.4.9 Der Dialog »E-Mail versenden« .............................................................. 724

14.4.10 Der Ordner-Auswahl-Dialog ................................................................... 725

14.4.11 Weitere integrierte Dialoge in Excel ...................................................... 726

14.5 Eigene Dialoge entwerfen und programmieren ............................................. 727

14.5.1 Eine UserForm zeichnen .......................................................................... 727

14.5.2 Der Entwurf einer automatischen dialoggesteuerten

Rechnungserfassung ............................................................................... 731

14.5.3 Der erste Aufruf der neuen UserForm ................................................... 733

14.5.4 Reihenfolge der Elemente festlegen ...................................................... 733

14.5.5 UserForms mit Makros bestücken ......................................................... 734

14.5.6 Die UserForm beenden ............................................................................ 737

14.5.7 Alle Textfelder initialisieren .................................................................... 737

14.5.8 Inhalte von Textfeldern markieren ........................................................ 738

14.5.9 UserForm mit Tabellendaten füllen ....................................................... 738

14.5.10 Textfelder mit Farben belegen ............................................................... 742

14.5.11 Textfelder prüfen ..................................................................................... 743

14.5.12 Bezeichnungsfelder dynamisch halten ................................................. 745

14.5.13 Textfelder ein- und ausblenden ............................................................. 746

14.5.14 Passwortabfrage über UserForm ........................................................... 747

14.5.15 Die Ausmaße von UserForms ändern .................................................... 750

14.5.16 Die Hintergrundfarbe der UserForm festlegen .................................... 752

14.5.17 Listenfelder in UserForms programmieren ........................................... 754

14.5.18 Listenfelder und Textfelder kombinieren .............................................. 756

14.5.19 Listenfeld leeren ....................................................................................... 757

14.5.20 Listeneinträge markieren ........................................................................ 757

14.5.21 Listenfeld ohne Leerzeilen füllen ........................................................... 759

14.5.22 Mehrspaltige Listenfelder programmieren ........................................... 760

14.5.23 Dropdown-Felder einsetzen ................................................................... 762

Inhalt

23

14.5.24 Dropdown-Felder ohne Duplikate füllen ............................................... 764

14.5.25 Dropdown-Felder dynamisch füllen ...................................................... 766

14.5.26 Auswahl aus einem Dropdown-Feld erzwingen .................................. 766

14.5.27 Der Datums-Bug in Dropdown-Feldern ................................................. 767

14.5.28 Abwesenheitszeiten über UserForm erfassen ...................................... 767

14.5.29 Multiseiten erstellen ............................................................................... 770

14.5.30 Multiseiten im Praxiseinsatz .................................................................. 772

14.5.31 Fortschrittsbalken für UserForms programmieren .............................. 775

14.5.32 Bilder in UserForms anzeigen ................................................................. 776

15 Steuerelemente in Tabellen programmieren 779

15.1 Schaltflächen einsetzen ....................................................................................... 780

15.1.1 Eine Schaltfläche automatisch in eine Tabelle einfügen .................... 780

15.1.2 Schaltfläche mit Bild einfügen ............................................................... 781

15.1.3 Makro automatisch starten .................................................................... 783

15.1.4 Welche Schaltfläche wurde angeklickt? ................................................ 783

15.1.5 Die mobile Schaltfläche ........................................................................... 784

15.1.6 Umschaltflächen programmieren ......................................................... 784

15.2 Textboxen programmieren ................................................................................. 788

15.2.1 Textfelder auslesen .................................................................................. 788

15.2.2 Textfelder mit Zelleninhalten füllen ...................................................... 789

15.2.3 Textfelder je nach Eingabe sperren ....................................................... 790

15.3 Dropdown-Felder programmieren .................................................................... 791

15.3.1 Dropdown-Felder füllen .......................................................................... 791

15.3.2 Dropdown-Felder sortieren ..................................................................... 793

15.3.3 Dropdown-Felder leeren ......................................................................... 795

15.3.4 Dropdown-Felder automatisch erstellen .............................................. 795

15.4 Listenfelder programmieren ............................................................................... 796

15.4.1 Listenfelder füllen .................................................................................... 796

15.4.2 Listenfelder bereinigen ............................................................................ 797

15.5 Drehfelder programmieren ................................................................................. 798

15.5.1 Preiskalkulation schrittweise durchführen ........................................... 798

15.5.2 Objekte schrittweise drehen ................................................................... 799

15.6 Bildlaufleisten einsetzen ..................................................................................... 801

15.7 Schnelles Umschalten über Optionsfelder ....................................................... 802

Inhalt

24

15.8 Kontrollkästchen programmieren ..................................................................... 803

15.8.1 Mehrfachauswahl mit Kontrollkästchen .............................................. 803

15.8.2 Kontrollkästchen synchronisieren ......................................................... 804

15.8.3 Kontrollkästchen initialisieren ............................................................... 805

15.9 Steuerelemente identifizieren ........................................................................... 806

16 Kontextmenüs und Ribbon-Programmierung 809

16.1 Die Programmierung von Kontextmenüs ........................................................ 809

16.1.1 Kontextmenüs deaktivieren ................................................................... 810

16.1.2 Das Zellen-Kontextmenü erweitern ...................................................... 811

16.1.3 Kontextmenü aufbauen (dreistufig) ...................................................... 814

16.1.4 Kontextmenü zurücksetzen .................................................................... 815

16.2 Die Ribbon-Programmierung .............................................................................. 815

16.2.1 Der »Custom UI Editor« ........................................................................... 816

16.2.2 Weitere wichtige Quellen und Hilfen .................................................... 818

16.2.3 Ribbon mit Schaltflächen erstellen ........................................................ 819

16.2.4 Ribbon mit ComboBox erstellen ............................................................ 821

16.2.5 Ribbon mit bereits verfügbaren Funktionen bestücken ...................... 823

17 Excel und sein Umfeld 827

17.1 Textdateien im Zugriff von Excel ....................................................................... 827

17.1.1 Textdateien speichern ............................................................................. 827

17.1.2 Textdateien mit Trennzeichen einlesen ................................................ 829

17.1.3 Textdateien fixer Länge öffnen .............................................................. 830

17.1.4 Mehrere Textdateien in eine Tabelle einfügen .................................... 831

17.2 Excel und Word im Duett ..................................................................................... 833

17.2.1 Eine Excel-Tabelle unverknüpft nach Word übertragen ..................... 834

17.2.2 Eine Excel-Tabelle verknüpft nach Word übertragen .......................... 837

17.3 Excel im Zusammenspiel mit Access ................................................................. 839

17.3.1 Die Aufgabe .............................................................................................. 839

17.3.2 Die Realisierung ....................................................................................... 840

17.3.3 Datenbankabfrage durchführen ............................................................ 840

17.3.4 Datenbank-Update durchführen ........................................................... 842

17.4 Excel und Outlook ................................................................................................. 844

17.4.1 Tabelle als E-Mail versenden .................................................................. 845

Inhalt

25

17.4.2 Einen Excel-Bereich als E-Mail versenden ............................................. 846

17.4.3 Mehrere Arbeitsmappen per E-Mail versenden ................................... 847

17.4.4 E-Mail bei Änderung senden ................................................................... 850

17.4.5 Kontakte austauschen ............................................................................. 851

18 Fehlerbehandlung, Tuning und der Schutz von VBA-Projekten 855

18.1 Kleinere Fehler beheben ...................................................................................... 855

18.1.1 Stimmt die Syntax? .................................................................................. 855

18.1.2 Ist die Variablendefinition gegeben? ..................................................... 856

18.1.3 Objekt vorhanden? .................................................................................. 856

18.1.4 Methode, Eigenschaft verfügbar? .......................................................... 857

18.2 Schwerwiegendere Fehler ................................................................................... 857

18.2.1 Fehler im Vorfeld erkennen und reagieren ........................................... 857

18.2.2 Fehler ignorieren ...................................................................................... 858

18.2.3 Fehlerursache ermitteln .......................................................................... 858

18.2.4 Die Funktion »IsError« ............................................................................. 859

18.3 Das Add-In »MZ-Tools« ........................................................................................ 860

18.3.1 Zeilennummern automatisch einfügen ................................................ 861

18.3.2 Eine Fehlerbehandlung mit den MZ-Tools hinzufügen ....................... 862

18.4 Laufzeiten verkürzen ............................................................................................ 863

18.4.1 Befehle zusammenfassen ....................................................................... 864

18.4.2 Variablen und Konstanten einsetzen .................................................... 866

18.4.3 Berechnung und Bildschirmaktualisierung ausschalten ..................... 867

18.4.4 Integrierte Tabellenfunktionen anwenden ........................................... 868

18.4.5 Weitere Faktoren zur Geschwindigkeitssteigerung ............................. 868

18.5 VBA-Projekte schützen ......................................................................................... 869

18.5.1 VBA-Quellcode schützen ......................................................................... 869

18.5.2 Add-In erstellen ........................................................................................ 870

18.5.3 Ein Add-In einbinden ............................................................................... 871

19 FAQ zur Programmierung mit Excel 873

19.1 Bildschirmaktualisierung abschalten ............................................................... 873

19.2 Kopfzeile mit komplettem Pfad ......................................................................... 874

Inhalt

26

19.3 Die letzte Zelle in einer Tabelle finden ............................................................. 875

19.4 Texte suchen und ersetzen .................................................................................. 876

19.5 Nicht benötigte Zeilen löschen .......................................................................... 877

19.6 Seitenwechsel festlegen ...................................................................................... 878

19.7 Druckbereich festlegen bzw. aufheben ............................................................ 879

19.8 Rechnen mit bedingten Formaten ..................................................................... 880

19.9 Doppelte Sätze ermitteln .................................................................................... 881

19.10 Zeilenumbrüche und Zellenverbunde aufheben ............................................ 882

19.11 Zellenverknüpfungen finden .............................................................................. 883

19.12 Datumsdifferenzen errechnen ........................................................................... 884

19.13 Warnmeldungen deaktivieren ........................................................................... 885

19.14 Datentransport nach Word ................................................................................. 886

19.15 Laufwerk und Pfad der aktiven Arbeitsmappe anzeigen ............................. 889

19.16 Drag & Drop ein- und ausschalten .................................................................... 889

19.17 Bilder umbenennen .............................................................................................. 890

19.18 Dateiinformationen auslesen ............................................................................. 891

Anhang: Nützliche und interessante Makros 895

Tabellenblätter alphabetisch sortieren ........................................................................ 895

Alle Tabellen einer Arbeitsmappe schützen ................................................................ 896

Alle Diagramme als Bilder exportieren ........................................................................ 897

Alle ausgeblendeten Tabellen einblenden .................................................................. 899

Buchstaben und Sonderzeichen aus dem Zellinhalt löschen ................................... 900

In allen Blättern Formeln mit Werten überschreiben ............................................... 901

Alle Formeln schützen und verstecken ......................................................................... 902

Alle Tabellenblätter in einzelnen Arbeitsmappen speichern ................................... 903

Alle Zellen mit Formeln oder Verweisen einfärben ................................................... 905

Alle versteckten oder gefilterten Zeilen löschen ........................................................ 905

Index ....................................................................................................................................................... 907

Index

907

Index

A

Activate ............... 186, 367, 398, 410, 422, 464, 712

ActiveCell .................................................... 71, 125, 273

ActiveSheet .................................. 385, 479, 501, 502

ActiveWorkbook .................................................... 421

Add ......... 46, 221, 222, 357, 359, 412, 422, 431, 440,

441, 461, 462, 473, 489, 516, 692, 720,

795, 812, 814, 836, 850

AddColorScale ......................................................... 172

AddComment ....................................... 234, 236, 237

AddCustomList ............................................... 151, 471

AddDataBar .............................................................. 173

AddFromFile .......................................................... 689

AddFromGuid ........................................................ 687

AddIconSetCondition .......................................... 173

Add-Ins ............................................................ 570, 870

AddItem ................................ 754, 759, 763, 769, 792

AddLabel ................................................................... 514

Address .......... 63, 124, 127, 138, 170, 202, 247, 272,

582, 876

AddShape ................................................................. 253

AdvancedFilter ....................................................... 343

Aktivierungsreihenfolge festlegen ................ 734

AllowMultiSelect .................................................. 720

AlternativeText ...................................................... 255

And ............................................................................... 66

Änderungen dokumentieren .......................... 647

Anwendername

ausgeben ............................................................. 604

ermitteln .............................................................. 363

ApplyDataLabels ......................................... 490, 495

Arbeitsmappe

abfragen ............................................................... 532

anlegen ................................................................ 439

ansprechen ............................................................ 61

ausdünnen ............................................................ 83

drucken ................................................................. 441

durchsuchen ................................................ 85, 462

löschen ................................................................. 439

öffnen .................................................................... 425

säubern ................................................................ 468

schließen ............................................ 434, 437, 622

sichern ................................................................... 419

speichern .............................................................. 417

Arbeitsmappenname abfragen ......................... 62

Arbeitsmappenübersicht erstellen ............... 466

Areas .................................................................. 116, 266

Array .......................................................................... 368

Asc ....................................................................... 218, 557

AsktoUpdateLinks ................................................ 427

Auskommentierung aufheben ......................... 46

Ausrichtung festlegen ......................................... 314

Auswertungen durchführen ............................ 186

Author ....................................................................... 448

AutoFilter ............................................... 326, 329, 333

aktivieren ............................................................ 326

ausschalten ........................................................ 328

AutoFilterMode ............................................ 326, 328

AutoFilter-Pfeile ausblenden ........................... 350

AutoFit .................................................... 277, 279, 854

AutoFormat ............................................................. 526

AutoOutline ............................................................. 351

AutoSize ............................................................ 235, 781

Average ...................................................................... 191

B

BackColor ......................................................... 752, 781

BCC ............................................................................. 849

Bearbeiter ermitteln ............................................ 538

Bedingte Formate anpassen ............................. 170

Bedingte Formatierung

einfügen ............................................................... 166

löschen ................................................................. 170

Bedingte Summierung anwenden ................ 180

Beep ............................................................................ 682

Befehlskette trennen ............................................. 47

BeginGroup .............................................................. 812

Benutzerdefinierte Liste erstellen ................. 150

Bereich

anpassen ............................................................. 120

ansprechen .......................................................... 116

auslesen ............................................................... 247

begrenzen ............................................................. 617

benennen .............................................................. 134

einfügen ................................................................ 251

kopieren ................................................................ 251

markieren ............................................. 116, 117, 139

prüfen .................................................................... 132

säubern ................................................................. 214

summieren ........................................................... 191

überwachen ........................................................ 645

verbinden ............................................................ 266

Index

908

Bereich (Forts.)

verschieben ......................................................... 253

zusammenfassen ............................................... 116

Bereichsgrenzen abstecken ............................... 271

Bereichsnamen ermitteln ................................... 93

Bereichsprüfung ausführen ............................. 148

Beschriftung

anzeigen .............................................................. 490

hinzufügen .......................................................... 514

Beträge umrechnen ............................................. 193

Bezüge

auslesen ................................................................ 138

umsetzen ............................................................. 199

Bezugsadressen anpassen ................................. 143

Bibliothek

deaktivieren ....................................................... 687

einbinden ............................................................ 688

Bilder

anzeigen ............................................................... 776

einfügen ............................................................... 412

umbenennen ..................................................... 890

Bildlaufleiste programmieren ........................ 801

Bildschirmaktualisierung

ausschalten .................................................. 93, 485

einschalten ................................................... 93, 485

Bildschirmauflösung ermitteln ...................... 593

Bildschirmmeldung anzeigen ........................ 705

Blattschutz

aufheben ............................................................. 350

entfernen ............................................................... 82

Block auskommentieren ...................................... 46

Body ........................................................................... 849

Bold ................................ 109, 153, 497, 513, 655, 880

Boolean ....................................................................... 57

BottomMargin ....................................................... 385

Buchstaben entfernen ................................ 217, 557

BuiltIn ........................................................................ 472

BuiltinDocumentProperties .................. 385, 444,

624, 752

BusinessAddressCity ........................................... 852

BusinessAddressCountry .................................. 853

BusinessAddressPostalCode ............................. 852

BusinessAddressState ......................................... 853

BusinessAddressStreet ....................................... 852

Byte ............................................................................... 57

C

Caller .......................................................................... 783

Caption .......................................... 746, 754, 804, 812

CDate ............................................. 260, 334, 439, 764

CDbl ............................................................................ 334

CD-ROM-Laufwerk bedienen ........................... 592

Ceiling ....................................................................... 196

CellDragAndDrop ................................................ 890

CellFormat ................................................................ 175

Cells ............. 101, 109, 120, 127, 240, 277, 291, 394,

769, 878

ChangeLink ............................................................. 457

Characters ........................................................ 155, 242

ChartObject ................................................... 492, 502

Charts ........................................................................ 491

ChartTitle ........................................................ 481, 498

ChartType .................................... 478, 479, 482, 489

ChDir ........................................ 53, 366, 417, 420, 832

ChDrive ...................................................... 53, 366, 417

CheckBox_Click ..................................................... 747

CheckSpelling ......................................................... 373

Chr .............................................................. 157, 316, 673

Clean ........................................................................... 215

Clear ....................................... 709, 757, 769, 795, 797

ClearComments .................................................... 238

ClearContents ....................................... 295, 313, 659

ClearFormats ................................................. 164, 170

ClearOutline ............................................................ 353

CLng .................................................................. 562, 885

Close ............................ 349, 424, 434, 437, 842, 904

Color ............................................................................ 153

ColorIndex .......... 88, 153, 154, 202, 276, 409, 463,

513, 640, 880

Column ............................................................ 273, 649

ColumnCount ......................................................... 761

ColumnHead ........................................................... 761

ColumnLevels ........................................................ 354

Columns ............................. 120, 264, 302, 324, 640

ColumnWidth ................................................ 276, 277

ColumnWidths ....................................................... 761

ComboBox_Change .................. 764, 767, 792, 793

CommandButton .................................................. 781

CommandButton_MouseMove ..................... 783

Comment ..................................... 234, 240, 469, 543

Computernamen ermitteln ............................ 604

Connection .............................................................. 841

Controls .................................................................... 746

ConvertFormula .......................................... 200, 813

Copy ........... 98, 106, 251, 348, 349, 370, 392, 400,

404, 407, 724, 846, 902

CopyPicture ............................................................. 412

Count ........ 81, 91, 105, 120, 125, 127, 137, 247, 266,

267, 271, 293, 305, 336, 358, 367, 369,

404, 440, 504, 761, 766, 832

CountA ............... 125, 268, 290, 300, 468, 535, 536

Index

909

CountIf ..................................................................... 659

CountOfLines ......................................................... 697

CreateItem ............................................................... 852

CreateObject .......................................... 835, 848, 852

Currency ..................................................................... 57

CurrentPage ............................................................ 527

CurrentRegion .................. 130, 146, 269, 309, 336,

387, 482

Cursorposition abfragen ................................... 605

CustomListCount .................................................. 471

CutCopyMode .................... 105, 253, 392, 394, 485

D

Databodyrange ...................................................... 221

DataLabel ................................................................. 490

DataLabels ............................................................... 497

Date ......... 42, 57, 100, 163, 234, 260, 361, 448, 619

Dateien

suchen .................................................................. 608

zählen .................................................................... 563

Dateiexistenz prüfen .......................................... 564

Dateiinformationen abfragen ......................... 891

Dateinamen extrahieren ................................... 218

Daten

ausdünnen ......................................................... 308

bereinigen ........................................................... 203

sortieren ..................................................... 307, 624

suchen ................................................................... 259

übertragen ........................................................... 317

Datenbalken einfügen ......................................... 172

Datenbereich markieren ..................................... 115

Datenbeschriftungen hinzufügen ................ 494

Datenbezüge herstellen ..................................... 144

Datentyp prüfen .................................................... 279

DateSerial ........................................................ 576, 579

Datum

abfragen ................................................................. 42

prüfen ...................................................................... 68

umwandeln ........................................................ 260

Datumsformat festlegen .................................... 163

Datumsgrenzen festlegen ................................. 229

Datumstest durchführen .................................. 100

Datumswerte filtern ............................................ 333

Datumszeilen löschen ....................................... 286

Datumszelle finden .............................................. 622

Debug.Print ........................ 258, 335, 492, 772, 808

Delete .............. 83, 98, 100, 103, 149, 172, 217, 229,

239, 279, 284, 286, 290, 291, 292, 294, 301,

363, 367, 450, 469, 474, 484, 490, 492,

516, 584, 695, 815, 877, 906

DeleteLines ............................................................. 694

Diagramm

einfügen .............................................................. 489

erstellen ................................................................ 478

exportieren ........................................................ 508

formatieren ....................................................... 509

identifizieren ...................................................... 492

löschen ................................................................ 490

positionieren ...................................................... 501

skalieren ..................................................... 501, 505

umbenennen ...................................................... 492

Diagrammtyp festlegen ..................................... 479

Diagrammüberschrift festlegen ..................... 481

Dialogs ...................................................................... 389

Dir ...................................................................... 564, 833

Direktbereich schreiben .................................... 258

Direktfenster anzeigen .......................................... 53

Direktsuche ausführen ....................................... 323

Display ..................................................................... 850

DisplayAlerts .............. 83, 364, 434, 435, 491, 670,

725, 886

DisplayGridlines ................................................... 786

DisplayOutline ....................................................... 354

DisplayRecentFiles ............................................... 428

DisplayStatusBar ........................................... 337, 435

DisplayWorkbookTabs ...................................... 408

Do Loop .................................................................... 260

Do Until Loop ................................ 95, 100, 104, 432

Do While ................................................................... 245

Do While Loop ....................................... 107, 110, 622

Dokumenteigenschaft

abfragen ..................................................... 385, 443

anlegen ................................................................. 449

auslesen ................................................................ 571

löschen ................................................................ 450

Doppelte Werte entfernen ................................ 344

Double .......................................................................... 57

Drag & Drop einschalten .................................. 889

Drehfeld programmieren .................................. 798

Dropdown-Feld ...................................................... 762

erstellen ................................................................ 795

füllen ...................................................................... 791

leeren .................................................................... 795

programmieren ................................................. 791

sortieren ............................................................... 793

Druckbereich festlegen ..................... 251, 387, 879

Druckseiten zählen ............................................. 390

Druckstatistik führen ........................................... 631

Dubletten verhindern ......................................... 658

Index

910

Duplikate

ermitteln ..................................................... 342, 881

löschen .................................................................. 292

E

Eigenschaft, Definition .......................................... 51

Eigenschaften anzeigen ....................................... 43

Eingabe verhindern ............................................ 664

Einzüge

vergrößern ............................................................ 44

verkleinern ............................................................ 44

Einzugsebene abfragen ...................................... 297

E-Mail versenden .................................................. 847

Email1Address ....................................................... 853

EnableAutoFilter .................................................. 350

EnableEvents ................................................. 629, 662

End .............. 267, 270, 272, 274, 275, 404, 407, 832

EntireColumn ......................................................... 276

EntireRow .................... 103, 274, 275, 276, 286, 877

Entwicklungsumgebung ...................................... 39

neue Module einfügen ..................................... 41

Scroll Area festlegen ......................................... 40

Suchen und Ersetzen ......................................... 50

Symbolleiste »Bearbeiten« ............................. 42

Tabellenblätter ein- und ausblenden ........ 40

Environ ............................................................ 363, 434

Ereignis, Definition ................................................ 52

Ereignissteuerung ausschalten ...................... 629

Ergebniszeile hinzufügen .................................. 224

Erl ................................................................................ 863

Err ................................................................................ 567

Erstelldatum anpassen ...................................... 448

Excel 4.0-Makro ausführen ............................... 391

Excel beenden ............................................. 436, 680

Excel-Version ermitteln ....................................... 77

ExecuteExcel4Macro ........................................... 391

Exit Do ...................................................................... 464

Exit For ..................................................... 321, 322, 341

Exit Function ................................................. 556, 566

Exit Sub ............. 139, 140, 144, 249, 364, 398, 629,

714, 744

Export ....................................................................... 898

Extremwert

berechnen ............................................................ 554

ermitteln ............................................................... 90

F

Farbverlauf einfügen ............................................ 171

Fensterstatus ermitteln ........................................ 75

FileDateTime ........................................................... 431

FileDialog ........................................................ 720, 726

FileName .................................................................. 382

Files ................................................................... 462, 700

FileSystemObject ........................ 451, 461, 572, 849

Filter .......................................................................... 508

Filterkriterien abfragen ....................................... 341

Filters ................................................................ 335, 720

Find .................................. 122, 324, 463, 741, 775, 844

FindNext ......................................................... 463, 464

FindWindow ........................................................... 594

FirstName ................................................................ 852

Floor ........................................................................... 196

Font ..................................................................... 153, 157

FontStyle ................................................................... 153

FooterMargin ......................................................... 385

For Each ......... 63, 154, 155, 194, 212, 231, 239, 240,

242, 245, 246, 279, 738

For Each Next ...... 83, 141, 295, 366, 376, 377, 378,

397, 409, 437, 465, 472, 474, 487, 497,

500, 503, 510, 521, 524, 681, 788, 807,

884, 896, 897, 898, 902, 904

For Next ........ 78, 81, 160, 165, 200, 285, 290, 291,

297, 309, 316, 321, 335, 341, 349, 367,

369, 397, 404, 405, 406, 455, 468, 504,

506, 508, 673, 703, 715, 720, 776, 878, 906

Format ........................................................ 91, 361, 425

prüfen ..................................................................... 74

übertragen ........................................................... 163

FormatConditions ................................................. 167

Formatierung entfernen ................................... 164

FormatNumberLocal ............................................ 165

Formatvorlagen löschen ................................... 472

Formeln

abfragen ............................................................. 200

entfernen .............................................................. 217

ersetzen ................................................................ 546

finden .................................................................... 196

formatieren ......................................................... 159

schützen .............................................................. 902

tauschen .............................................................. 210

verbergen ........................................................... 903

verstecken .......................................................... 902

Formelzellen suchen ............................................. 86

Formula ................................................... 180, 191, 457

FormulaLocal ................................................... 179, 717

FullName .......................... 62, 94, 385, 421, 533, 889

Funktion

einfügen ............................................................... 192

erfassen ................................................................. 179

verlassen .............................................................. 556

Index

911

Fußzeile

einrichten ............................................................ 378

erstellen .................................................................. 93

G

Geschwindigkeit umrechnen .......................... 553

GetDriveType .......................................................... 591

GetExitCodeProcess ............................................ 594

GetFolder ....................................................... 467, 700

GetObject .................................................................. 835

GetOpenFileName ....................................... 413, 434

GetOpenFilename ........................................ 429, 719

Gliederung

einfügen .............................................................. 350

entfernen ............................................................. 353

Gliederungssymbole bedienen ....................... 354

GoTo .................................................................. 139, 140

Grafik

einfügen ............................................................... 253

löschen ................................................................. 468

positionieren ...................................................... 254

prüfen ................................................................... 560

Grafikelement löschen ....................................... 582

Grenzwerte ermitteln ......................................... 156

Großbuchstaben, umwandeln in ..................... 76

Group ......................................................................... 352

Gruppierung

anzeigen ............................................................... 353

einfügen ............................................................... 352

Gültigkeit prüfen ................................................... 542

Gültigkeitsfunktion auslesen .......................... 226

Gültigkeitskriterien

einstellen .............................................................. 227

löschen .................................................................. 233

Gültigkeitstypen ermitteln ............................... 232

H

Haltepunkt setzen .................................................. 45

HasDataLabels ....................................................... 497

HasFormula ....................... 200, 203, 542, 656, 813

HasLegend ....................................................... 485, 513

HasTitle ............................................................ 481, 497

Height .............................................. 414, 501, 781, 795

Hidden ................................... 97, 297, 300, 301, 804

Hide ............................................................................ 737

Hintergrundbild einfügen ................................ 415

Hintergrundfarben auslesen ............................. 551

HorizontalAlignment .................................. 157, 314

HPageBreaks ........................................................... 879

Hyperlink einfügen .............................................. 412

I

IconSets ..................................................................... 173

IF ................................................................................... 555

If .......................................................... 65, 128, 266, 397

IgnoreBlank ............................................................. 229

IIf .................................................................................... 71

Import ...................................................................... 700

IndentLevel ............................................................. 297

Index .......................................................................... 534

Initialen bilden ...................................................... 545

InputBox ................................ 193, 252, 324, 392, 712

Insert ......................... 97, 98, 279, 280, 413, 414, 787

InsertAfter ............................................................... 836

InsertLines ............................................................... 703

Installed .......................................................... 569, 570

InStr ....... 111, 142, 202, 239, 245, 246, 284, 311, 457

Instr ................................................. 206, 218, 219, 462

InStrRev ..................................................................... 218

Int ................................................................................ 776

Integer .......................................................................... 57

Interior .......................................... 197, 202, 656, 884

Intersect ..... 133, 148, 336, 638, 641, 646, 648, 667

IsArray ......................................................................... 69

IsDate .................................................. 68, 69, 286, 745

IsEmpty .............................................. 68, 69, 270, 457

IsError ................................................................. 69, 859

IsNull ............................................................................ 69

IsNumeric ....... 67, 69, 84, 279, 405, 547, 657, 744

IsObject ....................................................................... 69

Italic ................................................................... 153, 880

Item ............................................................................. 142

K

Kalenderwoche ermitteln ................................ 580

Kill ............................................................................... 439

Kleinbuchstaben, umwandeln in ...................... 77

Kommentar

einfügen ................................................ 45, 234, 241

entfernen ............................................................. 238

erfassen .................................................................. 45

finden .................................................................... 237

formatieren ........................................................ 242

löschen ................................................................ 469

protokollieren .................................................... 239

prüfen ................................................................... 543

Kommentarfenster anpassen .................. 235, 243

Index

912

Konstanten

anzeigen ................................................................. 43

deklarieren ............................................................ 63

Kontakt anlegen .................................................... 853

Kontextmenü

deaktivieren ....................................................... 810

zurücksetzen ...................................................... 815

Kontrollkästchen

abfragen ............................................................... 747

initialisieren ...................................................... 805

programmieren ................................................ 803

synchronisieren ................................................ 804

Kopfzeile erstellen .................................................. 94

Kopie entfernen ...................................................... 99

Kopiermodus ausschalten ................................ 105

Kopierränder ausschalten ................................. 392

Kurzform, Variablendeklaration ...................... 58

L

Länge ermitteln .................................................... 209

Large .......................................................................... 190

LastName .................................................................. 852

Laufwerk wechseln .............................................. 366

Laufwerke ermitteln ........................................... 590

Laufzeit verkürzen ............................................... 863

LBound ...................................................................... 162

LCase ............................................................................. 77

Leerstellen entfernen .......................................... 245

Leerzeilen löschen ............................................... 289

Left ...... 100, 111, 208, 209, 217, 219, 255, 258, 279,

311, 321, 406, 501, 781, 784, 787, 795

LeftFooter ................................................................. 385

Legende formatieren ............................................ 513

Len .......... 111, 113, 125, 209, 212, 219, 406, 543, 556,

565, 738, 745, 749

Lesezeichen setzen ................................................. 46

Like .................................................................... 559, 884

LinkedCell ................................................................. 793

LinkSources .................................................... 453, 457

ListBox_Click ................................................. 761, 797

ListColumns ............................................................ 221

ListCount ......................................................... 759, 794

Liste

hinzufügen .......................................................... 471

sortieren ............................................................... 102

Listenfeld

bereinigen ............................................................ 797

füllen ..................................................................... 796

leeren ..................................................................... 757

programmieren ................................................ 796

ListFillRange ........................................................... 793

ListIndex ................................................................... 757

Listobject ................................................................... 221

ListRows .................................................................... 222

LoadPicture ..................................................... 777, 781

Location .......................................................... 502, 504

Locked ..................................................... 258, 761, 903

Long ............................................................................... 57

Löschrückfrage einholen .................................. 708

M

MacroType ............................................................... 695

MajorUnit ................................................................ 507

Makro

abarbeiten ............................................................. 54

beenden ................................................................. 139

importieren ....................................................... 696

löschen ....................................................... 693, 694

starten ......................................................... 619, 783

verlassen .............................................................. 144

zurücksetzen ........................................................ 54

Makrorekorder .......................................................... 32

Makrosicherheit ...................................................... 39

MarkerBackgroundColorIndex ....................... 487

MarkerForegroundColorIndex ....................... 487

Markierung

abfragen .............................................................. 266

auslesen ........................................................ 124, 125

entfernen ............................................................. 253

löschen ................................................................ 290

überwachen ........................................................ 665

MatchRequired ...................................................... 766

Max .................................. 90, 191, 554, 774, 777, 800

Maximum ................................................................ 428

MaximumScale ..................................................... 507

Mehrfachauswahl vornehmen ........................ 116

Mehrfachsprünge durchführen ...................... 272

Meldung ausgeben ......................................... 44, 139

MergeArea ....................................................... 126, 127

MergeCells .................... 128, 129, 130, 315, 544, 883

Methode

anzeigen ................................................................ 43

Definition ............................................................... 51

Mid ............................ 100, 111, 218, 219, 311, 557, 558

Min ............................................. 90, 191, 555, 774, 777

Minimalwert ermitteln ....................................... 185

MinimumScale ...................................................... 507

Mittelwert berechnen ................................. 191, 192

MkDir ........................................................................ 420

Mod .................................................................. 303, 305

Index

913

Modul

einfügen .............................................................. 692

löschen ................................................................. 693

Month ....................................................................... 764

MouseMove ............................................................. 784

Move ........................................................ 393, 397, 896

MsgBox ....................................................................... 44

Msgbox .................................................... 105, 139, 301

Multiseiten erstellen .......................................... 770

MZ-Tools .................................................................. 860

N

Name ....... 93, 94, 153, 221, 255, 359, 361, 370, 448,

473, 474, 479, 490, 508

abarbeiten ............................................................ 141

ändern ................................................................... 142

anpassen .............................................................. 142

ausblenden .......................................................... 149

drehen ................................................................... 219

einblenden ........................................................... 149

entfernen ............................................................. 149

löschen ......................................................... 149, 474

protokollieren ..................................................... 141

überprüfen ......................................................... 140

Namensanzeige unterdrücken ........................ 148

Namenskonventionen einhalten ................... 134

Namensprüfung durchführen ......................... 139

Names ................................................................ 142, 475

Netzlaufwerk verbinden ................................... 598

Next ........................................................................... 368

Noten vergeben ..................................................... 539

Nothing ..................................................................... 661

Notizen auslesen ................................................... 237

Now ............................................................ 60, 361, 434

Nullen

auffüllen ................................................................. 88

entfernen ............................................................. 212

ergänzen ............................................................... 211

Number ..................................................................... 567

NumberFormatLocal ........................................... 553

O

Obergrenzen festlegen ....................................... 195

Object ................................................................... 58, 231

Objekt, Definition .................................................... 51

Objektkatalog ............................................................ 51

aufrufen ................................................................. 52

Öffnen-Dialog anzeigen ..................................... 413

OffSet ........................................................ 147, 253, 658

Offset .......................... 71, 97, 100, 105, 131, 142, 267

OLEObjects ............................................................. 807

On Error ........................ 232, 335, 361, 364, 368, 857

On Error Resume Next ............................... 118, 146

OnAction ............................................... 700, 780, 812

OnKey .............................................................. 670, 673

OnTime ........................................................... 678, 684

Open ...................................... 349, 425, 720, 829, 842

OpenProcess .................................................. 594, 596

OpenText ............................................... 830, 831, 832

Option Explicit ....................................................... 856

Optionsfeld ............................................................ 802

Or .................................................................. 66, 291, 321

Orientation ............................................................... 315

Osterdatum berechnen ....................................... 577

OutLineFont ............................................................. 153

P

PageFields ................................................................. 527

PageSetup ..................................... 250, 319, 378, 385

Parameterinfo anzeigen ...................................... 44

Parent ....................................................................... 240

PasswordChar ................................................ 747, 748

Paste ................................................ 105, 106, 412, 484

PasteSpecial ........................ 163, 251, 394, 832, 902

Path ................................................................... 421, 889

Pattern ....................................................................... 276

Pause generieren .................................................. 410

Pfad ermitteln ......................................................... 421

Picture ........................................................................ 781

PivotSelect ............................................................... 524

Pivot-Tabelle

aktualisieren ...................................................... 520

erweitern ............................................................. 522

formatieren ......................................................... 525

Pivot-Tabellenbericht erstellen ....................... 517

Points ......................................................................... 497

Position ermitteln ................................................. 112

Print .................................................................. 349, 424

PrintArea ......................................................... 250, 387

PrintOut .................................................. 388, 441, 442

PrintPreview ........................................................... 379

PrintTitleColumns ................................................ 319

PrintTitleRows ........................................................ 319

ProcCountLines ........................................... 694, 703

ProcStartLine ................................................ 694, 703

Protect ...................... 61, 82, 350, 371, 651, 897, 903

ProtectContents .................................................... 536

Public ........................................................................... 59

Index

914

Q

Quit ......................................................... 436, 680, 681

R

Rahmen übertragen .............................................. 161

Rahmenart festlegen .......................................... 160

Rahmenfarbe bestimmen ................................ 160

Range ................................................... 62, 118, 154, 263

Rangfolge ausgeben ............................................ 540

Rechnungsfälligkeit berechnen ...................... 185

ReDim ................................................... 360, 369, 406

Redundanzen ermitteln ..................................... 343

RefersToRange ...................................................... 568

RefreshTable .......................................................... 520

Registerlaschen

ausblenden ......................................................... 408

einblenden .......................................................... 408

einfärben ............................................................. 409

entfärben ............................................................ 409

Remove ........................................................... 688, 693

RemoveItem ............................................................ 757

Rept ............................................................................. 385

Reset ........................................................................... 815

Resize ......................................................................... 120

RGB ..................................................................... 752, 781

Right .................................................. 99, 113, 209, 475

RightFooter .............................................................. 385

RightHeader ............................................................ 382

RightHeaderPicture ............................................. 382

Rnd ............................................................ 186, 754, 784

Rotation .................................................................. 800

Round ............................................. 187, 196, 550, 655

RoundDown ............................................................ 196

RoundUp .................................................................. 196

Row ..................................................................... 271, 273

RowHeight ...................................................... 276, 277

RowLevels ................................................................. 354

Rows ........... 120, 125, 263, 267, 280, 293, 297, 309

RowSource ...................................................... 761, 766

S

Save ............................................................................. 853

SaveAs ............................................ 418, 425, 724, 904

Saved .......................................................................... 422

Schaltflächen einsetzen ..................................... 780

SchemeColor ........................................................... 254

Schleife verlassen ................................. 321, 341, 464

Schleifen einsetzen ................................................ 78

Schreibschutz abfragen ....................................... 535

Schriftart

anpassen .............................................................. 152

ermitteln ............................................................... 152

Schriftfarbe

anpassen .............................................................. 154

ermitteln ................................................................ 66

Schriftschnitt abfragen ........................................ 66

Schrittweite festlegen ......................................... 291

ScreenUpdating ................................... 105, 485, 629

ScrollArea ................................................................. 677

ScrollBar_Change ................................................. 801

ScrollColumn .......................................................... 629

ScrollRow ................................................................. 629

Seitenansicht anzeigen ...................................... 379

Seitenfeld initialisieren ....................................... 527

Seitenrand einstellen .......................................... 385

Seitenwechsel festlegen ..................................... 878

Select ................................................ 101, 132, 274, 360

Select Case ................. 71, 75, 332, 556, 558, 572, 777

SelectAll .......................................................... 469, 505

Selected ...................................................................... 757

SelectedItems ......................................................... 726

SelectedSheets ....................................................... 370

Selection ........................................... 63, 124, 160, 170

SelLength ........................................................ 738, 749

SelStart ............................................................. 738, 749

SendMail ................................................................. 846

Series ................................................................. 497, 510

SeriesCollection ..................................................... 484

Set ................................... 126, 128, 266, 295, 393, 770

SetBackgroundPicture ......................................... 415

SetFocus ........................................................... 738, 739

SetSourceData ............................................... 480, 517

Shadow ....................................................................... 153

Shape ........................................................................ 468

ShapeRange ............................................................. 414

Shape-Typen bestimmen ................................... 255

Sheets ........................................................ 46, 378, 410

SheetsInNewWorkbook ..................................... 439

Show ................................................ 720, 724, 726, 746

ShowError ................................................................ 229

ShowInput ............................................................... 229

ShowLevels .............................................................. 354

ShrinkToFit .............................................................. 883

Sicherheitsrückfrage ausschalten .................. 364

Single ........................................................................... 58

Size ............................................................................... 153

Slicer einfügen ....................................................... 529

Small .......................................................................... 188

Sonderzeichen einfügen ..................................... 156

Index

915

Sort .................................................................... 102, 307

Sound ausgeben ................................................... 607

SourceData ............................................................... 519

Spalte

ausblenden ......................................................... 302

bereinigen ........................................................... 294

einfügen ............................................................... 293

ergänzen .............................................................. 221

löschen ................................................................. 294

markieren ............................................................ 265

vergleichen ............................................... 309, 639

zählen ................................................................... 268

Spaltenbreite

anpassen .............................................................. 276

einstellen .............................................................. 761

Spaltenüberschrift anzeigen ............................ 761

SparklineGroups ..................................................... 175

Sparklines ................................................................. 174

SpecialCells ............. 87, 121, 197, 211, 233, 237, 247,

336, 348, 367, 581

SpinButton1_Change .......................................... 773

Standardfarben ausgeben ................................. 198

Standardverzeichnis ändern ........................... 603

Static ......................................................................... 660

StatusBar .......................................................... 339, 435

Statusleiste ansprechen ............................ 337, 435

Steuerelemente identifizieren ....................... 806

Strikethrough .......................................................... 153

String ............................................................................ 58

Subject ...................................................................... 849

Subscript ................................................................... 154

Substitute ............................. 142, 205, 206, 211, 425

SubTotal ........................................................... 337, 339

Sum .............................................................................. 191

SumIf ........................................................................... 181

Superscript ............................................................... 154

Symbole verwenden ............................................. 173

Syntaxprüfung vornehmen ........................ 46, 47

T

Tab .............................................................................. 409

Tabelle

abfragen ............................................................... 533

benennen ............................................................. 359

einblenden ........................................................... 618

einfügen ............................................................... 412

einrichten ........................................................... 220

entfernen ............................................................. 225

filtern ..................................................................... 223

löschen .................................................................... 83

Tabelle (Forts.)

markieren ........................................................... 360

schützen .................................................................. 61

sichern .................................................................. 398

sortieren ............................................................... 224

suchen .................................................................. 397

verknüpfen ........................................................... 411

verschieben ......................................................... 393

zählen .................................................................. 440

Tabellenansicht anpassen .................................. 415

Tabellenblatt

aktivieren ................................................... 367, 614

anlegen .................................................................. 431

ausblenden .......................................................... 375

benennen ............................................................... 80

drucken ................................................................ 388

durchsuchen ......................................................... 85

einblenden ........................................................... 375

einfügen ......................................................... 79, 357

exportieren ......................................................... 394

gruppieren .......................................................... 368

konsolidieren .................................................... 403

kopieren ...................................................... 370, 391

löschen ................................................................. 363

schützen ........................................................ 81, 370

sortieren ............................................................... 396

summieren ......................................................... 404

überwachen ........................................................ 510

verlinken ............................................................... 411

zählen .................................................... 81, 358, 367

Tabellennamen vergeben .................................. 361

Tabellenschutz aufheben ............................. 61, 371

TableRange1 ............................................................ 525

Tagesdatum

abrufen .................................................................. 60

ermitteln ..................................................... 163, 363

Tasten abfangen ................................................... 609

Tastenkombination ............................................... 49

einschalten ......................................................... 672

Testumgebung kennenlernen ........................... 53

Text ................................ 279, 481, 514, 543, 700, 756

aufsplitten ........................................................... 109

einfügen ............................................................... 675

finden .................................................................... 201

konvertieren ...................................................... 605

übertragen ......................................................... 400

umbrechen .................................................. 259, 315

TextAlign .................................................................. 776

TextBox_Change .................................................. 799

Textbox_Exit .......................................................... 744

Textbox1_Enter .................................................... 743

Index

916

Textboxen programmieren .............................. 788

TextColumn ............................................................ 762

Textdatei

öffnen .......................................................... 349, 830

schließen ............................................................. 349

speichern .............................................................. 827

Textfeld

prüfen .................................................................... 743

sperren .................................................................. 761

TextFrame ....................................................... 242, 514

ThisWorkbook ........................................................ 421

Time ............................................................................ 234

Timer ......................................................................... 436

TimeValue ............................................. 410, 435, 679

Title ................................................................... 720, 726

To ................................................................................... 81

Top .................................. 255, 501, 781, 784, 787, 795

Transpose ....................................................... 345, 500

Trim ............................................................................ 245

Type ................................................................... 255, 542

TypeName ...................................................... 503, 629

TypeOf ....................................................................... 772

TypeParagraph ...................................................... 836

TypeText .................................................................. 836

TypName ................................................................. 808

U

UBound .................................................. 162, 457, 500

UCase ................................................................. 76, 560

Uhrzeit abrufen ...................................................... 60

Umlaute ersetzen .................................................... 87

Umsätze kommentieren ..................................... 118

Umschaltflächen programmieren ................. 784

Underline ............................................... 109, 154, 401

Unikate ermitteln ................................................. 345

Union ....................................................... 116, 266, 646

Unprotect .......................... 61, 82, 350, 371, 651, 897

Update ...................................................................... 844

UsedRange ............ 86, 128, 130, 137, 154, 258, 267,

271, 290, 349, 392, 407, 465,

506, 535, 761

UserForm

beenden ................................................................ 737

starten .................................................................. 636

UserForm_Click ...................................................... 751

UserForm_Initialize ................. 746, 747, 760, 769

Userform_Initialize ............................................. 764

UserForm_Resize ................................................. 750

UserName ................................................................ 234

V

Val ................................................................................. 78

Validation ................................................................ 542

Value ............................ 457, 737, 746, 772, 804, 808

Variablen

deklarieren ............................................................ 56

einsetzen ................................................................. 55

Konstanten ........................................................... 63

Objektvariablen ................................................... 61

öffentliche ............................................................. 59

private ................................................................... 60

statische ................................................................. 59

Typen ....................................................................... 57

Variablendeklaration

erzwingen ............................................................... 57

vornehmen ........................................................... 56

Variablenregeln ....................................................... 56

Variant ........................................................................ 58

VBA-Projekte schützen ...................................... 869

VBA-Quellcode schützen .................................. 869

vbCrLf ........................................................................ 570

VBE-Editor aufrufen ............................................ 691

vbLf ........................................................... 148, 189, 270

vbTab ......................................................................... 570

Verknüpfungen

aktualisieren ...................................................... 458

aufspüren ............................................................ 196

entfernen ............................................................. 456

umwandeln ........................................................ 452

Version ......................................................................... 77

VerticalAlignment ........................................ 315, 676

Verzeichnis

erstellen ...................................................... 420, 597

löschen ................................................................. 597

wechseln ..................................................... 366, 832

Verzeichnisbaum anzeigen .............................. 599

Verzweigungen einsetzen ................................... 65

Visible .................. 149, 236, 375, 376, 378, 691, 748,

787, 835, 902

Volatile ............................................................ 540, 550

Vorsetzungszeichen einsetzen ......................... 47

W

Wait .................................................. 410, 435, 683, 712

Warennummer bilden ........................................ 207

Warnmeldungen

ausschalten .......................................................... 83

deaktivieren ....................................................... 885

Weekday ................................ 88, 230, 231, 288, 306

Index

917

Werte

addieren ...................................................... 107, 176

dividieren ............................................................. 178

löschen .................................................................. 213

multiplizieren ..................................................... 177

potenzieren ......................................................... 178

runden ......................................................... 187, 196

subtrahieren ....................................................... 176

suchen ................................................................... 261

verdoppeln ............................................................ 84

zählen ................................................................... 246

While ......................................................................... 246

Width ........................................................ 501, 781, 795

Wiederholungsspalten definieren ................. 319

Wiederholungszeilen definieren .................... 319

Wiedervorlage-Liste auslesen ......................... 428

Windows-Verzeichnis ermitteln .................... 602

With .................................................................. 109, 379

Wochenende

abfragen .............................................................. 288

entfernen ............................................................. 287

ermitteln ........................................................ 87, 575

kennzeichnen .................................................... 305

Wochentag

abfragen .............................................................. 306

ermitteln ............................................................. 230

Workbook ................................................................... 61

Workbook_Activate ............................................. 632

Workbook_AddinInstall ..................................... 632

Workbook_AddinUninstall .............................. 632

Workbook_AfterSave .......................................... 632

Workbook_BeforePrint ............................ 630, 632

Workbook_BeforeSave .................... 624, 626, 632

Workbook_Deactivate ........................................ 632

Workbook_NewChart .......................................... 632

Workbook_NewSheet .......................................... 633

Workbook_Open ....... 298, 313, 327, 522, 612, 615,

619, 625, 677, 691

Workbook_PivotTableCloseConnection ..... 633

Workbook_PivotTableOpenConnection ..... 633

Workbook_SheetActivate ........................ 628, 633

Workbook_SheetBeforeDoubleClick ............ 633

Workbook_SheetBeforeRightClick ................ 633

Workbook_SheetCalculate ................................ 633

Workbook_SheetChange ................................... 633

Workbook_SheetDeactivate ................... 628, 633

Workbook_SheetFollowHyperlink ................ 633

Workbook_SheetSelectionChange ................ 633

Workbook_WindowActivate ............................ 633

Workbook_WindowDeactivate ....................... 633

Workbook_WindowResize ................................ 633

Workbooks ...................................................... 431, 437

Worksheet ........ 62, 86, 94, 107, 131, 366, 378, 407

Worksheet_Activate .................................. 634, 670

Worksheet_BeforeDoubleClick ............. 634, 670

Worksheet_BeforeRightClick ....... 634, 667, 670

Worksheet_Calculate ......................................... 670

Worksheet_Change ......... 322, 323, 510, 634, 639,

642, 647, 654, 655, 664,

670, 800

Worksheet_Deactivate ............................. 670, 674

Worksheet_FollowHyperlink ................ 669, 670

Worksheet_PivotTableUpdate .............. 668, 670

Worksheet_SelectionChange ...... 660, 664, 670

WorksheetFunction ............................................. 337

Wörter zählen ......................................................... 244

WrapText ................................ 259, 315, 675, 813, 883

X

xlColorIndexNone ................................................. 88

Y

Year ............................................................................. 764

Z

Zahl

formatieren ........................................................... 91

runden ......................................................... 196, 550

suchen .................................................................. 261

summieren .......................................................... 184

umwandeln ........................................................ 559

wandeln ................................................................. 74

Zahlenformat

festlegen .............................................................. 162

übertragen .......................................................... 164

Zeichen

eliminieren .......................................................... 544

entfernen ............................................................. 901

vergleichen ......................................................... 558

Zeichenfolge

ermitteln ............................................................... 555

ersetzen ................................................................. 143

suchen .................................................................. 245

Zeichenlänge

ermitteln ............................................................... 125

messen .................................................................... 111

Zeile

ausblenden ................................................. 96, 296

einblenden ................................................... 92, 296

Index

918

Zeile (Forts.)

einfärben .............................................................. 275

einfügen ..................................... 97, 222, 279, 280

filtern ..................................................................... 325

löschen .............................. 97, 279, 282, 288, 300

markieren ................................................... 264, 274

übertragen .......................................................... 103

umbrechen .......................................................... 259

zählen .................................................. 120, 268, 335

Zeilenhöhe anpassen ........................................... 276

Zeilenumbrüche

festlegen ................................................................ 313

zurücksetzen ..................................................... 674

Zeiten umrechnen ................................................ 548

Zeitverzögerung programmieren .................. 435

Zellcursor auslesen ............................................... 147

Zelle

ansprechen ............................................................ 62

ansteuern ..................................................... 121, 267

einblenden ......................................................... 300

färben ............................................................. 88, 197

füllen ..................................................................... 249

hervorheben ....................................................... 128

Zelle (Forts.)

löschen .................................................................. 215

markieren ..................................................... 115, 130

sperren ................................................................... 257

suchen ................................................................... 122

verbinden ............................................................ 126

verschieben ......................................................... 267

verweisen .............................................................. 132

zählen .................................................... 91, 125, 268

Zellenadresse abfragen ......................................... 63

Zellenformatierungen entfernen .................. 164

Zelleninhalt

ersetzen ................................................................ 205

löschen ......................................................... 154, 295

Zellenverbände aufheben ................................. 129

Zellenverbund

aufheben ............................................................... 128

erstellen ................................................................ 129

Zellenverknüpfungen aufspüren ................... 883

Zoom .......................................................................... 416

Zufallszahl

bilden .................................................................... 754

erstellen ................................................................ 186

Zwischenablage löschen ................................... 607

Wir hoffen sehr, dass Ihnen diese Leseprobe gefallen hat. Gerne dürfen Sie diese Leseprobe empfehlen und weitergeben, allerdings nur vollständig mit allen Seiten. Die vorliegende Leseprobe ist in all ihren Teilen urheberrecht-lich geschützt. Alle Nutzungs- und Verwertungsrechte liegen beim Autor und beim Verlag.

Teilen Sie Ihre Leseerfahrung mit uns!

Bernd Held ist von Haus aus gelernter Informatiker. Zu seinen Spezialgebieten zählen Excel, VBA-Programmie-rung, Access und allgemeine Office- und Tool-Themen. Er wurde von Microsoft mehrfach als MVP (Most Valuable Professional) für den Bereich Excel ausgezeichnet. Seit 2008 arbeitet er mit einem eigenen Team aus Experten zusammen, das Projekte und Schulungen durchführt, Unter- nehmen berät und Bücher sowie Fachartikel veröffentlicht.

Bernd Held

VBA mit Excel – Das umfassende Handbuch918 Seiten, 2013, mit DVD, 49,90 € ISBN 978-3-8362-2579-3

www.galileocomputing.de/3426

Wissen, wie’s geht.

VBA mit Excel - ReadingSample - beck-shop.de · PDF file65 4 Kapitel 4 Sprachelemente in VBA...

Documents

Transcript of VBA mit Excel - ReadingSample - beck-shop.de · PDF file65 4 Kapitel 4 Sprachelemente in VBA...