VBA für Excel

1. VBA für Excel 24.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 24.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 24.02.2010 VBA für Excel Fragen zum gestrigen Tag? Andreas Rozek HyMeSys Software & Consulting

4. VBA für Excel 24.02.2010 Lernziele erreicht? Idealerweise sollten Sie seit dem gestrigen Tag • wissen, daß Excel ein Objektmodell anbietet; • mit den Excel-Objekten umgehen können; • das Objektmodell mithilfe von Objektkatalog und eingebau- ter Hilfe inspizieren können; • erste Automatisierungsmakros programmieren können. Andreas Rozek HyMeSys Software & Consulting

5. VBA für Excel 24.02.2010 Lernziele des heutigen Tages Idealerweise sollten Sie am Ende des heutigen Tages • 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

6. VBA für Excel 24.02.2010 VBA für Excel Teil III: Ereignis-gesteuerte Programmierung Andreas Rozek HyMeSys Software & Consulting

7. VBA für Excel 24.02.2010 Ereignis-gesteuerte Programmierung • es werden laufend „Ereignisse“ generiert, z.B. • Mausbewegungen • Tastendrücke • Systemereignisse (CD/SD einlegen, USB-Gerät wechseln) • abgeleitete Ereignisse (grafische Benutzeroberfläche) • Ereignisse werden in Warteschlange (event queue) abgelegt • und der Reihe nach bearbeitet • Ereignisbehandlungsroutinen (event handler) müssen/sollten zügig terminieren, da sonst u.U. das System blockiert • unter VBA: Abbruch mit “Esc” bzw. “Alt-Break” Andreas Rozek HyMeSys Software & Consulting

8. VBA für Excel 24.02.2010 Ereignis-gesteuerte Programmierung (Fortsetzung) z.B. Mausklicks Andreas Rozek HyMeSys Software & Consulting

9. VBA für Excel 24.02.2010 Ereignis-gesteuerte Programmierung (Fortsetzung) Ereignis der Warte-schlange entnehmen Excel-internbearbeiten Benutzer-definiertbearbeiten Während einer Ereignis-behandlung ist Excel blockiert! Andreas Rozek HyMeSys Software & Consulting

10. VBA für Excel 24.02.2010 Quellen für Ereignisse in Excel • Excel-eigene Objekte • Application • Workbook • Worksheet • (Chart) • QueryTable • Steuerelemente • Excel-interne Steuerelemente • ActiveX Controls Andreas Rozek HyMeSys Software & Consulting

11. VBA für Excel 24.02.2010 Welche Ereignisse erzeugt ein bestimmtes Objekt? • Nutzen Sie den Code Editor und die Hilfe Andreas Rozek HyMeSys Software & Consulting

12. VBA für Excel 24.02.2010 Wie schreibt man eine Ereignisroutine? • Nutzen Sie den Code Editor der automatisch erzeugte „Rumpf“ enthält ggfs. eine Referenz auf die auslösende Komponente Andreas Rozek HyMeSys Software & Consulting

13. VBA für Excel 24.02.2010 Interessante Ereignisse des Application-Objektes • WorkbookBeforeSave vor dem Speichern einer Mappe • WorkbookBeforePrint vor dem Ausdrucken einer Mappe • WorkbookBeforeClose vor dem Schließen einer Mappe • SheetFollowHyperlink beim Anklicken eines Hyperlinks • SheetChange nach Änderung eines Blattes Andreas Rozek HyMeSys Software & Consulting

14. VBA für Excel 24.02.2010 Interessante Ereignisse des Workbook-Objektes • Open nach dem Öffnen • Activate nach dem Aktivieren • BeforeSave vor dem Speichern • BeforePrint vor dem Ausdrucken • BeforeClose vor dem Schließen • SheetFollowHyperlink beim Anklicken eines Hyperlinks • NewSheet nach Anlegen eines Blattes • SheetChange nach Änderung eines Blattes Andreas Rozek HyMeSys Software & Consulting

15. VBA für Excel 24.02.2010 Interessante Ereignisse des Worksheet-Objektes • Activate nach dem Aktivieren • Deactivate nach Deaktivierung • Calculate nach Neuberechnungen • Change nach Änderungen • FollowHyperlink nach Anklicken eines Hyperlinks • SelectionChange nach Änderung der Selektion Andreas Rozek HyMeSys Software & Consulting

16. VBA für Excel 24.02.2010 Interessante Ereignisse des QueryTable-Objektes • BeforeRefresh vor Aktualisierung einer Abfrage • AfterRefresh nach Aktualisierung einer Abfrage Andreas Rozek HyMeSys Software & Consulting

17. VBA für Excel 24.02.2010 VBA für Excel Teil III: Steuerelemente & Eingabeformulare Andreas Rozek HyMeSys Software & Consulting

18. VBA für Excel 24.02.2010 Zwei Varianten von Steuerelementen (Controls) • Formular-Steuerelemente • kleine, nicht erweiterbare Auswahl geringer Funktionalität • in Excel integriert, dadurch stets vorhanden • vermutlich Plattform-unabhängig (bitte stets vorher testen) • keine Unterscheidung Entwurfsmodus<->Arbeitsmodus • ActiveX-Steuerelemente • beliebig erweiterbare Auswahl (Drittanbieter) • nicht nur für Excel, für alle (ActiveX-fähigen) Programme • sehr vielseitig konfigurierbar, viele mögliche Events Andreas Rozek HyMeSys Software & Consulting

19. VBA für Excel 24.02.2010 Formular-Steuerelemente (Überblick) • Bezeichnungen (Label) • Schaltflächen (Button) • Drehfelder (SpinBox) • Kontrollkästchen (Checkbox) • Optionsfelder (Radiobutton) • Gruppenfelder (Group) • Listenfelder (Listbox) • Kombinationsfelder (Combobox) • Rollbalken (Scrollbar) Andreas Rozek HyMeSys Software & Consulting

20. VBA für Excel 24.02.2010 Formular-Steuerelemente (allg. Arbeitsschritte) • Anlegen eines Steuerelementes • Steuerelement im Menü wählen • mit linker Maustaste auf dem Tabellenblatt Umriß zeichnen • im Falle einer Schaltfläche: Makro wählen • Bearbeiten eines Steuerelementes • mit rechter Maustaste selektieren, danach • z.B. Verschieben oder Größe ändern • Text bearbeiten • Makro zuweisen • Steuerelement formatieren Andreas Rozek HyMeSys Software & Consulting

21. VBA für Excel 24.02.2010 Formular-Steuerelemente: Schaltflächen • rufen beim Anklicken das zugewiesene Makro auf • Änderung des Erscheinungs- bildes mittels “Steuerelement formatieren...” Andreas Rozek HyMeSys Software & Consulting

22. VBA für Excel 24.02.2010 Formular-Steuerelemente: Drehfelder • erlauben die Auswahl aus einem Zahlenbereich • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) Andreas Rozek HyMeSys Software & Consulting

23. VBA für Excel 24.02.2010 Formular-Steuerelemente: Rollbalken • erlauben die Auswahl aus einem Zahlenbereich • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) Andreas Rozek HyMeSys Software & Consulting

24. VBA für Excel 24.02.2010 Formular-Steuerelemente: Kontrollkästchen • erlauben das An- und Abwählen • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) Andreas Rozek HyMeSys Software & Consulting

25. VBA für Excel 24.02.2010 Formular-Steuerelemente: Optionsfelder • erlauben das An- und Abwählen eines von mehreren Werten • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) • ggfs. gruppieren! Andreas Rozek HyMeSys Software & Consulting

26. VBA für Excel 24.02.2010 Formular-Steuerelemente: Gruppenfelder • dienen dem “Gruppieren“ anderer Steuerelemente • entweder aus optischen Gründen oder • damit Optionsfelder wissen, daß Sie zu- sammengehören! Andreas Rozek HyMeSys Software & Consulting

27. VBA für Excel 24.02.2010 Formular-Steuerelemente: Kombinationsfelder • erlauben die Auswahl aus einer Werteliste • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) Andreas Rozek HyMeSys Software & Consulting

28. VBA für Excel 24.02.2010 Formular-Steuerelemente: Listenfelder • erlauben die Auswahl eines/(mehrerer) Werte aus einer Liste • rufen bei Wertänderung das zugewiesene Makro auf • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!) Andreas Rozek HyMeSys Software & Consulting

29. VBA für Excel 24.02.2010 ActiveX-Steuerelemente (Überblick) • Bezeichnungen (Label) • Schaltflächen (CommandButton) • Umschaltflächen (ToggleButton) • Drehfelder (SpinButton) • Texteingabefelder (Textbox) ein- und mehrzeilig • Kontrollkästchen (Checkbox) • Optionsfelder (Radiobutton) • Listenfelder (Listbox) • Kombinationsfelder (Combobox) • Grafikanzeigen (Image) • Rollbalken (Scrollbar)und beliebig mehr... Andreas Rozek HyMeSys Software & Consulting

30. VBA für Excel 24.02.2010 ActiveX-Steuerelemente (allg. Arbeitsschritte) • Anlegen eines oder mehrerer Steuerelemente • Entwurfsmodus wählen(!) • Steuerelement im Menü wählen • mit linker Maustaste auf dem Tabellenblatt Umriß zeichnen • evtl. weitere Steuerelemente anlegen • Bearbeiten eines Steuerelementes • z.B. mittels Doppelklick in den Code-Editor wechseln • im Eigenschaftenfenster Änderungen vornehmen • im Codefenster ggfs. Steuerelement wählen • gewünschtes Ereignis wählen und Routine schreiben • Verwenden eines Steuerelementes • Entwurfsmodus abwählen(!) Andreas Rozek HyMeSys Software & Consulting

31. VBA für Excel 24.02.2010 ActiveX-Steuerelemente in Excel • besitzen ein paar zusätzliche Eigenschaften, u.a. • LinkedCell Zelle, die aktuellen Wert aufnimmt • ListFillRange Zellen, die Listeninhalte vorgeben • manche Excel-Methoden sind in Ereignis-Routinen nicht verfügbar: Private Sub CommandButton1.Click Range("a1:a10").Sort Key1:=Range("a1") End Sub Abhilfe: je nach Bedarf Tabellenzelle oder ActiveX-Steuer- element explizit aktivieren: Private Sub CommandButton1.Click Range("a1").Activate Range("a1:a10").Sort Key1:=Range("a1") CommandButton1.Activate End Sub Andreas Rozek HyMeSys Software & Consulting

32. VBA für Excel 24.02.2010 ActiveX-Steuerelemente in Excel • ActiveX-Steuerelemente per Programm hinzufügen ActiveSheet.OLEObjects.Add "Forms.CommandButton.1", _ Left:=10, Top:=10, Height:=20, Width:=100 • ActiveX-Steuerelemente sind Bestandteil des Tabellenblattes: ActiveSheet.CommandButton1.Caption = "Click Me” und zugleich OLE-Objekte ActiveSheet.OLEObjects(“CommandButton1”).Caption = "Click Me” Andreas Rozek HyMeSys Software & Consulting

33. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Labels • dienen der (statischen) Anzeige von Texten (und Bildern), können aber auch auf Mausklicks reagieren • wichtige Eigenschaften • Name • Caption, evtl. Picture • Autosize • Visible, evtl. BackStyle Andreas Rozek HyMeSys Software & Consulting

34. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Befehlsschaltflächen • dienen dem Anstoßen einer Aktion • wichtige Eigenschaften: • Name • Caption, evtl. Picture • Enabled Andreas Rozek HyMeSys Software & Consulting

35. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Umschaltflächen • springen nach dem Anklicken nicht sofort wieder zurück • wichtige Eigenschaften: • Name • Caption, evtl. Picture • Enabled • Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

36. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Drehfelder • Auswahl eines Wertes aus einem numerischen Bereich • wichtige Eigenschaften: • Name • Orientation • Enabled • Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

37. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Texteingabefelder • für ein- oder mehrzeilige Text- eingabe – manchmal auch Anzeige • wichtige Eigenschaften: • Name • MultiLine, Scrollbars • Enabled, TextAlign, WordWrap • Value oder Text, LinkedCell • MaxLength, PasswordChar Andreas Rozek HyMeSys Software & Consulting

38. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Kontrollkästchen • für einfache Markierungen • wichtige Eigenschaften: • Name • Caption, TextAlign, evtl. WordWrap • Enabled, evtl. Picture, Alignment • evtl. TripleState • Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

39. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Optionsfelder • für Auswahl aus einer statischen Menge, keine Mehrfachauswahl • wichtige Eigenschaften: wie Kontrollkästchen, zusätzlich • GroupName Andreas Rozek HyMeSys Software & Consulting

40. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Listenfelder • Anzeige/Auswahl aus einer Liste evtl. Mehrfachauswahl • wichtige Eigenschaften: • Name • ListStyle, MultiSelect, ListFillRange • Enabled, IntegralHeight • Text oder Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

