VBA für Excel

1. VBA für Excel 22.02.2010 VBA für Excel eine Einführung in das Programmieren mit „Visual Basic for Applications“ speziell (aber nicht nur) für Excel unter Windows Andreas Rozek HyMeSys Software & Consulting Brunnenstraße 30/2 71032 Böblingen Telefon: (07031) 436 5784 Email: A.Rozek@gmx.de URL: www.Rozek.de Andreas Rozek HyMeSys Software & Consulting

2. VBA für Excel 22.02.2010 Organisatorisches • Theorie: von 900 Uhr bis 1200 Uhr • Praxis: von 1300 Uhr bis 1700 Uhr• Anwesenheitsliste → Teilnahmebestätigung • begleitende Literatur Christian Friedrich „Einstieg in VBA mit Excel“ Galileo Computing ISBN 3-89842-647-5 ca. 2490 € Andreas Rozek HyMeSys Software & Consulting

3. VBA für Excel 22.02.2010 Zum Einstieg... Andreas Rozek HyMeSys Software & Consulting

4. VBA für Excel 22.02.2010 Zum Einstieg... Andreas Rozek HyMeSys Software & Consulting

5. VBA für Excel 22.02.2010 Überblick über den Kurs Montag Grundlagen (Syntax & Semantik von VBA) Dienstag Das Excel-Objektmodell Mittwoch Ereignis-gesteuerte Programmierung Formular- und ActiveX-Steuerelemente Donnerstag Eingabeformulare, Programmentwicklung (Anmeldeformular, Zahlen-Memory) Freitag weiterführende Themen (Email, Web, usw.) Verwendung externer Objekte, Sudoku Andreas Rozek HyMeSys Software & Consulting

6. VBA für Excel 22.02.2010 Lernziele Idealerweise sollten Sie am Ende dieses (Crash-)Kurses • wissen, daß man die Funktionalität von Excel mit VBA erheblich erweitern kann; • einen ungefähren Eindruck von den Möglichkeiten und Grenzen von VBA haben; • in der Lage sein, eigene VBA-Makros zu schreiben und auszuführen; • eigene Benutzeroberflächen (UserForms) erstellen und programmieren können. Andreas Rozek HyMeSys Software & Consulting

7. VBA für Excel 22.02.2010 Kursmaterialien • Microsoft Excel: 60-Tage Testversionhttp://trial.trymicrosoftoffice.com/trialgermany/default.aspx • Excel VBA Language Referencehttp://www.microsoft.com/technet/scriptcenter/topcis/office/vba.mspx • Windows Script 5.6 Documentationhttp://www.microsoft.com/DOWNLOADS/details.aspx?familyid=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en • OpenOffice.org Version 3.xhttp://de.openoffice.org/product/info.html Andreas Rozek HyMeSys Software & Consulting

8. VBA für Excel 22.02.2010 VBA für Excel Teil I: Grundlagen Andreas Rozek HyMeSys Software & Consulting

9. VBA für Excel 22.02.2010 Ein paar Worte zu Excel • Tabellen-artige Darstellung von Zahlen und Texten(!) • Verarbeitung der Tabelleninhalte mithilfe von Formeln • Visualisierung der Tabelleninhalte mithilfe von Diagrammen Grenzen • komplexe Berechnungen, Textverarbeitung • Zugriff auf Dateien, Datenbanken, das WWW (Data Mining) • „Steuerung des Benutzers“ durch Benutzeroberflächen Andreas Rozek HyMeSys Software & Consulting

10. VBA für Excel 22.02.2010 Grenzen sprengen durch Makro-Programmierung Macros Macros Interpreter Interpreter DLLs, ActiveX Application Application System System • vorhandene Funktionalität durch (meist einfache) „Makros“ erweitern (sofern die Anwendung dies zuläßt) • durch Integration externer Komponenten (z.B. DLLs oder ActiveX Controls) zusätzliche Funktionalitäten möglich • das Gros der Funktionalitäten kommt weiterhin von der ursprgl. „Application“ (Wirtsprogramm) Andreas Rozek HyMeSys Software & Consulting

