Programmierkonzepte inVBA - Technische Universität Kaiserslautern

Programmieren in Anwendungen
Annette Bieniusa
Technische Universität Kaiserslautern
[email protected]
23.05.2013
1 / 41
Überblick
Visual Basic for Applications (VBA)
Ausdrücke
Kontrollstrukturen
Prozeduren
Im Dialog mit dem Benutzer
Objekte in VBA
VBA Objekte in Word und Excel
2 / 41
Themen
Visual Basic for Applications(VBA)
I
Wiederholung grundlegender Konzepte der imperativen
Programmierung anhand der neuen Sprache VB (Visual Basic)
I
Einführung in die ereignisorientierte Programmierung
I
Anwendungsbeispiele mit VBA in Word und Excel
Statistik und Grafiken mit R
I
Einführung in die Statistiksoftware R
I
Wiederholung grundlegender Konzepte der Statistik und
Datenanalyse
I
Datenvisualisierung und Datenanalyse in R an ausgewählten
Fallstudien
3 / 41
Visual Basic for Applications (VBA)
I
Skriptsprache zur Automatisierung und Anpassung von
Microsoft Office Programmen
I
Basiert auf der Syntax von Visual Basic (nicht mehr
kompatibel seit VB.NET)
I
Modul-orientiert und prozedural
Literaturhinweis
VBA-Programmierung - Integrierte Lösungen mit Office 2010, 1.
Auflage, Okt 2010 (erhältlich im Rechenzentrum bzw.
HERDT-Verlag)
4 / 41
Typische Einsatzgebiete
I
Automatisiertes Erzeugen von Dokumenten wie Serienbriefen
I
Benutzerdefinierte Dialogfenster oder Fehlermeldungen
I
Dokumentstatistiken erstellen
I
Daten aus anderen Anwendungen einbinden (insbesondere
Access-Datenbanken)
Einbinden von Funktionalität spezifischer Office-Anwendungen
(integrierte Lösungen)
I
I
I
Umsatz- und Budgetzahlen aus einer Access-Datenbank
werden in Excel ausgewertet und visualisiert.
Umfangreiche Excel-Tabellen können über Word kompakt
gedruckt werden.
5 / 41
Beispiel: Berechnung von Zeiträumen
’ Dauer des Semesters
Sub AnzahlTage ()
Dim Heute As Date , Semesterende As Date , Ausgabe As
String
Heute = Date
Semesterende = DateValue ( " 20.07.2013 " )
Ausgabe = " Bis zum Semesterende sind es noch " &
DateDiff ( " d " , Heute , Semesterende ) & " Tage . "
MsgBox Ausgabe
End Sub
6 / 41
Ausdrücke
7 / 41
Wiederholung: Variablen
I
Mit Hilfe von Variablen kann man (temporär) Werte speichern
und diese in den Prozeduren verwenden.
I
Der Wert einer Variablen kann durch eine Zuweisung
verändert werden.
I
Variablen werden über Bezeichner (Variablennamen)
referenziert.
I
Die Deklaration einer Variablen ist das Vereinbaren einer
Variablen vor ihrem ersten Gebrauch.
Dim Variablenname As Datentype
I
Beispiele:
Dim
Dim
Dim
Dim
Anzahl As Integer
AusgabeText As String
Alter As Integer , Temperatur As Integer
Gestern As Date
8 / 41
Konstanten
I
Konstanten werden ebenfalls über Bezeichner referenziert,
sind aber unveränderlich.
I
Einer Konstanten wird bereits während der Deklaration ein
Wert zugewiesen, der später nicht mehr verändert werden
kann.
I
VBA stellt eine Vielzahl von Konstanten zur Verfügung (siehe
z.B. Abschnitt Meldungsfenster).
Const Kon stantenn ame As Datentype = Ausdruck
I
Beispiele:
Const Pi as Double = 3.14159
Const Programmname as String = " Mein Programm "
9 / 41
Operatoren
I
Ein Ausdruck ist eine Kombination aus Werten, Variablen,
Konstanten und Operatoren.
I
Arithmetische Operatoren: +, −, ∗, /,\, Mod, ˆ
I
Vergleichsoperatoren: <, <=, >, >=, =, <>
I
Logische Operatoren: Not, And, Or
I
Verkettungsoperator: & (Konkatenation von Strings)
10 / 41
Vergleiche mit String-Mustern
I
Der Vergleichsoperator Like wird verwendet, um Strings mit
String-Mustern zu vergleichen.
Zeichen
?
*
[Liste]
[!Liste]
Bedeutung
Ein einzelnes Zeichen
Kein oder mehrere Zeichen
Ein Zeichen der Liste
Ein Zeichen nicht in der Liste
Beispiel
"Hallo" Like "H?lo"-> false
"Haut" Like "H*t"-> true
"X" Like "[A-Z]"-> true
"K" Like "[!a-m]"-> false
11 / 41
Kontrollstrukturen
12 / 41
Wiederholung: Verzweigungen
I
Bei Verzweigungen werden Programmteile abhängig von einer
Bedingung ausgewertet.
If Ausdruck Then
...
Else
...
End If
If Alter >= 18 Then
MsgBox " Normaltarif "
Else
MsgBox " Jugendtarif "
End If
13 / 41
Schleifen
I
Mit Schleifen wird ein Anweisungsblock wiederholt ausgeführt.
I
Zählergesteuerte Wiederholung
For Zaehler = Start To Ende [ Step d ]
A nw ei su n gs bl oc k
Next
I
Beispiel
For X = 1 To 20 Step 5
Debug . Print " X ist " & X
Next
I
Schrittweite wird durch Step angepasst, ohne Angabe wird
Schrittweite 1 verwendet.
14 / 41
Schleifen unter Bedingungen
I
Kopfgesteuerte bedingte Wiederholung
Do While / Until Ausdruck
A nw ei su n gs bl oc k
Loop
X = 1
Do Until X = 20
Debug . Print " X ist " & X
X = X + 5
Loop
I
Fussgesteuerte bedingte Wiederholung
Do
A nw ei su n gs bl oc k
Loop While / Until
Ausdruck
I
Y = 1
Do While Y <> 20
Debug . Print " Y ist " & Y
Y = Y + 5
Loop
X = 1
Do
Debug . Print " X ist " & X
X = X + 5
Loop Until X = 20
Fussgesteuerte Schleifen werden immer mind. einmal
ausgeführt!
15 / 41
Prozeduren
16 / 41
Übersicht: Prozeduren
I
Prozeduren bestehen aus einer Folge von Anweisungen (z.B.
Zuweisungen von Variablen, Prozeduraufrufen,
Verzweigungen, Schleifen, ...).
I
Wichtige Form der Abstraktion beim Programmieren!
17 / 41
Sub-Prozeduren
I
Sub-Prozeduren geben keinen Wert zurück.
I
Syntax von einfachen Sub-Prozeduren
Sub Prozedurname ()
...
End Sub
I
Syntax von Prozeduraufrufen
Prozedurname
Call Prozedurname
18 / 41
Prozeduren mit Parametern
I
Syntax von Sub-Prozeduren mit Parametern
Sub Prozedurname ([ ByVal | ByRef ] Parametername [ As Type
] ,...)
...
End Sub
I
Syntax von Prozeduraufrufen
Prozedurname ( Ausdruck ,...)
Call Prozedurname ( Ausdruck ,...)
I
Der Wert des Ausdrucks wird als Kopie an den die Prozedur
weitergegeben. Die Prozedur kann den übergebenen Wert
verändern, ohne dass sich der ursprüngliche Wert ändert (call
by value).
Sub Increment ( ByVal input as Integer )
input = input + 1
End Sub
...
Dim i as Integer , k as Integer
i = 10
Increment ( i )
k = i ’k hat hier den Wert 10
19 / 41
Prozeduren mit Parametern und Call-by-Reference
I
Alternativ kann ein Parameter eine Referenz auf die Variable
erhalten, die den Wert enthält (call by reference).
I
Die Prozedur arbeitet dann nicht mit einer Kopie, sondern
kann die Variable selbst direkt verändern.
Sub Increment ( ByRef input as Integer )
input = input + 1
End Sub
...
Dim i as Integer , k as Integer
i = 10
Increment ( i )
k = i ’k hat hier den Wert 11
20 / 41
Funktionen
I
I
Funktionen können, wie Prozeduren, mehrere Anweisungen
ausführen und geben immer einen Wert an das aufzurufende
Programm zurück.
Syntax von Funktionen
Function Funktionsname ([ ByVal | ByRef ] Parametername [ As
Type ] ,...) [ As Type ]
...
Funktionsname = Rueckgabewert
...
End Function
I
I
Wenn kein Rückgabewert spezifiziert wird, wird ein
Standardwert entsprechend dem Datentypen der Funktion
zurückgegeben.
Syntax von Funktionsaufrufen
Funktionsname ( Ausdruck ,...)
I
Während Prozeduraufrufe eigenständige Anweisungen sind,
sind Funktionsaufrufe Ausdrücke.
21 / 41
Beispiele: Prozeduren und Funktionen
Sub A b s a t z F o r m a t i e re n ()
Acti veDocume nt . Selection . Range . Bold = True
Acti veDocume nt . Selection . Range . Italic = True
End Sub
Function Max ( X as Integer , Y as Integer ) as Integer
If X < Y Then
Max = Y
Else
Max = X
End Function
22 / 41
Im Dialog mit dem Benutzer
23 / 41
Wiederholung: Meldungsfenster
I
Meldungsfenster können dazu genutzt werden, dem Benutzer
Informationen mitzuteilen und auch abzufragen. Sie bestehen
aus dem Meldungstext und standardmäßig der Schaltfläche
“OK”. Sie kann optional mit einem Titel,
Informationssymbolen und weiteren Schaltflächen ergänzt
werden.
I
Einfaches Meldungsfenster
MsgBox " Meldungstext "
I
Meldungsfenster mit Titel und Informationssymbol
MsgBox " Meldungstext " , vbInformation , " Titel "
24 / 41
Schaltflächen
Mögliche Kombinationen von Schaltflächen
Konstante
Wert Schaltfläche
vbOkOnly
0
OK
vbOkCancel
1
OK und Abbrechen
vbAbortRetryIgnore
2
Abbrechen, Wiederholen und Ignorieren
vbYesNoCancel
3
Ja, Nein und Abbrechen
vbYesNo
4
Ja und Nein
vbRetryCancel
5
Wiederholen und Abbrechen
25 / 41
Rückgabewerte von Schaltflächen
Konstante
vbOk
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo
Wert
1
2
3
4
5
6
7
gewählte Schaltfläche
OK
Abbrechen
Abbrechen
Wiederholen
Ignorieren
Ja
Nein
26 / 41
Eingabedialoge
I
Die Funktion InputBox erzeugt Eingabedialog mit Text und
einer Eingabezeile.
I
Der vom Anwender eingegebene Wert wird als String
zurückgeliefert. Bei Betätigen der Schaltfläche Abbrechen ist es
der leere String "".
I
Der optionale Parameter Default legt den Wert fest, der
standardmässig im Eingabefeld angezeigt wird.
InputBox ( Text , [ Title ] , [ Default ])
27 / 41
Objekte in VBA
28 / 41
Objekte in VBA
I
Alle Elemente in MS Office, wie Dokumente, Tabellen,
Graphiken, etc., sind Objekte.
I
Typische Objekte in Excel sind Arbeitsmappen (Workbook),
Tabellenblätter (Worksheet), Diagramme (Charts) und Zellen
(Range,Cell).
I
Das gerade aktive Objekt wird mittels
(z.B. ActiveWorkbook oder ActiveCell).
I
Eine vollständige Übersicht listen die Developer Referenzen
(z.B. http://msdn.microsoft.com/en-us/library/
office/ff846392(v=office.14).aspx).
ActiveXXX
referenziert
29 / 41
Objekte
I
I
Objekte sind Programmeinheiten, die Daten sowie die
Prozeduren zum Verarbeiten dieser Daten enthalten.
Objekte haben
I
I
I
einen Zustand (definiert durch Eigenschaften/Properties),
ein Verhalten (definiert durch objektspezifische
Prozeduren/Methoden) und
eine Identität (wodurch es sich von Objekten des gleichen Typs
unterscheidet).
30 / 41
Klassen
I
Eine Klasse ist eine Art Modell für Objekte des gleichen Typs.
I
Sie dient als Bauplan für die einzelnen Objekte und definiert
deren Eigenschaften und Methoden.
I
Beispiel:
bezeichnet die Klasse für Word-Dokumente,
eine Objekt-Instanz der Klasse Document.
Document
ActiveDocument
31 / 41
Zugriff auf Methoden und Eigenschaften
I
I
Objektverweise sind Variablen, die eine Referenz auf ein
Objekt enthalten.
Deklaration von Objektvariablen
Dim Objektv ariable as Obj ektdaten typ :
I
Zuweisung auf Objektvariable:
Set Objektv ariable = Objekt
I
Zugriff auf Eigenschaften:
Obje ktvariab le . E i g e n s c h a f t s n a m e
I
Aufruf von Methoden:
Obje ktvariab le . Methodenname
Obje ktvariab le . Methodenname ( Parameter )
I
Löschen von Objektvariablen
Set Objektv ariable = Nothing
32 / 41
Auflistungen (Collections)
I
Auflistungen sind spezielle Objekte, die aus einer Menge von
Objekten des gleichen Typs bestehen.
I
Beispiel: Die Auflistung Worksheets in Excel enthält alle aktuell
geöffneten Tabellenblätter.
I
Auf einzelne Objekte in einer Auflistung wird über einen
Indexwert zugegriffen (beginnend mit 1).
Sub Blaetter ()
Dim Anzahl As Integer , Index as Integer
Anzahl = Worksheets . Count
For Index = 1 To Anzahl
Debug . Print Worksheets ( Index ) . Name
End Sub
I
Häufig kann auch der Name des Objekts zum Zugriff
verwendet werden.
Workbooks ( " Mappe1 . xlsm " ) . Worksheets ( " Tabelle2 " ) . Range (
" A1 : B5 " )
33 / 41
Zusammenfassung
I
Ausdrücke: Variablen, Konstanten, zusammengesetzte
Ausdrücke mit Operatoren, Funktionsaufrufe
I
Anweisungen (Statements): Prozeduraufrufe,
Kontrollstrukturen (Verzweigungen, Schleifen)
I
Meldungs- und Eingabefenster
I
Objekte mit Eigenschaften und Methoden
34 / 41
VBA Objekte in Word und Excel
35 / 41
Beispiel: Dokumente in Word
Sub D o k u m e n t O e f f n e n S c h l i e s s e n E r s t e l l e n ()
Dim Brief As Document
Set Brief = Documents . Open ( Act iveDocum ent . Path & " \
Testbrief . docx " )
Brief . Close
Set Brief = Nothing ’ gibt den Speicherplatz wieder
frei
End Sub
Activate
Close
Path
Printout
Save
SaveAs2
Aktiviert das Dokument
Schliesst und speichert das Dokument
Pfad zum Speicherort des Dokuments
Druckt das Dokument
Speichert das Dokument
Speichert das Dokument unter einem neuen Namen
Weitere Methoden/Eigenschaften: Verwendung von Templates,
Schreib-/Passwortschutz
36 / 41
Textbereiche
I
Ein Range Objekt referenziert einen zusammenhängenden
Bereich eines Dokuments.
I
Es wird über einen Start- und einen Endcharacter definiert.
I
Ein
Range
Objekt kann auch nur die Eingabemarke definieren.
Sub T e s t R a n g e O b j e c t s ()
Dim rngIns As Range , rngPar1 As Range , rngPar2 as
Range
Dim doc As Document
Set rngPar1 = doc . Paragraphs (1) . Range
MsgBox " Der 1. Absatz beginnt mit dem Wort " &
rngPar1 . Words . First & " . "
Set rngPar2 = doc . Range ( Start := doc . Paragraphs (2) .
Range . Start , End := doc . Paragraphs (3) . Range . End )
Set rngIns = doc . Range ( Start :=0 , End :=0)
rngIns . InsertBefore " Hello "
End Sub
37 / 41
Markierte Textbereiche
I
Das Selection Objekt referenziert den markierten Bereich des
aktuellen Dokuments.
Sub T e s t S e l e c t i o n O b j e c t ()
Dim rngParagraph As Range
Selection . Font . Bold = True
Select Case Selection . Type
Case w d S e l e c t i o n N o r m a l
MsgBox " Sie haben folgenden Text markiert : " &
Selection . Text
Case wdSelectionIP
MsgBox " Sie haben nichts markiert "
Set rngParagraph = A ctiveDo cument . Paragraphs (2) . Range
rngParagraph . Select
Selection . Font . Italic = True
End Sub
38 / 41
Zugriff auf Dokumenteninhalte
Characters
Sentences
Paragraphs
Words
Start/End
Text
Auflistung einzelner Zeichen
Auflistung von Sätzen
Auflistung von Absätzen
Auflistung von Worten
Start- bzw. Endposition
Textinhalt
39 / 41
Beispiel: Arbeitsmappen und Tabellen in Excel
I
I
I
I
Arbeitsmappen (Workbooks) beinhalten Tabellenblätter
(Worksheets) und Diagramme (Charts).
UsedRange referenziert den verwendeten Bereich eines
Tabellenblatts.
CurrentRegion bezeichnet einen Bereich gefüellter Zellen, die
von leeren Zellen umgeben sind.
Mittels Cells lassen siche einzelne Zellen referenzieren.
Sub ArtikelSuchen ()
Dim ArtNr As String , Zaehler As Integer
ArtNr = InputBox ( " Geben Sie eine Artikelnummer ein : " ,
" Artikel suchen " )
ThisWorkbook . Sheets ( " Artikel " ) . Activate
For Zaehler = 1 To Range ( " A1 " ) . CurrentRegion . Rows . Count
If Cells ( Zaehler ,1) . Value = ArtNr Then
MsgBox " Artikel wurde gefunden "
Exit Sub
End If
Next
MsgBox " Artikel nicht vorhanden "
End Sub
40 / 41
Zellbereiche
I
Ein Range Objekt bezeichnet in Excel einen
zusammenhängenden Zellbereich.
Worksheets (1) . Range ( " A4 " ) . Value
Worksheets ( " Messreihe " ) . Range ( " B2 : B8 " ) . Count
Range ( " A1 : A5 " ," B1 : B5 " ) . Count
’ verwendet implizit das akutelle Tabellenblatt
Range ( Cells (3 ,3) , Cells (4 ,6) ) . Select
’ entspricht Range (" C3 : F3 ") . Select
41 / 41