Tilgen mit Access und Excel

2009, 6/2009, Interaktiv, Lösungen

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.

Grundsätzlich soll dieser Beitrag ein Beispiel für einen anderen Beitrag liefern – nämlich für Excel automatisieren (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.

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.

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.

pic001.png

Bild 1: Formular zum Aufnehmen der Darlehensparameter

Das Formular enthält vier einfache Textfelder mit den Namen txtDarlehen, txtZinssatz, txtTilgungssatz und txtSondertilgung. Die Schaltfläche mit der Beschriftung Tilgungsplan erstellen erhält den Namen cmdErstellen und wird mit einer Ereignisprozedur ausgestattet, die wie folgt aussieht:

Private Sub cmdErstellen_Click()
    Call TilgungsplanErstellen( _
    Me!txtZinssatz / 100, _
    Me!txtDarlehen, _
    Me!txtTilgungssatz / 100, _
    Nz(Me!txtSondertilgung, 0))
    End Sub

Damit verlassen wir dieses Formular zunächst und schauen uns an, wie das fertige Excel-Dokument später aussehen soll (siehe Bild 2).

pic002.png

Bild 2: So soll der Tilgungsplan einmal aussehen.

Im Kopf des Dokuments sollen die wichtigsten Informationen angezeigt werden. Darunter beginnt der Tilgungsplan, der sechs Spalten enthält:

  • die laufende Nummer des Monats
  • Monat und Jahr
  • Höhe der Rate
  • Zinsen im aktuellen Monat
  • Tilgung im aktuellen Monat
  • Sondertilgung im aktuellen Monat
  • Restschuld nach diesem Monat

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. Diese wird, wie bereits erwähnt, über die Schaltfläche cmdErstellen 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 übergeben wird, wenn das Textfeld mit der Sondertilgung leer ist.

Listing 1: Gerüst der Prozedur zum Erstellen von Tilgungsplänen

Public Sub TilgungsplanErstellen( _
curZinssatz As Currency, _
curDarlehen As Currency, _
curTilgungssatz As Currency, _
curSondertilgung As Currency)
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim i As Integer
Dim intMonat As Integer
Dim intJahr As Integer
Dim strMonatJahr As String
Dim curRestschuld As Currency
Set objExcel = New Excel.Application
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Worksheets(1)
objExcel.Visible = True
With objSheet
''Hier wird das Tabellenblatt gefüllt
End With
objWorkbook.Close
objExcel.Quit
End Sub

Die Routine deklariert je ein Excel.Application-, Excel.Workbook– und Excel.Worksheet-Objekt und einige weitere Variablen, deren Zweck wir weiter unten erläutern. objExcel wird mit einer neuen Instanz von Excel gefüllt, objWorkbook erhält einen Verweis auf eine neu angelegte Excel-Datei und objSheet füllt die Prozedur mit dem ersten Tabellenblatt der neuen Excel-Datei.

Nachdem die Excel-Instanz sichtbar gemacht wurde, folgt ein With…End With-Konstrukt, in dem sich alle Anweisungen zum Füllen des Tilgungsplans befinden. Diese stellen wir gleich im Anschluss vor.

Die Close-Methode des objWorkbook-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-Methode von objExcel 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.

Kopfdaten anlegen

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:

.Name = "Tilgungsplan"

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:

.Range("A1").Value = "Darlehen:"
.Range("B1").Value = curDarlehen
.Range("B1").NumberFormatLocal = "#.##0,00 "¬"
.Range("A2").Value = "Zinssatz:"
.Range("B2").Value = curZinssatz
.Range("B2").NumberFormatLocal = "0,00%"
.Range("A3").Value = "Tilgungssatz:"
.Range("B3").Value = curTilgungssatz
.Range("B3").NumberFormatLocal = "0,00%"

Wenn Sie einmal den Ausdruck für eine Formatierung benötigen, hilft folgender Trick:

  • Excel öffnen
  • Feld über den entsprechenden Dialog wie gewünscht formatieren und den Fokus auf diesem Feld belassen
  • Mit Alt + F11 zum VBA-Editor wechseln und dort im Direktfenster die Anweisung Debug.Print ActiveCell.NumberFormatLocal absetzen. Der resultierende Ausdruck ist die lokale Variante, die englische erhalten Sie einfach mit NumberFormat statt NumberFormatLocal.

Startzeitpunkt festlegen

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 zu übergeben.

Dazu müssen Sie natürlich zwei weitere Parameter im Prozedurkopf definieren (am besten namens intMonat und intJahr) 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 und Year die entsprechenden Zahlen ermitteln, zum Beispiel:

intMonat = Month(Date)
intJahr = Year(Date)

Spaltenüberschriften

Danach folgen schon die Spaltenüberschriften des eigentlichen Tilgungsplans:

.Cells(5, 1) = "Lfd. Nr.:"
.Cells(5, 2) = "Monat/Jahr:"
.Cells(5, 3) = "Rate:"
.Cells(5, 4) = "Zinsen:"
.Cells(5, 5) = "Tilgung:"
.Cells(5, 6) = "Sondertilgung:"
.Cells(5, 7) = "Restschuld:"

Erste Zeile des Tilgungsplans

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:

.Cells(6, 1) = 1

Monat und Jahr werden in der zweiten Zelle zusammengefasst:

.Cells(6, 2) = intMonat & "/" & intJahr

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:

.Cells(6, 3) = (.Cells(2, 2) + .Cells(3, 2)) _
* .Cells(1, 2) / 12

Natürlich wird auch diese Zeile im Währungsformat dargestellt:

.Cells(6, 3).NumberFormatLocal = "#.##0,00 "¬"

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:

.Cells(6, 4) = curZinssatz * curDarlehen / 12
.Cells(6, 4).NumberFormatLocal = "#.##0,00 "¬"
.Cells(6, 5) = curTilgungssatz * curDarlehen / 12
.Cells(6, 5).NumberFormatLocal = "#.##0,00 "¬"

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):

If curSondertilgung > 0 And intMonat = 12 Then
    .Cells(6, 6) = curSondertilgung
    .Cells(6, 6).NumberFormatLocal = "#.##0,00 "¬"
End If

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:

.Cells(6, 7) = .Cells(1, 2) - .Cells(6, 5) - .Cells(6, 6)
.Cells(6, 7).NumberFormatLocal = "#.##0,00 "¬"

Außerdem speichern wir die Restschuld in einer Variablen:

curRestschuld = curDarlehen

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 größer 0 ist:

Do While curRestschuld > 0

Als Erstes wird innerhalb der Do While-Schleife die Zählervariable erhöht:

    i = i + 1

Daraus ergibt sich nach Addition von 1 (weil wir die erste Zeile schon geschrieben haben) gleich die laufende Nummer:

    .Cells(6 + i, 1).Value = i + 1

Dann berechnen wir den Monat und das Jahr. Normalerweise wird einfach 1 zum Monat hinzuaddiert.

Wenn intMonat jedoch schon den Wert 12 hat, wird intMonat auf 1 gesetzt und der Wert von intJahr um 1 erhöht:

    If intMonat = 12 Then
        intMonat = 1
        intJahr = intJahr + 1
    Else
        intMonat = intMonat + 1
    End If

Das Ergebnis landet dann zusammengefasst und entsprechend formatiert in der zweiten Zelle einer jeden Zeile:

    strMonatJahr = intMonat & "/" & intJahr
    .Cells(6 + i, 2).Value = strMonatJahr
    .Cells(6 + i, 2).NumberFormat = "mmmm yyyy"

Dann folgt die Rate. Diese wird einfach aus der darüber liegenden Zeile gewonnen:

    .Cells(6 + i, 3) = .Cells(5 + i, 3)
    .Cells(6 + i, 3).NumberFormatLocal = "#.##0,00 "¬"

Die Zinsen berechnen sich aus der Restschuld des Vormonats und dem Zinssatz – wieder geteilt durch zwölf:

    .Cells(6 + i, 4) = .Cells(5 + i, 7) _
    * .Cells(2, 2) / 12
    .Cells(6 + i, 4).NumberFormatLocal = "#.##0,00 "¬"

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:

    .Cells(6 + i, 5).Value = .Cells(6 + i, 3) _
    - .Cells(6 + i, 4)
    .Cells(6 + i, 5).NumberFormatLocal = "#.##0,00 "¬"

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:

    If curSondertilgung > 0 And intMonat = 12 Then
        .Cells(6 + i, 6) = curSondertilgung
        .Cells(6 + i, 6).NumberFormatLocal = _
        "#.##0,00 "¬"
    End If

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:

    curRestschuld = .Cells(5 + i, 7) _
    - .Cells(6 + i, 5).Value _
    - .Cells(6 + i, 6).Value
    .Cells(6 + i, 7).Value = curRestschuld
    .Cells(6 + i, 7).NumberFormatLocal = "#.##0,00 "¬"
Loop

Ergebnis und Optimierungsvorschläge

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.

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.

Also schauen wir uns an, wie wir den Tilgungsplan dynamisieren und ihn schneller ausgeben können.

Das Grundgerüst der Prozedur, die in diesem Fall TilgungsplanErstellen_Schnell heißt, ist fast genau so aufgebaut wie das der zuvor vorgestellten Prozedur (s. Listing 2).

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar