<\/p>\n
Programmieren Sie eine Excel-Anwendung, die Daten in Access speichert und ausliest.<\/p>\n
Excel, VBA<\/p>\n
Access 2000 und höher<\/p>\n
Darlehenskalkulator.mdb, Darlehenskalkulator.xls<\/p>\n
André Minhorst, Duisburg<\/b><\/p>\n
Wer mal eine größere Sache wie ein Haus oder ähnliches kauft, muss ordentlich rechnen – zumindest, wenn er nicht gerade im Lotto gewonnen hat. Dabei geht es darum, das Darlehen, Zinssatz, Zahlungszeitraum, Sondertilgungen und mehr unter einen Hut zu bringen – eigentlich kein Fall für Access, sondern für Excel. Dennoch kommt hier aus Access zum Zuge – wie genau, erfahren Sie in diesem Beitrag.<\/b><\/p>\n
Die in diesem Beitrag vorgestellte Lösung berechnet Annuitätendarlehen. Diese Art des Darlehens kommt zum Beispiel oft bei der Immobilienfinanzierung vor.<\/p>\n
Die wichtigste Eigenschaft einer solchen Finanzierung ist eine konstante Annuitätenrate, die sich aus einem Zins- und einem Tilgungsanteil zusammensetzt, wobei der Zinsanteil durch das schrumpfende Restdarlehen ab- und der Tilgungsanteil zunimmt.<\/p>\n
Formeln<\/b><\/p>\n
Für die Umsetzung einer automatischen Berechnung eines solchen Kredits sind einige Formeln notwendig.<\/p>\n
Die erste Formel bestimmt die Laufzeit eines Kredits anhand des Zinssatzes und der Tilgung:<\/p>\n
Dabei ist n die Laufzeit in Jahren, i der Zinssatz und t der Tilgungssatz.<\/p>\n
Mit einem Zinssatz von 4 % und einer Tilgung von 1 % liefert diese Formel etwa eine Laufzeit von etwas mehr als 41 Jahren.<\/p>\n
Gegebenenfalls erfolgt die Zahlung nicht einmal, sondern mehrmals im Jahr – in dem Fall gilt die folgende Formel für die Berechnung der Laufzeit:<\/p>\n
Dabei entspricht m der Anzahl der Zahlungen pro Jahr.<\/p>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic001.png\")
<\/p>\n
<\/b><\/p>\n
Bild 1: Darlehensberechnung mit Excel<\/span><\/b><\/p>\n Mit der Laufzeit lässt sich die Annuität, also der jährlich zu zahlende Betrag, berechnen:<\/p>\n Dabei ist R die Annuität, S0 die Kreditsumme, i der Zinssatz und n die Laufzeit.<\/p>\n Wenn man die aus der obigen Formel berechnete Laufzeit einsetzt, lässt sich die Formel vereinfachen:<\/p>\n Für die monatliche Annuität teilt man das Ergebnis noch durch die Anzahl der Monate.<\/p>\n Umsetzung in Excel<\/b><\/p>\n Excel ist prädestiniert für die Berechnung von Annuitätendarlehen. Dazu benötigt man nur die Angabe von drei Werten (Darlehen, Zinssatz, Tilgung) und eine Reihe Formeln. Bild 1 zeigt die Berechnung in Excel, die Sie auch in der Beispieldatei finden. Die oberen drei Zeilen enthalten die für die Berechnung notwendigen Informationen; wenn Sie diese ändern, passt Excel die in der darunter befindlichen Tabelle enthaltenen Werte ebenfalls an.<\/p>\n Die Tabelle mit dem Verlauf des Annuitätendarlehens legen Sie folgendermaßen an: Die erste Spalte enthält die Monate. Dabei ist der nullte Monat in der ersten Zeile derjenige, in dem die Auszahlung erfolgt. Die Rückzahlung beginnt nach dem ersten Monat. Tragen Sie in die Spalte mit den Monaten den Wert 1 ein. Nun könnten Sie die übrigen Werte händisch eintragen, eine hilfreiche Funktion von Excel nimmt Ihnen diese Arbeit jedoch ab: Tragen Sie für den zweiten Monat eine Formel ein, die den Inhalt der darüber liegenden Zelle um eins erhöht – hier etwa =A6+1.<\/p>\n Diesen Vorgang sollen Sie natürlich nicht endlos wiederholen. Stattdessen markieren Sie die Zelle A7 und ziehen das Viereck in der linken unteren Ecke der Zelle soweit nach unten, wie Sie Monate benötigen (siehe Bild 2).<\/p>\n Wenn Sie noch nicht wissen, wie viele das sind, setzen Sie ruhig ab – Sie können den Inhalt der untersten kopierten Zelle in einem weiteren Schritt weiter „nach unten ziehen“. Der Clou ist, das Excel nicht nur den Zelleninhalt kopiert, sondern auch die relativen Bezüge beibehält – jede der neu gefüllten Zellen addiert den Wert 1 zum Wert der darüber liegenden Zelle.<\/p>\n <\/b><\/p>\n Bild 2: Kopieren von Zellen nach unten<\/span><\/b><\/p>\n Die zweite und dritte Spalte sollen die im Kopf des Tabellenblatts enthaltenen Werte für Zinssatz und Tilgung enthalten. Dementsprechend legen Sie für die Zelle B7 zunächst den Inhalt =$B$2 und für C7 den Inhalt =$B$3 fest. Was sollen nun die Dollar-Zeichen ($) in den Formeln Ganz einfach: Sie erzeugen absolute statt relativer Bezüge zu den Zellen, damit beim Kopieren dieser Zellen nach unten jeweils der Wert der Zellen B2 und B3 erhalten bleibt. Wenn Sie die Dollar-Zeichen weglassen und die Felder wie bereits bei den Monaten nach unten kopieren, würde beispielsweise die Zelle B8 nicht mehr den Inhalt von B2, sondern den von B3 anzeigen.<\/p>\n Aber ist dies überhaupt so sinnvoll Wenn der Benutzer etwa für den Zeitraum ab dem zehnten Jahr einen anderen Zinssatz eintragen möchte, müsste er dies für jede Zeile einzeln tun. Wenn Sie erreichen möchten, dass die änderung in dieser Zelle in alle darunter befindlichen Zeilen übertragen wird, setzen Sie doch wieder eine Formel mit Bezug zur darüber liegenden Zelle ein. Die Zelle B7 enthielte dann nach wie vor den Wert =$B$2, die Zelle B8 den Wert =B7, die Zelle B9 den Wert =B8 und so weiter.<\/p>\n Die Spalte Sondertilgungen bleibt zunächst leer, hier kann der Benutzer später nach Belieben Sondertilgungen eintragen.<\/p>\n Die Annuität berechnet man mit der Formel =$B$1*(B7+C7)\/12. Eigentlich könnte man hier auch =$B$1*($B$2+$B$3)\/12 verwenden. Normalerweise ist aber beispielsweise der Zinssatz nur auf eine Laufzeit von 5, 10 oder 15 Jahren festgelegt. Durch die hier verwendete Formel können Sie nach Ablauf dieser Zeit einen neuen Zinssatz festlegen, die Annuität wird entsprechend angepasst. Auch diese Formel tragen Sie in der ersten Zeile in der Zelle E7 ein und kopieren diese in die darunter liegenden Zellen.<\/p>\n Der Zins wird ganz einfach aus dem Produkt des Restdarlehens und des Zinssatzes ermittelt und lautet für die erste Zeile =H6*B7\/12. Auch den Inhalt dieses Feldes kopieren Sie nach unten.<\/p>\n Da die Annuität über die gesamte Laufzeit konstant bleiben soll, der Zins sich aber gemeinsam mit dem Restdarlehen vermindert, wächst die Tilgung. Die Formel für die entsprechende Spalte lautet ganz einfach =E7-F7.<\/p>\n Bleibt noch das Restdarlehen: Dabei handelt es sich um die Differenz des Restdarlehens des vorherigen Monats und der Summe der Tilgungen, also der Tilgung und der Sondertilgung des aktuellen Monats – für die erste Zahlungsperiode also etwa =H6-G7-D7.<\/p>\n Mit dem Kopieren aller Zeilen nach unten ist die erste Fassung dieser Berechnung fertig. Erste Fassung deshalb, weil man der Tabelle doch noch einiges an Komfort hinzufügen könnte – beispielsweise sollte diese möglichst automatisch die benötigte Anzahl Zeilen anzeigen. Bevor Sie solche kosmetischen Veränderungen vornehmen, bauen wie die Tabelle jedoch zunächst in eine Access-Anwendung ein.<\/p>\n Damit Sie später per VBA auf das hier angelegte Tabellenblatt zugreifen können, vergeben Sie dafür den Namen Darlehenskalkulator. Am einfachsten geht dies, wenn Sie einfach doppelt auf den Reiter mit der Beschriftung Tabelle 1 im unteren Bereich des Excel-Fensters klicken und diese Bezeichnung anpassen.<\/p>\n Die Tabelle kann mitunter einige hundert Zeilen enthalten. Beim Herunterscrollen nervt es dann unter Umständen, wenn man die Spaltenüberschriften und die übrigen Darlehensinformationen nicht mehr lesen kann. Sie können allerdings einen bestimmten Bereich der Tabelle fixieren. Dazu markieren Sie einfach die Zeile, oberhalb derer alle Informationen beim Scrollen stehen bleiben sollen und wählen den Menübefehl Fenster\/Fenster fixieren aus.<\/p>\n Da Excel genauso wie Access VBA unterstützt, könnte man die notwendigen Erweiterungen zum Steigern des Komforts auch komplett in Excel vornehmen. Access bietet jedoch eine sehr angenehme Eigenschaft: die Möglichkeit, Daten in relationalen Tabellen zu speichern. Wenn Sie mit der aktuellen Fassung der Excel-Anwendung zum Berechnen von Finanzierungen herumspielen und diverse Varianten durchprobieren, löschen Sie entweder jeweils die zuvor erstellte Berechnung oder Sie kopieren das Arbeitsblatt und erzeugen so verschiedene Berechnungen.<\/p>\n <\/b><\/p>\n Bild 3: Ein neues Steuerelement im Excel-Tabellenblatt und seine Eigenschaften<\/span><\/b><\/p>\n übersichtlich ist das alles allerdings nicht. Immerhin ließe sich auch mit Excel eine übersichtsseite bauen, mit der man auf die unterschiedlichen Varianten verweisen kann, aber dazu bräuchten Sie ja kein Access.<\/p>\n <\/b><\/p>\n Bild 4: Die Toolbox mit der Schaltfläche zum Aktivieren und Deaktivieren des Entwurfsmodus<\/span><\/b><\/p>\n Die nachfolgend vorgestellte Lösung soll nun so aussehen: Access arbeitet lediglich im Hintergrund und speichert die Daten der unterschiedlichen Berechnungen.<\/p>\n Excel bietet einige Steuerelemente, mit denen sich die aktuelle Berechnung speichern oder eine alte Berechnung wiederherstellen lässt. Damit greifen Sie also vom Excel-Tabellenblatt direkt auf die Datenbank zu.<\/p>\n Um die Anwendung möglichst einfach zu halten, sollen nur zwei Steuerelemente zum Einsatz kommen:<\/p>\n Die notwendigen Steuerelemente sind schnell angelegt: Dazu aktivieren Sie einfach die Symbolleiste Steuerelement-Toolbox (Menüeintrag Ansicht ( Symbolleisten ( Steuerelement-Toolbox), klicken auf die gewünschten Steuerelemente und ziehen Rahmen in der passenden Größe auf (siehe Bild 4).<\/p>\n Die Steuerelement-Toolbox unterscheidet sich im übrigen von der von Access bekannten Toolbox durch eine Schaltfläche zum An- und Ausschalten der Entwurfsansicht. Um etwa das Beim Klicken-Ereignis einer Schaltfläche per Mausklick auslösen zu können, müssen Sie zunächst den Entwurfsmodus beenden.<\/p>\n Das ändern wichtiger Eigenschaften wie dem Namen oder der Bezeichnung nehmen Sie im Eigenschaftsfenster vor, dass Sie im Kontextmenü des jeweiligen Steuerelements über den Eintrag Eigenschaften anzeigen (siehe Bild 3).<\/p>\n <\/p>\n Excel ist nicht standardmäßig für den Zugriff auf Access-Datenbanken vorbereitet, daher müssen Sie noch den fehlenden Verweis einstellen. Dazu wechseln Sie in den VBA-Editor: ändern Sie zunächst den Namen der neuen Schaltfläche in cmdSpeichern und klicken Sie dann doppelt auf diese, dann zeigt der VBA-Editor direkt die beim Klicken auf diese Schaltfläche ausgelöste Routine an.<\/p>\n Dort öffnen Sie über den von der VBA-Entwicklungsumgebung gewohnten Menüeintrag Extras\/Verweise den entsprechenden Dialog und fügen den Verweis auf die Bibliothek Microsoft DAO 3.6 Object Library hinzu (siehe Bild 5).<\/p>\n <\/b><\/p>\n Bild 5: Hinzufügen eines Verweises auf die DAO-Bibliothek zum VBA-Projekt der Excel-Anwendung<\/span><\/b><\/p>\n Speichern der Darlehen-Basisdaten<\/b><\/p>\n Die Basisdaten eines Darlehens befinden sich im oberen Bereich des Excel-Tabellenblatts. Per Mausklick auf die Schaltfläche Speichern sollen die passenden Daten in einer Tabelle namens tblDarlehen gespeichert werden. Diese Tabelle ist wie in Bild 6 aufgebaut.<\/p>\n Wichtig ist, dass die Eigenschaft Indiziert des Feldes Bezeichnung den Wert Ja (Ohne Duplikate) hat, damit dieses Feld nur eindeutige Werte enthalten kann. Außerdem müssen Sie für die beiden Felder Zinssatz und Tilgung als Feldgröße Single einstellen.<\/p>\n <\/b><\/p>\n Bild 6: Aufbau der Tabelle tblDarlehen<\/span><\/b><\/p>\n Die Informationen für die einzelnen Zeiträume (in dem Fall Monate), die sich im unteren Bereich des Tabellenblatts befinden, werden teilweise berechnet, teilweise benutzerdefiniert (wie die Sondertilgungen) und teilweise beides – die Zinssätze und Tilgungssätze etwa werden zunächst festgelegt, können aber vom Benutzer angepasst werden. Die beiden letzteren Kategorien von Informationen speichert die Anwendung in einer weiteren Tabelle namens tblZahlungperioden. Die Datensätze dieser Tabelle sind jeweils mit einem Datensatz der Tabelle tblDarlehen verknüpft, sodass eine eindeutige Zuordnung erfolgen kann (siehe Bild 7). Die Felder Zinssatz und Tilgung erhalten wiederum die Feldgröße Single.<\/p>\n <\/b><\/p>\n Bild 7: Die Tabelle für die Finanzierungsdetails<\/span><\/b><\/p>\n <\/b><\/p>\n Bild 8: Die Eigenschaften der Beziehung zwischen den Tabellen tblDarlehen und tblZahlungsperioden<\/span><\/b><\/p>\n Beim Speichern eines Darlehens inklusive Zahlungsperioden sollen eventuell bereits unter der gleichen Bezeichnung vorhandene Daten gelöscht werden. Damit dies mit einer einzigen DELETE-Anweisung erledigt werden kann, stellen Sie für die Beziehung zwischen den beiden Tabellen referentielle Integrität und Löschweitergabe ein (siehe Bild 8).<\/p>\n Das Speichern der in der Excel-Tabelle enthaltenen Daten bedarf einiger Vorüberlegungen. Die wichtigste davon ist: Wie viele Zahlungsperioden sollen zu einem Darlehen gespeichert werden Wichtig sind eigentlich alle Zahlungsperioden bis zur vollständigen Tilgung des Darlehens, also bis der Inhalt des Feldes Restdarlehen kleiner oder gleich 0 ist.<\/p>\n Prozedur zum Speichern einer Finanzierung<\/b><\/p>\n Die Routine aus Quellcode 1 übernimmt das Speichern der relevanten Informationen in die beiden Tabellen der Access-Datenbank.<\/p>\n Nach dem Deklarationsteil weist die Routine zunächst der Variablen xlsWorksheet einen Verweis auf das Tabellenblatt mit den zu speichernden Daten zu. Anschließend folgt schon das Herstellen des Verweises auf die Datenbankdatei mit den Zieltabellen. Dazu verwenden Sie die Methode OpenDatabase des DBEngine-Objekts. In diesem Falle reicht die Angabe des Dateinamens inklusive Pfad als Parameter aus.<\/p>\n Der Einfachheit halber sollen die Excel-Datei und die Datenbank im gleichen Verzeichnis liegen. Auf diese Weise treffen Sie mit ActiveWorkbook.Path immer das richtige Verzeichnis.<\/p>\n Die folgende DELETE-Anwiesung löscht alle Datensätze der Tabelle tblDarlehen, deren Feld Bezeichnung den aktuellen Wert der Zelle E1 des Tabellenblatts enthält. Da dieses Feld als eindeutig indiziert ist, kann dies maximal einen Datensatz betreffen.<\/p>\n Durch die Festlegung der Löschweitergabe in der Beziehung zur Tabelle tblZahlungsperioden werden die in dieser Tabelle enthalten und mit dem gelöschten Datensatz verknüpften Datensätze direkt mitgelöscht. Anschließend öffnet die Routine eine leere Datensatzgruppe auf Basis der Tabelle tblDarlehen und fügt dieser einen neuen Datensatz mit den im Kopf des Tabellenblatts enthaltenen Daten ein.<\/p>\n Dabei merkt sich die Routine den Wert des Primärschlüsselfeldes, denn dieser soll später beim Anlegen der einzelnen Perioden der Finanzierung ja ebenfalls mitgespeichert werden – und zwar als Inhalt des Fremdschlüsselfeldes DarlehenID.<\/p>\n Beim Anlegen dieser Daten durchläuft die Routine alle Zeilen der Excel-Tabelle von Zeile 7 – das ist die Zeile mit der ersten Zahlungsperiode – bis zu der Zeile, in der das Restdarlehen erstmals kleiner oder gleich 0 ist.<\/p>\n Für jede Zeile der Excel-Tabelle schreibt die Routine einen Datensatz in die Tabelle tblZahlungsperioden.<\/p>\n Quellcode 1: Speichern einer Finanzierung in Access-Tabellen<\/b><\/p>\n In der vorliegenden Fassung hat die Routine ein Manko: Sie speichert jeweils die in den einzelnen Zellen enthaltenen Zahlenwerte.<\/p>\n Das ist zwar nicht verkehrt, würde bei einem Wiederherstellen einer Finanzierungsberechnung aber dazu führen, dass lediglich die Zahlenwerte in die Excel-Tabelle geschrieben werden könnten. Dies schränkt natürlich den Komfort erheblich ein, weshalb Sie einige kleine änderungen vornehmen sollten: Hängen Sie an alle Zellenbezüge im Quellcode, die – falls vorhanden – eine Formel statt einer Zahl speichern sollen, den Ausdruck .Formula an:<\/p>\n !Monat = xlsWorksheet.Range(„A“ _<\/p>\n & intZeile).Formula<\/p>\n !Zinssatz = xlsWorksheet.Range(„B“ _<\/p>\n & intZeile).Formula<\/p>\n !Tilgung = xlsWorksheet.Range(„C“ _<\/p>\n & intZeile).Formula<\/p>\n !Sondertilgung = _<\/p>\n xlsWorksheet.Range(„D“ _<\/p>\n & intZeile).Formula<\/p>\n …<\/p>\n <\/b><\/p>\n Bild 9: Die Datenbankfassung einer Finanzierungsberechnung<\/span><\/b><\/p>\n Das bedingt natürlich, dass Sie in der Tabelle die Datentypen der betroffenen Felder auf Text ändern, und zwar bei den Feldern Monat, Zinssatz, Tilgung und Sondertilgung der Tabelle tblZahlungsperioden.<\/p>\n Nach dem Speichern sehen die passenden Datensätze der beiden Tabellen tblDarlehen und tblZahlungsperioden etwa wie in Bild 9 aus.<\/p>\n Nachdem das Speichern von Finanzierungen funktioniert, sollen diese bei Bedarf natürlich auch wieder zurück in die Excel-Tabelle geladen werden. Ausgangspunkt dafür ist ein Kombinationsfeld, das alle Bezeichnungen der in der Tabelle tblDarlehen gespeicherten Datensätze zur Auswahl anbietet. Der Inhalt dieses Kombinationsfeldes soll jeweils beim öffnen des Tabellenblatts und beim Speichern einer Finanzierung aktualisiert werden. Das Kombinationsfeld legen Sie wie schon zuvor die Schaltfläche über die Steuerelement-Toolbox an; stellen Sie außerdem den Namen auf cboDarlehen ein und fügen Sie ein passendes Bezeichnungsfeld hinzu.<\/p>\n Das Füllen der Liste übernimmt die Routine aus Quellcode 2. Den Aufruf dieser Routine fügen Sie sowohl der Ereignisprozedur cmdSpeichern_Click (am Ende) als auch einer Ereignisprozedur hinzu, die beim Aktivieren des Tabellenblattes ausgelöst wird. Letztere legen Sie an, indem Sie zum VBA-Editor wechseln, dort im linken Kombinationsfeld über dem Codefenster den Eintrag Worksheet und im rechten Kombinationsfeld den Eintrag Activate auswählen.<\/p>\n Dies erzeugt automatisch einen Prozedurrumpf, den Sie wie folgt um den Aufruf der Routine ListeFuellen erweitern:<\/p>\n Das Excel-Tabellenblatt sieht mit dem Kombinationsfeld nun wie in Bild 10 aus. Leider fehlt noch die Funktion, die nach der Auswahl eines der Einträge des Kombinationsfeldes die Daten der passenden Finanzierungsrechnung aus der Access-Datenbank in die Excel-Tabelle einliest. <\/p>\n Diese arbeitet prinzipiell wie die Routine zum Speichern der Finanzierungsrechnung – nur umgekehrt.<\/p>\n Im Detail sieht dies wie in Quellcode 3 aus. Eine wichtige Besonderheit im Code ist, dass man beim Zuweisen der im Textformat gespeicherten Formeln nicht die Formula-, sondern die FormularLocal-Eigenschaft des jeweiligen Zellbereichs verwendet und zusätzlich die CStr-Funktion über die Inhalte der Felder laufen lässt.<\/p>\n Quellcode 2: Diese Routine füllt ein Kombinationsfeld mit einer Liste der in der Datenbank gespeicherten Finanzierungsberechnungen.<\/b><\/p>\n Quellcode 3: Laden einer Finanzierungsberechnung von Access nach Excel<\/b><\/p>\n Andere Variationen erwiesen sich als nicht praxistauglich.<\/p>\n Eines der größten Probleme beim Speichern und Einlesen von Finanzierungsberechnungen ist, dass je nach dem Verhältnis von Zinssatz zu Tilgung unterschiedliche Laufzeiten zum Einsatz kommen. Sprich: Wenn Sie eine Kombination mit langer Laufzeit (Verhältnis Zinssatz\/Tilgung groß) eingeben, diese speichern und anschließend eine Berechnung mit kurzer Laufzeit laden, überschreibt die Anwendung nicht alle Daten des Tabellenblatts.<\/p>\n Die sinnvollste Möglichkeit, die überflüssigen Informationen zu löschen oder, im umgekehrten Fall, neue Felder anzufügen, besteht in einer Ergänzung der Routine aus Quellcode 3. Dabei ersetzen Sie den Bereich von der Zeile intZeile = 7 bis zum Ende der folgenden Do While…Loop-Schleife durch die Zeilen aus Quellcode 4. Die erste Do While…Loop-Schleife löscht die aktuellen Zahlungsperioden aus der Excel-Tabelle [1], die zweite schreibt die gespeicherten Werte dort hinein [2]. Zusätzlich fügt die Routine die Formeln für die nicht gespeicherten Werte hinzu [3].<\/p>\n <\/b><\/p>\n Bild 10: Auswahl von gespeicherten Finanzierungen<\/span><\/b><\/p>\n Quellcode 4: änderungen in der Prozedur aus Quellcode 3<\/b><\/p>\n Nun kann es passieren, dass der Benutzer eine neue Berechnung anlegen möchte, die gegebenenfalls mehr oder weniger Zahlungsperioden enthält als die aktuelle Berechnung. Wie Sie oben gesehen haben, hängt die Anzahl der Zahlungsperioden beziehungsweise die Laufzeit einzig und allein vom Verhältnis von Zinssatz und Tilgung ab – und hier kommt nun endlich die passende Formel ins Spiel:<\/p>\n Die Laufzeit soll nun bei jeder änderung einer der beiden Zellen B2 oder B3 berechnet werden, damit die Anwendung die Excel-Tabelle entweder um fehlende Zeilen erweitern oder überschüssige Zeilen entfernen kann.<\/p>\n Dies erledigt die Routine aus Quellcode 5. Sie wird generell beim ändern irgendeines Wertes auf diesem Tabellenblatt ausgelöst, was eine genauere überprüfung der geänderten Zelle nach sich zieht. Die Eigenschaft Address des Parameters Target liefert die Adresse der geänderten Zelle (etwa $B$2). Da nur bei einer änderung der beiden Zellen B2 und B3 eine Berechnung der Laufzeit und die Prüfung der vorhandenen Zahlungsperioden erfolgen soll, prüft die Routine in einer Select Case-Anweisung, ob eine dieser beiden Zellen geändert wurde.<\/p>\n Die Routine durchläuft dann zunächst so viele Zeilen, wie die Berechnung Zahlungsperioden enthält und füllt diese, soweit leer, mit den passenden Formen auf.<\/p>\n Quellcode 5: Löschen oder Hinzufügen von Zahlungsperioden beim ändern des Zinssatzes oder der Tilgung<\/b><\/p>\n Sollten mehr Zeilen als vorgesehen gefüllt sein, etwa weil die vorherige Berechnung eine längere Laufzeit aufwies, werden diese im folgenden Schritt geleert.<\/p>\n Die vorliegende Lösung bietet zahlreiche Erweiterungs- und Verbesserungsmöglichkeiten. So könnte man etwa eine Funktion hinzufügen, die regelmäßige Sondertilgungen anlegt – auf diese Weise muss man diese nicht von Hand eintragen. <\/p>\n Außerdem lässt sich die Komplexität beliebig erhöhen: Die aktuelle Version enthält beispielsweise noch keine Möglichkeit, alternative Laufzeiten oder eine tilgungsfreie Zeit zu Beginn festzulegen. Auch wurden Elemente wie eine Bearbeitungsgebühr noch nicht berücksichtigt. Wer mag, kann natürlich auch noch den effektiven Zins berechnen. Sie sehen: Es gibt noch einiges zu tun.<\/p>\n Enthaltene Beispieldateien:<\/p>\n Darlehenskalkulator97.mdb<\/p>\n Darlehenskalkulator97.xls<\/p>\n Darlehenskalkulator.mdb<\/p>\n Darlehenskalkulator.xls<\/p>\nAnnuität<\/h3>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic002.png\")
<\/p>\nFixieren für bessere übersicht<\/h3>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic003.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic004.png\")
<\/p>\nEntwurf ein\/aus<\/h3>\n
Eigenschaftsfenster<\/h3>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic005.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic006.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic007.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic008.png\")
<\/p>\nPrivate Sub cmdSpeichern_Click()\r\n Dim xlsWorksheet As Worksheet\r\n Dim db As DAO.database\r\n Dim strBezeichnung As String\r\n Dim rstDarlehen As DAO.Recordset\r\n Dim lngDarlehenID As Long\r\n Dim rstZahlungsperioden As DAO.Recordset\r\n Dim intZeile As Integer\r\n Set xlsWorksheet = Worksheets(\"Darlehenskalkulator\")\r\n Set db = DBEngine.OpenDatabase(ActiveWorkbook.Path & \"\\Darlehenskalkulator.mdb\")\r\n strBezeichnung = xlsWorksheet.Range(\"E1\")\r\n db.Execute \"DELETE FROM tblDarlehen WHERE Bezeichnung = ''\" & strBezeichnung & \"''\"\r\n Set rstDarlehen = db.OpenRecordset(\"SELECT * FROM tblDarlehen WHERE 1=0\", dbOpenDynaset)\r\n With rstDarlehen\r\n .AddNew\r\n !Bezeichnung = strBezeichnung\r\n !Datum = xlsWorksheet.Range(\"E2\")\r\n !Darlehen = xlsWorksheet.Range(\"B1\")\r\n !Zinssatz = xlsWorksheet.Range(\"B2\")\r\n !Tilgung = xlsWorksheet.Range(\"B3\")\r\n lngDarlehenID = !DarlehenID\r\n .Update\r\n End With\r\n rstDarlehen.Close\r\n Set rstDarlehen = Nothing\r\n Set rstZahlungsperioden = db.OpenRecordset(\"SELECT * FROM tblZahlungsperioden WHERE 1=0\", _ dbOpenDynaset)\r\n With rstZahlungsperioden \r\n intZeile = 7\r\n Do While xlsWorksheet.Range(\"H\" & intZeile) > 0\r\n .AddNew\r\n !DarlehenID = lngDarlehenID\r\n !Monat = xlsWorksheet.Range(\"A\" & intZeile)\r\n !Zinssatz = xlsWorksheet.Range(\"B\" & intZeile)\r\n !Tilgung = xlsWorksheet.Range(\"C\" & intZeile)\r\n !Sondertilgung = xlsWorksheet.Range(\"D\" & intZeile)\r\n .Update\r\n intZeile = intZeile + 1\r\n Loop\r\n End With\r\n rstZahlungsperioden .Close\r\n Set rstZahlungsperioden = Nothing\r\n Set db = Nothing\r\nEnd Sub<\/pre>\n
Formeln statt Zahlen<\/h3>\n
...<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic009.png\")
<\/p>\nPrivate Sub Worksheet_Activate()\r\n ListeFuellen\r\nEnd Sub<\/pre>\n
Private Sub ListeFuellen()\r\n Dim db As DAO.Database\r\n Dim rstDarlehen As DAO.Recordset\r\n Set db = DBEngine.OpenDatabase(ActiveWorkbook.Path & \"\\Darlehenskalkulator.mdb\")\r\n Set rstDarlehen = db.OpenRecordset(\"SELECT Bezeichnung FROM tblDarlehen \" _ & \"ORDER BY Bezeichnung\", dbOpenDynaset)\r\n With Me.cboDarlehen\r\n .Clear\r\n Do While Not rstDarlehen.EOF\r\n .AddItem rstDarlehen!Bezeichnung\r\n rstDarlehen.MoveNext\r\n Loop\r\n End With\r\n rstDarlehen.Close\r\n Set rstDarlehen = Nothing\r\n Set db = Nothing\r\nEnd Sub<\/pre>\n
Private Sub cboDarlehen_Change()\r\n ''Deklarationsteil siehe Beispieldateien\r\n Set xlsWorksheet = Worksheets(\"Darlehenskalkulator\")\r\n Set db = DBEngine.OpenDatabase(ActiveWorkbook.Path & \"\\Darlehenskalkulator.mdb\")\r\n strBezeichnung = Me.cboDarlehen.Value\r\n Set rstDarlehen = db.OpenRecordset(\"SELECT * FROM tblDarlehen WHERE Bezeichnung = ''\" _ & strBezeichnung & \"''\", dbOpenDynaset)\r\n If Not rstDarlehen.EOF Then\r\n xlsWorksheet.Range(\"E1\") = rstDarlehen!Bezeichnung\r\n xlsWorksheet.Range(\"E2\") = rstDarlehen!Datum\r\n xlsWorksheet.Range(\"B1\") = rstDarlehen!Darlehen\r\n xlsWorksheet.Range(\"B2\") = rstDarlehen!Zinssatz\r\n xlsWorksheet.Range(\"B3\") = rstDarlehen!Tilgung\r\n lngDarlehenID = rstDarlehen!DarlehenID\r\n End If\r\n rstDarlehen.Close\r\n Set rstDarlehen = Nothing\r\n Set rstZahlungsperiode = db.OpenRecordset(\"SELECT * FROM tblZahlungsperioden \" _ & \"WHERE DarlehenID = \" & lngDarlehenID, dbOpenDynaset)\r\n intZeile = 7\r\n Do While Not rstZahlungsperiode.EOF\r\n xlsWorksheet.Range(\"A\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Monat)\r\n xlsWorksheet.Range(\"B\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Zinssatz)\r\n xlsWorksheet.Range(\"C\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Tilgung)\r\n xlsWorksheet.Range(\"D\" & intZeile).Formula = CStr(rstZahlungsperiode!Sondertilgung)\r\n intZeile = intZeile + 1\r\n rstZahlungsperiode.MoveNext\r\n Loop\r\nEnd Sub<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{78CDC3E0-202A-4EEE-A761-551F3D08BE08}\/pic010.png\")
<\/p>\n...\r\nintZeile = 7\r\nDo While Not xlsWorksheet.Range(\"A\" & intZeile) = \"\" [1]\r\n For i = 1 To 8\r\n xlsWorksheet.Range(Chr(64 + i) & intZeile) = \"\"\r\n Next i\r\n intZeile = intZeile + 1\r\nLoop\r\nintZeile = 7\r\nDo While Not rstZahlungsperiode.EOF [2]\r\n xlsWorksheet.Range(\"A\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Monat)\r\n xlsWorksheet.Range(\"B\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Zinssatz)\r\n xlsWorksheet.Range(\"C\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Tilgung)\r\n xlsWorksheet.Range(\"D\" & intZeile).FormulaLocal = CStr(rstZahlungsperiode!Sondertilgung)\r\n xlsWorksheet.Range(\"E\" & intZeile).Formula = \"=$B$1*($B$2+$B$3)\/12\" [3]\r\n xlsWorksheet.Range(\"F\" & intZeile).Formula = \"=H\" & intZeile - 1 & \"*B\" & intZeile & \"\/12\"\r\n xlsWorksheet.Range(\"G\" & intZeile).Formula = \"=E\" & intZeile & \"-F\" & intZeile\r\n xlsWorksheet.Range(\"H\" & intZeile).Formula = \"=H\" & intZeile - 1 & \"-G\" & intZeile & \"-D\" _ & intZeile\r\n intZeile = intZeile + 1\r\n rstZahlungsperiode.MoveNext\r\nLoop\r\n...<\/pre>\n
Fehlende Zeilen hinzufügen<\/h3>\n
Private Sub Worksheet_Change(ByVal Target As Range)\r\n ''Deklarationsteil siehe Beispieldateien\r\n Select Case Target.Address\r\n Case \"$B$2\", \"$B$3\"\r\n Set xlsWorksheet = Worksheets(\"Darlehenskalkulator\")\r\n With xlsWorksheet\r\n i = .Range(\"B2\")\r\n t = .Range(\"B3\")\r\n m = 12\r\n intAnzahlZahlungsperioden = Log((i + t) \/ t) \/ Log(1 + i \/ 12)\r\n For intZahlungsperiode = 1 To intAnzahlZahlungsperioden\r\n If intZeile = 435 Then\r\n Debug.Assert intZeile = 435\r\n End If\r\n intZeile = intZahlungsperiode + 6\r\n If .Range(\"A\" & intZeile) = \"\" Then\r\n .Range(\"A\" & intZeile).FormulaLocal = \"=A\" & intZeile - 1 & \"+1\"\r\n .Range(\"B\" & intZeile).FormulaLocal = \"=B\" & intZeile - 1\r\n .Range(\"C\" & intZeile).FormulaLocal = \"=C\" & intZeile - 1\r\n .Range(\"D\" & intZeile).FormulaLocal = \"\"\r\n .Range(\"E\" & intZeile).Formula = \"=$B$1*($B$2+$B$3)\/12\"\r\n .Range(\"F\" & intZeile).Formula = \"=H\" & intZeile - 1 & \"*B\" & intZeile _ & \"\/12\"\r\n .Range(\"G\" & intZeile).Formula = \"=E\" & intZeile & \"-F\" & intZeile\r\n .Range(\"H\" & intZeile).Formula = \"=H\" & intZeile - 1 & \"-G\" & intZeile _ & \"-D\" & intZeile\r\n End If\r\n Next intZahlungsperiode\r\n intZeile = intZeile + 1\r\n Do While Not .Range(\"A\" & intZeile) = \"\"\r\n For i = 1 To 8\r\n xlsWorksheet.Range(Chr(64 + i) & intZeile) = \"\"\r\n Next i\r\n intZeile = intZeile + 1\r\n Loop\r\n End With\r\n Case Else\r\n End Select\r\nEnd Sub<\/pre>\n
Downloads zu diesem Beitrag<\/h3>\n