Programmieren in EXCEL mit VBA

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
Das Objekt „Tabelle1“ ............................................................................................... 7
Der Name eines Objekts (Name of object) ............................................................ 7
Eigenschaften (Properties) des Objektes „Tabelle1“ ............................................. 8
Die Methoden eines Objekts ................................................................................ 11
Der Eingabeknopf – ein „Objekt im Objekt“ ....................................................... 11
Übungen mit mehreren Steuerelemente ............................................................... 13
Eine neue Eigenschaft für ein Objekt definieren ................................................. 13
Programmstrukturen................................................................................................. 15
Verzweigungen..................................................................................................... 15
Die „If Anweisung“ ohne „else“ ...................................................................... 15
Die „If Anweisung“ mit „else“......................................................................... 15
Struktogramm und Flussdiagramm einer Verzweigung................................... 15
Die mehrstellige Verzweigung „if – elseif“ ..................................................... 16
Die Mehrfachverzweigung „Select Case“........................................................ 17
Beispiele zu Verzweigungen............................................................................ 17
Zählschleifen ........................................................................................................ 17
Die lokale Zählvariable -.................................................................................. 18
Die Option „STEP“ in einer Zählschleife ........................................................ 18
Übungen zu Zählschleifen................................................................................ 19
Gesteuerte Schleifen (Bedingter Schleifendurchlauf).......................................... 19
Kopfgesteuerte Schleifen ................................................................................. 19
Fußgesteuerte Schleifen ................................................................................... 19
Endlosschleife und N+1/2 Schleife .................................................................. 20
Die For - Each Schleife ........................................................................................ 20
Wichtige Objekte in EXCEL.................................................................................... 21
© Lothar Kerbl
5.4.2005
Seite 1 (1)
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
Bild 1.
Vorbereitung zum Einfügen von Steuerelementen;
beachte, dass auch die Symbolleiste „Visual Basic“ sichtbar ist.
© Lothar Kerbl
5.4.2005
Seite 2 (2)
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
5.4.2005
Seite 3 (3)
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 Anweisungenen 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
5.4.2005
Seite 4 (4)
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 rufst Word auf und wechselst in die „VBA-Programmierumgebung:“
Bild 7.
Aufruf des VB-Editors aus Word
Du kannst in Word die Steuerelemente nicht direkt auf dein „Blatt“ platzieren,
deshalb musst du „als Unterlage“ ein Fenster (Form) erstellen.
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
5.4.2005
Seite 5 (5)
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 Steuerelmente 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
5.4.2005
Seite 6 (6)
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.)
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.
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.
© Lothar Kerbl
5.4.2005
Seite 7 (7)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
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 Propertiy3“) 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 13.
Der aktuellen Wertes der Eigenschaft „StandardWidth“ des Objekts „Tabelle1“wird angezeigt,
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.
3
Die möglichen
Eigenschaften
werden
angezeigt
Beachte, dass sowohl das Objekt selbst als auch die Eigenschaften „Namen“ haben.
© Lothar Kerbl
5.4.2005
Seite 8 (8)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
Bild
Bei der Erstellung von Programmen werden die Namen der Objekteigenschaften angezeigt
Der Editor4 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 Eigenschaften5 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
.. oder6
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.
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“)
4
Editor: Das ist das Textprogramm, mit dem du den Programmtext (Source Code) erstellst
5
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.
6
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.
© Lothar Kerbl
5.4.2005
Seite 9 (9)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
Beispiel:
Ändere den Inhalt (Eigenschaft „Value“) des Objektes „Zelle A1 des
Tabellenblattes“7
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.8
9. Was passiert, wenn du den Wert der Eigenschaft „Tabelle1.Name“ änderst9 ?
7
Die Bedeutung der Schreibweise „Tabelle1.Cells(1,1)“ als Name für das Objekt „Zelle A1“ wird
noch genauer erklärt werden.
8
Natürlich kannst du dasselbe Ergebnis ohne Programmierung durch Eintrag einer Formel in der
Zelle B3 erzielen
9
Beachte: Durch Änderung der Eigenschaft „Tabelle1.Name“ änderst du nicht die Bezeichnung, mit
der du das Objekt in deinem Programm ansprichst.
© Lothar Kerbl
5.4.2005
Seite 10 (10)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
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 Objekte10
Methoden benötigen unter Umständen Argumente, durch die ihre Funktionsweise
(genauer) definiert wird.
Private Sub CommandButton1_Click()
Tabelle1.Cells(1, 1).AddComment ("blablabla")11
End Sub
oder12
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.
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.
10
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.
11
Der Kommentartext ist das Argument der Methode „AddComment“
12
Siehe dazu die Bemerkung über die Verwendung des Namens für „das eigene Objekt“
© Lothar Kerbl
5.4.2005
Seite 11 (11)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
Ein Beispiel für eine „Container-Beziehung“ hast du schon kennen gelernt.
„Tabelle1.Cells(1,1)13“ 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
aufgelistet14.
Bild 14.
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 15.
Eigenschaften und Methoden des Objektes „Tabelle1.CommandButton1“ („Objekt im Objekt“)
Private Sub CommandButton1_Click()
Tabelle1.CommandButton1.Caption = "ok"
End Sub
natürlich funktioniert auch ...
Private Sub CommandButton1_Click()
CommandButton1.Caption = "ok"
End Sub
Hinweis:
13
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.
© Lothar Kerbl
5.4.2005
“ angezeigt wird, wie die
Seite 12 (12)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
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
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
5.4.2005
Seite 13 (13)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
Bild 16.
Einfügen der selbstdefinierten Eigenschaft „a“ (Ganzzahl) in das Objekt „Tabelle1“15
Bild 17.
Die „Oberfläche“ eines Programmes, das die selbsterstellte Eigenschaft „a“ verwendet
Bild 18.
Die selbstdefinierte Eigenschaft ist sogar in der Eingabehilfe sichtbar
15
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.
© Lothar Kerbl
5.4.2005
Seite 14 (14)
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
5.4.2005
Seite 15 (15)
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,. 16
In den „nein“- Pfaden des Flussdiagrammes wird jeweils eine weitere Verzweigung
eingefügt.
Alle Pfade aus den Verzweigungen laufen in einem Vereinigungspunkt zusammen.
16
Beachte den Unterschied zur Programmiersprache „C“: In C werden in einer mehrstelligen
Verzweigung alle Anweisungen ausgeführt, deren „Wächterbedingungen“ wahr sind.
© Lothar Kerbl
5.4.2005
Seite 16 (16)
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
5.4.2005
Seite 17 (17)
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
5.4.2005
Seite 18 (18)
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
5.4.2005
Seite 19 (19)
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
SinnEndlosschleifen sind nur dann sinnvoll, wenn „irgendwo“ ein Aussprung
möglich ist, und damit werden auch sie zu gesteuerten Schleifen.17
Dieser „Aussprung“ – also der Punkt, an dem die Entscheidung über eine
Fortsetzung der Schleife zu treffen ist- kann "mitten im Schleifenrumpf"
programmiert werden. („bedingtes Exit18“)
...
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.
17
Es gibt Sonderfälle, bei denen Endlossschleifen ohne programmierten Aussprung sinnvoll sind
(z.B. interruptgesteuerte Programme in Mikrocontrolleranwendungen)
18
In früheren Versionen von VB genügte die Anweisung „Exit“ alleine (ohne „Do“)
© Lothar Kerbl
5.4.2005
Seite 20 (20)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
Die For Each Schleife19 wird im Zusammenhang mit den Begriffen des
„objektorientierten Programmierens“ genauer erklärt werden.
Mit einer „For-Each Schleife“ kannst du alle Elemente einer „Collection“
bearbeiten.
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.
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“
Objekte können
Objekte
„enthalten“
(„Objektbaum“)
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
19
20
Die „For Each-Schleife“ setzt voraus, dass du den Begriff „Collection“ kennst.
In einem VB-Programm werden Objekte durch „set .... = new .... “ instantiiert.
© Lothar Kerbl
5.4.2005
Seite 21 (21)
htl-donaustadt Abteilung für Elektrotechnik
Programmieren in EXCEL mit VBA
ist gleichbedeutend mit
ActiveCell
Es gibt auch Namensteile, die auf der rechten Seite „defaultmäßig“ definiert sind
Beispiel:
Application.Name
Es gibt für jedes
Objekt eine
„Standardeigenschaft“
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:
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
Das Objekt „Anwendung“ (Ausgangspunkt
aller EXCEL-Objekte)
Die Eigenschaft „Name“ des Objkets
„Application“
21
„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.
© Lothar Kerbl
5.4.2005
Seite 22 (22)
htl-donaustadt Abteilung für Elektrotechnik
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
Programmieren in EXCEL mit VBA
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
5.4.2005
Seite 23 (23)