Informatik - VBA - VBA Excel Übungen

Übungsaufgaben für das Programmieren in VBA

Die folgenden Seiten enthalten Programmieraufgaben für das Programmieren mit VBA. Die Aufgaben wurden ursprünglich für das Programmieren in C konzipiert, wo die Kommandozeile als Schnittstelle zum Benutzer, also für Ein-/Ausgaben dient. Einige Beispiele und Formulierungen sind daher für eine VBA Umgebung etwas ungewöhnlich und müssen etwas adaptiert werden.

In VBA kann für die Eingabe eine InputBox und für die Ausgabe eine MsgBox verwendet werden. Oft bietet sich an, die Ein- und Ausgabedaten auf einem Tabellenblatt abzulegen. Später können Sie als Benutzerschnittstelle auch Dialogfenster (Userforms) verwenden.

Datentypen und E/A

Schreiben Sie zunächst einige kleine Programme zum Thema Ein-/Ausgabe und Datentypen. Informieren Sie sich über die Online-Hilfe über die Konvertierungsfunktionen. Eine Alternative zu den Funktionen C.... ist die Funktion Val.

Produkt von 2 Zahlen

Schreibe ein Programm, das 2 Zahlen mittels InputBox einliest und das Produkt der Zahlen in einer MsgBox ausgibt. Verwenden Sie für die Zahlen unterschiedliche Datentypen.

Ausgaben zusammenstellen

MsgBox kann nur eine Zeichenkette ausgeben. Experimentiere mit verschiedenen Möglichkeiten eine Zeichenkette für die Ausgabe zusammenzustellen. Beispiel dazu:

Sub test()
  Dim text As String
  Dim x As Single
  MsgBox "x = " & x & " m"
  text = "x = " & x & " m"
  MsgBox text
  text = "x = " & Format(x, "##0.00 m")
  MsgBox text
  text = "Ergebnis:" & vbCrLf & "x = " & Format(x, "##0.00 m")
  MsgBox text, , "Ausgaben mit einer MsgBox"
End Sub

Vom Algorithmus zum Programm

Wurzelziehen nach Archmides

Archimedes (um 287-212 v. Chr.) hat für die Berechnung der Quadratwurzel einer Zahl a folgende Iterationsformel angegeben:

Ein Algorithmus zur Verwendung dieser Formel lautet:

  Zahl a lesen
  xneu = a
  Wiederhole 
    xalt = xneu
    xneu = (xalt + a/xalt)/2
  bis |xneu - xalt| < epsilon

Schreibe ein Programm dazu.

Anzahl der Ziffern einer ganzen Zahl bestimmen

Der folgende kurze Algorithmus bestimmt die Anzahl der Ziffern (w) einer ganzen Zahl z:

  w = 1, s = 10
  Solange s < z wiederhole
    w = w + 1
    s = s * 10

GGT

Führe folgenden Pseudocode für die Berechnung des größten gemeinsamen Teiler (GGT) zweier natürlichen Zahlen a, b aus. Wähle dazu zwei geeignete Werte für a und b.

  a und b aus Zellen lesen;
  Wiederhole
    Berechne den Rest r der Ganzzahldivision a / b.
    Ersetze a durch b.
    Ersetze b durch den berechneten Rest.
  bis Rest r gleich Null ist.
  Der letzte Wert von a ist der GGT.
  Resultat in Zelle schreiben

Schreibe ein VBA Programm zu diesem Pseudocode

Fibonacci-Zahlen

  amax einlesen;
  a = 1;
  b = 1;
  Wiederhole
    a ausgeben;
    c = a + b;
    a = b;
    b = c;
  solange a < amax;

Schreibe ein Programm zu diesem Pseudocode

Quadratische Gleichung

Ein Programm soll aus den Zellen B1 und B2 die Koeffizienten einer quadratischen Gleichung lesen und die Lösungen berechnen. Die Lösungen sind in den Zellen B3 und B4 einzutragen.

	A	B	C
	p =	-4.0
2	q =	8.0
3	x1 =
4	x2 =

Zapfenrechnung

Ein Programm soll in einem Tabellenblatt die einzelnen Resultate einer Zapfenrechnung eintragen

  Anfangswert für z einlesen und in Zelle (1,1) schreiben
  zeile = 2
  Für m = 2 bis 9
    z = z * m
    z in Zelle(zeile,1) schreiben
    zeile = zeile + 1
  Für m = 2 bis 9
    z = z / m
    z in Zelle(zeile,1) schreiben
    zeile = zeile + 1

