Programmieren in EXCEL mit VBA
Werbung
htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Programmieren in EXCEL mit VBA Einführung in das Programmieren mit VBA Inhaltsverzeichnis Einleitung ................................................................................................................... 2 Dein erstes VBA Programm... .................................................................................... 2 Definiere ein Steuerelement... ................................................................................ 2 Verbinde ein Programm mit dem Steuerelement... ................................................ 3 Führe das Programm aus... ..................................................................................... 4 Einstieg ins Programmieren mit Word ....................................................................... 5 Programmieren ohne Steuerelemente ......................................................................... 7 Das Objekt „Tabelle1“ ............................................................................................... 8 Der Name eines Objekts (Name of object) ............................................................ 9 Eigenschaften (Properties) des Objektes „Tabelle1“ ............................................. 9 Die Methoden eines Objekts ................................................................................ 12 Der Eingabeknopf – ein „Objekt im Objekt“ ....................................................... 12 Übungen mit mehreren Steuerelemente ............................................................... 14 Eine neue Eigenschaft für ein Objekt definieren.................................................. 14 Programmstrukturen ................................................................................................. 17 Verzweigungen ..................................................................................................... 17 Zählschleifen ........................................................................................................ 19 Gesteuerte Schleifen (Bedingter Schleifendurchlauf) .......................................... 21 Die For - Each Schleife ........................................................................................ 22 Wichtige Objekte in EXCEL.................................................................................... 23 Der Makro-Recorder ................................................................................................ 26 Fehler suchen und beheben ...................................................................................... 27 „Syntaxfehler“ und „Laufzeitfehler“ .................................................................... 27 „Breakpoints“ setzen und „Einzelschritte“ ausführen .......................................... 27 Das „Direktfenster“ .............................................................................................. 27 © Lothar Kerbl 10.6.2005 Seite 1 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Einleitung Dieses Skriptum setzt voraus, dass du mit den Grundlagen von EXCEL vertraut bist. Was bedeuten die Begriffe „Zeile“, „Spalte“, „Zelle“, „Bereich“, „Tabelle“, wie werden sie bezeichnet ? Wie gibt man Texte, Zahlen, Daten in verschiedenen Formaten ein ? Wie funktioniert das automatische Ausfüllen ? Wie gibt man „Formeln“ ein ? Wie kann man Zellen „benennen“, welchen Vorteil haben „benannte“ Zellen bzw. Bereiche ? Welche „Funktionen“ hast du in Formeln EXCEL Tabellen verwendet ? Was sind „Argumente“ einer Funktion ? Was ist eine „Bedingte Formel“ („Wenn-Formel“) Dein erstes VBA Programm... Definiere ein Steuerelement... Steuerelement in einem Tabellenblatt einbauen.... Bild 1. Vorbereitung zum Einfügen von Steuerelementen; beachte, dass auch die Symbolleiste „Visual Basic“ sichtbar ist. © Lothar Kerbl 10.6.2005 Seite 2 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 2. Visual Basic Steuerelemente Bild 3. Einfügen eines „Steuerknopfes“ (Command Button) Verbinde ein Programm mit dem Steuerelement... Bild 4. Anbinden von Programmcode an einen „Eingabeknopf“ (Option „Code anzeigen“ im Kontextmenu1)...... 1 Das Kontextmenü eines „sichtbaren Objekts“ wird durch „Rechtsclick“ angezeigt © Lothar Kerbl 10.6.2005 Seite 3 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 5. .....und schon hast du den Einstieg in die Welt des Programmierens geschafft ! Das Programm (die Folge von Anweisungen, die beim „Anclicken“ des Steuerelements ausgeführt werden) musst du selbst schreiben. Dein erstes Programm besteht aus einer „Anweisung“. Es ist die Anweisung „MsgBox“ mit einem zugehörigen Argument2. Die Anweisungen eines Programmes werden von einer Überschrift und einem Kennzeichen für das Programmende eingerahmt. Führe das Programm aus... Um dein Programm auszuführen, musst du wieder zum Tabellenblatt wechseln (nicht vergessen: in EXCEL musst du den „Entwurfsmodus“ verlassen ). Klicke auf das „Steuerobjekt“ CommandButton1 und ..... Bild 6. Gratulation, wenn es bei dir auch so aussieht – du hast gerade dein erstes selbst geschriebenes Programm ausgeführt ! 2 Den Begriff „Argument“ kennst du bereits von den EXCEL-Formeln. © Lothar Kerbl 10.6.2005 Seite 4 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Einstieg ins Programmieren mit Word Deinen Einstieg in die Welt des Programmierens kannst du auch in Word versuchen. Du könntest in „Word“ die Steuerelemente auch direkt auf dein „Blatt“ platzieren, (Menupunkt „Ansicht“/“Symbolleisten“) wir wollen aber „als Unterlage“ ein Fenster (Form) erstellen. Du rufst Word auf und wechselst in die „VBA-Programmierumgebung:“ Bild 7. Aufruf des VB-Editors aus Word Hinweis: Du kannst auch in EXCEL in derselben Weise ein Fenster als Grundlage für deine Steuerelemente erstellen ! Bild 8. Erstellen eines Eingabefensters im Entwurfsmodus (in der VBA-Entwicklungsumgebung) Das Eingabefenster kannst du mit Steuerelementen bestücken. Als erstes Steuerelement platzierst du eine Befehlsschaltfläche („Eingabeknopf“) auf deinem Eingabefenster: © Lothar Kerbl 10.6.2005 Seite 5 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 9. Das Eingabefenster kann mit Steuerelementen „bestückt“ werden ... Bild 10. ... ein „Doppelklick“ (bzw. „Code anzeigen“) und du kannst die „Click-Methode“ deines Steuerelementes programmieren Wenn dein Eingabefenster fertig ist (alle vorgesehenen Steuerelemente sind platziert, die zugeordneten Methoden sind programmiert), dann kannst du dein Programm ausführen: Bild 11. Du kannst dein „Programm“ durch „Anclicken“ der Pfeiltaste starten... © Lothar Kerbl 10.6.2005 Seite 6 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 12. ... und das sollte so das aussehen (das „Hallo Fenster“ wird natürlich erst geöffnet, nachdem du durch „AncClicken“ des „CommandButton1“ die zugehörige „Click-Methode“ aufgerufen hast.) Wahrscheinlich fällt dir auf, dass dein selbsterstelltes Fenster „nicht ganz“ das gewohnte „Windows Feeling“ vermittelt: Du kannst nämlich nicht einfach durch Anklicken mit der Maus auf das dahinterliegende Textblatt umschalten und dort Text eingeben, solange das „UserForm1“ geöffnet ist. Das „UserForm1“ lässt nicht zu, dass es „beiseite“ geschoben und ein anderes Fenster (inklusive Desktop) „aktiviert“ wird. Dieses Verhalten kennst du schon von den Fenstern „MsgBox“. Fenster mit solchen Eigenschaften nennt man „modal“.3 (Eigenschaften „ShowModal = True“) Wenn du beim Entwurf von UserForm1 die Eigenschaft „ShowModal“ auf „False“ setzt, zeigt dein Fenster das „gewohnte Windows-Verhalten“. Programmieren ohne Steuerelemente Dein Programm kommt auch ohne sichtbare Steuerelemente aus. Die folgende Anleitung gilt für „Word“ und „EXCEL“. (Dargestellt ist die Programmierung in EXCEL) 3 Das Gegenstück dazu sind Fenster nach dem „MDI“-Standard (Multiple Document Interface) © Lothar Kerbl 10.6.2005 Seite 7 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 13. Ein „Modul“ ist ein Programmtext ohne Bindung an ein Steuerelement Bild 14. Erstelle den Programmtext, nachdem ein „Modul“ eingefügt wurde... Dieses Programm wird als „Makro“ bezeichnet und kann unter dem Namen „main“ aufgerufen werden (Menupunkt „Extras“/ „Makro“ / „Ausführen“) Das Objekt „Tabelle1“ Wir wollen nun Programme schreiben, die Aufgaben mit dem EXCEL Tabellenblatt erledigen. Wir werden dazu die Werte von Eigenschaften („Values of Properties“) von Objekten („objects“) verändern. Natürlich kannst du auch Aufgaben mit dem WORD-Text durch ein (von dir erstelltes) Programm ausführen. Word-Objekte werden in diesem Skriptum allerdings nicht beschrieben. Unabhängig davon, ob du VBA in einer „WORD-Umgebung“ oder in einer „EXCEL-Umgebung“ benutzt, kannst du viele Programme, die nicht auf die WORD- oder EXCEL-Objekte angewiesen sind, erstellen und dabei grundlegende Programmiererfahrung sammeln. © Lothar Kerbl 10.6.2005 Seite 8 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Der Name eines Objekts (Name of object) Das Programm, das du schreibst, muss irgendwie „mit dem EXCEL Tabellenblatt arbeiten“ können. Dazu musst du in der Lage sein, die „Objekte des Tabellenblattes“ zu bezeichnen. Dinge, mit denen Programme arbeiten, werden als „Objekte“ bezeichnet. Das angezeigte EXCEL-Tabellenblatt ist ein Objekt, es wird unter dem Namen „Tabelle1“ oder auch „ActiveSheet“ im Programmtext angesprochen. Eigenschaften (Properties) des Objektes „Tabelle1“ Das dargestellte EXCEL-Tabellenblatt hat eine Reihe von Eigenschaften. Diese Eigenschaften können in einem Programm verwendet (gelesen) oder neu eingestellt (beschrieben) werden. Objekte haben Eigenschaften („Properties“). Die Bezeichnung („Name of Propertiy4“) einer Eigenschaft wird durch Punktschreibweise nach dem Objektnamen angegeben. Die Eigenschaften eines Objektes können unterschiedliche Werte („Values of Property“)annehmen Beispiel: Zeige den Wert der Eigenschaft „StandardWidth“ des Objektes „Tabelle1“ an Private Sub CommandButton1_Click() MsgBox (Tabelle1.StandardWidth) End Sub Bild 15. Der aktuellen Wertes der Eigenschaft „StandardWidth“ des Objekts „Tabelle1“wird angezeigt, 4 Beachte, dass sowohl das Objekt selbst als auch seine Eigenschaften „Namen“ haben. © Lothar Kerbl 10.6.2005 Seite 9 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA wenn du das Programm durch Anclicken von „CommandButton1“ ausführst Wenn du das Programm schreibst, fällt dir sicher folgendes auf: Nach Eingabe des Punktes hinter „Tabelle1“ wird dir ein „Angebot“ für die weitere Texteingabe gemacht. Die möglichen Eigenschaften werden angezeigt Bild Bei der Erstellung von Programmen werden die Namen der Objekteigenschaften angezeigt Der Editor5 erkennt nämlich, dass „Tabelle1“ der Name eines Objektes ist, und dass du wahrscheinlich eine der Eigenschaften dieses Objektes in deinem Programm verwenden willst. Wie du an der Auswahl erkennst, gibt es sehr, sehr viele Eigenschaften6 des Tabellenblattes „Tabelle1“. Diese Eigenschaften haben mitunter recht eigenartige Namen. Kein Programmierer kann sich alle Eigenschaftsbezeichnungen von allen möglichen Objekten merken, und so stellt diese Auswahlliste eine unentbehrliche Hilfe bei der Programmerstellung dar. Beispiel: Ändere den Wert der Eigenschaft „StandardWidth“ des Objektes „Tabelle1“ entsprechend einer Eingabe über die Tastatur. Private Sub CommandButton1_Click() Tabelle1.StandardWidth = InputBox("Gib die Zellenbreite ein!") End Sub .. oder7 Private Sub CommandButton1_Click() StandardWidth = InputBox("Gib die Zellenbreite ein!") End Sub In diesem Beispiel hast du die Funktion InputBox verwendet. Damit kannst du einen Wert von der Tastatur einlesen und diesen Wert der Eigenschaft, die auf der linken Seite des „=“ Zeichens steht zuweisen. 5 Editor: Das ist das Textprogramm, mit dem du den Programmtext (Source Code) erstellst Eigenschaften („Properties“) sind durch das Symbol „ “ gekennzeichnet. Der „fliegende grüne Ziegel“ ( ) kennzeichnet eine „Methode“ des Objekts. „Methoden“ des Objekts werden im nächsten Kapitel behandelt. 7 Wenn als Zielobjekt „das eigene Objekt“ gemeint ist, kann dessen Name (hier „Tabelle1“) auch weggelassen werden. Den Namen des „eigenen“ Objekts, siehst du in der Kopfzeile im Editorfenster. 6 © Lothar Kerbl 10.6.2005 Seite 10 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Beachte, dass das „=“ Zeichen in diesem Fall nicht eine Gleichheit anzeigt sondern „eine Zuweisung („Assignment“) bewirkt“. Neben der Eigenschaft „StandardWidth“ hat das Objekt „Tabelle1“ auch die Eigenschaft „StandardHeight“. Diese Eigenschaft kannst du aber nur „abfragen“ und nicht verändern („read only“) Beispiel: Ändere den Inhalt (Eigenschaft „Value“) des Objektes „Zelle A1 des Tabellenblattes“8 Private Sub CommandButton1_Click() Tabelle1.Cells(1, 1).Value = InputBox("Gib einen Wert für das Feld A1 ein") MsgBox ("beachte den Inhalt der Zelle A1") End Sub Aufgabe: Wie musst du das Programm ändern, dass der Inhalt der Zelle A5, C1, C3,... geändert wird ? Kontrollfragen: 1. Was bedeutet „Argument“ beim Programmieren 2. Warum wurde bei der MsgBox das Argument (Tabelle1.StandardWidth) nicht unter Anführungszeichen („“) angegeben. Wann muss in der MsgBox das Argument unter Anführungszeichen angeben werden ? 3. Wozu dient die Angabe eines Textes in der InputBox Anweisung 4. Was geschieht, wenn ein nicht erwarteter Wert bei einer InputBox Anweisung eingegeben wird (z.B. bei der Frage nach der Spaltenbreite wird der Buchstabe „A“ eingegeben) 5. Was geschieht in dem folgenden Programm ? Private Sub CommandButton1_Click() Tabelle1.StandardWidth = Tabelle1.StandardHeight End Sub 6. Was geschieht, wenn du in einem Programm versuchst, die Zellenhöhe des Tabellenblattes zu verändern ? 7. Schreibe ein Programm, das in einer MsgBox den Inhalt der Zelle A2 ausgibt. 8. Schreibe ein Programm, das das Vierfache des Inhaltes der Zelle A1 in die Zelle B3 schreibt.9 Die Bedeutung der Schreibweise „Tabelle1.Cells(1,1)“ als Name für das Objekt „Zelle A1“ wird noch genauer erklärt werden. 9 Natürlich kannst du dasselbe Ergebnis ohne Programmierung durch Eintrag einer Formel in der Zelle B3 erzielen 8 © Lothar Kerbl 10.6.2005 Seite 11 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA 9. Was passiert, wenn du den Wert der Eigenschaft „Tabelle1.Name“ änderst10 ? Die Methoden eines Objekts Objekte haben Methoden (Methods) Eine Methode eines Objektes wird durch ein Ereignis (Event) ausgelöst. Anders formuliert: Objekte reagieren auf Ereignisse(Events) durch Ausführen ihrer Methoden (methods) Ein Ereignis kann auch als „Botschaft (Message) an ein Objekt“ interpretiert werden. Die Methoden von Objekten sind Programme. Diese Programme ... werten die Eigenschaften des eigenen Objekts aus. ... verändern die Werte der Eigenschaften des eigenen Objekts ... veranlassen andere Objekte, deren Methoden auszuführen („Sie erzeugen Ereignisse (create events) für andere Objekte“ bzw. „Senden Botschaften (send messages) an andere Objekte“) ... lesen und verändern die Eigenschaften anderer Objekte11 Methoden benötigen unter Umständen Argumente, durch die ihre Funktionsweise (genauer) definiert wird. Private Sub CommandButton1_Click() Tabelle1.Cells(1, 1).AddComment ("blablabla")12 End Sub oder13 Private Sub CommandButton1_Click() Cells(1, 1).AddComment ("blablabla") End Sub Der Eingabeknopf – ein „Objekt im Objekt“ Bisher wissen wir, dass Objekte Eigenschaften haben, denen ein Wert zugewiesen werden kann. Beachte: Durch Änderung der Eigenschaft „Tabelle1.Name“ änderst du nicht die Bezeichnung, mit der du das Objekt in deinem Programm ansprichst. 11 Einige Programmieransätze gehen davon aus, dass die Eigenschaften anderer Objekte nicht direkt gelesen oder verändert werden sollen. Im Sinne einer „Kapselung“ sollte das nur durch „Botschaften an das andere Objekt“ möglich sein. 12 Der Kommentartext ist das Argument der Methode „AddComment“ 13 Siehe dazu die Bemerkung über die Verwendung des Namens für „das eigene Objekt“ 10 © Lothar Kerbl 10.6.2005 Seite 12 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Objekte können neben Eigenschaften auch Objekte „enthalten“ (Das umfassende Objekt wird dabei auch als „Container“ bezeichnet). Die enthaltenen Objekte haben selbst wieder Eigenschaften. Ein enthaltenes Objekt wird durch die Punktschreibweise hinter das Containerobjekt geschrieben. Diese „Container-Beziehung“ ist in beliebiger Tiefe fortsetzbar. Ein Beispiel für eine „Container-Beziehung“ hast du schon kennen gelernt. „Tabelle1.Cells(1,1)14“ bezeichnet das Objekt Zelle EXCEL-Zelle„A1“ im Tabellenblatt „Tabelle1“. Auch der Eingabeknopf ist ein Objekt innerhalb des Objekts „Tabelle1“ Er hat den Namen Tabelle1.CommandButton1 Du siehst diesen Namen im „Angebot“ der Eigenschaften von Tabelle1 aufgelistet15. Bild 16. Das Objekt „CommandButton1“ wird wie eine „Eigenschaft“ des Objekts „Tabelle1“ angezeigt Die Eigenschaften des Eingabeknopfes CommandButton1 kannst du natürlich ebenfalls abfragen oder ändern. Bild 17. Eigenschaften und Methoden des Objektes „Tabelle1.CommandButton1“ („Objekt im Objekt“) Private Sub CommandButton1_Click() Tabelle1.CommandButton1.Caption = "ok" Vorläufig bleibt noch die Erklärung der Indexschreibweise „(1,1)“ offen. Die Schreibweise ist im Zusammenhang mit einer EXCEL-Eingabe wohl selbsterklärend, wir werden aber später bei der Erklärung von „Collections“ noch genauer darauf eingehen. 14 Beachte, dass ein „Objekt im Objekt“ durch dasselbe Symbol „ Eigenschaft eines Objekts. 15 © Lothar Kerbl 10.6.2005 “ angezeigt wird, wie die Seite 13 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA End Sub natürlich funktioniert auch ... Private Sub CommandButton1_Click() CommandButton1.Caption = "ok" End Sub Hinweis: Beachte, dass die Beschriftung „ok“ erst dann erscheint, wenn der Eingabeknopf „angeclickt“ wird, also genau dann, wenn das „ClickEreignis“ auftritt, das Steuerelement die „Click Botschaft“ erhält und darauf mit seiner „Click“-Methode reagiert. Übungen mit mehreren Steuerelemente Versuche nun, die Eigenschaften der Steuerelemente genauer zu verstehen. Du kannst natürlich auch mehrere Steuerelemente auf deinem Tabellenblatt platzieren. Versuche zum Beispieldurch einen „Knopfdruck“ .. ...denn Inhalt der Zelle C4 in ein „Textfeld“ zuschreiben. ... die aktuelle Uhrzeit in das EXCEL-Feld B2 zu schreiben ... die Farbe des Zellbereiches A3:C5 entsprechend der Farbe der Zelle A1 zu ändern ... einen zweiten Eingabeknopf darzustellen bzw. zu verbergen (Eigenschaft „visible“) ...den Eingabeknopf nach rechts wandern zu lassen .... Wie raegieren „Kontrollkästchen“ und „Optionsfelder“ ? Wie heißen die „Routinen“, die beim „Anklicken“ eines Kontrollkästchens bzw. eines Optionsfeldes ausgeführt werden ? Notiere dir Anmerkungen zu den wichtigsten Eigenschaften. Es ist praktisch unmöglich, alle Eigenschaften der Steuerelemente im Detail zu kennen, auch erfahrene Programmierer finden immer neue Möglichkeiten für den Einsatz der Eigenschaften der Objekte. Eine neue Eigenschaft für ein Objekt definieren Können wir Eigenschaften auch selbst anlegen ? Du musst da genau aufpassen: Ich meine damit nicht, den Wert einer Eigenschaft zu ändern, sondern überhaupt eine neue Eigenschaft für das Objekt zu definieren. Zu abstrakt ? – Vielleicht hilft ein Beispiel: Du möchtest den Eingabeknopf „verbessern“, indem du ihm mit einem „Gedächtnis“ ausstattest. Jedes „Anclicken“ soll registriert werden, die Anzahl der © Lothar Kerbl 10.6.2005 Seite 14 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA ausgeführten Clickvorgänge soll zu einer zusätzlichen „Eigenschaft“ des Tabellenblattes „Tabelle1“ werden. Damit verfügt dein Eingabeknopf über eine „Zählerfunktion“. Dieser Zähler funktioniert vorerst noch „im Verborgenen“. Du solltest dir also noch überlegen, wie du den Zählerwert („Wert einer Eigenschaft“) anzeigst, und wie du die registrierte Anzahl der Zählereignisse „zurücksetzen“ kannst . © Lothar Kerbl 10.6.2005 Seite 15 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Bild 18. Einfügen der selbstdefinierten Eigenschaft „a“ (Ganzzahl) in das Objekt „Tabelle1“16 Bild 19. Die „Oberfläche“ eines Programmes, das die selbsterstellte Eigenschaft „a“ verwendet Bild 20. Die selbstdefinierte Eigenschaft ist sogar in der Eingabehilfe sichtbar Mit der Anweisung „Public“ definierst du eine Eigenschaft, die auch von außerhalb des Objektes sichtbar ist. Wenn du die Eigenschaft nur innerhalb des Objektes (d.h. von Methoden des eigenen Objektes) bearbeiten lassen willst, dann verwendest du die Anweisung „Dim“. In diesem Fall darfst du in den Anweisungen den Namen „a“ nur ohne vorangestellten Namen „Tabelle1“ verwenden. 16 © Lothar Kerbl 10.6.2005 Seite 16 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Programmstrukturen Verzweigungen Teile der "if-Anweisung" (Verzweigungsanweisung) Eine Bedingung wird ausgewertet, und abhängig vom Ergebnis wird einer der beiden "Zweige" (Anweisungen) ausgeführt. Eine Bedingung ist ein Ausdruck, der als Ergebnis entweder "wahr" oder "falsch" liefert. Die „If Anweisung“ ohne „else“ Liefert die Auswertung der Bedingung das Ergebnis "true", so werden die Anweisungen zwischen "then" und "end if" ausgeführt Liefert die Auswertung der Bedingung das Ergebnis „false“ wird sofort die auf "end if " folgende Anweisung ausgeführt. Die „If Anweisung“ mit „else“ Liefert die Auswertung der Bedingung das Ergebnis "true", so werden die Anweisungen zwischen "then" und "else" ausgeführt. Liefert die Auswertung der Bedingung das Ergebnis „false“ werden die Anweisungen zwischen "else" und "end if" ausgeführt. Struktogramm und Flussdiagramm einer Verzweigung Struktogramm: Die „äußere Form“ ist ein „Kasten“. Ein Kasten kann an jeder Stelle eingesetzt werden, an der eine „Anweisung“ steht. Flussdiagramm: Grundelemente eines Flussdiagrammes sind Anfangs- und Endpunkt Anweisungen Verzweigungen („ja“ / „nein“) (gerichtete) Linien Vereinigungspunkte Markierungen („Label“) © Lothar Kerbl 10.6.2005 Seite 17 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Beispiel Das Programm soll nach dem Anclicken des CommandButton1 den Wert in der Zelle A1 als Schulnote interpretieren und eine oder mehrere passende Bemerkung (abhängig von den Inhalten verschiedener EXCEL-Felder) ausgeben.... Das Einrücken von Text ist nicht notwendig, die Programmteile die im Falle „wahr“ bzw. „falsch“ ausgeführt werden, werden durch die „Schlüsselwörter“ geklammert: Zwischen If und then steht die Bedingung, die als Ergebnis „wahr“ oder „falsch“ liefert Zwischen Then und else stehen die Anweisungen, die bei „wahr“ ausgeführt werden. Zwischen Else und end if stehen die Anweisungen, die bei „falsch“ ausgeführt werden. Private Sub CommandButton1_Click() If (Tabelle1.Cells(1,1).Value < 4) Then msgBox "...das war schon ganz gut" Else msgBox "...du solltest dich mehr anstrengen !" msgBox " weil so geht das nicht weiter !" End if If (Tabelle1.Cells(1,1).Value = 5) Then msgBox("...das wahr wohl nichts!") End if End Sub Die mehrstellige Verzweigung „if – elseif“ If <Bedingung1> then <Anweisung1> ElseIf <Bedingung2> <Anweisung2> ElseIf <Bedingung3> <Anweisung3> ....... Else <Anweisung_n> End If Die erste Anweisung im Programmtext, deren zugehörige „Wächterbedingung“ wahr ist, wird ausgeführt,. 17 In den „nein“- Pfaden des Flussdiagrammes wird jeweils eine weitere Verzweigung eingefügt. Alle Pfade aus den Verzweigungen laufen in einem Vereinigungspunkt zusammen. Beachte den Unterschied zur Programmiersprache „C“: In C werden in einer mehrstelligen Verzweigung alle Anweisungen ausgeführt, deren „Wächterbedingungen“ wahr sind. 17 © Lothar Kerbl 10.6.2005 Seite 18 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Die Mehrfachverzweigung „Select Case“ Select Case-Anweisung Select Case <Testausdruck> Case <Ausdrucksliste1> <Anweisung1> Case <Ausdrucksliste2> <Anweisung2> .... Case Else <Anweisung_n> End Select Anstelle von Bedingungen ( mögliche Ergebnisse „ja/nein“) stehen die möglichen Ergebnisse eines <Ausdrucks> als „Wächter“ für die jeweiligen Anweisungen. Die Ergebnisse des <Ausdrucks> müssen „aufzählbar“ sein. Beispiele zu Verzweigungen Programmbeispiele für alle Arten von Verzweigungen, erstelle zuerst das Flussdiagramm (bzw. das Struktogramm) Zählschleifen Beispiel: du möchtest alle Zellen von A1 bis A20 mit einem Text ausfüllen. Natürlich kennst die „Automatisch Ausfüllen“ („Herunterziehen“) Funktion von EXCEL. Wir wollen aber das Ausfüllen programmgesteuert durchführen: Private Sub CommandButton1_Click Tabelle1.Cells(1,1)=“Hallo !“ Tabelle1.Cells(2,1)=“Hallo !“ Tabelle1.Cells(3,1)=“Hallo !“ Tabelle1.Cells(4,1)=“Hallo !“ Tabelle1.Cells(5,1)=“Hallo !“ Tabelle1.Cells(6,1)=“Hallo !“ Tabelle1.Cells(7,1)=“Hallo !“ Tabelle1.Cells(8,1)=“Hallo !“ Tabelle1.Cells(9,1)=“Hallo !“ Tabelle1.Cells(10,1)=“Hallo !“ Tabelle1.Cells(11,1)=“Hallo !“ Tabelle1.Cells(12,1)=“Hallo !“ Tabelle1.Cells(13,1)=“Hallo !“ Tabelle1.Cells(14,1)=“Hallo !“ Tabelle1.Cells(15,1)=“Hallo !“ Tabelle1.Cells(16,1)=“Hallo !“ Tabelle1.Cells(17,1)=“Hallo !“ Tabelle1.Cells(18,1)=“Hallo !“ Tabelle1.Cells(19,1)=“Hallo !“ Tabelle1.Cells(20,1)=“Hallo !“ End Sub .... Das muss doch einfacher gehen © Lothar Kerbl 10.6.2005 Seite 19 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Programmteile, die mehrmals hintereinander ausgeführt werden sollen, und von denen du spätestens unmittelbar vor der ersten Ausführung weißt, wie oft sie ausgeführt werden sollen, werden in eine Zählschleife „eingepackt“. Die Zählvariable (eine Ganzzahl) kann innerhalb der Zählschleife verwendet werden („lesender Zugriff“), sie sollte aber keinesfalls innerhalb der Zählschleife verändert werden. .... Dim Zaehler as integer For Zaehler = 1 to 20 do Tabelle1.Cells(Zaehler,1)=“Hallo !“ Next .... Eine Zählschleife steht zwischen For und Next Die Zählschleife besteht aus einer Steueranweisung und dem Schleifenrumpf. Der Textanfang des Schleifenrumpfes (=das Textende der Steueranweisung) ist in Visual Basic nicht besonders gekennzeichnet. Das Textende des Schleifenrumpfes (=das Ende der Programmtextes der Zählschleife) ist durch Next gekennzeichnet. Die Steueranweisung „wirkt“ auf eine Zählvariable, sie wird vor jedem Schleifendurchlauf ausgeführt. Die lokale Zählvariable Als Zählvariable muss eine Ganzzahlvariable verwendet werden. Am besten ist diese „möglichst als lokal Variable“ zu vereinbaren. Lokale Variable werden unmittelbar nach der Überschrift definiert. Lokale Variable „leben“ nur solange, wie die Routine, in der sie verwendet werden, ausgeführt wird. Mit lokalen Variablen ist es nicht möglich, Werte von einem Prozeduraufruf zum nächsten zu „retten“. Die Option „STEP“ in einer Zählschleife Oft ist es dabei vom Vorteil, wenn die Zählvariable nicht einfach durch Weiterzählen verändert wird. Durch die Option „STEP“ unmittelbar hinter der Steueranweisung kannst du die Zählvariable in „größeren Schritte“ verändern. Auch das „Herunterzählen“ durch Angabe eines negativen Wertes ist möglich. Sei vorsichtig mit der STEP-Option: Was macht das folgende Programm ? Private Sub CommandButton1_Click() Dim SchleifenZaehler As Integer For SchleifenZaehler = 1 To 10 Step 0 © Lothar Kerbl 10.6.2005 Seite 20 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA MsgBox ("Das ist der " + Str(SchleifenZaehler) + ". Durchlauf") Next End Sub Übungen zu Zählschleifen Füge die Zahlen (1,2,3,...20) in die Zellen C1,D1,E1,... ein Füge die Zahlen (102, 103,104,....118) in die Zellen F3,G4,H5,.... ein Füge die Zahlen (20,18,16,...0) in die Zellen G12,G11,G10,....ein Füge die Quadratzahlen (1,4,9,16,....,400) in die Zellen A1,B2,C1,D2,..... ein .....finde weitere „Einfügemuster“ Gesteuerte Schleifen (Bedingter Schleifendurchlauf) Bei vielen Schleifen steht vor dem ersten Durchlauf noch nicht fest, wie oft der Schleifenrumpf durchlaufen werden soll. In diesem Fall wird vor, nach oder während der Bearbeitung des Schleifenrumpfes eine Bedingung ausgewertet. Das Ergebnis dieser Bedingung entscheidet über: nochmaligen Schleifendurchlauf (Fortsetzung) oder Ausstieg aus der Schleife (Abbruch) Gesteuerte Schleifen können leicht zu Endlosschleifen werden, wenn die Bedingung nie zum Abbruch führt. Eine „Terminierungszusicherung“ solltest du in deinen Programmen für jede Schleife garantieren können. Kopfgesteuerte Schleifen Kopfgesteuerte Schleife mit Fortsetzungsbedingung Do while <Bedingung> <Anweisung(en)> Loop Kopfgesteuerte Schleife mit Abbruchbedingung Do until <Bedingung> <Anweisung(en)> Loop In einer kopfgesteuerten Schleife wird der Schleifenrumpf gegebenenfalls überhaupt nicht durchlaufen. Fußgesteuerte Schleifen Fußgesteuerte Schleife mit Fortsetzungsbedingung Do <Anweisung(en) Schleifenrumpf> © Lothar Kerbl 10.6.2005 Seite 21 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Loop while <Bedingung> Fußgesteuerte Schleife mit Abbruchbedingung Do <Anweisung(en) Schleifenrumpf> Loop until <Bedingung> In einer fußgesteuerten Schleife wird der Schleifenrumpf auf alle Fälle mindestens einmal durchlaufen. Endlosschleife und N+1/2 Schleife .... Do `keine Bedingung am Anfang der Schleife MsgBox („das geht ohne Ende so weiter....!“) MsgBox („hoffentlich kann man noch den Task-Manager aufrufen!“) Loop `keine Bedingung am Ende der Schleife ... Natürlich hat in einem „ordentlichen“ Programm eine Endlosschleife keinen Sinn. Endlosschleifen sind nur dann sinnvoll, wenn „irgendwo“ ein Aussprung möglich ist, und damit werden auch sie zu gesteuerten Schleifen.18 Dieser „Aussprung“ – also der Punkt, an dem die Entscheidung über eine Fortsetzung der Schleife zu treffen ist- kann "mitten im Schleifenrumpf" programmiert werden. („bedingtes Exit19“) ... Do Msgbox(„weißt du das Zauberwort ? Gib es ein :“) If Inputbox() = “danke” then Exit Do ‘Schleifensteuerung Msgbox(„das war falsch ... versuch es nochmals !“) Loop MsgBox („genau das ist es !“) ... Skizziere die Flussdiagramme der „Endlosschleife“ und der „n+1/2 Schleife“. Übungen zu Schleifen: zeichne zuerst das Flussdiagramm, formuliere die Aufgabenstellung in allen Schleifenvarianten. Finde die jeweils geeignetste Variante. Die For - Each Schleife Diese Schleife ist eine besondere Form der „Zählschleife“. 18 Es gibt Sonderfälle, bei denen Endlossschleifen ohne programmierten Aussprung sinnvoll sind (z.B. interruptgesteuerte Programme in Mikrocontrolleranwendungen) 19 In früheren Versionen von VB genügte die Anweisung „Exit“ alleine (ohne „Do“) © Lothar Kerbl 10.6.2005 Seite 22 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Die For Each Schleife dient dazu, alle Elemente einer „Collection“ nacheinander zu bearbeiten. Während in einer Zählschleife die „Laufvariable“ eine ganze Zahl sein muss, ist in einer For Each – Schleife die Laufvariable eine „Objektreferenz“, die in jedem Schleifendurchlauf auf ein anderes Objekt der Collection verweist. Der Typ der Laufvariablen kann entweder sein: Ein Verweis auf eine besondere Art von Objekten („Klasse“) Ein Verweis auf ein allgemeines Objekt („generisches Objekt“) Private Sub CommandButton1_Click() Dim bearbeiteteTabelle As Object Set bearbeiteteTabelle = CreateObject("Excel.sheet") For Each bearbeiteteTabelle In Excel.Sheets MsgBox (bearbeiteteTabelle.Name) Next Set bearbeiteteTabelle = Nothing End Sub Wichtige Objekte in EXCEL Die folgende Aufzählung von EXCEL-Objekten ist bei weitem nicht vollständig. Wenn du professionelle Anwendungen für EXCEL in VBA erstellen willst, kommst du nicht umhin, dir „EXCEL-VBA Literatur“ zu besorgen, bzw. im Internet in den entsprechenden Foren zu stöbern. Beachte auch, dass einige EXCEL-Objekte „vorhanden sind“. Von manchen Objekten ist aber nur der Bauplan (die „Klassendefinition“) angegeben. Entsprechende Objekte müssen vor ihrer Nuztung erst „instantiiert“ werden. Beispiel: Das Objekt „Application“ ist unter diesem Namen vorhanden (es wurde beim Aufruf von EXCEL „instantiiert“). Wenn du mit „mehreren Zellbereichen“ arbeiten willst, so kannst du dir selbst ein Objekt der Art („Klasse“) „Range“ erzeugen. Das geht durch ... ... Vereinbaren einer Objektvariable der Klasse „Range“ Dim MeinLoeschbereich as Range ... „Instantiieren“ eines entsprechenden Objektes – das erfolgt in VBA „implizit mit der Vereinbarung der Objektvariablen“ 20 ... Benutzen des Objekts MeinLoeschbereich(„a2:C6“).select MeinLoeschbereich.clear Objekte können in Objekten enthalten sein. 20 Objekte können Objekte „enthalten“ („Objektbaum“) In einem VB-Programm werden Objekte durch „set .... = new .... “ instantiiert. © Lothar Kerbl 10.6.2005 Seite 23 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA In diesem Fall sind die Objektnamen durch Punkte voneinander getrennt, die letzte (am weitesten rechts stehende) Bezeichnung kennzeichnet die Eigenschaft oder die Methode des „eigentlichen Objekts“ Beispiel: Application.ActiveCell. ...hier musst du noch genauer angeben, welche Eigenschaft des Objekts „ActiveCell“ du verwenden willst. (z.B. „Value“ – liefert den Inhalt der Zelle) Die EXCEL-Objekte sind baumförmig organsiert. Du musst aber nicht immer alle Namen des Stammes angeben. Für ein EXCEL-VBA – Programm ist der Namensteil „Application“ auf der linken Seite „defaultmäßig“ definiert. Beispiel: Application.ActiveCell ist gleichbedeutend mit ActiveCell Es gibt auch Namensteile, die auf der rechten Seite „defaultmäßig“ definiert sind Es gibt für jedes Objekt eine „Standardeigenschaft“ Beispiel: Application.Name ist gleichbedeutend mit Application Von der Verwendung dieser letztgenannten „Abkürzungsmöglichkeit“ rate ich dir ab, der Programmtext kann sonst recht verwirrend werden. Viele Objekte sind in einer „Auflistung“ (Collection) enthalten.21 Diese eingebetteten Objekte haben natürlich die ihnen zugeordneten Eigenschaften und Methoden. Die Collection selbst verfügt über eigene Eigenschaften und Methoden: Beispiel: In Application.Workbooks. sind alle geöffneten Dateien „enthalten“. Die Anzahl der Dateien kann durch eine Eigenschaft der Collection Collections sind „Objektcontainer“ Wichtige Eigenschaften und Methoden einer Collection: Count add delete ..... = Application.Workbooks.Count ermittelt (aber nicht verändert) werden. Auf ein Objekt, das in einer Collection enthalten ist, kannst du über einen Index (Ganzzahl) oder über einen Namen (Textstring) zugreifen. Application.Workbooks(1).<..Eigenschaften/Methoden..> Application.Workbooks(“Tabelle1“).<..Eigenschaften/Methoden...> Viele Objekte erreichst du über mehrere Namen: „Auflistungen“ (Collections) sind selbst Objekte, auf die du gewisse „Methoden“ anwenden kannst (diese Methoden sind vor allem: Hinzufügen und Wegnehmen eines Eintrags) Auf die Elemente einer Auflistung kannst du auf zwei Arten zugreifen: über den „Index“ (eine Ganzzahl in Klammern) oder über eine „Bezeichnung“ (Angabe eines Namens in Klammern). Auf alle aktuell vorhandenen Elemente einer Auflistung kannst du mit einer „for each“-Schleife nacheinander zugreifen. 21 © Lothar Kerbl 10.6.2005 Seite 24 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Beispiel: Application.Workbooks(1).Worksheets(1).name „erreichst“ du auch über Application.Sheets(1).name (zumindest wenn du nur eine Datei geöffnet hast) Application. Application.name Application.workbooks. Application.workbooks(1). Application.workbooks(1). worksheets. Application.workbooks(1). worksheets(1). Application.workbooks(1). worksheets(1).name Application.ActiveCell. ...oft benötigt werden: (Kurzform): Sheets(„Tabelle1“).Activate Range(„A1“).Select Sheets(„Tabelle1“).range(„A1“). select ActiveSheet. Selection. ActiveCell.Offset(i,j). ActiveSheet.Cells(i,j). Tabelle1.Cells(i,j). ActiveCell.value ActiveCell.Formula ActiveCell.Address ActiveCell.Column ActiveCell.Row ActiveCell.Comment. ActiveSheet.Pictures. ActiveCell.AddComment() © Lothar Kerbl Das Objekt „Anwendung“ (Ausgangspunkt aller EXCEL-Objekte) Die Eigenschaft „Name“ des Objkets „Application“ Die Auflistung aller Dateien Das Objekt „erste geöffnete Datei der Anwendung“ Die Auflistung (Collection) aller Tabellen der „ersten geöffneten Datei der Anwendung“ Das Objekt „erste Tabelle der ersten geöffneten Datei der Anwendung“ Die Eigenschaft „Name“ des Objekts „erstes Tabellenblatt in der ersten geöffneten Datei der Anwendung“ Das Objekt „aktivierte Zelle“ Methode zur Auswahl eines Tabellenblattes Methode zur Auswahl einer Zelle Methode zum Auswählen eines Tabellenblattes und einer Zelle Das Objekt „aktives Tabellenblatt“ Das Objekt „ausgeschnittener Bereich“ Das Objekt „Zelle mit definiertem Abstand zur aktiven Zelle“ Objekt „Zelle (i,j) im aktuellen Tabellenblatt Objekt „Zelle (i,j) im Blatt ‚Tabelle1’“ Eigenschaft „Wert in der Zelle“ Eigenschaft „Formel in der Zelle“ Eigenschaft „Adresse der Zelle“ (Text) Eigenschaft „Spalte der Zelle“ (Ganzzahl) Eigenschaft „Zeile der Zelle“ (Ganzzahl) Objekt „Kommentar der Zelle“ Auflistung der Bilder Methode für Aktive Zelle: „Einfügen eines Kommentars“; Argument: Kommentartext 10.6.2005 Seite 25 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Der Makro-Recorder Wahrscheinlich kannst du viele Teilaufgaben der von dir programmierten Anwendung ohne Probleme „interaktiv“ in EXCEL ausführen (beispielsweise das Einfärben einer Zelle). Um denselben Vorgang aber programmgesteuert auszuführen, müsstest du erst wissen, welche Eigenschaft der Zelle mit welchen Wert zu belegen ist. Natürlich kannst du in einer EXCEL-Dokumentation nachschlagen, aber oft führt folgender Weg schneller ans Ziel: 1. Du startest den Makro-Recorder („Extras“ -„Makro“ -„Aufzeichnen“ ... „ok“) 2. ... du führst die Befehle interaktiv in EXCEL aus ... Der Makro Recorder zeichnet „in der Programmiersprache VBA“ auf 3. ... du beendest die Aufzeichnung des Makros ... Bild 21. Schaltfläche zum Beenden der Makro Aufzeichnung 4. ... du öffnest mit „Extras“-„Makro“-„Makros...“–„Bearbeiten“ das neue Makro Sub Makro1() ' ' Makro1 Makro ' Makro am 10.06.2005 von Kerbl aufgezeichnet ' Range("E3").Select With Selection.Interior .ColorIndex = 40 .Pattern = xlSolid End With End Sub Bild 22. Ein „Makro“, das mit dem Makrorecorder erstellt wurde (Zelle E3 wird gelbbraun gefärbt)22 5. ... du „bearbeitest“ das entstandene Makro und fügst den Programmtext (eventuell modifiziert) in dein VBA-Programm ein „With“... „End With“ ist eine Kurzschreibweise, um nicht für jede Eigenschaft den gleich bleibenden Objektnamen (hier „Selection.Interior“ anschreiben zu müssen) 22 © Lothar Kerbl 10.6.2005 Seite 26 (27) htl-donaustadt Abteilung für Elektrotechnik Programmieren in EXCEL mit VBA Fehler suchen und beheben „Syntaxfehler“ und „Laufzeitfehler“ Der „sprachsensitive“ Editor überwacht schon bei der Eingabe, ob das Programm den „VBA-Regeln“ entspricht. Wenn ein Programm „syntaktisch“ korrekt ist, so bedeutet das aber nicht, dass es auch richtig funktioniert. Viele „Laufzeitfehler“ eines Programmes kannst du zwar finden, indem du den Programmtext nochmals sorgfältig überprüfst („Code Walk Through“), meist bist du aber auf Methoden angewiesen, mit denen du den Ablauf des Programmes „unter Beobachtung“ verfolgen kannst. „Breakpoints“ setzen und „Einzelschritte“ ausführen Möglichkeiten zur Fehlerbehebung sind unter dem Menüpunkt „Debuggen“ (in der VBA-Entwicklungsumgebung) zusammengefasst. Du kannst in deinem Programm Unterbrechungspunkte setzen. Die Ausführung des Programmes wird an dieser Programmzeile angehalten. Nachdem du überprüft hast, ob das Programm „bis hierher“ korrekt funktioniert, kannst du den Programmablauf fortsetzen (eventuell bis zum nächsten „Breakpoint“) Du kannst dein Programm auch „Anweisung für Anweisung“ ausführen. Um einen Breakpoint zu setzen, klickst du in die graue Randleiste links neben dem Befehl Einzelschritte werden mit F8 ausgeführt Das „Direktfenster“ Wenn du eine EXCEL-Anwendung programmierst, kannst du viele Ergebnisse unmittelbar im Tabellenblatt oder in deinen Steuerelementen sehen. Der Inhalt von „nicht direkt sichtbaren“ Eigenschaften (z.B. Zählvariablen) kannst du bei angehaltenem Programmablauf im Direktfenster überprüfen und ändern. Bild 23. Das Direktfenster – Abfrage und Ändern von Werten © Lothar Kerbl 10.6.2005 Seite 27 (27)
