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