/ / Leencalculator in Excel VBA

Leencalculator in Excel VBA

Deze pagina leert je hoe je een eenvoudige kunt maken leningberekenaar in Excel VBA. Het werkblad bevat de volgende ActiveX-besturingselementen: twee schuifbalken en twee optieknoppen.

Leencalculator in Excel VBA

Opmerking: de onderstaande instructies leren je niet hoe je het werkblad moet opmaken. We gaan ervan uit dat u weet hoe u lettertypen kunt wijzigen, rijen en kolommen kunt invoegen, randen kunt toevoegen, achtergrondkleuren kunt wijzigen, enzovoort.

Voer de volgende stappen uit om de leningcalculator te maken:

1. Voeg de twee schuifbalkknoppen toe. Klik op Invoegen vanaf het tabblad Ontwikkelaar en klik vervolgens op Bladeren in het gedeelte ActiveX-besturingselementen.

Maak schuifbalken

2. Voeg de twee optieknoppen toe. Klik op Invoegen vanaf het tabblad Ontwikkelaar en klik vervolgens op de Optieknop in het gedeelte ActiveX-besturingselementen.

Optieknoppen maken

Wijzig de volgende eigenschappen van de schuifbalkknoppen (zorg ervoor dat de Ontwerpmodus is geselecteerd).

3. Klik met de rechtermuisknop op de eerste schuifbalkbediening en klik vervolgens op Eigenschappen. Stel Min in op 0, Max op 20, SmallChange op 0 en LargeChange op 2.

4. Klik met de rechtermuisknop op het tweede schuifbalkbedieningselement en klik vervolgens op Eigenschappen. Stel Min tot 5, Max tot 30, SmallChange tot 1, LargeChange tot 5 en LinkedCell tot F8.

Uitleg: wanneer u op de pijl klikt, gaat de waarde van de schuifbalk omhoog of omlaag met SmallChange. Wanneer u klikt tussen de schuifregelaar en de pijl, gaat de waarde van de schuifbalk omhoog of omlaag met LargeChange.

Maak een werkblad wijzigingsgebeurtenis. Code die wordt toegevoegd aan het werkblad Wijzigingsgebeurtenis wordt uitgevoerd door Excel VBA wanneer u een cel in een werkblad wijzigt.

5. Open de Visual Basic-editor.

6. Dubbelklik op Blad1 (Blad1) in de Projectverkenner.

7. Kies Worksheet in de vervolgkeuzelijst links en kies Wijzigen in de rechter vervolgkeuzelijst.

Werkblad Wijzigingsgebeurtenis in Excel VBA

8. Het Werkblad Wijzigingsevent luistert naar alle wijzigingen op Blad1. We willen alleen dat Excel VBA de sub Calculate uitvoert als er iets verandert in cel D4. Om dit te bereiken, voegt u de volgende coderegel toe aan de werkbladveranderingsgebeurtenis (later meer over de subberekening berekenen).

If Target.Address = "$D$4" Then Application.Run "Calculate"

9. Krijg het juiste percentage in cel F6 (wijzig het formaat van cel F6 in percentage). Klik met de rechtermuisknop op de eerste schuifbalkbediening en klik vervolgens op Code weergeven. Voeg de volgende coderegels toe:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100
Application.Run "Calculate"

End Sub

10. Klik met de rechtermuisknop op het tweede schuifbalkbedieningselement en klik vervolgens op View Code. Voeg de volgende coderegel toe:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Klik met de rechtermuisknop op het besturingselement voor de eerste optieknop en klik vervolgens op View Code. Voeg de volgende coderegel toe:

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then Range("C12").Value = "Monthly Payment"
Application.Run "Calculate"

End Sub

12. Klik met de rechtermuisknop op het besturingselement van de tweede optieknop en klik vervolgens op View Code. Voeg de volgende coderegel toe:

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then Range("C12").Value = "Yearly Payment"
Application.Run "Calculate"

End Sub

13. Tijd om de sub te maken. Je kunt door ons Functie- en Subhoofdstuk gaan voor meer informatie over subs. Als je gehaast bent, plaats je gewoon de sub met de naam Calculate in een module (klik in de Visual Basic Editor op Invoegen, Module).

Sub Calculate()

Dim loan As Long, rate As Double, nper As Integer

loan = Range("D4").Value
rate = Range("F6").Value
nper = Range("F8").Value

If Sheet1.OptionButton1.Value = True Then
    rate = rate / 12
    nper = nper * 12
End If

Range("D12").Value = -1 * WorksheetFunction.Pmt(rate, nper, loan)

End Sub

Uitleg: de sub krijgt de juiste parameters voor de werkbladfunctie Pmt. De Pmt-functie in Excel berekent de betalingen voor een lening op basis van constante betalingen en een constante rentevoet. Als u maandelijkse betalingen uitvoert (Sheet1.OptionButton1.Value = True), gebruikt Excel VBA rate / 12 voor rate en nper * 12 voor nper (totaal aantal betalingen). Het resultaat is een negatief getal, omdat betalingen als een debet worden beschouwd. Het resultaat met -1 vermenigvuldigen geeft een positief resultaat.

Lening Calculator Resultaat

Lees ook: