1
Teil 1: VBA
Visual Basic for Applications (VBA) von Microsoft ist eine aus dem von Microsoft entwickelten Basic-Dialekt Visual Basic (VB)
abgeleitete ereignisorientierte Skriptsprache
2
Fortran u.ä. Programme sind sequentieller Programme.
Man gibt das Programm ein, startet und wartet auf das Ergebnis. Meist werden dann die Eingabedaten von einer Datei gelesen (Input File) und Ausgabedaten auf eine andere Datei geschrieben (Output File).
Für die Bearbeitung großer Datenmengen, insbesondere auf Großrechnern, ist das nach wie vor der Normalfall.
Input File
Output File
Fortran
3
VBA ist ein ereignisorientiertes Programm.
Ereignisorientierte Programme "warten" nach dem Start auf Mitteilungen des Anwenders.
Der Nutzer steuert interaktiv (durch Klicken) den Programmablauf.
Ereignisorientierte Programme besitzen dadurch eine andere Struktur als sequentielle Programme: anstelle einer Liste von Befehlen, die dem geplanten Ablauf entsprechend geordnet sind, hat man hier eine Sammlung von Objekten, Prozeduren und Funktionen.
4
Objekte stellen einen abstrakten Begriff dar. Sie sind eine Zusammenfassung aus Daten und den Funktionen.
Das bedeutet, Objekte sind alle Teile, die man in Excel sehen kann,
•z.B. die Arbeitsmappe, •das Tabellenblatt •und die Zelle als kleinste Einheit in Excel.
Aus die Objekte kann man zugreifen, man kann sie programmieren und steuern. Objekte können auch bestimmte Eigenschaften haben.
Objekte in VBA
5
Machen wir z.B. ein leeres Excel Arbeitsblatt auf und drücken Sie Alt + F11 oder installieren einen Command-Buttom und klicken darauf. In beiden Fällen kommen wir in die VBA-Entwicklungsumgebung.
Wir klicken die Tabelle 1 an und wollen einmal eine Eigenschaft dieses Arbeitsblattes auswählen. Geben Sie z.B. in das Feld Scroll Area und geben dort $A$1 : $D$20 ein, so ist nur der entsprechende Bereich adressierbar.
Eigenschaften: dahinter verbergen sich die Merkmale eines Objektes. Z.B. die Formatierung einer Zelle.
6
Was verbirgt sich hinter dem Ausdruck Methode?
Methode: Was kann man mit den Objekten machen? Für eine Arbeitsmappe z.B. Öffnen, Drucken, Speichern, Schließen, etc.
Beipiel: Dem Bereich "A1:B2" in einem Tabellenblatt soll ein symbolischer Name, d.h. eine Variable, zugewiesen werden, um die weitere Programmierung zu vereinfachen:
Private Sub CommandButton1_Click()Dim Auswahl As Range
Set Auswahl = Tabelle1.Range("A1:B2")Auswahl.Select
Auswahl.Interior.ColorIndex = 8Jetzt können wir z.B. die definierte Tabelle auslesen.
TextBox1 = Auswahl(1, 1)TextBox2 = Auswahl(1, 2)TextBox3 = Auswahl(2, 1)TextBox4 = Auswahl(2, 2)
End Sub
Auf den Range Befehl kommen wir noch
7
vereinbart das Objekt Auswahl vom Typ Range, das geeignet ist, einen Tabellenbereich aufzunehmen. weist dem Objekt „Auswahl“ den Bereich "A1:B2" des Tabellenblattes mit dem Namen Tabelle1 zu.
Methode. Der Bereich wird mit einer speziellen Anweisung (Methode) die nur in Verbindungmit dem Objekt anwendbar ist. Auswahl.Select
Private Sub CommandButton1_Click()Dim Auswahl As RangeSet Auswahl = Tabelle1.Range("A1:B2")
Auswahl.Select
Oder wir können z.B. die definierte Tabelle auslesen. TextBox1 = Auswahl(1, 1)TextBox2 = Auswahl(1, 2)TextBox3 = Auswahl(2, 1)TextBox4 = Auswahl(2, 2)End Sub
8
Eigenschaft. Die Farbe des Hintergrundes ist z.B. eine Eigenschaft.
Die Farbe ändern wir z.B. mit:
Auswahl.Interior.ColorIndex = 8
Das Wort Interior bezeichnet das Innere,d.h. den Hintergrund, des Zellbereiches. Der Hintergrund selbst wiederum ist ein Objekt. ColorIndex ist der Name eines der möglichen Attribute, bzw. eine Eigenschaft des Objektes Interior. Der Wert der Eigenschaft ist 8, das entspricht der Farbe Zyan.
Tabelle1.Range("A1:B2").Interior.ColorIndex = 8
Aufgabe: Erstellen Sie eine Bildlaufleiste, mit der Sie die Farbe des Datenberichs kontinuierlich von 1 bis 10 ändern können.
Jetzt wollen wir z.B. die Farbe des Hintergrundes des Tabellenblattes ändern
9
So genug des „Geplänkels“ lassen Sie uns mit einem richtigen Beispiel beginnen
Wir wollen eine Ampel programieren!
10
Makros: Ampellichter an- und ausschaltenAls erstes wollen wir die einzelnen Lichter der Ampel an- und ausschalten.Zunächst wird Excel gestartet. Wir zeichnen eine Ampel aus drei Kreisen und einem Rechteck und erstellen eine Tabelle für den Zustand der Lichter.
Aufzeichnung beginnen. Das Einschalten des roten Lichtes und die farbliche Markierung von dem Eintrag Rot „an" werden aufgezeichnet.
Makro-Recorder starten. Extras -> Makro -> Aufzeichnen ...Folgende Maske erscheint, der Name des Makros wird angegeben, hier "rot_an":
11
Jetzt kann man dieses Makro direkt in an eine Befehlsschaltfläche ankoppeln:Damit erzeugen wir eine „Prozedur“, sie beginnt mit dem Wort „Sub“
Private Sub CommandButton4_Click()rot_an
End Sub
Das Gleiche machen Sie nun bitte mit rot aus
Wir kommen gleich auf den allgemeinen Begriff der Prozedur zurück
12
Was steht denn da nun eigendlich in dem Makro zum Schalten der roten Ampel?
ActiveSheet.Shapes("Oval 2").Select Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid
Range("G4").Select
ActiveCell.FormulaR1C1 = "an"
Range("G4").Select Selection.Font.Bold = True Selection.Font.ColorIndex = 3
End Sub
Kreis "Ellipse" auswählenFüllfarbe rot
Füllung sichtbarFüllung monochrom
Zelle G4 auswählenText "an" in die ausgewählte
Zelle eintragen
Zelle G4 auswählenTextfarbe rot und fett
Das hätten wir auch mit realisieren können
13
Damit Sie an möglichst viele Erfolgserlebnisse kommen, machen Sie sich Makrosfür Gelb an und Gelb aus, Grün an und Grün aus und binden Sie diese an die jeweiligen Steuerelemente an oder kopieren Sie sich den Quellcode des Makros rot an und veränderndie Bezugsobjekte so , dass Sie auch grün und gelb schalten können
Es gibt nun 6 Makros. Oder Sie haben aus dem einen Makro weitere 5 Prozedurengemacht, die wie gewünscht agieren.
Sub gelb_an etc.
14
Eine Ampelschaltung besteht aus definierten Lichtkombinationen. Dazu müssen Lichter an- und ausgeschaltet werden:
Jetzt wollen wir die Ampel von Halt hin zu Fahrt steuern
Private Sub CommandButton2_Click()rot_anFor i = 1 To 1000000j = i ^ 2Next igelb_anFor i = 1 To 1000000j = i ^ 2Next irot_ausFor i = 1 To 1000000j = i ^ 2Next igruen_anFor i = 1 To 1000000j = i ^ 2Next igelb_aus
Hier wird eine „Pause“ programmiert.
Aber was bedeutet eigendlich
For i= ..... Next i
15
Schleifen mit Schleifenindex. Die For - Next - Schleife
For i = start To ende Step Inkrement<Anweisung>
Next i
Beispiel: Skalarprodukt
3
1
**
i
ii babac
Dazu kommen wir gleich
Private Sub CommandButton1_Click()Dim a(2), b(2)a(1) = Cells(2, 1)a(2) = Cells(3, 1)b(1) = Cells(2, 2)b(2) = Cells(3, 2)TextBox1 = "Vektor a: " & a(1) & " " & a(2)TextBox2 = "Vektor a: " & b(1) & " " & b(2)
sp = 0For i = 1 To 2sp = sp + (a(i) * b(i))Next iMsgBox "Das Skalarprodukt beträgt " & spEnd Sub
Einfachste Art der Ausgabe
16
Mit der MsgBox können wir nun Daten sehr einfach ausgeben,
Gibt es auch eine einfache Funktion Daten abzufragen und in das Programm zu bekommen?
InputBox:
Private Sub CommandButton2_Click()a = InputBox("a eingeben")Cells(20, 1) = aEnd Sub
17
Beispiel For ... Next: Farbtabelle ausgeben
Private Sub CommandButton1_Click()
For bfarbe = 1 To 10 Cells(bfarbe, 1) = bfarbe Cells(bfarbe, 2).Interior.ColorIndex = bfarbe Next bfarbeEnd Sub
18
Private Sub CommandButton2_Click()For i = 1 To 36 Step 2For j = 1 To 36 Step 2
Cells(i, j).Interior.ColorIndex = Rnd * 10
Next j Next iEnd Sub
19
VerzweigungenIF - Then - ElseIf
If <Bedingung1> Then<Anweisung1>'Bedingung1 erfüllt
elseif <Bedingung2><Anweisung2>'Bedingung2 erfüllt.
else<Anweisung3>'keine Bedingung erfüllt.
End If
Wenn wir nun die For ... Next Schleife haben können wir uns auch Verzweigungen ansehen
20
Jetzt Beispiel: Teilchen in Box
21
Private Sub CommandButton1_Click()Rem Lesen der x- Geschwindigkeitvx = Cells(4, 1)Rem Lesen der y- Geschwindigkeitvy = Cells(5, 1)Rem Setzen des Anfangspunktesx1 = 2y1 = 2For i = 1 To 500x2 = x1 + vxy2 = y1 + vyy1 = y2x1 = x2Cells(1, 1) = x1Cells(2, 1) = y1If x1 > 3.9 Then vx = vx * -1If y1 > 3.9 Then vy = vy * -1If x1 < 0.1 Then vx = vx * -1If y1 < 0.1 Then vy = vy * -1Application.ScreenUpdating = TrueNext i
End Sub
Es funktioniert, sieht aber nicht so „schön“ aus
22
Private Sub CommandButton2_Click()Rem Lesen der x- Geschwindigkeit vx = Cells(4, 1)Rem Lesen der y- Geschwindigkeit vy = Cells(5, 1)Rem Setzen des Anfangspunktesx1 = 2y1 = 2
Application.Calculation = xlCalculationManualFor i = 1 To 500x2 = x1 + vxy2 = y1 + vyy1 = y2x1 = x2Cells(1, 1) = x1Cells(2, 1) = y1If x1 > 3.9 Then vx = vx * -1If y1 > 3.9 Then vy = vy * -1If x1 < 0.1 Then vx = vx * -1If y1 < 0.1 Then vy = vy * -1Worksheets("Tabelle1").CalculateNext iApplication.Calculation = xlCalculationAutomaticEnd Sub
23
„Schön“ sind auch Select Case VerzweigungenSelect - Case
Select Case a Case 1 b = 20 : c =
30 Case 2
b = 21 : c = 31
Case Else b = 0 : c = 0
End SelectPrivate Sub CommandButton1_Click()a = Cells(1, 1)Dim Text As StringSelect Case aCase 1Text = "Fall a"Case 2Text = "Fall b"Case 3Text = "Fall c"End SelectMsgBox Text
End Sub
24
Vom Beispiel wieder zur Theorie:
Bevor eine Variable benutzt wird, sollte sie im Deklarationsteil der Prozedur deklariert werden. Dies ist in Visual Basic für Applikationen nicht zwingend erforderlich, sollte aber, um zum Beispiel Tippfehler zu vermeiden, immer gemacht werden. Variablennamen können aus bis zu 255 Zeichen des Alphabets einschließlich Umlauten und Unterstrich bestehen. Sonderzeichen (!, @, &, $, #) und Leerzeichen sind unzulässig. Es wird nicht zwischen Groß- und Kleinbuchstaben unterschieden. Das erste Zeichen muss ein Buchstabe sein.
Variable
25
Damit eine Variablendeklaration immer erzwungen wird, sollte am Anfang jedes Moduls die Anweisung Option Explicit eingefügt werden. Sie können dies als Voreinstellung wählen, in dem Sie im VBA-Menü Extras/Optionen auswählen und in dem erscheinenden Dialog die Option Variablendeklaration erforderlich markieren.
Explizite Variablendeklaration:
26
$
27
Sub ExplizitDim strvalue as stringStrvalue =„Explizit“Debug.Print strvalueEnd sub
Sub Implizit
Strvalue$ =„Implizit“Debug.Print strvalueEnd sub
28
Vom Beispiel wieder zur Theorie:
Prozeduren und FunktionenIn VBA gibt es zwei unterschiedliche Arten von Teilprogrammen:
Prozeduren und Funktionen.
Sie unterscheiden sich darin, ob sie einen Wert zurückgeben oder nicht.
1) ProzedurenEine Prozedur beginnt mit dem Schlüsselwort Sub. In unserem Ampelbeispiel haben wir ausschließlich Prozeduren verwendet. Die syntaktische Verwandtschaft mit FORTRAN fällt auf.
29
Erzeugen wir eine neue Prozedur
Am Anfang einer Konstantendeklaration kann der Modifizierer "Public" oder der Modifizierer "Private" verwendet werden, um festzulegen, daß die Konstanten in allen Modulen bzw. nur in ihrem Modul gültig sein sollen. Deklaration im Modulkopf eines Standardmoduls als PublicDie Variable gilt für alle Prozeduren der Arbeitsmappe, soweit das die Prozedur enthaltene Modul nicht als Private deklariert ist.
30
31
32
PublicDurch das Schlüsselwort Public
wird der Geltungsbereich von Variablen, Funktionen und Prozeduren auf alle Module ausgeweitet.
Publec Function SProd (a() As Double, b() As Double)
PrivatDurch das Schlüsselwort Privat
wird der Geltungsbereich von Variablen, Funktionen und Prozeduren auf einen Module beschränkt.
Privat Function SProd (a() As Double, b() As Double)
33
FunktionenFunktionen geben einen Wert zurück. Der Wert wird einer Variablen mit dem Namen der Funktion zugewiesen.
Function StatusRot() As Integer' gibt die aktuelle Farbe des Rotlichtes zurückActiveSheet.Shapes("Oval 2").SelectStatusRot = Selection.ShapeRange.Fill.ForeColor.SchemeColorEnd Function
34
Sub TestRot()Dim Farbe As IntegerFarbe = StatusRot ' Funktionsaufruf rechts vom KommaJetzt z.B. Ausgabe in ein TextfeldEnd SubZusätzlich wurde hier der Datentyp Integer verwendet. Siehe dazu weiter unten.
VBA FunktionenEs gibt eine große Menge von fertigen Funktionen in VBA. Den besten Überblick erhält man in derOnline-Sprachreferenz Hilfe -> Visual Basic Sprachreferenz
Die Funktion rufen wir nun z.B. in einer Prozedur auf:
35
Nützliche Funktionen2.10.1 InputBoxDiese Funktion öffnet ein Dialogfenster mit einemTextfeld, in das eine Zeichenfolge eingeben werdenkann. Die Funktion gibt einen Wert vom Typ stringzurück. Wenn der Datentyp der Variablen, die denRückgabewert der InputBox erhält, von einemanderen Datentyp als string ist, wird eine Typumwandlung durchgeführt (siehe Beispiel).SyntaxInputBox(prompt[, title] [, default] [, xpos] [, ypos])Beispiele:Dim i As Integeri = InputBox("Geben Sie eine Ganzzahl ein")Dim str As Stringstr = InputBox("Geben Sie eine Zeichenfolge ein")HinweiseMit dem zweiten Parameter title wird der Text in der Titelleiste der InputBox bestimmt. Derdritte Parameter default ermöglicht einen Voreinstellungswert in das Textfeld zu schreiben.
36
So, nun wollen wir das bisher Gelernte doch mal für die Bauphysik anwenden:
Beispiel : Instationäre Temperaturentwicklung in einem Bauteil
Hierzu : Finite Differenzenmethode
37
38
Dies ist die erste Ableitung nach der Zeit = f´(t)
Dies ist die erste Ableitung nach der Zeit = f´(t)
Doch wie sollen wir das in einem Programm umsetzen? Was ist die erste bzw. zweite Ableitung denn?
)()()()´( tOtfttftf t
Ohne Herleitung:
39
Wie gehen wir nun vor: 1.) wir definieren und ein festes Gitter in x Richtung, so lang wie das Bauteil istz.B. 20 cm lang. Dazu erstellen wir 20 Gitterpunkte, an denen die Temperatur in dem Bauteil berechnet werden soll. Der Abstand zwischen 2 Rechenpunkten hat die Länge 1 cm.
x1 2 3 4 5 6 .............................................20
Jetzt müssen wir die Anfangstemperaturverteilung in der Wand vorgeben. An der Innenwand soll eine Temperatur von 20 C herschen, die Wand selbst soll auch 20 C warmsein. T1(1) bis T1(19) = 20Außen, d.h. T1(20) herscht eine Temperatur von -10 C.
40
Damit haben wir das Diagramm für die Anfangsbedingung
Anfangs-temperatur
41
Jetzt geht es darum die Änderung der Temperaturverteilung T(xi) z.B. 1 sec nach dem Startzu berechnen.
Wir wissen:
Bzw. daraus T2 = T1 + T/t *t = T1 + /( cp) ²T/x² *t
Mit:
Folgt: T2(x) = T1(x) + /( cp) t * (T1(x+1) – 2 T1(x) + T1(x-1))/x²
T2(x) = T1(x) + /( cp) t * (T1(x+1) – 2 T1(x) + T1(x-1))/ x²
T2(x) = T1(x) + t * D * (T1(x+1) – 2 T1(x) + T1(x-1))
42
Erster Schritt: Diffusionskoeffizienten z.B. in Excle berechnen und an Programm übergeben
43
Und nun geht es los!
Rem Einlesen der Difusionskoeffizientendiff = Cells(8, 7)delta_t = Cells(9, 7)
Rem hier kommt die Prognoseschleife
For t = 1 To 400 Step delta_t
For i = 7 To 24 (die Temperaturen gehen von 6 bis 25, dh. Die Wandtemperatur bleibt fest)
Cells(i, 4) = Cells(i, 3) + delta_t * diff * (Cells((i + 1), 3) - 2 * Cells(i, 3) + Cells(i - 1, 3)) /0.0001
Next i
2
),(),(),(),( 2
xD
ttxxtxtxx
ttx
44
Nach einem Zeitschritt wurde nun die Temperaturverteilung in der Wand zum Zeitpunkt t2 berechnet. Das ist die Ausgangsverteilung für die Berechnung der Temperatur zum Zeitpunkt t3
D.h. nach jeder Berechnung müssen wir die „neue“ Temperaturverteilung über alte schreiben
Rem jetzt werden die Werte T(t+ dt) an T(t) übergebenFor i = 7 To 24Cells(i, 3) = Cells(i, 4)Next i
45
Private Sub CommandButton1_Click()
Rem wir schreiben die Anfangstemperatur in die Spalte 3 ab der Zeile 6For i = 6 To 24Cells(i, 3) = 20Next iCells(25, 3) = -10Rem Einlesen der Difusionskoeffizientendiff = Cells(8, 7)delta_t = Cells(9, 7)
Rem hier kommt die Prognoseschleife
For t = 1 To 400 Step delta_tFor i = 7 To 24Cells(i, 4) = Cells(i, 3) + delta_t * diff * (Cells((i + 1), 3) - 2 * Cells(i, 3) + Cells(i - 1, 3)) / 0.0001Next i
Rem jetzt werden die Werte T(t+ dt) an T(t) übergebenFor i = 7 To 24Cells(i, 3) = Cells(i, 4)Next i
Application.ScreenUpdating = True
Next tEnd Sub
46
47
Leider ist die Lösung dieser finiten Differenzgleichung sehr abhängig von der Wahl der Diskretisierung.
Das Verfahren ist nur stabil wenn:Das Neumann-Kriterium eingehalten ist, d.h. D * Dt/Dx² <0,5
48
Open: Öffnen einer DateiOpen <Dateiname> For <Modus>
As #<Kanal>
<Modus> : Input Daten lesenOutput Daten schreiben
<Kanal> : Die Kanalnummer regelt den Dateizugriff.
Beispiel:Open „e:\ergebnis.dat" For Output As #1
Close: Schließen einer DateiClose #<Kanal>
<Kanal> : Die Kanalnummer der zu schließenden Datei.
Beispiel:Close #1
Jetzt wollen wir das Ergebnis der Berechnung „auf Kommando“ einmal als File abspeichern