Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 6/2009.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Erzeugen Sie per Knopfdruck einen Tilgungsplan mit den gewünschten Parametern als Excel-Tabelle.

Techniken

VBA, Excel

Voraussetzungen

Access 2000 und höher, Excel 2000 und höher

Beispieldateien

Tilgen.mdb

Shortlink

695

Tilgen mit Access und Excel

André Minhorst, Duisburg

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 Abb. 1. Es dient der Eingabe des Darlehensbetrags, des Zinssatzes, des Tilgungssatzes und gegebenenfalls der Angabe der jährlichen Sondertilgungen.

pic001.png

Abb. 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 (s. Abb. 2).

pic002.png

Abb. 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:

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Verwandte Beiträge:

Excel automatisieren

Excel-Daten importieren mit TransferSpreadsheet

Lastschrifteinzug per Datenträgeraustausch

Benutzerdefinierte Formatierung

Word, Excel und Co. im Griff

Tabellen wie unter Excel

Excel-Import-Assistent im Eigenbau

Importieren von Textdateien

Projektzeitmanager

© 2003-2015 André Minhorst Alle Rechte vorbehalten.