Start Hilfe und Support Microsoft Office EXCEL 2010 VBA: Der Umgang mit einer Collection am Beispiel eines Annuitätendarlehens

EXCEL 2010 VBA: Der Umgang mit einer Collection am Beispiel eines Annuitätendarlehens

E-Mail PDF

Die Collection ist ein zentrales Instrument, um gleichartige Elemente zu speichern/im Speicher zu halten und damit Operationen durchzuführen

Einleitung

Der Umgang mit einer Collection ist etwas aufwendiger darzustellen. Daher ist dieser Artikel etwas umfangreicher ausgefallen. Damit Sie das Beispiel, das hier gewählt wird, besser nachvollziehen können, steht es zum Download hier bereit. Gewählt wurde ein Annuitäten-/Hypothekendarlehen, für das anhand definierter Ausgangsparameter der Zins- und Tilgungsplan erstellt wird. Die einzelnen Zahlungselemente werden in einer Collection gehalten, um dann auf einem separaten Tabellenblatt angezeigt zu werden.

Was ist eine Collection, die in VBA zur Verfügung gestellt wird?

Eine Collection kann man sich vorstellen wir ein Regal, das Raum zur Verfügung stellt, um Gegenstände dort abzulegen. Was in das Regal deponiert wird, weiß die Collection nicht. Sie weiß lediglich, wo die Dinge zu finden sind und stellt sie bei Bedarf zur Verfügung. Der Vorteil einer Collection gegenüber einem Regal ist, dass es je nach Anforderungen dynamisch anwachsen kann.

Vergleichen kann man eine Collection auch mit einem Array, das in der Lage ist, einen Set von Informationen zu halten, z.B. Stringinformationen, die dann über einen Index abgerufen werden können. Über ReDim kann man Arrays bei Bedarf auch vergrößern, jedoch bleibt der Inhalt eines Arrays immer gleichartig oder anders gesagt immer vom Typ her so, wie man ihn definiert hat.

Eine Collection ist in der Lage, komplexere Datenstrukturen zu halten, z.B. selbst definierte Objekte. In diesem Beispiel wird die Collection entsprechend verwendet. Der Umgang mit der Collection (selbst ein VBA-Objekt) erfordert etwas Schreibarbeit und den Umgang mit Eigenschaften und Methoden, auf der anderen Seite sorgt die Struktur von Objekten für übersichtlichen und transparenten Programmcode, der die Nachvollziehbarkeit und Erweiterbarkeit vereinfacht.

Wie funktioniert ein Annuitätendarlehen/Hypothekendarlehen?

Die Funktionsweise der Collection wird anhand eines Annuitätendarlehens erklärt. Hier kurz eine Erläuterung, wie ein Annuitätendarlehen funktioniert:

Ein Annuitätendarlehen wird in der Regel von Banken für die Hausfinanzierung angeboten. Der Sinn des Darlehens liegt darin, dass zu Beginn anhand des vereinbarten Zinssatzes und der anfänglichen Tilgung ein konstanter Kapitaldienst errechnet wird, der über die Laufzeit des Kredites gleich bleibt. Das hat den Vorteil, dass der Kunde bis zum Ende der Festzinsbindung in der Regel monatlich mit einer konstanten Zahlung kalkulieren kann, vergleichbar mit einer gleichbleibenden Miete.

Dabei ermittelt sich die Annuität (= die Summe aus Zins- und Tilgungszahlung) wie folgt:

Die Annuität (die monatliche Zahlung an die Bank) berechnet sich aus der Kombination vom Nominalzins und der anfänglichen Tilgung. In diesem Beispiel wird (und das ist der Regelfall), davon ausgegangen, dass eine monatliche Zahlung erfolgt, d.h. dass man in jeden Monat einen festen Betrag (die Annuität) an die Bank zahlt. Diese Annuität enthält dann den Zinsanteil (das ist der Obulus für das Ausleihen des Geldes an die Bank und das, woran die Bank verdient) und den Tilgungsanteil (das ist der Teil, den Sie an die Bank zurückzahlen und der dafür sorgt, dass die Restschuld/das Restkapital kleiner wird). Im Zeitablauf verändert sich das Verhältnis zwischen Zins und Tilgung. Der Tilgungsanteil der Annuität wächst um den Betrag, um den der Zinsanteil sinkt. Die Annuitätenhöhe wird wie folgt berechnet:

