<\/p>\n
Wer ein Haus, ein Auto oder auch nur einen neuen Computer kauft, wird dies möglicherweise über ein Darlehen finanzieren. Wer wissen will, wie lange ein Kredit unter verschiedenen Bedingungen läuft und wann welcher Betrag abgezahlt ist, kann eines der vielen Online-Portale zu diesem Thema befragen. Dem Access-Entwickler macht es aber natürlich viel mehr Spaß, dies mit Access zu erledigen – und dabei gleich noch Excel mit ins Boot zu holen.<\/b><\/p>\n
Grundsätzlich soll dieser Beitrag ein Beispiel für einen anderen Beitrag liefern – nämlich für Excel automatisieren <\/b>(Shortlink 693). Dieser Beitrag erläutert die wichtigsten Techniken für das Erstellen und Öffnen von Excel-Dokumenten sowie zum Lesen und Schreiben von Daten in den Tabellenblättern eines Excel-Dokuments.<\/p>\n
Nebenbei können Sie damit Tilgungspläne erstellen und erfahren so beispielsweise, wie lange die Rückzahlung eines Darlehens etwa für eine Immobilie dauern würde.<\/p>\n
Ausgangspunkt ist ein ganz einfaches Formular wie das in Bild 1. Es dient der Eingabe des Darlehensbetrags, des Zinssatzes, des Tilgungssatzes und gegebenenfalls der Angabe der jährlichen Sondertilgungen.<\/p>\n
![\"pic001.png\"](\"..\/fileadmin\/_temp_\/2009_06\/TilgenMitAccessUndExcel-web-images\/pic001_opt.jpeg\")
<\/p>\n
Bild 1: Formular zum Aufnehmen der Darlehensparameter<\/span><\/b><\/p>\n Das Formular enthält vier einfache Textfelder mit den Namen txtDarlehen<\/b>, txtZinssatz<\/b>, txtTilgungssatz <\/b>und txtSondertilgung<\/b>. Die Schaltfläche mit der Beschriftung Tilgungsplan erstellen <\/b>erhält den Namen cmdErstellen <\/b>und wird mit einer Ereignisprozedur ausgestattet, die wie folgt aussieht:<\/p>\n Damit verlassen wir dieses Formular zunächst und schauen uns an, wie das fertige Excel-Dokument später aussehen soll (siehe Bild 2).<\/p>\n Bild 2: So soll der Tilgungsplan einmal aussehen.<\/span><\/b><\/p>\n Im Kopf des Dokuments sollen die wichtigsten Informationen angezeigt werden. Darunter beginnt der Tilgungsplan, der sechs Spalten enthält:<\/p>\n Wie die einzelnen Zellen gefüllt werden, schauen wir uns weiter unten im Detail an. Vorerst erstellen wir erstmal eine nackte Excel-Datei, und zwar mit der Prozedur TilgungsplanErstellen<\/b>. Diese wird, wie bereits erwähnt, über die Schaltfläche cmdErstellen <\/b>unseres Formulars aufgerufen und ist in Listing 1 zu finden. Die Routine erwartet die in den vier Textfeldern des Formulars enthaltenen Werte als Parameter, wobei der letzte von der aufrufenden Anweisung als 0 <\/b>übergeben wird, wenn das Textfeld mit der Sondertilgung leer ist.<\/p>\n Listing 1: Gerüst der Prozedur zum Erstellen von Tilgungsplänen<\/p>\n Die Routine deklariert je ein Excel.Application<\/b>-, Excel.Workbook<\/b>– und Excel.Worksheet<\/b>-Objekt und einige weitere Variablen, deren Zweck wir weiter unten erläutern. objExcel <\/b>wird mit einer neuen Instanz von Excel gefüllt, objWorkbook <\/b>erhält einen Verweis auf eine neu angelegte Excel-Datei und objSheet <\/b>füllt die Prozedur mit dem ersten Tabellenblatt der neuen Excel-Datei.<\/p>\n Nachdem die Excel-Instanz sichtbar gemacht wurde, folgt ein With…End With<\/b>-Konstrukt, in dem sich alle Anweisungen zum Füllen des Tilgungsplans befinden. Diese stellen wir gleich im Anschluss vor.<\/p>\n Die Close<\/b>-Methode des objWorkbook<\/b>-Objekts schließt das neu angelegte Excel-Dokument, allerdings nicht ohne den Benutzer zu fragen, ob und wo er dieses speichern möchte, die Quit<\/b>-Methode von objExcel <\/b>beendet die Excel-Instanz. Hier ließe sich auch eine andere Vorgehensweise realisieren, etwa indem man die Excel-Instanz mit dem Tilgungsplan einfach geöffnet lässt.<\/p>\n Kopfdaten anlegen<\/p>\n Wenn die Excel-Datei erzeugt wurde, soll zunächst das Tabellenblatt, das den Tilgungsplan aufnimmt, mit einem aussagekräftigen Namen versehen werden. Das erledigt die folgende Anweisung:<\/p>\n Danach füllen wir den Kopfbereich der Excel-Tabelle. Dazu dienen die folgenden Anweisungen, wobei immer zuerst die linke Zelle mit der Bezeichnung des Werts gefüllt wird und dann die rechts daneben liegende Zelle mit dem eigentlichen Wert. Eine weitere Anweisung formatiert jeweils die Zelle mit dem Zahlenwert:<\/p>\n Wenn Sie einmal den Ausdruck für eine Formatierung benötigen, hilft folgender Trick:<\/p>\n Startzeitpunkt festlegen<\/b><\/p>\n Der Einfachheit halber gehen wir davon aus, dass der Tilgungsplan beginnend mit dem folgenden Monat berechnet werden soll. Wenn Sie dies individuell einstellen möchten, brauchen Sie nur zwei Textfelder zur Eingabe des Startmonats und -jahrs im Formular anzulegen und die Inhalte dieser Felder an die Prozedur TilgungsplanErstellen<\/b> zu übergeben.<\/p>\n Dazu müssen Sie natürlich zwei weitere Parameter im Prozedurkopf definieren (am besten namens intMonat<\/b> und intJahr<\/b>) und diese beiden Variablen aus dem Deklarationsbereich der Prozedur entfernen. Im vorliegenden Fall verwenden wir jedoch der Einfachheit halber das aktuelle Datum, aus dem wir mit den Funktionen Month <\/b>und Year <\/b>die entsprechenden Zahlen ermitteln, zum Beispiel:<\/p>\n Spaltenüberschriften<\/b><\/p>\n Danach folgen schon die Spaltenüberschriften des eigentlichen Tilgungsplans:<\/p>\n Erste Zeile des Tilgungsplans<\/b><\/p>\n Die erste Zeile des Tilgungsplans wird etwas anders gefüllt als die übrigen, weil sie ihre Informationen aus den im Tabellenkopf enthaltenen Zellen bezieht. Die erste Zelle erhält einfach den Wert 1<\/b>:<\/p>\n Monat und Jahr werden in der zweiten Zelle zusammengefasst:<\/p>\n Die erste Berechnung erfolgt bei der Ermittlung der Rate, die sich aus der Summe von Zins- und Tilgungssatz multipliziert mit der Darlehenssumme geteilt durch zwölf Monate ergibt. Die meisten Informationen stammen aus dem Tabellenkopf:<\/p>\n Natürlich wird auch diese Zeile im Währungsformat dargestellt:<\/p>\n Die Zinsen für den ersten Monat ergeben sich aus dem Zinssatz und der ursprünglichen Darlehenssumme, die Tilgung ist die Differenz von Rate und Zinsen:<\/p>\n Wurde eine Sondertilgung angegeben und schreiben wir den letzten Monat des Jahres, soll die Sondertilgungsrate in der fünften Zelle dieser Zeile eingetragen werden (den Monat, in dem die Tilgung erfolgt, können Sie natürlich auch parametrisieren):<\/p>\n Schließlich ermitteln wir aus dem Darlehensbetrag, der Tilgung und der Sondertilgung die nach diesem Monat verbleibende Restschuld und tragen diese in die siebte Zelle ein:<\/p>\n Außerdem speichern wir die Restschuld in einer Variablen:<\/p>\n <\/p>\n Danach geht es rund: Auf Basis der ersten Zeile werden alle weiteren Zeilen des Tilgungsplans berechnet und geschrieben. Dies geschieht so lange, wie der Wert von curRestschuld <\/b>größer 0<\/b> ist:<\/p>\n Als Erstes wird innerhalb der Do While<\/b>-Schleife die Zählervariable erhöht:<\/p>\n Daraus ergibt sich nach Addition von 1 <\/b>(weil wir die erste Zeile schon geschrieben haben) gleich die laufende Nummer:<\/p>\n Dann berechnen wir den Monat und das Jahr. Normalerweise wird einfach 1 <\/b>zum Monat hinzuaddiert.<\/p>\n Wenn intMonat <\/b>jedoch schon den Wert 12 <\/b>hat, wird intMonat <\/b>auf 1 <\/b>gesetzt und der Wert von intJahr <\/b>um 1 <\/b>erhöht:<\/p>\n Das Ergebnis landet dann zusammengefasst und entsprechend formatiert in der zweiten Zelle einer jeden Zeile:<\/p>\n Dann folgt die Rate. Diese wird einfach aus der darüber liegenden Zeile gewonnen:<\/p>\n Die Zinsen berechnen sich aus der Restschuld des Vormonats und dem Zinssatz – wieder geteilt durch zwölf:<\/p>\n Die Tilgung ergibt sich, wie schon in der ersten Zeile, aus der Differenz von Rate und Zinsen. Naturgemäß wird dieser Wert nach jeder Tilgung kleiner:<\/p>\n Fehlt noch die Sondertilgung, die nur dann eingetragen wird, wenn ein Wert für die Sondertilgung übergeben wurde und der Monat der zwölfte des Jahres ist:<\/p>\n Fehlt noch die Restschuld. Diese berechnen wir ganz einfach aus der Differenz der Restschuld des vorherigen Monats und der Summe der regulären und der Sondertilgung. Da wir diesen Wert auch brauchen, um beim nächsten Schleifendurchlauf zu prüfen, ob überhaupt noch eine Restschuld vorhanden ist, speichern wir diesen in der Variablen curRestschuld<\/b>:<\/p>\n Ergebnis und Optimierungsvorschläge<\/b><\/p>\n Wenn wir uns nun das Ergebnis ansehen, sieht dieses erstmal genau so aus wie gewünscht. Allerdings scheint die Erstellung nicht gerade rasend schnell zu erfolgen, und zweitens ergibt sich ein noch viel größeres Problem: Beim Versuch, etwa eine Sondertilgung zu entfernen oder hinzuzufügen oder gar die Darlehenssumme, den Zinssatz oder den Tilgungssatz zu ändern, tut sich im restlichen Tabellenblatt überhaupt nichts. Normalerweise sollten sich die Werte aber anpassen – wir wollten doch auch ein wenig mit der Kalkulation des Tilgungsplans spielen! Da hätten wir auch gleich einen Access-Bericht mit dem Tilgungsplan erstellen können, in dem wir keine nachträglichen Veränderungen mehr vornehmen können.<\/p>\n Woran hapert es also Nun: Das Tabellenblatt wurde komplett mit konkreten Zahlenwerten gefüllt und es kommt keinerlei Formel zum Einsatz. Dies wäre aber nötig, wenn wir nach der änderung bestimmter Werte gleich eine aktualisierte Fassung des Tilgungsplans sehen wollen.<\/p>\n Also schauen wir uns an, wie wir den Tilgungsplan dynamisieren und ihn schneller ausgeben können.<\/p>\n Das Grundgerüst der Prozedur, die in diesem Fall TilgungsplanErstellen_Schnell <\/b>heißt, ist fast genau so aufgebaut wie das der zuvor vorgestellten Prozedur (s. Listing 2). <\/p>\n Listing 3: Diese Formeln liefern nur ein Ergebnis, wenn die Restschuld in der darüber liegenden Zeile nicht negativ ist.<\/p>\n Einer der wenigen Unterschiede ist die Deklaration eines Variant-Arrays namens varTexte<\/b>, dessen Zweck gleich im Anschluss erläutert wird. Außerdem brauchen wir die Variablen für intMonat <\/b>und intJahr <\/b>nicht mehr.<\/p>\n In Listing 2 finden Sie wiederum eine With…End With<\/b>-Konstruktion, welche alle nachfolgend vorgestellten Anweisungen enthält. Gleich zu Beginn benennen wir wieder das Tabellenblatt:<\/p>\n Kopfdaten aus Array füllen<\/b><\/p>\n Die Kopfdaten, die wir in der ersten Version noch zellenweise gefüllt haben, bedienen wir diesmal über ein Array. Dieses hat zwei Dimensionen und soll vier mal zwei Werte aufnehmen. Das Füllen des Arrays übernehmen die folgenden Anweisungen:<\/p>\n Ist diese Aufgabe erst erledigt, ist der Rest ein Einzeiler: <\/p>\n Wir füllen also die im Array enthaltenen Werte auf einen Schlag in einen Bereich des Tabellenblatts. Die Dimension des Arrays und die des angegebenen Range<\/b>-Objekts müssen natürlich übereinstimmen.<\/p>\n Anschließend formatieren wir die vier mit Zahlenwerten gefüllten Zellen:<\/p>\n Anschließend füllen wir die Zellen mit den Spaltenüberschriften des Tilgungsplans. Dies geschieht wiederum über ein Array, allerdings über eines, das wir in der gleichen Zeile erst erzeugen. Dazu verwenden wir die Array<\/b>-Funktion von VBA. Diese fügt die als Parameter angegebenen und durch Kommata getrennten Werte zu einem Datenfeld zusammen. Auch bei der Definition des Range<\/b>-Objekts und somit des Bereichs, der mit dem Inhalt des Arrays gefüllt werden soll, gehen wir zur Abwechslung einen anderen Weg. Wir definieren die linke obere und die rechte untere Zelle mit der Cells<\/b>-Anweisung und somit unter Verwendung absoluter numerischer Koordinaten:<\/p>\n Anschließend kümmern wir uns wieder um die erste Zeile des Tilgungsplans, die auch hier von den übrigen Zeilen abweicht, weil sie ihre Werte teilweise aus den im Kopfbereich der Tabelle enthaltenen Daten bezieht und nicht, wie die folgenden Zeilen, aus der jeweils darüber liegenden Zeile.<\/p>\n Die laufende Nummer ist einfach:<\/p>\n Die Angabe des Monats und des Jahres erfolgt direkt auf Basis entsprechender VBA-Funktionen:<\/p>\n Bei der Rate stoßen wir auf die erste von zahlreichen, noch folgenden Formeln. Diese weisen wir über die Eigenschaft FormulaR1C1 <\/b>der Zelle zu. Eine Formel enthält unter Excel meist Bezüge zu anderen Feldern, die sich auf verschiedene Arten ausdrücken lassen – zum Beispiel wie hier durch die Z1S1<\/b>-Notation. Dabei steht Z <\/b>für Zeile und S <\/b>für Spalte. Die englische Variante bezieht sich auf Rows <\/b>und Columns <\/b>und heißt deshalb R1C1<\/b>-Notation, entsprechende Formeln gibt man daher für die Eigenschaft FormulaR1C1 <\/b>ein.<\/p>\n Für die Tilgung in der ersten Zeile verwendet die Prozedur die folgende Formel: <\/p>\n Dies entspricht der Summe aus Zinssatz (Zeile 2, Spalte 2) und Tilgungssatz (Zeile 3, Spalte 2) multipliziert mit dem Darlehensbetrag (Zeile 1, Spalte 2) dividiert durch 12.<\/p>\n Die Zinsen für den ersten Monat und die Tilgung werden so berechnet:<\/p>\n Die Sondertilgung wird ebenfalls automatisch eingetragen. Dafür sorgt die folgende Formel, die innerhalb einer IF<\/b>-Bedingung abfragt, ob der Monat (vierte Zelle links relativ zur aktuellen Zelle) den Wert 12 <\/b>aufweist und somit dem Dezember entspricht. Ist dieser Ausdruck wahr, erhält die Zelle den Wert des in Zeile 4, Spalte 2 angegebenen Sondertilgungssatzes, sonst 0<\/b>:<\/p>\n Schließlich berechnet die folgende Formel basierend auf der im Kopf angegebenen Darlehenssumme und den links von der aktuellen Zelle zu findenden Tilgungsbeträgen die Restschuld:<\/p>\n Und nun wird es interessant: Wir kümmern uns um die folgenden 1.000 Zeilen. Ja, Sie haben richtig gelesen: 1.000 Monate sollen ja wohl ausreichen, um einen Betrag zu tilgen. Warum durchlaufen wir nicht einfach wieder eine Schleife so lange, bis die Restschuld kleiner 0 <\/b>und das Darlehen somit inklusive Zinsen zurückgezahlt wurde<\/p>\n Der Grund ist einfach: Weil wir dann wie im ersten Lösungsansatz wiederum eine Schleife brauchen, die jede Zeile einzeln schreibt – bis das Abbruchkriterium erfüllt ist. Das dauert aber sehr lange. Und es ist auch nicht nötig: Mittlerweile wissen wir, dass wir die Zellen mit Formeln füllen möchten, die ihr Ergebnis jeweils aus der Restschuld der vorherigen Zeile und den Tilgungen der aktuellen Zeile berechnen.<\/p>\n Wenn wir da mit Formeln arbeiten, welche die verwenden Zellen nicht absolut (also etwa mit R1C1<\/b>), sondern relativ ermitteln (etwa R[-1]C <\/b>oder RC[-3]<\/b>), könnten die Formeln sogar in allen Zeilen je Spalte gleich lauten.<\/p>\n Für die Spalte mit der laufenden Nummer könnte dies, zunächst für eine Zelle, etwa so aussehen:<\/p>\n Dies schreibt in die Zelle A7 <\/b>die Formel =R[-1]C + 1<\/b>, was nichts anderes bedeutet, als dass diese Zelle den Wert der darüber liegenden Zelle plus eins anzeigen soll. Nun brauchen wir dies aber nicht nur für eine Zelle, sondern am besten gleich für knapp 1.000 Zellen. Dazu brauchen Sie einfach nur den Range entsprechend zu formatieren:<\/p>\n Fertig – die erste Zeile wäre geschafft. Das Gleiche erledigen wir nun für die folgenden Spalten. Den Monat und das Jahr für die aktuelle Zeile liefert diese Formel, die sich das Datum aus der Zelle der darüber liegenden Zeile holt, ebenso das Jahr, den um eins erhöhten Monat und den Tag und diese zu einem neuen Datum zusammensetzt:<\/p>\n Die Formatierung in der Form mmmm yyyy <\/b>wird später erledigt. Die Formel für die Rate ist wiederum einfach. Sie enthält schlicht den Wert der darüber liegenden Zelle:<\/p>\n Die Zinsen errechnen sich aus der Restschuld der vorherigen Zeile (also R[-1]C7<\/b>) und dem Zinssatz aus dem Tabellenkopf (R2C2<\/b>, nicht zu verwechseln mit R2D2<\/b>) durch 12:<\/p>\n Die Tilgung stammt komplett aus der aktuellen Zeile, und zwar aus der Differenz der beiden links neben der aktuellen Zelle liegenden Zellen:<\/p>\n Fehlt noch die Sondertilgung, deren Wert viel einfacher als der der eher VBA-basierten ersten Lösung zu ermitteln ist – und zwar mit einer einfachen IF<\/b>-Abfrage, die prüft, ob das Datum in der Zelle vier Positionen neben der aktuellen Zelle den Monat 12 <\/b>enthält. Ist dies der Fall, schreibt die Formel den im Kopf (R4C2<\/b>) angegebenen Sondertilgungsbetrag in das Feld, sonst den Wert 0<\/b>:<\/p>\n Schließlich ermittelt die nächste Formel die neue Restschuld:<\/p>\n Lassen wir die Prozedur in der aktuellen Fassung durchlaufen, erhalten wir in rasender Geschwindigkeit die Excel-Tabelle aus Bild 3.<\/p>\n Bild 3: Nach Abzahlung des Darlehens wird lustig weitergetilgt.<\/span><\/b><\/p>\n Wir legen dort besonderes Augenmerk auf den Bereich, in dem die Restschuld das Vorzeichen wechselt, und stellen fest, dass nach dem Monat der vollständigen Rückzahlung des Darlehens noch viele Zeilen folgen – genau genommen bis zur tausendsten Zeile der Tabelle.<\/p>\n Das soll natürlich nicht so sein, ist aber im aktuellen Zustand das Opfer, das wir für den Zugewinn an Geschwindigkeit bringen mussten: Da wir während des „blinden“ Füllens der Tabellenzeilen keine Prüfung vornehmen, wann die Restschuld negativ wird, können wir an dieser Stelle auch nicht abbrechen.<\/p>\n Es gibt jedoch eine Lösung, die eng mit den in dieser Variante verwendeten Formeln zusammenhängt. Sie haben ja bereits die IF<\/b>-Funktion kennengelernt, mit der Sie einen von zwei Werten in Abhängigkeit vom Ergebnis der Bedingung ausgeben können. Warum also nicht prüfen, ob die Restschuld in der vorherigen Zeile nicht bereits negativ ist, und in diesem Fall einfach keinen Wert statt der mit den obigen Formeln ermittelten Werte ausgeben<\/p>\n Genau dies erledigen die Versionen unserer Formeln für die sieben Spalten aus Listing 3. Sie prüfen allerdings nicht nur, ob der Wert der Spalte mit der Restschuld in der darüber liegenden Zeile negativ ist, sondern auch, ob dort überhaupt ein Wert angezeigt wird.<\/p>\n Sonst erhalten wir hinter der ersten negativen Restschuld nämlich eine leere Zeile, und dann eine Reihe von Zellen mit dem Inhalt #Wert!<\/b> – die Zelle mit der Restschuld der darüber liegenden Zeile wäre dann nämlich leer, und hier hat Excel dann offensichtlich Schwierigkeiten zu entscheiden, ob dies nun ein positiver oder negativer Wert ist.<\/p>\n Also erweitern wir die Bedingung so, dass diese prüft, ob die entsprechende Zelle einen negativen oder gar keinen Wert anzeigt (siehe Bild 4).<\/p>\n Bild 4: Hier endet die Tilgungsberechnung an der richtigen Stelle. <\/span><\/b><\/p>\n Fehlt nur noch die Formatierung der Zellen, die aber mit den letzten beiden Zeilen aus Listing 3 schnell erledigt ist – immerhin brauchen wir ja nur zwei Range<\/b>-Objekten die Formatierung zuzuweisen.<\/p>\n Zusammenfassung und Ausblick<\/b><\/p>\n Die einfache Tilgung ist erledigt – Sie können die vier Faktoren Darlehenssumme, Zins- und Tilgungssatz sowie jährliche Sondertilgungen angeben und per Mausklick einen Tilgungsplan erzeugen.<\/p>\n Außerdem können Sie in der resultierenden Excel-Tabelle Hand anlegen: Sie können dort die vier eingegebenen Informationen anpassen oder auch beliebige Sondertilgungen in den gesamten Tilgungsplan einbauen.<\/p>\n Mit ein wenig Geschick können Sie änderungen vornehmen und Varianten einbauen.<\/p>\n Vorschläge gibt es genug: Sie können beispielsweise Finanzierungen bestehend aus mehreren Bausteinen und unterschiedlichen Parametern in mehreren Tabellenblättern eintragen und in einem weiteren die Gesamtbelastung je Monat ausgeben lassen.<\/p>\n Oder Sie bauen weitere, im Formular der Anwendung verfügbare Parameter ein, mit denen Sie etwa eine tilgungsfreie Anlaufzeit definieren oder festlegen, in welchen Intervallen die Raten gezahlt werden müssen (bei Banken normalerweise monatlich, bei KfW-Krediten jedes Quartal).<\/p>\n Listing 2: Gerüst der Prozedur zum Erstellen von Tilgungsplänen, schnelle Version<\/p>\n<\/td>\n<\/tr>\n Enthaltene Beispieldateien:<\/p>\n Tilgen.mdb<\/p>\nPrivate Sub cmdErstellen_Click()\r\n Call TilgungsplanErstellen( _\r\n Me!txtZinssatz \/ 100, _\r\n Me!txtDarlehen, _\r\n Me!txtTilgungssatz \/ 100, _\r\n Nz(Me!txtSondertilgung, 0))\r\n End Sub<\/pre>\n
![\"pic002.png\"](\"..\/fileadmin\/_temp_\/2009_06\/TilgenMitAccessUndExcel-web-images\/pic002_opt.jpeg\")
<\/p>\n\n
Public Sub TilgungsplanErstellen( _\r\ncurZinssatz As Currency, _\r\ncurDarlehen As Currency, _\r\ncurTilgungssatz As Currency, _\r\ncurSondertilgung As Currency)\r\nDim objExcel As Excel.Application\r\nDim objWorkbook As Excel.Workbook\r\nDim objSheet As Excel.Worksheet\r\nDim i As Integer\r\nDim intMonat As Integer\r\nDim intJahr As Integer\r\nDim strMonatJahr As String\r\nDim curRestschuld As Currency\r\nSet objExcel = New Excel.Application\r\nSet objWorkbook = objExcel.Workbooks.Add\r\nSet objSheet = objWorkbook.Worksheets(1)\r\nobjExcel.Visible = True\r\nWith objSheet\r\n''Hier wird das Tabellenblatt gefüllt\r\nEnd With\r\nobjWorkbook.Close\r\nobjExcel.Quit\r\nEnd Sub<\/pre>\n
.Name = "Tilgungsplan"<\/pre>\n
.Range("A1").Value = "Darlehen:"\r\n.Range("B1").Value = curDarlehen\r\n.Range("B1").NumberFormatLocal = "#.##0,00 \"¬"\r\n.Range("A2").Value = "Zinssatz:"\r\n.Range("B2").Value = curZinssatz\r\n.Range("B2").NumberFormatLocal = "0,00%"\r\n.Range("A3").Value = "Tilgungssatz:"\r\n.Range("B3").Value = curTilgungssatz\r\n.Range("B3").NumberFormatLocal = "0,00%"<\/pre>\n\n
intMonat = Month(Date)\r\nintJahr = Year(Date)<\/pre>\n
.Cells(5, 1) = "Lfd. Nr.:"\r\n.Cells(5, 2) = "Monat\/Jahr:"\r\n.Cells(5, 3) = "Rate:"\r\n.Cells(5, 4) = "Zinsen:"\r\n.Cells(5, 5) = "Tilgung:"\r\n.Cells(5, 6) = "Sondertilgung:"\r\n.Cells(5, 7) = "Restschuld:"<\/pre>\n
.Cells(6, 1) = 1<\/pre>\n
.Cells(6, 2) = intMonat & "\/" & intJahr<\/pre>\n
.Cells(6, 3) = (.Cells(2, 2) + .Cells(3, 2)) _\r\n* .Cells(1, 2) \/ 12<\/pre>\n
.Cells(6, 3).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
.Cells(6, 4) = curZinssatz * curDarlehen \/ 12\r\n.Cells(6, 4).NumberFormatLocal = "#.##0,00 \"¬"\r\n.Cells(6, 5) = curTilgungssatz * curDarlehen \/ 12\r\n.Cells(6, 5).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
If curSondertilgung > 0 And intMonat = 12 Then\r\n .Cells(6, 6) = curSondertilgung\r\n .Cells(6, 6).NumberFormatLocal = "#.##0,00 \"¬"\r\nEnd If<\/pre>\n
.Cells(6, 7) = .Cells(1, 2) - .Cells(6, 5) - .Cells(6, 6)\r\n.Cells(6, 7).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
curRestschuld = curDarlehen<\/pre>\n
Do While curRestschuld > 0<\/pre>\n
i = i + 1<\/pre>\n
.Cells(6 + i, 1).Value = i + 1<\/pre>\n
If intMonat = 12 Then\r\n intMonat = 1\r\n intJahr = intJahr + 1\r\n Else\r\n intMonat = intMonat + 1\r\n End If<\/pre>\n
strMonatJahr = intMonat & "\/" & intJahr\r\n .Cells(6 + i, 2).Value = strMonatJahr\r\n .Cells(6 + i, 2).NumberFormat = "mmmm yyyy"<\/pre>\n
.Cells(6 + i, 3) = .Cells(5 + i, 3)\r\n .Cells(6 + i, 3).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
.Cells(6 + i, 4) = .Cells(5 + i, 7) _\r\n * .Cells(2, 2) \/ 12\r\n .Cells(6 + i, 4).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
.Cells(6 + i, 5).Value = .Cells(6 + i, 3) _\r\n - .Cells(6 + i, 4)\r\n .Cells(6 + i, 5).NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n
If curSondertilgung > 0 And intMonat = 12 Then\r\n .Cells(6 + i, 6) = curSondertilgung\r\n .Cells(6 + i, 6).NumberFormatLocal = _\r\n "#.##0,00 \"¬"\r\n End If<\/pre>\n
curRestschuld = .Cells(5 + i, 7) _\r\n - .Cells(6 + i, 5).Value _\r\n - .Cells(6 + i, 6).Value\r\n .Cells(6 + i, 7).Value = curRestschuld\r\n .Cells(6 + i, 7).NumberFormatLocal = "#.##0,00 \"¬"\r\nLoop<\/pre>\n
''Laufende Nummerierung\r\n.Range("A7:A1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",R[-1]C+1)"\r\n''Monat\r\n.Range("B7:B1000").FormulaR1C1 = _\r\n"=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))"\r\n''Rate\r\n.Range("C7:C1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",R[-1]C)"\r\n''Zinsen\r\n.Range("D7:D1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",R[-1]C7 * R2C2 \/ 12)"\r\n''Tilgung\r\n.Range("E7:E1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",RC[-2] - RC[-1])"\r\n''Sondertilgung\r\n.Range("F7:F1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",IF(MONTH(RC[-4])=12,R4C2,0))"\r\n''Restschuld\r\n.Range("G7:G1000").FormulaR1C1 = "=IF(OR(R[-1]C7<0,R[-1]C7=""""),"""",R[-1]C - RC[-2] - RC[-1])"\r\n''Formatierungen\r\n.Range("B6:B1000").NumberFormat = "mmmm yyyy"\r\n.Range("C6:G1000").NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n.Name = "Tilgungsplan"<\/pre>\n
varTexte(0, 0) = "Darlehen:"\r\nvarTexte(1, 0) = "Zinssatz:"\r\nvarTexte(2, 0) = "Tilgungssatz:"\r\nvarTexte(3, 0) = "Sondertilgung:"\r\nvarTexte(0, 1) = dblDarlehen\r\nvarTexte(1, 1) = dblZinssatz\r\nvarTexte(2, 1) = dblTilgungssatz\r\nvarTexte(3, 1) = dblSondertilgung<\/pre>\n
.Range("A1:B4").Value = varTexte()<\/pre>\n.Range("B1").NumberFormatLocal = "#.##0,00 \"¬"\r\n.Range("B2").NumberFormatLocal = "0,00%"\r\n.Range("B3").NumberFormatLocal = "0,00%"\r\n.Range("B4").NumberFormatLocal = "#.##0,00 \"¬"<\/pre>\n.Range(Cells(5, 1), _\r\nCells(5, 7)).Value = Array("Lfd. Nr.:", _\r\n"Monat\/Jahr:", "Rate:", "Zinsen:", _\r\n"Tilgung:", "Sondertilgung:", "Restschuld:")<\/pre>\n.Cells(6, 1) = 1<\/pre>\n
.Cells(6, 2) = _\r\nCStr(Month(Date) & "\/" & Year(Date))<\/pre>\n
.Cells(6, 3).FormulaR1C1 = _\r\n"=(R2C2 + R3C2) * R1C2 \/ 12"<\/pre>\n
.Cells(6, 4).FormulaR1C1 = "=R1C2 * R2C2 \/ 12"\r\n.Cells(6, 5).FormulaR1C1 = "=R1C2 * R3C2 \/ 12"<\/pre>\n
.Cells(6, 6).FormulaR1C1 = _\r\n"=IF(MONTH(RC[-4])=12,R4C2,0)"<\/pre>\n
.Cells(6, 7).FormulaR1C1 = _\r\n"=R1C2 - RC[-2] - RC[-1]"<\/pre>\n
.Range("A7:A7").FormulaR1C1 = "=R[-1]C + 1"<\/pre>\n.Range("A7:A1000").FormulaR1C1 = "=R[-1]C + 1"<\/pre>\n.Range("B7:B1000").FormulaR1C1 = _\r\n"=DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C))"<\/pre>\n.Range("C7:C7").FormulaR1C1 = "=R[-1]C"<\/pre>\n.Range("D7:D7").FormulaR1C1 = _\r\n"=R[-1]C7 * R2C2 \/ 12"<\/pre>\n.Range("E7:E7").FormulaR1C1 = "=RC[-2] - RC[-1]"<\/pre>\n.Range("F7:F7").FormulaR1C1 = _\r\n"=IF(MONTH(RC[-4])=12,R4C2,0)"<\/pre>\n.Range("G7:G7").FormulaR1C1 = _\r\n"=R[-1]C - RC[-2] - RC[-1]"<\/pre>\n![\"pic003.png\"](\"..\/fileadmin\/_temp_\/2009_06\/TilgenMitAccessUndExcel-web-images\/pic003_opt.jpeg\")
<\/p>\n![\"pic004.png\"](\"..\/fileadmin\/_temp_\/2009_06\/TilgenMitAccessUndExcel-web-images\/pic004_opt.jpeg\")
<\/p>\n\n\n
\n \n \n \n Public Sub TilgungsplanErstellen_Schnell( _\r\ndblZinssatz As Double, _\r\ndblDarlehen As Double, _\r\ndblTilgungssatz As Double, _\r\ndblSondertilgung As Double)\r\nDim objExcel As Excel.Application\r\nDim objWorkbook As Excel.Workbook\r\nDim objSheet As Excel.Worksheet\r\nDim varTexte(3, 1) As Variant\r\nSet objExcel = New Excel.Application\r\nSet objWorkbook = objExcel.Workbooks.Add\r\nSet objSheet = objWorkbook.Worksheets(1)\r\nobjExcel.Visible = True\r\nWith objSheet\r\n''Hier wird das Tabellenblatt gefüllt.\r\nEnd With\r\nobjWorkbook.Close\r\nobjExcel.Quit\r\nEnd Sub<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Downloads zu diesem Beitrag<\/h3>\n