VBA im Zusammenspiel mit Excel - RWTH

Seminarvortrag
Maik Bürschgens
13.12.2011
Einführung in die
Programmiersprache
Visual Basic for
Applications…
AGENDA
1.
2.
3.
4.
5.
6.
7.
8.
Vorwort
Allgemeine Informationen zu VBA
VBA im Zusammenspiel mit Excel
Zusätzliche Funktionalitäten von VBA
Weitergehende Informationen
Fazit und Schlusswort
Literatur
Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
2
AGENDA
1.
2.
3.
4.
5.
6.
7.
8.
Vorwort
a) Ziel des Vortrags
b) Warum Excel als Demonstrationsprogramm?
c) Wichtige Hinweise zur weiteren Verlauf der Präsentation
Allgemeine Informationen zu VBA
VBA im Zusammenspiel mit Excel
Zusätzliche Funktionalitäten von VBA
Weitergehende Informationen
Fazit und Schlusswort
Literatur
Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
3
Vorwort - Ziel des Vortrags
4
 Einblick in die Fähigkeiten der Programmiersprache
„Visual Basic for Applications“
 Einstieg in die damit verbundene Office Programmierung
Desweiteren:
• Vorurteilen gegenüber VBA vorbeugen
• Motivation mitgeben, VBA zu erlernen und einzusetzen
4
Ausgerechnet Excel als
Demonstrationsprogramm?
5
 VBA Programme können nicht direkt ausgeführt werden
• abhängig von einer Wirtsanwendung
• Zugriff auf die Skripte über eine Programmierschnittstelle (IDE)
 im weiteren Verlauf: Excel 2007
• weitreichende Anwendungsgebiete
• bekannt und häufig eingesetzt
• tiefgreifenden Gebrauch einer Office Anwendung kennenlernen
• Ausführungen lassen sich allgemein auf die Office Programmierung übertragen
5
AGENDA
1. Vorwort
2. Allgemeine Informationen zu VBA
a) Ursprung und Einsatzgebiete
b) Spracheigenschaften
3. VBA im Zusammenspiel mit Excel
4. Zusätzliche Funktionalitäten von VBA
5. Weitergehende Informationen
6. Fazit und Schlusswort
7. Literatur
8. Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
6
Allgemeine Infos
- Ursprung und Einsatzgebiete
7
Von Microsoft entwickelt
Seit Mitte der 1990er Jahre verwendet
 aus dem BASIC Dialekt Visual Basic entstanden
zur Steuerung von Abläufen innerhalb der Office Programme konzipiert
• Excel, Outlook, Visio,…
 „Microsoft Visual Basic for Applications licensing program“
• in Produkten der Corel Corporation z.B. CorelDraw zu finden
• MindManager, AutoCAD,…
7
Spracheigenschaften
8
Leistungsfähige Skriptsprache
Syntax stark an die von Visual Basic angelehnt
 für die modulare Programmierung konzipiert
• zu lösendes Problem wird in einzelne Module aufgeteilt, die jeweils eine genaue
Schnittstellenspezifikation aufweisen (Def.: Handbuch Programmiersprachen)
• Skripte (Makros) in kleinere Bestandteile zerlegt, die übersichtlich und einfach
zu warten sind
8
Spracheigenschaften
9
 Ursprünglich als prozedurale Programmiersprache entwickelt…
• Quellcode in kleine Einheiten unterteilt, die sich gegenseitig aufrufen können
 realisiert durch Prozeduren und Funktionen
• Erhielt mit der Zeit objektorientierte Aspekte
• Klassen und Objekte können syntaktisch dargestellt und implementiert werden
• Datenkapselung
• Keine direkten Speicherzugriffe wie z.B. in C/C++ möglich (Zeiger)
• Schnittstellenvererbung
 Aber: keine Implementierungsvererbung(!)
9
AGENDA
1. Vorwort
2. Allgemeine Informationen zu VBA
3. VBA im Zusammenspiel mit Excel
a) Vorteile von VBA
b) Vorstellung eines Anwendungsfalls – „Joggoh!“
c) Grundlegende Excel Objekte und Aktionen
d) Ereignis(-prozeduren)
e) Dialoge und Formulare zur Interaktion mit dem Benutzer
4. Zusätzliche Funktionalitäten von VBA
5. Weitergehende Informationen
6. Fazit und Schlusswort
7. Literatur
8. Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
10
VBA im Zusammenspiel mit Excel
– Warum VBA?
11
Theoretisch leistbar, viele Aufgaben manuell mit Excel Funktionen zu
lösen…
ABER!
• Excel Anwendung relativ einfach an die eigenen Bedürfnisse anpassen
Durch zusätzliche Funktionalitäten erweitern
zur Vermeidung von Fehlbedienungen einschränken
• Die von Excel benötigten Fähigkeiten optimal einsetzen
• Benutzerdefinierte Abläufe und Lösungen festlegen…
• …und im Hintergrund automatisieren!
• Deutliche Zeiteinsparung und saubere Lösungen!
11
Vorstellung eines Anwendungsfalls
12
„Joggoh!“ – die etwas sportlichere Datenbank
Ermöglicht die Verwaltung sportlicher Aktivitäten im Ausdauerbereich
• Jogging, Walking, Wandern, Fahrradfahren, Cross Trainer,…
• Upgrade für Krafttraining ist in Arbeit
12
Ein Anwendungsfall –
(Vor-)Formatierung einer Kategorie
13
13
Ein Anwendungsfall–
(Vor-)Formatierung einer Kategorie
14
Blau: Fehlerbehandlung
• GoTo wird dafür in VBA noch frequentiert verwendet
Schwarz: Funktionskörper
• Hier sogar eine Ereignisprozedur
• Wird aufgerufen, sobald eine neue Kategorie in Joggoh erzeugt wird
• Kategorien entsprechen Tabellen in Excel
Braun: Beispiele für einfache VBA Funktionen aus dem Sprachkern
Rot: Zellformatierung
• Beispiel für die Interaktion mit Excel Objekten
14
Dialoge und Formulare
18
dienen zur Interaktion mit dem Benutzer
sollen zu einer komfortablen und deutlichen Benutzerführung verhelfen
Dialogformen:
• Integrierte Dialogfelder
• Tabellenblätter als Formulare
• Benutzerdefinierte Dialoge („UserForms“)
15
Dialoge und Formulare
- integrierte Dialogfelder
19
Integrierte Dialogfelder:
• bereits für spezielle Aufgaben präpariert und konzipiert
• Message Box
• Input Box
• Speichern unter… Dialog [als Repräsentant für weitere Dialogformen]
16
Dialoge und Formulare
- Das Tabellenblatt als Formular
20
Positionierung von Steuerelementen in Tabellenblättern ermöglicht die
Umgestaltung zu Formularen
17
Dialoge und Formulare
– User Forms
21
Integrierte Dialoge reichen oftmals nicht aus
„UserForms“ (benutzerdefinierte Dialoge) können auf die Bedürfnisse der
Anwendung abgestimmt werden – VBA erweist sich hier als vielseitig
Integrierte Ereignisse dienen als Grundlage von Ablaufsteuerungen
Im VBA Editor mühelos zu gestalten
• Einfügen  UserForm
• Mit Drag&Drop angebotene Steuerelemente platzieren
• Das Eigenschaftenfenster zur Manipulation von Attributen nutzen
• Mit Rechtsklick  Code anzeigen zum Codefenster gelangen
• Wird eingeblendet mit der Anweisung
[UserFormName].Show()
18
User Forms
22
Ein Auszug wichtiger Ereignisse, Attribute und Methoden
Anweisung
UserForm_Initialize()
Beschreibung
Ereignis, Bietet die Möglichkeit beim Erzeugen des Dialoges den Komponenten
Standardwerte zuzuordnen.
[Komponente]_Clicked()
Ereignis, Einfacher Mausklick
[Komponente]_DblClick()
Ereignis, Doppelklick
[Liste].AddItem()
Fügt ein weiteres Auswahlelement der Liste / ComboBox hinzu
[Liste].Clear()
Löscht die Auswahlliste / ComboBox
[Liste].RemoveItem()
Entfernt ein Auswahlelement aus der Liste / ComboBox
[Liste].Value
Aktueller Wert einer Liste / ComboBox
[Liste].ListCount
Anzahl der Einträge einer Liste / ComboBox
19
User Forms
23
Der benutzerdefinierte Dialog zur Editierung bestehender Daten in der
Joggoh! – Datenbank [Auszüge vom Programmcode]
20
User Forms
24
Der benutzerdefinierte Dialog zur Editierung bestehender Daten in der
Joggoh! – Datenbank [Gestaltung nach dem Aufruf]
21
AGENDA
1.
2.
3.
4.
5.
6.
7.
8.
Vorwort
Allgemeine Informationen zu VBA
VBA im Zusammenspiel mit Excel
Zusätzliche Funktionalitäten von VBA
a) I/O Operationen
b) Interaktion mit Datenbanken
c) Präsentation der Daten: Diagramme
Weitergehende Informationen
Fazit und Schlusswort
Literatur
Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
22
Weitere Funktionalitäten von VBA
26
Bisher vorgestellte Möglichkeiten reichen für fortgeschrittene Office
Programmierung aus
Probleme bei tiefgreifenden Anwendungen, wenn man beispielsweise…
• Mehrere Datensätze aus einer Datenbank gleichzeitig auslesen
• Diese automatisch in eine Excel Tabelle hinterlegen
• Daten visualisieren und grafisch präsentieren
möchte!
Lösung:
• Befehlssatz zur Kommunikation mit externen Programmen und
Datenquellen
• Befehlssatz, um dynamisch Grafiken zu erzeugen und zu verwalten
23
Weitere Funktionalitäten von VBA
- I/O Operationen
27
Recht einfach gestaltet
Wenige Grundoperationen, die schon ausreichen:
• Datensätze aus Textdateien auslesen
• Datensätze in Textdateien exportieren
• Allgemeine Dateioperationen
Suchen
Kopieren
Löschen
Dateigröße
…
24
I/O Operationen
28
Beispiel: Export eines Datensatzes
aus Joggoh! in eine CSV Datei
I/O Operationen
29
Beispiel:
Import eines Datensatzes
aus einer CSV-Datei nach
Joggoh!
26
I/O Operationen
31
Weitere Dateioperationen
Methode
Beschreibung der Funktionalität
Dir ( [Pfad] & [Suchmuster] )
Dient zur Suche von Dateien. Es können auch Platzhalter (*)
verwendet werden. Als Rückgabewert erhält man den ermittelten
Pfad oder einen Leerstring.
FileDateTime ( [Pfad] )
Liefert Datum und Uhrzeit der letzten Änderung der Datei
FileLen ( [Pfad] )
Liefert die Dateigröße
GetAttr ( [Pfad] And Konstante)
Dient zur Ermittlung von Datei- und Verzeichnisattributen.
[vbHidden, vbSystem, vbDirectory und vbArchive]
FileCopy( [PfadAlt], [PfadNeu])
Kopiert angegebene Datei an den übergebenen Zielort
Name ( [Alt], [Neu])
Benennt eine Datei um
Kill ( [Pfad])
Löscht eine Datei. Die Aktion kann nicht rückgängig gemacht werden.
27
Weitere Funktionalitäten von VBA
- SQL Operationen
32
Datenbanken gewährleisten eine persistente Datenhaltung
Excel als Tabellenkalkulationsprogramm…
• Dient bei im Alltag häufig als „Datenbankersatz“ / Übersichtsprogramm
• Reicht bei größeren Datenmengen dafür nicht mehr aus
• Muss deswegen häufiger mit Datenbanken kommunizieren
VBA…
• Weist einen Befehlssatz für das Absetzen von SQL Befehlen auf
• Besitzt Methoden und Objekte zur Verarbeitung der Resultate
• Kann die Interaktion mit Datenbanken [im Hintergrund] automatisieren
28
SQL Operationen
33
Beispiel:
Import von Datensätzen aus
einer Access-Datenbank
[Prozedurauszug]
29
Weitere Funktionalitäten von VBA
- Präsentation der Daten
35
VBA bietet Möglichkeiten…
• Diagramme und eingebettete Diagramme zu erstellen
• Ihre Eigenschaften zu manipulieren
Beschriftungen, Typ, Form und Farbe
…
• Sie zu verwalten
Kopieren, Einfügen oder Export als PNG-Datei
VBA unterscheidet…
• Diagrammblätter
in einem separaten Blatt generiert und verwaltet
• Eingebettete Diagramme
in ein bereits bestehendes Tabellenblatt integriert
30
Diagramme
36
Präsentation von Distanz (m)
und zugehöriger Zeit (s) einer
Kategorie aus Joggoh! in einem
Diagrammblatt
[Prozedurauszug!]
Diagramme
40
Delete: Löscht das ausgewählte Diagramm
Copy (After/Before): Kopie eines Diagrammblatts
• keine Angabe: Kopie in eine frisch angelegten Arbeitsmappe
 Copy: Kopie eines eingebetteten Diagramms in die Zwischenablage
• Paste: Einfügen des eingebetteten Diagramms
 Export (Destination): Export des ausgewählten Diagramms
• PNG-Datei
32
AGENDA
1.
2.
3.
4.
5.
Vorwort
Allgemeine Informationen zu VBA
VBA im Zusammenspiel mit Excel
Zusätzliche Funktionalitäten von VBA
Weitergehende Informationen
a) Der Makro Rekorder
6. Fazit und Schlusswort
7. Literatur
8. Anhang
Einstieg in Visual Basic for Applications und die Office Programmierung
33
Der Makro Rekorder
42
Zeichnet diverse Aktionen, z.B. die Auswahl einer speziellen Zelle, auf
Übersetzt die Aktionen automatisch in VBA Code
Speichert den Code in einem Modul ab
Bietet Anfängern die Möglichkeit in die Office Programmierung einzusteigen
Erspart dem Professionellen Entwickler viel Zeit bei der Implementierung
„trivialer“ Aufgaben
Mehr Zeit und Konzentration für die schweren bzw. zeitintensiven Aufgaben
Unter Entwicklertools  Makro aufzeichnen zu finden
34
Fazit und Schlusswort
43
 VBA Befehlssatz ist sehr weit gefächert
• Sprachkern mit weitreichenden Funktionen
• I/O Operationen ( Textdateien, Datenbanken,…)
• Präsentation der Daten
• Manipulation von Zellen / Zellbereichen
•…
 Es lassen sich viele Abläufe…
• Im Hintergrund automatisieren
• Vereinfachen
35
Fazit und Schlusswort
44
 Viele Tutorials und Bücher zu diesem Thema
 Foren runden das Angebot ab
 Erfahrungen in einer Programmiersprache beruhen weniger auf Theorie als auf
Praxis
Übung macht den Meister!
36
Fragen?
45
Vielen Dank für ihre Aufmerksamkeit!
37
Literatur
46
Einstieg in VBA mit Excel, Thomas Theis
Galileo Computing, 2te und aktualisierte Auflage 2010
 ftp://ftp.fernuni-hagen.de/pub/pdf/urz-broschueren/broschueren/b012.pdf
Handbuch Programmiersprachen (Softwareentwicklung zum Lernen und Nachschlagen)
Peter A. Henning und Holger Vogelsang, Hanser Verlag, 2007
 http://www.online-excel.de/excel/singsel_vba.php?f=50
 http://www.office-loesung.de/
 http://msdn.microsoft.com/de-de/isv/bb190538.aspx
38
Literatur
47
 http://de.wikipedia.org/wiki/Visual_Basic_for_Applications
 http://de.wikipedia.org/wiki/Tabellenkalkulation
 http://de.wikipedia.org/wiki/Microsoft_Excel
 http://de.wikibooks.org/wiki/VBA_in_Excel_-_Grundlagen
http://www.vba-beispiele.de/office.php?act=excel&cat=vbaereignisse
http://www.activevb.de/tutorials/tut_vbatutorial/tut_vbatutorial.html
39
AGENDA
1.
2.
3.
4.
5.
6.
7.
8.
Vorwort
Allgemeine Informationen zu VBA
VBA im Zusammenspiel mit Excel
Zusätzliche Funktionalitäten von VBA
Weitergehende Informationen
Fazit und Schlusswort
Literatur
Anhang
a. Sprachkonzept von VBA (Sprachkern)
b. Excel als Tabellenkalkulationsprogramm
c. Der VBA-Editor als Entwicklungsumgebung
Einstieg in Visual Basic for Applications und die Office Programmierung
40
Syntaktischer Aufbau des Quellcodes
49
 Ungewohnte Syntax …
• Keine Semikolons, welche das Ende einer Anweisung markieren
• Prinzipiell nur eine Anweisung pro Zeile erlaubt
• Mehrzeilige Anweisungen durch das Einfügen von Leerzeichen und
Unterstrich am Ende der Zeile möglich [„ _“]
• Case insensitive
• Keine Escape-Sequenzen zur Darstellung von Steuerzeichen
 integrierte Konstanten
Datentypen
50
Datentyp
Speicherbedarf
Wertebereich und Bedeutung
Boolean
2 Byte
Wahrheitswert, kann den Wert „True“ oder „False“ annehmen
Byte
1 Byte
Ganze Zahl von 0 bis 255
Integer
2 Byte
Ganze Zahl von -32.768 bis 32.767
Long
4 Byte
Ganze Zahl von -2,1 * 10^9 bis 2,1 * 10^9
4 Byte
Gleitkommazahl mit einfacher Genauigkeit von ca. -3,4 * 10^38
bis ca. -1,4* 10^(-45) für negative und von ca. 1,4*10^(-45) bis
3,4 * 10^38 für positive Werte
Double
8 Byte
Gleitkommazahl mit doppelter Genauigkeit von ca. 1,8*10^308 bis -4,9*10^(-324) für negative und von ca.
4,9*10^(-324) bis 1,8*10^308 für positive Werte
Date
8 Byte
Datumsangabe zwischen 1.Januar 100 und 31.Dezember 9999
Object
4 Byte
Verweis auf ein Objekt
String
10 Byte (+)
Zeichenkette mit variabler Länge
Variant
16 Byte (+)
Datentyp nicht explizit festgelegt (universell)
Single
Operatoren und Variablen
51
Operatoren:
Arithmetische Operatoren (+,-,…)
Logische Operatoren(Not, And, Or,…)
Vergleichsoperatoren (<,>,…)
Verkettungsoperatoren (&, +)
Variablen:
• Deklaration und Initialisierung erfolgt zweistufig!
Explizite/Implizite Deklaration möglich
Auf Prozedurebene oder Modulebene möglich
• Schüsselphrase „Dim [Name] As [Datentyp]“
Operatoren und Variablen
52
Konstanten:
• Deklaration und Wertzuweisung in einem Schritt
• Schlüsselwort „Const“
• öffentlich (public) oder modulbezogen (private)
Integrierte Konstanten:
• Systemdefinierte Konstanten mit spezieller Funktionalität
Beispiel: vbCrLf  Zeilenumbruch
Beispiel: xlLine  Diagrammtyp „Liniendiagramm“
Unterprogrammtechnik
53
Prozeduren:
• kleinste selbstständige Einheiten eines Programms
• beinhalten eine Gruppe von Anweisungen
• kein Rückgabewert
• Schlüsselphrase „Sub…End Sub“
Funktionen:
• Können Werte zurückliefern
• Schlüsselphrase „Function…End Function“
Unterprogrammtechnik
54
Parameterübergabe:
• Als Kopie (Call-By-Value, Schlüsselwort „ByVal“)
• Als Referenz (Call-By-Reference, „ByRef“, Standardeinstellung)
• Optionale Parameter möglich („Optional“)
Prozeduren sind öffentlich…
• „Public“
…oder modulbezogen
• „Private“
Klassen
55
Klassen in eigenen (Klassen-)Modulen erstellt
• Enthalten nur Methoden und Attribute der Klasse
• Aus z.B. Java oder C++ bekannte syntaktische Einleitungen wie „Class A {…}“ fallen weg
• Deklaration mehrerer Klassen in einem Modul nicht möglich
 Setter und Getter mit der Schlüsselphrase „Property Let (bzw. Get)“ gekennzeichnet
 Deklaration und Initialisierung von Objekten ebenfalls zweistufig