Nominalkapital * (Nominalzins + Anfängliche Tilgung) / 12 = Annuität

Beispiel:

  • Nominalkapital: € 200.000,00
  • Nominalzins: 5,00%
  • Anfängliche Tilgung: 1,00%

€ 200.000 [Nominalkapital] * (5,00% [Nominalzins] + 1,00% [Anfängliche Tilgung]) / 12 = € 1.000 [Annuität]

Auf Basis dieser Ausgangsparameter wird dann der Zins- und Tilgungsplan errechnet.

In unserem Beispiel wird davon ausgegangen, dass die Zahlungen generell auf dem Monatsultimo liegen, das ist in der Regel auch so der Fall und stellt keine Verzerrung der Realität dar.

Grundüberlegungen

Als Ergebnis soll für ein beliebiges Annuitätendarlehen der Zins- und Tilgungsplan ausgegeben werden. Die Dauer des Darlehens, bis es zurückgezahlt ist, richtet sich nach dem Nominalzins und der anfänglichen Tilgung, ist also dynamisch. Dafür eignet sich die Collection als Datenhalter für den Zins- und Tilgungsplan bestens.

Somit gliedert sich das Programm in die folgenden drei Schritte:

  1. Erfassung der Ausgangsparameter (Die Parameter des Darlehens werden auf einem Tabellenblatt erfasst, die Annuität gleich mit EXCEL-Bordmitteln auf dem Tabellenblatt (ohne VBA-Code) ermittelt.)
    parameter
  2. Kalkulation des Zins- und Tilgungsplans (Über einen Button/Knopf auf dem Eingabetabellenblatt wird die Berechnung des Zins- und Tilgungsplans gestartet, zugrunde liegt eine kleine VBA-Ablaufsteuerung.)
    button
  3. Der Zins- und Tilgungsplan wird auf einem separaten Tabellenblatt ausgegeben (Da der Zins- und Tilgungsplan je nach eingestellten Parametern unterschiedlich lang ist, wird das Ausgabe-Tabellenblatt zuvor jeweils vollständig gelöscht und das neu berechnete Ergebnis angezeigt und dann automatisch formatiert, so dass ein sauberer Ausdruck erfolgen kann.)
    ausschnitt_zahlungsplan

Bis auf die Berechnung der Annuität wird alles in VBA berechnet und VBA übernimmt auch die Anzeige auf dem Tabellenblatt. Auf diese Weise werden Fehlerquellen, die auf einem Arbeitsblatt durch falsche Zellbezüge entstehen können, vermieden.

Notwendige programmtechnische Elemente

  1. Ein Zahlungselement des Zins- und Tilgungsplans besteht aus den folgenden Informationen:
    • Datum
    • Restschuld
    • Zinszahlung
    • Tilgungszahlung
    • Annuität
    Diese Informationen werden in dem Objekt clsZahlungselement gespeichert. Zu jedem Zahlungszeitpunkt existiert somit ein Zahlungselement.
  2. Das Collection-Objekt clsZinsUndTilgungsplan dient als Sammelbecken für alle einzelnen Zahlungszeitpunkte, merkt sich also jedes einzelne clsZahlungselement.
  3. Eine Ablaufsteuerung übernimmt das, was unter der Überschrift Grundüberlegungen beschrieben ist, steuert also die Berechnung und die Ausgabe des Ergebnisses "Zins- und Tilgungsplan".

Im Folgenden werden die VBA-Programmcodes für die beschriebenen Programmelemente hier dargestellt, die weiteren Programmbestandteile sind für das Verständnis nicht relevant, wenn Sie das dennoch interessiert, laden Sie sich bitte das Beispiel herunter, die Funktionsweise lässt sich leicht durch entsprechendes Debugging nachvollziehen.

Der Programmcode ist dokumentiert und ich hoffe, dass er für das Verständis ausreicht. Ansonsten können Sie eine Nachricht schreiben und entsprechende Hinweise hinterlassen, so dass dieser Artikel nachbearbeitet werden kann.

Code clsZahlungselement

Option Explicit

' Dieses Klassenmodul/Objekt dient lediglich dazu, als Datenhalter zu dienen.
' Die Eigenschaften halten die Informationen eines Zahlungselementes. Der
' Zugriff auf die Inhalte der Eigenschaften wird durch die Property-Methoden
' sichergestellt.

' Es werden die Eigenschaften definiert

Dim eDatum As Date
Dim eRestschuld As Double
Dim eZinszahlung As Double
Dim eTilgungszahlung As Double
Dim eAnnuitaet As Double


Property Let Restschuld(ByVal vWert As Double)

  eRestschuld = vWert
End Property


Property Get Restschuld() As Double

   Restschuld = eRestschuld
End Property


Property Let Zinszahlung(ByVal vWert As Double)

  eZinszahlung = vWert
End Property


Property Get Zinszahlung() As Double

  Zinszahlung = eZinszahlung
End Property


Property Let Tilgungszahlung(ByVal vWert As Double)

  eTilgungszahlung = vWert
End Property


Property Get Tilgungszahlung() As Double

  Tilgungszahlung = eTilgungszahlung
End Property


Property Let Annuitaet(ByVal vWert As Double)

  eAnnuitaet = vWert
End Property


Property Get Annuitaet() As Double

  Annuitaet = eAnnuitaet
End Property


Property Let Datum(ByVal vWert As Date)

  eDatum = vWert
End Property


Property Get Datum() As Date

  Datum = eDatum
End Property

Code clsZinsUndTilgungsplan

Option Explicit

' Zunächst werden die Eigenschaften des Objektes clsZinsUndTilgungsplan definiert, an diese
' Eigenschaften kommt man ohne Weiteres von außen nicht heran, außer man will das.
' Dann definiert man Propertys, die den Zugriff auf die Eigenschaften zulassen.

Dim eStartdatum As Date ' Eigenschaft Auszahlungsdatum des Darlehens
Dim eNominalkapital As Double ' Eigenschaft Nominalkapital
Dim eZinssatz As Double ' Eigenschaft Zinssatz
Dim eAnnuitaet As Double ' Eigenschaft Annuiaet
Dim eZahlungsreihe As New Collection ' Eigenschaft Zahlungsreihe, in diese Collection werden
                                     ' alle Elemente der Zahlungsreihe eingefügt und dann
                                     ' zu einem späteren Zeitpunkt verwendet.
                                     ' Das Wörtchen "New" sorgt dafür das gleich eine Instanz
                                     ' des Objectes Collection erzeugt wird. Mit anderen Worten:
                                     ' Die Collection eZahlungsreihe wird in den Hauptspeicher des
                                     ' Rechners gelegt, die Zahlungsreihe (Collection) ist jedoch
                                     ' noch leer.


Property Get Count() As Long

  ' Gibt die Anzahl der Elemente in der Zahlungsreihe zurück

  Count = eZahlungsreihe.Count
End Property


' Nun folgenden die Propertys, die es erlauben, auf die Eingeschaften dieses Objektes
' zuzugreifen. Die Propertys können später wie eine Variable verwerndet werden, können
' also links und rechts von Gleichheitszeichen verwendet werden (wenn man das ent-
' prechend zulässt).


Property Let Startdatum(ByVal vWert As Date)

  ' Der Eigenschaft eStartdatum wird der Inhalt von vWert zugewiesen

  eStartdatum = vWert
End Property


Property Get Startdatum() As Date

  ' Als Ergebniswert wird der Inhalt der Eigenschaft eStartdatum
  ' zurückgegeben.

  Startdatum = eStartdatum
End Property


Property Let Nominalkapital(ByVal vWert As Double)

  ' Der Eigenschaft eNominalkapital wird der Inhalt von vWert zugewiesen

  eNominalkapital = vWert
