Programmieren in Anwendungen
Annette Bieniusa
Technische Universit¨at Kaiserslautern bieniusa@cs.uni-kl.de
08.05.2014
Uberblick ¨
Wiederholung: Visual Basic for Applications (VBA) Datentypen
Ausdr¨ucke
Kontrollstrukturen
Prozeduren und Funktionen Im Dialog mit dem Benutzer Objekte in VBA
VBA Objekte in Word und Excel
Themen
Visual Basic for Applications(VBA)
I Wiederholung grundlegender Konzepte der imperativen Programmierung anhand der neuen Sprache VB (Visual Basic)
I Einf¨uhrung in die ereignisorientierte Programmierung
I Anwendungsbeispiele mit VBA in Word und Excel
Statistik und Grafiken mit R
I Einf¨uhrung in die Statistiksoftware R
I Wiederholung grundlegender Konzepte der Statistik und Datenanalyse
I Datenvisualisierung und Datenanalyse in R an ausgew¨ahlten Fallstudien
Visual Basic for Applications (VBA)
I Skriptsprache zur Automatisierung und Anpassung von Microsoft Office Programmen
I Modul-orientiert und prozedural
I Einsatzfelder
I Automatisiertes Erzeugen von Dokumenten wie Serienbriefen
I Benutzerdefinierte Dialogfenster oder Fehlermeldungen
Literaturhinweis
VBA-Programmierung - Integrierte L¨osungen mit Office 2010, 1.
Auflage, Okt 2010 (erh¨altlich im Rechenzentrum bzw.
HERDT-Verlag)
Beispiel: Berechnung von Zeitr¨ aumen
’ D a u e r des S e m e s t e r s Sub A n z a h l T a g e ()
Dim H e u t e As Date, S e m e s t e r e n d e As Date, A u s g a b e As S t r i n g
H e u t e = D a t e
S e m e s t e r e n d e = D a t e V a l u e(" 2 5 . 0 7 . 2 0 1 4 ")
A u s g a b e = " Bis zum S e m e s t e r e n d e s i n d es n o c h " &
D a t e D i f f(" d ", Heute , S e m e s t e r e n d e ) & " T a g e . "
M s g B o x A u s g a b e End Sub
Datentypen
I Der Datentyp definiert die Art sowie den Wertebereich einer Variablen oder Konstanten.
I Abh¨angig vom Datentyp wird Speicherplatz f¨ur Variablen bzw.
Konstanten reserviert.
I Bei impliziten Deklarationen wird in VBA der DatentypVariant
verwendet.
Ubersicht: Datentypen ¨
Integer % Ganze Zahlen [-32.768,32.767]
Long & Ganze Zahlen [-2.147.483.648, 2.147.483.647]
Single ! Fliesskommazahlen 7 Ziffern Genauigkeit
Double # Fliesskommazahlen 15 Ziffern Genauigkeit
Currency @ Fliesskommazahlen 15 Vor- und 4 Nachkommastellen
String $ Zeichenketten bis zu 65.535 Zeichen
Date Datum und Uhrzeit
Boolean Logische Werte True undFalse
Variant Belieber Wert / Objekt
Datentypen in Variablendeklarationen
Dim V a r N a m e 1 As D a t e n t y p 1 , V a r N a m e 2 As D a t e n t y p 2 , ...
Dim V a r N a m e 1 T y p k e n n z e i c h e n , V a r N a m e 2 T y p k e n n z e i c h e n , ...
Beispiele:
Dim A l t e r As Integer, N a m e as S t r i n g Dim K u n d e n n u m m e r & , R e c h n u n g s b e t r a g @
I Numerische Variablen werden mit 0 initialisiert.
I Variant-Variablen werden mitEmpty initialisiert.
I String-Variablen werden mit “” (leerer String) initialisiert.
Umgang mit Datentypen
Sub E r m i t t l e M o n a t 1 () Dim s t r W e r t As S t r i n g s t r W e r t = " 2 4 . 1 2 . 2 0 1 2 "
M s g B o x " M o n a t : " & Mid( strWert , 4 , 2) End Sub
Funktioniert nicht bei"strWert = "1.1.2012"!
Sub E r m i t t l e M o n a t 2 () Dim d a t W e r t As D a t e d a t W e r t = # 1 2 / 2 4 / 2 0 1 2 #
M s g B o x " M o n a t : " & M o n t h( d a t W e r t ) End Sub
Umgang mit Datentypen
Sub E r m i t t l e M o n a t 1 () Dim s t r W e r t As S t r i n g s t r W e r t = " 2 4 . 1 2 . 2 0 1 2 "
M s g B o x " M o n a t : " & Mid( strWert , 4 , 2) End Sub
Funktioniert nicht bei"strWert = "1.1.2012"!
Sub E r m i t t l e M o n a t 2 () Dim d a t W e r t As D a t e d a t W e r t = # 1 2 / 2 4 / 2 0 1 2 #
M s g B o x " M o n a t : " & M o n t h( d a t W e r t ) End Sub
Umgang mit Datentypen
Sub E r m i t t l e M o n a t 1 () Dim s t r W e r t As S t r i n g s t r W e r t = " 2 4 . 1 2 . 2 0 1 2 "
M s g B o x " M o n a t : " & Mid( strWert , 4 , 2) End Sub
Funktioniert nicht bei"strWert = "1.1.2012"!
Sub E r m i t t l e M o n a t 2 () Dim d a t W e r t As D a t e d a t W e r t = # 1 2 / 2 4 / 2 0 1 2 #
M s g B o x " M o n a t : " & M o n t h( d a t W e r t ) End Sub
Ausdr¨ ucke
Wiederholung: Variablen
I Mit Hilfe von Variablen kann man (tempor¨ar) Werte speichern und diese in den Prozeduren verwenden.
I Der Wert einer Variablen kann durch eine Zuweisung ver¨andert werden.
I Variablen werden ¨uber Bezeichner (Variablennamen) referenziert.
I Die Deklarationeiner Variablen ist das Vereinbaren einer Variablen vor ihrem ersten Gebrauch.
Dim V a r i a b l e n n a m e As D a t e n t y p I Beispiele:
Dim A n z a h l As I n t e g e r Dim A u s g a b e T e x t As S t r i n g
Dim A l t e r As Integer, T e m p e r a t u r As I n t e g e r Dim G e s t e r n As D a t e
Konstanten
I Konstanten werden ebenfalls ¨uber Bezeichner referenziert, sind aber unver¨anderlich.
I Einer Konstanten wird bereits w¨ahrend der Deklaration ein Wert zugewiesen, der sp¨ater nicht mehr ver¨andert werden kann.
C o n s t K o n s t a n t e n n a m e As D a t e n t y p = A u s d r u c k I Beispiele:
C o n s t Pi as D o u b l e = 3 . 1 4 1 5 9
C o n s t P r o g r a m m n a m e as S t r i n g = " M e i n P r o g r a m m "
I VBA stellt eine Vielzahl von Konstanten zur Verf¨ugung (siehe z.B. Abschnitt Meldungsfenster)
I Wichtig String-Konstanten: Zeilenumbruch vbCrLf , Tabulator
vbTab
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)
Vergleiche mit String-Mustern
I Der Vergleichsoperator Like wird verwendet, um Strings mit String-Mustern zu vergleichen.
Zeichen Bedeutung Beispiel
? Ein einzelnes Zeichen "Hallo"Like "H?lo"-> false
* Kein oder mehrere Zeichen "Haut"Like "H*t"-> true [Liste] Ein Zeichen der Liste "X"Like "[A-Z]"-> true [!Liste] Ein Zeichen nicht in der Liste "K"Like "[!a-m]"-> false
Kontrollstrukturen
Wiederholung: Verzweigungen
I Bei Verzweigungen werden Programmteile abh¨angig von einer Bedingung ausgewertet.
If A u s d r u c k T h e n ...
E l s e ...
End If
If A l t e r >= 18 T h e n M s g B o x " N o r m a l t a r i f "
E l s e
M s g B o x " J u g e n d t a r i f "
End If
Schleifen
I Mit Schleifen wird ein Anweisungsblock wiederholt ausgef¨uhrt.
I Z¨ahlergesteuerte Wiederholung
For Z a e h l e r = S t a r t To E n d e [ S t e p d ] A n w e i s u n g s b l o c k
N e x t I Beispiel
For X = 1 To 20 S t e p 5 D e b u g .P r i n t " X ist " & X N e x t
I Schrittweite wird durch Step angepasst, ohne Angabe wird Schrittweite 1 verwendet.
Schleifen unter Bedingungen
I Kopfgesteuerte bedingte Wiederholung
Do W h i l e/U n t i l A u s d r u c k A n w e i s u n g s b l o c k L o o p
X = 1
Do U n t i l X >= 20
D e b u g .P r i n t " X ist " & X X = X + 5
L o o p
Y = 1
Do W h i l e Y < 20
D e b u g .P r i n t " Y ist " & Y Y = Y + 5
L o o p I Fussgesteuerte bedingte Wiederholung
Do
A n w e i s u n g s b l o c k L o o p W h i l e/U n t i l
A u s d r u c k
X = 1 Do
D e b u g .P r i n t " X ist " & X X = X + 5
L o o p U n t i l X >= 20 I Fussgesteuerte Schleifen werden immer mind. einmal
ausgef¨uhrt!
Prozeduren und Funktionen
Ubersicht: Prozeduren ¨
I Prozeduren bestehen aus einer Folge von Anweisungen (z.B.
Zuweisungen von Variablen, Prozeduraufrufen, Verzweigungen, Schleifen, ...).
I Wichtige Form der Abstraktion beim Programmieren!
Sub-Prozeduren
I Sub-Prozeduren geben keinen Wert zur¨uck.
I Syntax von einfachen Sub-Prozeduren
Sub P r o z e d u r n a m e () ...
End Sub
I Syntax von Prozeduraufrufen
P r o z e d u r n a m e C a l l P r o z e d u r n a m e
Prozeduren mit Parametern
I Syntax von Sub-Prozeduren mit Parametern
Sub P r o z e d u r n a m e ([ B y V a l | B y R e f ] P a r a m e t e r n a m e [As T y p e ] ,...)
...
End Sub
I Syntax von Prozeduraufrufen
P r o z e d u r n a m e ( A u s d r u c k , . . . ) C a l l P r o z e d u r n a m e ( A u s d r u c k , . . . )
Prozeduren mit Parametern und Call-by-Value
I Der Wert des Ausdrucks wird als Kopie an den die Prozedur weitergegeben. Die Prozedur kann den ¨ubergebenen Wert ver¨andern, ohne dass sich der urspr¨ungliche Wert ¨andert (call by value).
Sub I n c r V a l ( B y V a l i as I n t e g e r) i = i + 1
End Sub ...
Dim j as Integer, k as I n t e g e r j = 10
C a l l I n c r V a l ( j )
k = j ’ k hat h i e r den W e r t 10
Prozeduren mit Parametern und Call-by-Reference
I Alternativ kann ein Parameter eine Referenz auf die Variable erhalten, die den Wert enth¨alt (call by reference).
I Die Prozedur arbeitet dann nicht mit einer Kopie, sondern kann die Variable selbst direkt ver¨andern.
Sub I n c r R e f ( B y R e f i as I n t e g e r) i = i + 1
End Sub ...
Dim j as Integer, k as I n t e g e r j = 10
C a l l I n c r R e f ( j )
k = j ’ k hat h i e r den W e r t 11
Funktionen
I Funktionen k¨onnen, wie Prozeduren, mehrere Anweisungen ausf¨uhren und gebenimmer einen Wert an das aufzurufende Programm zur¨uck.
I Syntax von Funktionen
F u n c t i o n F u n k t i o n s n a m e ([ B y V a l | B y R e f ] P a r a m e t e r n a m e [As T y p e] ,...) [As T y p e]
...
F u n k t i o n s n a m e = R u e c k g a b e w e r t ...
End F u n c t i o n
I Wenn kein R¨uckgabewert spezifiziert wird, wird ein Standardwert entsprechend dem Datentypen der Funktion zur¨uckgegeben.
I Syntax von Funktionsaufrufen
F u n k t i o n s n a m e ( A u s d r u c k , . . . )
I W¨ahrend Prozeduraufrufe eigenst¨andige Anweisungen sind, sind Funktionsaufrufe Ausdr¨ucke.
Beispiele: Prozeduren und Funktionen
Sub A b s a t z F o r m a t i e r e n ()
A c t i v e D o c u m e n t . S e l e c t i o n . R a n g e . B o l d = T r u e A c t i v e D o c u m e n t . S e l e c t i o n . R a n g e . I t a l i c = T r u e End Sub
F u n c t i o n Max( x as Integer, y as I n t e g e r) as I n t e g e r If x < y T h e n
Max = y E l s e
Max = x End F u n c t i o n
Aufruf von Prozeduren und Funktionen mit Parametern
I Wenn der Aufruf in einer Zeile steht, sind verschiedene Varianten m¨oglich:
P r o c N a m e A , B , C C a l l P r o c N a m e ( A , B , C )
I Aus der Praxis: Verwenden Sie in diesem FallCall zum Aufruf von Methoden und klammern Sie die Argumente!
I Sonst wird die Variante mit Klammern und ohneCall
verwendet (ProcName(A,B,C)).
I Hat eine Prozedur nur einArgument und wird dieses eingeklammert ohne Call zu verwernden, wird zun¨achst das Argument auswertet. Dabei kann aus einem Call-by-Ref unbeabsichtigt ein Call-by-Value werden, oder auch ein Typkonversionsfehler passieren.
C a l l I n c r R e f ( j ) ’ S e m a n t i k von Call - By - Ref I n c r R e f ( j ) ’ S e m a n t i k von Call - By - Val I n c r R e f j ’ S e m a n t i k von Call - By - Ref
Im Dialog mit dem Benutzer
Wiederholung: Meldungsfenster
I Meldungsfenster k¨onnen dazu genutzt werden, dem Benutzer Informationen mitzuteilen und auch abzufragen. Sie bestehen aus demMeldungstext und standardm¨aßig der Schaltfl¨ache
“OK”. Sie kann optional mit einem Titel,
Informationssymbolen und weiteren Schaltfl¨achen erg¨anzt werden.
I Einfaches Meldungsfenster
M s g B o x(" M e l d u n g s t e x t ")
I Meldungsfenster mit Titel und Informationssymbol
M s g B o x(" M e l d u n g s t e x t ", v b I n f o r m a t i o n , " T i t e l ")
Schaltfl¨ achen
M¨ogliche Kombinationen von Schaltfl¨achen Konstante Wert Schaltfl¨ache
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
R¨ uckgabewerte von Schaltfl¨ achen
Konstante Wert gew¨ahlte Schaltfl¨ache
vbOk 1 OK
vbCancel 2 Abbrechen
vbAbort 3 Abbrechen
vbRetry 4 Wiederholen
vbIgnore 5 Ignorieren
vbYes 6 Ja
vbNo 7 Nein
Eingabedialoge
I Die FunktionInputBox erzeugt Eingabedialog mit Text und einer Eingabezeile.
I Der vom Anwender eingegebene Wert wird als String
zur¨uckgeliefert. Bei Bet¨atigen der Schaltfl¨acheAbbrechen ist es der leere String"".
I Der optionale ParameterDefault legt den Wert fest, der standardm¨assig im Eingabefeld angezeigt wird.
I n p u t B o x ( Text , [ T i t l e ] , [ D e f a u l t ])
Objekte in VBA
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¨atter (Worksheet), Diagramme (Charts) und Zellen (Range,Cell).
I Das gerade aktive Objekt wird mittelsActiveXXX referenziert (z.B. ActiveWorkbook oderActiveCell).
I Eine vollst¨andige ¨Ubersicht listen die Developer Referenzen (z.B. http://msdn.microsoft.com/en-us/library/
office/ff846392(v=office.14).aspx).
Objekte
I Objekte sind Programmeinheiten, die Daten sowie die Prozeduren zum Verarbeiten dieser Daten enthalten.
I Objekte haben
I einen Zustand (definiert durch Eigenschaften/Properties),
I ein Verhalten (definiert durch objektspezifische Prozeduren/Methoden) und
I eine Identit¨at (wodurch es sich von Objekten des gleichen Typs unterscheidet).
Klassen
I Eine Klasse ist eine Art Modell f¨ur Objekte des gleichen Typs.
I Sie dient als Bauplan f¨ur die einzelnen Objekte und definiert deren Eigenschaften und Methoden.
I Beispiel: Documentbezeichnet die Klasse f¨ur Word-Dokumente,
ActiveDocument eine Objekt-Instanz der KlasseDocument.
Zugriff auf Methoden und Eigenschaften
I Objektverweise sind Variablen, die eine Referenz auf ein Objekt enthalten.
I Deklaration von Objektvariablen
Dim O b j e k t v a r i a b l e as O b j e k t d a t e n t y p I Zuweisung auf Objektvariable:
Set O b j e k t v a r i a b l e = O b j e k t I Zugriff auf Eigenschaften:
O b j e k t v a r i a b l e . E i g e n s c h a f t s n a m e I Aufruf von Methoden:
O b j e k t v a r i a b l e . M e t h o d e n n a m e
O b j e k t v a r i a b l e . M e t h o d e n n a m e ( P a r a m e t e r ) I L¨oschen von Objektvariablen
Set O b j e k t v a r i a b l e = N o t h i n g
Auflistungen (Collections)
I Auflistungen sind spezielle Objekte, die aus einer Menge von Objekten des gleichen Typs bestehen.
I Beispiel: Die AuflistungWorksheetsin Excel enth¨alt alle aktuell ge¨offneten Tabellenbl¨atter.
I Auf einzelne Objekte in einer Auflistung wird ¨uber einen Indexwert zugegriffen (beginnend mit 1).
Sub B l a e t t e r ()
Dim A n z a h l As Integer, I n d e x as I n t e g e r A n z a h l = W o r k s h e e t s .C o u n t
For I n d e x = 1 To A n z a h l
D e b u g .P r i n t W o r k s h e e t s ( I n d e x ) .N a m e End Sub
I H¨aufig kann auch der Name des Objekts zum Zugriff verwendet werden.
W o r k b o o k s (" M a p p e 1 . x l s m ") . W o r k s h e e t s (" T a b e l l e 2 ") . R a n g e (
" A1 : B5 ")
VBA Objekte in Word und Excel
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 B r i e f As D o c u m e n t
Set B r i e f = D o c u m e n t s .O p e n( A c t i v e D o c u m e n t . P a t h & " \ T e s t b r i e f . d o c x ")
B r i e f .C l o s e
Set B r i e f = N o t h i n g ’ g i b t den S p e i c h e r p l a t z w i e d e r f r e i
End Sub
Activate Aktiviert das Dokument
Close Schliesst und speichert das Dokument
Path Pfad zum Speicherort des Dokuments
Printout Druckt das Dokument
Save Speichert das Dokument
SaveAs Speichert das Dokument unter einem neuen Namen
Weitere Methoden/Eigenschaften: Verwendung von Templates, Schreib-/Passwortschutz
Textbereiche
I Ein RangeObjekt referenziert einen zusammenh¨angenden
Bereich eines Dokuments.
I Es wird ¨uber einen Start- und einen Endcharacter definiert.
I Ein RangeObjekt kann auch nur die Eingabemarke definieren.
Sub T e s t R a n g e O b j e c t s ()
Dim r n g I n s As Range , r n g P a r 1 As Range , r n g P a r 2 as R a n g e
Dim doc As D o c u m e n t
Set r n g P a r 1 = doc . P a r a g r a p h s (1) . R a n g e
M s g B o x " Der 1. A b s a t z b e g i n n t mit dem W o r t " &
r n g P a r 1 . W o r d s . F i r s t & " . "
Set r n g P a r 2 = doc . R a n g e ( S t a r t := doc . P a r a g r a p h s (2) . R a n g e . Start , End:= doc . P a r a g r a p h s (3) . R a n g e .End) Set r n g I n s = doc . R a n g e ( S t a r t :=0 , End: = 0 )
r n g I n s . I n s e r t B e f o r e " H e l l o "
End Sub
Markierte Textbereiche
I Das SelectionObjekt 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 r n g P a r a g r a p h As R a n g e S e l e c t i o n . F o n t . B o l d = T r u e S e l e c t C a s e S e l e c t i o n .T y p e
C a s e w d S e l e c t i o n N o r m a l
M s g B o x " Sie h a b e n f o l g e n d e n T e x t m a r k i e r t : "&
S e l e c t i o n . T e x t C a s e w d S e l e c t i o n I P
M s g B o x " Sie h a b e n n i c h t s m a r k i e r t "
Set r n g P a r a g r a p h = A c t i v e D o c u m e n t . P a r a g r a p h s (2) . R a n g e r n g P a r a g r a p h .S e l e c t
S e l e c t i o n . F o n t . I t a l i c = T r u e End Sub
Zugriff auf Dokumenteninhalte
Characters Auflistung einzelner Zeichen
Sentences Auflistung von S¨atzen
Paragraphs Auflistung von Abs¨atzen
Words Auflistung von Worten
Start/End Start- bzw. Endposition
Text Textinhalt
Beispiel: Arbeitsmappen und Tabellen in Excel
I Arbeitsmappen (Workbooks) beinhalten Tabellenbl¨atter (Worksheets) und Diagramme (Charts).
I UsedRange referenziert den verwendeten Bereich eines Tabellenblatts.
I CurrentRegionbezeichnet einen Bereich gef¨ullter Zellen, die von leeren Zellen umgeben sind.
I Mittels Cellslassen siche einzelne Zellen referenzieren.
Sub A r t i k e l S u c h e n ()
Dim A r t N r As String, Z a e h l e r As I n t e g e r
A r t N r = I n p u t B o x (" G e b e n Sie e i n e A r t i k e l n u m m e r ein : ",
" A r t i k e l s u c h e n ")
T h i s W o r k b o o k . S h e e t s (" A r t i k e l ") .A c t i v a t e
For Z a e h l e r = 1 To R a n g e (" A1 ") . C u r r e n t R e g i o n . R o w s .C o u n t If C e l l s ( Zaehler ,1) . V a l u e = A r t N r T h e n
M s g B o x " A r t i k e l w u r d e g e f u n d e n "
E x i t Sub End If N e x t
M s g B o x " A r t i k e l n i c h t v o r h a n d e n "
End Sub
Zellbereiche
I Ein RangeObjekt bezeichnet in Excel einen
zusammenh¨angenden Zellbereich.
W o r k s h e e t s (1) . R a n g e (" A4 ") . V a l u e
W o r k s h e e t s (" M e s s r e i h e ") . R a n g e (" B2 : B8 ") .C o u n t R a n g e (" A1 : A5 "," B1 : B5 ") .C o u n t
’ v e r w e n d e t i m p l i z i t das a k u t e l l e T a b e l l e n b l a t t R a n g e ( C e l l s (3 ,3) , C e l l s (4 ,6) ) .S e l e c t
’ e n t s p r i c h t R a n g e (" C3 : F3 ") . S e l e c t
Zusammenfassung
I Ausdr¨ucke: Variablen, Konstanten, zusammengesetzte Ausdr¨ucke mit Operatoren, Funktionsaufrufe
I Anweisungen (Statements): Prozeduraufrufe, Kontrollstrukturen (Verzweigungen, Schleifen)
I Meldungs- und Eingabefenster
I Objekte mit Eigenschaften und Methoden
I VBA Objekte in Word und Excel (Auswahl durchSelectionund
Range)