•
Schlüsselphrase „Set [Name] = New [Klasse]“
Diverses
56
 Desweiteren unterstützt VBA…
• (mehrdimensionale / dynamische) Arrays
• Benutzerdefinierte Datentypen (vgl. Structs aus C/C++, „type“)
• Schleifenstrukturen
• Kontrollstrukturen
• Methoden zur Fehlerbehandlung („On Error“ Anweisung, GoTo)
• Konvertierung und Manipulation von Daten, u.a.
 Datentypermittlung und Casts (Vartype, CInt, CString,…)
 Zeichenkettenmanipulation (InStr, LCase, Split,…)
[…]
Der VBA Editor
als Entwicklungsumgebung
57
Der VBA Editor
als Entwicklungsumgebung
58
Eigenständiges Programm mit Fenstersystem und Symbolleisten
Alt+F11 oder über den Reiter Entwicklertools  Visual Basic zu erreichen
Besteht aus mehreren Komponenten:
 Projektexplorer:
• Überschau der aktuell verwendeten Module in Form einer Baumstruktur
• Module als Container für bereits erstellten Programmcode
Office Objekte
Formulare
Klassenmodule
allgemeine Module
Der VBA Editor
als Entwicklungsumgebung
59
Eigenschaftenfenster:
• Übersicht für die Eigenschaften (Attribute) des aktuell ausgewählten Moduls
• erleichtert ihre Modifikation
• wird im Bereich der Formulare frequentiert verwendet
Direktfenster:
• Zu Testzwecken oder Debugging genutzt
• Akzeptiert direkte Eingaben (Funktionsaufrufe)
• Debug.Print[text]: angegebener Text erscheint im Direktfenster
Objektkatalog / IntelliSense:
• Auflistung aller durch VBA nutzbaren / verfügbaren Objekte
• Nachschlagewerk
• Standardmäßig ausgeblendet
• VBA Editor schlägt Eigenschaften und Methoden des aktuell
selektierten Objekts automatisch vor: IntelliSense
Excel als
Tabellenkalkulationsprogramm
60
Von Microsoft entwickeltes Programm zur Tabellenkalkulation
• Eine Software für die interaktive Eingabe und Verarbeitung von numerischen
und alphanumerischen Daten in Tabellenform (Def.:Wikipedia)
Teil des von Microsoft herausgegebenen Office Paketes
Aktuelle Version: Excel 2010 (bzw. 2011 für Mac)
 sehr bekannt und weit verbreitet
Excel als
Tabellenkalkulationsprogramm
61
Fähigkeiten und Bestandteile
• Bietet viele Möglichkeiten zur Datenkalkulation, -analyse und deren
Präsentation
 Sortier-/Filtervorgänge unterstützen die Auswertung
 Diagramme und Grafiken
 Gezielter Einsatz von Formeln und Funktionen
 Import von Daten aus externen Datenquellen (CSV, XML, Datenbanken,…)
 VBA Schnittstelle zur Ausführung und Bearbeitung von VBA Makros
Excel als
Tabellenkalkulationsprogramm
62
Der Arbeitsbereich von Excel
• Arbeitsmappen, welche Tabellenblätter beinhalten
• Tabellenblätter bestehen aus Zellen, die Daten speichern können
• Eindeutige Identifikation von Zellen:
Angabe von Spalte (Buchstabe) und Zeile (Nummer)
Zellbezugssystem
• Oberer Bereich: Menüleiste mit zahlreichen Funktionen
Excel als
Tabellenkalkulationsprogramm
63