Grundkurs VBA

Transcrição

Grundkurs VBA

Grundkurs VBA
Dietrich Baumgarten
«1. Dezember 2009
Inhaltsverzeichnis
1 Aufgaben
9
2 Die
2.1
2.2
2.3
2.4
Entwicklungsumgebung von Visual Basic
Arbeitsmappe . . . . . . . . . . . . . . . . .
Überblick . . . . . . . . . . . . . . . . . . .
Makroaufzeichnen . . . . . . . . . . . . . . .
Der Visual Basic Editor . . . . . . . . . . .
2.4.1 Symbolleisten . . . . . . . . . . . . .
2.4.2 Der Projekt-Explorer . . . . . . . . .
2.4.3 Das Code-Fenster . . . . . . . . . . .
2.4.4 Module . . . . . . . . . . . . . . . .
2.4.5 Das Eigenschaftenfenster . . . . . . .
2.5 Sub-Prozeduren . . . . . . . . . . . . . . . .
2.5.1 Ausführen von Sub-Prozeduren . . .
2.5.2 Laufzeitfehler . . . . . . . . . . . . .
2.5.3 Sub-Prozeduren und Zellen . . . . . .
2.6 Funktionen . . . . . . . . . . . . . . . . . .
2.7 Meldungsfelder . . . . . . . . . . . . . . . .
2.7.1 Auswertung des Rückgabewertes . .
2.8 Variable . . . . . . . . . . . . . . . . . . . .
2.9 Eingabefenster . . . . . . . . . . . . . . . .
2.10 Aufgaben . . . . . . . . . . . . . . . . . . .
3 Die Grundlagen von Visual Basic
3.1 Arbeitsmappe . . . . . . . . . . . .
3.2 Format und Zeichenvorrat . . . . .
3.2.1 Format von Anweisungen . .
3.2.2 Zeichenvorrat . . . . . . . .
3.2.3 Schlüsselworte . . . . . . . .
3.2.4 Bezeichner (identifier) . . .
3.2.5 Kommentare . . . . . . . . .
3.3 Ein einführendes Beispiel . . . . . .
3.4 Variable . . . . . . . . . . . . . . .
3.4.1 Datentyp von Variablen . .
3.4.2 Gültigkeit von Variablen . .
3.4.3 Initialisierung von Variablen
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
10
10
10
10
12
13
13
14
14
15
15
16
17
18
19
20
20
22
22
23
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
24
24
24
24
25
25
25
26
26
28
28
28
29
3
Inhaltsverzeichnis
3.4.4 Option Explicit-Anweisung .
3.5 Konstanten . . . . . . . . . . . . .
3.6 Gültigkeit von Prozeduren . . . . .
3.7 Einfache Datentypen . . . . . . . .
3.7.1 Basistypen für Zahlen . . .
3.7.2 Überlauf bei ganzen Zahlen
3.7.3 Der logische Datentyp . . .
3.7.4 Bedingte Anweisungen . . .
3.8 Ausdrücke und Anweisungen . . . .
3.9 Operatoren . . . . . . . . . . . . .
3.9.1 Arithmetische Operatoren .
3.9.2 Der Zuweisungsoperator . .
3.9.3 Der Modulooperator . . . .
3.9.4 Vergleichsoperatoren . . . .
3.9.5 Logische Operatoren . . . .
3.9.6 Vorrangregeln . . . . . . . .
3.10 Aufgaben . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
30
30
31
32
32
33
33
33
34
34
35
35
36
36
37
38
39
4 Komplexe Datentypen
4.1 Arbeitsmappe . . . . . . . . . . . . . . . . . . . . . .
4.2 Zeichenfolgen . . . . . . . . . . . . . . . . . . . . . .
4.2.1 Vordefinierte Zeichenfolgen . . . . . . . . . . .
4.2.2 Der Verkettungsoperator & für Zeichenfolgen .
4.2.3 Nützliche Stringfunktionen . . . . . . . . . . .
4.2.4 Typumwandlungsfunktionen . . . . . . . . . .
4.2.5 Die IsNumeric-Funktion . . . . . . . . . . . .
4.3 Wiederholungen von Anweisungen . . . . . . . . . . .
4.4 Datenfelder (Arrays) . . . . . . . . . . . . . . . . . .
4.4.1 Unter- und Obergrenzen eines Datenfelds . . .
4.4.2 Dynamische Datenfelder . . . . . . . . . . . .
4.4.3 Mehrdimensionale Datenfelder . . . . . . . . .
4.5 Der Datumstyp Date . . . . . . . . . . . . . . . . . .
4.5.1 Nützliche Datums-Funktionen . . . . . . . . .
4.6 Benutzerdefinierte Datentypen . . . . . . . . . . . . .
4.7 Der Datentyp Variant . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
40
40
40
41
42
42
44
44
45
46
47
47
48
49
50
51
52
5 Funktionen und Sub-Prozeduren
5.1 Arbeitsmappe . . . . . . . . . . . . . .
5.2 Einführendes Beispiel . . . . . . . . . .
5.3 Syntax von Funktionen . . . . . . . . .
5.4 Verwendung von Funktionen in Excel .
5.5 Aufruf von Funktionen in VBA . . . .
5.6 Parameter von Funktionen . . . . . . .
5.6.1 Formale und aktuelle Parameter
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
54
54
54
55
56
56
57
57
4
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Inhaltsverzeichnis
5.7
5.8
5.6.2 Parameterübergabe durch Wert . .
5.6.3 Parameterübergabe durch Referenz
Objekte als Parameter . . . . . . . . . . .
5.7.1 Sonderfall Zeichenfolgen . . . . . .
5.7.2 Vektoren als Parameter . . . . . . .
Ausstieg aus Prozeduren . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6 Kontrollstrukturen
6.1 Arbeitsmappe . . . . . . . . . . . . . . . . . . . . . . . . .
6.2 Verzweigungen . . . . . . . . . . . . . . . . . . . . . . . .
6.2.1 Die einseitige Auswahl . . . . . . . . . . . . . . . .
6.2.2 Die echte Verzweigung . . . . . . . . . . . . . . . .
6.2.3 Verschachtelte Verzweigungen . . . . . . . . . . . .
6.2.4 Die mehrfache Verzweigung . . . . . . . . . . . . .
6.2.5 Mehrfache Verzweigung mit Select Case . . . . . .
6.2.6 Weitere Beispiele . . . . . . . . . . . . . . . . . . .
6.2.7 Eine Testprozedur . . . . . . . . . . . . . . . . . .
6.3 Schleifen . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.3.1 For-Next-Schleife . . . . . . . . . . . . . . . . . . .
6.3.2 Schleifen mit Wiederholungsbedingung am Ende . .
6.3.3 Schleifen mit Abbruchbedingung am Ende . . . . .
6.3.4 Schleifen mit Wiederholungsbedingung am Anfang .
6.3.5 Schleifen mit Abbruchsbedingung am Anfang . . .
6.3.6 Tests der Funktionen . . . . . . . . . . . . . . . . .
6.4 Verschachtelte Kontrollstrukturen . . . . . . . . . . . . . .
6.4.1 Verschachtelte For-Next-Schleifen . . . . . . . . . .
6.4.2 Verschachtelung von For-Next- und Do-Schleifen . .
6.4.3 Verschachtelung von Do-Schleifen . . . . . . . . . .
6.5 Über Kalender . . . . . . . . . . . . . . . . . . . . . . . . .
6.5.1 Schaltjahrberechnung . . . . . . . . . . . . . . . . .
6.5.2 Die laufende Nummer eines Tages . . . . . . . . . .
6.5.3 Bestimmung des Wochentages . . . . . . . . . . . .
6.5.4 Eine Testfunktion . . . . . . . . . . . . . . . . . . .
6.6 Sprunganweisungen . . . . . . . . . . . . . . . . . . . . . .
6.6.1 Die GoTo Sprunganweisung . . . . . . . . . . . . .
6.7 Aufgaben . . . . . . . . . . . . . . . . . . . . . . . . . . .
7 Excel-Objekte
7.1 Die Basis-Objekte . . . . . . . . . . . . . . . . . .
7.1.1 Auflistungen . . . . . . . . . . . . . . . . .
7.1.2 Objektvariable . . . . . . . . . . . . . . .
7.1.3 Die With-Syntax . . . . . . . . . . . . . .
7.1.4 Durchlaufen von Listen . . . . . . . . . . .
7.1.5 Methoden von Workbook und Workbooks
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
57
58
59
60
60
62
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
64
64
64
65
66
67
68
69
70
72
72
73
75
76
78
78
79
80
80
81
81
82
83
83
84
85
86
86
87
.
.
.
.
.
.
89
89
90
91
92
93
94
5
Inhaltsverzeichnis
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
94
95
95
96
96
98
8 Formulare
8.1 Aufgabenstellung . . . . . . . . . . . . . . . . . . . . .
8.2 Erstellen von Formularen . . . . . . . . . . . . . . . . .
8.3 Einfügen von Steuerelementen in ein Formular . . . . .
8.4 Eigenschaften von Steuerelementen . . . . . . . . . . .
8.4.1 Wichtige Eigenschaften . . . . . . . . . . . . . .
8.5 Ereignisse von Steuerelementen . . . . . . . . . . . . .
8.6 Ereignisse und Eigenschaften des Programms . . . . . .
8.7 Die Tabellenereignisse . . . . . . . . . . . . . . . . . .
8.8 Die Formulareignisse . . . . . . . . . . . . . . . . . . .
8.8.1 Test des Codes . . . . . . . . . . . . . . . . . .
8.9 Eigenschaften, Methoden und Ereignisse . . . . . . . .
8.9.1 Objekte . . . . . . . . . . . . . . . . . . . . . .
8.9.2 Eigenschaften und Methoden . . . . . . . . . .
8.9.3 Unterschied zwischen Ereignissen und Methoden
8.10 Steuerelemente auf Tabellen . . . . . . . . . . . . . . .
8.10.1 Listen- und Kombinationsfelder . . . . . . . . .
8.11 Qualifizierte Bezeichner . . . . . . . . . . . . . . . . . .
8.12 Die With-Anweisung . . . . . . . . . . . . . . . . . . .
8.13 Das Formular DemoForm . . . . . . . . . . . . . . . .
8.13.1 Der Code des Formulars DemoForm . . . . . .
8.14 Aufgaben . . . . . . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
99
99
99
100
101
101
102
102
103
104
104
105
105
105
106
106
107
109
109
110
110
111
7.2
6
7.1.6 Methoden von Worksheet und Worksheets
Die Klasse Range . . . . . . . . . . . . . . . . . .
7.2.1 Zeilen und Spalten . . . . . . . . . . . . .
7.2.2 Die Eigenschaft CurrentRegion . . . . . .
7.2.3 Die Cells-Auflistung . . . . . . . . . . . .
7.2.4 Die Offset-Eigenschaft von Bereichen . . .
.
.
.
.
.
.
.
.
.
.
.
.
Abbildungsverzeichnis
1.1
Reihen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1
2.2
2.3
2.4
Symbolleisten für Visual Basic und Makrorecorder .
Der Visual Basic Editor . . . . . . . . . . . . . . .
Eine Fehlermeldung . . . . . . . . . . . . . . . . . .
Der Visual Basic Editor mit fehlerhafter Anweisung
.
.
.
.
11
12
17
18
7.1
Hierarchie der Excel-Objekte . . . . . . . . . . . . . . . . . . . . . . . . .
91
8.1
8.2
8.3
8.4
8.5
Der Visual Basic Editor . . . . . . .
Das Code-Fenster der Tabelle1 . . . .
Tabelle mit Steuerelement . . . . . .
Das Formular DemoForm . . . . . .
Der Code zum Formular DemoForm
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
9
100
103
107
108
111
7
Tabellenverzeichnis
8
2.1
2.2
Konstanten zur Ausgestaltung von Meldungsfenster . . . . . . . . . . . .
Rückgabewerte von Meldungsfenstern . . . . . . . . . . . . . . . . . . . .
20
21
3.1
3.2
3.3
3.4
3.5
Schlüsselworte . . . . . . . . .
Ausdrücke . . . . . . . . . . .
Vergleichsoperatoren . . . . .
Logische Operatoren . . . . .
Vorrangregeln für Operatoren
.
.
.
.
.
25
34
36
38
38
4.1
Vordefinierte Zeichenfolgen . . . . . . . . . . . . . . . . . . . . . . . . . .
41
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1 Aufgaben
1. Erstellen Sie das abgebildete Arbeitsblatt. Dabei soll der Bierumsatz monatlich
um 20% steigen, wobei immer auf die nächst kleinere Zahl abgerundet wird. Den
Verlauf der Limonade sollten Sie erkennen und sich dann noch den Zusammenhang
mit dem Gewinn überlegen. Die Gewinnspalte stellen Sie bitte als Matrixformel
dar.
Bei den Datums- und Zeitberechnungen soll der Anwender sein Geburtsdatum
eingeben können und dann die Anzahl der gelebten Tage und sein Lebensalter
erfahren. Weiter soll in der gezeigten Zelle immer die aktuelle Zeit mit Sekunden
erscheinen, wobei der Anwender allerdings die F9-Taste betätigen muss.
H
I
J
Säulendiagramm
4.000
3.500
3.000
2.500
2.000
1.500
1.000
500
0
Dez
Nov
Okt
Alter
38
Sep
Gelebte Tage
14141
Limo
Aug
Bier
Jul
Heute
Geburtstag
07.10.2009
19.01.1971
Blatt neu berechnen: Taste F9!
G
Jun
Limo
2000
2160
2320
2480
2640
2800
2960
3120
3280
3440
3600
3760
F
Mai
Bier
350
420
504
604
724
868
1041
1249
1498
1797
2156
2587
E
Apr
Monat
Jan
Feb
Mrz
Apr
Mai
Jun
Jul
Aug
Sep
Okt
Nov
Dez
D
Gewinn
in Euro
880,00
984,00
1099,20
1227,20
1371,20
1534,40
1720,80
1935,20
2182,40
2469,60
2804,80
3197,60
Mrz
C
Feb
B
Jan
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
Gewinn
i E
Uhrzeit
01:03:03
Abbildung 1.1: Reihen
9
2 Die Entwicklungsumgebung von
Visual Basic
Visual Basic for Applications, kurz VBA, ist eine leistungsfähige Skriptsprache, die zur
Erweiterung der Möglichkeiten der Microsoft-Office-Anwendungen wie Excel, Word oder
Access entwickelt wurde. VBA hat eine eigene Entwicklungsumgebung, die mit dem
zugehörigen Wirtsprogramm zusammen ausgeliefert wird. VBA ist eine Skriptsprache,
deren Anweisungen zeilenweise interpretiert werden. Deshalb ist VBA im Vergleich zu
C oder Fortran sehr langsam, also nicht für aufwendige mathematische Algorithmen
geeignet. VBA dient der Automatisierung von wiederholt durchzuführenden Aufgaben,
kann aber auch die Fähigkeiten von Office-Programmen beträchtlich erweitern.
2.1 Arbeitsmappe
Hinweis
Alle Sub-Prozeduren und Funktionen dieses Kapitels befinden sich in der Arbeitsmappe
»VBAEinfuehrung«, die Sie von der Seite
//htm
runterladen können.
2.2 Überblick
VBA erfüllt vier Aufgaben
• Makroaufzeichnen,
• Erstellen von Sub-Prozeduren,
• Erstellen von Funktionen,
• Erstellen von Formularen.
2.3 Makroaufzeichnen
Eine der Fähigkeiten von VBA ist die Automatisierung von Routineaufgaben. Sorgen
Sie bitte zunächst dafür, dass in Excel die Symbolleiste »Visual Basic« zu sehen ist.
10
2.3 Makroaufzeichnen
Abbildung 2.1: Symbolleisten für Visual Basic und Makrorecorder
Das geschieht entweder über das Kontextmenü der Symbolleisten oder über die Befehlsfolge Ansicht|Symbolleisten. In der Abbildung 2.2 auf Seite 12 befindet sich
diese Symbolleiste links oben. Darin befindet sich ein Symbol für den Start der Makroaufzeichnung. Sie können auch die Befehlsfolge Extras|Makros|Aufzeichnen. . .
verwenden. Es öffnet sich der Dialog, der in der erwähnten Abbildung links unten zu
sehen ist. Sie müssen sich für einen Namen für das Makro entscheiden, können aber auch
den vorgeschlagenen Bezeichner »Makro1« übernehmen, das nächste Makro hieße dann
»Makro2«. Außerdem kann dem Makro wie zu sehen ist ein Tastaturkürzel zugewiesen
werden.
Die Symbolleiste rechts ist die Werkzeugleiste, in der alle Steuerelemente von VBA
versammelt sind. Darauf komme ich bei der Besprechung der Formulare zurück.
Sobald Sie im Dialog »Makro aufzeichnen« auf «OK« drücken, verwandelt sich der
Kreis des Symbols »Aufzeichnen. . . « in ein Quadrat, womit die Aufzeichnung wieder
beendet werden kann. Zur Sicherheit erscheint auch noch das kleine Dialogfeld, das ich
zwischen den Symbolleisten abgebildet habe, womit die Aufzeichnung über das Quadrat
ebenfalls beendet werden kann. Aber dazu ist es noch zu früh, wir haben ja noch gar
nicht begonnen.
Ich habe wie beschrieben die Makroaufzeichnung gestartet und danach folgende Aktionen durchgeführt:
1. In der noch unbearbeiteten Arbeitsmappe »Mappe1« habe ich die dritte Tabelle
markiert und über das Kontextmenü gelöscht.
2. Dann habe ich die erste Tabelle markiert und in die Zelle A1 »Hallo!« eingegeben.
3. Nun habe ich die neue Mappe unter dem Namen »Makrotest« im Verzeichnis C:\
gespeichert.
4. Abschließend habe ich die Aufzeichnung über das quadratische Symbol der Symbolleiste »Visual Basic« angehalten.
11
2 Die Entwicklungsumgebung von Visual Basic
Excel hat alles getreulich ausgeführt und der Makrorecorder hat den in der Abbildung
2.2 zu sehenden Code aufgezeichnet. Der Code befindet sich innerhalb der Entwicklungsumgebung von Visual Basic. Wie Sie dorthin gelangen, zeige ich gleich.
2.4 Der Visual Basic Editor
Wenn Sie alles nachgemacht haben, werden Sie sicher den Code suchen. Dazu müssen
Sie den Visual Basic Editor öffnen.
Dafür haben Sie drei Möglichkeiten:
1. Über die Befehlsfolge Extras|Makros|Visual Basic Editor. . .
2. Über die Tastenkombination Alt + F11 .
3. Über das Symbol »Visual Basic Editor« der Symbolleiste »Visual Basic«.
Abbildung 2.2: Der Visual Basic Editor
Visual Basic erscheint dann in der folgenden klassischen Dreiteilung: Links oben ist
der Projekt-Explorer zu sehen, darunter das Eigenschaftenfenster und rechts von beiden
das Arbeitsfenster zum Anzeigen von Code und/oder Formularen.
12
2.4 Der Visual Basic Editor
Sollte das Eigenschaftenfenster nicht vorhanden sein, müssen Sie F4 drücken oder
den entsprechenden Befehl des Menüs Ansicht wählen. Sollte der Projekt-Explorer
nicht vorhanden sein, müssen Sie Strg + R drücken oder den entsprechenden Befehl
des Menüs Ansicht wählen. Zwischen den drei Fenstern befinden sich Trennbalken,
worüber Sie die Größen der einzelnen Fenster festlegen können.
Aus der Entwicklungsumgebeung von VBA gelangen Sie ebenfalls über die Tastenkombination Alt + F11 zurück nach Excel oder über den Befehl des Menüs Ansicht.
2.4.1 Symbolleisten
Genau wie Excel besitzt auch der Visual Basic Editor einige Symbolleisten, in der Abbildung 2.2 ist davon nur »Voreinstellung« geöffnet. Die wichtigsten Schaltflächen habe
ich erklärt. Über die Schaltfläche »Excel« gelangt man zurück nach Excel. Die Schaltfläche »Einfügen« enthält Unterbefehle für das Erstellen von neuen Modulen, Formularen
oder Prozeduren. Die Schaltfläche »Ausführen« setzt den Code der gerade bearbeiteten Prozedur oder des aktiven Formulars in Gang. Die Schaltfläche daneben unterbricht
die Ausführung. Falls der Code fehlerhaft ist und von VBA unterbrochen wurde, muss
die Schaltfläche »Zurücksetzen« angeklickt werden. Auf »Entwurfsmodus« komme ich
noch. Die beiden Schaltflächen »Projektexplorer« und »Eigenschaftenfenster« tun was
ihr Name verspricht und zeigen die entsprechenden Fenster. Sie sind aber keine Schalter,
denn Sie öffnen zwar geschlossene Fenster, schließen aber kein geöffnetes, da sowohl der
Projektexplorer als auch das Eigenschaftenfenster selbst Schaltflächen zum Schließen
haben.
2.4.2 Der Projekt-Explorer
Der Projekt-Explorer zeigt wie der Windows-Explorer eine hierarchische Liste der Projekte und aller Elemente an, die in den jeweiligen Projekten enthalten sind. Die Elemente
befinden sich im Listenfenster, das einen Überblick über die Arbeitsmappen, Tabellen,
Module und Formulare des Projekts gibt. Zu jedem Listenelement kann Code geschrieben
werden.
Objektorientiert gesehen steht das Programm Excel an der Spitze der Hierarchie. Excel verwaltet Arbeitsmappen, diese bestehen aus Tabellenblättern. Zu jedem Objekt
kann man VBA-Code bereitstellen. Zusätzlich kann weiterer Code in so genannten Modulen abgelegt werden. Im Projekt-Explorer sind die Bestandteile aufgeführt, rechts
daneben befindet sich das Code-Fenster zum Erstellen des Codes. Die Auswahl zum
Bearbeiten erfolgt durch einfachen Doppelklick (kleiner Scherz) im Listenfenster des
Projekt-Explorers.
Oberhalb des Listenfensters befinden sich drei Symbole. Über das Symbol rechts können die Objektordner ein- bzw. ausgeblendet werden, während die darin enthaltenen
Objekte weiterhin angezeigt werden.
Das Symbol links aktiviert das Code-Fenster, während das Symbol in der Mitte das
Objekt selbst zeigt. Das Code-Fenster öffnet sich auch über die Taste F7 und das
Objekt selber wird angezeigt durch ⇑ + F7 . Bei der Bearbeitung von Formularen
13
kann man auf diese Weise zwischen der Erstellung des Formulars und der Erzeugung
des Codes hin- und herschalten. Bei Excel-Objekten führt das mittlere Symbol bzw. die
Tastenkombination ⇑ + F7 zurück zum Excel-Fenster.
In der Abbildung 2.2 ist das Listenelement »Modul1« unter dem Ordner Module
aktiviert. Module enthalten Visual Basic Code und deshalb existiert hierzu kein Objekt,
das mittlere Symbol ist deaktiviert. Im rechten Fenster, dem so genannten Code-Fenster
befindet sich der vom Makrorecorder erzeugte Code.
2.4.3 Das Code-Fenster
Das Code-Fenster erscheint wie erwähnt bei Objekten durch Auswahl des Objekts im
Listenfenster des Projekt-Explorers und Klicken des Symbols »Code anzeigen«. Man
kann aber auch die Taste F7 drücken oder im Menü Ansicht den Befehl Code
wählen.
Im Code-Fenster wird der Visual Basic-Code erstellt und bearbeitet. Sie können so
viele Code-Fenster öffnen wie Module vorhanden sind, sodass der Code auf einfache
Weise in verschiedenen Formularen oder Modulen angezeigt und zwischen diesen kopiert
und eingefügt werden kann. Über das Menü Fenster lassen sich die geöffneten Fenster
anordnen. Sobald ein Fenster im Vollbild ist, überdeckt es alle übrigen Fenster.
Über dem Code-Fenster können Sie zwei Dropdown-Listenfelder erkennen, das linke
wird »Objekt«, das rechte »Prozedur« genannt. Bei Formularen sind die Objekte die
Steuerelemente und die Prozeduren die zum ausgewählten Steuerelement vorhandenen
Ereignisse. Die Prozeduren sind alphabetisch nach Namen sortiert. Durch Auswahl einer
Prozedur wird der Cursor in deren erste Code-Zeile positioniert.
Bei Formularen sind die Namen der Ereignisprozeduren nicht frei wählbar, sondern
setzen sich aus dem Namen des Steuerelements und dem Ereignis zusammen, etwa
CommandButton1_Click()für die Ereignisprozedur »Klicken« einer Schaltfläche namens »CommandButton1«.
Wenn bei Modulen »(Allgemein)« im Feld »Objekt« angezeigt wird, werden im Feld
»Prozedur« alle Deklarationen und allgemeinen Prozeduren aufgeführt.
2.4.4 Module
Der Makrorecorder hat den Code in einem so genannten Modul gespeichert und diesem
Modul den einfallsreichen Namen »Modul1« verpasst. Der Code selber ist eine so genannte Sub-Prozedur . Sie dürfen weitere Sub-Prozeduren im bisherigen oder in neuen
Modulen schreiben. Jeder Modul soll dabei einen logisch abgeschlossenen Themenbereich
des Gesamtprojekts abdecken. Module enthalten aufgezeichnete Makros oder selbst erstellten Programmcode. Die Gesamtheit aller Module und Formulare bezeichnet man als
Projekt. Jedes VBA-Projekt darf beliebig viele Module haben, in denen der Quellcode
enthalten ist.
Bei der Aufzeichnung eines Makros legt die Entwicklungsumgebung selbst einen neuen
Modul an. Sie können aber auch selbst einen neuen Modul erzeugen, und zwar über den
14
2.5 Sub-Prozeduren
Befehl Einfügen|Modul der Entwicklungsumgebung von VBA oder über die Schaltfläche der Symbolleiste »Voreinstellung«. Dadurch öffnet sich ein leeres Fenster des Editors.
Module erhalten standardmäßig die fortlaufenden Namen »Modul1«, »Modul2« usw.,
können aber über das Eigenschaftenfenster umbenannt werden.
2.4.5 Das Eigenschaftenfenster
Das Eigenschaftenfenster wird zunächst selten benötigt, es kommt erst bei der Erstellung von Formularen ins Spiel. Das Eigenschaftenfenster befindet sich unterhalb des
Projektexplorers, kann aber geschlossen werden und erscheint dann erneut über das
Menü Ansicht, die entsprechende Schaltfläche der Symbolleiste »Voreinstellung« oder
über F4 . In der Abbildung 2.2 auf Seite 12 ist im Projektexplorer das Listenelement
»Modul1« unter dem Ordner Module aktiviert. Deshalb zeigt das Eigenschaftenfenster
die Eigenschaften dieses Moduls.
2.5 Sub-Prozeduren
Erzeugen Sie nun bitte zur Übung einen neuen Modul und taufen den voreingestellten
Namen »Modul2« in »NeueMakros« um. Der neue Modul wird jetzt im Projekt-Explorer
angezeigt. Durch Doppelklick wird das Codefenster aktiv. Im Vollbild ist nur jeweils ein
Codefenster zu sehen, Sie können aber über den Menüpunkt Fenster die Codefenster
nach Ihren Wünschen anordnen. Der Inhalt eines neu angelegten Moduls ist zunächst
leer. Jede Sub-Prozedur beginnt mit der Anweisung Sub IrgendeinName(), worauf
der Editor selbst das abschließende End Sub ergänzt. Zwischen diese beiden Anweisungen kommt dann der eigentliche Code.
Ich möchte das vom Makro-Recorder aufgezeichnete Makro in drei Makros zerlegen.
Das erste übernimmt die Speicherung der Arbeitsmappe, das zweite löscht »Tabelle3«
und das dritte schreibt »Hallo« in die Zelle A1 des ersten Arbeitsblatts.
Den Code erhalte ich durch Kopieren aus dem Makro Makro1 des Moduls »Modul1«.
Dazu ordne ich die Codefenster über den Menüpunkt Fenster neben einander an. Der
Code zum Speichern lautet:
Sub Save_As()
ActiveWorkbook.SaveAs _
Filename:="C:\ Makrotest_28_11_09.xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
Das Löschen Tabelle »Tabelle3« übernimmt das folgende Makro.
Sub Tabelle3Weg()
Sheets("Tabelle3").Select
15
ActiveWindow.SelectedSheets.Delete
End Sub
Den Text »Hallo« bringt das nächste Makro in die Zelle A1 des ersten Arbeitsblatts.
Sub HalloA1()
Range("A1").Value = "Hallo"
End Sub
Alle drei Makros habe ich aus dem in Abbildung 2.2 zu sehenden Code des Makros
Makro1 des Moduls »Modul1« zusammengestellt.
Das letzte Makro soll die gelöschte Tabelle »Tabelle3« neu erstellen und hinten einfügen. Durch Spielerei mit dem Makrorecorder gelangte ich zu folgendem Code.
Sub Tabelle3Her()
Sheets.Add
ActiveSheet.Name = "Tabelle3"
Sheets("Tabelle3").Move After:=Sheets(Sheets.Count)
End Sub
Zu Ihrer Erleichterung muss ich erwähnen, dass Sie mit Ihren bisherigen Vorkenntnissen diesen Code keineswegs hätten raten können. Wenn Sie den Code selbst erstellen,
sollte nach der Eingabe von »Sheets.« ein Listenfeld mit allen Methoden des Objekts
Sheets erscheinen. Sie müssen dem Editor aber eine Chance geben und nach dem
Punkt eine Pause einlegen. Mit etwas Englisch, Erfahrung und Phantasie werden Sie
oft die passende Methode für Ihre Absicht finden. Das neue Tabellenblatt wird über die
Methode Add des Behälterobjekts Sheets angelegt. Sheet ist der englische Ausdruck
für Blatt, somit bezeichnet »Sheets« die Gesamtheit aller Blätter. Eines davon ist immer aktiv und wird im Code mit ActiveSheet angesprochen. Hier ist dies gerade das
neu angelegte und dieses gibt sich selbst den Namen »Tabelle3«. Neu eingefügte Blätter
erscheinen immer direkt vor dem Blatt, das vor dem Einfügen aktiv war, daher wird das
neue Blatt an das Ende verschoben.
Hinweis
Beachten Sie bitte, dass jede Sub-Anweisung ein leeres Klammernpaar nach dem Namen
enthalten muss, also Sub NeuesBlatt()und nicht Sub NeuesBlatt.
2.5.1 Ausführen von Sub-Prozeduren
»Eine Sub-Prozedur ist eine Folge von Visual Basic-Anweisungen, die in den Anweisungen Sub und End Sub eingeschlossen sind und Aktionen ausführen, aber keinen Wert
zurückgeben.« heißt es so schön in der Online-Hilfe.
Eine Sub-Prozedur kann auf mehreren Wegen ausgeführt werden. Am einfachsten ist
es, wenn der Cursor noch im Code der Sub-Prozedur ist. Dann genügt es, die F5-Taste
zu drücken. Sie können aber auch den längeren Weg über die Befehlsfolge Ausfüh-
16
2.5 Sub-Prozeduren
ren|Sub/UserForm ausführen gehen oder die Schaltfläche der Symbolleiste »Voreinstellung« klicken.
Auch in Excel können Sie Sub-Prozeduren aufrufen, und zwar über die Befehlsfolge
Extra|Makro|Makros. . . oder die Tastenkombination Alt + F8 . Dann erscheint
eine Liste aller erstellten Makros, in unserem Fall also die beiden Makros »Makro1« und
»NeuesBlatt«.
Hinweis
Es werden nur Sub-Prozeduren ausgeführt, die keine Argumente haben.
2.5.2 Laufzeitfehler
Schon die beiden Sub-Prozeduren Tabelle3Weg() und Tabelle3Her() haben
ihre Tücken, man kann keine zweimal hintereinander ausführen! Denn die erste SubProzedur löscht die Tabelle »Tabelle3« und dann fehlt diese zum erneuten Löschen. Sie
müssten also zuvor die Sub-Prozedur Tabelle3Weg() aufrufen, damit die verschwundene Tabelle wieder auftaucht. Aber auch diese sub-Prozedur kann nicht zweimal hintereinander aufgerufen werden, da Excel nicht zwei Tabellen gleichen Namens duldet.
Sollten Sie es aber trotzdem versuchen, wehrt sich der Interpreter mit der garstigen
Fehlermeldung von Abbildung 2.3. Sie können jetzt verzagt die Schaltfläche »Beenden«
Abbildung 2.3: Eine Fehlermeldung
drücken oder sich über »Debuggen« mutig dem begangenen Fehler stellen.
Der Editor hat die beanstandete Anweisung gelb unterlegt. Sie müssen nun im Menü
Ausführen den Befehl Zurücksetzen oder in der Symbolleiste »Voreinstellung« die
entsprechende Schaltfläche anklicken, also die mit dem blauen Quadrat. Wenn Sie sich
im Dialog der Abbildung 2.3 für Beenden entscheiden, hebt der Editor die häßliche
gelbe Untermalung selbst wieder auf. Sie können die Fehler berichtigen und es erneut
probieren. Zuvor aber müssen Sie die Tabelle »Tabelle3« löschen oder umbenennen,
sonst gibt’s wieder Ärger. Da Anfänger öfter Fehler begehen, sei nochmals festgehalten,
wie Sie wieder aus der Klemme kommen.
17
Abbildung 2.4: Der Visual Basic Editor mit fehlerhafter Anweisung
Hinweis
Fehlerhafte Anweisungen werden vom Visual Basic Editor gelb unterlegt. Danach kann
der Code nicht mehr ausgeführt werden bis die Schaltfläche »Zurücksetzen« der Symbolleiste »Voreinstellung« bzw. der Befehl Zurücksetzen des Menüs Ausführen geklickt
wurden.
2.5.3 Sub-Prozeduren und Zellen
Sub-Prozeduren dienen oft der Erweiterung von Möglichkeiten von Excel. Sie können
aber auch unmittelbar mit einzelnen Zellen zuzsammenarbeiten. Die folgende Prozedur
kopiert den Wert und das Format der Zelle A1 des Arbeitsblatts namens »Tabelle1« in
die Zelle A2 des letzten:
Sub Kopie_mit_Format()
Range("A1").Select
Selection.Copy
Sheets(Sheets.Count).Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Ich habe diese Sub-Prozedur über zweimaliges Mitlaufen des Makro-Recorders zusammengebastelt. Wie schon im Kapitel über Excel erwähnt, ist es nicht möglich über den
Befehl Bearbeiten|Einfügen. . . gleichzeitig Wert und das vollständige Format zu
übertragen, aber man kann jedes Vorhaben einzeln durchführen.
Ich habe Ihnen hier auch die beiden Möglichkeiten Arbeitsblätter anzusprechen gezeigt. Kennt man nur den Namen eines Arbeitsblatts, wird dieses mit
18
2.6 Funktionen
aktiviert. Wenn Sie wissen, an welcher Stelle das Arbeitsblatt steht, können Sie statt
des Namens die Ordnungszahl angeben, wobei das letzten Arbeitsblatt die laufende
Nummer Sheets.Count hat. Nach der Auswahl eines Arbeitsblatts greifen Sie dann
über Range("A2").Irgendwas auf die Zelle zu, wobei »Irgendwas« meistens für
Select oder Value steht. Über Select wird die Zelle aktiviert und über Value wird
der Wert angesprochen.
Möchte man nur den Wert, nicht aber das Format kopieren, vereinfacht sich der Code
stark:
Sub Kopie_ohne_Format()
Sheets(Sheets.Count).Range("A2").Value = _
Sheets("Tabelle1").Range("B1").Value
End Sub
Hinweis
Haben Sie sich über den drolligen Unterstrich »_« nach dem Gleichheitszeichen gewundert? Wenn eine Anweisung zu lang für eine Zeile ist, verwendet man den Unterstrich
als Fortsetzungszeichen und schreibt in der nächsten Zeile weiter.
2.6 Funktionen
Funktionen spielen in Excel eine große Rolle. Zu den vorhandenen dürfen Sie jederzeit
eigene Funktionen erstellen. Genau wie bei Sub-Prozeduren ist ein Modul nötig, um den
Code aufzunehmen. Ich verwende dafür den bereits vorhandenen Modul »NeueMakros«
und erstelle als Beispiel die folgende atemberaubende Funktion:
Function Addieren(ByVal a As Long, ByVal b As Long) As Long
Addieren = a + b
End Function
Das Schlüsselwort Sub ist Function gewichen. Außerdem haben Funktionen i.a. Parameter, hier a und b, aus denen das Ergebnis gebildet wird. Das Ergebnis befindet
sich immer in der Variablen, die den Namen der Funktion trägt.
Die erstellte Funktion taucht in Excel beim Funktionsassistent unter der Kategorie
»benutzerdefiniert« auf. Sie können aber auch direkt in eine Zelle »=Addieren(12;13)«
eingeben und erhalten 25 als Ergebnis. Funktionen und Sub-Prozeduren werden später
näher behandelt, hier soll es nur um das Editieren gehen.
Warnung
In VBA müssen Sie die Parameter von Funktionen mit Kommas, in Excel aber mit
Semikolons trennen.
19
2.7 Meldungsfelder
Über die MsgBox-Funktion wird eine Meldung in einem Dialogfeld gezeigt. Der Dialog besteht aus verschiedenen Schaltflächen wie »OK«, »Abbrechen« usw. und einer
Meldung an den Benutzer, der dann als Reaktion die passende Schaltfläche klickt, was
dann den Rückgabewert der Funktion festlegt. Im einfachsten Fall erscheint neben der
Meldung nur die Schaltfläche »OK« zum Schließen des so genannten Meldungsfensters.
Dann braucht der Rückgabewert natürlich nicht ausgewertet werden, da der Benutzer
eh keine Wahl hatte, wie im folgenden Fall:
Sub EiGudeWie()
MsgBox "Wir wünschen Ihnen einen guten Tag!"
End Sub
Das erste und hier einzige Argument der Funktion ist die Meldung. Sie können den
Dialog aber auch noch mit weiteren Schaltflächen und einem Titel ausstatten. Dann
werden zusätzliche Argumente benötigt. Das zweite Argument Btns muss als Wert
oder Summe von Werten aus der Tabelle 2.1 gewählt werden. Das dritte Argument ist
der Titel des Meldungsfensters. Die Anweisung lautet
Rueck = MsgBox( Meldung, Btns, Titel)
Die Tabelle zur Gestaltung der Schaltflächen und der Symbole entstammt der OnlineHilfe und ist nicht vollständig:
Tabelle 2.1: Konstanten zur Ausgestaltung von Meldungsfenster
Konstante
Wert Beschreibung
vbOKOnly
0
VbOKCancel
1
VbAbortRetryIgnore 2
VbYesNoCancel
3
VbYesNo
VbRetryCancel
4
5
VbCritical
VbQuestion
VbExclamation
VbInformation
16
32
48
64
Nur die Schaltfläche »OK« anzeigen.
Schaltflächen »OK« und »Abbrechen« anzeigen.
Schaltflächen »Abbruch«, »Wiederholen« und
»Ignorieren« anzeigen.
Schaltflächen »Ja«, »Nein« und »Abbrechen« anzeigen.
Schaltflächen »Ja« und »Nein« anzeigen.
Schaltflächen »Wiederholen« und »Abbrechen« anzeigen.
Meldung mit Stop-Symbol anzeigen.
Meldung mit Fragezeichen-Symbol anzeigen.
Meldung mit Ausrufezeichen-Symbol anzeigen.
Meldung mit Info-Symbol anzeigen.
2.7.1 Auswertung des Rückgabewertes
Der Rückgabewert eines Meldungsfensters richtet sich nach der gedrückten Schaltfläche.
Die möglichen Werte sehen Sie in der Tabelle 2.2.
20
2.7 Meldungsfelder
Tabelle 2.2: Rückgabewerte von Meldungsfenstern
Konstante
Wert
gewählte Schaltfläche
vbOK
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo
1
2
3
4
5
6
7
»OK«
»Abbrechen«
»Abbruch«
»Wiederholen«
»Ignorieren«
»Ja«
»Nein«
Die möglichen Rückgabewerte sind also von der gedrückten Schaltfläche in der MsgBoxFunktion abhängig. Die folgende Sub-Prozedur zeigt ein Meldungsfenster mit den Schaltflächen »OK« und »Abbrechen« und erschreckt den Benutzer mit einem nervigen Ausrufezeichen.
Sub MeldungsTest()
Sheets(1).Range("A1").Value = _
MsgBox("Mein Häuslein brennt!", _
VbOKCancel + VbExclamation, "Hilfe")
End Sub
In der Zelle A1 der ersten Tabelle steht die Nummer der gedrückten Schaltfläche.
Warnung
Achten Sie bitte auf die Klammern, die nötig werden, sobald die Funktion einen Wert
zurückgibt!
Eine typische Anwendung der MsgBox-Funktion ist die folgende Sub-Prozedur, womit das gerade aktive Arbeitsblatt in »Gustav« umbenannt wird, was aber im letzten
Moment noch zu verhüten ist:
Sub Gustav()
Dim Wert As String
Wert = MsgBox("Das aktive Arbeitsblatt
vbYesNo + vbQuestion)
If Wert = vbNo Then Exit Sub
ActiveSheet.Name = "Gustav"
Range("A1").Select
End Sub
Gustav nennen?", _
Dieses Meldungsfenster hat zwei Schaltflächen, »Ja« und »Nein« sowie ein Fragezeichen.
Am Rückgabewert der MsgBox-Funktion ist zu erkennen, ob der Anwender »Nein« wählte oder »Ja«. Im Argument Wert wird der Rückgabewert festgehalten. Bei der Wahl
von »Nein« ergibt sich für Wert der Wert vbNo, was zum vorzeitigem Abbruch der
Sub-Prozedur führt und die Umbenennung verhindert.
21
Die allgemeine Form der MsgBox-Funktion entnehmen Sie bitte der Online-Hilfe.
2.8 Variable
In der Sub-Prozedur »Gustav« wurde eine so genannte Variable verwendet, nämlich
Wert. Variable sind die Atome eines Programms. Sie beziehen sich auf einen festen
Speicherplatz, wohin über den Namen Bezug genommen wird. Sie dienen wie hier der
Speicherung eines Inhalts, der sich während der Programmausführung ändern darf. Variablenbezeichner müssen bestimmte Bedingungen einhalten. Variablen haben einen Datentyp, der die Größe des zugehörigen Speicherplatzes regelt und werden explizit in einer
so genannten nicht ausführbaren Anweisung deklariert.
Aus Variablen und Konstanten werden durch Operatoren wie + oder ∗ Ausdrücke
gebildet. Über den Zuordnungsoperator = wird aus einem Ausdruck eine so genannte
ausführbare Anweisung.
2.9 Eingabefenster
Die letzte Sub-Prozedur dieses Kapitels wird das gesamte erste Arbeitsblatt rot oder
gelb färben oder alles beim alten lassen, wenn nicht eine dieser Farben vom Anwender
gewählt wird. Für sehr einfache Eingaben verwendet man die InputBox-Funktion. »Die
InputBox-Funktion zeigt eine Eingabeaufforderung in einem Dialogfeld an, wartet auf
die Eingabe eines Textes oder auf das Klicken auf eine Schaltfläche und gibt einen Wert
vom Typ String zurück, der den Inhalt des Textfeldes angibt.« heißt es etwas holprig
in der meist sprachgewandten Online-Hilfe. Die Funktion hat bis zu sieben Argumente,
aber ich werde immer nur drei verwenden. Der erste ist die Meldung, der zweite der Titel
des Dialogs und der dritte der Vorgabewert, den der Anwender im Textfeld ändern kann
und soll. Wird auf dieses Argument verzichtet, erscheint das Textfeld am Anfang leer.
Sub FaerbeA1()
Dim Farbe As String
Farbe = InputBox("Wählen Sie die Farbe:", _
"Farbe eingeben", "rot")
Sheets(1).Select
Range("A1").Select
If UCase(Farbe) = "ROT" Then _
Selection.Interior.ColorIndex = 3
If UCase(Farbe) = "GELB" Then _
Selection.Interior.ColorIndex = 36
End Sub
Wie Sie erkennen können, habe ich »rot« als Vorgabe gesetzt. Gewählt werden können
nur »rot« oder »gelb«, egal in welcher Schreibweise, denn die Funktion UCase() verwandelt
alle Buchstaben in große.
22
2.10 Aufgaben
2.10 Aufgaben
1. Excel erlaubt die Kopie eines Bereichs als Werte und als Format aber nicht beides
zugleich. Zunächst muss der zu kopierende Bereich markiert und etwa über Strg
+ C in die Zwischenablage gebracht werden, dann wird die Anfangszelle des
Bereichs markiert, wo die Kopie eingefügt werden soll. Dann wird der Rest durch
den Dialog erledigt, der sich nach der Befehlsfolge Bearbeiten|Inhalte einfügen. . . öffnet. Am Beispiel des Arbeitsblatts von Aufgabe 1, Seite 9, Abbildung
1.1 erstelle man eine Sub-Prozedur, die den Bereich A1:D13 in eine andere Tabelle mit Werten und Formaten, aber ohne Formeln kopiert. Auch die Breiten der
Spalten sollen übertragen werden.
2. Erstellen Sie eine Sub-Prozedur, die das Diagramm von Abbildung 1.1 erzeugt und
eine zweite, die es löscht.
3. Lassen Sie den Makrorecorder bei folgenden Aktionen mitlaufen:
a) Erzeugen einer neuen Arbeitsmappe.
b) Löschen von »Tabelle3«.
c) Umbenennen der beiden verbliebenen Tabellen in »Blatt1« und »Blatt2«.
d) Speichern der Arbeitsmappe unter dem Namen »MeineMappe.xls«.
Schließen Sie daraus, welche Methoden jeweils gewirkt haben. Finden Sie über die
Online-Hilfe heraus, was die Methode DisplayAlerts()tut und verwenden Sie
diese, um die lästige Nachfrage, ob wirklich gelöscht werden soll, zu unterdrücken.
Basteln Sie damit dann eine Sub-Prozedur, die alle Anforderungen erfüllt und keine
dummen Fragen stellt.
4. Erstellen Sie ein Arbeitsblatt wie in der folgenden Abbildung
A
1 Schaltflächen
B
Symbol
C
Meldung
Lieben Sie große Hunde
2 OK und Abbrechen Fragezeichen und kleine Kinder?
D
Titel
E
Rückgabe
Neugierig
In der Zelle A2 sollen die Einträge auf die Möglichkeiten »Nur OK«, »OK und
Abbrechen« sowie »Ja und Nein« beschränkt sein. In der Zelle B2 sollen nur die
Einträge »Ausrufungszeichen«, »Fragezeichen« und »Stopp« erlaubt sein. In die
Zelle C2 kommt der Text der Meldung und in die Zelle D2 der Titel. Schreiben Sie
eine Sub-Prozedur Meldung(), die zu den gewählten Eingaben das entsprechende
Meldungsfenster erstellt und in der Zelle E2 den Rückgabewert anzeigt.
5. Schreiben Sie eine Sub-Prozedur, die alle Zellen des letzten Arbeitsblatts in zwei
Farben Ihrer Wahl färbt. Meine Version übermalt rücksichtslos alle Gitterlinien,
wenn Sie es Ihnen schaffen, sollten Sie je eine Version mit und ohne Gitterlinien
erstellen.
23
In diesem Kapitels werden Sie die Grundlagen von VBA lernen. Visual Basic ist eine typisierte Sprache, in der alle neuen Typen aus den Basistypen zusammengesetzt
werden. Die Basistypen beschreiben Zahlen, Zeichen und den logischen Datentyp. Die
Zahlen gliedern sich in Festpunkt- und Gleitpunktzahlen. Basistypen für Zahlen haben
einen festgelegten Wertebereich und erlauben bestimmte arithmetische Operationen. Ein
Operator verknüpft ein oder zwei Operanden zu einem Ausdruck, dessen Wert sich aus
den Werten der einzelnen Operanden ergibt. Unter den Operatoren gibt es wie beim
Rechnen bestimmte Vorrangregeln, etwa Punktrechnung geht vor Strichrechnung und
die Möglichkeit die Reihenfolge der Auswertung durch Klammern zu beeinflussen. Aus
Operatoren und den Operanden werden Ausdrücke gebildet, etwa a + b. Jeder Ausdruck hat einen Wert und kann daher wiederum Teil eines anderen Ausdrucks sein und
der Wert jedes Ausdrucks kann einer Variablen über den Zuweisungsoperator = zugewiesen werden. So ist der erwähnte Ausdruck a + b Teil des Ausdrucks d = c*(a + b).
Durch das Zeilenende wird aus einem Ausdruck eine Anweisung.
3.1 Arbeitsmappe
Hinweis
»VBAGrundlagen«, die Sie von der Seite
//htm
3.2 Format und Zeichenvorrat
Visual Basic Programme erfordern ein bestimmtes Format und beruhen auf Ziffern,
lateinischen Zeichen und Sonderzeichen. Dazu kommen so genannte Schlüsselwörter.
3.2.1 Format von Anweisungen
Jede Anweisung muss in einer eigenen Zeile geschrieben werden. Wenn der Platz nicht
langt, wird das Fortsetzungszeichen »_« benutzt.
Die in eine Zeile gehörende Funktion wird dadurch in die nächste Zeile verlängert.
Will man Platz sparen, darf man mehrere Anweisungen in eine Zeile schreiben, muss
24
3.2 Format und Zeichenvorrat
dann aber die einzelnen Anweisungen durch einen Doppelpunkt »:« trennen.
Sie sollten Sie sich an folgende Prinzipien halten, da sie die Lesbarkeit des Codes
begünstigen:
• Pro Anweisung eine Zeile.
• Nach jedem Schlüsselwort wie Sub, Function oder If werden die nachfolgenden Anweisungen eingerückt.
• Die zugehörige schließende Anweisung steht in der gleichen Spalte wie die öffnende
also Function und End Function oder If und End If .
3.2.2 Zeichenvorrat
Der Zeichenvorrat umfasst drei Gruppen:
• Lateinische Buchstaben: a . . . z und A . . . Z, aber keine Umlaute wie ä oder Ü.
• Arabische Ziffern: 0, 1,..., 9.
• Sonderzeichen:
;
,
+
=
(
.
*
)
:
/
^
’
>
"
<
3.2.3 Schlüsselworte
Jede Programmiersprache hat reservierte Schlüsselworte, die vom Programmierer nicht
als Bezeichner verwendet werden dürfen. Einige davon kennen Sie bereits, etwa If oder
Sub. In der folgenden Tabelle habe ich die wichtigsten Schlüsselworte aufgelistet.
If
GoTo
Else
Function
Tabelle 3.1: Schlüsselworte
Else If End False True
Do
Sub
For Next While Case
Until
Loop
Dim
Private
Public
Global
3.2.4 Bezeichner (identifier)
Bezeichner beginnen mit einem Buchstaben, gefolgt von weiteren Buchstaben oder Ziffern und sollten nicht länger als 20 Zeichen sein. Gültige Bezeichner sind somit Xxx12,
xXX12, Xxx12, und diese sind eigentlich alle gleich, da in Visual Basic nicht zwischen großen und kleinen Buchstaben unterschieden wird. Falsch sind dagegen _???,
92X oder gar -X.
Bezeichner stehen für
• Variablen, z.B. Dim x As Long.
25
• Namen von Funktionen und Sub-Prozeduren, z.B. Function Summe(). Bezeichner sollten nicht zu lang, aber aussagekräftig sein; sie dürfen nicht mit Schlüsselworten wie If übereinstimmen.
Im Gegensatz zu C unterscheidet Visual Basic nicht zwischen großen und kleinen Buchstaben! Sie dürfen deshalb nicht eine Variable Hans und eine andere hans nennen, der
Interpreter nähme nur an, dass Sie von nun an Hans klein schreiben wollten.
3.2.5 Kommentare
Kommentare beginnen mit einem Hochkomma »’« und erstrecken sich bis ans Zeilenende. Kommentare dürfen auch noch nach einer Anweisung in derselben Zeile stehen. Man
darf natürlich beliebig viele Kommentarzeilen hintereinander verwenden, muss dann aber
jede Zeile für sich mit dem Hochkomma auskommentieren. Das kann bei größeren Blöcken lästig werden, daher beachten Sie folgenden Hinweis.
Hinweis
Im Visual Basic Editor kann über die Befehlsfolge Ansicht|Symbolleisten die Symbolleiste »Bearbeiten« aktiviert werden. Diese enthält eine Schaltfläche zum Auskommentieren eines markierten Blocks. Eine weitere Schaltfläche macht diesen Vorgang rückgängig.
3.3 Ein einführendes Beispiel
Deutschland im Herbst 2013. Wie vier Jahre zuvor die SPD hat »Mutti« (CDU-Jargon,
wenn sie nicht dabei ist) Merkel nun auch Guidos FDP plattgemacht und einen triumphalen Wahlsieg errungen. Kurz danach wird bekannt, dass die Staatschulden auf 40
Phantasttillionen Euro angeschwollen sind, mehr als das Doppelte von Dagobert Ducks
Vermögen. Aus tiefer Ergebenheit zu den geliebten USA wird zunächst der Dollar als
Währung übernommen und die Staatsschulden werden gestrichen. Steuern werden wie
versprochen ganz abgeschafft und einheitliche Gehälter eingeführt. Jeder Untertan erhält ein Grundgehalt von $ 1000 und eine Zulage von $ 50 pro Lebensjahr, sodass ein
Dreißigjähriger $ 2500 = 1000 + 30 · 50 verdient. Zur Anpassung an die Wirtschaftslage
kann das Grundgehalt verändert werden, zum Ausgleich der Inflation werden darauf prozentuale Zuwächse vorgesehen, deren genauer Wert aber im Gegensatz zum Grundgehalt
geheim bleiben soll, Chefsache wie die Bundeskanzlerin so gerne sagt. Die Alterszulage soll dagegen ewig auf den Wert 50 eingefroren werden. Im Modul »Lohn2013« der
Arbeitsmappe »Syntax.xls« wird das wie folgt geregelt:
Option Explicit
Public Grundgehalt As Double
Private Zuwachs As Double
Public Const Zulage = 50
26
3.3 Ein einführendes Beispiel
Function Lohn(ByVal Alter As Long) As Double
Lohn = (Grundgehalt + Zulage * Alter) * (1 + Zuwachs)
End Function
Sub InitDaten()
Grundgehalt = 1000 ’Darf jeder wissen!
Zuwachs = 0.1
’Chefsache!
End Sub
Private Sub LohnTest()
’ Zwei Anweisungen in einer Zeile!
Dim alt As Long:
alt = 30
MsgBox "Lohn(30): " & Lohn(30)
InitDaten
alt = InputBox("Wie alt sind Sie?", "Alter eingeben", alt)
MsgBox "Sie verdienen " & Lohn(alt) & _
" amerikanische Talerchen!"
End Sub
Einige Bemerkungen zum Code.
1. Die Sub-Prozedur LohnTest() kann über die F5-Taste ausgeführt werden.
2. Jede Zeile darf genau eine Anweisung haben, es sei denn die Anweisungen sind
durch einen Doppelpunkt »:« voneinander getrennt wie hier
Dim alt As Long:
alt = 30.
3. Wenn eine Anweisung zu lang für eine Zeile ist, verwendet man das Fortsetzungszeichen »_« und schreibt in der nächsten Zeile weiter. Das geschieht hier in der
MsgBox-Anweisung.
4. Kommentarzeilen werden in VBA-Programmen durch ein Hochkomma als erstes
Zeichen der Zeile gekennzeichnet. Kommentare sind nur an den Leser des Programms gerichtet, der Interpreter geht über Kommentare kommentarlos hinweg.
Manchmal werden problematische Anweisungen durch das vorangestellte Hochkomma inaktiviert, oder wie die Programmierer sagen, auskommentiert. Kommentare dürfen auch noch nach einer Anweisung in derselben Zeile stehen.
5. Das Schlüsselwort ByVal müssen Sie erst einmal hinnehmen. Es steht vor jedem
Argument einer Sub-Prozedur oder Funktion, aber nur bei deren Deklaration, nicht
beim Aufruf.
6. Der Rückgabewert einer Funktion ist der letzte Wert derjenigen Variablen, die den
Namen der Funktion trägt. Das ist hier gut an der Funktion Lohn() zu sehen.
27
7. Beachten Sie, dass ich den Rückgabewert der MsgBox-Funktion nicht verwende, sodass ich keine Argumentklammern setzen darf! Die Rückgabewerte der InputBoxFunktion sowie die Funktion Lohn() werden dagegen benutzt und daher sind
Argumentklammern erforderlich. Auf diesen Blödsinn muss auch ein krankes Hirn
erst mal kommen.
3.4 Variable
Variable stehen für Bezeichner, denen im Programm ein konstanter Speicherbereich für
die Aufnahme von Werten zugewiesen wird. Der Name Variable rührt daher, dass sich
diese Werte im Verlauf des Programms verändern dürfen. Im Gegensatz zu C unterscheidet Visual Basic nicht zwischen großen und kleinen Buchstaben! Sie dürfen deshalb nicht
eine Variable Hans und eine andere hans nennen, der Interpreter nähme nur an, dass
Sie von nun an Hans klein schreiben wollten.
Variable haben einen bestimmten Datentyp und einen Gültigkeitsbereich. Die Größe
des Speicherbereichs richtet sich nach dem Datentyp der Variablen.
Der Gültigkeitsbereich einer Variablen wird über die gleich erklärten Schlüsselwort
Public, Private und Dim festgelegt.
3.4.1 Datentyp von Variablen
Betrachten wir die Anweisungen
Dim alt As Long:
alt = 30 ’Alt: Alter
der Sub-Prozedur LohnTest, so erkennen wir die Vorteile der Typangaben. Diese informieren den Interpreter über die Größe des Speicherbereiches und die zulässigen Werte.
Zunächst werden vier Byte für die Variable alt reserviert und danach mit dem Wert
0.12 versehen. Die Anweisung alt = "30" würde als unzulässig erkannt, da der Wert
auf der rechten Seite eine Zeichenfolge und keine ganze Zahl ist.
Visual Basic ist anders als C oder Pascal keine streng typisierte Sprache, daher können Variablen auch ohne Typangabe deklariert werden, dann vergibt Visual Basic den
Typ Variant, der stellvertretend für alle Typen steht. Sinnvoll ist das aber nicht. Die
Schlüsselworte Dim, Private oder Public sind aber immer nötig.
3.4.2 Gültigkeit von Variablen
Entscheidend für die Verfügbarkeit oder Gültigkeit einer Variablen ist, wo diese Anweisung innerhalb des Moduls steht. Visual Basic unterscheidet bei Code-Zeilen zwischen
zwei Ebenen, der Modul- und der Prozedurebene.
• Die Modulebene beschreibt den Code, der außerhalb einer Prozedur steht. Das sind
Anweisungen und Deklarationen von Variablen mit Bedeutung für den gesamten
Modul oder das ganze Projekt. Deklarationen von Variablen für die Modulebene
müssen zuerst aufgelistet werden, danach folgen die Prozeduren.
28
3.4 Variable
• Die Prozedurebene beschreibt Anweisungen, die sich innerhalb einer Prozedur befinden. Deklarationen werden üblicherweise zuerst aufgeführt, gefolgt von Zuweisungen und anderem ausführbaren Code.
Innerhalb der Modulebene werden Variablen durch die Schlüsselworte Public oder
Private deklariert. Die allgemeine Form lautet:
[Public][Private]
Variablenname [As Typ]
wobei die Typangabe optional ist, d.h. entfallen kann, wovon ich aber abrate.
Die Deklaration über Public wird auf Modulebene verwendet, um öffentliche Variablen zu deklarieren und den dafür erforderlichen Speicher zu reservieren. Auf Variablen,
die mit der Public-Anweisung deklariert wurden, kann von allen Prozeduren in allen
Modulen aus allen Anwendungen zugegriffen werden. Schreibt man allerdings die Anweisung
Option Private Module
in der Modulebene, sind die Variablen nur innerhalb des zugehörigen Projekts öffentlich.
Variablen vom Typ Private stehen nur in dem Modul zur Verfügung, in dem sie deklariert wurden. Statt Private ist auch Dim erlaubt. Beide Anweisungen sind gleichwertig, ich bevorzuge wegen der besseren Lesbarkeit Private. Variablen, die damit
auf Modulebene deklariert wurden, stehen allen Prozeduren innerhalb des Moduls zur
Verfügung.
Beim Schreiben des Codes beginnt man immer mit Variablen und Konstanten, die für
das gesamte Modul Gültigkeit haben sollen, erst dann folgen die Sub-Prozeduren und
Funktionen.
Auf Prozedurebene werden Variablen durch das Schlüsselwort Dim deklariert, nicht
erlaubt sind hier Private oder oder Public. Solche Variablen sind nur innerhalb der
Prozedur gültig, in der sie definiert wurden. Variablen sollten innerhalb einer Prozedur
immer ganz oben vor allen anderen Anweisungen deklariert werden.
Im Eingangsbeispiel sind die Variablen Grundgehalt und Zuwachs außerhalb der
Prozeduren, d.h. auf Modulebene deklariert und dürfen damit in jeder Prozedur des
Moduls verwendet werden, die Variable Grundgehalt sogar im ganzen Projekt.
Die Variable alt ist nur innerhalb der Sub-Prozedur LohnTest() gültig.
3.4.3 Initialisierung von Variablen
Bei der Deklaration versucht Visual Basic den Variablen sinnvolle Anfangswerte zu geben. Numerische Variable erhalten der Wert 0, das trifft im obigen Beispiel auf die Variablen Grundgehalt, Zuwachs und alt zu. Zeichenfolgen vom Typ String werden
mit der leeren Zeichenfolge "" initialisiert.
Benutzt man die Funktion Lohn(30) vor dem Aufruf der Prozedur InitDaten(),
ergibt sich der Wert
0 + 30 ∗ 50 ∗ (1 + 0) = 1500,
29
denn die Variablen Grundgehalt und Zuwachs haben noch die Werte 0. Nach dem
Aufruf von InitDaten() ergibt sich dagegen der Wert
1000 + 30 ∗ 50 ∗ (1 + 0.1) = 2750.
3.4.4 Option Explicit-Anweisung
Die Anweisung
Option Explicit
wird auf der Modulebene verwendet, um die explizite Deklaration aller Variablen in
diesem Modul zu erzwingen. Wenn diese Anweisung vorhanden ist, muss sie im jeweiligen
Modul vor jeder Prozedur stehen.
Wenn Sie die Option Explicit-Anweisung in einem Modul verwenden, müssen alle Variablen explizit mit Dim, Private, Public, ReDim oder Static deklariert werden.
Wenn Sie einen nicht deklarierten Variablennamen verwenden, tritt zur Kompilierungszeit ein Fehler auf. Dadurch vermeidet man den versehentlichen Gebrauch von Variablen
mit falsch geschriebenen Namen.
Wenn Sie die Option Explicit-Anweisung nicht verwenden, erhalten alle nichtdeklarierten Variablen den Typ Variant, solange mit einer DefTyp-Anweisung kein anderer
Standardtyp festgelegt wird.
Die Anweisung Option Explicit wird automatisch in einen neu erstellten Modul
eingefügt, wenn im Dialogfeld »Optionen«, das in der Entwicklungsumgebung von VBA
über den Befehl Extras|Optionen. . . erreichbar ist, auf der Registerkarte »Editor«
die Option »Variablendeklaration erforderlich« aktiviert wird.
Quelle: Excel-Hilfe.
3.5 Konstanten
Konstante sind wie Variable benannte Elemente für feste, vordefinierte Werte von Zahlen oder Zeichenfolgen, die während der gesamten Programmausführung nicht geändert
werden können. Eine Konstante kann durch andere Konstante über arithmetische oder
logische Operatoren gebildet werden. Es gibt unzählige von Visual Basic vordefinierte
Konstante. Jedes Projekt kann zusätzliche Konstanten mit der Const-Anweisung definieren. Durch die Verwendung von Konstanten anstelle der tatsächlichen Werte wird der
Programmcode leichter lesbar. Außerdem muss der Wert einer Konstanten nur einmal
geändert werden, wenn dies durch äußere Umstände nötig sein sollte.
Die Deklaration von Konstanten muss auf Modulebene vor allen Prozeduren erfolgen!
Vor dem Bezeichner muss das Schlüsselwort Const stehen und nach dem Typ muss die
Wertzuweisung folgen. Davor darf mit Public oder Private die Gültigkeit festgelegt
werden. Wenn diese fehlt, wird Public angenommen. Die allgemeine Form ist also
[Public][Private] Const Bezeichner [As Typ] = Ausdruck
30
3.6 Gültigkeit von Prozeduren
Dabei sind alle Datentypen zulässig. Wird der Typ nicht angegeben, ermittelt Visual
Basic ein zum Wert passender Typ . Gültige Beispiele von Konstanten sehen Sie jetzt.
Private Const KurzPi = 3.14
Public Const LangPi As Double = KurzPi + 0.00159
Public Const GebDatum = "19.1.1971"
Nur die Konstante LangPi habe ich mit Typ deklariert, die Typen der beiden anderen
Konstanten werden von Visual Basic selbst eingesetzt. Nach der Deklaration können die
Konstanten GebDatum und LangPi in allen Prozeduren des Projekts und KurzPi in
allen Prozeduren des Moduls benutzt werden, sofern sie typgerecht behandelt werden.
Abschließend sei noch erwähnt, dass konstante Zeichenfolgen wie etwa "Hallo" als
Literale bezeichnet werden.
Im Eingangsbeispiel ist die Konstante Zulage auf Modulebene vor den Prozeduren
deklariert und darf damit in jeder Prozedur des Moduls und wegen Public sogar des
ganzen Projekts verwendet werden.
3.6 Gültigkeit von Prozeduren
Prozeduren sind grundsätzlich in allen Modulen des Projekts, ja sogar in allen Modulen aller Anwendungen verfügbar, dazu ist anders als bei Variablen das Schlüsselwort Public nicht nötig, dürfte aber zur besseren Lesbarkeit vor dem Schlüsselwort
Sub bzw. Function stehen.
Prozeduren können aber durch das vorangestellte Schlüsselwort Private auf die
Verwendung innerhalb der Prozeduren des eigenen Moduls eingeschränkt werden. Ich
habe dies im Eingangsbeispiel mit der Prozedur LohnTest() getan.
In einem weiteren Modul namens »Gueltigkeit« habe ich die folgende Prozedur geschrieben:
Option Explicit
Sub Ausgabe()
InitDaten
MsgBox "Grundgehalt: " & Grundgehalt _
& vbNewLine & "Lohn im Alter 30: " & Lohn(30)
’Nicht erlaubt in einem anderen Modul
’MsgBox "Zuwachs = " & Zuwachs
’LohnTest
End Sub
Ich darf die Prozedur InitDaten() und die Variable Grundgehalt verwenden, da
der Gültigkeitsbereich über das eigene Modul »Lohn2013« hinausgeht. Die beiden auskommentierten Anweisungen wiese der Interpreter ab, da die Variable Zuwachs und die
Prozedur LohnTest() in ihrer Gültigkeit auf das Modul »Lohn2013« beschränkt sind.
Um dies aufzuheben, müsste die Variable Zuwachs mit Public deklariert werden und
vor der Prozedur LohnTest() ist Private zu streichen oder durch Public zu ersetzen.
31
3.7 Einfache Datentypen
Abhängig vom Typ einer Variablen legt der Interpreter Speicherplatz an und erkennt
unverträgliche Anweisungen. Die wichtigsten Basistypen von Visual Basic stehen für
ganze Zahlen und Gleitkommazahlen sowie für logische Ausdrücke.
3.7.1 Basistypen für Zahlen
• Den Datentyp Byte erwähne ich nur der Vollständigkeit halber. Variablen diesen
Datentyps beanspruchen ein Byte Speicherplatz und haben einen Wertebereich von
0 bis 255.
• Variablen vom Datentyp Integer werden als 16-Bit-Zahlen (2 Bytes) in einem
Bereich von -32.768 bis 32.767 gespeichert. Ich vermeide diesen Datentyp grundsätzlich, da der Zahlenbereich zu klein ist und schnell zu Überlauffehlern führt.
• Variablen vom Datentyp Long (lange Ganzzahl) werden als 32-Bit-Zahlen (4 Bytes) mit Vorzeichen im Bereich von -2.147.483.648 bis 2.147.483.647 gespeichert.
• Variablen vom Datentyp Single (Gleitkommazahl mit einfacher Genauigkeit)
werden als 32-Bit-Gleitkommazahlen (4 Bytes) nach IEEE für negative Werte im
Bereich von -3,402823E38 bis -1,401298E-45 und von 1,401298E-45 bis 3,402823E38
für positive Werte gespeichert.
• Variablen vom Datentyp Double (Gleitkommazahl mit doppelter Genauigkeit)
werden als 64-Bit-Gleitkommazahlen (8 Bytes) nach IEEE für negative Werte im
Bereich von -1,79769313486231E308 bis -4,94065645841247E-324 und für positive
Werte von 4,94065645841247E-324 bis 1,79769313486232E308 gespeichert.
• Der Datentyp Currency hat einen Wertebereich von -922.337.203.685.477,5808
bis 922.337.203.685.477,5807. Das sind Festkommazahlen mit 15 Stellen vor und 4
Stellen nach dem Komma. Auch dieser Datentyp benötigt acht Byte. Verwenden
Sie diesen Datentyp für Berechnungen von Währungen und für Berechnungen mit
festgelegten Nachkommastellen, bei denen es besonders auf Genauigkeit ankommt.
• Der Datentyp Decimal reicht bis zu ±79.228.162.514.264.337.593.543.950.335. Je
größer der Absolutbetrag der Zahl ist, umso weniger Nachkommastellen gibt es. Im
Bereich zwischen ±10 sind 27 Nachkommastellen möglich, mit jeder Zehnerpotenz
muss eine Nachkommastelle geopfert werden. Die kleinsten Zahlen ungleich Null
sind ±0, 0000000000000000000000000001 Dieser Datentyp benötigt 14 Byte.
32
3.7 Einfache Datentypen
3.7.2 Überlauf bei ganzen Zahlen
Alle arithmetischen Rechnungen mit ganzen Zahlen werden von Computern nur dann
korrekt ausgeführt, wenn das Ergebnis und alle Zwischenrechnungen immer im Wertebereich des verwendeten ganzzahligen Typs bleiben. Sobald eine arithmetische Operation
zu einem Wert außerhalb dieses Bereiches führt, kommt es zu einem Überlauf genannten
Fehler. Visual Basic bricht dann mit dieser Fehlermeldung ab. Im Modul »Ueberlauf«
habe ich diese Funktion untergebracht:
Sub UeberlaufTest()
Dim a As Integer, b As Integer
a = 16000
b = 17000
MsgBox "a + b = " & a + b
End Sub
Lässt man diese Sub-Prozedur laufen, gibt es eine Fehlermeldung mit Programmabruch.
Dies wäre durch Verwendung des Datentyps Long statt Integer leicht zu vermeiden
gewesen, aber natürlich kommt es auch bei diesem Datentyp zum Überlauf, aber erst
bei größeren Werten.
3.7.3 Der logische Datentyp
Der logische Datentyp Boolean modelliert alle zweiwertigen Zustände wie an und aus
oder ja und nein, und hat deshalb lediglich zwei Werte, nämlich True und False.
Logische Variable entstehen meist aus arithmetischen Vergleichen, etwa
Dim b As Boolean
b = i > 2
Dabei erhält b den Wert True, wenn die Variable i größer ist als 2.
3.7.4 Bedingte Anweisungen
Ganz eng mit dem logischen Datentyp ist die bedingte Anweisung verwandt, die ich
deshalb in diesen Abschnitt einschiebe, wo sie eigentlich nicht hingehört. Die Syntax
lautet:
If (logischer Ausdruck) Then Anweisung
Dabei wird der logische Ausdruck zur besseren Lesbarkeit in Klammern gesetzt, was
zwar in C++ aber nicht in Visual Basic erforderlich ist. Die nachfolgende Anweisung
wird nur ausgeführt, wenn der Wert des logischen Ausdrucks True ist. Die meisten
logischen Ausdrücke entstehen aus arithmetischen Vergleichen von zwei numerischen
Ausdrücken. Eine solche Bedingung ist ein Ausdruck, der wie in der zweiwertigen Logik
üblich nur True oder False sein kann. Ich habe mir folgende hirnerweichende SubProzedur als Beispiel ausgedacht und ebenfalls im Modul »Ueberlauf« untergebracht.
33
Sub IfTest()
Dim l As Long
l = InputBox("Bitte Länge in cm eingeben!", "Titel", "")
If (l > 200) Then MsgBox "Sie sind ein langer Lulatsch!"
End Sub
Die Meldung erscheint also nur, wenn die in Klammern stehende Bedingung wahr ist,
also mindestens 201 eingegeben wird.
3.8 Ausdrücke und Anweisungen
Ein Ausdruck entsteht aus Konstanten, Variablen sowie anderen Ausdrücken und Operatoren und hat immer einen Wert und einen Typ. Je nach Typ des Ausdrucks und der
Art der beteiligten Operatoren wird zwischen arithmetischen und logischen Ausdrücken
unterschieden. Durch das Zeilenende wird aus einem gültigen Ausdruck eine Anweisung.
Gültige Ausdrücke finden Sie in der folgenden Tabelle.
Tabelle 3.2: Ausdrücke
Ausdruck
Bemerkung
Wert
3
Eine Konstante ist ein Ausdruck
3
x="Gerd"
Eine Zuweisung ist ein Ausdruck
"Gerd"
3/4
12/5 < 3
ganzzahlige Division gibt es in Visual Basic 0.75
nicht!
arithmetischer Vergleich
True
"Helle"<"Gerd"
Vergleich von Zeichenfolgen
False
i = b = 2
(b = 2)ist Ausdruck mit Wert 2, der danach
i zugewiesen wird.
2
3.9 Operatoren
Ein Operator wie etwa +, -, * oder / verknüpft ein, zwei oder mehrere Operanden zu
einem Ausdruck, dessen Wert sich aus den Werten der einzelnen Operanden ergibt. In
Visual Basic gibt es nur unäre (ein Operand) und binäre (zwei Operanden) Operatoren. Viele Operatoren sind mathematischer Natur und haben das in der Mathematik
übliche Symbol. Wie in der Mathematik gibt es eine Vorrangliste, die über die Reihenfolge der Auswertung entscheidet. Es gilt wie üblich die Priorität von Multiplikation
und Division über Addition und Subtraktion, Punktrechnung geht vor Strichrechnung.
Die Auswertung wird durch Setzen von runden Klammern bestimmt, da diese höchste
34
3.9 Operatoren
Priorität haben. Es gibt neben den arithmetischen auch noch logische Operatoren. Alle
arithmetischen Operatoren haben Zahlen als Operanden.
3.9.1 Arithmetische Operatoren
Es gibt drei Arten von arithmetischen Operatoren:
• Der unäre Vorzeichenoperator -.
• Die aus der Algebra bekannten binären Operatoren für Zuweisung, Addition, Subtraktion, Multiplikation, Division und Rest, also =, +, -, *, /, Mod.
Auf den Operator Mod komme ich später noch zurück. Er gibt den Rest bei ganzzahliger
Division an, d.h. 17 Mod 4ist gleich 1.
3.9.2 Der Zuweisungsoperator
Der Zuweisungsoperator ist das Gleichheitszeichen »=«. Selbst in diesem harmlosen
Operator ist eine kleine Tücke für den Anfänger enthalten. Die Zuweisung
a = b + 3 + 3*f
erfolgt wie in der Mathematik. Der Ausdruck auf der rechten Seite wird ausgewertet
und der Wert wird der Variablen auf der linken Seite zugewiesen. Daher muss der Typ
der Variablen auf der linken Seite mit dem Typ des Ausdrucks auf der rechten Seite
übereinstimmen. Ist dies nicht der Fall, versucht der Interpreter den Wert des Ausdruckes
auf der rechten Seite in den Typ der Variablen links umzuwandeln. Ist dies nicht möglich,
so wird die Übersetzung mit einer Fehlermeldung abgebrochen.
In fast allen Programmiersprachen sind aber auch Ausdrücke der Form
a = 3*a + 2
erlaubt. In der Algebra wäre dies eine Gleichung für a mit der Lösung a = −1. In Visual
Basic aber wird zunächst der Ausdruck auf der rechten Seite mit dem bisherigen Wert
von a berechnet und das Ergebnis wird der Variablen a als neuer Wert zugewiesen. Sei
etwa 3 der alte Wert von a, so wird a durch diese Anweisung 11.
Wie alle Ausdrücke hat auch die Zuweisung ein Ergebnis, nämlich den zugewiesenen
Wert, und dieser kann nun einer anderen Variablen zugewiesen werden, also sind innerhalb einer Anweisung viele Zuweisungen möglich. Die Auswertung geschieht dabei von
rechts nach links:
a = b = c = 3*d + 1
Alle drei Variablen haben jetzt den Wert 3*d + 1.
35
3.9.3 Der Modulooperator
Ein Jahr hat bekanntlich etwas mehr als 365 Tage, in erster Näherung 365,25 Tage. Normale Kalenderjahre sind also um einen Vierteltag zu kurz! Daher legen die Schaltjahre
alle vier Jahre einen Extratag zu, den 29.2., um in einem Schlag den verlorenen Tag
wieder aufzuholen. Die Jahreszahlen dieser Jahre sind durch vier teilbar.
Wie aber wird dies programmiert? Dazu werfen wir einen Blick zurück in die Grundschule. Dort wird 17 geteilt durch 3 gleich 5 Rest 2. Bei ganzzahliger Division bleibt
immer ein Rest, es sei denn, der Zähler ist durch den Nenner teilbar. Der Rest heißt
vornehm Modulo, der entsprechende Operator Modulooperator . Der Modulooperator
hat kein eigenes Zeichen, sondern man schreibt Mod. Somit ist 17 Mod 4 gleich 1,
16 Mod 4 gleich 0 und 22 Mod 4 gleich 2.
Folgende Sub-Prozedur aus dem Modul »Ueberlauf« liest aus der Zelle A2 des ersten Arbeitsblatts eine Jahreszahl ein und gibt in der Zelle B2 »Ja« bzw. »Nein« aus,
abhängig davon ob die Jahreszahl zu einem Schaltjahr gehört oder nicht:
Sub IstSchalt()
Dim s As String
Dim j as Long
j = Sheets(1).Range("A2").Value
s = "Nein"
If (j Mod 4 = 0) Then s = "Ja"
Sheets(1).Range("B2").Value = s
End Sub
Zunächst hat die Variable s den Wert »Nein«. Ist die Bedingung j Mod 4 = 0 erfüllt,
z.B. für 1996, so ändert sich ihr den Wert auf »Ja«.
3.9.4 Vergleichsoperatoren
Logische Ausdrücke entstehen durch Vergleiche, etwa a < b, was nur wahr oder falsch
sein kann. Die folgenden sechs Vergleichsoperatoren vergleichen zwei Ausdrücke x und
y bezüglich ihrer Anordnung:
Tabelle 3.3: Vergleichsoperatoren
Syntax
Beschreibung
Syntax
Beschreibung
x > y
x größer als y
x < y
x kleiner als y
x >= y
x größer gleich y x <= y
x kleiner gleich y
x =y
x gleich y
x ungleich y
x <> y
Dabei können x und y Zahlen oder Zeichenfolgen sein. Zahlen werden nach Größe
und Zeichenfolgen alphabetisch geordnet, deshalb sind sowohl 3 < 4 als auch "drei"<
36
3.9 Operatoren
"vier" wahr. Alle Vergleichsoperatoren führen zum Wert True, falls die Bedingung
erfüllt ist und sonst entsprechend zu False.
3.9.5 Logische Operatoren
Die Vergleichsoperatoren tauchen meistens innerhalb der If-Anweisung und Wiederholungen auf und werden dabei gerne mit den nun betrachteten logischen Operatoren verknüpft. Wenn wir etwa das vollständige Datum abfragen und danach die Jahreszeit
ausgeben wollen, müssen wir im März unterscheiden zwischen den Tagen 1 bis 20, die
noch zum Winter zählen, und dem zum Frühjahr gehörenden Rest. Wir benötigen also
eine Bedingung, die aus der Verknüpfung von zwei arithmetischen Vergleichen besteht:
If (mon = 3 And tag > 20 ) Then _
MsgBox "Veronika, der Lenz ist da!"
Zwei logische Ausdrücke können wie in der Umgangssprache durch »UND« sowie
»ODER« verknüpft werden. Eine Bedingung, die durch zwei mit »UND« verbundene
Einzelbedingungen entsteht, ist genau dann erfüllt, wenn beide Bedingungen wahr sind.
Die Verknüpfung mit »ODER« weicht von der Umgangssprache ab, wo oder häufig
entweder, oder meint, wie in dem Vorhaben CDU oder FDP zu wählen. Anders ist die
Bedeutung von oder in der Aussage, Milch gibt es bei Edeka oder Aldi. Es wird erwartet,
Milch in mindestens einem Laden zu finden, möglicherweise auch in beiden. Genauso
wird »ODER« in der Logik verstanden. Eine Bedingung, die aus zwei mit »ODER«
verbundenen Bedingungen entsteht, ist genau dann zutreffend, wenn wenigstens eine
Bedingung erfüllt ist.
Neben »UND« sowie »ODER« gibt es noch drei weitere binäre logische Operatoren,
die man aber nur selten benötigt. Werden zwei logische Ausdrücke X und Y durch das
»exklusive ODER« verbunden, ist das Ergebnis genau dann True, wenn genau einer der
beiden Operatoren True ist. Verknüpft man X und Y durch den Operator Eqv, der
für »äquivalent« steht, so ist das Ergebnis nur True, wenn beide Operatoren denselben
Wert haben. Der Implikationsoperator heißt Imp. Das Ergebnis X Imp Y ist immer
True, außer wenn X den Wert True hat und dann Y = False ist.
Die Verneinung oder Negation wird durch den unären Operator Not realisiert, der
nur auf einen Ausdruck wirkt. Das Ergebnis ist das logische Gegenteil des Ausdrucks.
Die sechs logischen Operatoren von Visual Basic sind in folgender Wahrheitstabelle
zusammengefasst, wobei X und Y die Operanden bezeichnen.
Typische Beispiele sind:
If(a = 3 And c <> 5) Then Anweisung
If(a < 6 Or x > 4) Then Anweisung
If(Not(a < 6 Or x > 4)) Then Anweisung
Manchmal muss geprüft werden, ob eine Variable x einen Wert innerhalb der Grenzen
a und b hat. Dies geschieht korrekt wie folgt:
If(x > a And x < b) Then Anweisung
während die folgende verkürzte Form zwar gut gemeint, aber syntaktisch falsch ist:
37
Tabelle 3.4: Logische Operatoren
Operator
Bedeutung
Ergebnis = True, wenn
Not X
Negation
X = False
X And Y
Konjunktion
X = True und Y = True
X Or Y
inklusives Oder
mindestens ein Ausdruck = True
X Xor Y
exklusives Oder
genau ein Ausdruck = True
X Eqv Y
Äquivalenz
beide Ausdrücke gemeinsam = True oder False
X Imp Y
Implikation
außer wenn X = True und Y = False
If(b > x > a) Then Anweisung
3.9.6 Vorrangregeln
Wie beim gewöhnlichen Rechnen hält sich auch der Interpreter von Visual Basic an so
genannte Vorrangregeln bei der Auswertung von Ausdrücken mit mehreren Operatoren.
Diese habe ich in der Tabelle 3.5 zusammengestellt.
Tabelle 3.5: Vorrangregeln für Operatoren
Operator
Beschreibung
. oder !
Zugriff auf Methode oder Attribut
()
Zugriff auf Vektorelement über Index
()
Klammersetzen, Funktionsaufruf
Not
logische Verneinung
∗, /, Mod
Multiplikation, Division, Modulo
+, −
Addition und Subtraktion
<, <=, >, >= arithmetische Vergleiche
=, <>
arithmetische Gleichheit und Ungleichheit
And
logisches UND
Or
logisches ODER
=
Zuweisung
Alle Operatoren innerhalb einer Zeile haben dieselbe Priorität und die Priorität von
nimmt oben nach unten ab.
38
3.10 Aufgaben
Diese Regeln ersparen das Setzen von Klammern, so dass wie beim Rechnen der Ausdruck
2*3 + 4*5
den Wert 26 hat und nicht 50, was sich bei sturer Auswertung von rechts nach links ergibt. Auch die Kombination von arithmetischen und logischen Operationen funktioniert
ohne Klammern. Denn z.B.
a + b < c*d
wird vom Übersetzer als
(a + b) < (c*d)
interpretiert.
3.10 Aufgaben
1. In Deutschland gab es Münzen mit Werten von 5, 2 und 1 DM, sowie von 50, 10,
5, 2 und 1 Pfennig(en). Schreiben Sie eine Sub-Prozedur, die aus dem Feld B1 des
ersten Arbeitsblatts einen vom Anwender dort eingegebenen Betrag ausliest und
durch eine minimale Zahl dieser Münzen ausdrückt. Ein Betrag von 13,88 DM wird
also durch 2 Fünf-, und je einem Zwei- und Einmarkstück, sowie einer Münze zu 50
Pfennig, 3 Münzen mit 10 Pfennig und je einer der restlichen Münzen gebildet. Die
Anzahl der einzelnen Münzen sind dann ebenfalls in geeigneten Zellen des ersten
Arbeitsblatts auszugeben. Hinweis: Wandeln Sie den Betrag in Pfennige um, und
verwenden Sie die Operatoren »/« und »Mod«.
2. Schreiben Sie eine Visual Basic Sub-Prozedur, die aus zwei geeigneten Zellen eines
Arbeitsblatts Monat und Tag einliest und in einer anderen Zelle »Frühjahr« oder
»kein Frühjahr« ausgibt. Frühjahr ist die Jahreszeit zwischen dem 21.3 und dem
20.6 eines Jahres. In einer erschwerten Version können Sie natürlich auch zum
Datum die richtige Jahreszeit ausgeben.
3. Schreiben Sie eine Visual Basic Sub-Prozedur, die aus einer geeigneten Zelle eines
Arbeitsblatts das Jahr einliest und nachprüft, ob es sich um ein Schaltjahr handelt.
In einer anderen Zelle soll das Ergebnis ausgegeben werden. Schaltjahre sind seit
1582 alle Jahre, die durch 400 teilbar sind oder durch 4 teilbar, dann aber nicht
durch 100. Somit waren 1996, 2000, 2004 Schaltjahre, nicht aber 1900 und 1997.
4. Schreiben Sie eine Visual Basic Sub-Prozedur, die aus zwei geeigneten Zellen eines Arbeitsblatts Jahr und Monat einliest und in einer anderen Zelle die Anzahl
der Tage dieses Monats ausgibt. Verwenden Sie ausschließlich einfache bedingte
Anweisungen mit Bedingungen, die durch Vergleichs- und logischen Operatoren
gebildet werden.
39
Komplexe Datentypen setzen sich aus einfachen Datentypen zusammen. Zeichenfolgen
bestehen aus einer festen oder beliebigen Zahl von Zeichen. Datenfelder setzen sich aus
einer festen oder beliebigen Zahl von Elementen desselben Typs zusammen. Datenfelder
werden auch Vektoren oder Arrays genannt. Diese beiden komplexen Datentypen sind
von Visual Basic bereits vorgesehen. Man darf aber auch selbst aus bereits bestehenden
Typen neue, so genannte benutzerdefinierte Datentypen zusammenstellen. Dabei werden
logisch zusammengehörende Einzeltypen zu einem Informationsblock zusammengefasst.
Die einzelnen Bestandteile dürfen dabei die Basistypen von Visual Basic als auch selbst
wieder benutzerdefinierte Datentypen sein.
4.1 Arbeitsmappe
Hinweis
»VBADatentypen«, die Sie von der Seite
//htm
4.2 Zeichenfolgen
Eine Zeichenfolge besteht aus einer Folge von Zeichen. Jedes einzelne Zeichen wird durch
eine Zahl im Bereich von 0 bis 255 dargestellt. Die ersten 128 Zeichen (0 bis 127) entsprechen den Buchstaben und Symbolen auf einer US-amerikanischen Standardtastatur
und stimmen mit den im ASCII-Zeichensatz definierten Zeichen überein. Die zweiten
128 Zeichen (128 bis 255) sind Sonderzeichen, z.B. Buchstaben aus internationalen Alphabeten, Akzentzeichen, Währungssymbole und Symbole für mathematische Brüche.
Konstante Zeichenfolgen werden in Anführungsstriche gesetzt, etwa "Hallo", und Literale genannt. Es gibt zwei Arten von Zeichenfolgen: Zeichenfolgen variabler Länge und
Zeichenfolgen fester Länge.
• Zeichenfolgen variabler Länge können bis zu 2 Milliarden (oder 231 ) Zeichen enthalten.
• Zeichenfolgen fester Länge können 1 bis etwa 64 KB (216 ) Zeichen enthalten.
40
4.2 Zeichenfolgen
Die zugehörigen Datentypen heißen String und String*k, wobei k die beliebige,
aber feste Länge der Zeichenfolge ist. Der Name leitet sich von der englischen Bezeichnung für Zeichenfolgen ab.
Wie es genau geht, sehen Sie im folgenden Beispiel aus dem Modul »Zeichenfolgen«:
Sub StringTest()
Dim SVar As String
Dim SFest As String * 5
SVar = "Angela"
SFest = "Angela"
MsgBox "SVar = " & vbTab & SVar & vbNewLine & _
"SFest = " & vbTab & SFest
End Sub
Die Variable SVar ist variabel, während die Variable SFest die Länge 5 hat. Zeichenfolgen mit fester Länge können nur so viele Zeichen aufnehmen, wie es die Größe
gestattet, daher ist für das »a« kein Platz. Ist der Inhalt der Zeichenfolge zu kurz, werden
die restlichen Zeichen mit Leerzeichen belegt.
Das Meldungsfenster gibt somit folgenden Text aus:
SVar =
SFest =
Angela
Angel
Den Zeilenumbruch und die Formatierung durch Tabulatoren erkläre ich gleich.
4.2.1 Vordefinierte Zeichenfolgen
Das Office-Paket stellt eine umfangreichen Vorrat an integrierten Konstanten zur Verfügung. Diese können Sie im Objektkatalog von VBA einzusehen. Der Präfix des Namens einer Konstanten deutet auf ihre Herkunft hin. Konstanten mit dem Präfix »vb«
stammen beispielsweise aus der Objekt-Library von VBA, solche mit dem Präfix »xl«
beziehen sich auf Excel. In der folgenden Tabelle sind einige wichtige Konstanten und
ihre Bedeutung aufgelistet:
Tabelle 4.1: Vordefinierte Zeichenfolgen
Konstante
Bedeutung
vbCr
vbLf
Wagenrücklaufzeichen, bringt Cursor zurück an erste Stelle der Zeile.
Neue Zeile Zeilenvorschubzeichen, verschiebt Cursor um eine Zeile nach
unten.
vbCrLf
Kombination aus beiden.
vbNewLine Plattformspezifisches Zeilenumbruchzeichen; je nachdem, welches für
die aktuelle Plattform geeignet ist, also Windows, Mac oder DOS.
vbTab
Tabulator.
41
4.2.2 Der Verkettungsoperator & für Zeichenfolgen
Der Verkettungsoperator & verknüpft zwei Ausdrücke, wovon mindestens einer eine Zeichenfolge sein muss. Der andere Ausdruck kann alphanumerisch sein. Der rechte Ausdruck wird dabei an den linken angehängt. Für Leerzeichen müssen Sie dabei selbst
sorgen. Die folgende Sub-Prozedur ist ebenfalls Teil des Moduls »Zeichenfolgen«:
Sub TestEt()
Dim s As String, t As String, j As Long
s = "Hallo"
t = " Welt im Jahr " ’Leerzeichen beachten!
j = 2009
t = s & t & j & "!"
MsgBox t
End Sub
Der String t hat jetzt den Wert "Hallo Welt im Jahr 2009!". Ich verwende den
Operator & gerne, um eine sehr lange Zeichenfolge zu erzeugen, die nicht in eine Zeile
passt:
s = "Blah, blah, blah, blah, blah, blah, blah, blah, blah,"
s = s & " Dacapo: blah"
Der Verkettungsoperator wandelt numerische Ausdrücke bei Bedarf in Zeichenfolgen
um:
s = "Pi ist ungefähr " & 3.14
Damit trägt s den Text "Pi ist ungefähr 3.14".
4.2.3 Nützliche Stringfunktionen
Für Zeichenfolgen gibt es viele nützliche Funktionen. Einige davon möchte ich erwähnen.
• Die Funktion Len(s) gibt die Anzahl der Zeichen des Arguments s zurück.
• Die Funktion LCase(s) liefert eine Zeichenfolge, in der jeder große Buchstabe
von s durch einen kleinen ersetzt wird. Die anderen Zeichen bleiben unverändert.
• Die Funktion UCase(s)liefert eine Zeichenfolge, in der umgekehrt jeder kleine
Buchstabe von s durch einen großen ersetzt wird. Die anderen Zeichen bleiben
unverändert.
• Die Funktion Left(s, k), wobei s eine Zeichenfolge und k eine ganze Zahl
sein müssen, liefert den Teilstring von s, der von den ersten k Zeichen gebildet
wird.
• Die Funktion Right(s, k), wobei s eine Zeichenfolge und k eine ganze Zahl
sein müssen, liefert den Teilstring von s, der von den letzten k Zeichen gebildet
wird.
42
4.2 Zeichenfolgen
• Die Funktion Mid(s, i, k), wobei s eine Zeichenfolge und i sowie k ganze
Zahlen sein müssen, liefert den Teilstring von s, der beim Zeichen i beginnt und
beim Zeichen (i + k - 1) endet, also von der Position i an genau k Zeichen
umfasst.
• Die Funktion InStr(pos, s, teilstr) sucht innerhalb der ganzen Zeichenfolge s die Zeichenfolge teilstr. Die Suche wird an der Position pos begonnen.
Der Index des ersten Auftauchens wird zurückgegeben, also eine ganze Zahl. Ist
die Zeichenfolge teilstr innerhalb der Zeichenfolge s nicht zu finden, wird die
Zahl 0 zurückgegeben. Der erste Parameter darf fehlen werden, die Suche beginnt
dann vom ersten Zeichen an.
Der Ausdruck Mid(s, i, 1) liefert die Zeichenfolge, die aus dem Zeichen an der
Stelle i besteht. Bitte beachten Sie, dass VBA nur den Typ String kennt, aber keinen
eigenen Typ für ein einzelnes Zeichen.
Streng genommen liefern die erwähnten Funktionen nicht den Datentyp String,
sondern den Datentyp Variant. Man kann die Rückgabe des Typs String erzwingen,
indem nach dem Funktionsnamen ein Dollarzeichen folgt, also z.B. Left$(s, k) statt
Left(s, k). Das ist aber unnötig und tut auch keiner.
Auch hierzu eine Testprozedur aus dem Modul »Zeichenfolgen«. Am Beispiel der Zeichenfolge »Hallo Welt« werden die erwähnten Funktionen getestet.
Sub StringFunctionTest()
Dim s As String, anf As String, ende As String
Dim mitte As String, Us As String, pi As String
Dim ln As Long
Dim i As Long, j As Long, r As Long
s = "Hallo Welt"
Us = UCase(s)
ln = Len(s)
MsgBox "s = " & s & ", Us = " & Us & ", ln = " & ln
anf = Left(s, 3)
ende = Right(s, 2)
mitte = Mid(s, 7, 2)
MsgBox "anf = " & anf & ", ende = " & ende _
& ", mitte = " & mitte
i = InStr(s, "Welt")
j = InStr(3, s, "Welt")
r = InStr(3, s, "Hallo")
MsgBox "i = " & i & ", j = " & j & ", r = " & r
End Sub
43
Zunächst erscheint im ersten Meldungsfenster der Text »s = Hallo Welt, Us = HALLO
WELT, ln = 10«. Der zweite Meldungstext lautet »anf = Hal, ende = lt, mitte = We«
und der letzte »i = 7, j = 7, r = 0«.
4.2.4 Typumwandlungsfunktionen
Die einfachste Art der Kommunikation mit dem Anwender eines Programms erfolgt über
so genannte Inputboxen, das sind kleine Dialoge mit einer Frage und einem Antwortfeld.
Darin kann der Anwender eine beliebige Eingabe machen, die Visual Basic natürlich als
Zeichenfolge deutet, auch wenn nur Ziffern eingegeben wurden. Die Anwenderin schreibt
21 in ein Textfeld und meint die Zahl 21, das Textfeld jedoch vernimmt die Zeichenfolge
"21".
Visual Basic verweigert alle arithmetischen Operationen mit als Strings verkleideten
Zahlen. Die unentbehrlichen Helfer sind die Typumwandlungsfunktionen
• CInt(Ausdruck)
• CLng(Ausdruck)
• CSng(Ausdruck)
• CDbl(Ausdruck)
• CDec(Ausdruck)
• CCur(Ausdruck)
• CDate(Ausdruck)
welche das Argument Ausdruck in eine Zahl des jeweiligen Typs umwandeln, etwa
CInt() in eine ganze Zahl und CDbl() in eine Gleitpunktzahl. Der Ausdruck muss
dabei so sein, dass die Umwandlung gelingen kann.
Weitere Typumwandlungsfunktionen finden Sie in der Online-Hilfe.
4.2.5 Die IsNumeric-Funktion
Die Typumwandlungsfunktionen setzen vorraus, dass die Argumente auch wirklich als
Zahlen angesehen werden können. Da dies nicht immer gewährleistet ist, gibt es die Funktion IsNumeric(), die True zurückgibt, wenn ihr Argument als Zahl interpretierbar
ist.
Im folgenden Programmstück soll über eine Inputbox ein Alter erfragt werden, der
misstrauische Programmierer testet mit Hilfe der Funktion IsNumeric(), ob die Eingabe wirklich als Zahl vorliegt und formt erst dann die Zeichenfolge in eine Zahl vom
Typ Long um.
44
4.3 Wiederholungen von Anweisungen
Dim i As Long, istr As String
istr = InputBox("Alter", "Alter eingeben", 20)
If (IsNumeric(istr)) Then i = CLng(istr);
Ganze Zahlen sind weltweit unproblematisch, ganz anders als nicht ganzzahlige Werte.
In Europa erwartet die Funktion IsNumeric(), dass ein Dezimalkomma verwendet
wird, ein Punkt wird nämlich als Trennzeichen wie in 1.000 angesehen! In der folgenden
Sub-Prozedur aus dem Modul »Zeichenfolgen« werden zwar sowohl »3,14« als auch
»3.14« als Zahlen gedeutet, aber nur »3,14« wird korrekt in die Zahl 3.14 vom Typ
Double verwandelt, während aus »3.14« die Zahl 314 wird.
Sub CDblTest()
Dim piKomma As String, piPunkt As String
Dim piRichtig As Double, piFalsch As Double
piKomma = "3,14"
If (IsNumeric(piKomma)) Then piRichtig = CDbl(piKomma)
MsgBox "Korrekte Umwandlung: " & piRichtig
piPunkt = "3.14"
If (IsNumeric(piPunkt)) Then piFalsch = CDbl(piPunkt)
MsgBox "Falsche Umwandlung: " & piFalsch
End Sub
4.3 Wiederholungen von Anweisungen
Manchmal kommt es vor, dass eine Anweisung oder mehrere Anweisungen nicht nur
einmal, sondern mehrmals direkt hintereinander ausgeführt werden sollen. Das trifft
besonders oft im Zusammenhang mit Datenfeldern auf, die ich gleich besprechen möchte.
Die allgemeine Syntax der Wiederholung einer Folge von Anweisungen lautet:
Dim i As Long
For i = Anfangswert To Endwert
Anweisung1
Anweisung2
’usw
Next i
Die For-Anweisung wird sehr oft bei Datenfeldern und Zeichenfolgen eingesetzt.
Sachsen und Franken sprechen bekanntlich weder »p« noch »k« oder »t«. In der
folgenden Sub-Prozedur aus dem Modul »Zeichenfolgen« wird in einer Inputbox eine
hochdeutsche Zeichenfolge eingegeben und in einem Meldungsfenster erscheint danach
die Zeichenfolge, worin alle »P« oder »p« durch »B« bzw. »b« ersetzt wurden.
Sub Fraenkisch()
Dim SHoch As String, SFrank As String, i As Long
45
SFrank = InputBox("Geben Sie etwas ein:", "", "Papa")
SHoch = SFrank
For i = 1 To Len(SFrank)
If Mid(SFrank, i, 1) = "P" Then Mid(SFrank, i, 1) = "B"
If Mid(SFrank, i, 1) = "p" Then Mid(SFrank, i, 1) = "b"
Next i
MsgBox "Hochdeutsch:" & vbTab & SHoch & vbNewLine & _
"Fränkisch:" & vbTab & SFrank
End Sub
4.4 Datenfelder (Arrays)
Alle Beispiele zu Datenfeldern befinden sich im Modul »Felder« der Arbeitsmappe
»VBADatentypen.xls«.
Ein eindimensionales Datenfeld enthält aufeinanderfolgende Elemente desselben Datentyps. Jedes Element eines Feldes wird durch eine eindeutige Index genannte Zahl
identifiziert. Wenn bei einem Feldelement Änderungen vorgenommen werden, bleiben
die anderen Elemente davon unbeeinflusst. Die Indizes verlaufen dabei von einer Untergrenze low bis zu einer Obergrenze up. Die Angabe der Obergrenze ist erforderlich,
die Untergrenze kann fehlen, dann erhält sie automatisch den Wert 0. Ein Datenfeld hat
N = up-low+1 Elemente, wobei die natürliche Zahl N Dimension heißt.
Innerhalb einer Prozedur wird ein Datenfeld durch die Dim-Anweisung erzeugt, außerhalb durch Public oder Private anstelle von Dim .
Option Explicit
Public Tage(1 To 31) As Long
Sub Feldtest()
Dim i As Long
Dim WoTage(6) As String
For i = 1 To 31
Tage(i) = i
Next i
WoTage(0)
WoTage(2)
WoTage(4)
WoTage(5)
=
=
=
=
"Sonntag": WoTage(1) = "Montag"
"Dienstag":
WoTage(3) = "Mittwoch"
"Donnerstag"
"Freitag":
WoTage(6) = "Samstag"
MsgBox "Die Woche beginnt am " & WoTage(0) & _
", Monate enden spätestens am " & Tage(31) & "."
End Sub
46
4.4 Datenfelder (Arrays)
Nach dieser Prozedur hat Tage(12) z.B. den Wert 12, während WoTage(5) nur
innerhalb der Prozedur den Wert »Freitag« hat. Am Ende der Prozedur wird das gesamte
Datenfeld WoTage() gelöscht.
Beachten Sie, dass ich für das Datenfeld Tage() Unter- und Obergrenze angegeben
habe, während für WoTage() nur die Obergrenze gesetzt wurde, sodass die Untergrenze
den Wert 0 hat.
4.4.1 Unter- und Obergrenzen eines Datenfelds
Visual Basic erlaubt die Angabe von Unter- und Obergrenze bei der Deklaration eines
Datenfeldes, etwa:
Public ErstesDrittel(1 To 12) As Long
Public ZweitesDrittel(13 To 24) As Long
Public DrittesDrittel(24 To 36) As Long
Benötigen Sie oft 1 als Untergrenze, bietet sich die Anweisung
Option Base 1
an, die nur einmal pro Modul auftreten kann und dort vor allen Deklarationen von
Datenfeldern stehen muss, die Dimensionen enthalten. Bei Datenfeldern dieses Moduls wird dann eine fehlende Untergrenze automatisch 1 gesetzt. Bei anderen Modulen ohne diese Anweisung bleibt die automatische Untergrenze bei 0. Die Funktionen
LBound(Datenfeld) und UBound(Datenfeld) geben die Unter- bzw. Obergrenze des Arguments wieder. Das ist insbesondere bei dynamischen Datenfeldern, die ich
gleich bespreche, vorteilhat.
4.4.2 Dynamische Datenfelder
Sie können ein Datenfeld auch mit einem leeren Klammernpaar deklarieren, die Dimension ist damit zunächst unbestimmt. Danach können Sie mit der ReDim-Anweisung
innerhalb einer Prozedur die Unter- und Obergrenze festlegen. Sie dürfen allerdings nicht
den Typ des Datenfeldes verändern. Diesen Vorgang dürfen Sie beliebig oft wiederholen
und damit die Dimension des Datenfeldes dynamisch zur Laufzeit ändern. Nach jeder
ReDim-Anweisung gehen die bisherigen Elemente des Datenfeldes verloren.
Mit dem Schlüsselwort Preserve können Sie bei eindimensionalen Datenfeldern
die Dimension ändern und die bisherigen Werte erhalten. Im folgenden Beispiel wird die
Dimension eines dynamischen Datenfeldes vergrößert, ohne bereits bestehende Daten im
Datenfeld zu löschen.
Public Faecher() As String
Sub DynArrayneu()
Dim n As Long, i As Long
n = InputBox("Anzahl Faecher: ", "Eingeben!", "")
If (IsNumeric(n) And n > 0) Then ReDim Faecher(n - 1)
47
For i = 0 To n - 1
Faecher(i) = InputBox("Name des Fachs: ", "", "")
Next i
End Sub
Sub DynArrayZu()
Dim n As Long, i As Long, m As Long
m = UBound(Faecher)
n = InputBox("Anzahl neuer Faecher: ", "", "")
If (IsNumeric(n) And n > 0) Then _
ReDim Preserve Faecher(UBound(Faecher) + n)
MsgBox "Obere Grenze: " & UBound(Faecher)
For i = 1 To n
Faecher(m + i) = InputBox("Name des Fachs: ", "", "")
Next i
End Sub
Sub Ausgabe()
Dim i As Long, aus As String
For i = 0 To UBound(Faecher)
aus = aus & Faecher(i) & vbNewLine
Next i
MsgBox aus
End Sub
Sub DynarrayTest()
DynArrayneu
Ausgabe
DynArrayZu
Ausgabe
End Sub
4.4.3 Mehrdimensionale Datenfelder
Mehrdimensionale Datenfelder werden wie eindimensionale definiert, nur muss für jede
Dimension Unter- und Obergrenze angegeben werden. Auch hier wird eine fehlende
Untergrenze durch 0 ersetzt, es sei denn die Anweisung
Option Base 1
setzte die Untergrenze auf 1. Laut Online-Hilfe sind maximal 60 Dimensionen möglich.
Ein zweidimensionales Datenfeld von Werten des Typs Double werde etwa wie folgt
48
4.5 Der Datumstyp Date
deklariert:
Dim Mat(4 TO 8, 9) As Double
Damit hat das Datenfeld (8 − 4 + 1) · 10 Elemente, und zwar von Mat(4, 0) bis
Mat(8, 9). Der Speicherbedarf steigt bei mehrdimensionalen Feldern rapide an, da
die einzelnen Dimensionen multipliziert werden.
Zweidimensionale Datenfelder passen hervorragend zu rechteckigen Bereichen in Arbeitsblättern. Dazu ein kleines Beispiel:
Sub Austausch()
Dim Mat(2, 2) As Variant
Mat(0, 0) = "A1"
Mat(0, 2) = "C1"
Mat(1, 0) = "A2"
Mat(1, 1) = "B2"
Mat(2, 0) = "A3"
Mat(2, 2) = "C3"
Worksheets("Tabelle2").Range("A1:C3").Value = Mat
Worksheets("Tabelle2").Select
End Sub
Damit wird in einigen der Zellen des Bereichs A1:C3 der Bezug der Zelle geschrieben.
Alle nicht erwähnten Zellen werden mit Nullwerten, also ohne Inhalt, belegt, bereits vorhandene ältere Einträge werden gelöscht. Im obigen Beispiel erscheint in den 6 belegten
Zellen der Inhalt, die drei fehlenden bleiben leer, egal, was vorher dort stand.
4.5 Der Datumstyp Date
Variablen vom Datentyp Date werden als 64-Bit-Gleitkommazahlen (8 Bytes) nach
IEEE gespeichert und können ein Datum im Bereich vom 01. Januar 100 bis zum 31.
Dezember 9999 und eine Uhrzeit im Bereich von 0:00:00 bis 23:59:59 speichern. Jeder gültige Wert eines Datums- oder Zeitliterals kann einer Variablen vom Datentyp
Date zugewiesen werden. Ein Datumsliteral muss durch Rauten (#) eingeschlossen sein,
zum Beispiel: #January 1, 1993# oder #1 Jan 93#. Ich hatte am meisten Glück
mit der amerikanischen Kurzform #mm/tt/yy#, also z.B. für meinen Geburtstag am
13.11.2008 mit #11/13/2009#. Es werden also Monat und Tag vertauscht und das
Trennzeichen ist ein normaler Schrägstrich.
Variablen vom Datentyp Date verwenden zur Darstellung des Datums das auf dem
Computer eingestellte kurze Datumsformat. Zeitangaben werden mit dem auf dem Computer eingestellten Zeitformat (entweder 12- oder 24-Stunden) dargestellt.
Beim Umwandeln nummerischer Ausdrücke in Werte des Datentyps Date stehen die
Vorkommastellen für das Datum und die Nachkommastellen für die Uhrzeit. Mitternacht entspricht dem Wert 0, und Mittag entspricht den Nachkommawert 0,5. Negative
ganze Zahlen repräsentieren ein Datum vor dem 30. Dezember 1899. Das amerikanisch
49
geschriebene Datumsliteral #12/30/1899 0:00 am#, also Mitternacht des 30.12.1899,
entspricht dem Wert 0.
In den USA kennt man nur Uhrzeiten von 0 bis 12, unterscheidet dabei zwischen
dem Zeitraum von Mitternacht bis Mittag »am« und von Mittag bis Mitternacht »pm«.
Somit ist »3:30 am« halb vier am Morgen und »3:30 pm« halb vier am Nachmittag oder
15:30. Dabei stehen die Abkürzungen »am« (sprich eiemm) und »am« (sprich piemm)
für ante bzw. post meridiem, lateinisch für vor bzw. nach Mittag.
4.5.1 Nützliche Datums-Funktionen
Für den wichtigen Datentyp Date gibt es viele nützliche Funktionen. Ich werde hier
eine Auswahl vorstellen. Weitere Funktionen finden Sie in der Online-Hilfe, woher ich
auch in diesem Fall mein eigenes Wissen beziehe.
• Die Funktion Now gibt einen Wert vom Typ Date zurück, der das aktuelle Datum
und die aktuelle Zeit aus den Einstellungen für das Systemdatum und die Systemzeit auf Ihrem Computer angibt. Man erhält das Systemdatum als 10-Zeichen lange
Zeichenfolge der Form mm-tt-jjjj zurück, wobei mm (01-12) der Monat, tt (01-30)
der Tag und jjjj das Jahr ist. Dies entspricht dem Datumsbereich vom 1. Januar
1980 bis zum 31. Dezember 2099 des Gregorianischen Kalenders. Der Rückgabe
kann als Variant ausgewertet werden.
• Die Funktion IsDate(d) gibt einen Wert vom Typ Boolean zurück, der angibt, ob der Ausdruck d in ein Datum umgewandelt werden kann. Dies ist genau dann der Fall, wenn der Rückgabewert True ist. Da Ausdrücke vom Typ
Date intern als Gleitkommazahlen dargestellt werden, gib die IsDate-Funktion
für jeden numerischen Ausdruck den Wert True zurück. Die Zahl 0.5 z.B. entspricht dem Datumsliteral #12/30/1899 12:00 am#.
• Die Funktion Day(d) gibt einen Wert zurück, der den Tag des Monats als ganze
Zahl im Bereich von 1 bis 31 angibt.
• Die Funktion Month(d) gibt einen Wert zurück, der den Monat als ganze Zahl
im Bereich von 1 bis 12 angibt.
• Die Funktion Year(d) gibt einen Wert zurück, der das Jahr als ganze Zahl angibt.
• Die Funktion Hour(d) gibt einen Wert zurück, der die Stunde als ganze Zahl im
Bereich von 0 bis 23 angibt.
• Die Funktion Minute(d) gibt einen Wert zurück, der die Minute als ganze Zahl
• Die Funktion Second(d) gibt einen Wert zurück, der die Sekunde als ganze Zahl
50
4.6 Benutzerdefinierte Datentypen
• Die Funktion Weekday(d) gibt einen Wert zurück, der den Wochentag als ganze
Zahl angibt. Die Rückgabewerte verlaufen von 1 für Sonntag bis zu 7 für Samstag,
da Wochen immer an einem Sonntag beginnen.
• Die Funktion DateSerial(jahr,monat,tag) erzeugt einen Wert vom Typ
Date, der dem angegebenen Jahres-, Monats- und Tageszahlen entspricht. Die
Zeit wird dabei auf 0 Uhr gestellt, d.h. der Rückgabewert entspricht intern einer
ganzen Zahl. Somit ist DateSerial(2008,12,24) der Weihnachtstag im Jahr
2008.
• Die Funktion TimeSerial(stu,m,sek) erzeugt einen Wert vom Typ Date,
der der angegebenen Uhrzeit für eine bestimmte Stunde, Minute und Sekunde
entspricht.
Einige der erwähnten Funktionen finden Sie in der folgenden Funktion wieder, die sich
innerhalb des Moduls »Datum« befindet.
Sub DateTest()
Dim Weihnacht As Date, diff As Long
Dim Tage(1 To 7) As String
Tage(1) = "Sonntag"
: Tage(2) = "Montag"
Tage(3) = "Dienstag" : Tage(4) = "Mittwoch"
Tage(5) = "Donnerstag": Tage(6) = "Freitag"
Tage(7) = "Samstag"
Weihnacht = DateSerial(2009, 12, 24) + TimeSerial(18, 0, 0)
’Oder: Weihnacht = #12/24/2009 6:00:00 pm#
diff = DateSerial(2009, 12, 24) - Now + Time
MsgBox "Now: " & Now & ", Time: " & Time & _
", Tag: " & Tage(Weekday(Now)) & "." & _
vbNewLine & diff & "-mal werden wir noch wach, " _
& "heißa dann ist Weihnachtstach!"
MsgBox "0.5 entspricht: " & vbNewLine & _
Day(0.5) & "." & Month(0.5) & "." & Year(0.5) & _
", " & Hour(0.5) & ":" & Minute(0.5) & ":" & Second(0.5)
End Sub
4.6 Benutzerdefinierte Datentypen
Die Datentypen von Visual Basic sind die Bausteine für benutzerdefinierte Datentypen,
worin logisch zusammengehörende Einzeltypen zu einem Informationsblock zusammengefasst werden. Die einzelnen Bestandteile dürfen dabei die Basistypen von Visual Basic
51
als auch selbst wieder benutzerdefinierte Datentypen sein. Die Deklaration erfolgt über
das Schlüsselwort Type. Wie bei gewöhnlichen Variablen kann der Geltungsbereich über
Private und Public bestimmt werden.
Persönliche Daten von Person gehören logisch zusammen und bieten sich als benutzerdefinierter Datentyp an, Der Typname darf frei gewählt werden, etwa Person. Der
benutzerdefinierte Datentyp erlaubt die Deklaration von Variablen dieses Typs. Auf die
einzelnen Elemente wird wie auf Objekte über den Punktoperator zugegriffen. Die Eigenschaften erscheinen in einer Listbox und können über die Tabulatortaste in den Code
übernommen werden.
Das folgende Modul »Personen« setzt voraus, dass eine Tabelle »Personen« mit den
Daten der Professoren einer ausgedachten Hochschule vorhanden ist. Name und Vorname
des ersten Datensatzes werden dabei ausgelesen und in einem Meldungsfenster angezeigt:
Public Type Person
Nachname As String * 20
Vorname As String * 20
Geschlecht As String * 1
GebDat As Date
Verheiratet As Boolean
Kinder As Integer
FBR As String * 3
End Type
Sub ZeigePerson1()
Dim Pers As Person, Kopie As Person
Sheets("Personen").Activate
Pers.Nachname = Range("A2").Value
Pers.Vorname = Range("B2").Value
Kopie = Pers
MsgBox "Nachname: " & vbTab & Kopie.Nachname & vbNewLine _
& "Vorname: " & vbTab & Kopie.Vorname
End Sub
Nach der Zuweisung Kopie = Pers erhalten alle Datenelemente von Kopie dieselben
Werte wie die von Pers.
4.7 Der Datentyp Variant
Der Datentyp Variant ist der Datentyp für alle Variablen, die nicht explizit als anderer
Datentyp deklariert werden, etwa
Dim IchBinVariant As Variant ’Explizit als Variant deklariert
Dim IchAuch
’Variablen ohne Typangabe sind Variant
Variant ist ein besonderer Datentyp, der beliebige Daten mit Ausnahme von StringDaten fester Länge und benutzerdefinierten Typen enthalten kann. Variablen vom Typ
52
4.7 Der Datentyp Variant
Variant passen sich geschmeidig jeder Wertzuweisung an. Wenn Sie zum Beispiel einem Variant einen Wert vom Datentyp Long zuweisen, interpretieren alle nachfolgenden Operationen den Variant als Datentyp Long. Wenn Sie jedoch mit einem
Variant mit dem Typ Byte, Integer, Long oder Single eine arithmetische Operation ausführen und das Ergebnis den zulässigen Bereich für den ursprünglichen Datentyp überschreitet, wird das Ergebnis innerhalb des Variant automatisch zu dem
nächstgrößeren Datentyp erweitert. Byte wird zu Integer, Integer wird zu Long,
und Long bzw. Single werden zu Double umgewandelt. Werden die zulässigen Bereiche für den Datentyp Currency, Decimal oder Double in einer Variablen vom
Typ Variant überschritten, so tritt ein Fehler auf.
Trotz seiner Geschmeidigkeit sollten Sie diesen Datentyp nur in eng begründeten Ausnahmefällen verwenden. Die Gefahr besteht darin, dass über diesen Datentyp Operationen erfolgen, die Sie gar nicht beabsichtigen, etwa dass Visual Basic Zeichenfolgen als
Zahlen oder umgekehrt Zahlen als Zeichenfolgen auslegt.
53
Funktionen enthalten Code, der an vielen Stellen des Programms benötigt, aber nur
einmal geschrieben wird. Programme werden dadurch knapper und übersichtlicher. Ein
einfacher Aufruf der Funktion setzt deren Code in Gang, etwa sin(3.14) eine komplizierte nummerische Prozedur zur Berechnung dieses Ausdrucks. Dieselbe Prozedur
wird zur Berechnung von sin(2.1) nötig, nur tritt nun 2.1 an die Stelle von 3.14. Die
Grundidee besteht darin, ganz von den konkreten Werten abzuheben und einen generellen Stellvertreter etwa x einzusetzen, an dessen Stelle dann beim Funktionsaufruf die
tatsächlichen Werte treten. Ein Funktionsaufruf ist ein Ausdruck, daher muss jede Funktion einen Typ haben. Wie sin(x) erlauben die meisten Funktionen die Übergabe von
Parametern. Die übergebenen Parameter heißen aktuelle Parameter, doch deren Werte
sind beim Schreiben der Funktion nicht bekannt, nur ihr Typ. Daher erlaubt der Rechner
sin(3.14) und weist sin("Hans") ab. Stellvertretend für die aktuellen Parameter
stehen im Code einer Funktion die formalen Parameter, z.B. bei sin(x) der formale
Parameter x für alle reellen Zahlen. Es gibt zwei Arten der Übergabe von aktuellen Parametern an Funktionen, und zwar die Übergabe als Original, was mit Referenzübergabe
(call by reference) bezeichnet wird oder nur als Kopie, die sogenannte Wertübergabe (call
by value). Im ersten Fall verfügt die Funktion über die Originale der aktuellen Parameter und kann deren Werte daher verändern, während im anderen Fall die Änderungen
innerhalb der Funktion nach außen ohne Folgen bleiben, da nur mit Kopien gearbeitet
wurde.
5.1 Arbeitsmappe
Hinweis
»VBAProzeduren«, die Sie von der Seite
//htm
5.2 Einführendes Beispiel
Funktionen bilden die Basis von Excel. Es gibt für viele Probleme eine geeignete Funktion. Aber manchmal fehlt die richtige. Der Ausweg sind Funktionen, die man mit VBA
selbst schreibt. An folgendem Problem sei gezeigt, wie einfach es geht.
54
5.3 Syntax von Funktionen
Im glücklichen Staat Merkelien lohnen sich Arbeit und Kinderkriegen. Unabhängig von
der Tätigkeit erhalten alle Menschen ein einheitliches Gehalt nach folgendem Prinzip.
Bis zum Alter von 30 Jahren beträgt das Grundgehalt 500 Merkel. Im Alter von 31 und
45 bekommt man 1000 Merkel und ab dann sogar 1500 Merkel. Pro Kind gibt es dazu
eine Zulage von 300 Merkel.
Wir benötigen also eine Funktion Gehalt()mit zwei Parametern. Sie befindet sich
im Modul »Gehaelter«.
Option Explicit
Function Gehalt(ByVal al As Long, ByVal k As Long) As Long
Dim Grundgehalt as Long
Grundgehalt = 500
If (al > 30 And al < 46) Then Grundgehalt = 1000
If (al >= 46) Then Grundgehalt = 1500
Gehalt = Grundgehalt + k*300
End Function
Nach dem Aufruf werden die Anweisungen der Funktion ausgeführt. Funktionen werden
gewissermaßen auf Vorrat geschrieben, der Code wird nur ausgeführt, wenn Excel oder
eine Prozedur die Funktion aufrufen, was beliebig oft erfolgen kann. Der Wert einer
Funktion ist abhängig von den Werten der an die Funktion übergebenen Parameter. In
der folgenden Tabelle sind die Werte der Funktion in Abhängigkeit einiger Werte der
Parameter al und k angegeben:
al
k
Gehalt(al, k)
20
31
56
1
3
4
500 + 300 = 800
1000 + 900 = 1900
1500 + 1200 = 2700
5.3 Syntax von Funktionen
Syntaktisch haben Funktionen die Grundform:
Function Name_der_Funktion(Parameterliste) As Typ_der_Funktion
Anweisungen
End Function
Funktionen haben einen Kopf, der aus dem Schlüsselwort Function, dem Namen und
der Parameterliste besteht. Das Ende wird durch das Schlüsselwort End Function
angezeigt, dazwischen liegt der Rumpf, der aus den so genannten Anweisungen besteht.
Sie dürfen niemals die runden Klammern für die Parameterliste weglassen! Vor jedem
Parameter steht das Schlüsselwort ByVal, nach jedem Parameter des Typ, wobei auf
As zu achten ist. Funktionen kommen erst bei einem Aufruf ins Spiel.
55
In jeder Funktion gibt es eine Variable, die aber nicht deklariert werden darf, und
zwar diejenige, welche den Namen der Funktion trägt. Ich werde diese Variable Funktionsvariable nennen In unserem Beispiel ist dies die Variable Gehalt, da die Funktion
so heißt. Der Typ der Funktonsvariablen entspricht dem Typ der Funktion, hier also hat
die Variable Gehalt den Typ Long.
Jede Funktion gibt einen Wert zurück, und zwar denjenigen Wert, den die Funktionsvariable hat, wenn die letzte Anweisung der Funktion ausgeführt ist. Bei der Funktion
Gehalt() lautet die letzte Anweisung
Gehalt = Grundgehalt + k*300
und genau der Wert, den die Funktionsvariable Gehalt jetzt hat, wird als Wert der
Funktion zurückgeliefert.
5.4 Verwendung von Funktionen in Excel
Jede selbst geschriebene Funktion kann in Excel verwendet werden. Sie tauchen in der
Kategorie benutzerdefiniert auf. Sie müssen allerdings immer dran denken, dass in Excel
die Argumente durch Semikolons getrennt werden, in VBA dagegen durch Kommas.
5.5 Aufruf von Funktionen in VBA
Funktionen werden auf Vorrat geschrieben und müssen erst durch einen Aufruf in Gang
gesetzt werden. Der Rechner nimmt daher Funktionen zunächst nur zur Kenntnis, überprüft die syntaktische Korrektheit und unternimmt dann nichts weiter. Erst beim Aufruf
kommt die Maschinerie in Bewegung.
Folgende Aufrufe der Funktion Gehalt()sind falsch:
Dim
s =
s =
s =
s As Long
Gehal(20, 3)
’falscher Namen, Gehal statt Gehalt
Gehalt("Pi", 3.14) ’unsinnige Parametertypen
Gehalt(20, 3, 12) ’zu viele Parameter
Funktionen ohne rufende Prozeduren sind wie Fische ohne Fahrräder. Liefern wir
schnell eine Sub-Prozedur nach, welche die Funktion Gehalt() zweimal aufruft, wobei
die aktuellen Parameter einmal Variablen und dann Konstanten sind. Sie befindet sich
im Modul »Gehaelter«.
Sub GehaltTest()
Dim a As Long,
a = 48: k = 3
MsgBox "Alter:
", Gehalt:
MsgBox "Alter:
", Gehalt:
End Sub
56
k As Long
" &
" &
35"
" &
a & ", Kinder: " & k & _
Gehalt(a, k)
& ", Kinder: 2" & _
Gehalt(35, 2)
5.6 Parameter von Funktionen
Die Ausgabe lautet im ersten Fall: Alter: 48, Kinder: 3, Gehalt: 2400 und im zweiten:
Alter: 35, Kinder: 2, Gehalt: 1600
Die Implementation der Testroutine kann vor oder nach der Funktion stehen. Ich habe
die Funktion insgesamt zweimal aufgerufen, immer mit anderen aktuellen Parametern.
Ich werde jetzt näher beschreiben, was bei der Parameterübergabe geschieht.
5.6 Parameter von Funktionen
Bis auf wenige Ausnahmen haben alle Funktionen Parameter, deren Werte den Rückgabewert der Funktion bestimmen. Parameter bilden die Nahtstelle zwischen der Funktion
und dem Programmteil, das die Funktion aufruft.
5.6.1 Formale und aktuelle Parameter
Was aber geschieht eigentlich bei einem Aufruf wie
s = Gehalt(20, 3)
Im Prototyp der Funktion Gehalt(al, k)erscheinen al und k als so genannte formale Parameter . Die beim Aufruf eingesetzten Parameter heißen aktuelle Parameter .
Viele Anfänger glauben beide Parameter seien identisch, aber das ist sehr falsch. Jede
Variable oder Konstante, die den gleichen Typ wie der zugehörige formale Parameter
hat, darf rein syntaktisch der entsprechende aktuelle Parameter werden, so wie jeder
Deutsche das Recht hat Bundeskanzler zu werden. In diesem Beispiel ist das Grundgesetz die Funktion, Bundeskanzler der formale Parameter und Angela Merkel der aktuelle
Parameter.
Beim Aufruf Gehalt(20, 3)wird der formale Parameter al mit dem Wert 20 initialisiert und der formale Parameter k mit 3. Der Funktionsrumpf startet anschließend mit diesen Startwerten für die formalen Parameter. Innerhalb der Funktion kann
dann aber jeder formale Parameter neue Werte annehmen. Es spielt deshalb keine Rolle, wie die Bezeichner der formalen Parameter heißen. Statt al und k hätte ich auch
alter und kinder verwenden können. Der Name von formalen Parametern ist Schall
und Rauch, da sie nur eine Stellvertreterrolle für die aktuellen Parameter einnehmen.
Viele Anfänger glauben dagegen, dass Funktionen nur mit aktuellen Parametern aufgerufen werden dürfen, die genauso heißen wie die formalen. Das ist natürlich falsch,
da aktuelle und formale Parameter nur einmal in Kontakt treten, und zwar beim Aufruf. Aber genau hier gibt es zwei sehr unterschiedliche Vorgehensweisen, die ich jetzt
vorstellen werde.
5.6.2 Parameterübergabe durch Wert
Im Standardfall richtet die Funktion ihre eigenen Speicherbereiche für die formalen Parameter ein und initialisiert diese mit den Werten der aktuellen Parameter. Danach
57
trennen sich die Wege von aktuellen und formalen Parametern wieder. Deshalb kann eine Funktion bei dieser Art der Parameterübergabe auch niemals die Werte der aktuellen
Parameter ändern.
Die aktuellen Parameter werden also nicht als Originale übergeben, sondern nur deren
Werte. Die Funktion legt für jeden formalen Parameter eine Variable an und initialisiert
diese mit dem Wert des aktuellen Parameters. Deshalb ist eine Veränderung des formalen Parameters innerhalb der Funktion ohne Auswirkung auf den aktuellen, da dieser
sich nicht selbst in die Funktion einbringt, sondern nur seinen Wert übergibt. Diese Art
der Parameterübergabe heißt daher call by value oder Übergabe durch den Wert, anschaulicher wäre Übergabe als Kopie. Die Übergabe als Wert wird in Visual Basic durch
das Schlüsselwort Byval gekennzeichnet. Alle Wertübergaben erfolgten bisher nach
dieser Weise, was Sie an diesem Schlüsselwort vor den Parametern erkennen können.
5.6.3 Parameterübergabe durch Referenz
Visual Basic ermöglicht noch eine zweite Form der Parameterübergabe, wo beim Aufruf
der aktuelle Parameter als Original an die Funktion übergeben wird. Diese zweite Form
muss mit dem Schlüsselwort ByRef vor dem formalen Parameter angezeigt werden. Bei
formalen Parametern, die so markiert sind, tritt innerhalb der Funktion anstelle des formalen Parameters der aktuelle Parameter und kann daher von der Prozedur umgestaltet
werden, da er sich im Jargon der Selbsterfahrungsgruppen gesprochen voll in die Prozedur einbringt. Dieses Verhalten wird auf englisch call by reference und im Deutschen
Übergabe durch Referenz genannt, treffender wäre Übergabe als Original. Die Bezeichnungen drücken aus, dass der formale Parameter lediglich ein anderer Name, eben eine
Referenz, für den aktuellen Parameter ist.
Die folgende Sub-Prozedur vertauscht die Werte zweier Zahlen. Ihr müssen deshalb
die Werte als Referenz übergeben werden. Alle Tauschfunktionen sind Teil des Moduls
»Parameter«.
Sub Tausch(ByRef i As Long, ByRef j As Long)
Dim h As Long
h = j
j = i
i = h
End Sub
Hätte ich die Sub-Prozedur Tausch()versehentlich mit Wertübergaben ausgestattet,
so wären zwar die formalen Parameter innerhalb der Sub-Prozedur geändert worden,
nicht aber die aktuellen Parameter. Ich nenne die falsche Version TauschtNicht():
Sub TauschtNicht(ByVal i As Long, ByVal j As Long)
Dim h As Long
h = j
j = i
i = h
End Sub
58
5.7 Objekte als Parameter
In der folgenden Sub-Prozedur werden die Werte der Variablen a und b erfolgreich
vertauscht, nicht aber von c und d:
Sub TauschTest()
Dim a As Long, b As Long, c As Long, d As
a = 1:
b = 2:
c = 3:
d = 4
Tausch a, b
TauschtNicht c, d
Long
MsgBox "a = " & a & ", b = " & b & _
", c = " & c & ", d = " & d
End Sub
Die Ausgabe lautet dann
a = 2, b = 1, c = 3, d = 4
Nur aktuelle Parameter, die als Referenz, also als Original übergeben werden, können
von einer Funktion oder Sub-Prozedur verändert werden. Ein Aufruf der Art
Tausch 10, 20
ist dagegen nicht erlaubt, da bei Konstanten die Werte unveränderlich sind und daher
auch nicht vertauscht werden können.
Die Wertübergabe ist aufwendiger als die Referenzübergabe, da für die formalen Parameter Variablen angelegt und diese mit den Werten der aktuellen Parameter initialisiert
werden müssen. Bei Parameter vom Typ Long oder Double ist das Anlegen einer
Kopie kein Problem.
Zusammengesetze Datentypen wie Datenfelder, Zeichenfolgen oder selbstdefinierte Typen bezeichnet man als Objektdatentypen, die Realisationen als Objekte. Als Ganzes
bilden gleichartige Objekte eine so genannte Klasse. Streng genommen bilden natürlich
auch die einfachen Datentypen eine Klasse, etwa die Klasse der ganzen Zahlen vom Typ
Long. Man spricht aber erst dann von Objekten, wenn eine gewisse Komplexität dazu
kommt, wie bei Zeichenfolgen oder Datenfeldern. Der erhöhte Speicherbedarf von Objekten verbietet daher die Übergabe als Kopie. Man übergibt deshalb Objekte immer
als Referenz, auch wenn man die Werte der Argumente gar nicht ändern will. Ich halte
mich also an folgende Regel:
Regel
Objekte werden immer als Referenz, einfache Variablen fast immer als Wert übergeben,
eine der wenigen Ausnahmen ist die schon beschriebene Sub-Prozedur Tausch().
Ich zeige die Parameterregel am Beispiel einer Zeichenfolgenfunktion. Dabei werden
die Funktionen Left, Right und InStr in der folgenden Funktion verwendet, die zu
59
einer eine Zahl darstellenden Zeichenfolge eine Zeichenfolge liefert, in der das Dezimalkomma durch den Dezimalpunkt ersetzt wird:
Function KommaZuPunkt(ByRef z As String) As String
Dim i As Long
i = InStr(z, ",")
If (i > 0) Then KommaZuPunkt = Left(z, i - 1) & "." _
& Right(z, Len(z) - i)
End Function
Der formale Parameter z vom Typ String wird als Referenz übergeben, obwohl er
innerhalb der Prozedur nicht verändert wird. Daher dürfen als aktuelle Parameter auch
konstante Zeichenfolgen, so genannte Literale eingesetzt werden, wie in der folgenden
Testprozedur, wo in der europäisch geschriebenen Zahl "3,14"das Komma durch einen
Punkt ersetzt wird.
Sub KommaTest()
MsgBox "Europäisches pi = 3,14." & vbNewLine & _
"Amerikanisches pi = " & KommaZuPunkt("3,14")
End Sub
Diese beiden Prozeduren beschließen das Modul »Parameter«.
5.7.1 Sonderfall Zeichenfolgen
Die Funktion KommaZuPunkt() lässt die ursprüngliche Zeichenfolge unverändert und
liefert eine neue, wo das Komma durch einen Punkt ersetzt ist. Bei der folgenden SubProzedur soll dagegen in der ursprüngliche Zeichenfolge selbst der Dezimalpunkt durch
ein Komma ersetzt werden. Das geht scheinbar ganz einfach wie folgt, klappt aber nicht
und ich weiß noch nicht wieso. Muss an der internen Darstellung von Zeichenfolgen in
Basic liegen, die anders als in C keine Null-terminierten Zeichenfolgen sind.
Sub KommaWegGehtAberNicht(ByRef z As String)
Dim i As Long
i = InStr(z, ",")
If(i > 0) Then z = Left(z,i - 1) & "." & Right(z,Len(z)- i)
End Sub
Sollte z verändern, tut es aber nicht! Tant pis.
Kurz und gut: Man kann Zeichenfolgen innerhalb einer Prozedur zwar ändern, aber die
Zeichenfolge bleibt nach dem Aufruf trotzdem unverändert. Dagegen klappt die Rückgabe einer Zeichenfolge als Funktionswert reibungslos.
5.7.2 Vektoren als Parameter
Vektoren sind ebenfalls Objekte und werden immer als Referenz übergeben, vor den
formalen Parametern steht also immer ByRef, gefolgt vom Bezeichner mit offenen leeren
60
Klammern. Beim Aufruf dürfen Sie aber keine Klammern setzen! Die Werte der Elemente
des Vektors können innerhalb der Funktion geändert werden.
Als Beispiel sei ein Selbstständiger betrachtet, der in bestimmten Monaten eines Jahres
die Umsätze U(i) machte und jetzt den Monat mit dem größten Umsatz ermitteln
möchte. Die Umsatzwerte stehen in einem Vektor U unbekannter Dimension. Die Unterund Obergrenze des Vektors liefern die Funktionen LBound und UBound.
Ich habe einen Modul »VektorFunk« eingerichtet, der mit Option Explicit beginnt
und einen dynamischen Vektor V deklariert. Danach folgt die Funktion zur Bestimmung
des maximalen Umsatzes. Zum Testen der Funktion habe ich eine kleine Sub-Prozedur
geschrieben. Das vollständige Modul »VektorFunk« lautet:
Option Explicit
Private V() As Double
Function UMax(ByRef U() As Double) As Double
Dim i As Long, k As Long
k = LBound(U, 1)
UMax = U(k)
For i = k + 1 To UBound(U, 1)
If (U(i) > UMax) Then UMax = U(i)
Next i
End Function
Sub TestVecFunction()
Dim dm As Long, i As Long
dm = InputBox("Dimension des Vektors:", "Eingeben!", "")
ReDim V(dm - 1)
For i = 0 To dm - 1
V(i) = InputBox("Wert der Komponente V(" & i & "):" _
, "Eingeben!", "")
Next i
MsgBox "Maximaler Umsatz:" & UMax(V)
End Sub
Die Bestimmung des maximalen Umsatzes besorgt eine bedingte Anweisung, die für alle
Komponenten des Vektors wiederholt wird. Vor der Schleife müssen bestimmte Vorbereitungen getroffen werden. Bei der Suche nach dem Maximum wird der Wert UMax vor
der Schleife initialisiert.
Die Testprozedur erfragt zunächst die Dimension des Vektors und lässt sich danach
die Werte eingeben. Die Dimension des dynamischen Vektors wird durch die RedimAnweisung festgelegt.
61
5.8 Ausstieg aus Prozeduren
Manchmal erfordern die Umstände das vorzeitige Verlassen einer Sub-Prozedur oder
Funktion. Die Schlüsselwörter lauten Exit Sub und Exit Function. Auf einer Hilfeseite von Microsoft werden diese Anweisungen wie folgt erklärt.
• Exit Sub. Beendet die umgebende Sub-Prozedur mit sofortiger Wirkung. Die
Ausführung wird mit der Anweisung nach der Anweisung fortgesetzt, die die SubProzedur aufgerufen hat. Exit Sub kann nur in einer Sub-Prozedur verwendet
werden.
• Exit Function. Beendet die umgebende Function-Prozedur mit sofortiger Wirkung. Die Ausführung wird mit der Anweisung nach der Anweisung fortgesetzt,
die die Function-Prozedur aufgerufen hat. Exit Function kann nur in einer
Function-Prozedur verwendet werden.
Hinzufügen möchte ich noch, dass beide Anweisungen beliebig oft innnerhalb einer Prozedur stehen dürfen. Meistens ist der Ausstieg an eine Bedingung geknüpft, die auf
fehlerhafte Werte untersucht und dann abbricht.
In Visual Basic gibt es die Funktion Log(x) den natürlichen Logarithmus, der bekanntlich nur für positive Zahlen existiert. Es gibt aber keinen Logarithmus zur Basis
10, eine entsprechende Funktion, die ich Log10(x) nennen möchte, werde ich jetzt
schreiben. Beide Funktionen sind enge Verwandte, wie die Formel
Log10(x) = Log(x)/Log(10)
zeigt. Entsprechend einfach ist der Code:
Function Log10(ByVal x As Double)
If (x <= 0) Then Exit Function
Log10 = Log(x) / Log(10)
End Function
Hier hilft mir Exit Function, falls ein mathematischer Laie Log10(−2) bilden will.
Diese Funktion werde ich jetzt mit eine Sub-Prozedur aufwändig testen. Der Anwender
soll eine positive Zahl eingeben, von der die Prozedur den Logarithmus zur Bsais 10
berechnet. Der Anwender kann dabei zwei mögliche Fehler begehen, und zwar gar keine
Zahl, etwa »kkk« oder eine nicht positive Zahl wie -2 eingeben. In beiden Fällen wird
die Prozedur über die Anweisung Exit Sub beendet.
Sub ExitSubTest()
Dim x As Double, ein As String
ein = InputBox("Bitte positive Zahl eingeben: ", "Los", "0")
If (Not IsNumeric(ein)) Then Exit Sub
x = CDbl(ein)
If (x <= 0) Then MsgBox "Bitte nur positive Zahlen!"
62
5.8 Ausstieg aus Prozeduren
If (x <= 0) Then Exit Sub
MsgBox "Log10(" & x & ") = " & Log10(x)
End Sub
Diese Funktion verabschiedet sich kommentarlos, wenn der Anwender nicht mal in der
Lage ist eine Zahl einzugeben, weist aber bei der Eingabe von nicht positiven Zahlen
darauf hin. Da wir bisher nur in der Lage sind, eine einzige Anweisung auf eine Bedingung
folgen zu lassen, sind zwei bedingte Anweisungen nötig. Beide Prozeduren finden Sie im
Modul »ExitTest«.
63
Die Anweisungen innerhalb eines Programmes werden hintereinander so ausgeführt wie
sie im Quelltext stehen. In Visual Basic gibt es wie in allen höheren Programmiersprachen
die Möglichkeit den Verlauf durch so genannte Kontrollstrukturen zu steuern. Dabei
handelt es sich um Verzweigungen, Wiederholungen und Sprünge. Damit ändert sich
die sture sequentielle Reihenfolge der Verarbeitung und die Programmierung komplexer
Programme wird möglich. Kontrollstrukturen hängen meistens von Bedingungen ab,
bei den Verzweigungen muss die Entscheidung getroffen werden, zu welcher Gruppe
von Anweisungen zu verzweigen ist, bei den Wiederholungen geht es um die Frage, ob
und wie lange eine Gruppe von Anweisungen wiederholt ausgeführt werden muss. Das
dritte Steuerungselement sind die Sprünge. Dabei wird von einer Anweisung zu einer
anderen gesprungen. Auch dies erfolgt meist in Abhängigkeit einer Bedingung. Sprünge
aus Funktionen und Sub-Prozeduren und Schleifen gelten als eher unbedenklich, während
die GoTo-Anweisung das Schmuddelkind unter den Sprüngen ist, die den Sprung zu einer
mit einer Marke versehenen Anweisung erlaubt. Jede Kontrollstruktur besteht in Visual
Basic aus einem Anfangs- und einem Endeschlüsselwort. Die Schlüsselwörter rahmen
dabei den Code-Abschnitt ein, der mehrfach oder bedingt ausgeführt werden soll.
6.1 Arbeitsmappe
Hinweis
»VBAKontrollstrukturen«, die Sie von der Seite
//htm
6.2 Verzweigungen
Verzweigungen bilden die erste der drei großen Gruppen von Kontrollstrukturen zur
Ablaufkontrolle. Die einfachste Verzweigung ist die Auswahl. Am Anfang steht dabei eine
Bedingung, die ausgewertet wird. Trifft sie zu, wird ein Block genannter Codeabschnitt
ausgeführt, ansonsten übersprungen. Bei einer echten Verzweigung gibt es noch einen
zweiten Anweisungsblock, der ausgeführt wird, wenn die Bedingung nicht zutrifft. Bei
einer mehrfachen Verzweigung folgen auf die anfängliche Bedingung noch weitere, wobei
zu jeder Bedingung ein Block gehört, der bei Zutreffen der Bedingung ausgeführt wird.
64
6.2 Verzweigungen
6.2.1 Die einseitige Auswahl
Die einseitige Auswahl besteht aus einer Bedingung und einem Codeabschnitt, der aus
einer oder mehreren Anweisungen besteht. Wird bei der Programmausführung die Bedingung erreicht, wird sie ausgewertet, und wenn sie zutrifft, wird ein zugehöriger Codeabschnitt ausgeführt, im anderen Fall übersprungen. In beiden Fällen geht die Programmausführung mit der ersten Anweisung nach dem Block weiter. Die beiden Schlüsselwörter,
die Anfang und Ende der einseitigen Auswahl anzeigen, heißen If und End If.
Zusammenhängende Anweisungen, die innerhalb passender Schlüsselworte stehen, werden ein Block genannt, die einseitige Auswahl besteht also aus einer Bedingung und
einem Block. Bitte achten Sie darauf, die Anweisungen eines Blockes etwas einzurücken und die zugehörigen Schlüsselwörter in derselben Spalte beginnen zu lassen. Das ist
zwar nicht syntaktisch erforderlich, erleichtert aber die Lesbarkeit und sieht sonst sehr
amateurhaft aus.
Betrachten wir die Sub-Prozedur
Sub IfTest()
Dim x As Double
x = InputBox("Bitte positive Zahl eingeben: ", "Los", "0")
If (x <= 0) Then
MsgBox "Bitte nur positive Zahlen!"
x = 1
End If
’Hier geht’s in beiden Fällen weiter!
MsgBox "Log(" & x & ") = " & Log(x)
End Sub
Hier wird der Anwender aufgefordert eine positive Zahl einzugeben, von der dann der
natürliche Logarithmus gebildet wird. Falls die Eingabe doch eine nicht positive Zahl
ist, erfolgt eine Meldung und statt der Eingabe wird der Wert x = 1 genommen. Der
Anweisungsblock zwischen If und End If wird aber nur dann ausgeführt, wenn die anfängliche Bedingung x <= 0 zutrifft. In jedem Fall geht es mit der Anweisung weiter,
die direkt auf End If folgt.
Besteht der Anweisungsblock, der auf die Bedingung folgt, nur aus einer einzigen
Anweisung, spricht man von einer bedingten Anweisung. Dann wird diese Anweisung
direkt nach der Bedingung in dieselbe Zeile geschrieben und auf das Schlüsselwort
End If verzichtet. Sie dürfen aber auch in diesem Fall einen Block bilden, müssen dann
aber die Anweisung in eine neue Zeile schreiben und benötigen das abschließende End If.
Also entweder
If (x <= 0) Then MsgBox "Böse!"
oder
If (x <= 0) Then
65
MsgBox "Böse!"
End If
Falsch sind dagegen
If (x <= 0) Then
MsgBox "Hier fehlt
Fortsetzungszeichen
oder
End If!"
oder
If (x <= 0) Then _
MsgBox "Entweder Fortsetzungszeichen
End If
oder
End If!"
6.2.2 Die echte Verzweigung
Die einseitige Auswahl mit der anfänglichen If-Bedingung führt die folgende Anweisung
bzw. den Anweisungsblock aus, wenn die Bedingung wahr ist. Ist die Bedingung falsch,
werden die Anweisungen übersprungen. Wie in der Umgangssprache gibt es aber auch
die echte Verzweigung. Bestimmte Handlungen werden bei Erfüllung der Bedingung
ausgeführt, sonst aber wird etwas anderes getan. Diese Form bietet auch die Syntax von
Visual Basic an:
If(Bedingung) Then
Anweisung(en)
Else
Anweisung(en)
End If
In einem stark vereinfachtem Gehaltsmodell sei nur das Alter prägend für das Einkommen, wobei nur die Stufen über oder unter 40 zählen. Es gibt in jeder Altersstufe ein
Grundgehalt und einen Basiswert für die Krankenkasse, der mit dem Alter multipliziert
den gesamten Krankenkassenbetrag ausmacht. Das wird wie folgt verwirklicht:
Function Gehalt(ByVal Alter As Long) As Long
Dim Grundgehalt as Long, Krankenkasse As Long
If (Alter < 40) Then
Grundgehalt = 2000
Krankenkasse = 15
Else
Grundgehalt = 3000
Krankenkasse = 20
End If
’Hier geht’s weite!
Gehalt = Grundgehalt - Alter*Krankenkasse
End Function
66
6.2 Verzweigungen
Die beiden Anweisungen nach der If-Bedingung werden nur ausgeführt, wenn die Bedingung wahr ist, hier also wenn der Wert der Variablen Alter kleiner als 40 ist.
Ansonsten werden die beiden auf Else folgenden Anweisungen ausgeführt. In beiden
Fällen wird danach die erste Anweisung ausgeführt, die auf End If folgt. Im Alter
von 30 Jahren werden damit 2000 − 30 · 15 = 1550 Euro und im Alter von 60 Jahren
3000 − 60 · 20 = 1800 Euro ausgezahlt.
Zusammenhängende Anweisungen, die innerhalb passender Schlüsselworte stehen, werden ein Block genannt, die Verzweigung besteht also aus zwei Blöcken, wobei der erste
Block durch die If-Anweisung eingeleitet und durch Else beendet wird. Diese Anweisung muss allein in einer Zeile stehen und stellt auch den Anfang des zweiten Blocks dar,
der durch die Anweisung End If beendet wird. Bitte achten Sie darauf die Anweisungen eines Blockes etwas einzurücken, und die zugehörigen Schlüsselwörter in derselben
Spalte beginnen zu lassen.
6.2.3 Verschachtelte Verzweigungen
Es dürfen auch mehrere If-Blöcke und If-Else-Blöcke ineinander verschachtelt sein. Die
Zusammengehörigkeit ist dabei wie bei der Klammersetzung geregelt. Daher ist es besonders wichtig, durch die Einrückungen zusammengehörende Blöcke auch optisch deutlich
zu machen. Ich rate Ihnen allerdings dringend, nicht mehr als drei If-Else-Blöcke zu
verschachteln.
Betrachten wir ein Gehaltsmodell, das nur vom Alter und der Anzahl der Kinder
abhängt. Das Grundgehalt beträgt bis zum Alter von 30 Jahren 500 Euro, steigt dann
im Alter zwischen 31 und 45 auf 1000 Euro an und endet mit 1500 Euro für noch Ältere.
Das Kindergeld ist auch altersabhängig und wird auf 300, 250 und 200 Euro für die drei
Altersstufen festgesetzt. Die Funktion für das Bruttogehalt lautet dann:
Function Brutto1(ByVal al As
Dim Kindergeld as
Long, ByVal k As
Long) As Long
Long, Grundgehalt As Long
If ((Al < 0) Or (k < 0)) Then
Brutto1 = -1
Exit Function
End If
If (al < 31) Then
Grundgehalt = 500
Kindergeld = 300
Else
If (al > 30 And al < 46) Then
Grundgehalt = 1000
Kindergeld = 250
Else
67
Grundgehalt = 1500
Kindergeld = 200
End If
End If
Brutto1 = Grundgehalt + k*Kindergeld
End Function
Zunächst kommt eine If-Block ohne Else-Teil, der unsinnige Angaben für Alter oder
Kinderzahl abfängt, zur Warnung ein negatives Gehalt zurückgibt und die Funktion
über Exit Function beendet. Dieser Block zwischen der ersten If-Anweisung und
dem zugehörigen End If wird bei sinnvollen Eingaben übersprungen.
Dann folgen geschachtelte If-Else-Blöcke, wobei im äußeren Else-Block sich ein weiterer If-Else-Block befindet. An den Einrückungen können Sie zusammengehörende Teile
erkennen.
6.2.4 Die mehrfache Verzweigung
In unserem Beispiel gibt es nur drei Auswahlmöglichkeiten, die sich durch Schachtelung
noch gut realisieren lassen. Wenn das Gehalt sich nun aber alle 10 Jahre änderte, müsste man noch tiefer schachteln, was nach meiner Meinung zu Kopfschmerzen, schlecht
lesbaren und oft fehlerhaften Code führt.
Deshalb gibt es für solche Fälle die Möglichkeit auf die erste If-Bedingung weitere
Bedingungen folgen zu lassen, denen jeweils ein Codeabschnitt zugeordnet ist und erst
dann mit dem Else-Teil abzuschließen, der sich mit den restlichen Fällen beschäftigt,
aber auch ganz fehlen darf. Man spricht von einer mehrfachen Verzweigung. Die Syntax
lautet:
If(Bedingung_1) Then
Anweisung(en)
ElseIf(Bedingung_2) Then
Anweisung(en)
’usw.
Else
Anweisung(en)
End If
Zwischen If und Else können also beliebig viele ElseIf-Anweisungen stehen. Es werden der Reihe nach alle If- und ElseIf-Bedingungen abgefragt, solange bis eine davon
zutrifft. Dann wird der zu dieser Bedingung gehörende Block ausgeführt und die Behandlung der mehrfachen Verzweigung ist damit beendet. Wenn keine der Bedingungen
zutrifft, wird der Else-Block ausgeführt, falls er vorhanden ist. Es wird also immer nur
höchstens ein Block ausgeführt und die Bearbeitung mit der ersten Anweisung nach dem
abschließendem End If fortgesetzt.
68
6.2 Verzweigungen
Als Beispiel habe ich eine zweite Version zur Berechnung des Bruttogehalts geschrieben. Diese heißt Brutto2() und steht auch im Modul »Auswahl«:
Function Brutto2(ByVal al As
Dim Kindergeld as
Long, ByVal k As
Long) As Long
Long, Grundgehalt As Long
If (al < 0) Then
Brutto2 = -1
Exit Function
ElseIf (al < 31) Then
Grundgehalt = 500
Kindergeld = 300
ElseIf (al > 30 And al < 46) Then
Grundgehalt = 1000
Kindergeld = 250
Else
Grundgehalt = 1500
Kindergeld = 200
End If
Brutto2 = Grundgehalt + k*kindergeld
End Function
Hier gibt es vier Blöcke, von denen genau einer zur Ausführung gelangt. Die erste Bedingung fängt unsinnige Alterswerte ab und beendet die Funktion. Negative Werte für
die Kinderanzahl werden dagegen hingenommen, Sie sollten in der Lage sein, das zu
beheben.
6.2.5 Mehrfache Verzweigung mit Select Case
Auch dieses Sprachkonstrukt ermöglicht die mehrfache Verzweigung. Dabei wird zunächst ein Ausdruck ausgewertet und zu dem Block gesprungen, wo auf das Schlüsselwort
Case dessen Wert folgt. Die allgemeine Form der Auswahl mit Select Case lautet:
Select Case(Ausdruck)
Case Auswahlliste1
Anweisung(en)
Case Auswahlliste2
Anweisung(en)
’usw.
Case Else
Anweisung(en)
End Select
69
Der Ausdruck in der Select-Anweisung wird ausgewertet und mit den Auswahllisten
verglichen. Die Anweisung Case Else wird angesprungen, wenn keine der Auswahllisten den Wert des Ausdruckes enthält. Diese Anweisung darf weggelassen werden, sodass
dann eventuell kein einziger Block innerhalb des Select-Case-Konstrukts ausgeführt wird.
Merken Sie sich also:
• Die Einrückungen.
• Case Else kann weggelassen werden.
Ich gebe zur Illustration nun unter Verwendung der Select-Anweisung eine dritte Version für die Berechnung des Brutogehalts.
Function Brutto3(ByVal al As Long, ByVal k As Long) As Long
Dim Kindergeld As Long, Grundgehalt As Long
Select Case al
Case Is < 15
Brutto3 = -1
Exit Function
Case 15 To 30
Grundgehalt = 500
Kindergeld = 300
Case 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, _
42, 43, 44, 45
Grundgehalt = 1000
Kindergeld = 250
Case Else
Grundgehalt = 1500
Kindergeld = 200
End Select
Brutto3 = Grundgehalt + k * Kindergeld
End Function
Hier wäre es natürlich kürzer gewesen Case 31 To 45 zu schreiben.
Beachten Sie auch, dass Case Is ein Schlüsselwort ist und danach ein Vergleichsoperator folgen muss.
6.2.6 Weitere Beispiele
Die beiden folgenden Funktion bestimmen zu einem Datum die Jahreszeit. Bei der ersten
Funktion werden die logischen Operatoren innerhalb von If- und ElseIf-Bedingungen
verwendet. Die Funktion hat zwei Argumente, und zwar für den Tag und den Monat:
70
6.2 Verzweigungen
Function Jahreszeit(ByVal tag As Long, _
ByVal mon As Long) As String
If (mon = 1 Or mon = 2 Or (mon = 3 And tag < 21) _
Or (mon = 12 And tag > 20)) Then
Jahreszeit = "Winter"
ElseIf (mon = 4 Or mon = 5 Or (mon = 3 And tag > 20) _
Or (mon = 6 And tag < 21)) Then
Jahreszeit = "Lenz"
ElseIf (mon = 7 Or mon = 8 Or (mon = 9 And tag < 21) _
Or (mon = 6 And tag > 20)) Then
Jahreszeit = "Sommer"
Else
Jahreszeit = "Herbst"
End If
End Function
Ich habe hier wieder das Fortsetzungszeichen »_« für zu lange Anweisungen verwendet.
Der Winter verläuft vom 21.12 bis zum 20.3, der Frühling vom 21.3. bis zum 20.6.,
der Sommer vom 21.6. bis zum 20.9., der Rest ist Herbst.
Es gibt aber auch die Möglichkeit, die Select-Anweisung zu verwenden. Die entsprechende Funktion liefert dasselbe Ergebnis mit anderem Code. Ich habe deshalb einen
anderen Namen gewählt, nämlich Season():
Function Season(ByVal tag As Long, _
ByVal mon As Long) As String
Select Case mon
Case 1, 2, 3
Season = "Winter"
If (mon = 3 And tag > 20) Then Season = "Frühling"
Case 4 To 6
Season = "Frühling"
If (mon = 6 And tag > 20) Then Season = "Sommer"
Case Is <= 9
Season = "Sommer"
If (mon = 9 And tag > 20) Then Season = "Herbst"
71
Case Else
Season = "Herbst"
If (mon = 12 And tag > 20) Then Season = "Winter"
End Select
End Function
Man darf also die Select- und die If-Anweisung mischen.
6.2.7 Eine Testprozedur
Die Funktion Gehalt sowie die drei Funktionen Bruttoi und die beiden Funktionen
für die Jahreszeit stehen im Modul »Auswahl« der Arbeitsmappe »Kontrollstrukturen«
zusammen mit der folgenden Testprozedur.
Sub AuswahlTest()
Dim alt As Long, ki As Long
Dim t As Long, m As Long
alt = InputBox("Alter: ", "Los!", "30")
ki = InputBox("Anzahl Kinder: ", "Los!", "2")
MsgBox
MsgBox
MsgBox
MsgBox
Gehalt(alt)
Brutto1(alt, ki)
Brutto2(alt, ki)
Brutto3(alt, ki)
t = InputBox("Tag: ", "Los!", "13")
m = InputBox("Monat: ", "Los!", "11")
MsgBox Jahreszeit(t, m)
MsgBox Season(t, m)
End Sub
6.3 Schleifen
Sehr oft müssen eine oder mehrere Anweisungen wiederholt werden. Man spricht dann
von einer Schleife. Es gibt Schleifen mit:
• einer festen Anzahl von Wiederholungen.
• Abfrage der Wiederholungsbedingung am Anfang.
• Abfrage der Wiederholungsbedingung am Ende.
72
6.3 Schleifen
Im ersten Fall weiß man genau, wie oft das Programmstück wiederholt wird, während
in den beiden anderen Fällen solange wiederholt wird, wie eine bestimmte Bedingung
wahr ist. Diese bedingung kann am Anfang oder am Ende der Schleife stehen.
6.3.1 For-Next-Schleife
Schleifen mit einer festen Zahl von Wiederholungen haben Sie bereits im Abschnitt 4.3
auf Seite 45 kennengelernt. Die allgemeine Syntax der For-Schleife lautet:
Dim i As Long
For i = Anfangswert To
Anweisung(en)
[ Exit For ]
Anweisung(en)
Next i
Endwert [ Step Schritt ]
Hierbei sind i die Zählvariable, deren erster Wert Anfangswert ist und die nach jeder durchlaufenen Schleife um den Wert von Schritt erhöht wird. Falls kein Wert für
Schritt angegeben ist, wird standardmäßig 1 verwendet. Das ist meistens der Fall.
Wenn die Zählvariable größer als Endwert ist, wird die Schleife nicht mehr durchlaufen, sondern zur Anweisung gewechselt, die unmittelbar auf die Next-Anweisung folgt.
Dorthin gelangt man aber auch vorzeitig mit Hilfe der Anweisung Exit For. Das kann
nötig sein, wenn Bedingungen eintreten, die das Fortsetzen des Durchlaufs unnötig oder
unmöglich machen, etwa falsche oder unvollständige Eingaben. Sie können eine beliebige
Anzahl von Exit For-Anweisungen an jeder Stelle der For-Schleife einfügen.
Für die Summe der ganzen Zahlen zwischen 1 und n gilt die Formel:
n
X
i = n(n + 1)/2,
i=1
etwa
100
X
i = 100 · 101/2 = 5050.
i=1
Zur Überprüfung könnte man folgende Funktion verwenden:
Function MeineSumme(ByVal n As
Dim i As Long
Long) As Long
MeineSumme = 0
For i = 1 To n
MeineSumme = MeineSumme + i
Next i
End Function
73
Schreibt man jetzt in eine Zelle eines Arbeitsblatts die Eingabe »=MeineSumme(100)«,
wird sich 5050 ergeben.
Die For-Anweisung wird sehr oft bei Vektoren und Zeichenfolgen eingesetzt.
Beispiele mit Zeichenfolgen
Passend zur Rechtschreibreform und als Gruß an die Schweiz, wo schon vor Jahren das
ß ausgerottet wurde, werde ich jetzt ein Programm vorstellen, wo diesem possierlichen
Buchstaben der Garaus gemacht wird. Der Code ist nicht sehr schwer. Die nützliche
Suchfunktion InStr() findet die Position des ersten »ß«. Die Zeichenfolge wird an
dieser Position aufgespalten in einen Teil vor dem »ß« und den Teil nach dem »ß«.
Die Teile werden dann neu zusammengesetzt, wobei in der Mitte »ss« eingefügt wird.
Dieser Vorgang wird solange wiederholt, bis kein »ß« mehr vorhanden ist. Die Suchfunktion InStr() gibt dann den negativen Wert 0 zurück. Die einzelnen Zeichen der
Zeichenfolge werden über eine For-Schleife durchlaufen:
Function KillSz(ByRef s As String) As String
Dim i As Long
KillSz = ""
For i = 1 To Len(s)
If Mid(s, i, 1) = "ß" Then
KillSz = KillSz & "ss"
Else
KillSz = KillSz & Mid(s, i, 1)
End If
Next i
End Function
Der bereinigte String ist zunächst leer. Abhängig davon, ob in der deutschen Version das
Zeichen an der Position mit dem Index i ein »ß« steht, wird dem bereinigten String
entweder ein »ss« oder das ursprüngliche Zeichen angehängt. Später wird eine andere
Version zur Elimination des »ß« vorgestellt, deshalb der Zusatz 1 im Namen.
Ganz ähnlich ist die folgende Funktion, die den schönen deutschen Umlauten wie »Ä«
oder »ä« an den Kragen geht:
Function WegMitUmlauten(ByRef S As String) As String
Dim i As Long
WegMitUmlauten = ""
For i = 1 To Len(S)
Select Case Mid(S, i, 1)
Case "Ä": WegMitUmlauten
Case "ä": WegMitUmlauten
Case "Ö": WegMitUmlauten
Case "ö": WegMitUmlauten
Case "Ü": WegMitUmlauten
74
=
=
=
=
=
WegMitUmlauten
WegMitUmlauten
WegMitUmlauten
WegMitUmlauten
WegMitUmlauten
&
&
&
&
&
"Ae"
"ae"
"Oe"
"oe"
"Ue"
6.3 Schleifen
Case "ü": WegMitUmlauten = WegMitUmlauten & "ue"
Case Else: WegMitUmlauten = WegMitUmlauten & Mid(S, i, 1)
End Select
Next i
End Function
6.3.2 Schleifen mit Wiederholungsbedingung am Ende
Schleifen mit einer unbestimmten Zahl von Wiederholungen benötigen eine Wiederholungsbedingung am Anfang oder am Ende der Schleife. Solange diese Bedingung
erfüllt ist, wird die Schleife wiederholt. Alle Schleifen mit unbestimmter Wiederholungszahl verwenden das anfängliche Schlüsselworte Do und das am Ende stehende
Loop. Dazu kommen zur Formulierung der Bedingung bzw. While oder Until.
Der Unterschied besteht darin, dass mit While Wiederholungsbedingungen und mit
Until Abbruchsbedingungen formuliert werden. Die Bedingung darf dabei entweder
oben beim Do oder unten beim Loop stehen.
Ich beginne mit der Do - Loop While-Schleife, deren Syntax wie folgt lautet:
Do
Anweisung(en)
[Exit Do]
Anweisung(en)
Loop While(Wiederholungsbedingung)
nächste Anweisung
Der Block zwischen Do und Loop While heißt Schleifenkörper , dessen Anweisungen werden mindestens einmal ausgeführt. Solange die Wiederholungsbedingung wahr
bleibt, wird der Schleifenkörper immer wieder von oben an durchlaufen. Die Anweisungen innerhalb des Schleifenkörpers müssen demnach irgendwann zur Nichterfüllung der
Wiederholungsbedingung führen, da sonst die Schleife unendlich oft wiederholt würde.
Sobald die Wiederholungsbedingung zum ersten Mal nicht erfüllt ist, wird das Programm
mit der nächsten Anweisung fortgesetzt. Bitte beachten Sie also:
• Die Einrückungen nach der Do-Anweisung.
• Die Wiederholungsbedingung steht in runden Klammern.
• Die Schleife wird solange wiederholt, wie die Wiederholungsbedingung wahr ist.
• Eine so genannte Idiotenschleife entsteht, wenn die Wiederholungsbedingung immer wahr bleibt.
• Innerhalb des Schleifenkörpers dürfen beliebig viele Exit Do stehen. Nach dieser
Anweisung wird aus der Schleife direkt in die erste Anweisung gesprungen, die auf
die Schleife folgt.
Diese Schleife wird gerne bei Eingaben verwendet:
75
Dim Jahr As Long
Do
Jahr = CInt(InputBox("Bitte Geburtsjahr eingeben: ","1978"))
Loop While (1900 > Jahr Or Jahr > 1990)
Der Anwender hat den Abbruch selbst in der Hand, sobald das Eingabejahr zwischen
1900 und 1990 liegt, wird die Schleife verlassen.
6.3.3 Schleifen mit Abbruchbedingung am Ende
Diese Schleifen verwenden mit Hilfe des Schlüsselworts Until eine Abbruchbedingung
am Ende.
Do
Anweisung(en)
[Exit Do]
Anweisung(en
Loop Until(Abbruchbedingung))
Die Do - Loop While-Schleife wird solange wiederholt, wie eine bestimmte Wiederholungsbedingung wahr ist, während die Do - Loop Until-Schleife abbricht, wenn eine
Abbruchbedingung wahr wird. Daher ist die Abbruchbedingung gerade die logische Verneinung der Wiederholungsbedingung. Die oben erwähnte Schleife kann entsprechend
umformuliert werden:
Dim Jahr As Long
Do
Jahr = CInt(InputBox("Bitte Geburtsjahr eingeben: ","1978"))
Loop Until (1900 <= Jahr And Jahr <= 1990)
Bei der Verneinung eines logischen Ausdrucks wird jede Oder-Verknüpfungung zu einer
Und-Verknüpfung und ein >= geht in <= über.
Der größte gemeinsame Teiler einer Zahl
Die folgende Funktion berechnet den größten gemeinsamen Teiler von a und b und gibt
-1 zurück, wenn a oder b nicht positiv sind. Der Algorithmus geht auf Euklid zurück.
Die jeweils größere Zahl wird durch die kleinere geteilt. Ist dies ohne Rest möglich, ist
die kleinere Zahl der größte gemeinsame Teiler. Sonst wird die bisher größere Zahl durch
den Rest ersetzt und das Verfahren fortgesetzt. Soll etwa der größte gemeinsame Teiler
von 88 und 24 bestimmt werden, wird zunächst 88 Mod 24, also 16 berechnet. Danach
übernimmt 16 die Rolle von 88. Somit wird jetzt 24 Mod 16 berechnet, was 8 ergibt. Es
bleiben 16 und 8 im Rennen. Da 16 Mod 8 den Wert 0 hat, ist 8 der größte gemeinsame
Teiler von 88 und 24. Hier müssen innerhalb der Schleife fortwährend die Werte der
Variablen i und j getauscht werden.
Function Ggt(ByVal i As
As Long
76
Long, ByVal j As
Long) _
6.3 Schleifen
Dim h as Long
If (i < 1 Or j < 1) Then Ggt = -1
If j > i Then
h = i : i = j : j = h
End If
Do
i = i Mod j
h = i : i = j : j = h
Loop While (j > 0)
’oder Loop Until (j = 0)
Ggt = i
End Function
Die Quersumme einer Zahl
Zur Berechnung der Quersumme einer Zahl a benötigt man eine Schleife. Hat a etwa den
Wert 1234567, so ist die Quersumme 28. Das Problem besteht darin, an die einzelnen
Ziffern der Zahl a zu gelangen. Das erreicht man mit den Operatoren / und Mod. Die
letzte Dezimalziffer k einer Zahl a ergibt sich durch:
k = a Mod 10
denn diese Ziffer bleibt bei ganzzahliger Division als Rest übrig. Rechnet man dann mit
a = (a - a Mod 10)/10
weiter, so ist der neue Wert von a gerade die Zahl, die durch Streichen der letzten Ziffer
entsteht. Nun kann man mit dem Modulooperator die nächste Ziffer abschneiden. Das
wird für alle Ziffern duchgeführt.
Function Quersumme(ByVal a As Long) As Long
Quersumme = 0
If (a < 0) Then a = -a
Do
Quersumme = Quersumme + a Mod 10
a = (a - a Mod 10) / 10
Loop While (a > 0)
’Oder: Loop Until (a = 0)
End Function
Statt der Wiederholungsbedingung hätte ich auch eine Abbruchbedingung verwenden
können, die als Kommentar eingefügt ist.
77
6.3.4 Schleifen mit Wiederholungsbedingung am Anfang
Manche Schleifen sollen unter bestimmten Umständen gar nicht durchlaufen werden. Die
Wiederholungsbedingung wird dann an den Anfang der zu wiederholenden Anweisungen
gesetzt. Die allgemeine Form lautet:
Do While (Wiederholungsbedingung)
Anweisung(en)
[Exit Do]
Anweisung(en
Loop
Ist die Wiederholungsbedingung schon am Anfang nicht erfüllt, wird der Schleifenkörper
überhaupt nicht durchlaufen. Eine Do - Loop While-Schleife wird dagegen immer mindestens einmal ausgeführt, weil die Wiederholungsbedingung erst zum Schluss überprüft
wird.
Aus Sentimentalität haben die Entwickler von VBA auch die ursprüngliche Schleifenform mit der Syntax
While (Wiederholungsbedingung)
Anweisung(en)
[Exit While]
Anweisung(en
Wend
beibehalten. Beide Formen sind vollkommen gleichwertig, ich rate Ihnen aber von dieser
Form ab. Der Ausstieg erfolgt hier nicht über Exit Do, sondern über Exit While.
6.3.5 Schleifen mit Abbruchsbedingung am Anfang
Statt einer anfänglichen Wiederholungsbedingung ist auch eine anfängliche Abbruchsbedingung möglich. Wieder wird das Schlüsselwort While durch Until ersetzt. Die
allgemeine Form lautet:
Do Until (Abbruchsbedingung)
Anweisung(en)
[Exit Do]
Anweisung(en
Loop
Ist die Abbruchsbedingung schon am Anfang wahr, wird der Schleifenkörper überhaupt
nicht durchlaufen. Auch hier gilt, dass die Abbruchsbedingung gerade die logische Negation (Umdrehung) der Wiederholungsbedingung ist.
Gemeinsames Beispiel
Als Beispiel diene eine Funktion, die angibt, wie oft eine Zeichenfolge innerhalb einer
anderen vorkommt.
78
6.3 Schleifen
Function Zaehle(ByRef gesamt As String, ByRef teil As String) _
As Long
Dim i As Long
Zaehle = 0 : i = 1
Do While (InStr(i, gesamt, teil) > 0)
’oder
’Do Until (InStr(i, gesamt, teil) <= 0)
i = InStr(i, gesamt, teil) + Len(Teil)
Zaehle = Zaehle + 1
Loop
End Function
Vor der eigentlichen Iteration müssen meistens bestimmte Vorbereitungen getroffen werden, hier werden die Zählvariable und die Positionsvariable auf 0 bzw 1 gesetzt. Die
Suche nach der Teilzeichenfolge beginnt zunächst ab der Position 1. Die String-Funktion
InStr(i, gesamt, teil) liefert die Position i des nächsten Auftretens der Teilzeichenfolge oder gibt bei vergeblicher Suche den Wert 0 zurück, was dann einen weiteren
Durchlauf des Schleifenkörpers verhindert. Beachten Sie bitte, dass die neue Suche erst
wieder an der um die Länge des Teilstrings verschobenen Position beginnen darf.
6.3.6 Tests der Funktionen
Alle Funktionen dieses Abschnitts stehen im Modul »Schleifen« der Arbeitsmappe »Kontrollstrukturen«, zusammen mit der folgenden Testprozedur
Sub SchleifenTest()
MsgBox "Summe der Zahlen von 1 bis 100: " & MeineSumme(100)
MsgBox "Aus Flußpferdefüße wird: " & KillSz("Flußpferdefüße")
MsgBox "Aus Ää Öö Üü wird: " & WegMitUmlauten("Ää Öö Üü")
MsgBox "Größter gemeinsamer Teiler von 96 und 54: " _
& Ggt(96, 54)
MsgBox "Quersumme von 1234567: " & Quersumme(1234567)
MsgBox Zaehle("Fischers Fritz fischt frische Fische", "sch")
End Sub
Diese Funktionen lassen sich natürlich auch mit Excel selbst testen. Da es sich zum Teil
um Funktionen mit Parametern handelt, müssen Sie die aktuellen Prameter in Excel
mit Semikolons trennen, in VBA selbst aber durch Kommas. Die folgenden Eingaben in
beliebigen Zelle
=
=
=
=
=
KillSz("Flußpferdefüße")
WegMitUmlauten("Ää Öö Üü")
Ggt(96; 54)
Quersumme(1234567)
Zaehle("Fischers Fritz fischt frische Fische"; "sch")
79
müssten entsprechend die Ergebnisse »Flusspferdefüsse«, »Aeae Oeoe Ueue«, 6, 28 und
4 haben.
6.4 Verschachtelte Kontrollstrukturen
Alle Kontrollstrukturen dürfen ineinander verschachtelt werden, also Schleifen innerhalb
von anderen Schleifen oder innerhalb von Verzweigungen. Ich zeige die Verschachtelung
an einigen Beispielen.
6.4.1 Verschachtelte For-Next-Schleifen
Im folgenden Beispiel soll in den Zeilen 1, 2, . . . , i, . . . , n einer mehrzeiligen Zeichenfolge
jeweils 2i − 1 Sternchen in Dreiecksform
*
***
*****
*******
’usw
ausgegeben werden. Dafür benötige ich zwei verschachtelte Schleifen. Die äußere Schleife
gibt n Zeilen aus, die innere Schleife sorgt für die 2i − 1 Sternchen in der Zeile i. Der
Zeilenumbruch wird mit der Visual Basic Konstanten vbNewLine bewirkt. Da sich die
Form eines halben Weihnachtsbaums ergibt, nenne ich die Funktion Halbtanne(), sie
befindet sich auch im Modul »SchachtelSchleifen« der Arbeitsmappe »Kontrollstrukturen«.
Function HalbTanne(ByVal n As
Dim i, j As
Long) As String
Long
HalbTanne = ""
For i = 1 To n
For j = 1 To 2 * i - 1
HalbTanne = HalbTanne & "*"
Next j
HalbTanne = HalbTanne & vbNewLine
Next i
End Function
Ich teste diese Funktion mit der folgenden Sub-Prozedur:
Sub TestHalbTanne()
Dim i As Long
80
6.4 Verschachtelte Kontrollstrukturen
i = InputBox("Bitte Anzahl der Zeilen eingeben: ", "", "12")
MsgBox HalbTanne(i)
End Sub
6.4.2 Verschachtelung von For-Next- und Do-Schleifen
Diese Beispiel stammt von einer Seite von Microsoft, eigentlich zur Illustration von
verschiedenen Exit-Anweisungen, die eine For-Next-Schleife, eine Do-Schleife und eine
Sub-Prozedur zu beenden. Ich habe die Anweisung Randomize hinzugefügt, damit sich
nicht immer dieselben Zufallszahlen ergeben.
Sub exitStatementDemo()
Dim demoNum As Single, i As Long, j As Long
Randomize
Do
j = j + 1
For i = 1 To 1000
demoNum = Int(Rnd() * 100)
Select Case demoNum
Case 7: Exit For
Case 29: Exit Do
Case 54
MsgBox "Exit Sub bei j: " & j & ", i = " & i
Exit Sub
End Select
Next i
Loop
MsgBox "Exit Do bei j: " & j & ",i = " & i
End Sub
Da die Funktion Rnd() gleichverteilte Zufallszahlen zwischen 0 und 1 liefert, sind die
Werte von demoNum gleichverteilt zwischen 0 und 100. Ein spaßige Prozedur für Leute
mit etwas Geduld, denn erst wenn demoNum einen der beiden Werte 29 oder 54 hat,
ist Schluss. Ein Wert von 7 beendet zwar die innere For-Next-Schleife, leitet dann aber
einen neuen Durchlauf der umgebenden Do-Loop-Schleife ein.
6.4.3 Verschachtelung von Do-Schleifen
Beenden möchte ich die verschachtelten Schleifen mit einem Beispiel von mir. Wieder
wird der Anwender genötigt eine Zahl einzugeben, von der dann der natürlichen Logarithmus berechnet wird. In der äußeren Schleife trifft der Anwender die Entscheidung
ob und wie lange er sich das antun will, die Wiederholungsbedingung steht also am
Anfang. Durch die erste innere Schleife muss er sich solange quälen, bis es ihm oder ihr
81
gelingt eine Zahl einzugeben. In der zweiten inneren Schleife wird zusätzlich gefordert,
dass diese Zahl positiv ist. Hier stehen die Schleifenbedingungen somit am Ende. Aber
lesen Sie bitte selbst den Code:
Sub DreiSchleifen()
Dim x As Double, s As String, b As Boolean
Do Until (UCase(InputBox("Wollen sie wirklich? Ja/Nein ", _
"Tu’s nicht", "Nein")) = "NEIN")
Do
Do
s = InputBox("Positive Zahl eingeben: ", "", "0")
b = IsNumeric(s)
If (Not b) Then MsgBox "Zahlen!"
Loop Until (b)
x = CDbl(s)
If x <= 0 Then MsgBox "Positive Zahlen!"
Loop While (x <= 0)
MsgBox "ln(" & x & ") = " & Log(x)
Loop
End Sub
6.5 Über Kalender
Zur weiteren Illustration von Kontrollstrukturen werde ich einen Modul erstellen, der
einige Kalenderfunktionen enthält, die sowohl in Excel als auch im VBA vorhanden sind.
Die Nachprogrammierung dient also nur der Übung. Außerdem sehen Sie, wie Funktionen
sich gegenseitig aufrufen. In objektorientierten Sprachen würde man die Funktionen in
einer Klasse bündeln. Zunächst aber möchte ich den heute weltweit gültigen Kalender
vorstellen und die astronomischen Hintergründe erklären.
Ein Jahr hat bekanntlich etwas mehr als 365 Tage, in erster Näherung 365,25 Tage.
Normale Kalenderjahre sind also um einen Vierteltag zu kurz! Zum Ausgleich gibt es
alle vier Jahre so genannte Schaltjahre mit einem zusätzlichen Tag im Februar, um in
einem Schlag den verlorenen Tag wieder aufzuholen. Die Jahreszahlen der Schaltjahre
sind durch vier teilbar.
Diese Idee stammt von den alten Ägyptern. Julius Caesar eroberte vor mehr als 2000
Jahren Ägypten und führte in Rom den ägyptischen Kalender ein und reservierte sich
bei dieser Gelegenheit seinen Geburtsmonat, den Juli. Die Einteilung eines Jahres auf
365 Tage und 12 Monate mit einem zusätzlichen Schalttag alle vier Jahre, heißt ebenfalls
nach Caesars Vornamen »Julianischer Kalender«.
Leider ist ein Jahr geringfügig kürzer als 365,25 Tage. Deshalb tut man auf lange
Sicht zuviel des Guten, wenn man alle vier Jahre ein Schaltjahr einlegt. Das merkt man
82
6.5 Über Kalender
aber erst nach Jahrhunderten. In 400 Jahren hat man drei Tage zuviel angesammelt,
der Frühling fängt schon am 18.3 und nicht am 21.3. an. Im 16. Jahrhundert hatten sich
dann bereits 10 überflüssige Tage eingeschlichen, die Sonne stand am 11.6 und nicht am
21.6. am höchsten. Papst Gregor XIII setzte eine von einem Bayern geleitete Kommission
ein, die diesen Missstand beenden sollte. Die Lösung ist einfach und genial. Zunächst
wurden im im Oktober 1582 10 Tage ausgelassen. Innerhalb von 400 Jahren werden drei
Schaltjahre gestrichen, und zwar mit Ausnahme der durch 400 teilbaren Jahre wie 1600,
2000, 2400 usw. alle Anfänge der Jahrhunderte, also z.B. die Jahre 1700, 1800 und 1900.
Ein Jahr ist also genau dann Schaltjahr, wenn es durch 400 teilbar ist oder durch 4 aber
nicht durch 100. Erst in 3000 Jahren wird wieder eine Korrektur nötig sein, das lässt
sich gelassen abwarten.
Seit 1582 gilt in katholischen Ländern der neue, zu Ehren von Papst Gregor XIII
»Gregorianisch« genannte Kalender. Die Protestanten zogen widerwillig im frühen 18.
Jahrhundert nach, die Angelsachsen sogar erst 1752 und in Russland bedurfte es der Oktoberrevolution (eigentlich im November!) zum Anschluss an den päpstlichen Kalender.
Die Griechen blieben kirchlich immer beim alten Julianischen Kalender und feiern somit
Ostern an einem anderen Termin als in Westeuropa. Weltlich gilt der Gregorianische
Kalender heute global, auch in der gesamten islamischen Welt, die letzte Umstellung
erfolgte laut WikiPedia 1926 in der Türkei.
Der 1.1.1582 war übrigens ein Freitag.
6.5.1 Schaltjahrberechnung
Die folgende Funktion gibt bei Schaltjahren den Wert 1 und sonst den Wert 0 zurück.
Diese Funktion befindet sich im Modul »Kalender«.
Function Schaltjahr(ByVal j As Long) As Long
Schaltjahr = 0
If (j Mod 400 = 0) Or (j Mod 4 = 0 And j Mod 100 <> 0) _
Then Schaltjahr = 1
End Function
Die Funktionsvariable Schaltjahr erhält zunächst den Wert 0 und wird 1, wenn
der logische Ausdruck der Schaltjahrbedingung stimmt. Diese Funktion finden Sie im
Modul »Kalender«. Sie können diese Funktion jetzt in Excel testen. Wenn Sie etwa in
eine beliebige Zelle
=Schaltjahr(2009)
schreiben, müsste sich »0« ergeben.
6.5.2 Die laufende Nummer eines Tages
Man benötigt oft die laufende Nummer eines Datums, z.B. 40 für den 9.2. und 365 bzw.
366 für den 31.12. Ich werde jetzt eine Funktion schreiben, wo der Anwender Jahr, Monat
und Tag eingibt und als Antwort die laufende Nummer des Datums erscheint:
83
Function LaufendeNr(ByVal j As Long, ByVal m As Long, _
ByVal t As Long) As Long
Dim MTage(1 To 12) As Long, s As Long, i As Long
For i = 1 To 7
MTage(i) = 30 + i Mod 2
Next i
For i = 8 To 12
MTage(i) = 31 - i Mod 2
Next i
MTage(2) = 28 + Schaltjahr(j)
s = 0
For i = 1 To m - 1 Step 1
s = s + MTage(i)
Next i
LaufendeNr = s + t
End Function
In einem Vektor mit 12 Elementen halte ich die Tage eines Monats fest. Im Februar hängt
dies davon ab, ob das Jahr j ein Schaltjahr ist. Bis zum Juli haben immer ungerade
Monate 31 Tage, ab August dann die geraden Monate 8, 10 und 12. Die Monate mit
30 Tagen erreiche ich über den Modulooperator. Die laufende Nummer ergibt sich als
Summe der Tage bis zum Vormonat m - 1 und der Tage t innerhalb des Monats.
Bei Schleifen, die eine Summation durchführen, muss man immer vor dem Schleifenbeginn die Summationsvariable 0 setzen!
6.5.3 Bestimmung des Wochentages
Ich werde nun eine Funktion schreiben, die für jeden Tag seit dem 1.1.1582 den Wochentag angibt. Dazu muss zunächst berechnet werden, der wie vielte Tag seit dem
1.1.1582 der entsprechende Tag ist. Zum Testen habe ich in einer schlaflosen Nacht
nachgezählt: Mein 50. Geburtstag am Samstag, dem 13.11.1999, war der 152623. Tag
seit dem 1.1.1582. Schneller wäre ich mit dieser Funktion am Ziel gewesen:
Function TageSeitGregor(ByVal j As Long, ByVal m As Long, _
ByVal t As Long) As Long
Dim i As Long
84
6.5 Über Kalender
TageSeitGregor = LaufendeNr(j, m, t)
For i = 1582 To j - 1
TageSeitGregor = TageSeitGregor + 365
TageSeitGregor = TageSeitGregor + Schaltjahr(i)
Next i
End Function
Die laufenden Tage innerhalb eines Jahres j können wir bereits mit der Funktion
LaufendeNr() bestimmen. Die Anzahl der Tage bis zum letzten Tag des Vorjahres
wird durch die anschließende Schleife ermittelt. Dabei werden alle Jahre i zwischen 1582
und j - 1 durchlaufen und die Summationsvariable TageSeitGregor für immer
um 365 und für Schaltjahre um einen zusätzlichen Tag erhöht.
Über die Funktion TageSeitGregor(j, m, t) lässt sich der Wochentag bestimmen. Der 1.1.1582 war ein Freitag, daher ist ein beliebiger Tag genau dann ein Sonntag,
wenn TageSeitGregor(j, m, t) + 4) Mod 7 den Wert 0 hat. Entsprechend
weist der Wert 1 auf einen Montag, usw. Die Funktion WTag(j, m, t) verwendet
TageSeitGregor(j, m, t) wie folgt:
Function WTag(ByVal j As Long, ByVal m As Long, _
ByVal t As Long) As String
Select Case (TageSeitGregor(j, m, t) + 4) Mod 7
Case 0:
WTag = "Sonntag"
Case 1:
WTag = "Montag"
Case 2:
WTag = "Dienstag"
Case 3:
WTag = "Mittwoch"
Case 4:
WTag = "Donnerstag"
Case 5:
WTag = "Freitag"
Case Else: WTag = "Samstag"
End Select
End Function
Da zu jedem Case nur eine Anweisung gehört, habe ich diese direkt danach geschrieben.
Da aber eigentlich eine neue Zeile nötig wäre, habe ich den Doppelpunkt verwendet, der
einem Zeilenvorschub entspricht.
6.5.4 Eine Testfunktion
Alle Funktionen befínden sich im Modul »Kalender«. Die Testprozedur lautet
Sub KalenderTest()
MsgBox WTag(1999, 11, 13) & ", der 13.11.1999 war der " _
& LaufendeNr(1999, 11, 13) & " Tag des Jahres, und der " _
& TageSeitGregor(1999, 11, 13) & " Tag seit dem 1.1.1582."
End Sub
Im Meldungsfenster erscheint dann der Text: Samstag, der 13.11.1999 war der 317. Tag
des Jahres und der 152623. Tag seit dem 1.1.1582.
85
6.6 Sprunganweisungen
Durch eine Sprunganweisung wird die Ausführung des Programms an einer anderen
Stelle fortgesetzt. Dabei werden entweder bestimmte Anweisungen übersprungen oder
es erfolgt ein Rücksprung, mit dem Ziel eine Reihe von Anweisungen nochmal zu durchlaufen. Sprunganweisungen sind also notwendig für Verzweigungen und Schleifen, aber
auch für Unterprogramm-Aufrufe, da die aktuelle Position auf dem Stack gespeichert
und dann der Sprung in die Prozedur erfolgt, wobei an derem Ende dann der Rücksprung erfolgt. Sprunganweisungen sind oft an eine Bedingung geknüpft, die festlegen
ob der Sprung erfolgen soll oder nicht. Die Zielanweisungen der Sprünge werden markiert. Der entsprechende Befehl heißt in fast allen Programmiesprachen »GoTo« und
wurde im Verlauf der Siebziger-Jahre erst hemmungslos verunglimpft, dann so gut wie
ausgerottet und schließlich durch die bereits besprochenen Kontrollstrukturen ersetzt.
6.6.1 Die GoTo Sprunganweisung
Diese Anweisung gilt als das Schmuddelkind der höheren Programmiersprachen, weil sie
angeblich zu so genannten Spaghetticode führt, also wilden Sprüngen vor und zurück,
die sich wie besagte Nudeln ineinander verschlingen. Man sollte sie also nur in Notfällen
verwenden, um bei fehlerhaftem Programmverlauf zu einer Fehlermeldung zu springen.
In Visual Basic gibt es die unbedingte Sprunganweisung
GoTo Marke
und die bedingte Sprunganweisung
If (Bedingung)
Then
GoTo Marke
Der Bezeichner Marke zeigt die Stelle des Programms an, wo Bearbeitung fortgesetzt
wird.
Um Ihnen einen Eindruck von Spaghetticode im Stil der wilden Siebzigerjahre zu
vermitteln, habe ich mir zur Abschreckung folgende Sub-Prozedur ausgedacht und im
Modul »Spruenge« festgehalten. Der Anwender soll eine positive Zahl eingeben und erhält davon den natürlichen Logarithmus, der in VBA »Log(x)« heißt und nicht »Ln(x)«.
Der Anwender kann dabei zwei mögliche Fehler begehen, und zwar gar keine Zahl, etwa
»kkk« oder eine nicht positive Zahl wie -2 eingeben. Im ersten Fall endet die Prozedur mit einer Beschimpfung, im zweiten gibt es eine Bewährungschance. Bei regulärem
Verlauf darf die Eingabe mit einer weiteren Zahl wiederholt werden, bis die Geduld des
Anwenders erschöpft ist.
Sub GoToTest()
Dim x As Double, aus As String
On Error GoTo Beschimpfung
Eingabe:
x = InputBox("Bitte positive Zahl eingeben: ", "Tu’s!", "0")
If (x <= 0) Then GoTo Beratung
86
6.7 Aufgaben
MsgBox "ln(" & x & ") = " & Log(x)
GoTo Ende
Beratung:
MsgBox "Die Zahl
GoTo Eingabe
" & x & " ist nicht positiv!"
Ende:
aus = InputBox("Nochmal? Ja/Nein ", "Tu’s nicht", "Nein")
If UCase(aus) = "JA" Then GoTo Eingabe
Exit Sub
Beschimpfung:
aus = "Das war’s! Sie sind gar zu blöd!" & vbNewLine
aus = aus & "Sie begingen Fehler mit Nummer " & Err & "!"
aus = aus & vbNewLine & Error & "!"
MsgBox aus, vbCritical, "Dummkopf!"
End Sub
Ich habe hier etwas vorschnell bereits Methoden der Ausnahmebehandlung von Fehlern vorgestellt, die erst im nächsten Kapitel behandelt werden. Nur sei bereits verraten,
dass die Bedingung OnError immer wahr wird, wenn ein Fehler auftritt, der eigentlich
zum Programmabbruch führen würde. Stattdessen wird zur Marke gesprungen, die nach
dem GoTo steht. Jeder Fehler hat eine Nummer, der über die Funktion Err zu erfragen
ist und einen Fehlertext, der in einem Meldungsfenster erscheinen würde, wenn der fehler
nicht wie hier abgefangen wird. Der Fehlertext wird von der Funktion Error geliefert.
6.7 Aufgaben
1. Im schönen Staat Merkelien ergibt sich das Nettogehalt aus dem Bruttogehalt nach
Abzug der Steuer. Diese richtet sich nach der Größe des Haushalts, Ledige bezahlen
40% Steuer, Haushalte mit zwei Personen (unverheiratet und ein Kind oder verheiratet ohne Kinder) entrichten 30% Steuer und alle größeren Haushalte werden
sogar nur mit 20% besteuert. Schreiben Sie drei Versionen für diesen Sachverhalt,
und zwar mit verschachtelten Verzweigungen, mit mehrfachen Verzweigungen und
mit dem Select-Case-Konstrukt.
2. Schreiben Sie eine Visual Basic Funktion AnzTage(), die Jahr und Monat als
Argumente hat und die Anzahl der Tage des Monats ausgibt.
3. Schreiben Sie eine Funktion, deren Parameter Jahr, Monat und Tag sind. Die
Funktion überprüft, ob die Eingabe sinnvoll oder wie etwa der 31.11. falsch ist
und gibt entsprechend True oder False zurück. Verwenden Sie Select Case.
Schreiben Sie dann eine zweite Version unter Verwendung von If und ElseIf .
87
4. Man benötigt oft die laufende Nummer eines Datums, z.B. 40 für den 9.2. und 365
oder 366 für den 31.12., da Schaltjahre zu berücksichtigen sind. Man schreibe eine
weitere Funktion LaufendeNr2, die das liefert. Verwenden Sie Select Case.
5. Wenn die Jahre also aus astronomischen Gründen 365 oder 366 Tage haben müssen und in 12 Monate aufgeteilt sein sollen, wäre es naheliegend, allen ungeraden
Monaten 31 und alle geraden bis auf den Februar 30 Tage zu geben. Der Februar selbst hätte dann in normalen Jahren 29 und in Schaltjahren 30 Tage. Diese
einfache Regelung wurde nicht eingeführt, weil der römische Kaiser Augustus für
seinen Geburtsmonat ebenfalls 31 Tage beanspruchte. Schreiben Sie eine Funktion
AnzTageNeu(), die für jedes Jahr und jeden Monat die Anzahl der Tage unter
den obigen vereinfachten Regeln liefert.
6. Man schreibe eine Funktion Tannenbaum(Byval N As Long), die eine Zeichenfolge mit N Zeilen aus Sternchen in gleichschenkliger Dreiecksform erzeugt:
*
***
*****
*******
*********
Hier ist das Argument N = 5. Dieses N soll aber beliebig sein können.
7. Entwickeln Sie eine Funktion, deren Argument eine Zeichenfolge ist, zu der die
Anzahl der Vokale ausgegeben wird.
8. Schreiben Sie eine Sub-Prozedur, deren Argumente ein Vektor von Zahlen und
eine Variable Auf vom Typ Boolean sind. Hat Auf den Wert True, so soll
der Vektor aufsteigend, sonst absteigend sortiert werden.
9. Schreiben Sie Funktionen MyHour(d) und MyMinute(d), die zu einer Zahl
zwischen 0 und 1 die Stunde und Minute liefern, wobei MyHour(0) = 0 und
MyMinute(0) = 0 sowie MyHour(1) = 24 und MyMinute(1) = 0 sind.
10. Schreiben Sie Funktionen MyYear(d) und MyMonth(d) und MyDay(d), die
zu einer ganzen Zahl Jahr, Monat und Tag liefern, wobei der Wert 1 dem 1.1.1582
entspricht. Wenn ich mich nicht verrechnet habe, muss sich für d = 152623 das
Datum 13.11.1999 ergeben.
Alle Aufgaben nur mit Bordmitteln von Basic selbst lösen, also ohne Verwendung
eingebauter Funktionen wie Month(), Sort() usw.! Ziehen Sie diese Funktionen aber
zum Vergleich und als Probe heran, wobei aber zu bedenken ist, dass bei den eingebauten
Datumsfunktionen die Zahl 0 dem 30.12.1899 entspricht.
88
7 Excel-Objekte
Ich werde jetzt den zeigen, wie VBA und Excel zusammenspielen.
7.1 Die Basis-Objekte
Eine Excel-Datei wird als Arbeitsmappe bezeichnet. Sie ist in Blätter unterteilt, wobei es
zwei Typen gibt, die gewöhnlichen Arbeitsblätter mit den Zellen und die Diagrammblätter. Ein Arbeitsblatt besteht aus Zeilen, Spalten und einzelnen Zellen. Jedem Element
von Excel wird ein bestimmter Objekttyp zugeordnet. Diese Bestandteile werden in VBA
durch Objekte dargestellt, die über Eigenschaften und Methoden und verfügen. Manche
Objekte wie etwa Arbeitsmappen lösen zusätzlich noch Ereignisse aus, etwa die bereits
bekannten Ereignisse Activate und Deactivate. Die Gesamtheit aller gleichartigen Objekte wird als Klasse bezeichnet, wobei für jede Klasse ein eigener Datentyp
eingerichtet wird. Excel benötigt damit zumindest folgende vier neue Datentypen
• Application. Diese Klasse steht für alle Anwendungen des Office-Pakets wie Excel, Word oder Powerpoint. Gleichzeitig gibt es eine vordefinierte Variable dieses
Namens, die das gerade aktuelle Anwendungsobjekt repräsentiert. Das ist etwas
verwirrend, da normalerweise Objekte nicht den Namen ihrer Klasse als Bezeichner
verwenden dürfen.
• Workbook. Diese Klasse steht für die Exceldateien, die als Arbeitsmappen, englisch
workbook bezeichnet werden.
• Sheet. Diese Klasse steht für die Blätter einer Arbeitsmappe, egal ob Arbeitsblatt
oder Diagrammblatt.
• Worksheet. Diese Klasse steht für die eigentlichen Arbeitsblätter.
• Chart. Diese Klasse steht für die Diagrammblätter.
Der Zustand und das Aussehen der Objekte wird durch Eigenschaften, die Aktivitäten
durch Methoden gesteuert. Eigenschaften kann man lesen und meistens auch verändern.
Normalerweise haben Eigenschaften einen einfachen Datentyp, wie etwa die Eigenschaft
Name vom Typ String oder Visible vom Typ Boolean. Bestimmte Eigenschaften und Methoden haben aber selbst einen Objekttyp. Dazu gehören die Eigenschaften
ActiveWorkbook, ActiveSheet sowie ActiveCell der Klasse Application.
Diese Eigenschaften beziehen sich auf die gerade ausgewählte Mappe, bzw. das aktive
Blatt oder dessen ausgewählte Zelle. Da für die Klasse Application ein gleichnamiges
89
7 Excel-Objekte
Objekt gibt, wird die folgende Prozedur die aktuelle Mappe verbergen, den Namen des
aktuellen Arbeitsblatts in TabNeu verändern und in die aktive Zelle den Inhalt »Hallo
Welt« schreiben sowie die aktive Arbeitsmappe schließen. Vergessen Sie also nicht diese
zuvor zu speichern!
Sub ActiveTest()
Application.ActiveCell.Value = "Hallo Welt"
Application.ActiveSheet.Name = "TabNeu"
ActiveWorkbook.Close
End Sub
Es wird Ihnen sicher auffallen, dass ich in der letzten Anweisung auf den Bezeichner
Application verzichtet habe. Generell gilt, dass das Objekt Application meistens
fortgelassen werden darf. Beachten Sie, dass Value und Name Eigenschaften sind,
während es sich bei Close um eine Methode handelt. Eigenschaften und Methoden
werden von ihrem zugehörigen Objekt durch einen Punkt getrennt.
Der Code-Editor zeigt alle Eigenschaften und Methoden eines Objektss in einer Listbox, wenn man den Namen und den anschließenden Punkt wie Application. schreibt
und dann kurz verharrt. Wenn Sie den Anfangsbuchstaben der gewünschten Eigenschaft
oder Methode kennen und eingeben, springt die Listbox an diese Stelle. Sie können die
gewünschte Eigenschaft/Methode markieren und deren Bezeichner durch Drücken der
Tab-Taste in das Codefenster übernehmen. Objekte wie Arbeitsmappen und -blätter
lösen auch Ereignisse aus. Diese werden im Gegensatz von Methoden vom Benutzer
ausgelöst, etwa Maustaste drücken oder loslassen oder Eingaben über die Tastatur. Ereignisse werden vom Betriebssystem registriert und nach Auswertung an die zuständigen
Programme zur Verarbeitung weitergeleitet. Daher muss das betroffene Programm, das
Ereignis mit einer Prozedur verknüpfen, die so genannte Ereignisbehandlungsprozedur,
auf englisch event handler. Im Gegensatz zu den bereits von Excel programmierten
Methoden müssen die Ereignisbehandlungsprozeduren selbst geschrieben werden. Die
Ereignisbehandlungsprozeduren eines Arbeitsblatts gehören zum Code dieses Blatts.
7.1.1 Auflistungen
Im Excel-Objektmodell werden gleichartige Objekte wie Arbeitsmappen und -blätter zu
einem so genannten Auflistungs-Objekt gebündelt. Drei besonders wichtige Auflistungen
sind:
• Workbooks. Dies ist eine Auflistung aller Workbook-Objekte, die momentan in der
Anwendung Microsoft Excel geöffnet sind. Auf diese Liste wird über die WorkbooksEigenschaft des Application-Objekts zugegriffen.
• Sheets. Eine Auflistung aller Blätter in der angegebenen oder aktiven Arbeitsmappe. Über die Sheets-Auflistung können Blätter eines beliebigen Typs zurückgegeben werden. Genauer legt man sich mit den beiden Teilaufzählungen Charts und
Worksheets fest.
90
• Worksheets. Diese Auflistung enthält lediglich die Arbeitsblätter.
• Charts. Diese Auflistung enthält alle Diagrammblätter.
Zu diesen Auflistungen gelangt man über Eigenschaften des jeweils übergeordneten Behälters. Die Workbooks-Auflistung ist Eigenschaft des Application-Objekts, die Auflistungen Sheets und Worksheets wiederum sind Eigenschaften des übergeordneten
Workbook-Objekts. Alle Auflistungen sind selbst natürlich auch wieder Objekte und
haben somit Eigenschaften und Methoden. Eine wichtige Eigenschaft heißt Count und
gibt die Anzahl der Elemente in der Liste wieder. Diese Eigenschaft ist nur lesend.
Zu einem einzelnen Objekt der Aufzählung gelangt man auf zwei unterschiedliche
Weisen, entweder durch seine fortlaufende Nummer oder über den Bezeichner. Falls die
Arbeitsmappe eeeprofs.xls geöffnet ist, gelangt man zum ersten Arbeitsblatt wie folgt
Application.Workbooks("eeeprofs.xls").Worksheets(1).Activate
Die Objekte vom Typ Application, Workbook und WorkSheet sind Behälter
für die entsprechenden untergeordneten Objekte, wobei jede Auflistung der Objekte eines
untergeordneten Typs Eigenschaft des übergeordneten Typs ist. Man gelangt daher an
die untergeordneten Objekte nur über den vollständigen Pfad der Abhängigkeiten. Der
Inhalt der Zelle B1 des zweiten Arbeitsblatts der Mappe eeeprofs.xls wird im VBA-Code
wie folgt verändert
Application.Workbooks("eeeprofs.xls"). _
Worksheets(1).Cells(1,2).Value = "Hi"
Ich habe dabei gleich noch eine weitere Auflistung, nämlich Cells(z,s) eingeführt.
Diese Auflistung ist Eigenschaft der Objekte vom Typ Worksheet, wobei in den Klammern zunächst der Zeilen- und dann der Spaltenindex anzugeben ist. Die Zelle B1 steht
in der ersten Zeile und in der zweiten Spalte. Das Hierarchiekonzept von Excel zeigt die
folgende Abbildung stark vereinfacht:
Abbildung 7.1: Hierarchie der Excel-Objekte
7.1.2 Objektvariable
Auch für Objekte gibt es Variable, wobei die Wertzuweisung das zusätzliche Schlüsselwort Set erfordert. Das ist lästig und eigentlich überflüssig, aber unbedingt nötig.
91
7 Excel-Objekte
Objektvariable ermöglichen kürzeren und übersichtlicheren Code. Sollen etwa die Zellen
B1 und C1 des zweiten Arbeitsblatts der Mappe eeeprofs.xls die Werte »Hallo« und
»Welt« bekommen, geht dies mit Objektvariablen wie folgt:
Sub MacheHallo()
Dim xlApp As Application
Dim xlWb As Workbook
Dim xlWs As Worksheet
Set xlApp = Application
Set xlWb = xlApp.Workbooks("eeeprofs.xls")
Set xlWs = xlWb.Worksheets(2)
xlWs.Cells(1, 2).Value = "Hallo"
xlWs.Cells(1, 3).Value = "Welt"
End Sub
7.1.3 Die With-Syntax
Oft beziehen sich mehrere Aktionen auf ein Objekt. Dann erspart die With-Syntax die
mehrmalige Angabe des Objekts. In der folgenden Prozedur wird die Schriftart und
-farbe sowie die Hintergrundfarbe der Zelle C3 des zweiten Tabellenblatts der ersten
Arbeitsmappe festgelegt.
Sub MaleC3()
With Workbooks(1).Worksheets(2).Cells(1,3)
With .Font
.ColorIndex = 3
.Name = "Century Gothic"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Bold = True
End With
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End With
End Sub
Excel verschlüsselt Farben mit der Eigenschaft ColorIndex, wobei jedem Wert zwischen 0 und 56 eine Farbe zugeordet wird. Die Eigenschaft Interior einer Zelle be-
92
schreibt deren Hintergrund, die Eigenschaft Font deren Schriftart. Da der Wert 3 rot
entspricht und 13 hellgrau, wird die Zelle C3 nun mit roter, fetter Schrift der Schriftart
»Century Gothic« auf grauen Hintergrund erscheinen.
7.1.4 Durchlaufen von Listen
Oft ist es nötig alle Elemente einer Liste zu betrachten und für jedes Element einen
bestimmten Code auszuführen. Die Iteration erfolgt durch eine gewöhnliche Schleife, die
von 1 bis zur Eigenschaft Count verläuft, wobei die Listenelemente durch ihren Index angesprochen werden. Für Listen gibt es aber noch eine zweite Syntax, und zwar
For Each listEl In Auflistung ... Next. Dabei ist listEl eine Objektvariable vom Typ der Elemente der Auflistung. Im folgenden Beispiel sollen in der Datei
eeeprofs.xls alle Tabellenblätter mit Ausnahme der ersten verborgen werden. Dazu muss
in der äußeren Schleife zunächst die richtige Arbeitsmappe ausgewählt werden, in der
inneren Schleife werden die Arbeitsblätter verborgen. Diese Prozedur steht im Modul
For_Each der Arbeitsmappe eeeprofs.xls. In diesem Modul habe ich noch zwei weitere Prozeduren eingefügt. Die eine enthüllt die verborgenen Arbeitsblätter wieder, die
andere zeigt in einem Meldungsfenster alle geöffneten Arbeitsmappen.
Option Explicit
Sub Verbergen()
Dim i As Long
For Each xlWb In Workbooks
If (LCase(xlWb.Name) = "eeeprofs.xls") Then
For i = 2 To xlWb.Worksheets.Count
xlWb.Worksheets(i).Visible = xlSheetHidden
Next i
Exit Sub
End If
Next
End Sub
Sub Zeigen()
Set xlWb = Workbooks("eeeprofs.xls")
For Each xlWs In xlWb.Worksheets
xlWs.Visible = xlSheetVisible
Next
End Sub
93
7 Excel-Objekte
Sub ZeigeDateiNamen()
Dim aus As String
For Each xlWb In Workbooks
aus = aus & xlWb.Name & vbCr
Next
MsgBox aus
End Sub
7.1.5 Methoden von Workbook und Workbooks
Das Öffnen, Speichern und Schließen von Arbeitsmappen wird von Methoden dieser
beiden Klassen erledigt. Wie es genau geht, erfährt man durch Aufzeichnung der Operationen in einem Makro. Das folgende Makro zeichnete das Öffnen, Speichern, Schließen
und Speichern unter neuem Namen von Arbeitsmappen auf. Beachten Sie, dass das Öffnen eine Methode der Klasse Workbooks, Speichern und Schließen dagegen der Klasse
Workbook ist.
Sub Makro1()
Workbooks.Open Filename:= "C:\gaga\Mappe1.xls"
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.SaveAs Filename:= _
"C:\ws_08\eeeprofs_neu.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
7.1.6 Methoden von Worksheet und Worksheets
Im folgenden Makro habe ich einige Operationen wie Umbenennen, Löschen und Neuanlegen von Arbeitsblättern aufgezeichnet. Der Code spricht für sich selbst:
Sub Makro2()
Sheets("Tabelle3").Name = "NeuerName"
Sheets("TabNeu").Select
Sheets.Add
Sheets("Tabelle1").Move Before:=Sheets(1)
94
7.2 Die Klasse Range
Sheets("TabNeu").Name = "TabNeu"
ActiveWindow.SelectedSheets.Delete
End Sub
Die Klasse Range ist die wichtigste Klasse in VBA für Excel. Sie umfasst sowohl einzelne
Zellen als auch Zellbereiche, d.h. es gibt für einzelne Zellen keinen eigenen Typ! Diese
Klasse ist sehr umfangreich. Zum Löschen von Inhalten, Formaten oder allem gibt es die
Methoden ClearContents, ClearFormats und Clear.
Sehr nützlich sind die Eigenschaften Columns und Rows, welche sich auf die Spalten
und Zeilen des Range-Objekts beziehen. Die Anzahl der Spalten ergibt sich aus der
Eigenschaft Count. Damit lassen sich bequem alle Zellen des Bereichs durchlaufen.
Sie dürfen diese Auflistungen nicht zwei fast identisch geschriebenen Eigenschaften
verwechseln, und zwar Row und Column. Diese Eigenschaften geben den Spaltenbzw. Zeilenindex der ersten Zelle links oben im Bereich wieder.
7.2.1 Zeilen und Spalten
Verwenden Sie die Eigenschaften Rows oder Columns, um mit ganzen Zeilen oder Spalten
zu arbeiten. Diese Eigenschaften geben ein Range-Objekt zurück, das einen Zellbereich
darstellt. Im folgenden Beispiel gibt Rows(1) die erste Zeile auf Sheet1 zurück. Anschließend wird die Bold-Eigenschaft des Font-Objekts für diesen Bereich auf True festgelegt.
Sub RowBold()
Worksheets("Sheet1").Rows(1).Font.Bold = True
End Sub
Die folgende Tabelle verdeutlicht, wie Zeilen- und Spaltenbezüge mit den Eigenschaften
Rows und Columns verwendet werden können.
Bezug Bedeutung Rows(1) Zeile eins Rows Alle Zeilen des Arbeitsblattes Columns(1)
Spalte eins Columns(Ä") Spalte eins Columns Alle Spalten des Arbeitsblattes
Um gleichzeitig mit mehreren Zeilen oder Spalten zu arbeiten, erstellen Sie eine Objektvariable, und verwenden Sie die Union-Methode, um mehrere Aufrufe mit der Eigenschaft Rows bzw. Columns zu verbinden. Im folgenden Beispiel wird das Format
der Zeilen eins, drei und fünf im ersten Arbeitsblatt der aktiven Arbeitsmappe in fett
geändert.
Sub SeveralRows()
Worksheets("Sheet1").Activate
Dim myUnion As Range
Set myUnion = Union(Rows(1), Rows(3), Rows(5))
myUnion.Font.Bold = True
End Sub
95
7 Excel-Objekte
7.2.2 Die Eigenschaft CurrentRegion
Die CurrentRegion-Eigenschaft gibt ein Range-Objekt zurück, das den aktuellen Bereich
darstellt. Der aktuelle Bereich wird von einer beliebigen Kombination leerer Zeilen und
Spalten umschlossen. Der Bereich CurrentRegion ist der größte zusammenhängende
Datenblock um eine Zelle herum. In der nächsten Prozedur wird die Dimension dieses
Bereichs für die gerade aktuelle Zelle in einem Meldungsfenster ausgegeben:
Sub Groesse_von_Bereich()
Dim r As Integer, s As Integer
Dim str As String
Dim rg As Range
With ActiveCell
r = .Row
s = .Column
Set rg = .CurrentRegion
End With
str = "Der Tabellenbereich um die Zelle (" & r & ", " & s
str = str & ") hat " & rg.Rows.Count & " Zeilen "
str = str & "und " & rg.Columns.Count & " Spalten!"
str = str & " Der Bereich begint in Zeile " & rg.Row
str = str & " und Spalte " & rg.Column
MsgBox str
End Sub
7.2.3 Die Cells-Auflistung
Eine einzelne Zelle wird über die bereits eingeführte Auflistung Cells oder in der
A1-Notation ausgewählt, wobei die Koordinaten der Zelle dann in eckigen Klammern
erscheinen. Im folgenden Programmstück wird auf zwei verschiedene, aber gleichwertige
Weisen die Zelle C4 ausgewählt:
Sub ZellTest()
Set xlWs = Workbooks(1).Worksheets(2)
xlWs.Select
’xlWs.Cells(4, 3).Select
xlWs.[C4].Select
ActiveCell.Value = "Hi"
End Sub
96
Einen rechteckigen Bereich oder auch eine einzelne Zelle liefert die Range-Eigenschaft
der Klasse WorksheetḊas Argument dieser Eigenschaft ist die einzelne Zelle oder dergewünschte Zellbereich, wobei die Angaben in der A1-Noptation oder über die CellsAuflistung erfolgen kann. Im folgenden Beispiel wird der Wert der Zelle A5 des zweiten
Arbeitsblattes von der Zelle A6 des dritten Arbeitsblattes übernommen:
Sub RangeTest()
Workbooks(1).Activate
Worksheets(3).Range("A6").Value = _
Worksheets(2).Cells(4, 3).Value
Worksheets(3).Activate
Range(Cells(1, 1), Cells(3, 57)).Clear
Range("A1:D3").Formula = "=Rand()"
Columns("A:D").EntireColumn.AutoFit
End Sub
Im folgenden Beispiel wird der Bereich Ä1:H8"mit Zufallszahlen aufgefüllt, indem für
jede Zelle des Bereichs die Formel festgelegt wird. Bei Verwendung ohne Objektkennzeichner (das Objekt links vom Punkt) gibt die Range-Eigenschaft einen Bereich im
aktiven Blatt zurück. Ist das aktive Blatt kein Arbeitsblatt, schlägt die Methode fehl.
Aktivieren Sie mit Hilfe der Activate-Methode ein Arbeitsblatt, bevor Sie die RangeEigenschaft ohne expliziten Objektkennzeichner einsetzen.
Sie können mit der Cells-Eigenschaft auf eine einzelne Zelle verweisen, indem Sie
die Indexnummern der jeweiligen Zeile und Spalte verwenden. Diese Eigenschaft gibt
ein Range-Objekt zurück, das eine einzelne Zelle darstellt. Im folgenden Beispiel gibt
Cells(6,1) die Zelle A6 von Sheet1 zurück. Danach wird die Value-Eigenschaft auf 10
festgelegt.
Sub EnterValue()
Worksheets("Sheet1").Cells(6, 1).Value = 10
End Sub
Die Cells-Eigenschaft eignet sich besonders gut dafür, Zellbereiche in einer Schleife
zu durchlaufen, da Sie für die Indexzahlen Variablen einsetzen können. Dies macht das
folgende Beispiel deutlich.
Sub CycleThrough()
Dim Counter As Integer
For Counter = 1 To 20
Worksheets("Sheet1").Cells(Counter, 3).Value = Counter
Next Counter
End Sub
97
7 Excel-Objekte
7.2.4 Die Offset-Eigenschaft von Bereichen
Die Offset-Eigenschaft gibt ein Range-Objekt zurück, das einen Bereich darstellt, der
gegenüber dem angegebenen Bereich versetzt ist. Die Syntax lautet
Range_Objekt.Offset(RowOffset, ColumnOffset)
wobei Range_Objekt ein Ausdruck des Typs Range ist. Der neue Bereich ist gegenüber dem Ausgangsbereich verschoben. Dabei bestimmt RowOffset die Anzahl der
Zeilen (positiv, negativ oder 0 (Null)), um die der Bereich versetzt werden soll. Bei positiven Werten erfolgt ein Versatz nach unten, bei negativen Werten ein Versatz nach
oben. Der Standardwert ist 0. Entsprechend versetzt das zweite Argument die Spalten.
Meistens ist der Ausgangsbereich eine Zelle.
Zur Darstellung aller Farben und ihres Codes verhilft die folgende Prozedur:
Sub ZeigeFarben()
Dim i As Long
Workbooks(1).Worksheets(3).Activate
ActiveSheet.Rows("1:6").Delete
With ActiveSheet.Cells(1, 1)
For i = 0 To 56
Columns(i + 1).ColumnWidth = 2
.Offset(0, i).Value = i
With .Offset(1, i).Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Next i
End With
End Sub
98
8 Formulare
Formulare sind aus Anwendungsprogrammen, die unter Windows laufen, nicht mehr weg
zu denken. Sie werden in verschiedenen Situationen von der Anwendung eingeblendet,
um bestimmte Informationen vom vom Benutzer einzuholen. Formulare sind standardisiert und werden aus Steuerelementen aufgebaut. Die bekanntesten Steuerelemente sind
Schaltflächen, sowei Text- und Eingabefelder. Fast genauso wichtig sind Listenfelder,
Optionsfelder und Kontrollkästchen.
8.1 Aufgabenstellung
In der Abbildung 8.1 ist das Formular zu sehen, das erstellt werden soll. Es soll auftauchen, sobald die erste Tabelle aktiviert wird und wieder verschwinden, wenn eine
andere Tabelle gezeigt wird. Das Textfeld soll beim Erscheinen des Formulars den Inhalt
der Zelle A1 der ersten Tabelle anzeigen. Der Anwender kann im Textfeld diesen Inhalt
verändern. Durch Drücken der Schaltfläche wird der neue Inhalt dann in der Zelle A1
übernommen. Das ist keine sinnvolle Aufgabe für ein Formular, der eigentliche Zweck
ist die Demonstration der grundsätzlichen Vorgehensweise.
8.2 Erstellen von Formularen
Formulare sind komplizierter als Module. Microsoft bezeichnet sie als Userform oder
Benutzerformular. Ich werde meistens bei der Bezeichnung Formular oder Dialogfeld
bleiben.
Wechseln Sie über Alt+F11 nach Visual Basic und stellen dort den Entwurfsmodus
her. Jedem Formular ist in VBA ein UserForm-Objekt zugeordnet, das innerhalb der
Entwicklungsumgebung von VBA durch die Befehlsfolge Einfügen|UserForm erzeugt
wird.
Dann öffnet sich das so genannte UserForm-Fenster mit dem noch jungfräulichen Formular und der Werkzeugsammlung (Toolbox), so wie in der Abbildung 8.1 zu erkennen.
Allerdings ist dieses Formular bereits mit Steuerelementen besiedelt, wie das geht, wird
gleich gezeigt. Die Toolbox ist frei verschiebbar und entspricht der Symbolleiste Werkzeugsammlung, die in Abbildung 2.1 rechts zu sehen ist. Dort finden Sie auch die Beschreibung der einzelnen Symbole, sofern Sie deren Bedeutung nicht ohnehin erkennen.
Die Funktionen einiger Steuerelemente werde ich kurz beschreiben.
Für einfache Ja/Nein-Entscheidungen sind die viereckigen Kontrollkästchen zuständig, durch Anklicken erscheint oder verschwindet ein Kreuz oder Häkchen. Listenfelder
99
8 Formulare
Abbildung 8.1: Der Visual Basic Editor
lassen aus mehreren Möglichkeiten ein Feld auswählen. Die Wahl aus einer Menge sich
gegenseitig ausschließenden Optionen bietet auch eine Gruppe von Optionsfeldern, die
im Gegensatz zu den Kontrollkästchen mit runden Feldern erscheinen. Eingaben geschehen in einzeiligen und mehrzeiligen Textfeldern, die aber auch für Eingaben deaktiviert
werden können und dann ausschließlich der Textausgabe dienen. Die meist grau dargestellten Schaltflächen lösen bestimmte Aktionen aus.
8.3 Einfügen von Steuerelementen in ein Formular
Um nun ein Steuerelement auf einem Formular abzulegen, muss in der Werkzeugsammlung das zugehörige Symbol angeklickt werden. Der Mauszeiger nimmt die Form eines
Kreuzes an. An geeigneter Stelle wird dann im Formular ein Rahmen gezogen, worin
dann das Steuerelement erscheint. Anschließend passen Sie Größe und Form des Steuerelements unter Verwendung eines seiner Ziehpunkte nach Ihren Wünschen an. Jedes
Steuerelemnt hat acht Ziehpunkte. Diese erscheinen, sobald das Steuerelement aktiviert
wird. In der Abbildung 8.1 ist das Formular selbst ausgewählt, von seinen acht Ziehpunkten sind nur fünf zu sehen, die drei rechten liegen unter der Toolbox. Ich habe auf
diese Weise von links nach rechts dem Formular ein Bezeichnungsfeld, ein Textfeld und
eine Schaltfläche hinzugefügt. Die Feineinstellung des Erscheinungsbilds erfolgt in einem
Fenster, dem Eigenschaftenfenster.
100
8.4 Eigenschaften von Steuerelementen
8.4 Eigenschaften von Steuerelementen
Ein Steuerelement ist ausgewählt, wenn es mit den Ziehpunkten und den gepunkteten
Rahmen erscheint. Dann erscheinen die Eigenschaften des aktivierten Steuerelements im
Eigenschaftenfenster. Sollte das nicht der Fall sein, müssen Sie die F4-Taste drücken. In
der Abbildung 8.1 ist das Formular selbst ausgewählt. Eigenschaften bestimmen Aussehen wie Größe, Farbe, Font und Bildschirmposition sowie Zustände, wie aktiviert oder
deaktiviert. Das Eigenschaftenfenster zeigt die gewählten Eigenschaften. Ganz oben steht
das ausgewählte Steuerelement, in meinem Beispiel UserForm1. Über das Listenfeld
können alle anderen Steuerelemente des aktiven Formulars angezeigt und ausgewählt
werden.
Die Eigenschaften werden entweder alphabetisch oder nach Kategorien angezeigt. Das
lässt sich über die beiden Registerkarten steuern. Wenn Sie sich die Eigenschaften nach
Kategorien anzeigen lassen, können Sie über Pluszeichen(+) und Minuszeichen(-) die
Liste einer Kategorie ein- und ausblenden.
Die Eigenschafteneinstellung wird durch Auswahl des Eigenschaftennamens rechts und
Eingabe oder Auswahl der neuen Einstellung links geändert.
8.4.1 Wichtige Eigenschaften
Die wichtigste Eigenschaft überhaupt ist die Eigenschaft Name. Über diese Eigenschaft
wird das Steuerelement im Code identifiziert.
Der Standardwert von Name besteht bei Objekten aus dem Klassennamen des Objekts,
gefolgt von einer ganzen Zahl. Der Standardname für das erste Textfeld, Klassenname
TextBox, das Sie auf einem Formular anlegen, ist z. B. TextBox1. Der Standardname
für das zweite Textfeld-Steuerelement ist TextBox2. Genauso verhält es sich mit allen anderen Steuerelementen, Bezeichnungsfelder, deren Klassenname Label ist, haben
z.B. die Standardnamen Label1, Label2 usw. Sie dürfen diese Namen zur Entwurfszeit ändern, aber nicht mehr zur Laufzeit. Das unterscheidet diese Eigenschaft von allen
anderen Eigenschaften.
Die Eigenschaft Name wird leicht mit der Eigenschaft Caption verwechselt, da auch
diese Eigenschaft zunächst mit dem Klassenname des Objekts, gefolgt von einer ganzen
Zahl, vorbesetzt wird. Die Eigenschaft Caption haben u.a. Schaltflächen, Bezeichnungsfelder und Formulare. Bei Schaltflächen und Bezeichnungsfeldern wird damit die
Beschriftung festgelegt und bei Formularen der Fenstertitel. In der Abbildung 2.2 habe
ich über diese Eigenschaft den Titel von UserForm1 in Testformular umgeändert.
Auch die Beschriftungen der Schaltfläche und des Bezeichnungsfeldes habe ich so verändert. Die Namen habe ich dagegen übernommen, somit heißen die drei Steuerelemente
von links nach rechts Label, TextBox1 und CommandButton1 nach den englischen
Klassennamen.
Die dritte bedeutende Eigenschaft heißt Value. Diese gibt den Zustand oder Inhalt
des Steuerelements an. Bei einem Textfeld ist dies der Text im Bearbeitungsbereich. Bei
einem Kontrollkästchen und einem Optionsfeld hat Value den Wert -1 bzw. True, wenn
das Element ausgewählt ist, und sonst den Wert 0 oder False.
101
8 Formulare
Über die Eigenschaften Top, Left, Width und Height können Sie die Steuerelemente pixelgenau auf dem Formular platzieren. Diese Eigenschaften bestimmen die obere
linke Ecke sowie Breite und Höhe des Steuerelements. Die obere linke Ecke des Formulars
selbst hat die Werte 0 für Left und Top.
8.5 Ereignisse von Steuerelementen
Ein Ereignis (engl. event) dient bei Formularen zur Steuerung des Programmablaufes.
In VBA sind für jedes Steuerelement Ereignisse vorgesehen, die bestimmten Aktionen
des Benutzers wie Klicken oder Drücken einer Taste entsprechen. Zu jedem Ereignis gibt
es eine Ereignisbehandlungsroutine, die immer dann ausgeführt wird, wenn das Ereignis
auftritt. Die beiden wichtigsten Ereignisse heißen in VBA Click und Change. Die zugeordnete Ereignisbehandlungsroutine setzt sich aus dem Namen des Steuerelements und
dem Ereignis zusammen. Dieser Bezeichner darf nicht verändert werden. Kein Programmierer kann die Art und die Reihenfolge der Ereignisse vorhersehen, er muss trotzdem
für alle Fälle gewappnet sein. Die Kunst der ereignisorientierten Programmierung besteht darin, alle möglichen Ereignisse und Systemzustände vorherzusehen und immer
die richtige Antwort aus dem Ärmel zu ziehen. Alle Programme mit grafischer Oberfläche, speziell alle Windowsprogramme, sind ereignisorientiert, aber auch die meisten
Steuerprogramme für technische Prozesse und Anlagen.
Formulare gelten auch als Steuerelement. Das wichtigste Ereignis ist Initialize. Es tritt
unmittelbar bevor das Formular erscheint ein. Daher wird in der zugehörigen Ereignisbehandlungsroutine das Formular mit sinnvollen werten vorbesetzt.
8.6 Ereignisse und Eigenschaften des Programms
Das Formular besteht aus vier Objekten, dem Formular selbst und den drei Steuerelementen. Von links nach rechts sind dies ein Bezeichnungsfeld, ein Textfeld und eine
Schaltfläche.
Ein Bezeichnungsfeld wird auf englisch Label genannt. Es zeigt in der Regel einen
statischen Text, der andere Steuerelemente beschreibt, wie hier das Eingabefeld. Die
Einstellung des Beschriftung erfolgt über die Caption-Eigenschaft. Bezeichnungsfelder
lösen in der Regel keine Ereignisse aus.
Ein Textfeld, englisch TextBox, dient der Eingabe des Benutzers, kann aber auch zur
Ausgabe verwendet werden. Textfelder sind dynamisch. Ihre Standardeigenschaft ist die
Value-Eigenschaft. Das Standardereignis für Textfelder ist das Change-Ereignis.
Schaltflächen sind fast auf jedem Formular vorhanden. Ihr Standardereignis ist das
Click-Ereignis, die wichtigste Eigenschaft ist Caption, um die Beschriftung festzulegen.
Ich habe alle Namen übernommen, also UserForm1, Label1, TextBox1 und CommandButton1.
Dann habe ich die Caption-Eigenschaft des Formulars, der Schaltfläche und des Bezeichnungsfelds verändert. Beim Formular entspricht Caption dem Titel, bei den beiden
102
8.7 Die Tabellenereignisse
Steuerelementen der Beschriftung.
Ich habe beim Formular die Eigenschaft ShowModal auf False gesetzt.
Formulare erscheinen standardmäßig, d.h. beim Wert False für die Eigenschaft ShowModal, als modale Pop-up-Fenster vor dem Hauptfenster der Anwendung, d. h. der
Benutzer kann in den anderen Fenstern derselben Anwendung nicht weiterarbeiten, solange das Formular angezeigt wird. Hier möchte ich aber, dass der Anwender die Tabellen
bearbeiten kann, auch wenn der der Dialog geöfnet ist.
Schon diese einfache Programm benötigt vier Ereignisbehandlungsroutinen. Das Formular selbst kommt mit zwei aus, und zwar das Click-Ereignis der Schaltfläche und das
Initialize-Ereignis des Formulars.
Die Tabelle Tabelle1 sorgt für die beiden weiteren Ereignisse. Ja, Sie lesen richtig,
auch Tabellen sind Auslöser von Ereignissen, und zwar Activate und Deactivate.
8.7 Die Tabellenereignisse
Ereignisse werden durch Ereignisbehandlungsroutinen beantwortet, also durch Code.
Klicken Sie innerhalb des Projekt-Explorers doppelt auf den Eintrag Tabelle1. Es erscheint wieder das Code-Fenster, wählen Sie im linken Listenfeld den Eintrag Worksheet
und rechts Activate. Dann erscheint im Code-Fenster eine Basisversion der zugehörigen
Ereignisbehandlungsroutine. Ich musste nur eine einzige Anweisung hinzufügen, vgl. dazu Abbildung 8.2
Abbildung 8.2: Das Code-Fenster der Tabelle1
Das Ereignis Activate tritt immer ein, wenn die entsprechende Tabelle aktiviert wird.
Dann soll über die Methode Show der Dialog erscheinen und sich durch die Methode Hide diskret zurückziehen, wenn die Tabelle Tabelle1 nicht mehr ausgewählt ist. Diese wird
aufgerufen, wenn die Tabelle Tabelle1 nicht mehr aktiv ist, weil eine andere ausgewählt
wurde. Das entsprechende Ereignis ist Deactivate. Der Code der beiden Ereignisbehandlungsroutinen ist ebenfalls in der Abbildung 8.2 zu sehen.
103
8 Formulare
Achten Sie jetzt noch einmal genau auf das Fenster des Projekt-Explorers. Die Tabelle
Tabelle1 ist hervorgehoben und das Symbol für Code ganz links ist gedrückt. Im Codefenster sind im linken Listenfeld der Eintrag Worksheet und im rechten das Ereignis
Activate ausgewählt. Der Wechsel zum Formular geschieht im Listenfeld des ProjektExplorers durch Auswahl des Eintrags UserForm1. Dann wird zunächst das Formular
erscheinen. Durch Drücken der F7-Taste oder des Symbols Code anzeigen im ProjektExplorer öffnet sich das Code-Fenster für das Formular.
Tipp
Der Arbeitsbereich für Code- und Userform ist ein so genanntes MDI-Fenster, in dem
mehrere Fenster gleichzeitig geöffnet werden können, z.B. mehrere Code-Fenster, aber
auch wie in Abbildung 8.1 sowohl das Code- als auch das Userform-Fenster eines Formulars. Sie erreichen dies durch die üblichen Windowstechnikem über den Menüpunkt
Fenster.
8.8 Die Formulareignisse
Wenn Sie Vollbilder bevorzugen, erfolgt die Umschaltung zwischen Userform- und CodeFenster über die beiden Symbole im Projekt-Explorer oder durch die Taste F7 für Code
und Umschalt + F7 für die Userform.
Auch im Codefenster eines Formulars befinden sich oben zwei Listenfelder, wobei links
alle Steuerelemente des Formulars und das Formular selbst unter UserForm erscheint.
In dem Listenfeld rechts sind alle Ereignisse aufgeführt, worauf das links ausgewählte
Steuerelement über Ereignusbehandlungsroutinen antworten kann, natürlich über Visual
Basic Code. Bei Formularen reagiert man meistens nur auf das Ereignis Initialize, das
eintritt, wenn das Formular neu erstellt wird und die Steuerelemente des Formulars in
einen vernünftigen Ausgangszustand gebracht werden sollen.
Mein Code ist in Abbildung 8.1 zu erkennen. Über die Eigenschaft Value erhält das
Textfeld den Inhalt der Zelle A1 der Tabelle Tabelle1.
Die Umkehrung erfolgt über das Click-Ereignis der Schaltfläche. Zunächst muss die
Tabelle Tabelle1 aktiviert werden und danach erhält die Zelle A1 über die Eigenschaft
Value als Wert den Inhalt des Textfeldes.
8.8.1 Test des Codes
Beenden Sie zunächst über das entsprechende Symbol den Entwurfsmodus und wechseln über Alt+ F11 zurück nach Excel. Jedesmal, wenn Sie die erste Tabelle auswählen,
sollte das Formular erscheinen und wieder verschwinden, wenn eine andere Tabelle erscheint. Auch der Austausch der Werte zwischen dem Formular und der Zelle A1 sollte
reibungslos klappen.
104
8.9 Eigenschaften, Methoden und Ereignisse
8.9 Eigenschaften, Methoden und Ereignisse
Nachdem Sie nun mnit der Entwicklungsumgebung von Visual Basic vertraut sind, möchte ich ein das Beispiel noch ein wenig ausbauen, um die Prinzipien der objekt- und
ereignisorientierten Programmierung zu erläutern.
8.9.1 Objekte
Steuerelemente sind Objekte, die über Eigenschaften, Ereignisse und Methoden verfügen.
Weitere Objekte in VBA sind Programme wie Excel, Arbeitsmappen, Tabellen oder
Grafiken. Nicht alle Objekte lösen Ereignisse aus, aber alle Objekte haben Eigenschaften
und Methoden. Die Gesamtheit aller gleichartigen Objekte wird als Klasse bezeichnet.
Die Schaltflächen bilden z.B. die Klasse CommandButton und die Textfelder die Klasse
TextBox. Der Zustand und das Aussehen des Objekts wird durch seine Eigenschaften,
die Aktivitäten des Objekts wird durch die Menge seiner Operationen beschrieben. Diese
Operationen nennt man Methoden.
8.9.2 Eigenschaften und Methoden
Eigenschaft wie z.B. die Eigenschaft Caption werden durch Variablen realisiert. Der Wert
der Eigenschaft kann während der Entwicklungszeit gesetzt werden und darf im Programm jederzeit verändert werden. Die Beschriftung der Schaltfläche namens CmdBtn
wird durch folgende Anweisung geändert:
CmdBtn.Caption = "Bitte, bitte drück mich!"
Methoden sind Fähigkeiten und Aktionen, die von den Objekten ausgeführt werden. Einer Methode entspricht also immer eine Prozedur, also ausführbarer Code. Eine Schaltfläche kann sich auf dem Formular mit Hilfe der Methode Move bewegen, Ein Formular
kann sich über die Methode Hide verbergen und ein Tabellenblatt löscht sich über die
Methode Delete selbst aus. Jedes Objekt vefügt über eine klar abgegrenzte Zahl von Eigenschaften und Methoden, daher spricht man von objektorientierter Programmierung.
Der Zugriff auf eine Eigenschaft oder eine Methode erfolgt lesend und schreibend durch
den Namen des Objekts und den Namen der der Eigenschaft bzw. der Methode, wobei
Objektname und Eigenschafts- bzw. Methodenname durch einen Punkt voneinander
getrennt werden. Da Methoden Prozeduren sind, können sie Argumente haben, die in
VBA ohne Klammern (!) durch Kommas getrennt aufgelistet werden. Betrachten wir die
folgenden drei Anweisungen
CmdBtn.Move CmdBtn.Left + 10, CmdBtn.Top + 20
UserForm1.Hide
Sheets("Tabelle3).Delete
In der ersten Anweisung ist Move eine Methode und Left und Top sind sowohl Eigenschaften der Schaltfläche als auch Übergabeparameter an die Methode.
105
8 Formulare
Hinweis
Der Code-Editor zeigt alle Eigenschaften und Methoden eines Steuerelements in einer
Listbox, wenn man den Namen und den anschließenden Punkt wie CommandButton1.
schreibt und dann kurz verharrt. Wenn Sie den Anfangsbuchstaben der gewünschten
Eigenschaft oder Methode kennen und eingeben, springt die Listbox an diese Stelle.
Sie können die gewünschte Eigenschaft/Methode markieren und deren Bezeichner durch
Drücken der Tab-Taste in das Codefenster übernehmen.
Der Code-Editor zeigt alle Eigenschaften und Methoden eines Steuerelements in einer
Listbox, wenn man den Namen und den anschließenden Punkt wie CommandButton1.
schreibt und dann kurz verharrt. Wenn Sie den Anfangsbuchstaben der gewünschten
Eigenschaft oder Methode kennen und eingeben, springt die Listbox an diese Stelle.
Sie können die gewünschte Eigenschaft/Methode markieren und deren Bezeichner durch
Drücken der Tab-Taste in das Codefenster übernehmen.
8.9.3 Unterschied zwischen Ereignissen und Methoden
Ereignisse werden im Gegensatz von Methoden vom Benutzer ausgelöst, etwa Maustaste
drücken oder loslassen oder Eingaben über die Tastatur. Ereignisse stehen immer mit
den Steuerelementen einer UserForm in Zusammenhang. Aber auch Arbeits- und Arbeitsblätter lösen Ereignisse aus, etwa das Aktivieren und Deaktivieren eines Blattes
oder das Laden oder Speichern einer Mappe.
Ereignisse werden vom Betriebssystem registriert und nach Auswertung an die zuständigen Programme zur Verarbeitung weitergeleitet. Daher muss das betroffene Programm,
das Ereignis mit einem Prozedur verknüpfen, dei so genannte Ereignisbehandlungsprozedur, auf englisch de event handler. Im Gegensatz zu den bereits von Excel programmierten Methoden müssen die Ereignisbehandlungsprozeduren selbst geschrieben werden.
Diese sind bei Steuerelementen objektorientiert gesehen Methoden des Formulars und
nicht des Steuerelements, also gehört zwar das Ereignis zum Steuerelement, aber die
Ereignisbehandlungsprozedur zum Formular.
8.10 Steuerelemente auf Tabellen
Sie können auch auf Tabellen Steuerelemente einfügen. Dazu muss die Symbolleiste Visual Basic geöffnet sein. Darauf befindet sich das Symbol für die Toolbox, worüber diese
geöffnet wird. Sie können diese Symbolleiste auch durch den Befehl Ansicht| Symbolleisten und Klicken von Steuerelement-Toolbox erhalten. Sie können jetzt Steuerelemente genau wie bei Formularen auf die Tabelle ziehen. Sobald Sie das tun, ändert sich
der Zustand des Symbols Entwurfsansicht von inaktiv zum aktiv. Die Steuerelemente
werden in diesem Zustand bearbeitet, können aber nicht verwendet werden. Die Umschaltung erfolgt über dieses Symbol, dann sind die Steuerelemente zum Einsatz bereit.
Das wichtige Symbol ist in der Abbildung 8.3 zu sehen. Es befindet sich sowohl auf der
106
8.10 Steuerelemente auf Tabellen
Abbildung 8.3: Tabelle mit Steuerelement
Werkzeugleiste als auch auf der Symbolleiste Visual Basic. Wenn Sie im Entwurfszustand
sind und ein Steuerelement anklicken, erscheint es mit seinen acht Ziehpunkten.
Wenn Sie nur die Beschriftung eines Steuerelements ändern wollen, klicken Sie bitte mit der rechten Maustaste auf das Steuerelement und wählen SteuerelementObjekt| Bearbeiten.
Wenn Sie andere Eigenschaften bearbeiten wollen, klicken Sie zunächst mit der rechten Maustaste auf das Steuerelement, und klicken dann im Kontextmenü auf Eigenschaften. Es öffnet sich das Eigenschaftenfenster mit der Liste der Eigenschaften. Die
Beschriftung ändern Sie über die Eigenschaft Caption, den Namen legen Sie über die
Eigenschaft Name fest. Ich habe auf der Tabelle der Abbildung 8.3 eine Schaltfläche
eingefügt und ihr den Namen CmdBtn gegeben.
Im Codefenster der Tabelle, in der Steuerelemente eingefügt wurden, tauchen diese
in der linken Listbox Objekt auf. Betrachten Sie bitte noch einmal die Abbildung 8.2,
wo das Codefenster der Tabelle Tabelle1 geöffnet ist. Der Code zun Click-Ereignis der
Schaltfläche CmdBtn ist auch zu sehen. Es wird damit eine Form namens Demoform über
deren Show-Methode gezeigt. Um von Visual Basic wieder zu Excel zu wechseln, klicken
Sie im Menü Datei auf Beenden oder geben Alt+F11 ein. In Excel müssen Sie den
Entwurfsmodus beenden, indem Sie auf das mehrfach erwähnte Symbol Entwurfsmodus
beenden drücken.
8.10.1 Listen- und Kombinationsfelder
Das neue Formular besteht aus einer Schaltfläche und einem Kombinationsfeld. Ich habe
ein Kombinationsfeld ausgewählt, weil sich daran schön die Merkmale von Eigenschaften,
Methoden und Ereignisse darstellen lassen.
Kombinationsfelder sind leicht mit Listenfelder zu verwechseln. Die Symbole sind auf
der Werkzeugleiste benachbart. Ein reines Listenfeld zeigt eine Liste von Werten an,
aus der man einen oder mehrere Werte auswählen kann. Die Standardeigenschaft für
das Listenfeld-Steuerelement ist die Value-Eigenschaft. Deren Inhalt ist der ausgewählte
107
8 Formulare
Wert, in Abbildung 8.3 hat Value den Wert Birne. Der Klassenname der Listenfelder ist
ListBox.
Ein Kombinationsfeld kombiniert die Merkmale eines Textfeldes und eines Listenfeldes. Der Benutzer kann entweder einen neuen Wert wie in ein Textfeld eingeben oder
einen vorhandenen Wert wie bei einem Listenfeld auswählen. Der Klassenname der Kombinationsfelder ist ComboBox.
Man kann Kombinationsfelder aber auch als reine Dropdown-Listenfelder verwenden,
wenn man die Werte auf die in der Liste enthaltenen Einträge beschränken möchte. Dazu
muss wie abgebildet die Style-Eigenschaft des Kombinationsfelds auf den Wert fmStyleDropDownList setzen. Dann öffnet sich das Kombinationsfeld über den Pfeil rechts.
In einem Kombinationsfeld, das als Dropdownlistenfeld entworfen wird, kann kein Text
abgelegt werden. Das Standardereignis für Listen- und Kombinationsfelder ist das Click-
Abbildung 8.4: Das Formular DemoForm
Ereignis, genauso wichtig ist das Change-Ereignis, das von einem Wechsel innerhalb der
Liste von Werten ausgelöst wird. Damit wird automatisch auch die Value-Eigenschaft
verändert. Es kommt dabei nicht darauf an, ob die Veränderung durch das Ausführen
von Code oder durch eine Benutzeraktion über die Benutzeroberfläche ausgelöst wurde.
Listen- und Kombinationsfelder haben einige sinnvolle Methoden zum Erstellen und
Bearbeiten der Liste von Werten. Die AddItem-Methode fügt der Liste ein Element
hinzu. Der Prototyp lautet
object_name.AddItem [ item [, varIndex]]
wobei das Argument item der hinzuzufügende Wert ist und varIndex die Position innerhalb der Liste angibt. Fehlt dieses Argument, wird der neue Wert am Ende der Liste
angefügt.
Die Clear-Methode löscht bei einem Listen- oder Kombinationsfeld alle Werte.
Die Eigenschaft ListCount gibt die Anzahl der Listenelemente im angegebenen Listenoder Kombinationsfeld zurück. Es handelt sich dabei um einen schreibgeschützten LongWert. Im Beispiel der Abbildung 8.3 hat ListCount den Wert 3.
Die Eigenschaft ListIndex gibt die Indexnummer des aktuell markierten Elements in
einem Listenfeld oder einem Kombinationsfeld zurück oder legt sie fest. Long-Wert mit
108
8.11 Qualifizierte Bezeichner
Lese-/Schreibzugriff. Dabei wird von 0 angefangen zu zählen, d.h. der oberste Eintrag
hat den Wert 0, der unterste den Wert ListCount-1.
8.11 Qualifizierte Bezeichner
Aus objektorientierter Sicht ist ein Steuerelement, das einer Tabelle oder einem Formular hinzugefügt wurde, eine Eigenschaft dieser Tabelle oder dieses Formulars. Daher
dürfen auch Schaltflächen denselben Bezeichner, etwa CommandButton1, haben, wenn
sie auf unterschiedlichen Objekten platziert sind. In meinem Beispiel haben die Formulare UserForm1 und das in Abbildung 8.3 dargestellte DemoForm je eine Schaltfläche CommandButton1. Innerhalb des Codes von UserForm1 genügt es, den Bezeichner
CommandButton1 zu verwenden, von außen, etwa im Code einer Tabelle, muss dem
Bezeichner der Name des Formulars vorangestellt werden. Dies nennt man qualifizierte
Bezeichner.
Beispiel 8.1
Nach Aktivieren der Tabelle Tabelle2 sollen die Bezeichnungen der beiden Schaltflächen
auf englisch erscheinen, also von »Verändern« auf »Change« und von »Verschieben« auf
»Move« wechseln. Sinnvoller wäre es, eine solche Aktion durch eine Schaltfläche oder
einen Menüpunkt zu veranlassen. Der Code lautet:
Private Sub Worksheet_Activate()
UserForm1.CommandButton1.Caption = "Change"
DemoForm.CommandButton1.Caption = "Move"
End Sub
Dieser Code muss im Codefenster von Tabelle2 eingetragen werden. Wirksam wird der
Code nur, wenn zunächst beide Formulare geschlossen werden und danach zu Tabelle2
gewechselt wird, um deren Ereignis Activate auszulösen.
8.12 Die With-Anweisung
Objektorientierte Programmierung zieht also längliche Anweisungen nach sich, da immer der Namen des Objekts vor der Eigenschaft oder Methode stehen muss. Die WithKontrollstruktur erleichtert dem Programmierer das Leben, und mir erleichtert die OnlineHilfe die nähere Erklärung:
Mit der With-Anweisung können Sie eine Reihe von Anweisungen für ein bestimmtes
Objekt ausführen, ohne den Namen des Objekts mehrmals angeben zu müssen. Wenn Sie
zum Beispiel mehrere Eigenschaften eines bestimmten Objekts verändern möchten, sollten Sie die Zuweisungsanweisungen für die Eigenschaft in einer With-Kontrollstruktur
unterbringen. Sie brauchen dann den Namen des Objekts nicht bei jeder einzelnen Zuweisung, sondern nur einmal zu Beginn der Kontrollstruktur anzugeben.
Als Beispiel verwende ich die With-Anweisung im Initialize-Ereignis des Formulars
DemoForm:
109
8 Formulare
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Birne"
.AddItem "Banane"
.AddItem "Melone"
.ListIndex = 0
End With
End Sub
Das Kombinatiosfeld hat drei Werte. Da die Methode AddItem immer hinten anfügt,
wenn keine Position bestimmt wird, steht »Birne« oben und »Melone« unten.
8.13 Das Formular DemoForm
Das neue Formular wird in folgenden Schritten erstellt:
1. Wechsel zu Visual Basic durch Alt+F11.
2. Befehl Einfügen|UserForm.
3. Umbenennen des Namens von UserForm2 in DemoForm über die Name-Eigenschaft
des Formulars.
4. Ändern des Titels von UserForm2 in Eigenschaften, Methoden und Ereignisse über
die Caption-Eigenschaft des Formulars.
5. Einfügen des Kombinationsfelds. Setzen der Style-Eigenschaft auf den Wert fmStyleDropDownList.
6. Einfügen der Schaltfläche. Änderung der Beschriftung von CommandButton1 in
Verschieben über die Caption-Eigenschaft der Schaltfläche.
Nun benötigen wir noch den Code der Ereignisse.
8.13.1 Der Code des Formulars DemoForm
Wechseln Sie nun über die Taste F7 oder über das Symbol im Projekt-Explorer in das
Codefenster des Formulars. Erstellen Sie zunächst den Code zum Ereignis Initialize wie
beschrieben. Wählen Sie dann im geöffneten Listenfeld Objekt wie in Abbildung 8.5
das Kombinationsfeld und klicken im Listenfeld Prozedur rechts daneben das Ereignis
Change. Jeder Wechsel der Auswahl ändert die Value-Eigenschaft des Kombinationsfelds
und llöst das Change-Ereignis aus. Über ein Meldungsfenster wird die Auswahl angezeigt.
Mehr zu Meldungsfenster im nächsten Abschnitt.
Die Anzahl der Werte im Kombinationsfeld möchte ich ebenfalls über ein Meldungsfenster zeigen, und zwar beim Verlassen des Kombinationsfelds. Das löst das ExitEreignis aus. Sie brauchen dafür nur die Schaltfläche anzuklicken. Die Ereignisbehandlungsprozedur des Exit-Ereignis hat einen Parameter Cancel, dessen voreingestellter
110
8.14 Aufgaben
Abbildung 8.5: Der Code zum Formular DemoForm
Wert False ist. Wenn die Ereignisbehandlungsprozedur diesen Wert auf True verändert,
bleibt der Focus auf dem entsprechenden Steuerelement. Damit sollten Sie vorsichtig
sein, da sonst eventuell das ganze Formular nie zu schließen ist.
Die Schaltfläche verschiebt das Kombinationsfeld um 20 Pixel nach rechts und um 10
Pixel nach unten.
8.14 Aufgaben
1. Excel erlaubt die Kopie eines Bereichs als Werte und als Format aber nicht beides zugleich. Zunächst muss der zu kopierende Bereich markiert und etwa über
Strg + C in die Zwischenablage gebracht werden, dann wird die Anfangszelle
des Bereichs markiert, wo die Kopie eingefügt werden soll. Dann wird der Rest
durch den Dialog erledigt, der sich nach der Befehlsfolge Bearbeiten|Inhalte
einfügen. . . öffnet. Am Beispiel der Tabelle von Aufgabe 1, Seite 9, Abbildung
1.1 erstelle man ein Makro, das den Bereich A1:D13 in eine andere Tabelle mit
Werten und Formaten, aber ohne Formeln kopiert. Auch die Breite der Spalten
soll übertragen werden. Erstellen Sie danach ein Makro, das das Diagramm von
Abbildung 1.1 erzeugt.
Hinweis: Verwenden Sie den Makrorecorder zum Aufzeichnen von Aktionen.
2. Lassen Sie den Makrorecorder bei folgenden Aktionen mitlaufen:
a) Erzeugen einer neuen Arbeitsmappe.
b) Löschen von Tabelle3.
c) Umbenennen der beiden verbliebenen Tabellen in Blatt1 und Blatt2.
111
8 Formulare
d) Speichern der Arbeitsmappe unter dem Namen MeineMappe.xls.
Schließen Sie daraus, welche Methoden jeweils gewirkt haben.
3. Erstellen Sie ein Formular namens BlattForm, das nur eine Listbox hat, deren
Werte die Tabellen der Arbeitsmappe mit Ausnahme der ersten sind. Dieses Formular soll auftauchen, wenn die erste Tabelle erscheint und wieder verschwinden,
wenn der Anwender zu einer anderen Tabelle wechselt. Das Formular soll nicht
modal sein. Ein Doppelklick auf eine der aufgelisteten Tabellen der Listbox soll
die entsprechende Tabelle löschen.
Finden Sie über die Online-Hilfe heraus, was die Methode DisplayAlerts() tut und
verwenden Sie diese, um die lästige Nachfrage, ob wirklich gelöscht werden soll, zu
unterdrücken.
4. Erstellen Sie ein Formular FilmForm, das Ihre Lieblingsfilme in einem Kombinationsfeld zeigt. Ein neuer Filmtitel wird in das Eingabefeld des Kombinationsfelds
eingegeben und soll nach Drücken einer Schaltfläche mit der Beschriftung Hinzufügen zu den Werten des Kombinationsfelds hinzugefügt werden. Über eine weitere
Schaltfläche sollen alle Filmtitel gelöscht werden. Ein einzelner Filmtitel soll durch
Doppelklick auf seinem Eintrag gelöscht werden. Dieses Formular soll modal sein
und nach Drücken einer Schaltfläche erscheinen, die sich auf der ersten Tabelle
befindet.
5. Erstellen Sie ein Formular Meldungen, das aus zwei Kombinationsfeldern ohne
Eingabemöglichkeit besteht. Das erste soll die Einträge Nur Ok, OK und Abbrechen sowie Ja und Nein haben. Das zweite soll die Einträge Ausrufungszeichen,
Fragezeichen und Stopp haben. Dazu soll sich ein Textfeld und eine Schaltfläche
gesellen. Drückt man die Schaltfläche, soll ein Meldungsfenster erscheinen, dessen Nachricht das Textfeld und dessen Schaltflächen und Aussehen die beiden
Kombinationsfelder bestimmen. Diese Formular soll ebenfalls nach Drücken einer
Schaltfläche auftauchen.
112
Index
Überlauf, 33
Datenfeld
mehrdimensionales, 48
Select Case, 69
Anweisung, 34
bedingte, 33
Arbeitsfenster, 12
Auflistungen, 90
Ausdruck, 34
Bezeichner, 25
Bezeichnungsfeld, 102
Block, 65
Boolean, 33
ByRef, 58
ByVal, 55, 58
call by reference, 58
call by value, 58
Case, 69
CDbl(), 44
Cells-Auflistung, 96
Charts, 91
CInt(), 44
Clear, 95
ClearContents, 95
ClearFormats, 95
Code-Fenster, 14
Columns, 95
Count, 95
CurrentRegion, 96
Date, 49
Datenfeld, 46
Dimension, 46
Elemente, 46
Datentyp, 28
Dim, 28, 29
Do, 75
Eigenschaftenfenster, 12, 15
Else, 66
ElseIf, 68
End Function, 55
Ereignis, 102
Ereignisbehandlungsroutine, 102
False, 33
For, 45, 74
For Each, 93
Formulare, 99
Fortsetzungszeichen, 24
Function, 55
Funktion, 55
Funktionen, 19
Funktionsvariable, 56
Funktonsvariable, 56
Gültigkeitsbereich, 28
If, 33
Index, 46
InputBox-Funktion, 22
InStr(pos, s, teilstr), 43
IsNumeric(), 44
Klasse, 89, 105
Kommentare, 26
Konstante, 30
Label, 102
Laufzeitfehler, 17
LBound-Funktion, 47
113
Index
LCase(s), 42
Left(s, k), 42
Len(s), 42
Literale, 31, 40
Loop, 75
Loop While, 75
Makroaufzeichnen, 10
Meldungsfelder, 20
Mid(s, i, k), 43
Modul, 14, 15
Modulebene, 28
Modulooperator, 36
Objekt, 59
Operand, 34
Operator, 34
arithmetisch, 35
binär, 34
für Zeichenfolgen, 35
logisch, 35
unär, 34
Option Base 1, 47
Parameter, 57
Parameter einer Funktion
aktuelle, 57
formale, 57
Preserve, 47
Private, 28, 29
Projekt, 14
Projekt-Explorer, 12, 13
Prozedurebene, 29
Public, 28, 29
Range
Column, 95
Row, 95
Right(s, k), 42
Rows, 95
Schlüsselwort, 25
Schleife, 72
Schleifenkörper, 75
Sheets, 90
114
Sprunganweisung, 85
Steuerelemente, 99
Listenfeld, 99
Optionsfeld, 100
Schaltfläche, 100
Steuerelemente1Textfeld, 100
String, 41
Sub-Prozedur, 14–16
TextBox, 102
Textfeld, 102
True, 33
UBound-Funktion, 47
UCase(s), 42
Until, 75, 76, 78
Variable, 22, 28
Datentyp einer, 28
Gültigkeit einer, 28
Vergleichsoperatoren, 36
Verkettungsoperator, 42
Visual Basic Editor, 12
Vorrangregeln, 38
Werkzeugsammlung, 99
While, 75
Workbooks, 90
Worksheets, 91
Zeichenfolge, 40
feste Länge, 40
variable Länge, 40
Zuweisungsoperator, 35

Grundkurs VBA

Transcrição

Documentos relacionados

Microsoft Visual Basic Express Edition

M3-220 Multimedia Monitor System - CSS

Blutsaugende Insekten usw.

Long John Baldry

Humboldt-Universität zu Berlin Probeklausur Lineare Algebra 1

Import Kalender (ics) aus VISUAL PLANNING

Komplett-Set und Erstausstattung für den Start in die

Vergleichstest Kompakt-PAs von dBTechnologies, FBT, HK

NET - Department of Information Systems

Risikomanagement: Hintergrund und Ziele