VBA für Excel

1. VBA für Excel 25.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 25.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

3. VBA für Excel 25.02.2010 VBA für Excel Fragen zum gestrigen Tag? Andreas Rozek HyMeSys Software & Consulting

4. VBA für Excel 25.02.2010 Lernziele erreicht? Idealerweise sollten Sie seit dem gestrigen Tag • wissen, was “Ereignis-gesteuerte Programmierung” ist; • den Unterschied zwischen Formular- und ActiveX- Steuerelementen kennen; • Formular- und ActiveX-Steuerelemente einsetzen und programmieren können. Andreas Rozek HyMeSys Software & Consulting

5. VBA für Excel 25.02.2010 Lernziele des heutigen Tages Idealerweise sollten Sie am Ende des heutigen Tages • wissen, was ein “Eingabe-Formular” (UserForm) ist; • UserForms erstellen und benutzen können; • auch nicht-triviale Aufgaben wohldurchdacht lösen können; Andreas Rozek HyMeSys Software & Consulting

6. VBA für Excel 25.02.2010 VBA für Excel Teil IV: Eingabeformulare Andreas Rozek HyMeSys Software & Consulting

7. VBA für Excel 25.02.2010 Eingabeformulare („UserForms“) • sind eigentlich eigenständige (Eingabe-)Dialoge Andreas Rozek HyMeSys Software & Consulting

8. VBA für Excel 25.02.2010 Eingabeformulare (Fortsetzung) • können “modal” und “nicht-modal” angezeigt werden • modal Excel ist während der Eingabe blockiert • nicht-modal Benutzer kann Excel weiterhin bedienen • im nicht-modalen Fall: Vorsicht z.B. mit “ActiveSheet”! • können vom Code-Fenster aus getestet werden (F5) Andreas Rozek HyMeSys Software & Consulting

9. VBA für Excel 25.02.2010 Erstellen eines Eingabeformulars • im Code-Editor: • neues Formular anlegen • Eigenschaften anpassen • wichtige Eigenschaften • Name • Caption • evtl. Scrollbars, KeepScrollbarsVisible • showModal, StartUpPosition Andreas Rozek HyMeSys Software & Consulting

10. VBA für Excel 25.02.2010 Erstellen eines Eingabeformulars (Fortsetzung) • im Code-Editor auf dem Formular selbst: • Steuerelemente anlegen und konfigurieren • wieder im Code-Editor: Ereignisbehandlungsroutinen schreiben • für die Vorbereitung des Formulares • evtl. für die Steuerelemente des Formulares selbst • für “Ok” • Formular auslesen, evtl. Fehler melden • ansonsten UserForm.hide • für “Cancel” • UserForm.hide • evtl. Start-Makro schreiben und ... • ausprobieren! Andreas Rozek HyMeSys Software & Consulting

11. VBA für Excel 25.02.2010 UserForm-Steuerelemente (Überblick) • Bezeichnungen (Label) • Schaltflächen (CommandButton) • Umschaltflächen (ToggleButton) • Drehfelder (SpinButton) • Kontrollkästchen (Checkbox) • Optionsfelder (Radiobutton) • Rahmen (Frame) • Listenfelder (Listbox) • Kombinationsfelder (Combobox) • Rollbalken (Scrollbar) • Tabulatorstreifen (TabStrip) • TabulatorFelder (MultiPage) Andreas Rozek HyMeSys Software & Consulting

12. VBA für Excel 25.02.2010 UserForm-Steuerelemente (allg. Arbeitsschritte) • Anlegen eines Steuerelementes • aus der Toolbox in das Formular ziehen (praktisch!) oder • in der Toolbox anwählen und auf dem Formular mit der Maus einen Umriß zeichen • Bearbeiten eines Steuerelementes • mit linker Maustaste selektieren und im Eigenschaftenfenster Anpassungen vornehmen • mit linker Maustaste doppelt anklicken und im Code-Editor die Ereignisroutine schreiben Andreas Rozek HyMeSys Software & Consulting

