VBA in ECXEL
VBA („Visual Basic for Applications“) ist eine Basic-Programmiersprache, die speziell für die MS-Office Programme entwickelt wurde und zwei Zielen dient:· Arbeit automatisieren, indem Befehlsfolgen in einem einzigen Befehl zusammengefasst werden (sog. „Makro“)
· Funktionsumfang erweitern, indem die vorhandenen Befehle durch selbstgeschriebene Befehle ergänzt werden
Makros lassen sich über das Menü „Extras – Makro – Aufzeichnen ...“ wie mit einem Videorecorder aufnehmen und speichern. Alle Mausklicks und Tastendrücke werden aufgenommen, bis das Symbol „Aufzeichnung beenden“ angeklickt wird. Über „Extras – Makro – Makros ...“ kann es jederzeit abgespielt werden.
Eigenhändige VBA-Programmierung bietet mehr Möglichkeiten, verlangt aber auch mehr Aufwand. Zuerst den VBA-Editor öffnen: „Alt+F11“ oder „Extras – Makro – Visual Basic-Editor“. Befehle können nur innerhalb von Unterprogrammen stehen (sie beginnen mit „sub“ und einem frei wählbaren Namen):
sub Berechnen()
...
end sub
Außerhalb (d.h. oberhalb) dieser Unterprogramme sind nur globale Variablendeklarationen erlaubt, z.B.:
dim daten as SeriesCollection
Auf Zellinhalte oder Diagramme greift man über die unzähligen Objekte zu, aus denen eine Excel-Datei besteht:
An oberster Stelle steht das Objekt „application“, aber da klar ist, dass wir uns in der Applikation Excel befinden, kann es weggelassen werden. Darunter folgt das Arbeitsblatt. Zugriff entweder über die Auflistung „worksheets“, die in Klammer die Nummer des Arbeitsblatts bzw. dessen Namen verlangt, oder über „ActiveSheet“ (= das momentan bearbeitete Tabellenblatt):
worksheets(1) oder worksheet("Verkäufe") oder ActiveSheet
Unterhalb des Tabellenblatts verzweigt sich die Hierarchie: will man den Inhalt bestimmter Zellen ändern oder auslesen, benötigt man das „Range“-Objekt (Beispiel: in die Zelle A3 wird die Zahl 12,5 geschrieben – Achtung auf das Dezimaltrennzeichen (Punkt, nicht Beistrich)!):
worksheets(1).Range("A3").Value = 12.5
Formatierungen von Zellen erlaubt das Objekt „Interior“ (Beispiel: die Zellen H3 bis I4 erhalten rote Hintergrundfarbe):
Worksheets(1).Range("H3:I4").Interior.ColorIndex = 3
Diagramme erreicht man über das Objekt „chart“ und die Auflistung „chartobjects“ (enthält alle Diagramme dieses Tabellenblatts). Die einzelnen Datenreihen über die Auflistung „SeriesCollection“, einzelne Datenpunkte über die Auflistung „Points“. Beispiel (der zweite Datenpunkt der ersten Datenreihe erhält eine Datenbeschriftung):
Worksheets(1).ChartObjects(1).Chart.SeriesCollection(1).Points(2).HasDataLabel = True
Wie man sieht, kann die Objektliste recht lang werden. Der Übersichtlichkeit zuliebe deklariert man besser mit dim eine Objektvariable und weist ihr das gewünschte Objekt mit „set“ zu (Beispiel: die Variable „daten“ soll die Auflistung SeriesCollection enthalten):
dim daten as SeriesCollection
set daten = Worksheets(1).ChartObjects(1).Chart.SeriesCollection
Der Zugriff vereinfacht sich nun:
daten(1).points(2).HasDataLabels = True
Beachte den Unterschied zwischen Datenreihe (SeriesCollection) und Datenpunkt (Points)! Im folgenden Beispiel gibt es 5 Datenpunkte mit je zwei Datenreihen. Der zweite Datenpunkt der ersten Datenreihe bekommt rote Füllfarbe, alle Datenpunkte der zweiten Datenreihe eine Datenbeschriftung:
| 2.Datenpunkt, 1.Datenreihe: ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(2).Interior.ColorIndex = 3 alle Datenpunkte, 2 Datenreihe: ActiveSheet.ChartObjects(1).Chart.SeriesCollection(2).HasDataLabels = True |
Man erzeugt über „Extras – Makro – Makros ...“ ein neues Makro (Namen eingeben und mit Klick auf „Erstellen“ bestätigen) und schreibt dort den VBA-Code hinein. Dieses Makro kann man anschließend wie jedes aufgezeichnete Makro aufrufen und natürlich auch als Symbol in die Symbolleiste integrieren („Extras – Anpassen – Symbolleiste – Befehle - Makros“).
Der zweite, interessantere Weg führt über Ereignisse, auf die unsere VBA-Prozeduren als sog. „Ereignisprozeduren“ oder „Event-Handler“ reagieren. In Frage kommen vor allem zwei Ereignisse:
1) Daten im Tabellenblatt wurden geändert
Das entsprechende Ereignis lautet „Change“ und wird bei jeder Änderung in einer beliebigen Zelle des Tabelenblatts ausgelöst. Der dazugehörende Event-Handler verlangt eine ganz bestimmte Schreibweise (im folgenden Beispiel wird bei jeder Änderung einer beliebigen Zelle in die Zelle H3 der Text „erwischt!“ geschrieben):
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("H3").Value = "erwischt!"
End Sub
Ereignisprozeduren beginnen grundsätzlich mit dem Schlüsselwort „Private“. Da es sich um ein Ereignis des Tabellenblatts handelt, steht im Prozedurnamen „Worksheet“ vor dem eigentlichen Ereignis („Change“). In Klammer folgt ein „Parameter“ (damit wir erfahren, welche Zelle geändert wurde). Natürlich muss man nicht sämtliche Ereignisse und deren genaue Schreibweise auswendig lernen. Mit Mausklick auf das rechte obere Listfeld im Codefenster wird ein Prozedurrumpf in der korrekten Schreibweise von Excel selbst erzeugt (im linken Listfeld zuvor „Worksheet“ auswählen):
| Im rechten Listfeld das gewünschte Ereignis wählen |
| Im linken Listfeld „Worksheet“ auswählen |
Mit rechter Maustaste in die Schaltfläche klicken, Mausklick rechts öffnet das bekannt Popup-Menü, den Eintrag „Code anzeigen“ wählen – und endlich sind wir wieder im VBA-Editor. Ein Prozedurrumpf mit dem Namen der Schaltfläche wurde bereits von Excel angelegt. Wie man sieht, besteht eine Ereignisprozedur für Schaltflächen aus dem Namen, den man der Schaltfläche im Eigenschaften-Fenster gegeben hat, und – durch Unterstrich getrennt – dem Wörtchen „Click“:
Private Sub bovp_Click()
End Sub
Wir müssen nur noch den VBA-Code in diesen Rumpf hineinschreiben und fertig sind Schaltfläche und Eventhandler.
Wichtig: um den Schalter testen zu können, muss in der Toolbox der „Entwurfsmodus“ ausgeschaltet werden. Umgekehrt ist er wieder einzuschalten, wenn wir die Schalter-Eigenschaften neuerlich bearbeiten wollen.
Beispiel: animiertes Säulendiagramm
Aufgabe: die Ergebnisse der Vorarlberger Landtagswahlen vom 19.9.2004 sollen in einem Säulen-Diagramm angezeigt werden. Die einzelnen Säulen sollen auf Mausklick in die Höhe wachsen und anschließend oberhalb ihre Datenbeschriftung einblenden.Die Daten befinden sich bereits fertig in einer Tabelle. Das Diagramm bekommt seine Daten allerdings aus den Spalten „D“ (für 2004) und „G“ (für 1999), in denen alle Zellen auf „0“ gesetzt sind. Das hat seinen guten Grund: denn die Säulen des Diagramms sollen ja zu Beginn unsichtbar sein und erst auf Mausklick in die Höhe wachsen. Im Eventhandler des Schalters merken wir uns daher die tatsächlichen Endwerte (sie stehen in den Spalten „C“ für 2004 und „F“ für 1999) in zwei Variablen („max04“ und „max1999“) und füllen anschließend in einer for ... next - Schleife allmählich die Spalten „D“ und „G“ (aus denen das Diagramm seine Daten bezieht), beginnend bei „0“ bis zum Endergebnis.
Da alle fünf Schalter das gleiche tun – nur mit verschiedenen Datenpunkten - , bietet es sich an, die eigentliche Ereignisverarbeitung in eine Unter-Prozedur zu stellen (ich nenne sie „Zeichnen“) und diese von den Schaltern aufrufen zu lassen. Als Parameter erwartet sie in der Variablen „punkt“ die Nummer des Tabellenzeile, in der die Werte für das Diagramm stehen (D6 bis D10). Die fünf Event-Handler der Schalter lauten somit:
Private Sub bovp_Click()
zeichnen punkt:=6
End Sub
-------
Private Sub bspo_Click()
zeichnen punkt:=7
End Sub
-------
Private Sub bfpo_click()
zeichnen punkt:=8
End Sub
-------
Private Sub bgruen_Click()
zeichnen punkt:=9
End Sub
-------
Private Sub bdiv_Click()
zeichnen punkt:=10
End Sub
In der Prozedur „Zeichnen“ deklarieren wir zuerst ein paar Variablen und füllen anschließend in der genannten „for ... next“ – Schleife die erste Datenreihe des aktuellen Datenpunktes:
Sub zeichnen(punkt As Integer)
Dim AktZeit As Single
Dim daten As SeriesCollection
Dim i As Integer
Dim max04 As Single
Dim max99 As Single
Set daten = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
max04 = ActiveSheet.Range("C" & punkt).Value
max99 = ActiveSheet.Range("F" & punkt).Value
For i = 0 To max04
ActiveSheet.Range("D" & punkt).Value = i
AktZeit = Timer
Do While Timer < aktzeit + 0.01
DoEvents
Loop
Next
Da im Parameter „punkt“ die Nummer der Tabellenzeile geliefert wird, in der die Daten des Diagramms stehen (mögliche Werte: zwischen 6 und 10), können wir mit ("C" & punkt) die entsprechende Zelladresse basteln (z.B. „D7“, wenn punkt = 7). Über die Eigenschaft „value“ haben wir Zugriff auf den Zellinhalt, der in der for ... next – Schleife langsam gefüllt wird und damit das Diagramm, das seine Werte aus dieser Zelle bezieht, allmählich wachsen lässt.
Eine Warteschleife („do while ... loop“) sorgt dafür, dass die Säulen nicht schlagartig in den Himmel schießen. Die Funktion „timer“ liefert die aktuelle Uhrzeit, und zwar als reelle Zahl: die Vorkommastellen geben die Anzahl Sekunden an, die Nachkommastellen die 1/100 Sekunden. Wir merken uns die aktuelle Zeit in der Variablen „AktZeit“ und fragen solange die Uhrzeit ab, bis sie 0,01 Sekunden weitergerückt ist. Tatsächlich ist nicht genau vorhersagbar, wie lange die Verzögerung dauert. Denn der Aufruf von „DoEvents“ veranlasst Excel, für einen Moment die Kontrolle an Windows abzugeben (damit die Wartezeit genützt und der Computer nicht blockiert wird).
Zuletzt möchten wir noch, dass oberhalb der Säule ihre Datenbeschriftung eingeblendet wird. Die soll natürlich genau sein, aber die Variable „i“, die in der for ... next – Schleife als Zählvariable gedient und die Zelle „D & punkt“ gefüllt hat, war eine Ganzzahl („integer“) – musste sie auch sein, denn reelle Zahlen sind als Zählvariable nicht erlaubt – und lieferte daher keine Kommastellen. Damit die Beschriftung dennoch den exakten Wert inklusive Kommastellen (für die Säule „ÖVP - 2004“ z.B. „54,9“) anzeigt, müssen wir nach Ende der for ... next – Schleife noch einmal den Inhalt der Spalte „C“, den wir uns zu Beginn in der Variablen „max04“ (Datentyp „Single“ – daher mit Nachkommastellen) gemerkt haben, in die Spalte „D“ übertragen:
ActiveSheet.Range("D" & punkt).Value = max04
Um die Beschriftung auch tatsächlich sehen zu können, wird sie erst einmal eingeschaltet. Die Aufzählung „points“ erwartet in Klammer die Nummer (sog. „Index“) des Datenpunkts, beginnend mit „1“. Da in der Variablen „punkt“ die Zeilennummer der zum Diagramm gehörenden Tabellenspalte steht (beginnend mit „6“), subtrahieren wir einfach „5“ und kommen damit auf den richtigen Index:
daten(1).Points(punkt - 5).HasDataLabel = True
Für die korrekte Anzeige der Kommastelle (wir wollen aus Platzmangel nur eine sehen) sorgt die Formatanweisung an die Eigenschaft „NumberFormat“ der Datenbeschriftung (Objekt „DataLabel“):
daten(1).Points(punkt - 5).DataLabel.NumberFormat = "#0.0"
Damit ist die erste Datenreihe fertig (hier am Beispiel der Säule „ÖVP“, d.h. Variable punkt = 6):
| Klick auf Schalter „ÖVP“ zeichnet zuerst die Säule für 2004 (Datenpunkt 1, Datenreihe 1) und darüber die Datenbeschriftung („54,9“) ... ... anschließend die Säule für 1999 (Datenpunkt 1, Datenreihe 2) mit Datenbeschriftung („45,7“) |
Hier noch einmal der komplette Quellcode:
Sub zeichnen(punkt As Integer)
Dim aktzeit As Single
Dim daten As SeriesCollection
Dim i As Integer
Dim max04 As Single
Dim max99 As Single
Set daten = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
max04 = ActiveSheet.Range("C" & punkt).Value
For i = 0 To max04
aktzeit = Timer
ActiveSheet.Range("D" & punkt).Value = i
Do While Timer < aktzeit + 0.01
DoEvents
Loop
Next
ActiveSheet.Range("D" & punkt).Value = max04
daten(1).Points(punkt - 5).HasDataLabel = True
daten(1).Points(punkt - 5).DataLabel.NumberFormat = "#0.0"
max99 = ActiveSheet.Range("F" & punkt).Value
For i = 0 To max99
aktzeit = Timer
ActiveSheet.Range("G" & punkt).Value = i
Do While Timer < aktzeit + 0.01
DoEvents
Loop
Next
ActiveSheet.Range("G" & punkt).Value = max99
daten(2).Points(punkt - 5).HasDataLabel = True
daten(2).Points(punkt - 5).DataLabel.NumberFormat = "#0.0"
Set daten = Nothing
End Sub
Private Sub bovp_Click()
zeichnen punkt:=6
End Sub
-------
Private Sub bspo_Click()
zeichnen punkt:=7
End Sub
-------
Private Sub bfpo_click()
zeichnen punkt:=8
End Sub
-------
Private Sub bgruen_Click()
zeichnen punkt:=9
End Sub
-------
Private Sub bdiv_Click()
zeichnen punkt:=10
End Sub
-------
Private Sub bClear_Click()
Set daten = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
For i = 6 To 10
ActiveSheet.Range("D" & i).Value = 0
ActiveSheet.Range("G" & i).Value = 0
Next
daten(1).HasDataLabels = False
daten(2).HasDataLabels = False
End Sub
No comments:
Post a Comment