Beschleunigte Bewegung

Schreibe ein Programm, das für eine gleichmäßig beschleunigte Bewegung eine Tabelle für den Weg s(t) = ½a t² in Abhängigkeit von der Zeit t ausgibt.
Die Beschleunigung, die Schrittweite und der Endwert der Zeit soll durch den Benutzer im Dialog gewählt werden.

Beispiel: a = 2 m/s²

	A	B
1	t	s(t)
2	0.0	0.0
3	1.0	1.0
4	2.0	4.0

s(t) -> v(t)

Ein Programm soll für eine gegebene Funktion s(t) von tmin bis tmax in Abständen tstep den Funktionswert s(t) und die Momentangeschwindigkeit v(t) berechnen und in einer Tabelle die Werte t, s(t) und v(t) eintragen.

Berechnung der Momentangeschwindigkeit:

Test mit folgenden Werten:

s(t) = A sin (w t) A = 2 m w = 2 s^-1 tmin = 0 s, tmax = 4 s, tstep = 0.2 s, D t = 0.05 s

Algorithmus in Pseudocode, Programm

Untersuche durch einen Vergleich mit dem exakten Resultat v(t) = A cos(wt),
wie genau die Berechnung der Geschwindigkeit v(t) ist!

Eine Liste von Zahlen auf dem Tabellenblatt bearbeiten

Ausgehend von Daten auf einem Tabellenblatt bieten sich eine Menge von Übungsaufgaben an. Sie können z.B. viele der Tabellenblattfunktionen selber in einem Programm ausprogrammieren. Wenn Sie dann das Resultat Ihres Programmes mit dem Resultat der Tabellenblattfunktion vergleichen, wissen Sie auch, ob Ihr Programm korrekt arbeitet.

Zahlenkolonne verarbeiten

In der Spalte A sind fortlaufende Zahlenwerte eingetragen.
Folgender Programmcode ermittelt den Bereich, der Zahlenwerte enthält:

    Sub Bereich()
      Dim ersteZeile As Integer, letzteZeile As Integer
      Dim zeile As Integer
     
      Sheets("Tabelle1").Activate
      zeile = 0
      Do
        zeile = zeile + 1
      Loop While IsEmpty(Cells(zeile, 1))
      ersteZeile = zeile
      Do
        zeile = zeile + 1
      Loop Until IsEmpty(Cells(zeile, 1))
      letzteZeile = zeile - 1
      MsgBox "Die Zahlen beginnen in der Zeile " & ersteZeile _
        & vbCrLf & "und enden in der Zeile " & letzteZeile
    End Sub

Schreibe je eine Prozedur (Sub) welche folgende Aufgaben löst:

Die kleinste bzw. größte Zahl suchen.
Feststellen, ob eine bestimmte Zahl in der Liste enthalten ist.
Den Mittelwert der Zahlen berechnen.
In die Spalte daneben das Resultat einer Funktion y(x) eintragen.
Eine Zahl aus der Liste entfernen, und die anderen Zahlen nachrücken.
Eine Zahl in die Liste einfügen.
Die Zahlen sortieren.
Feststellen, wie viele Zahlen kleiner als zmin sind.

Algorithmus und Programm entwickeln

Intervallhalbierung

Schreibe ein Programm, welches die Nullstelle einer Funktion f(x) nach dem Intervallhalbierungsverfahren ermittelt.
Ausgehend von einem vorgegebenen Intervall [a,b] wird durch fort laufende Halbierung jenes Teilintervalles, in welchem die Nullstelle liegt, die Nullstelle immer mehr "eingekreist". Das Verfahren wird angebrochen, wenn |f(x)| < ε wird, oder wenn nach einer vorgegebenen Anzahl von Iterationen keine Nullstelle gefunden wurde. Die Funktion f(x), Argument und Resultat vom Typ Double, soll bekannt sein.
Stelle den Algorithmus zunächst in einem Struktogramm dar und schreibe anschließend ein Programm dazu.

Entladung eines Kondensators

Bei der Entladung eines Kondensators über einen Widerstand gilt

u_c(t) = - u_R(t) = - R i(t)

d.h. i(t) = - u_c(t)/R

Der Strom i(t) vermindert die Ladung am Kondensator im Zeitintervall D< t um D q = i D t. Die Spannung am Kondensator ändert sich dadurch um D u_c = D q / C. Mit dieser neuen Spannung am C ergibt sich ein neuer Strom i(t), usw.

Schreibe ein Programm, das i(t) und uc(t) in Abständen von t ermittelt und die Resultate in ein Tabellenblatt einträgt.

	A	B	C
1	t	i(t)	u_c(t)
2
3
4

Löse die Aufgabe auch ohne VBA-Programm, also nur mit Formlen in den Zellen.

Baywatch (oder das Prinzip von Fermat)

Ein Rettungsschwimmer am Punkt A am Strand will eine ertrinkende Person am Punkt B im Wasser retten.

Um jene Strecke zu ermitteln, über die ein Rettungsschwimmer in der kürzest möglichen Zeit von A nach B kommen kann, läßt man ein Programm die benötigte Zeit für verschiedene Strecken berechnen. Die Strecke ist durch die Koordinaten der Punkte A (x₁, y₁) , B (x₂, y₂) und den Punkt P (x₀ , 0 ) gegeben. Die Punkte A und B sind fix, den Punkt x₀ läßt man zwischen den Punkten A und B auf der x-Achse mit einer Schrittweite von D x wandern und berechnet für jedes x₀ die für diese Strecke benötigte Zeit. Zu dieser Aufgabe ist ein Algorithmus zu entwerfen und ein VBA-Programm zu schreiben. Die Lage der Punkte A und B ist z.B. durch (0, -20 m) und (50 m, 30 m) gegeben, D x soll im Dialog gewählt werden können.

Das Beispiel eignet sich gut, um die Rechnung als Animation in einem Diagramm darzustellen.

Lösung

Würfeln mit Zufallszahlen

Ein Programm soll

n Zufallszahlen im Bereich 1 bis 6 (ganze Zahlen) in die erste Spalte einer Tabelle eintragen.
Häufigkeitsverteilung ermitteln

Es soll ermittelt werden, wie oft die Zahlen 1 bis 6 in den erzeugten Zufallszahlen vorkommen. Verwenden Sie für die Ausgabe des Resultats wieder das Tabellenblatt

Lottozahlen

Schreibe für folgende Aufgaben je eine Funktion

Datenfeld initialisieren

Ein Datenfeld mit 45 (oder 46) Elementen so initialisiert werden, daß alle Elemente 0 oder False enthalten.

Lotto-Tip

Das Programm soll einen Lottotip zusammenstellen. Die 6 Lottozahlen sind auf dem Tabellenblatt auszugeben. Für die Erzeugung der Zufallszahlen verwenden Sie die Bibliotheksfunktion rand und eine geeignete Rechenoperation. Um sich zu merken, ob eine Zahl bereits gezogen wurde, verwenden Sie das Datenfeld aus Aufgabe a). Sobald eine Zahl gezogen wurde, wird sie im Datenfeld mit 1 oder True markiert.

Stack

Entwickle den Pseudocode für ein Programm, das einen Stack verwaltet. In einem Stack kann man Zahlen abspeichern und wieder entfernen, wie man Papier auf einem Stapel ablegt. Das Blatt, das zuletzt oben abgelegt wurde, muß als erstes wieder entfernt werden. Den Stack realisieren Sie mit einem Vektor für NMAX Zahlen, dessen Zustand am Tabellenblatt immer sichtbar sein soll. Das Programm soll dem Benutzer in einer Wiederholung folgende Möglichkeiten anbieten:

Zahl auf Stack ablegen
Zahl aus Stack entfernen (auslesen)
Programm beenden

Sortieren durch direktes Auswählen

Diese Methode um z.B. Zahlen nach aufsteigender Größe zu sortieren, funktioniert folgendermaßen :

1. Suche aus den n Zahlen die kleinste Zahl heraus

2. Setze diese Zahl an die erste Stelle, d.h. diese Zahl wird x(1)

Schritt 1 und 2 wird für die restlichen n-1 Zahlen, dann für die restlichen n-2 Zahlen usw. wiederholt, bis nur noch ein Element - das größte - übrigbleibt.

Zellen formatieren

Ergänzen Sie die bisherigen Programme durch automatîsches Formatieren der Zellen. Um zu lernen, wie man Zellen formatiert, zeichnen Sie zugehörige Makros auf und studieren die Makros. Oft kann man die aufgezeichneten Makros stark reduzieren.

Aufgezeichneter Makro:

  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection.Borders(xlEdgeTop)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection.Borders(xlEdgeBottom)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection.Borders(xlEdgeRight)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection.Borders(xlInsideVertical)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection.Borders(xlInsideHorizontal)
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlBottom
   .WrapText = False
   .Orientation = 0
   .ShrinkToFit = False
   .MergeCells = False
  End With
  With Selection.Interior
   .ColorIndex = 34
   .Pattern = xlSolid
  End With
  Selection.Font.Bold = True

Vereinfachter Makro:

  Cells(5, 5).Select
  With Selection.Borders
   .LineStyle = xlContinuous
   .Weight = xlThin
   .ColorIndex = xlAutomatic
  End With
  With Selection
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlBottom
   .WrapText = False
   .Orientation = 0
   .ShrinkToFit = False
   .MergeCells = False
  End With
  With Selection.Interior
   .ColorIndex = 34
   .Pattern = xlSolid
  End With
  Selection.Font.Bold = True

Funktion: signum

Eine Funktion signum soll das Vorzeichen einer reellen Zahl bestimmen.

Funktion: Liegt x im Intervall [ xmin, xmax ]

Eine Funktion soll für einen Wert x prüfen, ob er innerhalb des Bereiches xmin bis xmax liegt.

Funktion: In welchem Quadrant liegt ein Punkt

Für einen Punkt mit den kartesischen Koordinaten x, y ist zu ermitteln, in welchem Quadranten (1,2,3,4) er sich befindet.

Funktion: Winkel zur x-Achse

Für einen Punkt mit den kartesischen Koordinaten x, y ist der Winkel zu berechnen, den er mit der x-Achse einschließt.

Funktion: Winkel einer komplexen Zahl

Eine Funktion soll den Winkel j berechnen, den ein komplexer Zeiger z = (re,. im) mit der reellen Achse bildet.

Funktion: Geradengleichung

Eine Funktion soll eine Gerade durch zwei Punkte berechnen.
Gegeben: die Koordinaten der beiden Punkte
Gesucht: Steigung k und Achsenabschnitt d der Gerade

Funktion: Winkel zwischen zwei Geraden

Eine Funktion soll den Absolutbetrag des kleineren Winkels zwischen zwei Geraden berechnen. (Der Wertebereich der BibliotheksFunktion atan(x) ist -p /2 bis +p /2 .)

Funktion: Schnittpunkt von zwei Geraden

Der Schnittpunkt (x, y) zweier Geraden ist zu berechnen.
Gegeben: k1, d1 und k2, d2
Gesucht: Schnittpunkt x, y und ein Fehlercode (falls es keinen Schnittpunkt gibt)

Lösung der letzten drei Aufgaben

Funktion: Inverse einer komplexen Zahl

Eine komplexe Zahl z = (re, im) soll durch eine Funktion cinv in die inverse Zahl
y = 1/z umgewandelt werden.

Funktion: Betrag einer komplexen Zahl

Eine Funktion cabs soll den Betrag einer komplexen Zahl berechnen.

Funktion: isupper

Eine Funktion soll prüfen, ob ein Zeichen ein Großbuchstaben ist.

Funktion: Rwarm

Eine Funktion soll den Widerstandswert Rwarm bei der Temperatur J berechnen.

R_warm = R₂₀ ( 1 + αΔδ + βΔδ² )

Argumente der Funktion:
Widerstand bei 20 °C
Die Temperaturkoeffizienten α und β .
Die Temperaturdifferenz Δδ
Schreibe ein Programm, das die Widerstandswerte in einem wählbaren Temperaturbereich mit wählbarer Schrittweite in Form einer Tabelle am Bildschirm ausgibt, bzw. auf eine Datei schreibt.

Funktion: Parallelschaltung

Eine Funktion soll den Ersatzwiderstand zweier parallel geschalteter Widerstände berechnen.

Funktion: Schaltung mit zwei Widerständen

Eine Funktion soll folgende Aufgabe lösen:

Gegeben: zwei Widerstände R₁ und R₂

Gesucht: der Serien- und Parallelersatzwiderstand

Funktion: Tauschen

Eine Funktion soll zwei Zahlen dann tauschen, wenn die zweite Zahl kleiner als die erste Zahl ist. Die Funktion soll als Resultat zurückgeben, ob die Zahlen getauscht wurden oder nicht.

Funktion: n!

Eine Funktion soll die Fakultät n! einer Zahl n berechnen.

n! = 1.2.3.4. .... .(n-2).(n-1).n

Funktion: n über k

Eine Funktion soll den Binominalkoeffizienten n über k berechnen.

Inverse 1/x

Eine Funktion soll eine Variable x des Typs float in den Kehrwert 1/x umwandeln.

Zeichenkette mit Trennzeichen zerlegen

Schreibe eine Funktion, welche eine Zeichenkette mit definierten Trennzeichen in ihre Bestandteile zerlegt, und am Bildschirm ausgibt.
Beispiel :

    Zeichenkette : HN1a,HN2a,HN3b,HN3a
    Trennzeichen : ,
    Resultat, d.h. Ausgabe der Funktion
    HN1a
    HN2a
    HN3b
    HN3a

Funktionparameter : Zeichenkette, Trennzeichen
maximale Länge der Zeichenkette : 132
Die korrekte Funktion der Funktion ist durch eine geeignete Testumgebung (main) zu testen und dokumentieren.

Telefonnummer

Eine Funktion soll alle Zusatzzeichen in einer Telefonnummer entfernen.

Funktion: Primzahl

Eine Funktion soll prüfen ob eine ganze positive Zahl eine Primzahl ist.

Funktion: Fibonaccizahl

Eine Funktion soll prüfen ob eine ganze positive Zahl eine Fibonacci-Zahl ist. Für die Folge der Fibonacci-Zahlen gilt:
F_i = F_i-1 + F_i-2 und F₁ = F ₂ = 1

Vektor in zwei Vektoren aufteilen

Schreibe eine Funktion, die die als Vektor abgelegten ganzen Zahlen in zwei neuen Vektoren so ablegt, daß der eine Vektor alle geraden Zahlen und der andere Vektor alle ungeraden Zahlen enthält.

Zwei sortierte Vektoren zu einem Vektor mischen

Zwei sortierte Vektoren sollen zu einem Vektor so zusammengemischt werden, dass der neue Vektor wieder sortiert ist.

Elemente eines Vektors stürzen (Reihenfolge umdrehen)

Die Elemente eines Vektors sollen gestürzt werden, d.h. ein z.B. aufsteigend sortierter Vektor ist dann absteigend sortiert.

Bereich von Elementen aus einem Vektor entfernen

Aus einem Vektor mit den Elementen 0 bis n-1 sind die Elemente i1 bis i2 zu entfernen. Die Lösung ist als Funktion zu implementieren.

Elemente in einen Vektor einfügen

In einen Vektor sind ab der Stelle i1 die Elemente eines zweiten Vektors einzufügen. Die Lösung ist als Funktion zu implementieren.

Münzliste

Es ist ein Programm zu schreiben, das für die Auszahlung von mehreren Geldbeträgen eine sogenannte Münzliste erstellt.

Die Münzliste enthält die notwendige Anzahl der einzelnen verfügbaren Münzen (Geldscheine) um alle Geldbeträge damit (ohne Rückgeld) auszahlen zu können.
Verfügbare Geldscheine bzw. Münzen : S 1000, 500, 100, 50, 20, 10, 5, 1
Geldbeträge : werden auf ganze Schillingbeträge gerundet.
Beispiel: Geldbeträge in der ersten Spalte:

	1000	500	100	50	20	10	5	1
3423.-	3	0	4	0	1	0	0	3
746.-	0	1	2	0	2	0	1	1
4612.-	4	1	1	0	0	1	0	2
8781.-	7	2	7	0	3	1	1	6
17562.-	14	4	14	0	6	2	2	12

Kondensator laden/entladen

Aus dem momentan fließenden Strom kann näherungsweise die im nächsten Zeitintervall D t transportierte Ladung D Q = i(t). D t berechnet werden. Dadurch ändert sich die Ladung am Kondensator um D Q, bzw. die Spannung um D u_c. Aus u_c (und U_q) kann u_R neu berechnet werden, und i(t) ist wieder u_r(t) / R.

Für eine gegebene Schaltung soll ein Programm nach dieser Methode i(t) und/oder u_c(t) berechnen und in Tabellenform am Bildschirm ausgeben, bzw. durch Ausgabeumleitung in eine Datei schreiben.

Ausfluß aus einem Gefäß

Mittels eines Programmes ist h(t) zu berechnen und in Form einer Tabelle amBildschirm und durch Ausgabeumleitung auf eine Datei auszugeben.
Mittels der Bernoulli-Gleichung kann die Ausflußgeschwindigkeit berechnet werden. Im Zeitintervall D t fließ daher eine Wassermenge von A1. v D t aus dem Behälter. Damit sinkt die Höhe um D h, damit wieder die Ausflußgeschwindigkeit usw.

Gegeben: Querschnitt der Auslaßöffnung A₁, Querschnitt des Flüssigkeitsbehälters und h(t=0)