13. VBA für Excel 25.02.2010 Achtung! • manche Steuerelemente besitzen andere Eigenschaften, wenn Sie in einem Eingabeformular verwendet werden: • Kombinationsfelder: RowSource • Listenfelder: RowSource • manche Steuerelemente sind nur in Eingabeformularen verfügbar: • Rahmen (Frame) • Tabulatorstreifen (TabStrip) • TabulatorFelder (MultiPage) Andreas Rozek HyMeSys Software & Consulting

14. VBA für Excel 25.02.2010 UserForm-Steuerelemente: Rahmen • dienen der (echten) Gruppierung von Steuerelementen • wichtige Eigenschaften • Name • Caption • evtl. Scrollbars, KeepScrollbarsVisible Andreas Rozek HyMeSys Software & Consulting

15. VBA für Excel 25.02.2010 UserForm-Steuerelemente: Rahmen • um z.B. Optionsfelder zu gruppieren: • Rahmen anlegen • Optionsfelder innerhalb des Rahmens anlegen oder • Optionsfelder anlegen • Rahmen anlegen • Optionsfelder in den Rahmen schiebenElemente im Rahmen werden mit dem Rahmen verschoben! Andreas Rozek HyMeSys Software & Consulting

16. VBA für Excel 25.02.2010 UserForm-Steuerelemente: MultiPages • dienen der seitenweise Anzeige von Steuerelementen • jede Seite kann im Entwurfs- formular einzeln angewählt werden! Konfiguriert werden die einzelnen Seiten! Andreas Rozek HyMeSys Software & Consulting

17. VBA für Excel 25.02.2010 UserForm-Steuerelemente: MultiPages • wichtige Eigenschaften: • Name • Caption • evtl. Scrollbars, KeepScrollbarsVisible • Elemente auf den einzelnen Seiten bleiben selektierbar – es werden aber immer nur die gerade sichtba- ren Elemente gezeigt! Andreas Rozek HyMeSys Software & Consulting

18. VBA für Excel 25.02.2010 “Lebenszyklus“ eines Eingabeformulares • (Erstellen) • Laden Load UserForm • Initialisieren private sub UserForm_Initialize() • Vorbereiten und Einblenden (und Benutzen) UserForm.Show [vbModeless] • Auslesen und Ausblenden UserForm.Hide • Aufräumen und private sub UserForm_Terminate() Entladen Unload UserForm Andreas Rozek HyMeSys Software & Consulting

19. VBA für Excel 25.02.2010 Ein konkretes Beispiel... Andreas Rozek HyMeSys Software & Consulting

20. VBA für Excel 25.02.2010 Ein konkretes Beispiel: Erstellen Andreas Rozek HyMeSys Software & Consulting

21. VBA für Excel 25.02.2010 Ein konkretes Beispiel: Vorbereiten und Einblenden Sub showDialog() Load BeispielFormular With BeispielFormular .SalutationChoice.Value = ... ... End With BeispielFormular.Show End Sub Andreas Rozek HyMeSys Software & Consulting

22. VBA für Excel 25.02.2010 Ein konkretes Beispiel: Auslesen und Ausblenden Private Sub OkButton_Click() ... (Eingaben überprüfen, Beispiel folgt) If (Trim(SalutationChoice.Value) = "") Then MsgBox "Bitte wählen Sie eine Anrede" Exit Sub End If ... (Eingaben auslesen und in Tabelle schreiben) BeispielFormular.Hide End Sub Andreas Rozek HyMeSys Software & Consulting

23. VBA für Excel 25.02.2010 VBA für Excel Teil IV: Programmiertips Andreas Rozek HyMeSys Software & Consulting

24. VBA für Excel 25.02.2010 Das Wasserfallmodell Pflichtenheft Analyse MockUp Entwurf Prototyp Implementierung Test Einsatz Andreas Rozek HyMeSys Software & Consulting

