Tilgen mit Access und Excel

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.

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

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

Benutzername oder E-Mail-Adresse: