VBA

___________________________________________________________________
VBA
Objektorientiertes Programmieren
in MS-Office-Anwendungen
___________________________________________________________________
Hinweis: Diese Unterlagen basieren auf diversen Veröffentlichungen und Beispielen im
Internet, die VBA zum Thema haben. Dem Leser wird angeraten - neben geeigneten
Fachbüchern – sich dieses Medium zu Nutze zu machen und weitere Übungsbeispiele sich dort
zu beschaffen. Ich bedanke mich herzlich bei den – mir zum Teil unbekannten – Autoren.
Roland.Kraus
-1Prof. Dr. R. Kraus
0. Einführung
Um in einer MS-Office-Anwendung programmieren zu können, ergänzt man zuerst die
Symbolleiste durch die Visual-Basic Icons und die Steuerelement Toolbox, eine Werkzeugleiste
mit den Objekt-Steuerelementen (im Menü: Ansicht, Symbolleisten).
Steuerelemente-Toolbox
Im Entwurfsmodus
werden die einzelnen Objekte aus der Steuerelemente-Toolbox auf die
Arbeitsfläche gezogen. Durch Rechtsklick auf ein abgelegtes Objekt kommt man zum
Eigenschaften-Fenster, oder zum VBA-Editor mit dem zum Objekt zugehörigen Quelltext.
Zu jedem Objekt gehören Eigenschaften, Ereignisse und Methoden.
Die Eigenschaften des markierten Objektes werden zunächst im Objektinspektor bearbeitet, die
Methoden werden durch den entsprechenden Quelltext aufgerufen. Methoden sind Prozeduren
oder Funktionen, die dem Objekt zugeordnet sind und mit ihm zusammenarbeiten.
1. Objekte, Eigenschaften, Ereignisse
Im Eigenschaften-Fenster sind die für die Objekte spezifischen Eigenschaften in einer Auswahlliste angeordnet. Jedes Objekt besitzt Eigenschaften, die im Eigenschaften-Fenster
voreingestellt sind. Eigenschaften bestimmen die Charakteristika von Objekten, wie etwa
Größe, Farbe und Bildschirmposition, aber auch den Zustand eines Objekts, wie z.B. aktiviert
oder deaktiviert.
Sie können im Eigenschaften-Fenster bzw. im Programm geändert werden.
Eigenschaften-Fenster
(Eigenschaften-Editor)
-2Prof. Dr. R. Kraus
Ebenso kann jedem Objekt ein Ereignis zugewiesen werden, z.B. einem Button das Ereignis
"Click".
Durch Doppelklick auf das Objekt wird automatisch vom Office Programm der VBA-Editor
geöffnet und ein Ereignisprozedur-Rumpf erzeugt.
Beispiel:
Private Sub CommandButton1_Click()
Befehle ...
End Sub
Nichts
Jedes Objekt ist ein Datentyp, der Daten, Prozeduren und Funktionen zu einer Einheit
verbindet. Die Prozeduren und Funktionen innerhalb eines Objekts nennt man Methoden.
Die wichtigsten Standardobjekte
Button
CheckBox
ComboBox
Image
Label
ListBox
Option
TextBox
Befehlsschaltfläche (Befehls-Knopf) zum Ausführen von Operationen
Kontrollfeld, um dem Benutzer die Wahl zwischen zwei Werten wie True/False
zu geben
Kombinations-Listenfeld, kombiniert die Merkmale einer TextBox und einer
ListBox
Bild, Möglichkeit um ein Bild darzustellen.
Bezeichnungsfeld, zeigt einen beschreibenden Text an.
Listenfeld, aus dem ein oder mehrere Listenelemente ausgewählt werden können
Optionsfeld, zeigt an, ob ein Element in einer Gruppe von Wahlmöglichkeiten
ausgewählt wurde oder nicht.
Textfeld, Eingabezeile für Text und Daten
2. Die wichtigsten Eigenschaften der Standardobjekte
Jedes Objekt erhält während des Entwurfs automatisch einen Namen z.B. Button1, der vom
Programmierer jederzeit in einen sinnvollen Namen geändert werden kann. Gleichzeitig wird
der Name der zugehörigen Ereignisprozedur automatisch geändert.
BackColor
BackStile
Caption
Enabled
Font
Height
Name
Value
Width
Text
Farbe des Objekts
Stil des Objekts
Aufschrift z.B. bei Label oder Button als Vorbelegung oder Anweisungstext
Objekt aktiv oder nicht, je nachdem ob TRUE oder FALSE eingestellt ist
Schriftart und Schriftgröße des Textes im Objekt
Höhe des Objektes (in Pixel)
Name, Bezeichnung des Objekts
Wert
BreiteHöhe des Objektes (in Pixel)
Textinhalt (Edit)
-3Prof. Dr. R. Kraus
Die Werte der Objekte können im Entwurfsmodus im Eigenschaften-Fenster zur Laufzeit im
Quelltext geändert werden. Z.B. Änderung zur Laufzeit:
Aufruf einer Eigenschaft:
<Objektbezeichner> .<Eigenschaft>
Textbox1.Font = Courier
Textbox1.Text = "Huhu"
Button1.Enabled = True
Kraus.Enabled = False
3. Die wichtigsten Ereignisse für die Standardobjekte
Beim Erzeugen eines Objekts wird diesem automatisch ein Standard-Ereignis, z.B. für Button
Click, zugewiesen.
Durch Doppelklick auf das Objekt wird zur Ereignisbehandlung ein zugehöriger
Ereignisprozedurrumpf im Quelltext erzeugt.
Beispiel:
Private Sub CommandButton1_Click()
//Ihr Programm steht hier
End Sub
Weitere Ereignisprozeduren können dem Objekt durch den Programmierer zugewiesen werden.
Ereignisse:
Click
DblClick
Enter
Exit
KeyPress
Change
Focus
Auf Mausklick
Auf Doppelklick
Das Enter-Ereignis tritt ein, bevor ein Steuerelement den Fokus von
einem anderen Steuerelement im gleichen Formular erhält.
Das Exit-Ereignis tritt ein, unmittelbar bevor der Fokus von einem
Steuerelement auf ein anderes Steuerelement im gleichen Formular
wechselt.
Auf Tastendruck, tritt ein, wenn der Benutzer eine ANSI-Taste drückt
Tritt ein, wenn sich die Value-Eigenschaftdes Objekts ändert
Die Fähigkeit, zu einem Zeitpunkt Mausklicks oder Tastatureingaben zu
empfangen. In der Microsoft Windows-Umgebung kann nur ein einziges
Fenster, ein einziges Formular oder ein einziges Steuerelement diese
Fähigkeit zu einem gegebenen Zeitpunkt besitzen. Das Objekt, das "den
Fokus besitzt", wird normalerweise durch eine hervorgehobene
Überschrift oder Titelleiste angezeigt. Der Fokus kann vom Benutzer oder
von der Anwendung gesetzt werden.
-4Prof. Dr. R. Kraus
4. Methoden
Jedes Objekt ist ein Datentyp, der Daten und Code zu einer Einheit verbindet. Methoden sind
Prozeduren und Funktionen, die auf die Daten der Objekte wirken und damit diesem Objekt
zugeordnet sind. Jedes Objekt besitzt eine Auswahl bestimmter Methoden. Weitere
Informationen über die Methoden eines Objektes sind in der Hilfedatei zu finden.
Beispiele:
Aufruf einer Methode:
<Objektbezeichner> .[<Eigenschaft>]. <Methodenbezeichner>
TextBox1.Cut (Copy)
TextBox2.Paste
TextBox1.SetFocus
ListBox1.AddItems('Neues Element')
ListBox1.AddItem (TextBox1.Text)
ListBox1.Clear
ListBox1.RemoveItem (i)
Hilfe: Objekt markieren und F1 drücken,
dann Methoden auswählen!
5. VBA-Code
Kommentare
Kommentare werden mit dem Schlüsselwort rem oder mit dem Hochkomma eingeleitet:
rem
Kommentarzeile 1
‘
Kommentarzeile 2
Fortsetzungszeile
Fortsetzungszeilen werden mit dem Unterstrich eingeleitet:
MsgBox
“Es wurde ein unzulässiger Name eingegeben“,_
vbOkOnly,_
“Eingabefehler“
Anweisungen
Eine Programmzeile kann mehrere Anweisungen enthalten.
Anweisungen in einer Zeile werden durch den Doppelpunkt separiert.
x1 = 0. : x2 = 1. : x3 = 3.
-5Prof. Dr. R. Kraus
Datentypen von Variablen:
Typ
Byte
Boolean
Integer
Long
Single (Gleitkommazahl
mit einfacher
Genauigkeit)
Double
(Gleitkommazahl mit
doppelter Genauigkeit)
String
Byte
1
2
2
4
4
Kenner Wertebereich
0 bis 255
True oder False
%
-32.768 bis 32.767
&
-2.147.483.648 bis 2.147.483.647
!
-3,402823E38 bis -1,401298E-45
8
#
-1,79769313486232E308 bis -4,94065645841247E324.
$
String (feste Länge)
Date
Object
Variant(mit Zahlen)
1/char
+10
1/char
8
4
16
Variant (mit Zeichen)
22
plus Zeichenfolgenlänge
0 bis ca. 2 Milliarden
1 bis ca. 65.400
1. Januar 100 bis 31. Dezember 9999.
Beliebiger Verweis auf ein Objekt vom Typ Object.
Numerische Werte im Bereich des Datentyps
Double.Variant
plus Zeichenfolgenlänge(wie bei String mit
variabler Länge). Benutzerdefiniert (mit Type)
Wichtiger Hinweis:
Werden Variablen nicht deklariert, dann sind sie vom Datentyp „Variant“.
Bei Datentyp „Variant“ wird der Typ durch die erste Verwendung festgelegt.
Mit der (ersten) Befehlszeile Option explizit wird der Programmierer „gezwungen“ jede
Variable zu deklarieren.
Variablendeklaration
Konstanten-Deklarationen
Dim
Dim
Dim
Dim
Dim
Const Pi = 3.14159
Const Antwort = "ja!"
i, j, n As Integer
x, y, z As Double
Zahlen(10) As Double
Done, Error As Boolean
Namen As String
Static wert as integer
´Staticvariablen behalten ihren Wert!
Die Zuweisung
Name = TextBox1.Text
TextBox2.Text = "Herr "+ NName
TextBox3.Value = Summe
TextBox1.BackColor = &H00C0FFFF&
TextBox1.Font = Courier
Button1.enabled = TRUE
x = 17.45
Cells(2,4) = "huhu"
I = 2
J = 4
Cells(2,3) = 6
// es wird in die Zelle 2,3 (2.te Reihe, 3.te Spalte) der Wert 6 eingetragen)
Cells(i,j) = 16
-6Prof. Dr. R. Kraus
Operatoren:
Operator
^
+
*
/
\
Mod
Erklärung
Potenzierung ( x = y ^2 )
Addition
(x=y+2)
Subtraktion ( x = y - 2 )
Multiplikation ( x = 2*y )
Gleitkommadivision ( x = y /2 )
Integerdivision
( x = y\2 )
Rest aus einer Integerdivision
Not
And
Or
Xor
Negation
( x = Not y )
Logisches Und
( x = a And b )
Logisches Oder
( x = a Or b )
Exclusives Oder
( x = a Xor b )
Dient zum Durchführen einer logischen Exklusion zwischen zwei
Ausdrücken. Ergibt True, wenn genau ein Operand True ist. Sind
beide Operanden True oder beide False, dann ergibt sich False.
Eqv
Imp
Äquivalenz
Implikation
<
>
=
<=
>=
<>
kleiner als
(a<b)
größer als
(a>b)
gleich ( a = b )
kleiner gleich ( a <= b )
größer gleich ( a >= b )
ungleich
( a <>b)
( x = a Mod b )
( x = a Eqv b … entpricht a = b auf „Bitebene“)
( x = a Imp b … same as … (Not a) Or b
Die einseitige Auswahl/Bedingung
Die zweiseitige Auswahl:
if (a > b) then
cells(1,1) = “a ist größer als b”
If Edit1.Text = "Ferien" Then
Label1.Caption = "Juhu!!"
Else
Label1.Caption = "pfui!"
End If
End If
If a > b Then
if ((a > b) and (c > b)) then
cells(1,1) = “b ist am kleinsten”
cells(1,1) = “a ist größer als b”
Else
End If
cells(1,1) = “b ist größer gleich a”
End If
-7Prof. Dr. R. Kraus
Die mehrseitige Auswahl:
Select Case a
Case 1
b = 20 : c = 30
Case 2
b = 21 : c = 31
Case Else
b = 0 : c = 0
End Select
Select Case Zahl
Case 1,3,5,7,9
Case 0,2,4,6,8
End Select
Label1.Caption ="ungerade Zahl"
Label1.Caption = "gerade Zahl"
Wiederholungsanweisungen:
For..To
For i=5 To 100
Cells(i,1) = i
Next i
For..To
For i=10 1 Step -1
Cells(i,1) = i
Next i
Do Loop Until
While
t=0
While (t < tend)
t=t+0.001
cells(i,1) = i*i*i
Wend
i=0
do
i=i+1
cells(i,1) = i
Loop until ((i=10)or (cells(i,1)=8))
Exit Do / Exit For: Abbruch von Schleifen
Mit Exit Do bzw. Exit For wird eine Do bzw. For-Schleife frühzeitig
abgebrochen. Die erste Anweisung nach der Schleife wird ausgeführt
'Beispiel:
Dim i, j As Integer
j = cells(1,1)
For i = to 10 do
if j > j Then
exit for
end if
Next i
Array-Typen eindimensional:
zweidimensional:
Dim quadrate(10) As Double
For i=1 To 10
For j=1 To 10
produkt(i,j) = i*j
Next j
Next i
For i =1 To 10
quadrate(i) = i * i
Next i
String:
Dim Namen(10) as String
For i=1 to 10
Namen (i) = "Huber"+ Str$(i)
Next i
-8Prof. Dr. R. Kraus
Prozeduren und Funktionen:
Prozeduren
In VBA können eigene Prozeduren auf Modulebene geschrieben werden.
Vordefinierte Prozeduren werden aus Bibliotheken eingebunden.
Eine Prozedur wird in folgender Form in einem Modul definiert:
Sub Name (Parameter)
...
End Sub
Eine Prozedur liefert keinen Rückgabewert und hat keinen Typ.
Variablen können wie bei Prozeduren per Wert ByVal oder per Referenz ByRef
übergeben werden.
ByVal
: Der Wert einer Variablen wird übergeben.
ByRef
: Die Adresse einer Variablen ist zu übergeben, wenn ein
Wert zurückgegeben werden soll.
In einer Prozedur können lokale Variablen definiert werden, deren
Gültigkeitsbereich durch die Prozedur beschränkt wird.
Funktionen
Funktionen unterscheiden sich von Prozeduren durch die Rückgabe
einesFunktionswerts (z.B. Funktion der Quadratwurzel:c = Sqr(2#) ).
Eine Funktion wird in folgender Form in einem Modul definiert:
Function Name (Parameter) As Typ
...
End Function
Variablen können wie bei Prozeduren per Wert ByVal oder per Referenz ByRef
übergeben werden.
In einer Funktion können lokale Variablen definiert werden, deren
Gültigkeitsbereich durch die Funktion beschränkt wird.
Der Rückgabewert einer Funktionen kann in Ausdrücken direkt verwendet werden.
Beispiel:
Function fsin2(x) As Double
Dim y as double
Y = 2*sin(x)
sin2 = y
End Function
Sub psin2(x, ByRef y As Double)
Y = 2*sin(x)
End Sub
'
' Hauptprogramm
'
Private Sub CommandButton1_Click()
Dim x, y As Double
' Berechnung von 2*sin(x) durch Aufruf einer Prozedur
Call psin2(x, y)
' Berechnung von 2*sin(x) durch Aufruf einer Funktion
y = fsin2(y)
End Sub
-9Prof. Dr. R. Kraus
Public
Durch das Schlüsselwort Public wird der Geltungsbereich von Variablen, Funktionen und
Prozeduren auf alle Module ausgeweitet.
Public Function .....
Privat
Durch das Schlüsselwort Privat wird der Geltungsbereich von Variablen, Funktionen und
Prozeduren auf einen Module beschränkt.
Privat Sub ....
MsgBox: Ausgabe einer Meldung
MsgBox (<Meldung> [,<Buttons>] [,<Titel>] [,<Datei>,<Kontext>])
<Meldung>
<Bottons>
<Titel>
<Datei>
<Kontext>
<Return>
:
:
:
:
:
:
Meldungstext.
Auszugebende Schaltflächen.
Text in der Titelzeile des Fenster.
Name der Hilfedatei.
Kontextbezeichnung für Einsprung.
Als Funktionswert wird der Tasten-Code
zurückgegeben.
Beispiel:
wert = MsgBox ("ja oder nein – das ist hier die Frage" , VbYesNo )
if wert = vbYes then cells(1,1) = “ja geklickt”
if wert = vbNo then cells(1,1) = “ja geklickt”
InputBox: Eingabeaufforderung in Dialogbox
InputBox (<Meldung> [,<Titel>] [,<Default>],...)
<Meldung>
<Titel>
<Default>
<Return>
:
:
:
:
Meldungstext.
Text in der Titelzeile des Fenster.
Text wird in Eingabefeld ausgegeben.
Als Funktionswert wird der eingegebene Text
zurückgegeben.
Beispiel:
wert = InputBox ("Is was?")"
cells(1,1) = wert”
- 10 Prof. Dr. R. Kraus
6. VBA – EXCEL
Auswahl einer Zelle (aktivieren/markieren/selectieren)
Range("A1").Select
´ Zelle A1 wird markiert
[A1].Select
'Kurzform der Auswahl einer Zelle
Cells(2, 1).Select
Adresse einer Zelle
Cells(i, j).Select
'Besonders für Schleifen geeignet
'(Zeile;Spalte)
Range("A1:C2").Select.
'Dieser Bereich wird markiert
[A1:C2].Select
'Dieser Bereich wird markiert
ActiveCell.Offset(0, 1).Select
'Verschiebt die Markierung um 1 Spalte
'Funktioniert auch entsprechend mit negativen Werten
Einer Zelle einen Wert zuweisen
ActiveCell.Value = 10
ActiveCell.Formula = "=3+4"
Cells(4, 3).Value = 10
Cells(4, 3) = 10
Cells(5, 4).Formula = 4 / 3
[C4].Value = 34
'Die Zelle, auf die der Zellzeiger zeigt, erhält den Wert 10
'In die aktive Zelle wird diese Formel eingesetzt
'Wertzuweisung
'Festlegung einer Formel
Optische Gestaltung
Range("A1:C1").Font.FontStyle = "fett" 'Formatiert die Schrift im angegebenen Bereich Fett
Selection..Font.FontStyle = "fett" ''Formatiert die Schrift der selektierte Zellen Fett
Selection.Interior.ColorIndex = 3 'rot 'Formatiert die selektierte Zelle Rot
Selection.Interior.Pattern = xlSolid
´Formatiert Muster in selektierter Zelle SOLID
ActiveCell.Font.Bold = True
'Formatiert die Schrift in aktiver Zelle fett
Kopieren von Zellen
[A1].Select
Selection.Copy
ActiveCell.Offset(4, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Zelle aktivieren; alternativ mit anderen Methoden
'Zelle wird mit dem Laufrahmen versehen
'Zeiger um vier Zeilen versetzen
'Fügt in die aktive Zelle den Wert ein
'Beendet den Kopiermodus
- 11 Prof. Dr. R. Kraus
Beispielprogramme
1. Ändern von Eigenschaften
Das Programm grüßt den Anwender, wenn er auf die Bilder klickt. Im Layout werden zwei
Bildobjekte plaziert. Die Ereignisprozeduren Click werden durch Doppelklick auf das Bild
erzeugt.
Private Sub CommandButton3_Click()
Label5.Caption = ""
End Sub
Treffer!
0
Private Sub Image3_Click()
Label5.Caption = "Treffer!"
End Sub
I shoot the Sheriff ...
2. Zins
Das Programm berechnet den Zins eines Kapitals. Die Vorbelegung wird im Objektinspektor
bei der Eigenschaft Text vorgenommen.
Private Sub CommandButton2_Click()
Dim k, p, z As Double
k = TextBox2.Value
p = TextBox3.Value
z = k * p / 100
TextBox4.Value = z
End Sub
Kapital:
5000
Zinssatz in %:
88
Zins:
4400
Rechnen
- 12 Prof. Dr. R. Kraus
3. Zinseszinsberechnung
In den Textboxen können Einträge gemacht werden. Durch Klicken auf den Rechnen-Button
werden die Zinsen in einer Listbox aufgelistet.
Vorteil: Auch längere Ergebnisreihen können durch den seitlichen Scrollbalken betrachtet
werden.
Kapital:
100000
Zinssatz in %:
5,3
Anzahl Jahre:
5
Rechnen
Private Sub CommandButton11_Click()
Dim k, p, z As Double
Dim n, i As Integer
k = TextBox11.Value
p = TextBox21.Value
n = TextBox31.Value
ListBox1.Clear
For i = 1 To n
k = k + k * p / 100
ListBox1.AddItem (Format(i, "00") + ". Jahr: " + Format(k, "#,##0.00"))
´Umwandlung von Zahlen in Zeichenketten
Next i
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ListBox1.Clear
End Sub
- 13 Prof. Dr. R. Kraus
4. Volumen einer Kugel
Das Volumen einer Kugel wird bei jeder Eingabe durch das Change-Ereignis neu berechnet.
(Eingabe z.B. 1.5)
Radius:
Private Sub TextBox_Radius_Change()
Dim V As Double
Dim r As String
r = TextBox_Radius.Text
V = 4 / 3 * Val(r) * 3.141
Label_V.Caption = V
End Sub
cm
Volumen der Kugel:
0
7. 1 mal 1
Schreiben Sie ein VBA-Programm, dass das kleine 1x1 berechnet und in einer Excel-Tabelle
ausgibt!
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
2
4
6
8
10
12
14
16
18
20
3
6
9
12
15
18
21
24
27
30
4
8
12
16
20
24
28
32
36
40
5
10
15
20
25
30
35
40
45
50
6
12
18
24
30
36
42
48
54
60
7
14
21
28
35
42
49
56
63
70
8
16
24
32
40
48
56
64
72
80
9
18
27
36
45
54
63
72
81
90
10
20
30
40
50
60
70
80
90
100
- 14 Prof. Dr. R. Kraus
8. Waagrechter Wurf
Schreiben Sie ein VBA-Programm, dass nach Eingabe von der Geschwindigkeit v und der Zeit t
den Verlauf eines waagrechten Wurfes berechnet und diesen graphisch darstellt.
v
10
tend
5
x
51
y
-127,449
Waagrechter Wurf
0
-20
0
10
20
30
40
-40
H
[m
]
-60
y
-80
-100
-120
-140
Weite [m]
- 15 Prof. Dr. R. Kraus
11. Nullstellenbestimmung
Schreiben Sie ein VBA-Programm, dass von einer Funktion mit Hilfe der Bisektionierung die
Nullstellen bestimmt.
Nullstellensuche
Nullstellensuc
he
x
1,5625
f(x)=cos(x)
0,008296232
1,5625
y0
n
0,008296232
x0
Schritt
x
1
2
3
4
5
6
7
a
b
0
8
nmax max. Residuum
100
1,00E-02
7
y
4
2
1
1,5
1,75
1,625
1,5625
-0,6536436
-0,4161468
0,54030231
0,0707372
-0,1782461
-0,0541771
0,00829623
- 16 Prof. Dr. R. Kraus