11. VBA für Excel 22.02.2010 Was ist „VisualBasic for Applications (VBA)“? • VisualBasic „Classic“ > VBA > VBScript • VisualBasic • für Anwendungen, Steuerelemente, Bibliotheken • Übersetzung in Maschinensprache (bis Version 6) • VBScript • interpretierte Scriptsprache, z.B. für WSH, HTTP-Server, ... • keine echten Datentypen, ActiveX-Steuerung • VisualBasic for Applications • interpretierte „Automatisierungssprache“ in Wirtsprogramm • Zugriff auf Objektmodell des Wirtsprogrammes Andreas Rozek HyMeSys Software & Consulting

12. VBA für Excel 22.02.2010 Wo und wie wird VBA (in Excel) eingesetzt? • exploratives Prototyping (Anforderungen erkennen) • experimentelles Prototyping (Lösungsmöglichkeiten suchen) • „Einmal“-Programme (Konvertierung von Datenbeständen, Analysen, Studien) • kleinere (häufig Firmen-interne) Projekte • kleinere Anwendungen Treibender Faktor ist stets: • Zeitersparnis (es ist fast schon alles vorhanden) Andreas Rozek HyMeSys Software & Consulting

13. VBA für Excel 22.02.2010 Einmal lernen – mehrmals nutzen • (Visual)Basic-Syntax ist stets gleich • die Objekt-Modelle ähneln sich, die Konzepte ohnehin • Ereignis-orientierte Programmierung ist omnipräsent VBA ist direkt einsetzbar in • Microsoft's Office-Anwendungen unter Windows • z.T. Microsoft's Office-Anwendungen unter MacOS X • manchen Anwendungen von Drittanbietern • OpenOffice 3.0 (evtl. auch unter MacOS X und Linux) Andreas Rozek HyMeSys Software & Consulting

14. VBA für Excel 22.02.2010 VBA für Excel Excel als VBA-Entwicklungsumgebung(im Vergleich zu Java) Andreas Rozek HyMeSys Software & Consulting

15. VBA für Excel 22.02.2010 Excel als Entwicklungsumgebung • Entwicklerwerkzeuge einblenden • Sicherheitseinstellungen anpassen • Makro-Rekorder (wird hier nicht behandelt) • Persönliche Makroarbeitsmappe c:\Dokumente und Einstellungen\benutzer\Anwendungsdaten\ Microsoft\Excel\XLSTART\Personal.xlsb Andreas Rozek HyMeSys Software & Consulting

16. VBA für Excel 22.02.2010 Java-Übersetzungseinheiten • Klassen (eine Klasse pro Datei) • Pakete (packages, als Verzeichnis oder JAR-Archiv) • z.B. unter Eclipse: Projekte und zusätzliche Dateien VBA (in Excel, Projekt-Explorer) • „Projekte“ (Arbeitsmappen, persönliche Makroarbeitsmappe) • „Excel-Objekte“ (Tabellenblätter u.a.) *.cls • Formulare (UserForms) *.frm • Standardmodule *.bas • Klassenmodule *.cls Andreas Rozek HyMeSys Software & Consulting

17. VBA für Excel 22.02.2010 Excel als Entwicklungsumgebung • VBA-Editor • Eigenschaftenfenster • Direktbereich • Editor-Einstellungen • Kontext-sensitive Hilfe (F1), IntelliSense • Objektkatalog (F2) • Modul-Ebene, Prozedur-Ebene, Code-Ebene Andreas Rozek HyMeSys Software & Consulting

18. VBA für Excel 22.02.2010 Excel als Entwicklungsumgebung (Fortsetzung) • Ausführen einer Prozedur (F5) • Haltepunkt setzen/löschen (F9) • Einzelschritt (F8), Prozedurschritt (Shift-F8) • Variablenwerte als Tool-Tip • Lokalfenster: Variablen-Sichtung und -Verwaltung • Überwachungsfenster: Überwachen von Ausdrücken • Debug.print expression Andreas Rozek HyMeSys Software & Consulting

19. VBA für Excel 22.02.2010 VBA für Excel Syntax und Semantik Andreas Rozek HyMeSys Software & Consulting

20. VBA für Excel 22.02.2010 Grundlegendes zur Syntax • eine Anweisung pro Zeile (Zeilenende = Anweisungsende) • : als Trenner zwischen zwei Anweisungen • _ als (Zeilen-)Fortsetzungszeichen • Groß-/Kleinschreibung ist nicht signifikant • Code-Editor paßt Schreibweise automatisch an • ' als Kommentarzeichen (für Zeilenkommentare) • Code-Editor (vgl. Eclipse) • beherrscht „IntelliSense“, „Syntax Colorization“ • gibt häufig bereits den Rahmen für eine Prozedur vor Andreas Rozek HyMeSys Software & Consulting

21. VBA für Excel 22.02.2010 Namen („Bezeichner“) • 1...255 Zeichen • erstes Zeichen muß ein Buchstabe sein • danach sind Buchstaben, Ziffern und Unterstriche erlaubt • Schlüsselworte sind zu meiden • Groß-/Kleinschreibung wird nicht unterschieden • ISO 8859-1 ist zulässig (Unicode?) Andreas Rozek HyMeSys Software & Consulting

22. VBA für Excel 22.02.2010 Datentypen • Boolean 2 Bytes true oder false • Byte 1 Byte 0...255 • Integer 2 Bytes -32768...+32767 • Long 4 Bytes -2147483648...+2147483647 • Single 4 Bytes ±3,402823e38...±1,401298e-45 • Double 8 Bytes ±1,79769313486231e308... ±4,94065645841247e-324 • Currency 8 Bytes -922337203685477,5808... +922337203685477,5807 Andreas Rozek HyMeSys Software & Consulting

23. VBA für Excel 22.02.2010 Datentypen (Fortsetzung) • Decimal 14 Bytes (groß, Untertyp von Variant) • Date 8 Bytes 01.01.100...31.12.9999 • Object 4 Bytes Referenz auf ein Objekt • String 10+n Bytes variable Länge < 2147483648 Zeichen • String n Bytes feste Länge 1...65535 Zeichen • Variant 16 Bytes für Zahlen • Variant 22+n Bytes für Zeichenketten Andreas Rozek HyMeSys Software & Consulting

24. VBA für Excel 22.02.2010 Datentypen-Suffixe • Integer % • Long & • Single ! • Double # • Currency @ • String $ Suffixe sind nicht Bestandteil des Variablennamens Andreas Rozek HyMeSys Software & Consulting

25. VBA für Excel 22.02.2010 Namenskonventionen • vor allem für Variablen und Konstanten • machen (Variablen-)Eigenschaften namentlich sichtbar • allgemeine Form [prefix]kind[name] • Präfixe • s = lokale, statische Variablen • m = Modul-spezifische Variablen und Konstanten • g = globale Variablen und Konstanten Andreas Rozek HyMeSys Software & Consulting

26. VBA für Excel 22.02.2010 Namenskonventionen (Fortsetzung) • Variablen-Arten für Excel-Blätter • wks Worksheet (Arbeitsblatt) sht • cht Chart (Diagramm) • frm Form (Dialog) • bas Basic (Standardmodul) mdl • cls Class (Klassenmodul) • xl4 Excel4 (Makroblatt) • im Falle einer Liste wird der Art ein „s“ angehängt Andreas Rozek HyMeSys Software & Consulting

27. VBA für Excel 22.02.2010 Namenskonventionen (Fortsetzung) • Variablen-Arten für die verschiedenen Datentypen • bln Boolean • sng Single • cur Currency • str String • dat Date • typ Type (Benutzerdefiniert) • dbl Double • var Variant • int Integer • lng Long • obj Object Andreas Rozek HyMeSys Software & Consulting

28. VBA für Excel 22.02.2010 Namenskonventionen (Fortsetzung) • Variablen-Arten für die verschiedenen Steuerelemente • lbl Label • mpg MultiPage • txt Textbox • spn SpinButton • cbo Combobox • scr Scrollbar • lst Listbox • img Image • chk Checkbox • ref RefEdit • opt OptionButton • trv TreeView • tgl ToggleButton • lsv ListView • cmd CommandButton • cal Calendar • tab TabStrip Andreas Rozek HyMeSys Software & Consulting

29. VBA für Excel 22.02.2010 Deklaration von Variablen • implizit (durch Verwendung in einer Zuweisung)varValue = „implizit deklarierte Variable“ strValue$ = „implizit deklarierte String-Variable“ • explizit (irgendwo, aber vor der ersten Verwendung) dim varValue dim strValue as string dim|public|static name[as type] [,name[as type]] • Verbieten impliziter Deklarationen option explicit Andreas Rozek HyMeSys Software & Consulting

30. VBA für Excel 22.02.2010 Deklaration von Datenfeldern (Arrays) • stets explizit dim varArray() dim intArray(4) as Integer dim strArray(-3 to 3) as String dim|public|static name (n | n to m[,...])[as type][,...] • bis zu 60(!) Dimensionen möglich • Änderung des impliziten Start-Index option base 1 • Änderung der Feldgröße zur Laufzeit redim varArray(3) redim preserve intArray(15) Andreas Rozek HyMeSys Software & Consulting

31. VBA für Excel 22.02.2010 Gültigkeitsbereich von Variablen und Konstanten • Deklaration auf Modulebene (public) • innerhalb des gesamten Modules sichtbar • Variablenwert bleibt erhalten • Deklaration auf Prozedurebene (dim) • nur innerhalb der Prozedur sichtbar • auf Modulebene deklarierte Variablen dürfen überdeckt werden • statische Variablen behalten ihren Wert Andreas Rozek HyMeSys Software & Consulting

32. VBA für Excel 22.02.2010 Literale • Zahlen 1234 1234e-56 • Zeichenketten „dies ist ein Text“ • Datum und Uhrzeit #21/07/2008 9:15:35# Konstanten const conValue = „Zeichenkette“ const conValue = 1234 const conValue = 1234e-56 const conValue = #21/07/2008 9:15:35# const conValue as String = „Zeichenkette“ const conValue as Integer = 1234 const name[as type] = value Andreas Rozek HyMeSys Software & Consulting

33. VBA für Excel 22.02.2010 Arithmetische Operatoren • Potenzierung ^ a ^ b • Grundrechenarten + - * / a + b • Ganzzahldivision \ a \ b • Modulo-Division mod a mod b • Negation - -a • Potenzierung negativer Zahlen nur für ganzzahlige Exponenten • Ganzzahldivision wandelt Argumente in Byte/Integer/Long • Modulo-Division liefert Divisionsrest Andreas Rozek HyMeSys Software & Consulting

34. VBA für Excel 22.02.2010 Vergleichsoperatoren • Vergleich < <= >= >a < b • Gleichheit = a = b • Ungleichheit <>a <> b • Referenzvergleich is a is b • Ähnlichkeit like a like pattern • Zeichenkettenvergleich vergleicht ASCII-Codes • like-Operator erlaubt Platzhalter (? *) im Vergleichsmuster Andreas Rozek HyMeSys Software & Consulting

35. VBA für Excel 22.02.2010 Logische Operatoren ab: 00 10 01 11 • Konjunktion and a and b 0 0 0 1 • Äquivalenz eqva eqv b 1 0 0 1 • Implikation imp a imp b 1 0 1 1 • Negation not not a • Disjunktion or a or b0 1 1 1 • Antivalenz xor a xor b0 1 1 0 Andreas Rozek HyMeSys Software & Consulting

36. VBA für Excel 22.02.2010 Verkettungsoperatoren • Verkettung & a & b • Verkettung +a + b Operator-Hierarchie • ^ • - • * / • \ • mod • + - • & • = <> < > <= >= is like • not and or xor eqv imp Andreas Rozek HyMeSys Software & Consulting

37. VBA für Excel 22.02.2010 Fallunterscheidungen • if ... then ... else if condition then [...] [else [...]] if condition then ... [elseif condition then ...] [else ...]end if • verschachtelbar Andreas Rozek HyMeSys Software & Consulting

38. VBA für Excel 22.02.2010 Fallunterscheidungen (Fortsetzung) • select case select case expression [case expression [,...] ...] [case ntom [,...] ...] [case is < | <= | > | >= | = | <>expression ...] [case else ...]end select • kein break erforderlich! Andreas Rozek HyMeSys Software & Consulting

39. VBA für Excel 22.02.2010 Schleifen • for ... next for counter = first to last [step delta] ... [exit for]next [counter] • Zählvariable außerhalb der Schleife deklarieren • Zählvariable innerhalb der Schleife nicht verändern! • Vorsicht mit Zählvariablen vom Typ single oder double Andreas Rozek HyMeSys Software & Consulting

40. VBA für Excel 22.02.2010 Schleifen (Fortsetzung) • for each for each element in group ... [exit for]next [element] • „Gruppen“ können Arrays oder Collections sein • auch mehrdimensionale Arrays zulässig (niedrige Indices werden zuerst durchlaufen) Andreas Rozek HyMeSys Software & Consulting