41. VBA für Excel 24.02.2010 Umgang mit Listenfeldern • Füllen eines Listenfeldes • aus Tabelle (ListFillRange) • per Programm: ListBox.AddItem “neuer Eintrag” • Ermitteln der selektierten Werte • bei Einfachselektion: ListBox.Value • bei Mehrfachselektion: for i = 0 to ListBox.ListCount – 1 if ListBox.Selected(i) then ... next Andreas Rozek HyMeSys Software & Consulting

42. VBA für Excel 24.02.2010 Umgang mit Listenfeldern (Fortsetzung) • Löschen von Elementen eines ListenfeldesListBox.RemoveItem (index)ListBox.Clear • Anzahl der Elemente eines ListenfeldesListBox.Count • Zugriff auf einzelne ListenelementeListBox.List (index) Andreas Rozek HyMeSys Software & Consulting

43. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Kombinationsfelder • Aufklappmenü (ggfs. editierbar!), Einfachauswahl • wichtige Eigenschaften: • Name • Style(!) • ListFillRange • Text oder Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

44. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Grafiken • Anzeige einer (einfachen) Grafik • wichtige Eigenschaften: • Name • Picture, PictureAlignment, • PictureSizeMode, PictureTiling • SpecialEffect Andreas Rozek HyMeSys Software & Consulting

45. VBA für Excel 24.02.2010 ActiveX-Steuerelemente: Rollbalken • für (kontinuierliche) Auswahl aus einem Zahlenbereich • wichtige Eigenschaften: • Name • Orientation • Min/Max • SmallChange/LargeChange • Value, LinkedCell Andreas Rozek HyMeSys Software & Consulting

46. VBA für Excel 24.02.2010 VBA für Excel Übungen Andreas Rozek HyMeSys Software & Consulting

47. VBA für Excel 24.02.2010 Übungen • programmieren Sie eine Endlosschleife, starten Sie sie und brechen Sie sie abNutzen Sie für die nächsten Übungen Formular-Steuerelemente • erstellen Sie ein Drehfeld für Monatsnamen verknüpfen Sie das Steuerelement dazu mit einem (unsichtba- ren) Tabellenfeld und reagieren Sie auf Benutzereingaben, in- dem Sie das Feld auslesen und den zugehörigen Monatsnamen in einem anderen Feld anzeigen • erstellen Sie einen Rollbalken (mit passendem Wertebereich) und verwenden Sie den vom Benutzer gewählten Wert als Palettenindex für die Hintergrundfarbe Andreas Rozek HyMeSys Software & Consulting

48. VBA für Excel 24.02.2010 Übungen (Fortsetzung) • erstellen Sie ein Kontrollkästchen und lesen Sie den Wert aus • erstellen Sie mindestens 2 Optionsfelder, verknüpfen Sie diese mit demselben Tabellenfeld und lesen Sie den Wert aus • erstellen Sie ein Ausklappmenü für die Tage einer Woche • erstellen Sie eine (Auswahl-)Liste für die Monate eines Jahres Andreas Rozek HyMeSys Software & Consulting

49. VBA für Excel 24.02.2010 Übungen (Fortsetzung) • Ab sofort sollten Sie ActiveX-Steuerelemente verwenden - achtenSie auf den Entwurfsmodus! • erstellen Sie einen Label und weisen Sie ihm per Programm einen Wert zu: schreiben Sie zu diesem Zweck eine Ereignis- behandlungsroutine, die bei jedem Klick auf den Label einen Zähler erhöht und diesen im Label selbst anzeigt • erstellen Sie ein Drehfeld für Monatsnamen: lesen Sie in der Ereignisbehandlungsroutine den aktuellen Wert aus und zeigen Sie den zugehörigen Monatsnamen an Andreas Rozek HyMeSys Software & Consulting

50. VBA für Excel 24.02.2010 Übungen (Fortsetzung) • erstellen Sie einen Rollbalken (mit passendem Wertebereich) und verwenden Sie den vom Benutzer gewählten Wert als Palettenindex für die Hintergrundfarbe • erstellen Sie eine Umschaltfläche und setzen Sie diese nach zwei Sekunden per Programm zurück. Das Kommando für die Wartezeit lautet: Application.Wait now() + TimeValue(„0:0:2“) Andreas Rozek HyMeSys Software & Consulting