End Property


Property Get Nominalkapital() As Double

  ' Als Ergebniswert wird der Inhalt der Eigenschaft eNominalkapital
  ' zurückgegeben.

 Nominalkapital = eNominalkapital
End Property


Property Let Zinssatz(ByVal vWert As Double)

  ' Der Eigenschaft eZinssatz wird der Inhalt von vWert zugewiesen

  eZinssatz = vWert
End Property


Property Get Zinssatz() As Double

  ' Als Ergebniswert wird der Inhalt der Eigenschaft eZinssatz
  ' zurückgegeben.

  Zinssatz = eZinssatz
End Property


Property Let Annuitaet(ByVal vWert As Double)

  ' Der Eigenschaft eAnnuitaet wird der Inhalt von vWert zugewiesen

  eAnnuitaet = vWert
End Property


Property Get Annuitaet() As Double

  ' Als Ergebniswert wird der Inhalt der Eigenschaft eAnnuitaet
  ' zurückgegeben.

  Annuitaet = eAnnuitaet
End Property


Sub ZahlungsplanErstellen()

  ' Diese Prozedur erstellt den Zins und Tilgungsplan

  ' Ein Element in der Zahlungsreihe besteht aus den folgenden
  ' Bestandteilen, die hier als Variablen deklariert werden.
  Dim dRestschuld As Double
  Dim dateZahlungsdatum As Date
  Dim dZinszahlung As Double
  Dim dTilgungszahlung As Double
  Dim dAnnuitaet As Double
  Dim iZahlungselement As clsZahlungselement

  ' Nun wird zunächst einmal geprüft, ob sich die Rechnerei überhaupt lohnt.

  If eNominalkapital = 0 Then Exit Sub
  If eZinssatz = 0 Then Exit Sub
  If eAnnuitaet = 0 Then Exit Sub

  ' Ist der Zinsanteil >= der Annuitätenhöhe, wird der Kredit nie getilgt,
  ' die Berechnung läuft ins Endlose, das wollen wir nicht. Also, raus hier...
  If eNominalkapital * eZinssatz / 12 >= eAnnuitaet Then Exit Sub

  dRestschuld = eNominalkapital
  dateZahlungsdatum = eStartdatum

  ' Das erste Element wird in die Zahlungsreihe aufgenommen

  ' Das Zahlungselement wir als Instanz im Speicher angelegt
  Set iZahlungselement = New clsZahlungselement

  iZahlungselement.Datum = eStartdatum
  iZahlungselement.Restschuld = dRestschuld
  iZahlungselement.Zinszahlung = 0
  iZahlungselement.Tilgungszahlung = 0
  iZahlungselement.Annuitaet = 0

  eZahlungsreihe.Add iZahlungselement

  ' Das Element ist nun in der Collection und wird hier nicht mehr gebraucht.
  Set iZahlungselement = Nothing

  ' Bisher war fast alles nur Schreibarbeit, hier findet die eigentliche Berechnung
  ' der Zahlungsreihe und damit des Annuitätendarlehens statt.
  Do While dRestschuld > 0
    ' Das Datum wird um einen Monat erhöht und auf das Monatsultimo gesetzt
    dateZahlungsdatum = DateAdd("m", 1, "15." & Month(dateZahlungsdatum) & "." & Year(dateZahlungsdatum))
    dateZahlungsdatum = Monatsletzter(dateZahlungsdatum)

    ' Berechnung der einzelnen Elemente eines Zahlungselementes
    dZinszahlung = dRestschuld * eZinssatz / 12
    dTilgungszahlung = eAnnuitaet - dZinszahlung

    ' Hier wird geprüft, ob die Tilgung größer ist als die Restschuld.
    ' Das spielt eine Rolle am Ende der Laufzeit des Darlehens und ist
    ' das Abbruchkriterium der Do..Loop-Schleife. Dann ist der Kredit getilgt.
    If dTilgungszahlung > dRestschuld Then
      dTilgungszahlung = dRestschuld
    End If

    dRestschuld = dRestschuld - dTilgungszahlung
    dAnnuitaet = dZinszahlung + dTilgungszahlung

    ' Die errechneten Werte werden nun an ein Zahlungselement übergeben,
    ' das Zahlungselement wird der Zahlungsreihe (Collection) zugefügt.

    ' Das Zahlungselement wir als Instanz im Speicher angelegt
    Set iZahlungselement = New clsZahlungselement

    iZahlungselement.Datum = dateZahlungsdatum
    iZahlungselement.Restschuld = dRestschuld
    iZahlungselement.Zinszahlung = dZinszahlung
    iZahlungselement.Tilgungszahlung = dTilgungszahlung
    iZahlungselement.Annuitaet = dAnnuitaet

    eZahlungsreihe.Add iZahlungselement

    ' Das Element ist nun in der Collection und wird hier nicht mehr gebraucht.
    Set iZahlungselement = Nothing
  Loop