41. VBA für Excel 22.02.2010 Schleifen (Fortsetzung) • do do [while | until condition] ... [exit do]loop do ... [exit do]loop [while | until condition] • auch Endlosschleifen möglich Andreas Rozek HyMeSys Software & Consulting

42. VBA für Excel 22.02.2010 Schleifen (Fortsetzung) • while while condition ...wend • bitte nicht mehr verwenden („deprecated“) • kein Verlassen der Schleife möglich Andreas Rozek HyMeSys Software & Consulting

43. VBA für Excel 22.02.2010 Goto und Sprungmarken • goto gotoline | label • Sprungmarken number label: • alphanumerische Sprungmarken: • erstes Zeichen muß ein Buchstabe sein • danach können Buchstaben oder Ziffern folgen • für on error goto von Bedeutung Andreas Rozek HyMeSys Software & Consulting

44. VBA für Excel 22.02.2010 Prozeduren: Subroutinen und Funktionen • Subroutinen [private | public][static] sub name [(params)] ... [exit sub]end sub • Parameterlisten [optional][byVal | byRef][ParamArray] name[()][as type][=def] im Normalfall [byVal | byRef] name[()][as type] static läßt alle inneren Variablen einen Prozedur-Aufruf überdauern Andreas Rozek HyMeSys Software & Consulting

45. VBA für Excel 22.02.2010 Prozeduren (Fortsetzung) • Funktionen [private | public][static] function name [(params)][as type] ... [exit function] ... name= expressionend function • Sichtbarkeit von Prozeduren • public von allen Modulen aller Projekte aus aufrufbar • private nur aus dem eigenen Modul aus aufrufbar (static hat nichts mit der Sichtbarkeit zu tun) Andreas Rozek HyMeSys Software & Consulting

46. VBA für Excel 22.02.2010 Aufruf von Prozeduren • Subroutinen[call] name [(] [argument [, argument]] [)] • Funktionen ... = name ([argument [, argument]]) Übergabe von Argumenten • byValue für Variablen und Ausdrücke, Übergabe einer Kopie • byRef nur für Variablen, Übergabe einer Referenz Andreas Rozek HyMeSys Software & Consulting

47. VBA für Excel 22.02.2010 Konvertierungsfunktionen • CBool (expression) → Boolean • CByte (expression) → Byte • CCur (expression) → Currency • CDate (expression) → Date • CDbl (expression) → Double • CDec (expression) → Decimal • CInt (expression) → Integer • CLng (expression) → Long • CSng (expression) → Single • CVar (expression) → Variant • CStr (expression) → String Andreas Rozek HyMeSys Software & Consulting

48. VBA für Excel 22.02.2010 Konvertierungsfunktionen (Fortsetzung) • Val (string-expression) → Zahl • Str (number-expression) → String • Hex (number-expression) → Hexadezimal-Darstellung • Oct (number-expression) → Oktal-Darstellung • Asc (string-expression) → Zeichencode des ersten Zeichens • Chr (byte-expression) → Zeichen mit ggb. Zeichencode Andreas Rozek HyMeSys Software & Consulting

49. VBA für Excel 22.02.2010 Datentypen (o.ä.) überprüfen oder anzeigen • IsArray (variable) ist Variable ein Feld? • IsDate (expression) ist Ausdruck vom Typ „Date“? • IsEmpty (expression) ist Ausdruck initialisiert? • IsError (expression) enthält Ausdruck ein Fehler-Objekt? • IsMissing (expression) ist optionales Argument vorhanden? • IsNull (expression) enthält Ausdruck nur Null-Werte? • IsNumeric (expression) ist Ausdruck numerisch? • IsObject (variable) enthält Variable eine Objekt-Referenz? • TypeName (expression) liefert den Datentyp des Ausdruckes Andreas Rozek HyMeSys Software & Consulting

50. VBA für Excel 22.02.2010 Klassen • Instanzieren ja, Vererbung nein • Anlegen und Verwalten über den Code-Editor • in eigenem Klassenmodul • nur Instanzen-Methoden und -Eigenschaften • static bedeutet: alle Prozedur-internen Variablen überdauern einen Aufruf (erspart viele einzelne "static"s) • Selbst-Referenz me Andreas Rozek HyMeSys Software & Consulting