25. VBA für Excel 25.02.2010 Programmiertips • dies ist keine Vorlesung über • Entwurfstheorie • Systemmodellierung • Algorithmenentwurf • stattdessen erhalten Sie ein paar ganz pragmatische Tips zur Programmierung – bitte nicht Ihrem Professor zeigen! Andreas Rozek HyMeSys Software & Consulting

26. VBA für Excel 25.02.2010 Konkretes Beispiel: Zahlenmemory Andreas Rozek HyMeSys Software & Consulting

27. VBA für Excel 25.02.2010 Ereignis-orientierte Programmierung • Ereignisse lösen i.a. “Zustandsänderungen” aus (Zustand, Zustandsübergang) • formal korrekte Modellierung und Darstellung z.B. durch • Zustandsdiagramm • Petri-Netz • in der Praxis “privat”: zeichnen Sie einfach irgendwie ein Zustandsdiagramm - aber zeichnen Sie es! Andreas Rozek HyMeSys Software & Consulting

28. VBA für Excel 25.02.2010 „Irgendein“ Zustandsdiagramm Start :Reset Spiel initialisiert 1. Karte aufgedeckt weitere Felder verdeckt: 2. Karte aufgedeckt ungleich:zudecken gleich: alle Felder aufgedeckt: Ende Andreas Rozek HyMeSys Software & Consulting

29. VBA für Excel 25.02.2010 Programmablauf • formal korrekte Modellierung und Darstellung z.B. durch • Flußdiagramm o.ä. • (PseudoCode) • in der Praxis “privat”: zeichnen Sie einfach irgendein Flußdiagramm ohne gra- fischen Schnickschnack - aber zeichnen Sie es! Andreas Rozek HyMeSys Software & Consulting

30. VBA für Excel 25.02.2010 „Irgendein“ Flußdiagramm Memory-Feld füllen „Liste“ (String) aller Feldinhalte erstellen (je zweimal „0“ bis „9“) for Row = 1 to 4 for Column = 1 to 5 Inhalt Nr. rnd(1...len(„Liste“)) in Feld (r-1)*5+c eintragen Inhalt aus Liste entfernen Ende Andreas Rozek HyMeSys Software & Consulting

31. VBA für Excel 25.02.2010 Weitere Tips aus der Praxis • “Machen Sie es so einfach wie möglich - aber nicht einfacher” • KISS-Prinzip: “Keep It Simple - but not Stupid!” • “die Produktivität von Programmierern variiert um den Faktor 20” (und je länger es dauert, desto unwahrscheinlicher wird es, daß das Projekt abgeschlossen wird) ZEIT! Andreas Rozek HyMeSys Software & Consulting

32. VBA für Excel 25.02.2010 Weitere Tips aus der Praxis (Fortsetzung) • „Pareto-Prinzip“: „mit 20% des Aufwandes erreicht man 80% des Ergebnisses“ • keine unumstößliche Regel, wohl aber eine Beobachtung • niemals darauf verlassen, aber im Hinterkopf behalten: => MockUps und Prototypen • „Der frühe Wurm fängt den Fisch!“ Andreas Rozek HyMeSys Software & Consulting

33. VBA für Excel 25.02.2010 Weitere Tips aus der Praxis (Fortsetzung) • seien Sie pragmatisch – nicht perfektionistisch: • Sie können 1000-mal behaupten, daß Ihre Lösung verbesse- rungswürdig ist – wenn Sie denn eine haben • aber Sie dürfen kein einziges Mal behaupten, daß Sie die beste aller Lösungen liefern werden, wenn sie noch nicht vorzeigbar ist (setzen Sie Ihre eigenen Ziele nicht zu hoch) • => Vorabversionen – keine “Vaporware” (Duke Nukem) Andreas Rozek HyMeSys Software & Consulting

34. VBA für Excel 25.02.2010 Weitere Tips aus der Praxis (Fortsetzung) • planen Sie! Aber nicht sich zu Tode! • planen Sie gerade soweit, daß Sie denken, Sie können Ihre Lösung implementieren – es werden ohnehin unvorhergese- hene Probleme auftreten! • haben Sie keine Angst vor Kritik – es wird immer jemanden geben, dem Ihre Lösung nicht gefällt! • keine Angst vor O(n2) bzw. O(n3) bei kleinen n Andreas Rozek HyMeSys Software & Consulting