End Sub


Function Zahlungselement(ByVal vIndex As Double) As clsZahlungselement

  ' Diese Methode dient dazu, dass von außen ein Zahlungselement abgerufen werden kann.
  ' Übergeben wird wie bei einem Array ein Index, der dann das entsprechende Element
  ' aus der Collection abruft und an die Methode als Ergebniswert übergibt.

  Set Zahlungselement = eZahlungsreihe.Item(vIndex)
End Function

Code AblaufSteuerung

Sub AblaufSteuerung(ByRef vTabelle As Worksheet)

  ' Vom Klassenmodul clsZinsUndTilgungsplan wird eine Instanz erstellt, die dann die einzelnen
  ' Elemente der Zahlungsreihe hält.
  Dim iZinsUndTilungsplan As New clsZinsUndTilgungsplan

  ' Die im Tabellenblatt tabParameter eingegebenen Parameter zum Darlehen werden an das Objekt
  ' clsZinsUndTilgungsplan übergeben. Die verwendeten Ranges sind benamt, um die Lesbarkeit
  ' des Programmcodes zu erleichtern und Fehler zu vermeiden.
  With iZinsUndTilungsplan
    .Startdatum = Range("Auszahlungsdatum").Value
    .Nominalkapital = Range("Kapital").Value
    .Zinssatz = Range("Nominalzins").Value
    .Annuitaet = Range("Annuitaet").Value
    .ZahlungsplanErstellen
  End With

  ' Die Zahlungsreiche wird in der Ergebnistabelle ausgegeben.
  ZahlungsreiheAnzeigen vTabelle, iZinsUndTilungsplan

End Sub

Schlussbetrachtung

Dieser Artikel dient dazu, einen ersten Eindruck über die Möglichkeit einer Collection zu vermitteln. Er soll Appetit machen auf mehr. In diesem Beispiel wurde lediglich einfach per Index auf ein Element in der Collection zugegriffen, die Collection bietet jedoch auch die Möglichkeit, über Schlüssel (Key), die man beim Einfügen eines Elementes mitgibt, auf diese zuzugreifen. In diesem Beispiel wurde die Zahlungsreihe von vorn nach hinten gerechnet, so dass die Elemente schon in der richtigen Reihenfolge ankommen. Man kann die Collection jedoch auch so erweitern, dass man ihr beibringen kann, an welcher Stelle ein bestimmtes Element einzufügen ist, das funktioniert über die Add-Methode, in dem man mitteilt, ob ein Element Before oder After einem bestimmten Element eingefügt werden soll (Sorted-Collection). Probieren Sie es aus, ich bin überzeugt, dass Sie viele weitere Anwendungsfälle für die Collection finden werden.

Und hier noch einmal das Beispiel zum Download.


Zuletzt aktualisiert am Montag, den 04. November 2013 um 18:38 Uhr  


Machen Sie mit

Sie bewegt ein Thema rund um unser Dorf, das Sie gerne mit anderen diskutieren möchten? Das kommt auf unsere Internetseite von Happerschoß. Melden Sie sich, schreiben Sie eine Mail direkt über den Kontaktbereich oder natürlich auch über Info@happerschoss.net.