35. VBA für Excel 25.02.2010 VBA für Excel Übungen Andreas Rozek HyMeSys Software & Consulting

36. VBA für Excel 25.02.2010 Übungen • implementieren Sie das Beispielformular • erstellen Sie zunächst die Tabelle mit den Vorgaben für die Listen sowie den Zellen für die Benutzereingaben • legen Sie nun das Formular mit allen Steuerelementen an • den Inhalt für Datenschutzerklärung und AGBs lassen Sie sich von einem “Lorem Ipsum”-Generator erzeugen (dazu recher- chieren Sie im Web!) • erstellen Sie die Ereignisroutine für den “Cancel”-Button Andreas Rozek HyMeSys Software & Consulting

37. VBA für Excel 25.02.2010 Übungen (Fortsetzung) • erstellen Sie die Ereignisroutine für den “Ok”-Button • prüfen Sie zunächst die Vollständigkeit aller Eingaben im Fehlerfalle zeigen Sie eine MsgBox an und verlassen Sie die Ereignisroutine, ohne die Tabelle modifiziert zu haben • lesen Sie nun die verschiedenen Eingabefelder aus und schreiben Sie passende Werte in die Tabelle Achtung: fassen Sie alle selektierten Interessen in einer Zeichenkette zusammen (Komma-separiert) • am Ende blenden Sie den Dialog einfach wieder aus • testen Sie Ihr Formular aus dem Code-Editor heraus! Andreas Rozek HyMeSys Software & Consulting

38. VBA für Excel 25.02.2010 Übungen (Fortsetzung) • erstellen Sie nun die Ereignisroutine für den “Eingabe”-Button auf dem Tabellenblatt • “laden” Sie zunächst das Eingabeformular • füllen Sie nun das Formular mit den Vorgaben aus der Tabelle Achtung: die Tabellenzellen müssen dazu bereits gültige Werte enthalten (dürfen insbesondere nicht “leer” sein - “leer” nicht mit der leeren Zeichenkette verwechseln!). Verwenden Sie die Funktion split, um den Inhalt der Zelle mit den Adressen in ein Datenfeld umzuwandeln... Andreas Rozek HyMeSys Software & Consulting

39. VBA für Excel 25.02.2010 Übungen (Fortsetzung) • Fortsetzung zum Vorbelegen des Listenfeldes durchlaufen Sie alle Elemen- te des Listenfeldes und setzen ListBox.selected(i) = true für jedes i-te Element, dessen Text im Tabellenfeld vorhanden ist • testen Sie das Beispiel in seiner vollen Form Andreas Rozek HyMeSys Software & Consulting

40. VBA für Excel 25.02.2010 Übungen (Fortsetzung) • entwerfen und implementieren Sie ein “Zahlenmemory” • skizzieren Sie die Anforderungen an das Spiel (Anzeige des Spielfeldes, Bedienung (Aufdecken von Feldern) Erkennung des Spielzuges, Erkennung des Spielendes) • überlegen Sie sich eine Implementierung in Excel und VBA (so einfach wie möglich) Andreas Rozek HyMeSys Software & Consulting

41. VBA für Excel 25.02.2010 Übungen (Fortsetzung) • implementieren Sie das Spiel Schritt für Schritt • erstellen Sie das Tabellenfeld mit allen Bedien- und Anzeige- komponenten => MockUp! • implementieren Sie die Bedienung und die Ende-Erkennung • testen Sie Ihr Spiel – im Zweifelsfalle “mogeln” Sie, indem Sie sich die aufgedeckten Karten notieren • testen Sie auch auf Fehlbedienungen: was passiert, wenn aufgedeckte Karten erneut aufgedeckt werden? Wenn jemand versucht, im selben Zug zweimal dieselbe Karte aufzudecken? Andreas Rozek HyMeSys Software & Consulting